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 →

How To Calculate the Number of Week Days Between two Dates

If the start date and end date are both week days, then the total number of week days in between is simply: (total difference in days) - (total difference in weeks) * 2 or DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2 … since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i. 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 (@@FETCHSTATUS=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): Msg 145, Level 15, State 1, Line 4ORDER BY items must appear in the select list if SELECT DISTINCT is specified. 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 →

Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at the Database Layer?

Introduction A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating multiple values into single CSV string columns. For example, taking data like this: FarmerName FruitName  ——————– ———- Farmer Ted Apple Farmer Ted Orange Farmer Fred Orange Farmer Fred Grapes Farmer Fred Grapefruit Farmer Jed Orange  … and returning the results from the database in this format: Farmer FruitList——— ——-Farmer Ted Apple,OrangesFarmer Fred Grapefruit,Grapes,OrangeFarmer Jed Orange Notice that we are only returning one row per Farmer, and the "FruitList" column returned is a concatenated comma-separated list of values in the Fruit column. 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 →