We have these four tables:
Store (
row bigint,
id uniqueidentifier,
name varchar
)
Products (
row bigint,
id uniqueidentifier,
storeID uniqueidentifier,
productname varchar
)
Customer (
row bigint,
id uniqueidentifier,
storeID uniqueidentifier,
fName,
lName,
email
)
orders (
row bigint,
id uniqueidentifier,
store_ID uniqueidentifier,
cust_id uniqueidentifier,
prod_id uniqueidentifier,
quantity bigint,
date datetime
)
After looking at the current design, since row is just counting 1 up to the row number that was last inserted, I don't see any need to index this column. The row column is present on all the table and hard to image this ever showing up in the where clause to justify any indexing strategy column.
However, all the tables have an ID column which will uniquely identify that row item, so this will be a highly random value and not ordered, so this column will make a great column to be a private key for the table, thus a clustered index on this column along with the name associated with that row. So the indexing will look like this for each table:
- Proposed Solution 1
create clustered index on table STORE on column (id,name)
--> This will speed up queries on finding the ID on a given store name or vice-versa...
create clustered index on table Products on column (id,store_ID, productname )
--> Easy to image a lot of queries trying to find a product id for a product name or find a name of what a product ID is? But I'm not sure if I should be using a primary key for this combination?
create clustered index on table Customer on column (id,firstname, lastname )
--> I imagine there will be a lot of queries taking a name (first, last) to find the ID so we can join the tables together to find relevant data. So I was guessing a primary key for these columns or a cluster index. Am I thinking right on this?
create clustered index on table orders on column (id,cust_id, store_id, prod_id )
--> This table is the most interesting because it has id columns that link it to the other three. This one is very perplexing. I would think that the first index to be listed would be the order ID (which is the same as the others above) since I see this table being in a lot of queries for a given order ID
or storeID
or cust_id
. Since this table will most likely have all of those IDs in a join statement, I would think that creating a primary key with all ID values is a good idea. But is it?
However, since ID is generated by the newid()
function, it would be a random value and the table would have to be re-order all the time, so now I'm thinking that ROW is a better candidate for a primary key or clustered index.
- Proposed Solution 2
Primary key on Row column for all tables store, product, customer and Orders, the only row to be the primary key is the row column because rules state that you should limit the # of columns in a primary key. Plus the data will be stored by ascending order since as the row is inserted, the row value is incremented by 1 so the data will be stored on the harddrive in ascending order so row is the right choice for a PK.
non clustered index on Store table on (id,name)
non clustered index on Products table on (id,store_ID, productname )
non clustered index on Customer table on (id,firstname, lastname )
non clustered index on Orders table on (id,cust_id, store_id, prod_id )
I believe that solution 1 was really bad since the primary key was on rows that are random in nature (and the way data is stored on disk) but solution 2 is getting closer to a better solution.
- Proposed Solution 3
Well after thinking about proposal #2, it doesn't make any sense just creating a PK on row field because row will most likely never show up in a where clause, so I believe that the PK should now include the ID field like so:
create PrimaryKey on store table on (Row, ID) field
create PrimaryKey on customer table on (Row, ID) field
create PrimaryKey on product table on (Row, ID) field
create PrimaryKey on orders table on (Row, ID) field
I believe this will help SQL translate an ID to a location on hard drive, since the row number is sequential in nature and the data is stored in order on the hard drive.
Now to help improve the queries that will be joining the above tables together to find data, we will use non-clustered indexes, here I'm uncertain if I go over board with the number of columns in the index.
create nonclustered index on store table on (ID, Name)
create nonclustered index on customer table on (ID, store_id,firstName, lastName)
create nonclustered index on products table on (ID, store_id, Name)
create nonclustered index on orders table on (ID, store_id)
create nonclustered index on orders table on (ID, cust_id, product_id)
create nonclustered index on orders table on (ID, quantity)
I came up with queries that I would likely use on this table, so based on those query's where clause, I came up with several indexes on that table. I may have went over board here, so please let me know if you have any suggestions.
Please do comment.
v/r
id
columns the primary keys? Are there other unique constraints? What are the foreign keys? Providing actualCREATE TABLE
statements would help others more. – ypercubeᵀᴹ Aug 29 '13 at 10:36