Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Cluster Re-Index Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 9:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
rivermorrison22 (10/28/2013)
So when you re-index, you move the data around. That would make the non-clustered indexes invalid right?


Nope. Nonclustered indexes don't contain the row's physical location when the table is a cluster.

So it must update those indexes, just not rebuild them.


Nope. Not touched in any way.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1508969
Posted Monday, October 28, 2013 10:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:39 AM
Points: 2, Visits: 1
So what does a non-clustered index actually do then, in the presence of a clustered index? If you had an employee table with a clustered index by employeeID, and a non-clustered index by lastname, would the nonclustered index contain a sorted list of last names, and associated employeeIDs, then reference the clustered index to return all the rows with those employeeids?

Post #1509036
Posted Monday, October 28, 2013 10:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
rivermorrison22 (10/28/2013)
If you had an employee table with a clustered index by employeeID, and a non-clustered index by lastname, would the nonclustered index contain a sorted list of last names, and associated employeeIDs, then reference the clustered index to return all the rows with those employeeids?


Pretty much, yes.

http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1509038
Posted Monday, October 28, 2013 7:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
One option to consider when rebuilding indexes for defragmentation purposes is the WITH DROP_EXISTING option. You can read more about it at the following URL (2005 version)...
http://technet.microsoft.com/en-us/library/ms188783(v=sql.90).aspx

Once that page is up, do a page search for "DROP_EXISTING Clause" (without the quotes) for more information.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1509168
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse