By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE x (
id INT,
location INT
);
INSERT INTO x VALUES
(1, 1),
(2, NULL),
(3, 2)
;
3 rows affected
CREATE VIEW vw WITH SCHEMABINDING AS
SELECT Id, Location, COUNT_BIG(*) as Count, COUNT_BIG(Location) as LocationCount
FROM dbo.X
GROUP BY Id, Location
CREATE UNIQUE CLUSTERED INDEX CIX_X
ON vw(id)
Warning: Null value is eliminated by an aggregate or other SET operation.
INSERT INTO x VALUES (1,2) --this should bomb
Msg 2601 Level 14 State 1 Line 1
Cannot insert duplicate key row in object 'dbo.vw' with unique index 'CIX_X'. The duplicate key value is (1).
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
INSERT INTO x VALUES (1,NULL) --this should NOT bomb
Msg 2601 Level 14 State 1 Line 1
Cannot insert duplicate key row in object 'dbo.vw' with unique index 'CIX_X'. The duplicate key value is (1).
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
SELECT *
FROM vw
ORDER BY Id, Location
/*
This view should look like this after the insertion from the null:
Id Location Count LocationCount
1 1 2 1
2 NULL 1 0
3 2 1 1
*/
| Id | Location | Count | LocationCount |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | null | 1 | 0 |
| 3 | 2 | 1 | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.