August 25, 2009 at 1:48 pm
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!
August 26, 2009 at 7:04 am
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
MCSE: Data Platform
August 26, 2009 at 9:03 am
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[/url]
Learn Extended Events
November 18, 2009 at 10:31 am
I thought that truncate will do the job. Now I know it does not. Good question!
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply