Here we are with another superfunkycalifragisexy episode of the SQL Friday, The Best SQL Server Links Of The Past Week show
Here is what I found interesting this past week in SQL Land:
Kalen Delaney shows us that each table in SQL Server 2008 can contain a maximum of 999 nonclustered indexes, and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.
The official Microsoft definition of a HEAP is “a table without a clustered index.” As simple as it can get. It doesn’t matter if the table has 0 or 10 non-clustered indexes, as long as there is no clustered index on the table, it’s called a HEAP. And let me say it another way “any table that is not associated with a clustered index is called heap”
I enjoyed this post because I myself have to deal with people like this every day in forums
Gail Shaw writes “There’s a common piece of advice given about columns in an index key that says that the most selective column should go first. I’m not going to say that’s incorrect, because it’s not. The problem is that it’s often given without any explanation as to why the most selective column should go first, nor are the other considerations for index key order mentioned.
This can lead to misunderstandings like, in the extreme case, where one person after hearing that advice went and added the primary key column as the leading column of every single nonclustered index (because it’s highly selective), and then wondered why his database performance decreased dramatically.”
Aaron Alton writes: “Occasionally on the MSDN forums, someone has the requirement to break out a string into words, one word per line. The poster usually says “I have the requirement to break out a string into words, one word per line”.”
Jonathan Kehayias uses the CLR to break a string into words
Aaron Bertrand lets us know that SQL Server 2008 Cumulative Update 3 is available
Sarah Henwood and Bob Dorr explain SQL Server Sparse Files
Answer to the question: I am counting pages in the buffer pool using ‘Select count(*) from sys.dm_os_buffer_descriptors’ and I get 6,460 buffers but when when I look at the Buffer Node:Database pages counter it shows 6,599. Why the difference?”
We’ve lately gotten several questions from users regarding ReadTrace and how to workaround when your input trace files contain MARS (Multiple Active Result Sets)
Denny Cherry explains how to view SQL Server Plan Guides
That is it for this week, I will tag the weekly posts with SQL Friday in case you want to see the whole archive in the future