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
Index columns, selectivity and equality predicates
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.”
Breaking a String into “Words”
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”.”
Breaking a String into “Words” the CLR way
Jonathan Kehayias uses the CLR to break a string into words
SQL Server 2008 Cumulative Update 3 is available
Aaron Bertrand lets us know that SQL Server 2008 Cumulative Update 3 is available
How It Works: SQL Server Sparse Files (DBCC and Snapshot Databases) Revisited
Sarah Henwood and Bob Dorr explain SQL Server Sparse Files
How It Works: sys.dm_os_buffer_descriptors
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?”
RML Utilities – ReadTrace and how to workaround MARS
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)
Viewing SQL Server Plan Guides
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
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
