Hi! Using the new SQL 2005 functionality where the optimizer determines
where the data is based on the check constraints defined on the tables, can
you use a non-deterministic function such as getdate() in the check
constraints as long as you have another mechanism to make certain the data
gets moved when the date is no longer in the valid range.
For example: Table 1 has data that is 12 months old or less. Table 2 has
data that is 13 months old or more.
Thanks.alw (alw@.discussions.microsoft.com) writes:
> Hi! Using the new SQL 2005 functionality where the optimizer determines
> where the data is based on the check constraints defined on the tables,
> can you use a non-deterministic function such as getdate() in the check
> constraints as long as you have another mechanism to make certain the
> data gets moved when the date is no longer in the valid range.
> For example: Table 1 has data that is 12 months old or less. Table 2 has
> data that is 13 months old or more.
No. It simply does not make any sense. SQL Server cannot trust you to
actually use that mechanism. If you want to move data daily, then you
have to redefine the constraints daily. Not to talk about if your datetime
column also has minutes and milliseconds...
Note that in SQL 2005 you also have partitioned tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Tuesday, March 20, 2012
Nonclustered Indexes
Is this true or false based on reading this:
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true
If a table has a nonclustered index AND a clustered index the
NONCLUSTERED index will use the clustered index key as a row locater?
Meaning SQL has to
1. Decide based on the execution plan whether or not it should use the
non clustered index
2. Scan the nonclustered index to get to the leaf node which contains
the clustered index key
3. Scan the clustered index by that key to get to its leaf node ( which
will be the data page )
4. Find the row within that page
Am I correct?The clustered index IS the table order. Therefore it becomes the lookup key
for any non-clustered index operation. Steps 2. and 3. should be Search,
not Scan operations on the nonclustered and clustered indexes. Searching
for an item in a modified B-tree structure is a very fast operation. FYI,
the operation in step 3 is called a bookmark lookup.
One of the optimizer's challenges is deciding when to skip the index use and
just scan the table. If the nonclustered index is poorly selective or the
filter criteria returns a very large result set or returns a large
proportion of the underlying table, SQL may decide that the "extra hop" to
do the bookmark lookups is slower than a single pass table scan.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141933932.804305.235590@.u72g2000cwu.googlegroups.com...
> Is this true or false based on reading this:
> http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true
> If a table has a nonclustered index AND a clustered index the
> NONCLUSTERED index will use the clustered index key as a row locater?
> Meaning SQL has to
> 1. Decide based on the execution plan whether or not it should use the
> non clustered index
> 2. Scan the nonclustered index to get to the leaf node which contains
> the clustered index key
> 3. Scan the clustered index by that key to get to its leaf node ( which
> will be the data page )
> 4. Find the row within that page
> Am I correct?
>|||Thanks Geoff for your reply.
Now a nonclustered index on a heap table uses the combination of
File ID, page number, and number of row on the page as the ROW ID in
its leaf node.
The docs state that the row locator when a clustered is available "is
the clustered index key for the row"
Does the above mean that the clustered index key takes you DIRECTLY to
the row or it takes you directly to the DATA PAGE that contains the
row?
Because from my understanding the leaf node of a clustered index IS the
data page.|||For resolving data lookups, Row and Data Page containing the row are
identical concepts. SQL loads and saves data in pages. Going from a page
to a row within a page is a trivial exercise and the two are sometimes used
interchangably when talking about lookup operations. And you are correct,
the leaf level IS the data row for a clustered index.
Note that a clustered index doesn't have to be unique. Earlier versions of
SQL (6.5 and before) worked better when the clustered index was not unique.
That changed with SQL 7.0 and true row-level locking. Now, the default for
SQL is to create a clustered index out of the Primary Key. This is not a
requirement and can be overridden at design time. When the clustered index
is not unique, a uniquifier is added to each row so the index lookup
functions work correctly.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141938175.459830.100580@.i39g2000cwa.googlegroups.com...
> Thanks Geoff for your reply.
> Now a nonclustered index on a heap table uses the combination of
> File ID, page number, and number of row on the page as the ROW ID in
> its leaf node.
> The docs state that the row locator when a clustered is available "is
> the clustered index key for the row"
> Does the above mean that the clustered index key takes you DIRECTLY to
> the row or it takes you directly to the DATA PAGE that contains the
> row?
> Because from my understanding the leaf node of a clustered index IS the
> data page.
>|||One issue not touched upon. Clustered indexes in general, are not a
great idea for most tables.
Non-clustered indexes have relatively low overhead, super fast
addition, update, and delete capabilities, and are extremely fast to
traverse.
Clustered indexes take HUGE hits when you modifiy a column in the
index, potentially when you insert a lot into the middle of the table,
but are equivalent for deletes.
An interesting side note. The non-clustered index finds itself very
quickly into cache for even the largest of tables. Not true of a
clustered index.
And pay attention to the size of the columns in the clustered index.
Pick large columns, and your performance will be seriously downgraded.
In general, if you will ALWAYS be pulling multiple rows of sequential
data, with sequential ALWAYS being defined exactly teh same, then
clustered can make sense. As an example, time stamped data, whree you
need 1000 rows at a time.
Pulling a single row, or reporting data based upon several variables,
and the non-clustered index will typically outperform the clustered
index in all manners.
regards,
doug|||Perhaps an example.
Public library, the fiction section. The clustered index is the
placement on the shelves due to the author's last name. Really nice if
ALL you EVER did was check out one author's books. You can walk right
to the correct shelf, and grab your books.
OTOH, the card catalogue is the non-clustered index. You know the
subject, or title, you don't start scanning book shelves.
it is MUCH quicker to go to teh card catalogue, get the author's name
and book name, and go to the shelf to get teh right book.
IMO, an EFFICIENT library would just throw the books on any old shelf,
noting where you stuffed it, and updating the card catalogue. Then, on
lookups, book losses, or additions, you just go to teh catalogue, find
the shelf/position, adn get your book.
regards,
doug|||So basically the clustered index is best when the selectivity is HIGH'
and the queries against the tables are generally the same ( like a
reference table )
non-clustered indexes are good when you are doing something like a
search on a catalog of parts ( or a library )
So seems to me more often then not non clustered will be the best
choice.
Why is the overhead so high for clustered?|||A poorly selected Clustered index can cause performance degradation. A
well-chosen clustered index will improve performance.
Your example illustrates a poorly chosen Clustered order. Here is a good
one:
Books are assigned a sequential number as they are purchased. They are
stocked on the shelves in that numbered order. Card catalog is used to find
books according to pre-determined criteria. Note that the physical ordering
of the books has nothing to do with most catalog requests. Only when
checking books by purchase date would the indexes align, and then only by
coincidence. This improves insert performance since all the insertion work
happens in the same part of the library. In most libraries, the more recent
a book or periodical, the more frequently it is accessed. A smart librarian
would put those high traffic books near each other and in an easily accesed
place (think cache).
Just because SQL creates a clustered index on the Primary Key by default,
doesn't mean those two entities are irrevocably tied together. A clustered
index is a physical construct. A Primary Key is a logical database design
component. They can be implemented differently.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142203270.733531.260280@.e56g2000cwe.googlegroups.com...
> Perhaps an example.
> Public library, the fiction section. The clustered index is the
> placement on the shelves due to the author's last name. Really nice if
> ALL you EVER did was check out one author's books. You can walk right
> to the correct shelf, and grab your books.
> OTOH, the card catalogue is the non-clustered index. You know the
> subject, or title, you don't start scanning book shelves.
> it is MUCH quicker to go to teh card catalogue, get the author's name
> and book name, and go to the shelf to get teh right book.
> IMO, an EFFICIENT library would just throw the books on any old shelf,
> noting where you stuffed it, and updating the card catalogue. Then, on
> lookups, book losses, or additions, you just go to teh catalogue, find
> the shelf/position, adn get your book.
> regards,
> doug
>|||so in your example, you'd have a clustered index on the identity key,
and this woudl be more efficient then have a non-clustered index?
I disagree. I would suggest having no clustered index would be faster.
the key would be "shorter." Inserts faster.
I am curious as to the logic that a clustered index would make this
scenario faster.|||Yes Im curious also.
I looking for an aswer as to why the overhead is so high just because
its physical construct.
If I load physical in the same data page then most likely if its high
transaction on that table the same data pages should be found in the
cache.
Obviously the process for searching for an data page with free space
within an extent is slow for a clustered index?
Should clustered index be used when rows will be selected "together"
most of the time? Meaning a low cardinality column?
So in a employee database all sex columns with a "Male" value can have
a clustred index because those records will be selected together?|||Again, SQL "tunes" heaps for minimum space usage, not for maximum
performance.
Inserts are faster on clustered indexes, even with a non-monotonically
increasing key which will lead to page splits. It is faster because SQL
does not have to scan for a free slot to insert the page. To continue
beating up the library analogy, the librarian has to search the stacks to
find a free space for each new book. With a clustered index, the librarian
knows where the book has to go. With the correct index choice, such as an
Identity column, inserts happen near to each other which gives the cache
manager a big advantage.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142278820.789510.195480@.p10g2000cwp.googlegroups.com...
> so in your example, you'd have a clustered index on the identity key,
> and this woudl be more efficient then have a non-clustered index?
> I disagree. I would suggest having no clustered index would be faster.
> the key would be "shorter." Inserts faster.
> I am curious as to the logic that a clustered index would make this
> scenario faster.
>|||Hi,
Clustered tables are tables that have a clustered index:
The data rows are stored in order based on the clustered index key. The
index is implemented as a B-tree index structure that supports fast
retrieval of the rows based on their clustered index key values. The
pages in each level of the index, including the data pages in the leaf
level, are linked in a doubly-linked list, but navigation from one level
to another is done using key values.
Heaps are tables that have no clustered index:
The data rows are not stored in any particular order, and there is no
particular order to the sequence of the data pages. The data pages are
not linked in a linked list.
From these descriptions you can see that there is more overhead when
using clustered indexes because when you execute SQL DML there will be
extra resources used to insure your data page is still sorted correctly,
never mind the extra data behind the scenes being stored as a reference
table for your original data. Although you?re SELECT queries will be
fast if you?re querying on the clustered index. Non - clustered indexes
are more common because lots of the time you are giving your end - users
the option to query on multiple fields from multiple tables. A good
place to use clustered indexes would be on lookup tables. For instance a
table containing country codes where there will only be a certain amount
of rows and the table will be relatively small.
Happy Coding,
Stefan
C# GURU
www.DotNETovation.com
"You always have to look beyond the horizon and can never be complacent
-- God forbid we become complacent."
Jozef Straus
*** Sent via Developersdex http://www.developersdex.com ***|||As you can see Geoff from Stefans post that we have somebody that feels
that inserts on a clustered index has MORE OVERHEAD because of the
"extra" work of keeping the data page stored/sorted correctly.
Your library example made sense to me.
I did personally conclude that clustered indexes where better for
reference/lookup tables. Stefan made a mention of that.
Which makes me think selects are faster on clustered then non clustered
Im going to trust what you said Geoff and expect that inserts are
faster on clustered indexes and select can be fast if used on the right
"type" of data/table.|||I think that the librarian example is a perfect example of the "it depends" concept. Because it
really does. The examples provided here has focused on rapid inserts. Which can be of major
important for me.
But what if my major concern it to allow people to go an look at all the books written by a specific
author? Clustering on author allow the person to look up in the index where the books written by
author X are, then he can go an browse through those books.
Or perhaps, in order to give high degree of service to my customers, I want people to browse through
books from a certain genre? Cluster on Genre, and same principle as above applies.
Trying to narrow down a "perfect" indexing scheme is a moot point, as the requirements and
priorities varies.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eEAOJvrRGHA.5552@.TK2MSFTNGP14.phx.gbl...
>A poorly selected Clustered index can cause performance degradation. A well-chosen clustered index
>will improve performance.
> Your example illustrates a poorly chosen Clustered order. Here is a good one:
> Books are assigned a sequential number as they are purchased. They are stocked on the shelves in
> that numbered order. Card catalog is used to find books according to pre-determined criteria.
> Note that the physical ordering of the books has nothing to do with most catalog requests. Only
> when checking books by purchase date would the indexes align, and then only by coincidence. This
> improves insert performance since all the insertion work happens in the same part of the library.
> In most libraries, the more recent a book or periodical, the more frequently it is accessed. A
> smart librarian would put those high traffic books near each other and in an easily accesed place
> (think cache).
> Just because SQL creates a clustered index on the Primary Key by default, doesn't mean those two
> entities are irrevocably tied together. A clustered index is a physical construct. A Primary
> Key is a logical database design component. They can be implemented differently.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Doug" <drmiller100@.hotmail.com> wrote in message
> news:1142203270.733531.260280@.e56g2000cwe.googlegroups.com...
>> Perhaps an example.
>> Public library, the fiction section. The clustered index is the
>> placement on the shelves due to the author's last name. Really nice if
>> ALL you EVER did was check out one author's books. You can walk right
>> to the correct shelf, and grab your books.
>> OTOH, the card catalogue is the non-clustered index. You know the
>> subject, or title, you don't start scanning book shelves.
>> it is MUCH quicker to go to teh card catalogue, get the author's name
>> and book name, and go to the shelf to get teh right book.
>> IMO, an EFFICIENT library would just throw the books on any old shelf,
>> noting where you stuffed it, and updating the card catalogue. Then, on
>> lookups, book losses, or additions, you just go to teh catalogue, find
>> the shelf/position, adn get your book.
>> regards,
>> doug
>|||Point taken Tibor. I agree nothing is a perfect scheme.
I was honestly just trying to get a good understanding of WHAT is
happening "underneath" when using a clustered versus a non clustered.
You get an "explanation" in books online but you can get from the docs
the "facts" of overhead in clustered indexes or non clustered indexs
are generally better then selects.
I was hoping to get good direction from others because I am new to SQL
Server and I personally dont have the insight.
Thanks for all the replies. It is appreciated.
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true
If a table has a nonclustered index AND a clustered index the
NONCLUSTERED index will use the clustered index key as a row locater?
Meaning SQL has to
1. Decide based on the execution plan whether or not it should use the
non clustered index
2. Scan the nonclustered index to get to the leaf node which contains
the clustered index key
3. Scan the clustered index by that key to get to its leaf node ( which
will be the data page )
4. Find the row within that page
Am I correct?The clustered index IS the table order. Therefore it becomes the lookup key
for any non-clustered index operation. Steps 2. and 3. should be Search,
not Scan operations on the nonclustered and clustered indexes. Searching
for an item in a modified B-tree structure is a very fast operation. FYI,
the operation in step 3 is called a bookmark lookup.
One of the optimizer's challenges is deciding when to skip the index use and
just scan the table. If the nonclustered index is poorly selective or the
filter criteria returns a very large result set or returns a large
proportion of the underlying table, SQL may decide that the "extra hop" to
do the bookmark lookups is slower than a single pass table scan.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141933932.804305.235590@.u72g2000cwu.googlegroups.com...
> Is this true or false based on reading this:
> http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true
> If a table has a nonclustered index AND a clustered index the
> NONCLUSTERED index will use the clustered index key as a row locater?
> Meaning SQL has to
> 1. Decide based on the execution plan whether or not it should use the
> non clustered index
> 2. Scan the nonclustered index to get to the leaf node which contains
> the clustered index key
> 3. Scan the clustered index by that key to get to its leaf node ( which
> will be the data page )
> 4. Find the row within that page
> Am I correct?
>|||Thanks Geoff for your reply.
Now a nonclustered index on a heap table uses the combination of
File ID, page number, and number of row on the page as the ROW ID in
its leaf node.
The docs state that the row locator when a clustered is available "is
the clustered index key for the row"
Does the above mean that the clustered index key takes you DIRECTLY to
the row or it takes you directly to the DATA PAGE that contains the
row?
Because from my understanding the leaf node of a clustered index IS the
data page.|||For resolving data lookups, Row and Data Page containing the row are
identical concepts. SQL loads and saves data in pages. Going from a page
to a row within a page is a trivial exercise and the two are sometimes used
interchangably when talking about lookup operations. And you are correct,
the leaf level IS the data row for a clustered index.
Note that a clustered index doesn't have to be unique. Earlier versions of
SQL (6.5 and before) worked better when the clustered index was not unique.
That changed with SQL 7.0 and true row-level locking. Now, the default for
SQL is to create a clustered index out of the Primary Key. This is not a
requirement and can be overridden at design time. When the clustered index
is not unique, a uniquifier is added to each row so the index lookup
functions work correctly.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141938175.459830.100580@.i39g2000cwa.googlegroups.com...
> Thanks Geoff for your reply.
> Now a nonclustered index on a heap table uses the combination of
> File ID, page number, and number of row on the page as the ROW ID in
> its leaf node.
> The docs state that the row locator when a clustered is available "is
> the clustered index key for the row"
> Does the above mean that the clustered index key takes you DIRECTLY to
> the row or it takes you directly to the DATA PAGE that contains the
> row?
> Because from my understanding the leaf node of a clustered index IS the
> data page.
>|||One issue not touched upon. Clustered indexes in general, are not a
great idea for most tables.
Non-clustered indexes have relatively low overhead, super fast
addition, update, and delete capabilities, and are extremely fast to
traverse.
Clustered indexes take HUGE hits when you modifiy a column in the
index, potentially when you insert a lot into the middle of the table,
but are equivalent for deletes.
An interesting side note. The non-clustered index finds itself very
quickly into cache for even the largest of tables. Not true of a
clustered index.
And pay attention to the size of the columns in the clustered index.
Pick large columns, and your performance will be seriously downgraded.
In general, if you will ALWAYS be pulling multiple rows of sequential
data, with sequential ALWAYS being defined exactly teh same, then
clustered can make sense. As an example, time stamped data, whree you
need 1000 rows at a time.
Pulling a single row, or reporting data based upon several variables,
and the non-clustered index will typically outperform the clustered
index in all manners.
regards,
doug|||Perhaps an example.
Public library, the fiction section. The clustered index is the
placement on the shelves due to the author's last name. Really nice if
ALL you EVER did was check out one author's books. You can walk right
to the correct shelf, and grab your books.
OTOH, the card catalogue is the non-clustered index. You know the
subject, or title, you don't start scanning book shelves.
it is MUCH quicker to go to teh card catalogue, get the author's name
and book name, and go to the shelf to get teh right book.
IMO, an EFFICIENT library would just throw the books on any old shelf,
noting where you stuffed it, and updating the card catalogue. Then, on
lookups, book losses, or additions, you just go to teh catalogue, find
the shelf/position, adn get your book.
regards,
doug|||So basically the clustered index is best when the selectivity is HIGH'
and the queries against the tables are generally the same ( like a
reference table )
non-clustered indexes are good when you are doing something like a
search on a catalog of parts ( or a library )
So seems to me more often then not non clustered will be the best
choice.
Why is the overhead so high for clustered?|||A poorly selected Clustered index can cause performance degradation. A
well-chosen clustered index will improve performance.
Your example illustrates a poorly chosen Clustered order. Here is a good
one:
Books are assigned a sequential number as they are purchased. They are
stocked on the shelves in that numbered order. Card catalog is used to find
books according to pre-determined criteria. Note that the physical ordering
of the books has nothing to do with most catalog requests. Only when
checking books by purchase date would the indexes align, and then only by
coincidence. This improves insert performance since all the insertion work
happens in the same part of the library. In most libraries, the more recent
a book or periodical, the more frequently it is accessed. A smart librarian
would put those high traffic books near each other and in an easily accesed
place (think cache).
Just because SQL creates a clustered index on the Primary Key by default,
doesn't mean those two entities are irrevocably tied together. A clustered
index is a physical construct. A Primary Key is a logical database design
component. They can be implemented differently.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142203270.733531.260280@.e56g2000cwe.googlegroups.com...
> Perhaps an example.
> Public library, the fiction section. The clustered index is the
> placement on the shelves due to the author's last name. Really nice if
> ALL you EVER did was check out one author's books. You can walk right
> to the correct shelf, and grab your books.
> OTOH, the card catalogue is the non-clustered index. You know the
> subject, or title, you don't start scanning book shelves.
> it is MUCH quicker to go to teh card catalogue, get the author's name
> and book name, and go to the shelf to get teh right book.
> IMO, an EFFICIENT library would just throw the books on any old shelf,
> noting where you stuffed it, and updating the card catalogue. Then, on
> lookups, book losses, or additions, you just go to teh catalogue, find
> the shelf/position, adn get your book.
> regards,
> doug
>|||so in your example, you'd have a clustered index on the identity key,
and this woudl be more efficient then have a non-clustered index?
I disagree. I would suggest having no clustered index would be faster.
the key would be "shorter." Inserts faster.
I am curious as to the logic that a clustered index would make this
scenario faster.|||Yes Im curious also.
I looking for an aswer as to why the overhead is so high just because
its physical construct.
If I load physical in the same data page then most likely if its high
transaction on that table the same data pages should be found in the
cache.
Obviously the process for searching for an data page with free space
within an extent is slow for a clustered index?
Should clustered index be used when rows will be selected "together"
most of the time? Meaning a low cardinality column?
So in a employee database all sex columns with a "Male" value can have
a clustred index because those records will be selected together?|||Again, SQL "tunes" heaps for minimum space usage, not for maximum
performance.
Inserts are faster on clustered indexes, even with a non-monotonically
increasing key which will lead to page splits. It is faster because SQL
does not have to scan for a free slot to insert the page. To continue
beating up the library analogy, the librarian has to search the stacks to
find a free space for each new book. With a clustered index, the librarian
knows where the book has to go. With the correct index choice, such as an
Identity column, inserts happen near to each other which gives the cache
manager a big advantage.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142278820.789510.195480@.p10g2000cwp.googlegroups.com...
> so in your example, you'd have a clustered index on the identity key,
> and this woudl be more efficient then have a non-clustered index?
> I disagree. I would suggest having no clustered index would be faster.
> the key would be "shorter." Inserts faster.
> I am curious as to the logic that a clustered index would make this
> scenario faster.
>|||Hi,
Clustered tables are tables that have a clustered index:
The data rows are stored in order based on the clustered index key. The
index is implemented as a B-tree index structure that supports fast
retrieval of the rows based on their clustered index key values. The
pages in each level of the index, including the data pages in the leaf
level, are linked in a doubly-linked list, but navigation from one level
to another is done using key values.
Heaps are tables that have no clustered index:
The data rows are not stored in any particular order, and there is no
particular order to the sequence of the data pages. The data pages are
not linked in a linked list.
From these descriptions you can see that there is more overhead when
using clustered indexes because when you execute SQL DML there will be
extra resources used to insure your data page is still sorted correctly,
never mind the extra data behind the scenes being stored as a reference
table for your original data. Although you?re SELECT queries will be
fast if you?re querying on the clustered index. Non - clustered indexes
are more common because lots of the time you are giving your end - users
the option to query on multiple fields from multiple tables. A good
place to use clustered indexes would be on lookup tables. For instance a
table containing country codes where there will only be a certain amount
of rows and the table will be relatively small.
Happy Coding,
Stefan
C# GURU
www.DotNETovation.com
"You always have to look beyond the horizon and can never be complacent
-- God forbid we become complacent."
Jozef Straus
*** Sent via Developersdex http://www.developersdex.com ***|||As you can see Geoff from Stefans post that we have somebody that feels
that inserts on a clustered index has MORE OVERHEAD because of the
"extra" work of keeping the data page stored/sorted correctly.
Your library example made sense to me.
I did personally conclude that clustered indexes where better for
reference/lookup tables. Stefan made a mention of that.
Which makes me think selects are faster on clustered then non clustered
Im going to trust what you said Geoff and expect that inserts are
faster on clustered indexes and select can be fast if used on the right
"type" of data/table.|||I think that the librarian example is a perfect example of the "it depends" concept. Because it
really does. The examples provided here has focused on rapid inserts. Which can be of major
important for me.
But what if my major concern it to allow people to go an look at all the books written by a specific
author? Clustering on author allow the person to look up in the index where the books written by
author X are, then he can go an browse through those books.
Or perhaps, in order to give high degree of service to my customers, I want people to browse through
books from a certain genre? Cluster on Genre, and same principle as above applies.
Trying to narrow down a "perfect" indexing scheme is a moot point, as the requirements and
priorities varies.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eEAOJvrRGHA.5552@.TK2MSFTNGP14.phx.gbl...
>A poorly selected Clustered index can cause performance degradation. A well-chosen clustered index
>will improve performance.
> Your example illustrates a poorly chosen Clustered order. Here is a good one:
> Books are assigned a sequential number as they are purchased. They are stocked on the shelves in
> that numbered order. Card catalog is used to find books according to pre-determined criteria.
> Note that the physical ordering of the books has nothing to do with most catalog requests. Only
> when checking books by purchase date would the indexes align, and then only by coincidence. This
> improves insert performance since all the insertion work happens in the same part of the library.
> In most libraries, the more recent a book or periodical, the more frequently it is accessed. A
> smart librarian would put those high traffic books near each other and in an easily accesed place
> (think cache).
> Just because SQL creates a clustered index on the Primary Key by default, doesn't mean those two
> entities are irrevocably tied together. A clustered index is a physical construct. A Primary
> Key is a logical database design component. They can be implemented differently.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Doug" <drmiller100@.hotmail.com> wrote in message
> news:1142203270.733531.260280@.e56g2000cwe.googlegroups.com...
>> Perhaps an example.
>> Public library, the fiction section. The clustered index is the
>> placement on the shelves due to the author's last name. Really nice if
>> ALL you EVER did was check out one author's books. You can walk right
>> to the correct shelf, and grab your books.
>> OTOH, the card catalogue is the non-clustered index. You know the
>> subject, or title, you don't start scanning book shelves.
>> it is MUCH quicker to go to teh card catalogue, get the author's name
>> and book name, and go to the shelf to get teh right book.
>> IMO, an EFFICIENT library would just throw the books on any old shelf,
>> noting where you stuffed it, and updating the card catalogue. Then, on
>> lookups, book losses, or additions, you just go to teh catalogue, find
>> the shelf/position, adn get your book.
>> regards,
>> doug
>|||Point taken Tibor. I agree nothing is a perfect scheme.
I was honestly just trying to get a good understanding of WHAT is
happening "underneath" when using a clustered versus a non clustered.
You get an "explanation" in books online but you can get from the docs
the "facts" of overhead in clustered indexes or non clustered indexs
are generally better then selects.
I was hoping to get good direction from others because I am new to SQL
Server and I personally dont have the insight.
Thanks for all the replies. It is appreciated.
Monday, March 19, 2012
Nonclustered Indexes
Is this true or false based on reading this:
[url]http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true[/url
]
If a table has a nonclustered index AND a clustered index the
NONCLUSTERED index will use the clustered index key as a row locater?
Meaning SQL has to
1. Decide based on the execution plan whether or not it should use the
non clustered index
2. Scan the nonclustered index to get to the leaf node which contains
the clustered index key
3. Scan the clustered index by that key to get to its leaf node ( which
will be the data page )
4. Find the row within that page
Am I correct?The clustered index IS the table order. Therefore it becomes the lookup key
for any non-clustered index operation. Steps 2. and 3. should be Search,
not Scan operations on the nonclustered and clustered indexes. Searching
for an item in a modified B-tree structure is a very fast operation. FYI,
the operation in step 3 is called a bookmark lookup.
One of the optimizer's challenges is deciding when to skip the index use and
just scan the table. If the nonclustered index is poorly selective or the
filter criteria returns a very large result set or returns a large
proportion of the underlying table, SQL may decide that the "extra hop" to
do the bookmark lookups is slower than a single pass table scan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141933932.804305.235590@.u72g2000cwu.googlegroups.com...
> Is this true or false based on reading this:
> [url]http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true[/u
rl]
> If a table has a nonclustered index AND a clustered index the
> NONCLUSTERED index will use the clustered index key as a row locater?
> Meaning SQL has to
> 1. Decide based on the execution plan whether or not it should use the
> non clustered index
> 2. Scan the nonclustered index to get to the leaf node which contains
> the clustered index key
> 3. Scan the clustered index by that key to get to its leaf node ( which
> will be the data page )
> 4. Find the row within that page
> Am I correct?
>|||Thanks Geoff for your reply.
Now a nonclustered index on a heap table uses the combination of
File ID, page number, and number of row on the page as the ROW ID in
its leaf node.
The docs state that the row locator when a clustered is available "is
the clustered index key for the row"
Does the above mean that the clustered index key takes you DIRECTLY to
the row or it takes you directly to the DATA PAGE that contains the
row?
Because from my understanding the leaf node of a clustered index IS the
data page.|||For resolving data lookups, Row and Data Page containing the row are
identical concepts. SQL loads and saves data in pages. Going from a page
to a row within a page is a trivial exercise and the two are sometimes used
interchangably when talking about lookup operations. And you are correct,
the leaf level IS the data row for a clustered index.
Note that a clustered index doesn't have to be unique. Earlier versions of
SQL (6.5 and before) worked better when the clustered index was not unique.
That changed with SQL 7.0 and true row-level locking. Now, the default for
SQL is to create a clustered index out of the Primary Key. This is not a
requirement and can be overridden at design time. When the clustered index
is not unique, a uniquifier is added to each row so the index lookup
functions work correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141938175.459830.100580@.i39g2000cwa.googlegroups.com...
> Thanks Geoff for your reply.
> Now a nonclustered index on a heap table uses the combination of
> File ID, page number, and number of row on the page as the ROW ID in
> its leaf node.
> The docs state that the row locator when a clustered is available "is
> the clustered index key for the row"
> Does the above mean that the clustered index key takes you DIRECTLY to
> the row or it takes you directly to the DATA PAGE that contains the
> row?
> Because from my understanding the leaf node of a clustered index IS the
> data page.
>|||One issue not touched upon. Clustered indexes in general, are not a
great idea for most tables.
Non-clustered indexes have relatively low overhead, super fast
addition, update, and delete capabilities, and are extremely fast to
traverse.
Clustered indexes take HUGE hits when you modifiy a column in the
index, potentially when you insert a lot into the middle of the table,
but are equivalent for deletes.
An interesting side note. The non-clustered index finds itself very
quickly into cache for even the largest of tables. Not true of a
clustered index.
And pay attention to the size of the columns in the clustered index.
Pick large columns, and your performance will be seriously downgraded.
In general, if you will ALWAYS be pulling multiple rows of sequential
data, with sequential ALWAYS being defined exactly teh same, then
clustered can make sense. As an example, time stamped data, whree you
need 1000 rows at a time.
Pulling a single row, or reporting data based upon several variables,
and the non-clustered index will typically outperform the clustered
index in all manners.
regards,
doug|||Perhaps an example.
Public library, the fiction section. The clustered index is the
placement on the shelves due to the author's last name. Really nice if
ALL you EVER did was check out one author's books. You can walk right
to the correct shelf, and grab your books.
OTOH, the card catalogue is the non-clustered index. You know the
subject, or title, you don't start scanning book shelves.
it is MUCH quicker to go to teh card catalogue, get the author's name
and book name, and go to the shelf to get teh right book.
IMO, an EFFICIENT library would just throw the books on any old shelf,
noting where you stuffed it, and updating the card catalogue. Then, on
lookups, book losses, or additions, you just go to teh catalogue, find
the shelf/position, adn get your book.
regards,
doug|||So basically the clustered index is best when the selectivity is HIGH'
and the queries against the tables are generally the same ( like a
reference table )
non-clustered indexes are good when you are doing something like a
search on a catalog of parts ( or a library )
So seems to me more often then not non clustered will be the best
choice.
Why is the overhead so high for clustered?|||A poorly selected Clustered index can cause performance degradation. A
well-chosen clustered index will improve performance.
Your example illustrates a poorly chosen Clustered order. Here is a good
one:
Books are assigned a sequential number as they are purchased. They are
stocked on the shelves in that numbered order. Card catalog is used to find
books according to pre-determined criteria. Note that the physical ordering
of the books has nothing to do with most catalog requests. Only when
checking books by purchase date would the indexes align, and then only by
coincidence. This improves insert performance since all the insertion work
happens in the same part of the library. In most libraries, the more recent
a book or periodical, the more frequently it is accessed. A smart librarian
would put those high traffic books near each other and in an easily accesed
place (think cache).
Just because SQL creates a clustered index on the Primary Key by default,
doesn't mean those two entities are irrevocably tied together. A clustered
index is a physical construct. A Primary Key is a logical database design
component. They can be implemented differently.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142203270.733531.260280@.e56g2000cwe.googlegroups.com...
> Perhaps an example.
> Public library, the fiction section. The clustered index is the
> placement on the shelves due to the author's last name. Really nice if
> ALL you EVER did was check out one author's books. You can walk right
> to the correct shelf, and grab your books.
> OTOH, the card catalogue is the non-clustered index. You know the
> subject, or title, you don't start scanning book shelves.
> it is MUCH quicker to go to teh card catalogue, get the author's name
> and book name, and go to the shelf to get teh right book.
> IMO, an EFFICIENT library would just throw the books on any old shelf,
> noting where you stuffed it, and updating the card catalogue. Then, on
> lookups, book losses, or additions, you just go to teh catalogue, find
> the shelf/position, adn get your book.
> regards,
> doug
>|||so in your example, you'd have a clustered index on the identity key,
and this woudl be more efficient then have a non-clustered index?
I disagree. I would suggest having no clustered index would be faster.
the key would be "shorter." Inserts faster.
I am curious as to the logic that a clustered index would make this
scenario faster.|||Yes Im curious also.
I looking for an aswer as to why the overhead is so high just because
its physical construct.
If I load physical in the same data page then most likely if its high
transaction on that table the same data pages should be found in the
cache.
Obviously the process for searching for an data page with free space
within an extent is slow for a clustered index?
Should clustered index be used when rows will be selected "together"
most of the time? Meaning a low cardinality column?
So in a employee database all sex columns with a "Male" value can have
a clustred index because those records will be selected together?
[url]http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true[/url
]
If a table has a nonclustered index AND a clustered index the
NONCLUSTERED index will use the clustered index key as a row locater?
Meaning SQL has to
1. Decide based on the execution plan whether or not it should use the
non clustered index
2. Scan the nonclustered index to get to the leaf node which contains
the clustered index key
3. Scan the clustered index by that key to get to its leaf node ( which
will be the data page )
4. Find the row within that page
Am I correct?The clustered index IS the table order. Therefore it becomes the lookup key
for any non-clustered index operation. Steps 2. and 3. should be Search,
not Scan operations on the nonclustered and clustered indexes. Searching
for an item in a modified B-tree structure is a very fast operation. FYI,
the operation in step 3 is called a bookmark lookup.
One of the optimizer's challenges is deciding when to skip the index use and
just scan the table. If the nonclustered index is poorly selective or the
filter criteria returns a very large result set or returns a large
proportion of the underlying table, SQL may decide that the "extra hop" to
do the bookmark lookups is slower than a single pass table scan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141933932.804305.235590@.u72g2000cwu.googlegroups.com...
> Is this true or false based on reading this:
> [url]http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_75mb.asp?frame=true[/u
rl]
> If a table has a nonclustered index AND a clustered index the
> NONCLUSTERED index will use the clustered index key as a row locater?
> Meaning SQL has to
> 1. Decide based on the execution plan whether or not it should use the
> non clustered index
> 2. Scan the nonclustered index to get to the leaf node which contains
> the clustered index key
> 3. Scan the clustered index by that key to get to its leaf node ( which
> will be the data page )
> 4. Find the row within that page
> Am I correct?
>|||Thanks Geoff for your reply.
Now a nonclustered index on a heap table uses the combination of
File ID, page number, and number of row on the page as the ROW ID in
its leaf node.
The docs state that the row locator when a clustered is available "is
the clustered index key for the row"
Does the above mean that the clustered index key takes you DIRECTLY to
the row or it takes you directly to the DATA PAGE that contains the
row?
Because from my understanding the leaf node of a clustered index IS the
data page.|||For resolving data lookups, Row and Data Page containing the row are
identical concepts. SQL loads and saves data in pages. Going from a page
to a row within a page is a trivial exercise and the two are sometimes used
interchangably when talking about lookup operations. And you are correct,
the leaf level IS the data row for a clustered index.
Note that a clustered index doesn't have to be unique. Earlier versions of
SQL (6.5 and before) worked better when the clustered index was not unique.
That changed with SQL 7.0 and true row-level locking. Now, the default for
SQL is to create a clustered index out of the Primary Key. This is not a
requirement and can be overridden at design time. When the clustered index
is not unique, a uniquifier is added to each row so the index lookup
functions work correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141938175.459830.100580@.i39g2000cwa.googlegroups.com...
> Thanks Geoff for your reply.
> Now a nonclustered index on a heap table uses the combination of
> File ID, page number, and number of row on the page as the ROW ID in
> its leaf node.
> The docs state that the row locator when a clustered is available "is
> the clustered index key for the row"
> Does the above mean that the clustered index key takes you DIRECTLY to
> the row or it takes you directly to the DATA PAGE that contains the
> row?
> Because from my understanding the leaf node of a clustered index IS the
> data page.
>|||One issue not touched upon. Clustered indexes in general, are not a
great idea for most tables.
Non-clustered indexes have relatively low overhead, super fast
addition, update, and delete capabilities, and are extremely fast to
traverse.
Clustered indexes take HUGE hits when you modifiy a column in the
index, potentially when you insert a lot into the middle of the table,
but are equivalent for deletes.
An interesting side note. The non-clustered index finds itself very
quickly into cache for even the largest of tables. Not true of a
clustered index.
And pay attention to the size of the columns in the clustered index.
Pick large columns, and your performance will be seriously downgraded.
In general, if you will ALWAYS be pulling multiple rows of sequential
data, with sequential ALWAYS being defined exactly teh same, then
clustered can make sense. As an example, time stamped data, whree you
need 1000 rows at a time.
Pulling a single row, or reporting data based upon several variables,
and the non-clustered index will typically outperform the clustered
index in all manners.
regards,
doug|||Perhaps an example.
Public library, the fiction section. The clustered index is the
placement on the shelves due to the author's last name. Really nice if
ALL you EVER did was check out one author's books. You can walk right
to the correct shelf, and grab your books.
OTOH, the card catalogue is the non-clustered index. You know the
subject, or title, you don't start scanning book shelves.
it is MUCH quicker to go to teh card catalogue, get the author's name
and book name, and go to the shelf to get teh right book.
IMO, an EFFICIENT library would just throw the books on any old shelf,
noting where you stuffed it, and updating the card catalogue. Then, on
lookups, book losses, or additions, you just go to teh catalogue, find
the shelf/position, adn get your book.
regards,
doug|||So basically the clustered index is best when the selectivity is HIGH'
and the queries against the tables are generally the same ( like a
reference table )
non-clustered indexes are good when you are doing something like a
search on a catalog of parts ( or a library )
So seems to me more often then not non clustered will be the best
choice.
Why is the overhead so high for clustered?|||A poorly selected Clustered index can cause performance degradation. A
well-chosen clustered index will improve performance.
Your example illustrates a poorly chosen Clustered order. Here is a good
one:
Books are assigned a sequential number as they are purchased. They are
stocked on the shelves in that numbered order. Card catalog is used to find
books according to pre-determined criteria. Note that the physical ordering
of the books has nothing to do with most catalog requests. Only when
checking books by purchase date would the indexes align, and then only by
coincidence. This improves insert performance since all the insertion work
happens in the same part of the library. In most libraries, the more recent
a book or periodical, the more frequently it is accessed. A smart librarian
would put those high traffic books near each other and in an easily accesed
place (think cache).
Just because SQL creates a clustered index on the Primary Key by default,
doesn't mean those two entities are irrevocably tied together. A clustered
index is a physical construct. A Primary Key is a logical database design
component. They can be implemented differently.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142203270.733531.260280@.e56g2000cwe.googlegroups.com...
> Perhaps an example.
> Public library, the fiction section. The clustered index is the
> placement on the shelves due to the author's last name. Really nice if
> ALL you EVER did was check out one author's books. You can walk right
> to the correct shelf, and grab your books.
> OTOH, the card catalogue is the non-clustered index. You know the
> subject, or title, you don't start scanning book shelves.
> it is MUCH quicker to go to teh card catalogue, get the author's name
> and book name, and go to the shelf to get teh right book.
> IMO, an EFFICIENT library would just throw the books on any old shelf,
> noting where you stuffed it, and updating the card catalogue. Then, on
> lookups, book losses, or additions, you just go to teh catalogue, find
> the shelf/position, adn get your book.
> regards,
> doug
>|||so in your example, you'd have a clustered index on the identity key,
and this woudl be more efficient then have a non-clustered index?
I disagree. I would suggest having no clustered index would be faster.
the key would be "shorter." Inserts faster.
I am curious as to the logic that a clustered index would make this
scenario faster.|||Yes Im curious also.
I looking for an aswer as to why the overhead is so high just because
its physical construct.
If I load physical in the same data page then most likely if its high
transaction on that table the same data pages should be found in the
cache.
Obviously the process for searching for an data page with free space
within an extent is slow for a clustered index?
Should clustered index be used when rows will be selected "together"
most of the time? Meaning a low cardinality column?
So in a employee database all sex columns with a "Male" value can have
a clustred index because those records will be selected together?
Nonclustered Indexes
Is this true or false based on reading this:
http://msdn.microsoft.com/library/en...asp?frame=true
If a table has a nonclustered index AND a clustered index the
NONCLUSTERED index will use the clustered index key as a row locater?
Meaning SQL has to
1. Decide based on the execution plan whether or not it should use the
non clustered index
2. Scan the nonclustered index to get to the leaf node which contains
the clustered index key
3. Scan the clustered index by that key to get to its leaf node ( which
will be the data page )
4. Find the row within that page
Am I correct?
The clustered index IS the table order. Therefore it becomes the lookup key
for any non-clustered index operation. Steps 2. and 3. should be Search,
not Scan operations on the nonclustered and clustered indexes. Searching
for an item in a modified B-tree structure is a very fast operation. FYI,
the operation in step 3 is called a bookmark lookup.
One of the optimizer's challenges is deciding when to skip the index use and
just scan the table. If the nonclustered index is poorly selective or the
filter criteria returns a very large result set or returns a large
proportion of the underlying table, SQL may decide that the "extra hop" to
do the bookmark lookups is slower than a single pass table scan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141933932.804305.235590@.u72g2000cwu.googlegr oups.com...
> Is this true or false based on reading this:
> http://msdn.microsoft.com/library/en...asp?frame=true
> If a table has a nonclustered index AND a clustered index the
> NONCLUSTERED index will use the clustered index key as a row locater?
> Meaning SQL has to
> 1. Decide based on the execution plan whether or not it should use the
> non clustered index
> 2. Scan the nonclustered index to get to the leaf node which contains
> the clustered index key
> 3. Scan the clustered index by that key to get to its leaf node ( which
> will be the data page )
> 4. Find the row within that page
> Am I correct?
>
|||Thanks Geoff for your reply.
Now a nonclustered index on a heap table uses the combination of
File ID, page number, and number of row on the page as the ROW ID in
its leaf node.
The docs state that the row locator when a clustered is available "is
the clustered index key for the row"
Does the above mean that the clustered index key takes you DIRECTLY to
the row or it takes you directly to the DATA PAGE that contains the
row?
Because from my understanding the leaf node of a clustered index IS the
data page.
|||For resolving data lookups, Row and Data Page containing the row are
identical concepts. SQL loads and saves data in pages. Going from a page
to a row within a page is a trivial exercise and the two are sometimes used
interchangably when talking about lookup operations. And you are correct,
the leaf level IS the data row for a clustered index.
Note that a clustered index doesn't have to be unique. Earlier versions of
SQL (6.5 and before) worked better when the clustered index was not unique.
That changed with SQL 7.0 and true row-level locking. Now, the default for
SQL is to create a clustered index out of the Primary Key. This is not a
requirement and can be overridden at design time. When the clustered index
is not unique, a uniquifier is added to each row so the index lookup
functions work correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141938175.459830.100580@.i39g2000cwa.googlegr oups.com...
> Thanks Geoff for your reply.
> Now a nonclustered index on a heap table uses the combination of
> File ID, page number, and number of row on the page as the ROW ID in
> its leaf node.
> The docs state that the row locator when a clustered is available "is
> the clustered index key for the row"
> Does the above mean that the clustered index key takes you DIRECTLY to
> the row or it takes you directly to the DATA PAGE that contains the
> row?
> Because from my understanding the leaf node of a clustered index IS the
> data page.
>
|||One issue not touched upon. Clustered indexes in general, are not a
great idea for most tables.
Non-clustered indexes have relatively low overhead, super fast
addition, update, and delete capabilities, and are extremely fast to
traverse.
Clustered indexes take HUGE hits when you modifiy a column in the
index, potentially when you insert a lot into the middle of the table,
but are equivalent for deletes.
An interesting side note. The non-clustered index finds itself very
quickly into cache for even the largest of tables. Not true of a
clustered index.
And pay attention to the size of the columns in the clustered index.
Pick large columns, and your performance will be seriously downgraded.
In general, if you will ALWAYS be pulling multiple rows of sequential
data, with sequential ALWAYS being defined exactly teh same, then
clustered can make sense. As an example, time stamped data, whree you
need 1000 rows at a time.
Pulling a single row, or reporting data based upon several variables,
and the non-clustered index will typically outperform the clustered
index in all manners.
regards,
doug
|||Perhaps an example.
Public library, the fiction section. The clustered index is the
placement on the shelves due to the author's last name. Really nice if
ALL you EVER did was check out one author's books. You can walk right
to the correct shelf, and grab your books.
OTOH, the card catalogue is the non-clustered index. You know the
subject, or title, you don't start scanning book shelves.
it is MUCH quicker to go to teh card catalogue, get the author's name
and book name, and go to the shelf to get teh right book.
IMO, an EFFICIENT library would just throw the books on any old shelf,
noting where you stuffed it, and updating the card catalogue. Then, on
lookups, book losses, or additions, you just go to teh catalogue, find
the shelf/position, adn get your book.
regards,
doug
|||So basically the clustered index is best when the selectivity is HIGH?
and the queries against the tables are generally the same ( like a
reference table )
non-clustered indexes are good when you are doing something like a
search on a catalog of parts ( or a library )
So seems to me more often then not non clustered will be the best
choice.
Why is the overhead so high for clustered?
|||A poorly selected Clustered index can cause performance degradation. A
well-chosen clustered index will improve performance.
Your example illustrates a poorly chosen Clustered order. Here is a good
one:
Books are assigned a sequential number as they are purchased. They are
stocked on the shelves in that numbered order. Card catalog is used to find
books according to pre-determined criteria. Note that the physical ordering
of the books has nothing to do with most catalog requests. Only when
checking books by purchase date would the indexes align, and then only by
coincidence. This improves insert performance since all the insertion work
happens in the same part of the library. In most libraries, the more recent
a book or periodical, the more frequently it is accessed. A smart librarian
would put those high traffic books near each other and in an easily accesed
place (think cache).
Just because SQL creates a clustered index on the Primary Key by default,
doesn't mean those two entities are irrevocably tied together. A clustered
index is a physical construct. A Primary Key is a logical database design
component. They can be implemented differently.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142203270.733531.260280@.e56g2000cwe.googlegr oups.com...
> Perhaps an example.
> Public library, the fiction section. The clustered index is the
> placement on the shelves due to the author's last name. Really nice if
> ALL you EVER did was check out one author's books. You can walk right
> to the correct shelf, and grab your books.
> OTOH, the card catalogue is the non-clustered index. You know the
> subject, or title, you don't start scanning book shelves.
> it is MUCH quicker to go to teh card catalogue, get the author's name
> and book name, and go to the shelf to get teh right book.
> IMO, an EFFICIENT library would just throw the books on any old shelf,
> noting where you stuffed it, and updating the card catalogue. Then, on
> lookups, book losses, or additions, you just go to teh catalogue, find
> the shelf/position, adn get your book.
> regards,
> doug
>
|||so in your example, you'd have a clustered index on the identity key,
and this woudl be more efficient then have a non-clustered index?
I disagree. I would suggest having no clustered index would be faster.
the key would be "shorter." Inserts faster.
I am curious as to the logic that a clustered index would make this
scenario faster.
|||Yes Im curious also.
I looking for an aswer as to why the overhead is so high just because
its physical construct.
If I load physical in the same data page then most likely if its high
transaction on that table the same data pages should be found in the
cache.
Obviously the process for searching for an data page with free space
within an extent is slow for a clustered index?
Should clustered index be used when rows will be selected "together"
most of the time? Meaning a low cardinality column?
So in a employee database all sex columns with a "Male" value can have
a clustred index because those records will be selected together?
http://msdn.microsoft.com/library/en...asp?frame=true
If a table has a nonclustered index AND a clustered index the
NONCLUSTERED index will use the clustered index key as a row locater?
Meaning SQL has to
1. Decide based on the execution plan whether or not it should use the
non clustered index
2. Scan the nonclustered index to get to the leaf node which contains
the clustered index key
3. Scan the clustered index by that key to get to its leaf node ( which
will be the data page )
4. Find the row within that page
Am I correct?
The clustered index IS the table order. Therefore it becomes the lookup key
for any non-clustered index operation. Steps 2. and 3. should be Search,
not Scan operations on the nonclustered and clustered indexes. Searching
for an item in a modified B-tree structure is a very fast operation. FYI,
the operation in step 3 is called a bookmark lookup.
One of the optimizer's challenges is deciding when to skip the index use and
just scan the table. If the nonclustered index is poorly selective or the
filter criteria returns a very large result set or returns a large
proportion of the underlying table, SQL may decide that the "extra hop" to
do the bookmark lookups is slower than a single pass table scan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141933932.804305.235590@.u72g2000cwu.googlegr oups.com...
> Is this true or false based on reading this:
> http://msdn.microsoft.com/library/en...asp?frame=true
> If a table has a nonclustered index AND a clustered index the
> NONCLUSTERED index will use the clustered index key as a row locater?
> Meaning SQL has to
> 1. Decide based on the execution plan whether or not it should use the
> non clustered index
> 2. Scan the nonclustered index to get to the leaf node which contains
> the clustered index key
> 3. Scan the clustered index by that key to get to its leaf node ( which
> will be the data page )
> 4. Find the row within that page
> Am I correct?
>
|||Thanks Geoff for your reply.
Now a nonclustered index on a heap table uses the combination of
File ID, page number, and number of row on the page as the ROW ID in
its leaf node.
The docs state that the row locator when a clustered is available "is
the clustered index key for the row"
Does the above mean that the clustered index key takes you DIRECTLY to
the row or it takes you directly to the DATA PAGE that contains the
row?
Because from my understanding the leaf node of a clustered index IS the
data page.
|||For resolving data lookups, Row and Data Page containing the row are
identical concepts. SQL loads and saves data in pages. Going from a page
to a row within a page is a trivial exercise and the two are sometimes used
interchangably when talking about lookup operations. And you are correct,
the leaf level IS the data row for a clustered index.
Note that a clustered index doesn't have to be unique. Earlier versions of
SQL (6.5 and before) worked better when the clustered index was not unique.
That changed with SQL 7.0 and true row-level locking. Now, the default for
SQL is to create a clustered index out of the Primary Key. This is not a
requirement and can be overridden at design time. When the clustered index
is not unique, a uniquifier is added to each row so the index lookup
functions work correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141938175.459830.100580@.i39g2000cwa.googlegr oups.com...
> Thanks Geoff for your reply.
> Now a nonclustered index on a heap table uses the combination of
> File ID, page number, and number of row on the page as the ROW ID in
> its leaf node.
> The docs state that the row locator when a clustered is available "is
> the clustered index key for the row"
> Does the above mean that the clustered index key takes you DIRECTLY to
> the row or it takes you directly to the DATA PAGE that contains the
> row?
> Because from my understanding the leaf node of a clustered index IS the
> data page.
>
|||One issue not touched upon. Clustered indexes in general, are not a
great idea for most tables.
Non-clustered indexes have relatively low overhead, super fast
addition, update, and delete capabilities, and are extremely fast to
traverse.
Clustered indexes take HUGE hits when you modifiy a column in the
index, potentially when you insert a lot into the middle of the table,
but are equivalent for deletes.
An interesting side note. The non-clustered index finds itself very
quickly into cache for even the largest of tables. Not true of a
clustered index.
And pay attention to the size of the columns in the clustered index.
Pick large columns, and your performance will be seriously downgraded.
In general, if you will ALWAYS be pulling multiple rows of sequential
data, with sequential ALWAYS being defined exactly teh same, then
clustered can make sense. As an example, time stamped data, whree you
need 1000 rows at a time.
Pulling a single row, or reporting data based upon several variables,
and the non-clustered index will typically outperform the clustered
index in all manners.
regards,
doug
|||Perhaps an example.
Public library, the fiction section. The clustered index is the
placement on the shelves due to the author's last name. Really nice if
ALL you EVER did was check out one author's books. You can walk right
to the correct shelf, and grab your books.
OTOH, the card catalogue is the non-clustered index. You know the
subject, or title, you don't start scanning book shelves.
it is MUCH quicker to go to teh card catalogue, get the author's name
and book name, and go to the shelf to get teh right book.
IMO, an EFFICIENT library would just throw the books on any old shelf,
noting where you stuffed it, and updating the card catalogue. Then, on
lookups, book losses, or additions, you just go to teh catalogue, find
the shelf/position, adn get your book.
regards,
doug
|||So basically the clustered index is best when the selectivity is HIGH?
and the queries against the tables are generally the same ( like a
reference table )
non-clustered indexes are good when you are doing something like a
search on a catalog of parts ( or a library )
So seems to me more often then not non clustered will be the best
choice.
Why is the overhead so high for clustered?
|||A poorly selected Clustered index can cause performance degradation. A
well-chosen clustered index will improve performance.
Your example illustrates a poorly chosen Clustered order. Here is a good
one:
Books are assigned a sequential number as they are purchased. They are
stocked on the shelves in that numbered order. Card catalog is used to find
books according to pre-determined criteria. Note that the physical ordering
of the books has nothing to do with most catalog requests. Only when
checking books by purchase date would the indexes align, and then only by
coincidence. This improves insert performance since all the insertion work
happens in the same part of the library. In most libraries, the more recent
a book or periodical, the more frequently it is accessed. A smart librarian
would put those high traffic books near each other and in an easily accesed
place (think cache).
Just because SQL creates a clustered index on the Primary Key by default,
doesn't mean those two entities are irrevocably tied together. A clustered
index is a physical construct. A Primary Key is a logical database design
component. They can be implemented differently.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142203270.733531.260280@.e56g2000cwe.googlegr oups.com...
> Perhaps an example.
> Public library, the fiction section. The clustered index is the
> placement on the shelves due to the author's last name. Really nice if
> ALL you EVER did was check out one author's books. You can walk right
> to the correct shelf, and grab your books.
> OTOH, the card catalogue is the non-clustered index. You know the
> subject, or title, you don't start scanning book shelves.
> it is MUCH quicker to go to teh card catalogue, get the author's name
> and book name, and go to the shelf to get teh right book.
> IMO, an EFFICIENT library would just throw the books on any old shelf,
> noting where you stuffed it, and updating the card catalogue. Then, on
> lookups, book losses, or additions, you just go to teh catalogue, find
> the shelf/position, adn get your book.
> regards,
> doug
>
|||so in your example, you'd have a clustered index on the identity key,
and this woudl be more efficient then have a non-clustered index?
I disagree. I would suggest having no clustered index would be faster.
the key would be "shorter." Inserts faster.
I am curious as to the logic that a clustered index would make this
scenario faster.
|||Yes Im curious also.
I looking for an aswer as to why the overhead is so high just because
its physical construct.
If I load physical in the same data page then most likely if its high
transaction on that table the same data pages should be found in the
cache.
Obviously the process for searching for an data page with free space
within an extent is slow for a clustered index?
Should clustered index be used when rows will be selected "together"
most of the time? Meaning a low cardinality column?
So in a employee database all sex columns with a "Male" value can have
a clustred index because those records will be selected together?
Monday, March 12, 2012
Non standard port - non default instance
Hi all,
I'm trying to create a subscription for a merge replication on a sql
server mobile 2005 based application.
The publisher is on a intranet, listening on port 9091 and with a named
instance.
The merge agent is in a DMZ with port 9091 open for inbound/outbound TCP
traffic to the intranet.
Problem:
when I try to synchronize I receive the "SQL Server does not exists" error.
My subscription has the following setup:
Publisher = "ServerName\InstanceName";
PublisherNetwork = NetworkType.TcpIpSockets;
PublisherAddress = "10.xxx.xxx.xxx,9091";
PublisherDatabase = "DatabaseName";
PublisherSecurityMode = SecurityType.DBAuthentication;
PublisherLogin = "name";
PublisherPassword = "pwd";
name/pwd user has correct priviledges non publisher database.
I can connect via SQL Client to the database from the machine in DMZ.
In my (many) tries, I also created an alias on the DMZ client and used
that for the Publisher property at the subscriber.
Nothing worked.
I'm finishing options...
Cl@. ha scritto:
> Problem:
> when I try to synchronize I receive the "SQL Server does not exists" error.
I solved opening 1434 udp port.
Question is: why sql client can connect without 1434 port open and the
merge agent can't?
I'm trying to create a subscription for a merge replication on a sql
server mobile 2005 based application.
The publisher is on a intranet, listening on port 9091 and with a named
instance.
The merge agent is in a DMZ with port 9091 open for inbound/outbound TCP
traffic to the intranet.
Problem:
when I try to synchronize I receive the "SQL Server does not exists" error.
My subscription has the following setup:
Publisher = "ServerName\InstanceName";
PublisherNetwork = NetworkType.TcpIpSockets;
PublisherAddress = "10.xxx.xxx.xxx,9091";
PublisherDatabase = "DatabaseName";
PublisherSecurityMode = SecurityType.DBAuthentication;
PublisherLogin = "name";
PublisherPassword = "pwd";
name/pwd user has correct priviledges non publisher database.
I can connect via SQL Client to the database from the machine in DMZ.
In my (many) tries, I also created an alias on the DMZ client and used
that for the Publisher property at the subscriber.
Nothing worked.
I'm finishing options...
Cl@. ha scritto:
> Problem:
> when I try to synchronize I receive the "SQL Server does not exists" error.
I solved opening 1434 udp port.
Question is: why sql client can connect without 1434 port open and the
merge agent can't?
Friday, March 9, 2012
NON equijoins
Hi there.
I just wanted to see if anyone has come across any patterns to deal with NON equijoins.
Based on this article - http://sqljunkies.com/WebLog/tpagel/archive/2005/08/31/16585.aspx - there seems to be 2 approaches:
1) encapsulate the logic in a stored procedure or
2) generate a joined dataset with potentially alot of unwanted rows which then need to be filtered out.
Are there any other patterns people have discovered?
Thanks.
Hi,
I had the same issue we found the solution ourselves. Look at the third thread in this question
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=367775&SiteID=1
Basically you can modify the sql in Advanced tab. Hope this helps
Nominal intrest in TSQL
Hi
I have to make a calculation based on the Excel Nominal function to get the nomanal Interest
In Exell the formula goes like this:
Per Month =NOMINAL(aIntrest;12)/12
Per Quarter =NOMINAL(aIntrest;4)/4
Half Year =NOMINAL(aIntrest;2)/2
Per Year =NOMINAL(aIntrest;1)
I would be very happy to get help on this.
I'm not entirely sure, if Excel's NOMINAL is working that way, however you can use the following to get the same resultsDECLARE @.apr FLOAT
DECLARE @.frequency FLOAT
SET @.apr = 0.02
SET @.frequency = 12
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Monthly
SET @.frequency = 4
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Quarterly
SET @.frequency = 2
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Semiannual
SET @.frequency = 1
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Annual
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||
Super
Thanks, so simple
Nominal intrest in TSQL
Hi
I have to make a calculation based on the Excel Nominal function to get the nomanal Interest
In Exell the formula goes like this:
Per Month =NOMINAL(aIntrest;12)/12
Per Quarter =NOMINAL(aIntrest;4)/4
Half Year =NOMINAL(aIntrest;2)/2
Per Year =NOMINAL(aIntrest;1)
I would be very happy to get help on this.
I'm not entirely sure, if Excel's NOMINAL is working that way, however you can use the following to get the same results
DECLARE @.apr FLOAT
DECLARE @.frequency FLOAT
SET @.apr = 0.02
SET @.frequency = 12
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Monthly
SET @.frequency = 4
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Quarterly
SET @.frequency = 2
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Semiannual
SET @.frequency = 1
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Annual
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||
Super
Thanks, so simple
Saturday, February 25, 2012
No. of records returned by a query.
Dear all
i want to run a query on sql server based on whether the no. of records returned by another query is 0 or >0. what will be the sql code for this.Assume your SELECT statement is called Q. Than you can write
if (select count(*) from (Q) q)>0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END|||...or, if you have already run your select statement as part of your procedure, use this so you don't have to execute it a second time:
if @.@.ROWCOUNT > 0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END
@.@.ROWCOUNT stores the number of records returned by the last executed statement. If you don't use the value immediately, you will need to store it in a variable because it will change with your next statement.
blindman
i want to run a query on sql server based on whether the no. of records returned by another query is 0 or >0. what will be the sql code for this.Assume your SELECT statement is called Q. Than you can write
if (select count(*) from (Q) q)>0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END|||...or, if you have already run your select statement as part of your procedure, use this so you don't have to execute it a second time:
if @.@.ROWCOUNT > 0
BEGIN
SELECT 'Your TRUE block'
END
ELSE
BEGIN
SELECT 'Your block for count(*) = 0'
END
@.@.ROWCOUNT stores the number of records returned by the last executed statement. If you don't use the value immediately, you will need to store it in a variable because it will change with your next statement.
blindman
Subscribe to:
Posts (Atom)