SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 index creation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sachingovekar
Yak Posting Veteran

99 Posts

Posted - 12/21/2012 :  03:22:11  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/21/2012 :  04:00:05  Show Profile  Reply with Quote
see

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

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

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/21/2012 :  09:23:57  Show Profile  Reply with Quote
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
Yak Posting Veteran

99 Posts

Posted - 12/21/2012 :  10:05:34  Show Profile  Reply with Quote
Thanks Visakh and Sodeep
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/21/2012 :  10:15:31  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 12/21/2012 :  11:52:07  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
410 Posts

Posted - 12/21/2012 :  17:18:27  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000