Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

How to calculate Median in SQL Server

Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him. -- sample table:create table People(Person varchar(1) primary key,City varchar(10),Age int)go-- with some sample data: insert into People select 'A','Boston',23 union all -- odd #select 'B','Boston',43 union allselect 'C','Boston',29 union allselect 'D','Chicago',15 union all -- single #select 'E','NY',12 union all -- even #select 'F','NY',55 union allselect 'G','NY',57 union allselect 'H','NY',61go-- here's our query, showing median age per city: select city,AVG(age) as MedianAgefrom (select City, Person, Age, ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,COUNT(*) over (partition by City) as CityCountfromPeople) xwherex. Read more →

The Truth about "Cursor Busting" in SQL

Let's say you are called in to troubleshoot a stored procedure that is performing poorly. You dive in to investigate and this is what you find: create procedure ProcessProductsasdeclare @Products cursor, @ProductID intset @Products = cursor for select ProductID from Products order by ProductIDopen @Productsfetch next from @Products into @ProductIDwhile (@@FETCH_STATUS=0)beginexec DoSomething @ProductID fetch next from @Products into @ProductIDenddeallocate @Products Ah ha! Read more →


Here's an obscure piece of SQL you may not be aware of: The "ALL" option when using a GROUP BY. Consider the following table: Create table Sales(SaleID int identity not null primary key,CustomerID int,ProductID int,SaleDate datetime,Qty int,Amount money)insert into Sales (CustomerID, ProductID, SaleDate, Qty, Amount)select 1,1,'2008-01-01',12,400 union allselect 1,2,'2008-02-25',6,2300 union allselect 1,1,'2008-03-02',23,610 union allselect 2,4,'2008-01-04',1,75 union allselect 2,2,'2008-02-18',52,5200 union allselect 3,2,'2008-03-09',99,2300 union allselect 3,1,'2008-04-19',3,4890 union allselect 3,1,'2008-04-21',74,2840SaleID CustomerID ProductID SaleDate Qty Amount----------- ----------- ----------- ----------------------- ----------- ---------------------9 1 1 2008-01-01 00:00:00. Read more →

UNPIVOT: Normalizing data on the fly

Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set. Read more →

Does SQL Server Short-Circuit?

I got an email recently regarding one of my early blog posts from the olden days: Steve Kass wrote about your post:"there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed". Read more →


Let's take a look at another one of those stupid, arbitrary SQL Server error messages that Bill Gates clearly only created because Micro$oft is evil and incompetent and they want to annoy us (and probably kill baby squirrels, too): Read more →

By The Way … DISTINCT is not a function …

Have you ever seen (or written) code like this: select distinct(employeeID), salary from salaryhist That compiles and executes without returning any errors. I've seen that attempted many times over the years, and of course people think DISTINCT is "broken" and "not working" because they see multiple rows for each employeeID. Read more →

A handy but little-known SQL function: NULLIF()

A web application I have inherited uses data from a stored procedure that returns two columns: Description and Override. Basically, for each item returned, the web page should display the Description unless an Override is provided, in which case the Override is displayed. Read more →