[dba-SQLServer] Performance of a unique index

jwcolby jwcolby at colbyconsulting.com
Wed Nov 14 07:19:50 CST 2007


An interesting explanation.  Thanks. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Maddison
Sent: Tuesday, November 13, 2007 7:53 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Performance of a unique index

John,

Hi John,

BOL doesn't help, mentions nothing about performance and unique indexes.

This guy says unique indexes improve performance.
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1187.entry

However, in general its unlikely you would be able to make all your indexes
unique ;-)

How good are you getting at reading execution plans?

cheers


Michael M

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, 14 November 2007 3:12 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Performance of a unique index

> The fastest index is the clustered index.

And in fact it missed the point of my question.  An index can be unique or
not unique.  I am asking specifically about these two things, not anything
else.  I have tables with DOZENS of indexes.  There can be only one
clustered index and I don't have any on these tables or it is on the PKID
(created by SQL Server when I created the PKID field and made it the PK). 

So I am really not interested in what is the FASTEST index in the universe,
simply in whether there is ANY difference in speed between a unique index
and a non unique index.  IOW should I make all of my indexes UNIQUE because
they are faster, or should I make all of my indexes NON UNIQUE because they
are fastest or should I not care because they are equal speed.

John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Tuesday, November 13, 2007 10:43 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Performance of a unique index

This is correct, and is due to the fact that creating a clustered index
physically resorts the table into the sequence specified by the key.
Although, JC, be aware that this is going to take a while, given the size of
your table.

A.

On 11/13/07, Robert L. Stewart <robert at webedb.com> wrote:
>
> The fastest index is the clustered index.
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list