November 3, 2011 at 12:58 am
All,
I would like to know the performance difference between PRIMARY KEY WITH CLUSTERED and PRIMARY KEY WITH NONCLUSTERED.
My job is : We will load a huge file(2 GB size)into the table. we have created a composite PRIMARY KEY with NONCLUSTERED (We just changed the option from clustered to nonclustered)
we have choosen PRIMARY KEY WITH NONCLUSTERED. is it better to choose PRIMARY KEY WITH NONCLUSTERED? will it impact the performance?
Inputs are welcome!
karthik
November 3, 2011 at 2:39 am
I think, if your most of the search(where query) is based on the primary key then Clustered index is advisable but if its getting updated very frequently and not being used in search condition frequently, then non-clustered is better.
PS:- not sure how accurate/correct is my thinking though
----------
Ashish
November 3, 2011 at 3:47 am
we just fetch the records from the table and show in the UI. We won't update the table.
my question is,
INSERT into PRIMARY with CLUSTERED
and
INSERT into PRIMARY with NONCLUSTERED
which one will be fast? which will take less I/O? which one should i choose?
I hope PRIMARY WITH CLUSTERED. It would be nice to hear some experts answer for my questions.
karthik
November 3, 2011 at 3:54 am
It depends. Really, this is not a question that has a hard and fast answer. Depends on data types, access patterns and a whole lot more.
A table should always have a clustered index. It doesn't have to be on the primary key though.
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
November 3, 2011 at 4:07 am
Insert into Non Clustered index column will be more faster as there is no need to change the physical sorting order of the data. Also more indexes on a table will slow down the Insert/update/delete operation.
One good clustered index is better as it will help in solving deadlock issues also.
November 3, 2011 at 6:01 am
again the confusion is if clustered index puts the data in the physical order?
November 3, 2011 at 6:45 am
dva2007 (11/3/2011)
again the confusion is if clustered index puts the data in the physical order?
No. It enforces the logical sort order. SQL will try and create the index so that logical sort order matches physical, but it's not guaranteed to be able to, and the index is unlikely to stay in the same physical order.
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
November 3, 2011 at 7:49 am
thanks gail, i understood this from our previous posts. People are still referencing this as physical order.
November 6, 2011 at 11:24 am
Ashish has a very good point. Also, clustered one is a "physical" order and sorted data and by far is faster than a non-clustered.. of course you know you cannot have more than ONE clustered one in a given table ..
Cheers,
John Esraelo
November 6, 2011 at 11:38 am
John Esraelo-498130 (11/6/2011)
Also, clustered one is <snip> by far is faster than a non-clustered..
Is it?
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
November 6, 2011 at 2:41 pm
Perhaps a single search on a single column with non-clustered index would be fast and as a matter of fact there are some recommendations on building a non-clustered index on top of a clustered indexed column. The notion is that the clustered index may contain the entire record physically stored and in order.. where a non-clustered is on one or more columns..
Cheers,
John Esraelo
November 7, 2011 at 2:47 am
Perhaps i'm being a bit daft here but seeing as we don't fully know the environment or the SQL going through it, wouldn't it be better for you to set up a quick test of both? Capture IO stats etc from the TSQL you throw at it and that will give you more of an idea as to which way to go.........
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
November 7, 2011 at 2:53 am
John Esraelo-498130 (11/6/2011)
The notion is that the clustered index may contain the entire record physically stored and in order.. where a non-clustered is on one or more columns..
Doesn't make it faster. Nonclustered indexes are also stored logically ordered and with include columns they too can contain non-key portions of the row.
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
November 7, 2011 at 6:01 am
The question I'd ask is not which is faster for the insert, but which supports the SELECT queries better. You're going to load once an hour/day/week/year and then in between times all the work will be doing SELECT statements. What do those SELECT statements look like? Are they bringing back a substantial number of columns from the table? Is the primary path to the data through the primary key? If the answer to both those questions is yes, then having that primary key clustered is likely to be the better solution for the SELECT statements, not the INSERTS.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply