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

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

Indexing for View Containing UNION

Back to article view | Back to microsoft.public.sqlserver.programming

Contents

  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

#31348 — Indexing for View Containing UNION

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.

[toc] | [next] | [standalone]

#31349

Michael Cole (micheal.cole@invalid.com) writes:
> 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.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web