Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tips On Optimizing Index Performance Expand / Collapse
Author
Message
Posted Tuesday, March 08, 2005 10:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 28, 2005 7:56 AM
Points: 27, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kThaker/tipsonoptimizingindexperformance.asp
Post #166439
Posted Wednesday, March 16, 2005 1:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102

Good article, but I'd have to disagree with the following:

"Non-Clustered indexes are better for singleton and individual row lookups. "

Non-Clustered indexes really aren't any "better", they costs virtually the same (an index seek vs and clustered index seek) during selects.  And when ordering is necessary they can drastically improve performance.

One of the subtle things about SQL is how often is uses ordering.

Also, clustered indexes can help the compiler do optimal joins.

--------------------------------------------------------------------------------------


use pubs


if object_ID('TEST_CL') is not null drop table Test_CL

create table TEST_CL (Name sysname Primary Key)

 

if object_ID('TEST_NONCL') is not null drop table TEST_NONCL

create table TEST_NONCL (Name sysname,)

create index NC_TEST_NONCL_ID  on TEST_NONCL (Name)

 


declare @Loop int


select  @Loop = 0


While @Loop <=100

 BEGIN

 
 Insert TEST_CL
 select Table_Name + 'test' + cast(@Loop as varchar(25))
 from information_schema.tables
 
 
 
 Insert TEST_NONCL
 select Table_Name + 'test' + cast(@Loop as varchar(25))
 from information_schema.tables
 
 
 set @Loop = @Loop + 1
 

 END


select  *
from TEST_CL c0
JOIN TEST_CL c1 on c0.Name = c1.Name


select  *
from TEST_NONCL c0
JOIN TEST_NONCL c1 on c0.Name = c1.Name


 

 



Signature is NULL
Post #168166
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse