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