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 «««1234

Reclaiming freed space Expand / Collapse
Author
Message
Posted Tuesday, August 25, 2009 1:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 5,793, Visits: 8,002
CirquedeSQLeil (8/25/2009)
This was a good question. I would have initially ticked the truncate table option but ran some testing and research first. Though accurate, I am not sure I agree with dropping the clustered index - I think I would rather drop the nonclustered index and leave a clustered index on the table.


Thanks for the good question.


Hi Jason,

Thanks for the kind words.

The answer option with a clustered index included dropping of same because it had to be assumed that the schema should remain unchanged. In an idea world, a table with no clustered index doesn't just happen; someone has thought about it long and hard, discussed it with a colleague, and then made the decision to not have a clustered index on the table. Such a decision should not be unmade on a wimp. Though I do of course know that this ideal world often has little to no resemblence of reality. ;)

In the large majority of cases, tables should have a clustered index. There are exceptions to this rules, but they should be a small minority.
And though not the real reason for prefering a clustered index on each table, a nice added benefit is that you can henceforth reclaim lost space by simply rebuilding the index. With Enterprise Edition, that can even be done without downtime, by using the online rebuild feature!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #777042
Posted Wednesday, August 26, 2009 7:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:33 PM
Points: 2,526, Visits: 530

The answer option with a clustered index included dropping of same because it had to be assumed that the schema should remain unchanged. In an idea world, a table with no clustered index doesn't just happen; someone has thought about it long and hard, discussed it with a colleague, and then made the decision to not have a clustered index on the table. Such a decision should not be unmade on a wimp. Though I do of course know that this ideal world often has little to no resemblence of reality. ;)


I agree, you should think long and hard about the decision to not have a clustered index, but sadly, thats not true in many cases I have seen in reality. But even worse, many developers uses the management studio to design the tables and just click the "Primary key" button and create the table without any thought at all about if the Primary key is the best clustered index.

I think Microsoft have made it too easy to design and develop a database, so everyone think they can do it.




/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
Post #777445
Posted Wednesday, August 26, 2009 9:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:11 PM
Points: 20,455, Visits: 14,075
Hugo and Hakan, both bring up good points. There may be justifiable cases to not have a clustered index on a table - in an ideal world where one has thought long and hard about the decision. Besides the ability to rebuild indexes online, or defrag the indexes in the table; we also have the added benefit of being able to more easily move tables to new filegroups - should we desire or the need arise. Too often it is a novice at database design creating new tables or even databases on the whole, and oftentimes all indexes are overlooked as well as keys of any sort (PK or FK). And agreed, it should be evaluated by the team (dev and dba) whether to use a clustered index in place of the non-clustered index - just in case there was a valid reason to just use non-clustered indexing.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #777581
Posted Wednesday, November 18, 2009 10:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 29, 2010 7:15 AM
Points: 331, Visits: 62
I thought that truncate will do the job. Now I know it does not. Good question!
Post #821016
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse