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 unique
I 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?
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.
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!
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 ).
index creation
Posted - 12/21/2012 : 03:22:11
