interface supported: nonclustered

Showing posts with label nonclustered. Show all posts

Tuesday, March 20, 2012

NONCLUSTERED?

CREATE NONCLUSTERED INDEX
Hi, What's diff between NONCLUSTERED and CLUSTERED? thanksClustered indexes physically order the data in the tables (think of a Yellow
pages, which is basically ordered by lastname, firstname)
Non-clustered is additional data with pointers to the actual data (think of
all your technical books, with those index pages at the back)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"js" <js@.someone@.hotmail.com> wrote in message
news:O%23KQoGUYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> CREATE NONCLUSTERED INDEX
> Hi, What's diff between NONCLUSTERED and CLUSTERED? thanks
>|||See:
http://msdn.microsoft.com/library/d...asp?frame=true
And the subtopics...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:O%23KQoGUYFHA.3040@.TK2MSFTNGP14.phx.gbl
..
> CREATE NONCLUSTERED INDEX
> Hi, What's diff between NONCLUSTERED and CLUSTERED? thanks
>

Nonclustered UNIQUE INDEX

Hi
I have read about "Nonclustered UNIQUE INDEX" on BOL and
it am not clear what exactly is Nonclustered Unique Index!
Plese note i do understand the difference between the
Clustered and Nonclustered Index!
What i don't understand is the "Nonclustered UNIQUE
INDEX"? What does it mean when you create a "Nonclustered
UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
CONSRAINT and it allows duplicate values.
I appreciate if you could also tell me which columns are
usually good candidates for a Nonclustered UNIQUE INDEX.
Thank you,
Mitra> What i don't understand is the "Nonclustered UNIQUE
> INDEX"? What does it mean when you create a "Nonclustered
> UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
> CONSRAINT and it allows duplicate values.
You've made some assumptions here. If you create a UNIQUE INDEX then it
will not allow duplicate values. Just because you didn't explicitly create
a constraint doesn't mean one isn't implicitly created for you. Try it!
CREATE TABLE blat(foo INT)
CREATE UNIQUE INDEX splunge ON blat(foo)
GO
INSERT blat SELECT 1
INSERT blat SELECT 2
INSERT blat SELECT 3
SELECT foo FROM blat
GO
-- you will see 1, 2, 3 in the resultset.
-- however, when you try this:
INSERT blat SELECT 1
-- you will get:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'blat' with unique index
'splunge'.
The statement has been terminated.
So, essentially, a UNIQUE INDEX creates an index *and* it is enforced like a
unique constraint (though no constraint will show up using sp_help or
sp_helpconstraint).
Note that unless you include the word CLUSTERED the index will be
non-clustered.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Aaron,thank you for the clarification!
I guess I was confused because when I open the Design
Table window for a table in SQL Server 2000, in the
Properties dialog box there are two options for Create
UNIQUE setting: Constraint, and Index. Why is that?
Thanks,
Mitra
>--Original Message--
>> What i don't understand is the "Nonclustered UNIQUE
>> INDEX"? What does it mean when you create
a "Nonclustered
>> UNIQUE INDEX" on a column that it's NOT defined as
UNIQUE
>> CONSRAINT and it allows duplicate values.
>You've made some assumptions here. If you create a
UNIQUE INDEX then it
>will not allow duplicate values. Just because you
didn't explicitly create
>a constraint doesn't mean one isn't implicitly created
for you. Try it!
>
>CREATE TABLE blat(foo INT)
>CREATE UNIQUE INDEX splunge ON blat(foo)
>GO
>INSERT blat SELECT 1
>INSERT blat SELECT 2
>INSERT blat SELECT 3
>SELECT foo FROM blat
>GO
>-- you will see 1, 2, 3 in the resultset.
>-- however, when you try this:
>INSERT blat SELECT 1
>-- you will get:
>Server: Msg 2601, Level 14, State 3, Line 1
>Cannot insert duplicate key row in object 'blat' with
unique index
>'splunge'.
>The statement has been terminated.
>
>So, essentially, a UNIQUE INDEX creates an index *and*
it is enforced like a
>unique constraint (though no constraint will show up
using sp_help or
>sp_helpconstraint).
>Note that unless you include the word CLUSTERED the
index will be
>non-clustered.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||A constraint doesn't add an index, it just enforces uniqueness. You might
want to have 30 constraints on a table but you will be very unlikely to have
30 indexes that are helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
> Aaron,thank you for the clarification!
> I guess I was confused because when I open the Design
> Table window for a table in SQL Server 2000, in the
> Properties dialog box there are two options for Create
> UNIQUE setting: Constraint, and Index. Why is that?
> Thanks,
> Mitra
>>--Original Message--
>> What i don't understand is the "Nonclustered UNIQUE
>> INDEX"? What does it mean when you create
> a "Nonclustered
>> UNIQUE INDEX" on a column that it's NOT defined as
> UNIQUE
>> CONSRAINT and it allows duplicate values.
>>You've made some assumptions here. If you create a
> UNIQUE INDEX then it
>>will not allow duplicate values. Just because you
> didn't explicitly create
>>a constraint doesn't mean one isn't implicitly created
> for you. Try it!
>>
>>CREATE TABLE blat(foo INT)
>>CREATE UNIQUE INDEX splunge ON blat(foo)
>>GO
>>INSERT blat SELECT 1
>>INSERT blat SELECT 2
>>INSERT blat SELECT 3
>>SELECT foo FROM blat
>>GO
>>-- you will see 1, 2, 3 in the resultset.
>>-- however, when you try this:
>>INSERT blat SELECT 1
>>-- you will get:
>>Server: Msg 2601, Level 14, State 3, Line 1
>>Cannot insert duplicate key row in object 'blat' with
> unique index
>>'splunge'.
>>The statement has been terminated.
>>
>>So, essentially, a UNIQUE INDEX creates an index *and*
> it is enforced like a
>>unique constraint (though no constraint will show up
> using sp_help or
>>sp_helpconstraint).
>>Note that unless you include the word CLUSTERED the
> index will be
>>non-clustered.
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>http://www.aspfaq.com/
>>
>>.|||Uhm, Aaron?
A UNIQUE constraint always automatically adds an index, that's the only way
in SQL server you can implement it.
Mitra,
A constraint is part of your logical database design, an index is physical
construct. The effect of them is the same. It is best practice however to
enforce uniqueness via constraints, as it is an element of your logical
design, just like foreign keys for example. The only good reason to
implement a unique index without a unique constraint is if a subset of the
columns in the unique index is already covered by a unique constraint. One
example is when you have two columns with a unique constraint on it and for
performance reasons you also want an index with the columns in the opposite
order.
--
Jacco Schalkwijk
SQL Server MVP
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%233kkbVIOEHA.128@.TK2MSFTNGP12.phx.gbl...
> A constraint doesn't add an index, it just enforces uniqueness. You might
> want to have 30 constraints on a table but you will be very unlikely to
have
> 30 indexes that are helpful.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
> > Aaron,thank you for the clarification!
> >
> > I guess I was confused because when I open the Design
> > Table window for a table in SQL Server 2000, in the
> > Properties dialog box there are two options for Create
> > UNIQUE setting: Constraint, and Index. Why is that?
> >
> > Thanks,
> >
> > Mitra
> >
> >>--Original Message--
> >> What i don't understand is the "Nonclustered UNIQUE
> >> INDEX"? What does it mean when you create
> > a "Nonclustered
> >> UNIQUE INDEX" on a column that it's NOT defined as
> > UNIQUE
> >> CONSRAINT and it allows duplicate values.
> >>
> >>You've made some assumptions here. If you create a
> > UNIQUE INDEX then it
> >>will not allow duplicate values. Just because you
> > didn't explicitly create
> >>a constraint doesn't mean one isn't implicitly created
> > for you. Try it!
> >>
> >>
> >>CREATE TABLE blat(foo INT)
> >>CREATE UNIQUE INDEX splunge ON blat(foo)
> >>GO
> >>
> >>INSERT blat SELECT 1
> >>INSERT blat SELECT 2
> >>INSERT blat SELECT 3
> >>SELECT foo FROM blat
> >>GO
> >>
> >>-- you will see 1, 2, 3 in the resultset.
> >>-- however, when you try this:
> >>
> >>INSERT blat SELECT 1
> >>
> >>-- you will get:
> >>
> >>Server: Msg 2601, Level 14, State 3, Line 1
> >>Cannot insert duplicate key row in object 'blat' with
> > unique index
> >>'splunge'.
> >>The statement has been terminated.
> >>
> >>
> >>So, essentially, a UNIQUE INDEX creates an index *and*
> > it is enforced like a
> >>unique constraint (though no constraint will show up
> > using sp_help or
> >>sp_helpconstraint).
> >>
> >>Note that unless you include the word CLUSTERED the
> > index will be
> >>non-clustered.
> >>
> >>--
> >>Aaron Bertrand
> >>SQL Server MVP
> >>http://www.aspfaq.com/
> >>
> >>
> >>.
> >>
>|||Yes, much better explanation, sorry...
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OqBXTyMOEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Uhm, Aaron?
> A UNIQUE constraint always automatically adds an index, that's the only
way
> in SQL server you can implement it.
> Mitra,
> A constraint is part of your logical database design, an index is physical
> construct. The effect of them is the same. It is best practice however to
> enforce uniqueness via constraints, as it is an element of your logical
> design, just like foreign keys for example. The only good reason to
> implement a unique index without a unique constraint is if a subset of the
> columns in the unique index is already covered by a unique constraint. One
> example is when you have two columns with a unique constraint on it and
for
> performance reasons you also want an index with the columns in the
opposite
> order.

Nonclustered server

Hi,
What can I do transform my SQL server in noclustered server. ?
Luiz Antonio
Recife - PE BRAZILLuiz
I don't think anyone understands the question. What do you
want to transform and to what?
Regards
John
>--Original Message--
>Hi,
>What can I do transform my SQL server in noclustered
server. ?
>Luiz Antonio
>Recife - PE BRAZIL
>
>.
>

Nonclustered indexes quickie

Hi

You know when you reread something you've read many times and a penny suddenly seems to drop and you realise that you probably just never quite got it afterall?

If creating a nonclustered index with no includes statement it is totally pointless to specificy a fillfactor other than 0 or 100 if you do not also use the PAD_INDEX option?

Thought I'd make it a poll just for the hell of it.
:)Huh ?|||Hi

You know when you reread something you've read many times and a penny suddenly seems to drop and you realise that you probably just never quite got it afterall?

If creating a nonclustered index with no includes statement it is totally pointless to specificy a fillfactor other than 0 or 100 if you do not also use the PAD_INDEX option?

Thought I'd make it a poll just for the hell of it.
:)

Are you drunk or something?|||i think the brits may already be off to the pubs by this time, but please explain before I go off to BOL to try and figure this one out.|||Heh heh - I just had a couple of cups of a colleague's rocket fuel coffee - it really does affect me in odd ways. In any event I think I might have got all excited about a very obvious thing.

Ok - this was my epiphany:
The leaf level of a nonclustered index contains nothing but the index values contained in the B-Tree plus a pointer to the data page (unless you use an includes statement in which case this doesn't apply).

Fill factor affects the leaf level only. The pad index option applies the fill factor value to the B-Tree of the index. As such if pad index is not used the B-Tree is stuffed full while there is room at the leaf level. Any changes to the index will (most likely) require movement to another page on the B-Tree and, following on, movement at the leaf level. The advantage of leaving loads of room at the leaf level is to a large extent lost since you need to shuffle the B-Tree about anyway. ...

Actually - I've just realised where I have got to. If your indexes are likely to change use pad_index otherwise don't. I knew that already - I just got there backwards.

I'm going for a lie down. :D|||Heh heh -

I'm going for a lie down. :D

Careful Poots, blindman's gonna make you change your sig line.

I think I understand where you're going. I'm going to read more about it now.

Regards,

hmscott

nonclustered indexes

Non-clustered is additional data with pointers to the actual data, Then
where the pointers are stored?
shineshine
Leaf level of NCI contains index rows. Each index row contains a non
clustered key and 'pointer/s' to the actual data.

> where the pointers are stored?
Index Page
"shine" <shine@.discussions.microsoft.com> wrote in message
news:1B4E3260-A560-48BD-A5BB-F78B2A1FFF90@.microsoft.com...
> Non-clustered is additional data with pointers to the actual data, Then
> where the pointers are stored?
> shine

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.

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?

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?

Nonclustered index question

I have a table that has around 100,000 records in it, growing at a rate
of 200 records/day:
CREATE TABLE [Main] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Code] [char] (17) NOT NULL ,
[AgentID] [int] NULL ,
[DueDate] [smalldatetime] NOT NULL ,
[Status] [varchar] (18) NOT NULL
)
(This table also has about 30 more fields, but I have left them out for
brevity.)
I am looking to speed up a few specific search queries. I already have
a clustered index on my primary key field [ID], and a non-clustered
index on field [Code]. These indexes are fine performance wise.
I am looking to speed up 2 searches:
1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
AND DueDate BETWEEN (...)
2) [Status]: WHERE [Status]=xxx. Actually, this search is already
fast, and [Status] will really only have about 7 distinct values, so I
didn't index this field, as I figured a table scan would be faster in
this case. IS THIS A CORRECT ASSUMPTION. The result set for this
query is almost aways under 25 rows
My main question is: Should I put nonclustered indexes on [DueDate]
and [AgentID]. AgentID will have maybe 25 distinct values throughout
the table. The result set will usually be under 50 rows.
I just want to make sure I am not overindexing the table, and that if I
index these fields it will be beneficial.
Any help would be appreciated.for query 1, an index on ([AgentID], [DueDate]) could be an option, but
it looks like AgentID is not too sleective:

> AgentID will have maybe 25 distinct values throughout
so I'd also consider indexes on ([DueDate], [AgentID]) and just on
([DueDate])|||> 2) [Status]: WHERE [Status]=xxx. Actually, this search is already
> fast, and [Status] will really only have about 7 distinct values, so I
> didn't index this field, as I figured a table scan would be faster in
> this case. IS THIS A CORRECT ASSUMPTION. The result set for this
> query is almost aways under 25 rows
How exactly are you using this column? (i.e. how many rows per each value)
Searching for individual status values might be improved through the use of
indexed views - one per each status value. But may have a negative impact on
inserts/updates.
ML
http://milambda.blogspot.com/|||With 7 distinct values for Status, 100,000 rows in the table, and
WHERE [Status]=xxx returning under 25 rows, I have to assume that the
values for Status are severely skewed, and the status being tested is
one of the low frequency ones. Even with so few values, a query on a
specific low frequency value may in fact benefit from an index. I
would add an index on Status and see if it was used and if it helped.
With 100,000 rows, 25 distinct values for AgentID, and a result set of
around 50 rows, I have to assume that the DueDate BETWEEN test is
quite restrictive. I would try an index on (AgentID,DueDate).
Roy
On 23 Feb 2006 08:29:24 -0800, kaczmar2@.hotmail.com wrote:

>I have a table that has around 100,000 records in it, growing at a rate
>of 200 records/day:
>CREATE TABLE [Main] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Code] [char] (17) NOT NULL ,
> [AgentID] [int] NULL ,
> [DueDate] [smalldatetime] NOT NULL ,
> [Status] [varchar] (18) NOT NULL
> )
>(This table also has about 30 more fields, but I have left them out for
>brevity.)
>I am looking to speed up a few specific search queries. I already have
>a clustered index on my primary key field [ID], and a non-clustered
>index on field [Code]. These indexes are fine performance wise.
>I am looking to speed up 2 searches:
>1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
>AND DueDate BETWEEN (...)
>2) [Status]: WHERE [Status]=xxx. Actually, this search is already
>fast, and [Status] will really only have about 7 distinct values, so I
>didn't index this field, as I figured a table scan would be faster in
>this case. IS THIS A CORRECT ASSUMPTION. The result set for this
>query is almost aways under 25 rows
>My main question is: Should I put nonclustered indexes on [DueDate]
>and [AgentID]. AgentID will have maybe 25 distinct values throughout
>the table. The result set will usually be under 50 rows.
>I just want to make sure I am not overindexing the table, and that if I
>index these fields it will be beneficial.
>Any help would be appreciated.|||kaczmar2@.hotmail.com wrote:
> I have a table that has around 100,000 records in it, growing at a
> rate of 200 records/day:
> CREATE TABLE [Main] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Code] [char] (17) NOT NULL ,
> [AgentID] [int] NULL ,
> [DueDate] [smalldatetime] NOT NULL ,
> [Status] [varchar] (18) NOT NULL
> )
> (This table also has about 30 more fields, but I have left them out
> for brevity.)
> I am looking to speed up a few specific search queries. I already
> have a clustered index on my primary key field [ID], and a
> non-clustered index on field [Code]. These indexes are fine
> performance wise.
> I am looking to speed up 2 searches:
> 1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
> AND DueDate BETWEEN (...)
Depending on the selectiveness of your query criteria and distribuition of
values it might in fact be better to change the PK to non clustered and
have a clustered index on (DueDate, AgentID). If inserted DueDates are
increasing then inserts may also benefit from this index layout. On the
other hand, ID will be increasing, too. But for a range query a clustered
index is usually favourable.

> 2) [Status]: WHERE [Status]=xxx. Actually, this search is already
> fast, and [Status] will really only have about 7 distinct values, so I
> didn't index this field, as I figured a table scan would be faster in
> this case. IS THIS A CORRECT ASSUMPTION. The result set for this
> query is almost aways under 25 rows
Assuming equal distribution of values you'll get only 14% of the rows with
this criterion. I would assume that an index pays off here as this seems
pretty selective. Since you seem to be mostly searching for low freq
values it's highly likely that and index would help, especially as the
table grows. The more often you search for low freq values the more the
index pays off.

> My main question is: Should I put nonclustered indexes on [DueDate]
> and [AgentID]. AgentID will have maybe 25 distinct values throughout
> the table. The result set will usually be under 50 rows.
> I just want to make sure I am not overindexing the table, and that if
> I index these fields it will be beneficial.
You'll have to do some testing to get definitive answers. And you
probably should verify your findings from time to time as the table grows.
You might as well create a test table with the same layout and much more
data (but with typical distribution) if you want to see the behavior for
large tables beforehand.
Kind regards
robert

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
AminSQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
--
David G.

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
AminSQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
David G.

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
Amin
SQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>
|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
David G.

nonclustered index fields

Hi folks,
I was wondering if it should be taken as a rule of thumb to allways avoid
the us of the fields that conform the primary key when we are designing any
nonclustered index, as in reality they allready have this key in "their
inside".
For example, say we have a pk composed of the fields: date, id
and then we create an index1 with fields : field1, date
and yet another index2 with just field: field1
Then, whenever I search in this simplistic example for field 1 between a
range of dates I allways get better performance with just the simple index.
The question is, will it happen allways or will it depend on the particular
queries and or situations?
Thanks in advance,
Tristan."Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
>
It depends. Often, for instance, you will have a non-clustered index on the
trailing column of a two-column clustered primary key. in your example a
non-clustered index on id would probably be appropriate to enable lookups by
ID. Even though the ID is replicated in the index leaf data, since it is
not the leading column in the clustered primary key, the clustered primary
key does not provide an efficient access path for lookups or sorting by ID.
David|||Tristan
You are confusing PK with clustered index. They are not the same thing.
Clustered index keys are contained in every NC index, but the clustered
index might not be on the primary key column(s).
However, if we assume you meant clustered index when you said pk, then you
answered your own questions. It completely depends on your queries and your
data distributions. A very general rule of thumb is that if you do a lot of
modifications, you want to keep your indexes to a minimum, so you wouldn't
have indexes with overlapping keys. But if you do mainly SELECTs, more
indexes can be useful, and having different leading columns can be a BIG
help. However, in your specific example, your index1 and index2 will be
identical in every way, so there is little reason to have both of them.
I wrote an article for SQL Server Magazine about a year ago on the reasons
why you might want to explicitly list one of your clustered index columns in
your nc index definitions.
http://www.sqlmag.com/Article/Artic...rver_44807.html
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
> Thanks in advance,
> Tristan.|||Thanks lot Kalen & David for your replies. Indeed I pretendet to say
clustered when I used pk, thanks por pointing that out. Your answers have
being most clarifiying, thanks again,
Tristan.
"Kalen Delaney" wrote:

> Tristan
> You are confusing PK with clustered index. They are not the same thing.
> Clustered index keys are contained in every NC index, but the clustered
> index might not be on the primary key column(s).
> However, if we assume you meant clustered index when you said pk, then you
> answered your own questions. It completely depends on your queries and you
r
> data distributions. A very general rule of thumb is that if you do a lot o
f
> modifications, you want to keep your indexes to a minimum, so you wouldn't
> have indexes with overlapping keys. But if you do mainly SELECTs, more
> indexes can be useful, and having different leading columns can be a BIG
> help. However, in your specific example, your index1 and index2 will be
> identical in every way, so there is little reason to have both of them.
> I wrote an article for SQL Server Magazine about a year ago on the reasons
> why you might want to explicitly list one of your clustered index columns
in
> your nc index definitions.
> http://www.sqlmag.com/Article/Artic...rver_44807.html
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tristan" <Tristan@.discussions.microsoft.com> wrote in message
> news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
>
>

NonClustered Index Corruption

Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
Jerry
First off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>
|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>
|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>
|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>
|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>
|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>
|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>
|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>

NonClustered Index Corruption

Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>

Tuesday, March 20, 2012

NONCLUSTERED?

Nonclustered UNIQUE INDEX

Nonclustered UNIQUE INDEX

Nonclustered UNIQUE INDEX

Nonclustered server

Nonclustered indexes quickie

nonclustered indexes

Nonclustered Indexes

Monday, March 19, 2012

Nonclustered Indexes

Nonclustered Indexes

Nonclustered index question

Nonclustered index helps ORDER BY?

Nonclustered index helps ORDER BY?

Nonclustered index helps ORDER BY?

nonclustered index fields

NonClustered Index Corruption

NonClustered Index Corruption

interface supported

Blog Archive

About Me