Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sachingovekar
Posting Yak Master
101 Posts |
Posted - 2012-12-21 : 03:22:11
|
Hi,I have a table which has millions of rows.1. The table has no keys nor any indexes.2. There is no column in the table with unique data.3. The table is used in a join select query for eg: machinename is used which has data but not uniqueI want to create an index on machinename:Question?1. I know I have to create a non clustered index, however which will be the best option :1a) introduce a identity column and create a clustered index on it and then create a non clustered index on machinename column.2a) or just create a non clustered index on machinename? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 04:00:05
|
seehttp://technet.microsoft.com/library/Cc917672#EFAA------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-21 : 09:23:57
|
Yes table should have clustered otherwise table will be considered as heap and your NC index will point to RID to fetch the informations. If any of column is not unique I would suggest you have primary key in table. Also does the combination of 2 columns make it unique. Otherwise you can have Covering index or Non-clustered index with include for machine name so you get Index seek and seek predicate which is good enough for performance.Check how indexes is being used with execution plan, that is best way to implement indexes. |
|
|
sachingovekar
Posting Yak Master
101 Posts |
Posted - 2012-12-21 : 10:05:34
|
Thanks Visakh and Sodeep |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 10:15:31
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-12-21 : 11:52:07
|
In re: "I know I have to create a non clustered index"Why do you need to create a non-clustered index? A clustered index does not need to be unique; it just typically is.=================================================Hear the sledges with the bells - silver bells! What a world of merriment their melody foretells! |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2012-12-21 : 17:18:27
|
It depends.If you need an index only on machinename, then just create a clustered index on that column.CREATE CLUSTERED INDEX <index_name> ON dbo.<table_name> ( machinename )If you need to index other columns, to prevent SQL from having to generate unique numbers itself, you can an identity column and then a clustered index on ( machinename, identity_column ). |
|
|
|
|
|