I was wondering if clustered indexes require major movement of data if a row needs to be inserted in the middle of a table, the answer is no.
http://www.revealnet.com/newsletter-v4/1103_B.htm
"The other possible disadvantage of clustered indexes is that inserts might suffer a little from the page splits that can be necessary to add a row to the table. Because the data is stored in the order of the index, to insert a new row SQL Server must find the page with the two rows between which the new row shall be placed. Then, if there is not room to fit the row on that page, a split occurs and some of the rows get moved from this page to a newly created one. If the table would have been a heap -- a table without a clustered index -- the row would just have been placed on any page with enough space, or a new page if none exists. Some people see this as a big problem with clustered indexes, but many of them actually misunderstand how they work. When we say that the data in clustered indexes are stored in order of the index, this doesn't mean that all the data pages are physically stored in order on disk. If it actually was this way, it would mean that in order to do a page split to fit a new row, all following pages would have to be physically moved one 'step'. As I said, this is of course not how it works. By saying that data is stored in order of the index we only mean that the data on each page is stored in order. The pages themselves are stored in a doubly linked list, with the pointers for the list (i.e. the page chain) in order. This mean that if a page split does occur, the new page can still be physically placed anywhere on the disk, it's just the pointers of the pages prior and next to it that need to be adjusted. So once again, this is actually a pretty small issue, and as you will see later in the article there are possible problems of not having a clustered index that can have much more significance than these minor disadvantages. "
Set Fill Factor for all indexes: http://stackoverflow.com/questions/16542191/sql-server-change-fill-factor-value-for-all-indexes-by-tsql
In a query execution plan, "Clustered Index Scan" is only marginally better than "Table Scan". You want to see "Clustered Index Seek" - http://stackoverflow.com/questions/18764/whats-the-difference-between-a-table-scan-and-a-clustered-index-scan
Autoshrink:
Pretty much NEVER use it. Guy claims he used to "own" the SQL Server product.
http://serverfault.com/questions/20909/is-it-safe-to-have-sql-server-auto-shrink-turned-on
On his blog, says autoshrink perfectly FRAGMENTS the table.
http://www.sqlskills.com/blogs/paul/auto-shrink-turn-it-off/
tags: cluster, index, sqlserver