Here we are with another juicy 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:
Calculating running totals is notoriously slow, whether you do it with a cursor or with a triangular join. It is very tempting to denormalize, to store running totals in a column, especially if you select it frequently.
Recently, I needed to create an index on a 1.5 billion row table. I’ve created some large indexes before, but this was the largest, so I thought I’d share my experience in case anyone was interested
A while back someone posted on the ITKE forum asking what Indexes where, and what they were used for. I put up a quick answer, but I felt that it deserved a more in depth blog post; so here it is.
In the basic view, an Index is a subset of columns from a table. This subset of columns is stored in a sorted order so that the SQL Server can more quickly find the records based on the data in the index.
Once you have been a DBA for any length of time, you will encounter the situation of a new vendor coming in with software that has a database back end. Wherever I am working or helping, I like to interject the idea of “DBA Input” as early into the process as possible and I go through a discussion with them.
I’ve seen this question a few times on the forums, and unfortunately there never is a really good answer that is easy to use. SQL Server 2000 didn’t track this information internally, and even though an internal tracking mechanism was added to SQL Server 2005, it isn’t the easiest thing to get for every database in a large environment
SQL Server Books Online uses the terms super latches and sub-latch to describe them. For example the SQL Server:Latches performance counter group calls them super latches. The DVM that exposes the super latches use the term sub-latch, sys.dm_os_sublatches. They are the same internal structure simply exposed under separate terms.
Interesting post make sure you read the comments
I’d be willing to place a bet that on any given week, I’ll field at least one question on the MSDN Forums that reads a bit like this:
“I need to create a unique key for a table using a Stored Procedure|User-Defined Function. Please do NOT suggest IDENTITY.”
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