SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reclaiming freed space


Reclaiming freed space

Author
Message
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10836 Visits: 11968
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. Wink

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
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2826 Visits: 605

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. Wink


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. Sad

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31946 Visits: 18551
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.Hehe



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

dstemate
dstemate
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 62
I thought that truncate will do the job. Now I know it does not. Good question!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search