September 12th, 2008

what’s faster?


select count(*)  or select count(1) ?

I think they are the same.

2 Comments | In: Uncategorized | | #



August 29th, 2008

What is a clustered index anyway?


 Well I had assumed that since a “cluster” is two or more things squished together, that a “clustered index” was an index that was composed of two or more fields. 

Did you see that word? “Assumed”, as my fellow DBA Eric says “makes an ASS out if U and ME”…ass-u-me assume get it…haha…yeah okay. *cough* SO ANYWAY…
I was wrong to assume, imagine that. But a clustered index actually orders the physical data, stored in “pages”. My best guess at the reason WHY the word “clustered” is used is that it takes all the pages that the data is stored on, reorders them, and puts them together for faster read access. But that’s just my guess.

Here is an example:

create table MyTestTable
(
HouseNumber int,
StreetName varchar(8000)
)

insert into MyTestTable values (125, ‘Skyline’)
GO
insert into MyTestTable values (234, ‘Skyline’)
GO
insert into MyTestTable values (90, ‘Skyline’)
GO
insert into MyTestTable values (75, ‘Skyline’)
GO
insert into MyTestTable values (127, ‘Skyline’)
GO

–BEFORE CLUSTERED INDEX
select * from MyTestTable
125 Skyline
234 Skyline
90 Skyline
75 Skyline
127 Skyline

Now let’s add a clustered index to this table:

CREATE CLUSTERED INDEX MyTestTable_HouseNumber
ON MyTestTable (HouseNumber)
GO

–AFTER CLUSTERED INDEX
select * from MyTestTable
75 Skyline
90 Skyline
125 Skyline
127 Skyline
234 Skyline

 


 

 

 

Clustered indexes are better for queries using ranges of data. If a human being were told to get all books off the shelf whos author’s last names start with Ri-Ro if the books are in order you can grab them and come back. If they are out of order your going to have to go an index, look up where the books are located, visit each shelf and then grab the book requested. Or worse, if there is no index, you need to scan each book in the library taking the book you need down as you go. (in SQL they call this a table scan) 
 

 

1 Comment | In: Uncategorized | tags: , . | #



Next»