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 vwNonNull WITH SCHEMABINDING AS
SELECT Id, Location, COUNT_BIG(*) as Count
FROM dbo.X
WHERE Location IS NOT NULL
GROUP BY Id, Location
CREATE VIEW vwNull WITH SCHEMABINDING AS
SELECT Id, Location, COUNT_BIG(*) as Count
FROM dbo.X
WHERE Location IS NULL
GROUP BY Id, Location
CREATE UNIQUE CLUSTERED INDEX CIX_X
ON vwNonNull(id)
CREATE UNIQUE CLUSTERED INDEX CIX_X
ON vwNull(id)
CREATE VIEW vw AS
WITH CTE AS (
SELECT *, Count as LocationCount
FROM vwNonNull
UNION ALL
SELECT *, 0 as LocationCount
FROM vwNull
)
SELECT
Id
,AVG(Location) as Location
,SUM(Count) as Count
,SUM(LocationCount) as LocationCount
FROM CTE
GROUP BY Id
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.vwNonNull' 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
1 rows affected
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 | 2 | 1 |
| 2 | null | 1 | 0 |
| 3 | 2 | 1 | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.