Indexing for View Containing UNION

[Michael Cole]

Basic outline is that I wish to ensure uniqueness of data within a
table where some of the data can be considered as "shared".  Sample
data and code: -

[code]

CREATE TABLE [dbo].[TestSplitOwnership](
[Item] [int] NOT NULL,
[Owner] [char](1) NOT NULL,
CONSTRAINT [PK_TestSplitOwnership] PRIMARY KEY CLUSTERED
(
[Item] ASC,
[Owner] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (1, N'C')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (2, N'C')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (3, N'C')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (4, N'C')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (11, N'1')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (11, N'2')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (11, N'3')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (12, N'1')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (12, N'2')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (13, N'1')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (14, N'2')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (15, N'3')
GO

DROP VIEW [dbo].[vwTestSplitOwnership];
GO

CREATE VIEW [dbo].[vwTestSplitOwnership]
WITH SCHEMABINDING
AS
SELECT [TestSplitOwnership].[Item],
[TestSplitOwnership].[Owner]
FROM [dbo].[TestSplitOwnership]
WHERE [Owner] <> 'C'
UNION ALL
SELECT [TestSplitOwnership].[Item],
A.[Owner]
FROM [dbo].[TestSplitOwnership]
CROSS JOIN (
SELECT DISTINCT [Owner]
FROM [dbo].[TestSplitOwnership]
WHERE [Owner] <> 'C'
) AS A
WHERE [TestSplitOwnership].[Owner] = 'C';
GO

CREATE UNIQUE CLUSTERED INDEX [PK_TestSplitOwnership]
ON [dbo].[vwTestSplitOwnership] (
[Item] ASC,
[Owner] ASC
)
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY];
GO


[/code]



The intent is "C" is not an actual owner - that all the records with
"C" as their owner are effectively shared for all other owners, and
this we need to ensure uniqueness of ownership based on the query in
the view, e.g., any "owned" items can not be the same as a "shared"
item.  Issue is that I cannot put a PK on the view as it contains the
UNION.I cannot change the data structure, but any suggestions as to how
I could implement such a constraint would be welcome.

[Erland Sommarskog]

Post by Michael Cole
The intent is "C" is not an actual owner - that all the records with
"C" as their owner are effectively shared for all other owners, and
this we need to ensure uniqueness of ownership based on the query in
the view, e.g., any "owned" items can not be the same as a "shared"
item.  Issue is that I cannot put a PK on the view as it contains the
UNION.I cannot change the data structure, but any suggestions as to how
I could implement such a constraint would be welcome.

Not only is the UNION preventing you indexed view, the subquery does too,
and I don't thinkt the CROSS APPLY is good.

I think you will need to use triggers to enforce this.