SQL Server 2008 R2 : A Possible indexing strategy for the given schema?

Question

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

Answer 1

The way this is designed you only have suboptimal choices. Random GUIDs are not well suited as clustered index keys, since they are neither small (which affects the size of all secondary indexes) nor sequential (unless you can use NEWSEQUENTIALID()) which leads to index fragmentation, which leads to wasted space, slower insert performance and slower query performance through more I/O.

On the other hand, if your normalized tables are linked by such a GUID then each join depends on them and you will have to bite the bullet and use them as primary keys with clustered index anyway. Just create the PRIMARY KEY constraint and the clustered index in separate steps so you can define PAD_INDEX = ON and FILLFACTOR=50 to slow down the fragmentation somewhat. Still, expect to do regular, expensive index REBUILDs to reduce the inevitable fragmentation.

Your secondary indexes must not start with the id, because that renders them useless! Imagine a telephone book, where each entry is given a random or running id, then the phone book is sorted by that id plus the name. Have fun searching a given name in that. A useable index must start with the column that is used in the where- or join clause.

So, with the clustered indexes created so far you cover queries of the type

SELECT p.productname, s.name as StoreName 
FROM Products p
INNER JOIN Store s ON p.storeid = s.id

The query runs through the products, can efficiently look up the store ids and has immediate access to the store name, since the store id index is clustered.

Now you want to do this:

SELECT p.productname, s.name as StoreName 
FROM Products p
INNER JOIN Store s ON p.storeid = s.id
WHERE p.productname LIKE 'A%'

For this you need a nonclustered index with just productname as the key column (and optionally storeid as included column, if you do frequent range searches on productname).

OK, what about the reverse case?

SELECT p.productname, s.name as StoreName 
FROM Store s
INNER JOIN Products p ON p.storeid = s.id
WHERE s.name = 'My little cornershop'

For this, you need two additional indexes: One nonclustered on store with the name column and one nonclustered on products with storeid as the column. SQL Server can efficiently find the store record (expecting only one record), then through the second index can find all product entries for this store (still only a few compared to all entries in product), then for each of these products go through the clustered index (the clustered index key is automatically part of each nonclustered index) to get to the productname column.

I hope you see the pattern here. Create a nonclustered index for each column that gets queried with a high selectivity (meaning that only a small subset of all the rows will be selected).

The row-columns are completely useless in this scenario, just drop them to save space.

Using client generated GUIDs is attractive from the client point of view. You can create coherent datasets (such as a new customer including his first order) and push them to the database without caring for the correct INSERT order and without having to read database generated ids afterwards to update your object model. But you pay a nontrivial performance price for this when it comes to getting the data back from the database, as I hopefully made clear above. The large primary key (8 bytes) gets added to each nonclustered index and blows up its size, and you get a heavily fragmented clustered index which is never good.

Using IDENTITY values for primary keys has disadvantages at INSERT time, but pays off every time after that.

Answer 2

So you basically have two unique IDs (surrogate IDs even) in each of your table? why? and then you create (in sol 3) a PK using both columns? a uniqueidentifier (16-byte binary values) combined with a bigint with storage size of 8 bytes? both being unique anyway? .. I don't understand this.

From your description you don't seem to require a Global Unique Identifier - i would suggest: - Just drop your row field, change the datatype of your ID to bigint (if you expect up to 9,223,372,036,854,775,807 rows - including the ones you would delete, restore, what have you - I hope this will be enough for your requirements ;)) - Add PK on ID (surrogate PK) - Create a bunch of indexes on each table depending on the queries you expect to run against your DB, and think about the order of the columns in your indexes, and which columns are used in your indexes ... don't forget to use the [include]-clause when you are creating your nonclustered indexes to cover more queries (i'm looking at your queries related to your customer table for example).

Before you do all this, measure your performance (CPU, cache etc) and then measure again after your changes ;)