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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 index creation

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 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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 04:00:05
see

http://technet.microsoft.com/library/Cc917672#EFAA

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-12-21 : 10:05:34
Thanks Visakh and Sodeep
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 10:15:31
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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!
Go to Top of Page

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 ).
Go to Top of Page
   

- Advertisement -