In 2005, rebuilding a table that was a heap (no clustered index) wasn't easy.  You could copy it to a different table, or you could add a clustered index and then drop it. In 2008, this is a far easier thing to do.  They have added to the ALTER TABLE command a method to rebuild the table, which is the same as rebuilding the clustered ...

Tonight, as I was creating my sample database for my chapter on implementing the database, I learned something new, that existed in 2005.  I had always used sp_changedbowner to change the owner of a database, but I was reading in another section about ALTER AUTHORIZATION earlier to change the owner of an object in a schema, so I said to my ...

This dynamic management object returns the SQL that was saved when a query was executed. This is a very exciting and useful thing to have, as there are dynamic management views that you will be able to use to get statistics for a query, as well as the full SQL for any actively executing query. This is a tremendous leap ahead of the functionality ...

  This object provides very useful stats on how many times an index has been used, locked, waited on, etc. An index in this context can mean several things: a clustered index, heap, index, or a partition of either of these. The grain of the function is down to the partition level, so if you are working with a table that is partitioned into ...

This object gives statistics on how an index has been used to resolve queries. Most importantly it tells you the number of times a query was used to find a single row (user_seeks), a range of values, or to resolve a non-unique query (user_scans ), if it has been used to resolve a bookmark lookup (user_lookups) and how many changes to the index ...

Give space usage of objects in tempdb (most likely this will be extended to more than just tempdb in a future edition of SQL Server.) Can be used to see how and why space is being used in Tempdb, on a file by file basis. Type: View Data: temporal, reflects the current state of the file usage Columns: database_id – identifies the database (relates ...

For the current database, gives you space oriented statistics for each partition of indexes (even if you only have one partion), like row count, page counts, etc. Resembles the sysindexes in previous versions of SQL Server, with more information. Type: view Data: historic, refreshed at server restart Scope: Reset on server restart (or ...