I just need advice on how I am going to optimize my database.

Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN

I have that format of tables on my database. Each tables has 30-days of records with hundred thousands of records each data. Uploading of data to database is every morning. The data of yesterday will be uploaded.

The most common queries to run is grouped by Date or Date and Area or Date and Area and Block. Now, in order to make queries faster, I found out using indexes. I used indexes before. But that was when I am using a primary key. In this table, I didn't use primary key because it is totally unnecessary.

Now, I am really confused on how I am going to optimize this. Based on this reference on Column Considerations part, clustered index is not a good choice on Columns that undergo frequent changes. I am really confused.

Please help me if I need to use clustered index here or just non clustered or both.


2 Answers 2


You may believe primary key is unnecessary, but in my experience it is critical. You may never use it. But the database itself uses the primary key to determine PHYSICAL organization of the data. Correction: I have been corrected here - in SQL Server, the physical organization is based on the clustering index. By default a primary key IS the clustering index, so by adding a primary key, the clustered index is added and the rest of my point is still correct. But if you don't want to use an autonumber or some other primary key as the clustered index, you CAN still use some other column to cluster. You should definitely pick one, though. Check out this link: http://technet.microsoft.com/en-us/library/ms186342.aspx

Without a primary key clustered index you risk corruption of your data. And in some cases, when it seems like totally unnecessary, adding a primary key that you don't even search on will dramatically improve query performance.

here is an excellent description of the basic indexes in SQL Server. https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Based on the basics you have given us, I would say you need something like a primary key (auto number works) that will be your clustered index, then several nonclustered indexes (on all three of your search fields), which are the typical index when you want to search on various columns that are not necessarily the primary key.

If data will be edited a lot (like when a software application is used to manage data), a lot of indexes (i.e., nonclustered indexes) will slow down insert/update/delete activity, because the indexes are updated when the table is. But if your focus is on selecting data, indexes are critical.

If this is updated in batch daily and is not part of a software system, you could have a routine which drops the indexes, does a batch insert, then recreates the indexes.

But regardless - what you want for your search fields are nonclustered indexes. Clustered indexes "cluster" an entire record around the primary key (or whatever field is chosen as the cluster basis). Nonclustered indexes are what are created when you do indexes on any other field.

  • I see. Thanks for giving me the idea about primary key. I actually don't use them if I will not use them as reference to other tables. I should read more about that. And thanks for the answer. BTW, do I need to add Date, Area, and Block as Index Key Columns? Or Area and Block in Included Columns only> Please give me an idea. Commented Apr 5, 2014 at 4:42
  • if you will be doing a search on date, area, and block, then yes, index them. The best way to figure this out is to add the index, then run some queries and query plans to see which ways work best. Generally, though, if you will be doing grouping on columns OR if those columns will be used in joins or in your where clause, you should definitely index them.
    – user158017
    Commented Apr 5, 2014 at 4:43
  • Thanks a lot man. I will consider now redesigning the tables to include primary key since data is so critical. Thanks for the inputs. Commented Apr 5, 2014 at 4:48
  • 4
    To be very clear: the primary key in SQL Server DOES NOT determine the physical ordering of the table! It's the clustering key which does that. By default, the primary key is also the clustering key - but it doesn't have to be. The primary key is "just" a logical construct to uniquely and reliably identify every single row in your table.
    – marc_s
    Commented Apr 5, 2014 at 6:58
  • I stand corrected - thank you! I use Oracle more than SQL Server and did not understand the distinction. So the reason I recognized the issue in SQL Server is that without a primary key there usually is not a clustering key, thus essentially creating the same problem.
    – user158017
    Commented Apr 5, 2014 at 12:20

A primary key will enforce uniqueness in your data, which is almost always highly desired. One of the issues you'll run into if you allow yourself to have multiple copies of the same record is that any results you have will be skewed to the point of being unusable for many purposes. Also, this will happen, no matter how much you try to say it won't. You can either use a primary key or a unique index for this purpose, and a primary key in SQL Server, by default on a table without a clustered index, will create a unique clustered index.

The rule of "clustered index is not a good choice on columns that undergo frequent changes" is misquoted here. You appear to have records that have a static value for date, you're just adding more records every day that have a new date field.

The date field is actually perfect as the first key field of your clustered index for a couple reasons. It's added in order, so inserts will be quicker and cause less fragmentation. Also, you always query by it, so queries will be able to take advantage of it. Just add the key columns to the clustered index until you can make it unique and make it a unique index (primary key is optional, but preferred).

What the clustered index is going to do for you is actually store the data sorted by the key fields. A phone book is nothing more than a clustered index with the first key field of "Last Name", and you're able to find the person you're looking for very quickly. In your case, you're adding data according to the date making it easy for SQL Server to keep it sorted this way, then you're querying by the date making it so SQL Server can find the rows in question as quickly and efficiently as you look up names in the phone book.

To learn more about indexes, you can read my blog post on the topic here: http://simplesqlserver.com/2013/11/20/indexing-fundamentals/

  • +1. Another reason to cluster on the date field (first; you may have other fields subsequent in the clustering key) is that dates are typically used for ranges. A range lookup on a clustered index is very efficient. Commented Mar 12, 2015 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.