SQL Server 2017

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.