Clustered DB Server: indexes

Showing posts with label indexes. Show all posts

Thursday, March 8, 2012

code information for unused indexes in sql server

Hi,
I want to know about the unused indexes. Is sql server provide any direct
api or services to give information about the unused indexes.
My problem is to find indexes which are not used in certain period of time
and if yes then delete that indexes.
Please help me by providing some suggestion or solution.Perhaps you need run ITW to get some info about unused indexes
"Nikhil Kumar Jain" <Nikhil Kumar Jain@.discussions.microsoft.com> wrote in
message news:D4D1D792-5015-4D3C-B5D3-7C7474CFD95B@.microsoft.com...
> Hi,
> I want to know about the unused indexes. Is sql server provide any direct
> api or services to give information about the unused indexes.
> My problem is to find indexes which are not used in certain period of time
> and if yes then delete that indexes.
> Please help me by providing some suggestion or solution.
>|||In profiler you can get indexid for your queries for the scans event.
Run profiler for enough period of times and also run all queries which
you use for number of times.
Now from all indexid , object id in profiler and sysindex table you
can find which indexes are being used.
Remanining indexes you can decide what to do with it.
Regards
Amish|||Thanks for your reply.
Is there no other way to do the same.
Because i want to do all this things programitically. No manual intervention
is required.
I have to write a code which will search for unused indexes and delete it if
it were not used in some periods of times.
Thats why i want some alternate approach for this.
Regards,
"amish" wrote:

> In profiler you can get indexid for your queries for the scans event.
> Run profiler for enough period of times and also run all queries which
> you use for number of times.
> Now from all indexid , object id in profiler and sysindex table you
> can find which indexes are being used.
> Remanining indexes you can decide what to do with it.
> Regards
> Amish
>|||Of course, there is the Index Tuning Wizzard.
http://search.microsoft.com/search/...r />
ng+Wizard
However, it sounds like you want to do this programmatically. I don't think
that the system tables, at least in SQL Server 2000, store meta data about
the frequency of when indexes are utilized.
http://search.microsoft.com/search/...indexes

Therefore, monitoring usage of indexes would involve examining the execution
plan when a query is executed. Read up in Books Online about SET STATISTICS
PROFILE ON.
http://search.microsoft.com/search/...TICS+PROFILE+ON
When ON, a query will return it's result set followed by another result set
that contains data about the execution plan (joins, index scans, sorts,
etc.). You can write an application that parses this data (perhaps the
StmtText column) for index usage information for specific queries.
"Nikhil Kumar Jain" <NikhilKumarJain@.discussions.microsoft.com> wrote in
message news:5D59AF07-62A0-4195-951E-DC29E1A2A82A@.microsoft.com...
> Thanks for your reply.
> Is there no other way to do the same.
> Because i want to do all this things programitically. No manual
> intervention
> is required.
> I have to write a code which will search for unused indexes and delete it
> if
> it were not used in some periods of times.
> Thats why i want some alternate approach for this.
> Regards,
>
> "amish" wrote:
>

Thursday, February 16, 2012

Clustered/Non-clustered indexes column selection

From what I've read, clustered index is particularly good for returning a set
of data, expecially if the result set requires a sort by on those columns. My
company is running education services. Let's say our enrollment table
contains the fields year, session (term), course, student_id (fk to student
table), and marks.
We do a lot of queries based on year, session, and course. I'm planning to
put year, session, and course on clustered index. The question is we
definitely will query a particular enrollment for a student using student id.
I read from many articles that non-clustered index always include the
location of the clustered index. If so, does it mean an index on student_id
(which is already there because of the FK) is the same as building one using
year, session, course, and student_id which would cover many of our queries?> If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?
Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
Just be careful, having a wide cl ix makes you nc indexes wide.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
> From what I've read, clustered index is particularly good for returning a set
> of data, expecially if the result set requires a sort by on those columns. My
> company is running education services. Let's say our enrollment table
> contains the fields year, session (term), course, student_id (fk to student
> table), and marks.
> We do a lot of queries based on year, session, and course. I'm planning to
> put year, session, and course on clustered index. The question is we
> definitely will query a particular enrollment for a student using student id.
> I read from many articles that non-clustered index always include the
> location of the clustered index. If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?|||Thanks Tibor. I'm just curious if I do create an index on all four columns.
Is SQL Server smart enough to see the first 3 are in the clustered index and
not duplicate them in the nc index?
Thanks also for the advice on the wide cl. We were debating whether it is
better to have year + session (5 digits) or year + session + course code (8
digits) as our cl ix. If we have time (yeah, like that will ever happens),
we'll go through the exec plan of our heavy queries with different set up and
see what happens.
"Tibor Karaszi" wrote:
> > If so, does it mean an index on student_id
> > (which is already there because of the FK) is the same as building one using
> > year, session, course, and student_id which would cover many of our queries?
> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
> Just be careful, having a wide cl ix makes you nc indexes wide.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
> > From what I've read, clustered index is particularly good for returning a set
> > of data, expecially if the result set requires a sort by on those columns. My
> > company is running education services. Let's say our enrollment table
> > contains the fields year, session (term), course, student_id (fk to student
> > table), and marks.
> >
> > We do a lot of queries based on year, session, and course. I'm planning to
> > put year, session, and course on clustered index. The question is we
> > definitely will query a particular enrollment for a student using student id.
> > I read from many articles that non-clustered index always include the
> > location of the clustered index. If so, does it mean an index on student_id
> > (which is already there because of the FK) is the same as building one using
> > year, session, course, and student_id which would cover many of our queries?
>
>|||> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysindexes.keycnt, good source
of information for these things.
Heh, sometimes you just need to get on with your work. But an understanding of index architecture
along with how SQL Server can use indexes (like covering a query with non-clustered indexes) will
take you a long way. A big thing is to ask the right questions, and of course later to verify your
thinking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:8025E15E-8E25-4369-8033-40A586AD7205@.microsoft.com...
> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
> Thanks also for the advice on the wide cl. We were debating whether it is
> better to have year + session (5 digits) or year + session + course code (8
> digits) as our cl ix. If we have time (yeah, like that will ever happens),
> we'll go through the exec plan of our heavy queries with different set up and
> see what happens.
> "Tibor Karaszi" wrote:
>> > If so, does it mean an index on student_id
>> > (which is already there because of the FK) is the same as building one using
>> > year, session, course, and student_id which would cover many of our queries?
>> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the
>> index.
>> Just be careful, having a wide cl ix makes you nc indexes wide.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
>> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
>> > From what I've read, clustered index is particularly good for returning a set
>> > of data, expecially if the result set requires a sort by on those columns. My
>> > company is running education services. Let's say our enrollment table
>> > contains the fields year, session (term), course, student_id (fk to student
>> > table), and marks.
>> >
>> > We do a lot of queries based on year, session, and course. I'm planning to
>> > put year, session, and course on clustered index. The question is we
>> > definitely will query a particular enrollment for a student using student id.
>> > I read from many articles that non-clustered index always include the
>> > location of the clustered index. If so, does it mean an index on student_id
>> > (which is already there because of the FK) is the same as building one using
>> > year, session, course, and student_id which would cover many of our queries?
>>

Clustered/Non-clustered Indexes and B-Trees

I would like to find information on Clustered and Non-clustered indexes and how B-trees are used. I know a clustered index is placed into a b-tree which makes sense for fast ordered searching. What data structure does a non-clustered index use and how?

I tried to find info. on the web but couldn't get much detail...I believe all indexes use B-Trees. The difference between a clustered and a non-clustered index is that the clustered index represents the way the records are actually stored in the database, and that is why a table can have only one clustered index.

Non-clustered indexes are stored separately from the table and reference the table's Primary Key.|||Why is it I always find the answer right after I post? :o

Correct me if I'm wrong but a non-clustered index stores the keys in a B-tree with the leaf level nodes containing pointers to the non-contiguous data pages.

A clustered index, however, also stores the keys in a B-tree but the leaves are the actual data pages ordered contiguously. The table data is physically ordered around the key.|||You said it, brother.

clustered vs non-clustered performance

Hi,
In a previous post I've asked a question which in the end boiled down to
the differences between clustered & non-clustered indexes.
I am not using clustered indexes in general but people here suggested that I
should. Here is an example of a query that should have made a difference
after I've changed my indexes to clustered from nonclustered as people
suggested.
With all nonclustered...
Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
reads 0, read-ahead reads 0.
Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
physical reads 0, read-ahead reads 0.
Table 'REVIEW'. Scan count 2, logical reads 650, physical reads 0,
read-ahead reads 0.
Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'PROCESS_STATUS'. Scan count 3, logical reads 84, physical reads 0,
read-ahead reads 0.
Table 'ABN_MEMBER'. Scan count 2, logical reads 132, physical reads 0,
read-ahead reads 0.
Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
With tables I can create as Clustered cause data arrive in the right order.
That means table review, abn_member and entity_type were changed.
Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
reads 0, read-ahead reads 0.
Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
physical reads 0, read-ahead reads 0.
Table 'REVIEW'. Scan count 2, logical reads 658, physical reads 0,
read-ahead reads 0.
Table 'PROCESS_STATUS'. Scan count 3, logical reads 165, physical reads 0,
read-ahead reads 0.
Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'ABN_MEMBER'. Scan count 2, logical reads 106, physical reads 0,
read-ahead reads 0.
Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
That means the nonclustered is actually better by 16 logical reads,
basically the same performance. I take logical reads as the best way to judg
e
performance. Someone mentioned that doing insertions with clustered indexes
is better cause it knows where to insert do you know by how much?
Also do you know what happens if the size of a table is over 8K > size of
page and the index is clustered, does this create any issues with performanc
e?
Thank you,
Panos.
P.S the old post was here...
http://www.microsoft.com/technet/co...0ee0f8
1Panos Stavroulis. wrote:
> Hi,
> In a previous post I've asked a question which in the end boiled
> down to
> the differences between clustered & non-clustered indexes.
> I am not using clustered indexes in general but people here suggested
> that I should. Here is an example of a query that should have made a
> difference after I've changed my indexes to clustered from
> nonclustered as people suggested.
> <SNIP>
Using logical reads is only one metric to measure performance. CPU would
be another very useful metric. Your statement about changing indexes
from non-clustered to clustered has me worried. Firstly, you can only
have one clustered index per table. So either your tables only have a
single index on each or you somehow chose which index to change.
I'm in the camp that believes there's almost always a compelling reason
for each table to have a clustered index. But there are many
considerations you have to take into account before choosing which
index, if any, is best for this. Have a look here for some good
information:
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Panos
Non-clustered indexes have the advantage of being able to be specifically
tailored to queries (having exacctly the columns required by the query, no
more, no less). This is commonly refered to as "covering" the query.
From a performance tuning perspective, Clustered indexes have the
dis-advantage of always having EVERY column in the table in their leaf
index. So, unless your query actually requires all columns (eg, select
*...), any i/o performed against the leaf pages in a clustered index
involves reading data that is not required for the query.
Hence, its common to see i/o against non-clustered indexes be less than
against clustered indexes.
I generally use clustered indexes to manage physical database maintenance
issues rather than performance tuning.
Regards,
Greg Linwood
SQL Server MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:09D211F2-EC83-4410-A129-4FDA5EDE6050@.microsoft.com...
> Hi,
> In a previous post I've asked a question which in the end boiled down to
> the differences between clustered & non-clustered indexes.
> I am not using clustered indexes in general but people here suggested that
> I
> should. Here is an example of a query that should have made a difference
> after I've changed my indexes to clustered from nonclustered as people
> suggested.
> With all nonclustered...
> Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
> reads 0, read-ahead reads 0.
> Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
> physical reads 0, read-ahead reads 0.
> Table 'REVIEW'. Scan count 2, logical reads 650, physical reads 0,
> read-ahead reads 0.
> Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'PROCESS_STATUS'. Scan count 3, logical reads 84, physical reads 0,
> read-ahead reads 0.
> Table 'ABN_MEMBER'. Scan count 2, logical reads 132, physical reads 0,
> read-ahead reads 0.
> Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
>
> With tables I can create as Clustered cause data arrive in the right
> order.
> That means table review, abn_member and entity_type were changed.
> Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
> reads 0, read-ahead reads 0.
> Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
> physical reads 0, read-ahead reads 0.
> Table 'REVIEW'. Scan count 2, logical reads 658, physical reads 0,
> read-ahead reads 0.
> Table 'PROCESS_STATUS'. Scan count 3, logical reads 165, physical reads 0,
> read-ahead reads 0.
> Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'ABN_MEMBER'. Scan count 2, logical reads 106, physical reads 0,
> read-ahead reads 0.
> Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> That means the nonclustered is actually better by 16 logical reads,
> basically the same performance. I take logical reads as the best way to
> judge
> performance. Someone mentioned that doing insertions with clustered
> indexes
> is better cause it knows where to insert do you know by how much?
> Also do you know what happens if the size of a table is over 8K > size of
> page and the index is clustered, does this create any issues with
> performance?
> Thank you,
> Panos.
> P.S the old post was here...
> http://www.microsoft.com/technet/co...0ee0
f81
>

clustered Vs NonClustered indexes

I was looking for some info on the major differences between a clustered and
a non-clustered index, but I couldn't find anything in the MS knowledgebase.
Which is more efficient? I am creating reports on an audit table that has
approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
queries take for ever to run. If I changed the indexes to clustered will
that speed things up or will it make no difference?
TIA,
JoeClustered Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms190639.aspx
Nonclustered Index Design Guidelines
http://msdn.microsoft.com/library/e...ar_da2_1tbn.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@.microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>|||Efficient for reporting queries? I have found it is particularly beneficial
to have a clustered index that compliments the same sort order of the query.
It also helps if the first column of the clustered index is included in the
where clause.
select
state, product category, dateofsale, price
from
SALES
where
state = 'FL'
order by
state, product category, dateofsale
Try creating a clustered index on: state, product category, dateofsale
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@.microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>|||On Fri, 16 Dec 2005 12:38:02 -0800, jaylou
<jaylou@.discussions.microsoft.com> wrote:
>I was looking for some info on the major differences between a clustered an
d
>a non-clustered index, but I couldn't find anything in the MS knowledgebase
.
>Which is more efficient? I am creating reports on an audit table that has
>approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
>queries take for ever to run. If I changed the indexes to clustered will
>that speed things up or will it make no difference?
Indexed separately or together?
How many rows is returned by each query?
If you want any informed answers here, posting at least the select
statements (and what fields you've indexed) is going to be helpful.
In general, it's unlikely that clustered/unclustered by itself is
going to affect your runtimes much.
J.
ps - how long is "for ever"?
pps - are other people updating the table at the same time you're
running reports?|||Clustered indexes are best used when you need to return data ordered
according to a criteria, in other words a range of data. Non-clustered
indexes are best used for singleton fetches, or data which is not
necessarily contiguous.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@.microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>

clustered vs non clustered indexes

can someone bottom line this for me? whats the difference between the two
and how do I know when to use which type?
any info is greatly appreciated. Thanks.
The first place to look for information about sql server is in the online
documentation (BOL - Books Online).
Creating and Maintaining Databases/Indexes/Designing an Index/Using
Clustered Indexes
This section has a wealth of information about indexes. Try giving that a
thorough reading.
"djc" <noone@.nowhere.com> wrote in message
news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> can someone bottom line this for me? whats the difference between the two
> and how do I know when to use which type?
> any info is greatly appreciated. Thanks.
>
|||As Scott states you should read up on it but the main difference is that you
can only have one clustered index per table since it physically sorts the
data in the order of the column(s) in the index expression. The leaf level
(lowest level) of the clustered index is the actual row of data where as in
a non-clustered index the leaf level points to the key in the clustered
index. Most tables should have a clustered index if for no other reason as
that is the only way to control fragmentation and avoid forwarding pointers.
But a CI can be extremely useful with lookups especially if you have range
type queries that use the expression the Ci is built on.
Andrew J. Kelly SQL MVP
"djc" <noone@.nowhere.com> wrote in message
news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> can someone bottom line this for me? whats the difference between the two
> and how do I know when to use which type?
> any info is greatly appreciated. Thanks.
>
|||Thanks Scott. I may have to do that but I was hoping for one of the experts
here to bottom line it for me since I don't need all the detail yet. I'm
just being resourceful.
Thanks for the reply though.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%233J0%23hlmEHA.2864@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> The first place to look for information about sql server is in the online
> documentation (BOL - Books Online).
> Creating and Maintaining Databases/Indexes/Designing an Index/Using
> Clustered Indexes
> This section has a wealth of information about indexes. Try giving that a
> thorough reading.
> "djc" <noone@.nowhere.com> wrote in message
> news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
two
>
|||That will do. Thank you very much!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ODyJ5ulmEHA.2764@.TK2MSFTNGP11.phx.gbl...
> As Scott states you should read up on it but the main difference is that
you
> can only have one clustered index per table since it physically sorts the
> data in the order of the column(s) in the index expression. The leaf
level
> (lowest level) of the clustered index is the actual row of data where as
in
> a non-clustered index the leaf level points to the key in the clustered
> index. Most tables should have a clustered index if for no other reason
as
> that is the only way to control fragmentation and avoid forwarding
pointers.[vbcol=seagreen]
> But a CI can be extremely useful with lookups especially if you have range
> type queries that use the expression the Ci is built on.
> --
> Andrew J. Kelly SQL MVP
>
> "djc" <noone@.nowhere.com> wrote in message
> news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
two
>

clustered vs non clustered indexes

can someone bottom line this for me? whats the difference between the two
and how do I know when to use which type?
any info is greatly appreciated. Thanks.The first place to look for information about sql server is in the online
documentation (BOL - Books Online).
Creating and Maintaining Databases/Indexes/Designing an Index/Using
Clustered Indexes
This section has a wealth of information about indexes. Try giving that a
thorough reading.
"djc" <noone@.nowhere.com> wrote in message
news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> can someone bottom line this for me? whats the difference between the two
> and how do I know when to use which type?
> any info is greatly appreciated. Thanks.
>|||As Scott states you should read up on it but the main difference is that you
can only have one clustered index per table since it physically sorts the
data in the order of the column(s) in the index expression. The leaf level
(lowest level) of the clustered index is the actual row of data where as in
a non-clustered index the leaf level points to the key in the clustered
index. Most tables should have a clustered index if for no other reason as
that is the only way to control fragmentation and avoid forwarding pointers.
But a CI can be extremely useful with lookups especially if you have range
type queries that use the expression the Ci is built on.
--
Andrew J. Kelly SQL MVP
"djc" <noone@.nowhere.com> wrote in message
news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> can someone bottom line this for me? whats the difference between the two
> and how do I know when to use which type?
> any info is greatly appreciated. Thanks.
>|||Thanks Scott. I may have to do that but I was hoping for one of the experts
here to bottom line it for me since I don't need all the detail yet. I'm
just being resourceful.
Thanks for the reply though.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%233J0%23hlmEHA.2864@.tk2msftngp13.phx.gbl...
> The first place to look for information about sql server is in the online
> documentation (BOL - Books Online).
> Creating and Maintaining Databases/Indexes/Designing an Index/Using
> Clustered Indexes
> This section has a wealth of information about indexes. Try giving that a
> thorough reading.
> "djc" <noone@.nowhere.com> wrote in message
> news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> > can someone bottom line this for me? whats the difference between the
two
> > and how do I know when to use which type?
> >
> > any info is greatly appreciated. Thanks.
> >
> >
>|||That will do. Thank you very much!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ODyJ5ulmEHA.2764@.TK2MSFTNGP11.phx.gbl...
> As Scott states you should read up on it but the main difference is that
you
> can only have one clustered index per table since it physically sorts the
> data in the order of the column(s) in the index expression. The leaf
level
> (lowest level) of the clustered index is the actual row of data where as
in
> a non-clustered index the leaf level points to the key in the clustered
> index. Most tables should have a clustered index if for no other reason
as
> that is the only way to control fragmentation and avoid forwarding
pointers.
> But a CI can be extremely useful with lookups especially if you have range
> type queries that use the expression the Ci is built on.
> --
> Andrew J. Kelly SQL MVP
>
> "djc" <noone@.nowhere.com> wrote in message
> news:uFq3wSlmEHA.324@.TK2MSFTNGP11.phx.gbl...
> > can someone bottom line this for me? whats the difference between the
two
> > and how do I know when to use which type?
> >
> > any info is greatly appreciated. Thanks.
> >
> >
>

Clustered Vrs Non Clustered Indexes

> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.

Clustered Vrs Non Clustered Indexes

Dear All
I recently posted a message where I stated that a
clustered index is faster than a non clustered index, is
this correct or do I have my facts totally wrong ?
Thanks
PeterSome definitions first:
An index doesn't have any speed to talk about; it isn't fast or slow. The optimizer generates
execution plans where those plans might use indexes in different ways. And when the execution engine
uses the plans, using an index or different index types influences performance.
A blanket statement like "clustered index are fasten than non-clustered indexes" is too much of a
generalization. There are cases where using a non-clustered index will result in fewer I/O
operations and shorter execution time compared to using a clustered index. One such example is where
a non-clustered index covers the query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
> Dear All
> I recently posted a message where I stated that a
> clustered index is faster than a non clustered index, is
> this correct or do I have my facts totally wrong ?
> Thanks
> Peter|||Thanks Tibor,
Ah well, wrong again ;)
Peter
>--Original Message--
>Some definitions first:
>An index doesn't have any speed to talk about; it isn't
fast or slow. The optimizer generates
>execution plans where those plans might use indexes in
different ways. And when the execution engine
>uses the plans, using an index or different index types
influences performance.
>
>A blanket statement like "clustered index are fasten than
non-clustered indexes" is too much of a
>generalization. There are cases where using a non-
clustered index will result in fewer I/O
>operations and shorter execution time compared to using a
clustered index. One such example is where
>a non-clustered index covers the query.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>> Dear All
>> I recently posted a message where I stated that a
>> clustered index is faster than a non clustered index, is
>> this correct or do I have my facts totally wrong ?
>> Thanks
>> Peter
>
>.
>|||I wouldn't say "wrong", Pete. But perhaps an over-generalization... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
> Thanks Tibor,
> Ah well, wrong again ;)
> Peter
>
>>--Original Message--
>>Some definitions first:
>>An index doesn't have any speed to talk about; it isn't
> fast or slow. The optimizer generates
>>execution plans where those plans might use indexes in
> different ways. And when the execution engine
>>uses the plans, using an index or different index types
> influences performance.
>>
>>A blanket statement like "clustered index are fasten than
> non-clustered indexes" is too much of a
>>generalization. There are cases where using a non-
> clustered index will result in fewer I/O
>>operations and shorter execution time compared to using a
> clustered index. One such example is where
>>a non-clustered index covers the query.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Peter The Spate" <anonymous@.discussions.microsoft.com>
> wrote in message
>>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>> Dear All
>> I recently posted a message where I stated that a
>> clustered index is faster than a non clustered index, is
>> this correct or do I have my facts totally wrong ?
>> Thanks
>> Peter
>>
>>.|||So far over its caused a capsize :-D
>--Original Message--
>I wouldn't say "wrong", Pete. But perhaps an over-
generalization... :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
>> Thanks Tibor,
>> Ah well, wrong again ;)
>> Peter
>>
>>--Original Message--
>>Some definitions first:
>>An index doesn't have any speed to talk about; it isn't
>> fast or slow. The optimizer generates
>>execution plans where those plans might use indexes in
>> different ways. And when the execution engine
>>uses the plans, using an index or different index types
>> influences performance.
>>
>>A blanket statement like "clustered index are fasten
than
>> non-clustered indexes" is too much of a
>>generalization. There are cases where using a non-
>> clustered index will result in fewer I/O
>>operations and shorter execution time compared to using
a
>> clustered index. One such example is where
>>a non-clustered index covers the query.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Peter The Spate" <anonymous@.discussions.microsoft.com>
>> wrote in message
>>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>> Dear All
>> I recently posted a message where I stated that a
>> clustered index is faster than a non clustered index,
is
>> this correct or do I have my facts totally wrong ?
>> Thanks
>> Peter
>>
>>.
>
>.
>|||Peter, Peter, Peter...
Don't be so hard on yourself.
Don't you remember what Einstein said?
He would rather have a watch that had stopped completely, than one that lost
a second a day.
A watch that had stopped would still show the correct time twice every day.
If you just keep saying that clustered indexes are faster than nonclustered,
you will occasionally be right.
:-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0efe01c51509$798a6890$a401280a@.phx.gbl...
> So far over its caused a capsize :-D
>
>>--Original Message--
>>I wouldn't say "wrong", Pete. But perhaps an over-
> generalization... :-)
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Peter The Spate" <anonymous@.discussions.microsoft.com>
> wrote in message
>>news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
>> Thanks Tibor,
>> Ah well, wrong again ;)
>> Peter
>>
>>--Original Message--
>>Some definitions first:
>>An index doesn't have any speed to talk about; it isn't
>> fast or slow. The optimizer generates
>>execution plans where those plans might use indexes in
>> different ways. And when the execution engine
>>uses the plans, using an index or different index types
>> influences performance.
>>
>>A blanket statement like "clustered index are fasten
> than
>> non-clustered indexes" is too much of a
>>generalization. There are cases where using a non-
>> clustered index will result in fewer I/O
>>operations and shorter execution time compared to using
> a
>> clustered index. One such example is where
>>a non-clustered index covers the query.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Peter The Spate" <anonymous@.discussions.microsoft.com>
>> wrote in message
>>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>> Dear All
>> I recently posted a message where I stated that a
>> clustered index is faster than a non clustered index,
> is
>> this correct or do I have my facts totally wrong ?
>> Thanks
>> Peter
>>
>>.
>>
>>.|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23r$pIsQFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Don't you remember what Einstein said?
> He would rather have a watch that had stopped completely, than one that
lost
> a second a day.
> A watch that had stopped would still show the correct time twice every
day.
I hope this is apocryphal or metaphorical or just a joke. :)
Personally, I'd rather have a watch that lost a second a day.|||Mark Wilden wrote:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23r$pIsQFFHA.2600@.TK2MSFTNGP09.phx.gbl...
>> Don't you remember what Einstein said?
>> He would rather have a watch that had stopped completely, than one
>> that lost a second a day.
>> A watch that had stopped would still show the correct time twice
>> every day.
> I hope this is apocryphal or metaphorical or just a joke. :)
> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.

Clustered Vrs Non Clustered Indexes

> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.

Tuesday, February 14, 2012

clustered indexes in OLTP

Hi everyone,
I came across an opinion that clustered indexes shouldn't be used in busy
OLTP systems. What are pros and cons in that?
Thanks a lot in advance
AlexIt is very useful that a table has a clustered index. What column(s) are you
using for the key, that is another thing. For example, if your table store
orders and the clustered index key is column [orderdate], I do not see a
problem there, but if you are using a uniqueidentifier data type column then
every new insert will cause that the index and table to be sort because of
the ordering of this kind of data.
AMB
"Alex" wrote:

> Hi everyone,
> I came across an opinion that clustered indexes shouldn't be used in busy
> OLTP systems. What are pros and cons in that?
> Thanks a lot in advance
> Alex
>
>|||the opinion is a dangerous one.
Generally speaking, all tables should have a clustered index. this is
crucial to the health of SQL Server.
If you need, I can explain in more detail...
Greg Jackson
PDX, Oregon|||Yes Greg, please, just in a few words
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%239u%23J7RQFHA.3448@.TK2MSFTNGP10.phx.gbl...
> the opinion is a dangerous one.
> Generally speaking, all tables should have a clustered index. this is
> crucial to the health of SQL Server.
> If you need, I can explain in more detail...
>
> Greg Jackson
> PDX, Oregon
>|||Thank you Greg
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eIVchJSQFHA.604@.TK2MSFTNGP10.phx.gbl...
>a table without a clustered index is known as a Heap.
> In a nutshell, heaps can cause Fragmentation which results in Page Splits
> (Expensive on IO)
> IF the table in quesiton, has nonclustered indexes, they cannot be
> defragged without a cluster.
> in order to do index maintenance a Clustered index is absolutely
> necessary.
>
> Here is an article I wrote that discusses many of these specifics.
>
> Cheers
> Greg J
>|||Greg, but why can nc index not be defragged without a cluster?
Alex
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eIVchJSQFHA.604@.TK2MSFTNGP10.phx.gbl...
>a table without a clustered index is known as a Heap.
> In a nutshell, heaps can cause Fragmentation which results in Page Splits
> (Expensive on IO)
> IF the table in quesiton, has nonclustered indexes, they cannot be
> defragged without a cluster.
> in order to do index maintenance a Clustered index is absolutely
> necessary.
>
> Here is an article I wrote that discusses many of these specifics.
>
> Cheers
> Greg J
>|||a heap has no order to it.
with no "Order" how can you move data to contiguous disk space...?
that is the answer. Its impossible.
GAJ|||Well, my understanding is a heap itself (its data pages) can't be defraged
but nonclustered indexes on it (index pages) can be?
Alex
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAqhz3SQFHA.3144@.tk2msftngp13.phx.gbl...
>a heap has no order to it.
> with no "Order" how can you move data to contiguous disk space...?
>
> that is the answer. Its impossible.
>
> GAJ
>|||no that is incorrect.
easy to prove.
run dbcc indexdefrag or dbcc dbreindex on a heap
before you run check frag levels with ShowContig
check again AFTER ShowContig
you'll see that frag levels (Scan density) have not improved.
GAJ|||http://www.sql-server-performance.c...red_indexes.asp
http://www.quest-pipelines.com/newsletter-v4/1103_B.htm
AMB
"Alex" wrote:

> Yes Greg, please, just in a few words
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:%239u%23J7RQFHA.3448@.TK2MSFTNGP10.phx.gbl...
>
>

Clustered Indexes and physical order.

I've got a question about how clustered indexes map to the physical order of
the records in a database. I've always been under the impression that a
clustered index orders the records physically in the order of the clustered
key. As an MCT, I've taught this idea many times.
However, on a whim I just did a page dump of a table, and noticed that the
records were not in the order of the clustered key (I used the authors table
in the pubs database). An an aside, a "select *" does display them in
clustered key order; Also the column order in a "select *" does not match
the column order in the page dump.
From a practical point of view, I suppose this makes sense, since the unit
of IO is the page, and once we have the page in the buffer, reading from
anywhere on the page is probably going to "cost" the same.
But then what does it mean to say that a clustered index orders the data
physically in the order of the index? It can't mean that the pages are
contiguous. Otherwise, fragmentation of a clustered index would be
impossible (and its not). Does it simply mean that the pages are in the
order of the index? For example, if the index is on id number, does it mean
that on page 1, if the highest id number is 5, then on page 2, the lowest id
number is greater than 5, but it may not be at the beginning of the page?
If that's so, then Microsoft's documentation and courseware is seriously
misleading in some places. I'd be grateful for any insight into this issue.It means that the pages are in the order of the index, but data with a page
are in no specified order. I don't find the documentation misleading, becaus
e
the smallest I/O is a page, and as you said, once a page is in memory, there
is no performance difference between retrieving the first or the last row in
the page.
Vern
"MAS" wrote:

> I've got a question about how clustered indexes map to the physical order
of
> the records in a database. I've always been under the impression that a
> clustered index orders the records physically in the order of the clustere
d
> key. As an MCT, I've taught this idea many times.
> However, on a whim I just did a page dump of a table, and noticed that the
> records were not in the order of the clustered key (I used the authors tab
le
> in the pubs database). An an aside, a "select *" does display them in
> clustered key order; Also the column order in a "select *" does not match
> the column order in the page dump.
> From a practical point of view, I suppose this makes sense, since the unit
> of IO is the page, and once we have the page in the buffer, reading from
> anywhere on the page is probably going to "cost" the same.
> But then what does it mean to say that a clustered index orders the data
> physically in the order of the index? It can't mean that the pages are
> contiguous. Otherwise, fragmentation of a clustered index would be
> impossible (and its not). Does it simply mean that the pages are in the
> order of the index? For example, if the index is on id number, does it me
an
> that on page 1, if the highest id number is 5, then on page 2, the lowest
id
> number is greater than 5, but it may not be at the beginning of the page?
> If that's so, then Microsoft's documentation and courseware is seriously
> misleading in some places. I'd be grateful for any insight into this issu
e.
>
>|||MAS
There was one of the longest threads in the history of this newsgroup on
this very topic, last w

.
Please see the thread entitled "insert into tbl1 select * from tbl2order by
field1 doesnt work!"
It was initially posted on May 23, by someone called CoolTech77. However,
once he posting the original question, he never came back. But someone else
jumped in and swore that because all the docs said 'physical order', it must
be true, in spite of lots of evidence to the contrary.
If you are SQL Server MCT, teaching SQL Server classes, I strongly recommend
you read my book, Inside SQL Server 2000 (2005 is coming soon) so you can
make sure you have the details correct. I actually use the authors table as
an example when talking about page structures.
Also, take a look at this blog entry by David Portas (who was one of the
people on the thread last w

) who tried to clear things up.
http://blogs.conchango.com/davidpor...05/30/4007.aspx
But, as an MCT, it would really be worthwhile to also read the whole thread.
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:%23u6SVzlhGHA.1272@.TK2MSFTNGP03.phx.gbl...
> I've got a question about how clustered indexes map to the physical order
> of the records in a database. I've always been under the impression that
> a clustered index orders the records physically in the order of the
> clustered key. As an MCT, I've taught this idea many times.
> However, on a whim I just did a page dump of a table, and noticed that the
> records were not in the order of the clustered key (I used the authors
> table in the pubs database). An an aside, a "select *" does display them
> in clustered key order; Also the column order in a "select *" does not
> match the column order in the page dump.
> From a practical point of view, I suppose this makes sense, since the unit
> of IO is the page, and once we have the page in the buffer, reading from
> anywhere on the page is probably going to "cost" the same.
> But then what does it mean to say that a clustered index orders the data
> physically in the order of the index? It can't mean that the pages are
> contiguous. Otherwise, fragmentation of a clustered index would be
> impossible (and its not). Does it simply mean that the pages are in the
> order of the index? For example, if the index is on id number, does it
> mean that on page 1, if the highest id number is 5, then on page 2, the
> lowest id number is greater than 5, but it may not be at the beginning of
> the page?
> If that's so, then Microsoft's documentation and courseware is seriously
> misleading in some places. I'd be grateful for any insight into this
> issue.
>|||Thanks,
I'll check those resources out.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23zVzeBmhGHA.1856@.TK2MSFTNGP03.phx.gbl...
> MAS
> There was one of the longest threads in the history of this newsgroup on
> this very topic, last w

.
> Please see the thread entitled "insert into tbl1 select * from tbl2order
> by field1 doesnt work!"
> It was initially posted on May 23, by someone called CoolTech77. However,
> once he posting the original question, he never came back. But someone
> else jumped in and swore that because all the docs said 'physical order',
> it must be true, in spite of lots of evidence to the contrary.
> If you are SQL Server MCT, teaching SQL Server classes, I strongly
> recommend you read my book, Inside SQL Server 2000 (2005 is coming soon)
> so you can make sure you have the details correct. I actually use the
> authors table as an example when talking about page structures.
> Also, take a look at this blog entry by David Portas (who was one of the
> people on the thread last w

) who tried to clear things up.
> http://blogs.conchango.com/davidpor...05/30/4007.aspx
> But, as an MCT, it would really be worthwhile to also read the whole
> thread.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:%23u6SVzlhGHA.1272@.TK2MSFTNGP03.phx.gbl...
>|||Good point. The SQL courses 2733 and 2734 are particularly egregious
examples of inaccurate courseware.
And BTW, even the trainers that just read slides wouldn't admit it -- it's
usually the people hiring the trainers that think all there is to the job is
reading slides.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:enlhvYLiGHA.1000@.TK2MSFTNGP04.phx.gbl...
> Yes, the courseware has many inaccuracies. It is up to the trainer to know
> the product well enough to recognize when the courseware is incomplete and
> to pursue deeper. (I know that's just my opinion, and not everyone shares
> it. Some people think all a trainer has to be able to do is read the
> slides.)
> I provide tools in Inside SQL Server that you can use to see for yourself
> how the data is physically organized.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:%23M11jCLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
>|||It's all still logical ordering. Page to page ordering is with pointers. On
the pages themselves, there is a set of bytes at the bottom of the page that
gives the offsets of the rows in logical order. I describe these row-offset
bytes in detail in my book and in my course.
It's all for performance reasons. If the rows were stored physically in
order on pages, inserts would mean shuffling rows around on the pages. This
way, a new row can go wherever there is room, and only a few bytes at the
end of the page need to be adjusted.

> your book; I've been meaning to for years -- but, I am an MCT and I also
> have a full time job as a SQL Engineer AND four kids with soccer and
> baseball games to watch -- so I don't have a lot of time. :))
Excuses, excuses ...
I've got 4 kids with classroom meetings, choir concerts and drama
productions to watch, a day job and a night job as assistant instructor at a
martial arts dojo, and MVP responsibilities to answer questions on the
newsgroups, so if I can WRITE a book, you can certainly READ it. ;-)
(Actually, this is just MY excuse for why Inside SQL Server 2005 isn't done
yet. :-) )
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:Oy0P0aMiGHA.3296@.TK2MSFTNGP05.phx.gbl...
> Now, that I've looked at it, I remember reading that thread. I'm not
> under the misunderstanding that the data is ordered physically (what a
> long thread!). I understand file fragmentation both exterior and
> interior. I was just surprised on looking at a page dump that even WITHIN
> a page the data didn't seem to be in physical order.
> On seeing that, the question in my mind was, OK, what could be meant by
> logical ordering, if, even within a page, a clustered index doesn't order
> the rows? It seems to me that within a page, the rows aren't even
> logically ordered. The IAM pages can define the logical "order" of the
> data pages, I suppose. So I'm left with the surmise that logically
> ordering doesn't mean logical ordering of the rows within a page, but the
> logical ordering of rows between pages.
> For example, we have a clustered index on letter. On page 1, we have ABCD
> but they are in the order CADB. On page 2 (which may be anywhere in the
> file physically) we have FGHI, but they are in the order GFIH. If the
> pages are full and I insert E, the page 2 splits and has EGF on page 2 and
> I on page 3.
> Is this a correct understanding? (And Kalen, I will make an attempt to
> read your book; I've been meaning to for years -- but, I am an MCT and I
> also have a full time job as a SQL Engineer AND four kids with soccer and
> baseball games to watch -- so I don't have a lot of time. :))
> Thanks again.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23zVzeBmhGHA.1856@.TK2MSFTNGP03.phx.gbl...
>|||I completely understand about the training centers. It's a very rare one
that really works hard to make sure the best possible instructor is in the
room for each course. I know of a few like that, but not many.
I assume you are aware that there is a private MCT forum (with a group
dedicated to SQL Server), where you can discuss the inaccuracies in the
courseware with other SQL MCTs. Several of the most active MVPs here are
also MCTs and you can get lots of very specific MOC-related info there.
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:e0xF44MiGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Good point. The SQL courses 2733 and 2734 are particularly egregious
> examples of inaccurate courseware.
> And BTW, even the trainers that just read slides wouldn't admit it -- it's
> usually the people hiring the trainers that think all there is to the job
> is reading slides.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:enlhvYLiGHA.1000@.TK2MSFTNGP04.phx.gbl...
>

Clustered Indexes / Primary Key

Hi All,
I am converting an Access app. from .mdb to ODBC link to a SQL Server.
The first thing I have run into is Clustered Indexes not sorted. I have a
small lookup table with 2 fields. From what I was told here by our DBA,
every key has to have a primary key/clustered index. So, the lookup tables
first field is nothing but Category Numbers which are unique and created by
the user. The table has 121 records. The 2nd field is the Category Name.
The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Name
is indexed non-clustered. When you open the table, it is sorted by the Cat.
Name and not by the Primary Key! Why?
This is not the behavior I'm looking for. Just learning SQL Server.
Don
The only way you are guaranteed any type of sorting is by
using a query and using an order by clause. You don't want
to depend on a clustered index for sorting your data. You
use an order by.
-Sue
On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:

>Hi All,
> I am converting an Access app. from .mdb to ODBC link to a SQL Server.
>The first thing I have run into is Clustered Indexes not sorted. I have a
>small lookup table with 2 fields. From what I was told here by our DBA,
>every key has to have a primary key/clustered index. So, the lookup tables
>first field is nothing but Category Numbers which are unique and created by
>the user. The table has 121 records. The 2nd field is the Category Name.
>The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Name
>is indexed non-clustered. When you open the table, it is sorted by the Cat.
>Name and not by the Primary Key! Why?
>This is not the behavior I'm looking for. Just learning SQL Server.
>Don
|||Sue,
Thanks, I figured out that a Primary key DOES NOT have to be clustered
which I was misinformed by the DBA here. The ORDER BY clause in a query will
execute faster I believe if the field that your ORDER BY clause refers to is
Indexed.
Tks again,
Don
"Sue Hoegemeier" wrote:

> The only way you are guaranteed any type of sorting is by
> using a query and using an order by clause. You don't want
> to depend on a clustered index for sorting your data. You
> use an order by.
> -Sue
> On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
> <DonaldKing@.discussions.microsoft.com> wrote:
>
>
|||I think the clustering PK issues hits people because if you
don't have a clustered index on a table and create a PK the
default is for it to be clustered. But it does not mean it
has to be that way - you can specify non-clustered for the
PK. And yes, if you are accessing the data sequentially that
column may be a good candidate for the clustered index. It's
always good to look at the whole picture with the table
though. You get one clustered index so its important to
choose wisely.
-Sue
On Tue, 16 Aug 2005 10:01:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
> Thanks, I figured out that a Primary key DOES NOT have to be clustered
>which I was misinformed by the DBA here. The ORDER BY clause in a query will
>execute faster I believe if the field that your ORDER BY clause refers to is
>Indexed.
>Tks again,
>Don
>--
>"Sue Hoegemeier" wrote:

Clustered Indexes / Primary Key

Hi All,
I am converting an Access app. from .mdb to ODBC link to a SQL Server.
The first thing I have run into is Clustered Indexes not sorted. I have a
small lookup table with 2 fields. From what I was told here by our DBA,
every key has to have a primary key/clustered index. So, the lookup tables
first field is nothing but Category Numbers which are unique and created by
the user. The table has 121 records. The 2nd field is the Category Name.
The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Name
is indexed non-clustered. When you open the table, it is sorted by the Cat.
Name and not by the Primary Key! Why'
This is not the behavior I'm looking for. Just learning SQL Server.
DonThe only way you are guaranteed any type of sorting is by
using a query and using an order by clause. You don't want
to depend on a clustered index for sorting your data. You
use an order by.
-Sue
On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:

>Hi All,
> I am converting an Access app. from .mdb to ODBC link to a SQL Server.
>The first thing I have run into is Clustered Indexes not sorted. I have a
>small lookup table with 2 fields. From what I was told here by our DBA,
>every key has to have a primary key/clustered index. So, the lookup tables
>first field is nothing but Category Numbers which are unique and created by
>the user. The table has 121 records. The 2nd field is the Category Name.
>The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Nam
e
>is indexed non-clustered. When you open the table, it is sorted by the Cat
.
>Name and not by the Primary Key! Why'
>This is not the behavior I'm looking for. Just learning SQL Server.
>Don|||Sue,
Thanks, I figured out that a Primary key DOES NOT have to be clustered
which I was misinformed by the DBA here. The ORDER BY clause in a query wil
l
execute faster I believe if the field that your ORDER BY clause refers to is
Indexed.
Tks again,
Don
--
"Sue Hoegemeier" wrote:

> The only way you are guaranteed any type of sorting is by
> using a query and using an order by clause. You don't want
> to depend on a clustered index for sorting your data. You
> use an order by.
> -Sue
> On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
> <DonaldKing@.discussions.microsoft.com> wrote:
>
>|||I think the clustering PK issues hits people because if you
don't have a clustered index on a table and create a PK the
default is for it to be clustered. But it does not mean it
has to be that way - you can specify non-clustered for the
PK. And yes, if you are accessing the data sequentially that
column may be a good candidate for the clustered index. It's
always good to look at the whole picture with the table
though. You get one clustered index so its important to
choose wisely.
-Sue
On Tue, 16 Aug 2005 10:01:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
> Thanks, I figured out that a Primary key DOES NOT have to be clustered
>which I was misinformed by the DBA here. The ORDER BY clause in a query wi
ll
>execute faster I believe if the field that your ORDER BY clause refers to i
s
>Indexed.
>Tks again,
>Don
>--
>"Sue Hoegemeier" wrote:
>

Clustered indexes - unique and not unique

Hi friends,
In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
get rebuilt too along with it, and it's clear why.
In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
Why is that?
Thanks in advance.
Because the uniqueifier that is used in 2005 does not change when the
clustered index is rebuilt. So the key appended to the non-clustered index
will still point to the proper clustered index row after a rebuild.
Andrew J. Kelly SQL MVP
"Falconer" <me@.isp.net> wrote in message
news:C28zh.38401$Oa.29862@.edtnps82...
> Hi friends,
> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
> get rebuilt too along with it, and it's clear why.
> In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
> Why is that?
> Thanks in advance.
>
|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJ5Ee9KTHHA.4252@.TK2MSFTNGP05.phx.gbl...
> Because the uniqueifier that is used in 2005 does not change when the
> clustered index is rebuilt. So the key appended to the non-clustered index
> will still point to the proper clustered index row after a rebuild.
> --
> Andrew J. Kelly SQL MVP
> "Falconer" <me@.isp.net> wrote in message
> news:C28zh.38401$Oa.29862@.edtnps82...
>

Thursday, March 8, 2012

code information for unused indexes in sql server

Thursday, February 16, 2012

Clustered/Non-clustered indexes column selection

Clustered/Non-clustered indexes column selection

Clustered/Non-clustered indexes column selection

Clustered/Non-clustered Indexes and B-Trees

clustered vs non-clustered performance

clustered Vs NonClustered indexes

clustered vs non clustered indexes

clustered vs non clustered indexes

Clustered Vrs Non Clustered Indexes

Clustered Vrs Non Clustered Indexes

Clustered Vrs Non Clustered Indexes

Tuesday, February 14, 2012

clustered indexes in OLTP

Clustered Indexes and physical order.

Clustered Indexes / Primary Key

Clustered Indexes / Primary Key

Clustered indexes - unique and not unique

Clustered DB Server

Blog Archive

About Me