January 8, 2006 at 7:53 pm
I am constructing a huge table which will have millions of datasets. I have a column which will be a primary key(say column A) and two other columns(say column B and column C) which are null in 10-15 tuples and else unique in rest of the rows. I need to create an index using Sql Server Enterprise Manager. There should be two different indexes (1) Combination of column A (primary key) and column B and (2) Combination of column A (primary key) and column C. I read it in theory.
How to practically implement it in the Table design using Enterprise Manager? Your help will be greatly appreciated. Thanks
January 9, 2006 at 10:51 am
If I recall correctly, Ent Mgr has a button in the Table GUI to manage indexes. Create a new one, choose the appropriate columns and sort order, give it a name. Create another index, repeat.
For PK's, choose the column(s) you want to use as a PK (you can use CTRL+click to select multiples) and hit the button that looks like a key.
Be sure about clustering as PK's get clustered by default if there is no other clustered index. Not always a bad thing, but we've been bitten by this before with warehouses where someone clustered on customer id.
-Pete
January 9, 2006 at 7:52 pm
Thanks for your reply!!
I have one more doubt. My primary key (one column only) will be my clustered index. While creating new indexes (2 more indexes in my case), do I need to use a composite key of a combination of PK and other key. Or should new indexes be non-clustered and just consist of one column only.
January 9, 2006 at 8:36 pm
Depends on what you're planning to do. I would plan the clustered index carefully as that defines the physical order of the table. If you'll be doing a lot of non-sequential writes/updates on this, it's probably not the best choice for the clustered index.
As for the other indexes - think about what you'll be searching by the most or what columns will be returned. Sometimes a covered index (pull all columns used for a query) makes sense, but generally for small sets of columns. Sometimes a small index (1 or two columns) makes sense, but remember that SQL reads those columns in that order. So if you have an index on OrderID, DateOfOrder and always search by DateOfOrder, you'll never use this index. However if your index is DateOfOrder,OrderID you are more likely to hit it.
There's a lot of planning that goes into an indexing strategy and it's hard to give a lot of advice without knowing the database structure, number of rows, width of rows and columns in the rows, how the DB will be used, etc.
I'd start with single-column indexes and perhaps get some traces going to see how people use the database. Run that through the Database/Query tuning advisor to get some recommendations and see how they'll affect your system. Remember that indexes have some overhead to maintain as well. I'd recommend a good book on SQL Server to get some overviews, even some of the older ones could help you with planning this carefully.
-Pete
January 9, 2006 at 11:23 pm
The best choice for a clustered index is a column that is narrow, evver increasing and unique. The closer you get to that, the better. The PK is not always the best choice for the cluster.
A non-clustered index always contains within it the cluster column. It's needed to do a lookup back to the table.
If you have a table called orders, with a clustered index on orderdate and a non clustered index on productid, then the nc index contains both productid and orderdate.
I'd also recomend finding a good book on SQL, specifically db design and performance. Additionally, you can post your table design here, with some of the common queries and ask for advice. From personal experience, designing effective indexes for a very large table (20 million records +) is not easy.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply