March 8, 2005 at 10:17 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kThaker/tipsonoptimizingindexperformance.asp
March 16, 2005 at 1:23 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply