Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31348

Indexing for View Containing UNION

Show all headers | View raw

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.

Back to microsoft.public.sqlserver.programming | Previous | Next — Next in thread | Find similar

Thread

Indexing for View Containing UNION Michael Cole <micheal.cole@invalid.com> - 2019-06-24 18:05 +1000
  Re: Indexing for View Containing UNION Erland Sommarskog <esquel@sommarskog.se> - 2019-06-25 21:05 +0200

csiph-web