January 28, 2009 at 4:06 am
Hello all,
i have some questions:
1. what's the different between clustered and non clustered? i've got a lot of answer in google, but i wanna know from this forum.
2. how non-clustered index works? (from not in index until get indexed. i know b-tree index, but i dont know how it works in SQL Server. different vendor, different implementation.)
3. how non-clustered index works? (when insert query executed).
ok, i will wait. thx for your expertise.
January 28, 2009 at 4:51 am
kiman_keren (1/28/2009)
Hello all,i have some questions:
1. what's the different between clustered and non clustered? i've got a lot of answer in google, but i wanna know from this forum.
2. how non-clustered index works? (from not in index until get indexed. i know b-tree index, but i dont know how it works in SQL Server. different vendor, different implementation.)
3. how non-clustered index works? (when insert query executed).
ok, i will wait. thx for your expertise.
Those are rather large questions for a forum. I'd suggest hitting the Books Online first instead of Google.
Short answers
1. A clustered index defines not simply an ordered storage of key values like the nonclustered index, but the actual ordered storage of the data itself. To all intents and purposes, a clustered index is the table.
2) I don't understand what you're asking here "from not in index until get indexed" Do you mean what happens when an index is created?
3) An insert query adds data first to the clustered index. The clustered key value is used as the pointer in the nonclustered key. Once that key value is established in the clustered index structure, the appropriate values are added to the right spot in the nonclustered index using the key value as pointer from the nonclustered index back to the data. If necessary pages are split and the b-tree structure is rearranged.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2009 at 7:07 am
Grant Fritchey (1/28/2009)
kiman_keren (1/28/2009)
Hello all,i have some questions:
1. what's the different between clustered and non clustered? i've got a lot of answer in google, but i wanna know from this forum.
2. how non-clustered index works? (from not in index until get indexed. i know b-tree index, but i dont know how it works in SQL Server. different vendor, different implementation.)
3. how non-clustered index works? (when insert query executed).
ok, i will wait. thx for your expertise.
Those are rather large questions for a forum. I'd suggest hitting the Books Online first instead of Google.
Short answers
1. A clustered index defines not simply an ordered storage of key values like the nonclustered index, but the actual ordered storage of the data itself. To all intents and purposes, a clustered index is the table.
2) I don't understand what you're asking here "from not in index until get indexed" Do you mean what happens when an index is created?
3) An insert query adds data first to the clustered index. The clustered key value is used as the pointer in the nonclustered key. Once that key value is established in the clustered index structure, the appropriate values are added to the right spot in the nonclustered index using the key value as pointer from the nonclustered index back to the data. If necessary pages are split and the b-tree structure is rearranged.
for question number 1: ow. i will try...
for question number 2: yup, i wanna know that.
for question number 3: wow. using clustered index for non clustered index... am i right?
January 28, 2009 at 7:26 am
kiman_keren (1/28/2009)
for question number 1: ow. i will try...for question number 2: yup, i wanna know that.
for question number 3: wow. using clustered index for non clustered index... am i right?
3. Just to be clear, the clustered index key is used within the clustered index, not "for" as you stated.
2. Again, short answer, the index keys (the column or columns you're interested in) is used as criteria to create a b-tree which is then stored. That has a structure based on the data defined within the keys. There's a lot too it. Fill factor, data types, INCLUDE statements, ONLINE or OFFLINE... Start with BOL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2009 at 7:31 am
kiman_keren (1/28/2009)
for question number 2: yup, i wanna know that.
SQL reads the table, gets all the values for the columns that the index key includes, sorts them, allocates pages and writes out the leaf pages of the index, then the higher levels of the b-tree.
The leaf pages will contain all all the values for the index key columns (including duplicates), as well as the clustering key (as it's the row's 'location')
The higher levels of the b-tree just contain the starting and ending values for each of the lower pages.
Does that answer your question?
for question number 3: wow. using clustered index for non clustered index
What?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2009 at 7:42 am
ok.
question number 2 is clear.
question number 3: i got this one
http://msdn.microsoft.com/en-us/library/ms190962.aspx
but i want for non clustered.
i'm in research team, analize oracle indexing vs sql server indexing. when i insert 1000 records to a table that already indexed, sql server wins. so i want to know the algorithm of b-tree index, especially for non clustered index.
i'm from indonesia, therefore i'm sorry if my english is bad.
thx for your expertise...
January 28, 2009 at 7:55 am
kiman_keren (1/28/2009)
question number 3: i got this one
http://msdn.microsoft.com/en-us/library/ms190962.aspx
That link's got nothing to do with index algorithms. It's talking about how the execution plan shows a clustered index seek.
Try these two:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2009 at 8:02 am
ah.. thx bro...
February 2, 2009 at 1:55 am
Dear Masters,
I want to know, how to trace transaction? i want to trace insert query in an indexed column. Thx.
February 2, 2009 at 6:01 am
kiman_keren (2/2/2009)
Dear Masters,I want to know, how to trace transaction? i want to trace insert query in an indexed column. Thx.
Usually it's best when you have a new question to start a new thread on the forum so that more people can see the question and respond to it.
Really quickly, you want to look up in Books Online, the SQL Trace function. You can trace a procedure or query start and stop or you can drill down and trace individual statement calls. There is a gui that you can use to get an understanding of the process called SQL Profiler. It installs with the other SQL Server tools. Just don't point it at a production server, especially for tracing individual statements.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2009 at 8:03 pm
ah.. sorry...
February 3, 2009 at 5:40 am
No need to be sorry. Just trying to help. You'll get more eyeballs on your question when it's new if you make it a new post. That's all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2009 at 6:14 am
ok.. thx a lot...
February 3, 2009 at 8:57 am
It might be worth looking at one of Kalen Delaney's books on SQL Server Internals - Just running Profiler won't (I believe) give you the information that you need
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply