October 8, 2015 at 4:24 am
I have many lookup tables - parent table has a ChildID and JOINs the Child Table to get a column containing a descriptive value.
Primary Key is the ChildID.
These Child / Lookup Tables have 99% (or more!) reads. WRITES are extremely rare.
1) Is there any benefit in having a Non-clustered index on ChildID with an INCLUDE for the Description column?
2) what an index with a FILTER in situations where there is a need to exclude ChildTable rows marked as "Disabled"?
My sample code only has 20 rows, so not sure whether I would be able to see any tangible difference. STATISTIC Scans / etc. look the same, but the Query Plan is clearly using the non-clustered index as appropriate.
-- Create Table sample
CREATE TABLE dbo.TEMP_Lookup
(
LookupID int NOT NULL
, LookupDescription varchar(50) NULL
, LookupIsEnabled bit NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.TEMP_Lookup ADD CONSTRAINT
PK_TEMP_Lookup PRIMARY KEY CLUSTERED
(
LookupID
) WITH( PAD_INDEX = OFF, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE dbo.TEMP_Master
(
MasterID int NOT NULL
, MasterLookupID int NOT NULL
, MasterCol1 varchar(50) NULL
, MasterCol2 bit NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.TEMP_Master ADD CONSTRAINT
PK_TEMP_Master PRIMARY KEY CLUSTERED
(
MasterID
) WITH( PAD_INDEX = OFF, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT INTO dbo.TEMP_Lookup(LookupID, LookupDescription, LookupIsEnabled)
SELECT101, 'Desc101', 1 UNION ALL
SELECT102, 'Desc102', 0 UNION ALL
SELECT103, 'Desc103', 1 UNION ALL
SELECT104, 'Desc104', 0 UNION ALL
SELECT105, 'Desc105', 1 UNION ALL
SELECT106, 'Desc106', 0 UNION ALL
SELECT107, 'Desc107', 1 UNION ALL
SELECT108, 'Desc108', 0 UNION ALL
SELECT109, 'Desc109', 1 UNION ALL
SELECT110, 'Desc110', 0 UNION ALL
SELECT111, 'Desc111', 1 UNION ALL
SELECT112, 'Desc112', 0 UNION ALL
SELECT113, 'Desc113', 1 UNION ALL
SELECT114, 'Desc114', 0 UNION ALL
SELECT115, 'Desc115', 1 UNION ALL
SELECT116, 'Desc116', 0 UNION ALL
SELECT117, 'Desc117', 1 UNION ALL
SELECT118, 'Desc118', 0 UNION ALL
SELECT119, 'Desc119', 1 UNION ALL
SELECT120, 'Desc120', 0
INSERT INTO dbo.TEMP_Master(MasterID, MasterLookupID, MasterCol1, MasterCol2)
SELECT201, 101, 'Desc201', 1 UNION ALL
SELECT202, 102, 'Desc202', 0 UNION ALL
SELECT203, 103, 'Desc203', 1 UNION ALL
SELECT204, 104, 'Desc204', 0 UNION ALL
SELECT205, 105, 'Desc205', 1 UNION ALL
SELECT206, 106, 'Desc206', 0 UNION ALL
SELECT207, 107, 'Desc207', 1 UNION ALL
SELECT208, 108, 'Desc208', 0 UNION ALL
SELECT209, 109, 'Desc209', 1 UNION ALL
SELECT210, 110, 'Desc210', 0 UNION ALL
SELECT211, 111, 'Desc211', 1 UNION ALL
SELECT212, 112, 'Desc212', 0 UNION ALL
SELECT213, 113, 'Desc213', 1 UNION ALL
SELECT214, 114, 'Desc214', 0 UNION ALL
SELECT215, 115, 'Desc215', 1 UNION ALL
SELECT216, 116, 'Desc216', 0 UNION ALL
SELECT217, 117, 'Desc217', 1 UNION ALL
SELECT218, 118, 'Desc218', 0 UNION ALL
SELECT219, 119, 'Desc219', 1 UNION ALL
SELECT220, 120, 'Desc220', 0
-- ***** TEST 1 *****
SELECTMasterID, MasterCol1, MasterCol2, LookupDescription
FROMdbo.TEMP_Master
JOIN dbo.TEMP_Lookup
ON LookupID = MasterLookupID
-- NOT IN THIS TEST AND LookupIsEnabled = 1
/*
-- ***** TEST 1 *****
Table 'TEMP_Lookup'. Scan count 0, logical reads 40
Table 'TEMP_Master'. Scan count 1, logical reads 2
|--Nested Loops(Inner Join, OUTER REFERENCES: ([MyDB].[dbo].[TEMP_Master].[MasterLookupID]))
|--Clustered Index Scan(OBJECT: ([MyDB].[dbo].[TEMP_Master].[PK_TEMP_Master]))
|--Clustered Index Seek(OBJECT: ([MyDB].[dbo].[TEMP_Lookup].[[highlight="#ffff11"]PK_TEMP_Lookup[/highlight]])
, SEEK: ([MyDB].[dbo].[TEMP_Lookup].[LookupID]=[MyDB].[dbo].[TEMP_Master].[MasterLookupID])
ORDERED FORWARD)
*/
-- ***** TEST 2 *****
SELECTMasterID, MasterCol1, MasterCol2, LookupDescription
FROMdbo.TEMP_Master
JOIN dbo.TEMP_Lookup
ON LookupID = MasterLookupID
[highlight="#ffff11"]AND LookupIsEnabled = 1[/highlight]
/*
-- ***** TEST 2 *****
Table 'TEMP_Lookup'. Scan count 0, logical reads 40
Table 'TEMP_Master'. Scan count 1, logical reads 2
|--Nested Loops(Inner Join, OUTER REFERENCES: ([MyDB].[dbo].[TEMP_Master].[MasterLookupID]))
|--Clustered Index Scan(OBJECT: ([MyDB].[dbo].[TEMP_Master].[PK_TEMP_Master]))
|--Clustered Index Seek(OBJECT: ([MyDB].[dbo].[TEMP_Lookup].[[highlight="#ffff11"]PK_TEMP_Lookup[/highlight]])
, SEEK: ([MyDB].[dbo].[TEMP_Lookup].[LookupID]=[MyDB].[dbo].[TEMP_Master].[MasterLookupID])
, [highlight="#ffff11"]WHERE: ([MyDB].[dbo].[TEMP_Lookup].[LookupIsEnabled]=(1))[/highlight]
ORDERED FORWARD)
*/
CREATE UNIQUE NONCLUSTERED INDEX IX_TEMP_LookupDescription1 ON dbo.TEMP_Lookup
(
LookupID
)
INCLUDE
(
LookupDescription
)
-- NOT IN THIS TEST:WHERE LookupIsEnabled = 1
WITH( FILLFACTOR = 100, 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
-- ***** TEST 3 *****
SELECTMasterID, MasterCol1, MasterCol2, LookupDescription
FROMdbo.TEMP_Master
JOIN dbo.TEMP_Lookup
ON LookupID = MasterLookupID
-- NOT IN THIS TEST AND LookupIsEnabled = 1
/*
-- ***** TEST 3 *****
Table 'TEMP_Lookup'. Scan count 0, logical reads 40
Table 'TEMP_Master'. Scan count 1, logical reads 2
|--Nested Loops(Inner Join, OUTER REFERENCES: ([MyDB].[dbo].[TEMP_Master].[MasterLookupID]))
|--Clustered Index Scan(OBJECT: ([MyDB].[dbo].[TEMP_Master].[PK_TEMP_Master]))
|--Index Seek(OBJECT: ([MyDB].[dbo].[TEMP_Lookup].[[highlight="#ffff11"]IX_TEMP_LookupDescription1[/highlight]])
, SEEK: ([MyDB].[dbo].[TEMP_Lookup].[LookupID]=[MyDB].[dbo].[TEMP_Master].[MasterLookupID])
ORDERED FORWARD)
*/
CREATE UNIQUE NONCLUSTERED INDEX IX_TEMP_LookupDescription2 ON dbo.TEMP_Lookup
(
LookupID
)
INCLUDE
(
LookupDescription
)
WHERE LookupIsEnabled = 1
WITH( FILLFACTOR = 100, 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
-- ***** TEST 4 *****
SELECTMasterID, MasterCol1, MasterCol2, LookupDescription
FROMdbo.TEMP_Master
JOIN dbo.TEMP_Lookup
ON LookupID = MasterLookupID
[highlight="#ffff11"]AND LookupIsEnabled = 1[/highlight]
/*
-- ***** TEST 4 *****
Table 'TEMP_Lookup'. Scan count 0, logical reads 40
Table 'TEMP_Master'. Scan count 1, logical reads 2
|--Nested Loops(Inner Join, OUTER REFERENCES: ([MyDB].[dbo].[TEMP_Master].[MasterLookupID]))
|--Clustered Index Scan(OBJECT: ([MyDB].[dbo].[TEMP_Master].[PK_TEMP_Master]))
|--Index Seek(OBJECT: ([MyDB].[dbo].[TEMP_Lookup].[[highlight="#ffff11"]IX_TEMP_LookupDescription2[/highlight]])
, SEEK: ([MyDB].[dbo].[TEMP_Lookup].[LookupID]=[MyDB].[dbo].[TEMP_Master].[MasterLookupID])
ORDERED FORWARD)
*/
DROP TABLE dbo.TEMP_Lookup
GO
DROP TABLE dbo.TEMP_Master
GO
October 8, 2015 at 4:30 am
The QO will chose the smallest index, so if there's a nonclustered index that's 1 byte smaller than the clustered, that'll be the one chosen. Whether it makes sense to have such a nonclustered index is another matter. Usually it isn't. There are cases where having such a nonclustered index is useful, generally when the table is huge, the query needs to just access one column and the query is incredibly time-sensitive, in that it absolutely may not take 1ms longer than necessary.
As for a filtered index, test and see, but again you're probably not going to see a huge benefit on small tables. Also consider the problems of matching queries to filtered indexes http://sqlinthewild.co.za/index.php/2011/11/09/sql-university-advanced-indexing-filtered-indexes-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2015 at 5:00 am
Thanks Gail. Sounds like more-trouble-than-it-is-worth 🙂
In the main all - but I ought to make a Belt & Braces test to be sure ... - my Lookup Tables are pretty small.
Today's panic came about because there were two unique columns (one was the clustered index) and the Lookup was on the other column which had overlooked being indexed, so I set about doing a series of tests (once I'd created the non clustered unique index with both INCLUDE and FILTER) to see whether there appeared to be any benefit for a covering index on the Clustered Index key column(s).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply