January 6, 2018 at 8:18 am
Hello,
While I've searched this topic with some hits, I've never been able to fully digest this to my satisfaction. My situation is this: I have a database with a table that I use a composite of two fields (Date and StoreNumber) to make the primary key since the combo of the two will always be unique. Let's say the other fields are int fields.... WidgetsDelivered, WidgetsSold, WidgetDefects blah blah blah. My question is this: If I do searches and run queries off of each of these two fields (Date and StoreNumber), would I still need to create separate indexes on them or would they already be indexed since they are the pk?
Thank you in advance for any help
January 6, 2018 at 9:16 am
John524 - Saturday, January 6, 2018 8:18 AMHello,
While I've searched this topic with some hits, I've never been able to fully digest this to my satisfaction. My situation is this: I have a database with a table that I use a composite of two fields (Date and StoreNumber) to make the primary key since the combo of the two will always be unique. Let's say the other fields are int fields.... WidgetsDelivered, WidgetsSold, WidgetDefects blah blah blah. My question is this: If I do searches and run queries off of each of these two fields (Date and StoreNumber), would I still need to create separate indexes on them or would they already be indexed since they are the pk?
Thank you in advance for any help
Use them in the same order as the composition of the PK, no need for a separate index
😎
January 6, 2018 at 9:46 am
Thank you Eirikur
When you say same order of the composition of the PK, you mean it would be Date (first) and then StoreNumber (second)? While I was awaiting a reply to this, I read somewhere that the composite could do searches on both together, or Date (since that's first) but that I would need to create an additional index for StoreNumber searches only.
Again, thank you for your help!
January 6, 2018 at 1:43 pm
John524 - Saturday, January 6, 2018 9:46 AMThank you Eirikur
When you say same order of the composition of the PK, you mean it would be Date (first) and then StoreNumber (second)? While I was awaiting a reply to this, I read somewhere that the composite could do searches on both together, or Date (since that's first) but that I would need to create an additional index for StoreNumber searches only.
Again, thank you for your help!
That is correct, if the Store Number is the second column of the primary key/clustered index, then you need a separate index if you need to search on that key. Otherwise you'll get a scan, not a seek of the index, mind you, if the unique entries aren't that many, i.e. the selectivity is low, you'll get a scan anyway
😎
January 6, 2018 at 3:56 pm
Good stuff. Thank you kindly!
January 8, 2018 at 8:31 am
John524 - Saturday, January 6, 2018 9:46 AMThank you Eirikur
When you say same order of the composition of the PK, you mean it would be Date (first) and then StoreNumber (second)? While I was awaiting a reply to this, I read somewhere that the composite could do searches on both together, or Date (since that's first) but that I would need to create an additional index for StoreNumber searches only.
Again, thank you for your help!
If you have two queries in your application and one of them has where condition using only the StoreNumber , then yes you need a separate index for it .
The composite key index would suffice the searches on Date and StoreNumber , provided they are in the same order.
January 8, 2018 at 9:42 am
You may or may not need to create another index for StoreNumber first. It's possible SQL would never use such an index even if you created it.
If you will always search by ( Date, StoreNumber ) or by ( StoreNumber ) alone, then uniquely cluster the table by ( StoreNumber, Date ) rather than the other way around. You make it a PK as well if you want, but that's not actually required for optimum searching, only that it be uniquely clustered on that key.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 9, 2018 at 9:53 am
Indexes in transact SQL are built in the order in which the columns are declared. This means the first column will be the value that is used to create the tree structure. The following columns will be indexed within the preceding columns.
However, there's a couple of other things you need to remember. First of all, look up the optional include clause. I usually explain this as the stuff in the include columns is like carry-on luggage; it doesn't count against your baggage allowance 🙂
Then, if you need to port this application, indexing was never part of the ANSI standard. Every product will be a little bit different, and occasionally radically different. For example, Teradata uses hashing rather than tree structured indexes, so the order of the columns doesn't matter. There hashing algorithm gives you retrieval in one probe over 98% of the time. This is why it's used for large amounts of data. Columnar databases also have a different structure and tricks for improving their performance
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply