I don’t know why but for some reason I have never liked using sp_spaceused. It probably started a long time ago when the results were often inaccurate due to the meta data being out of date. I am pretty sure that was fixed somewhere along the line but the system stored procedure had some other limitations ...

There are interesting things to be learned from even the simplest queries.  For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but fewer than ten. One possible query to meet that specification is: SELECT ...

Back in 2008, Marc Friedman of the SQL Server Query Processor Team wrote a blog entry entitled “Distinct Aggregation Considered Harmful”, in which he shows a way to work around the poor performance that often results simply from adding the keyword DISTINCT to an otherwise perfectly reasonable aggregate function in a query.  This post is an ...

You might have noticed that January was a quiet blogging month for me.  Part of the reason was that I was working on a series of articles for Simple Talk, examining how parallel query execution really works.  The first part is published today ...

You probably think something like ''What! Of course we can!''. And so did I when I read the title for a post about this in a forum. Let us first establish that you can have a column alias in ORDER BY:SELECT p.ProductID AS pid,p.ListPrice AS prFROM Production.Product AS pORDER BY pr The problem was that a table name was also in the ORDER BY ...

When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause.  To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause.  Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned. The ...

You might just not get what you think. I would be surprised if this hasn't been blogged already, but if so, it would be worth repeating. Here's the deal (example from a forum,, slightly re-worked): I want the values in one column to be unique, assuming the value in another column is 1. Can I use an UDF for that? On the surface, yes. You can ...

I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write future update scripts when you don’t know the actual name it will be. In any case I decided to ...

Actually, the article has been out for about a month now, but I first solicited feedback on errors and typo's and then I forgot to mention the update here... Anyhow, the article now also covers 2008 and the new date, time, datetime2 and datetimeoffset types. There are some interesting aspects of the new types which sets them apart from the old ...

In my last blog post:  http://sqlblog.com/blogs/andrew_kelly/archive/2008/02/27/when-getdate-is-not-a-constant.aspx I mentioned that I ran across a situation in which GETDATE() used in a SELECT statement occasionally returned more than 1 value for the result set. That sparked quite a debate amongst SQL Server programmers who belonged to ...