| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-06-12 : 05:45:59
|
| I have a Primary Key on columns [Brand], [Code]We have lots of lookups which doWHERE Brand = 'X' AND Code = 'Y' AND IsActive = 1Is there any benefit NOT having a PK but instead having a Clustered Index on [Brand], [Code] with an INCLUDE for [IsActive]? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-12 : 07:48:21
|
| Only if those are the only columns you'll be SELECTing. If there are other columns in the SELECT list it will do a lookup or a clustered index scan. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-12 : 09:10:54
|
| A clustered index cannot have include columns. Which makes sense when you consider that the clustered index has the actual data pages at the leaf level and hence technically includes every single column in the table.Depending on what you lookup, I might recommend a nonclustered index on (Brand, Code, IsActive) and include whatever column you're looking up. Doesn't strike me as a good candidate for the clustered index.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-12 : 09:21:37
|
Ah, good point.<Looks at actual query>Okay, I wasn't quite as gormless as I thought! but I should have said "JOIN" and "ON" rather than "WHERE"FROM OtherTable AS O JOIN ThisTable AS T ON T.Brand = O.Brand AND T.Code = O.Code AND T.IsActive = 1 this will retrieve multiple rows from ThisTable, matching the rows in OtherTableSo presumably, on that basis, the INCLUDE [IsActive] column is beneficial to reduce the number of lookups - and thus a Clustered index instead of a PK is fine? No adverse side effects of not having a PK? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-12 : 11:50:55
|
| I still wouldn't recommend a clustered index here. Looks too wide. What's the select clause? That'll determine any needed include columns. Why do you want to make IsActive an include column? It's part of the predicate, it should be part of the key. It's columns in the select that should be included.The table should still have a primary key on one of the candidate key columns. Whether the pk is clustered or nonclustered is absolutely irrelevant.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-13 : 05:41:52
|
| Thanks Gail. I hadn't realised that INCLUDE not permitted in Clustered Index, but as you say it makes sense.The primary key is Brand + Code. If I add IsActive to the PK in there it might allow duplicate of Brand + Code - one active, one inactive. There are several columns in the SELECT, I don't think there is any gain in making them INCLUDE columns ... they are typically most of the columns in the table anyway.I was looking at some queries yesterday and there are lots of JOINs to this table, and all of them have AND IsActive = 1 - so I was thinking that adding IsActive to the index would allow Inactive rows to be rejected "sooner".Maybe this is as efficient as it can be; I expect that a "mature" system will have about 50% of rows marked as Inactive, certainly no more than that. A new system will have 0% rows marked Inactive. Most are probably 10% - 25% Inactive ("Inactive" means [IsActive] = 0)(Recently come from SQL 2000 where INCLUDE was not available ... hence I'm on a bit of a mission looking for places to change TEXT to VARCHAR(MAX), and add INCLUDE columns, use Filtered Indexes on columns where many rows have NULL values, and the like.) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-13 : 06:06:56
|
| Leave the PK where it is. Try adding a nonclustered index on (Code, Brand, IsActive) or (Code, Brand) where Isactive=1See if SQL will use that, it may not if the index is not selective. If it doesn't, then that cluster is about as good as you'll be able to get.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-13 : 06:20:36
|
I was being stingy! and not wanting to make an index on something that was the same as PK bar 1 extra bitBut you are right, a test costs me nothing compared to the possible gain.I'll report back  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-13 : 08:44:22
|
Secondary Index wasn't used. Fiddled about with all sorts of variations ... none improved things. But, typically  , looking at the whole SProc that closely - Logical I/Os's and Query Plan for each SELECT, brought to light some other things, and I've added another index which does make a difference.One thing though:I wanted to add indexCREATE NONCLUSTERED INDEX MyIndex ON dbo.MyTable( LookupColumn, Brand, IsActive) WHERE LookupColumn IS NOT NULL because [LookupColumn] is NULL on more than 50% of rows, but the index was unused in JOINs withFROM dbo.OtherTable AS O JOIN dbo.MyTable AS T ON T.LookupColumn = O.OtherColumn whereas if I left the Filter off MyIndex then it was used. Bit of a shame that the parser doesn;t recognise that ... but I may be expecting a bit much?!If time I'll try changing the JOIN to be JOIN dbo.MyTable AS T ON (T.LookupColumn IS NOT NULL AND T.LookupColumn = O.OtherColumn) to see if that triggers use of the Filtered index |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-13 : 09:45:30
|
| The filter on the index has to match exactly to a where clause predicate to be used. If nc index wasn't used, then it's not selective enough. I suggest leave it, the cluster is almost adequate for that filter and the NC would be only a small benefit. There are probably better places to optimise.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-13 : 10:06:08
|
| Yup, I agree. Its been a useful exercise for me though, thanks for the pointers. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-13 : 10:41:15
|
FWIW if I explicitly check for NOT NULL in the JOIN: JOIN dbo.MyTable AS T ON (T.LookupColumn IS NOT NULL AND T.LookupColumn = O.OtherColumn) then my filtered index is used and I get the exact same query plans as having an UNfiltered index and leaving the additional test out (except that I have an added "T.LookupColumn IS NOT NULL" in the Query Plan of course)Given that 50% of my rows have T.LookupColumn IS NOT NULL I think I'll go with a Filtered Index. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-13 : 13:30:27
|
| Before you do that, do some performance tests, CPU, reads, duration and make sure that the query with the additional predicate is faster (or at least not slower)--Gail ShawSQL Server MVP |
|
|
|
|
Primary Key vs. Clustered Index