SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Tricks

Just stuff it!

Hugo Kornelis — Thu, 12 Oct 2006 17:50:00 GMT

I’m sure you’ve all heard it, and probably even said it, many times: “SQL Server sucks at string manipulation”. And with good reason – it is true. But not quite as true as many seem to believe.

I notice that many people who complain about SQL Server lacking string manipulation are themselves unaware of the string functions that SQL Server does have. Most know LIKE, LEFT, RIGHT, and SUBSTRING. Some also know CHARINDEX, maybe even REPLACE. But how many of you know and use PATINDEX, REVERSE, QUOTENAME, and LTRIM, to name just a few?

The string function that appears to be the most overlooked has to be STUFF. And yet, this function can prove to be an invaluable tool. I’ll give a short description first, then show some examples of how STUFF can be used for string manipulation that would otherwise be much harder to achieve.

What STUFF does, basically, is cut a specified portion from a string and replace it with a new string. The replacement string can be shorter, longer, or exactly as long as the part that has been cut out. The STUFF function takes four arguments, none of which is optional. The first is of character data type and specifies the input string; the second and third are integers specifying the starting position and length of the substring to remove, and the fourth is the replacement string. Here are some examples to illustrate the use of STUFF:

DECLARE @Test varchar(15)

SET @Test = 'Hugo Kornelis'

SELECT STUFF (@Test, 1, 4, 'I am'), -- Basic usage example

STUFF (@Test, 6, 0, 'SQL '), -- Replaced string can be empty

STUFF (@Test, 2, 10, '') -- Replacement string can be empty

--------------- ------------------- ------

I am Kornelis Hugo SQL Kornelis His

The first real world example of using STUFF is based on a newsgroup question. The question was how to replace a substring of a string. Here’s the simplified and non-working example given by the poster (based on the pubs sample database):

UPDATE authors

SET SUBSTRING(phone,5,3) = '888'

WHERE SUBSTRING(phone,5,3) = '826';

One of the regulars in the group replied with this suggestion:

UPDATE authors

SET phone = REPLACE(phone, '826', '888')

WHERE SUBSTRING(phone,5,3) = '826';

That looks clean and tidy, and runs fine on the data in pubs – but what if one of the authors in pubs has phone number ‘801 826-0826’? Indeed, it would be changed to ‘801 888-0888’ instead of ‘801 888-0826’, as requested.

Another way to do this that I often see recommended (though not in this particular thread) is to use LEFT and SUBSTRING to cut the string in pieces, then mend them together after making the change. Like this:

UPDATE authors

SET phone = LEFT(phone, 4) + '888' + SUBSTRING(phone, 8, LEN(phone) - 7)

WHERE SUBSTRING(phone,5,3) = '826';

That works – but ugh!, how ugly. This would be a lot easier with STUFF:

UPDATE authors

SET phone = STUFF(phone, 5, 3, '888')

WHERE SUBSTRING(phone,5,3) = '826';

Here’s another example. A common question in the newsgroups is how to find individual parts in a string. For instance, a bulk import results in a table of names in the form “Lastname, Firstname”. How to get individual Lastname and Firstname values from this?

For the first part of the string (Lastname in this case), this is easy. Use POSINDEX to find the starting position of the separator, use that to calculate the argument for the LEFT function.

The second part is harder. Answers typically given in the groups to these questions use techniques such as finding the starting position, calculating remaining length and feeding those as parameters to the SUBSTRING (version 1 below); using REVERSE to change the task to another task of finding the first part in a string (version 2 below) or using RIGHT instead of LEFT, with again the use of REVERSE to calculate length (version 3). However, with STUFF (version 4), this becomes much easier! See version 4, below:

CREATE TABLE BadData (FullName varchar(20) NOT NULL);

INSERT INTO BadData (FullName)

SELECT 'Clinton, Bill' UNION ALL

SELECT 'Johnson, Lyndon' UNION ALL

SELECT 'Bush, George';

-- Version 1, using SUBSTRING

SELECT LEFT(FullName, CHARINDEX(', ', FullName) - 1) AS LastName,

SUBSTRING(FullName,

CHARINDEX(', ', FullName) + 2,

LEN(FullName) - CHARINDEX(', ', FullName) - 1) AS FirstName

FROM BadData

-- Version 2, using REVERSE, LEFT, and REVERSE again

SELECT LEFT(FullName, CHARINDEX(', ', FullName) - 1) AS LastName,

REVERSE(LEFT(REVERSE(FullName),

CHARINDEX(' ,', REVERSE(FullName)) - 1)) AS FirstName

FROM BadData

-- Version 3, using RIGHT and REVERSE

SELECT LEFT(FullName, CHARINDEX(', ', FullName) - 1) AS LastName,

RIGHT(FullName, CHARINDEX(' ,', REVERSE(FullName)) - 1) AS FirstName

FROM BadData

-- Version 4, using STUFF

SELECT LEFT(FullName, CHARINDEX(', ', FullName) - 1) AS LastName,

STUFF(FullName, 1, CHARINDEX(', ', FullName) + 1, '') AS FirstName

FROM BadData

You might say that there’s not actually that much difference between versions 3 and 4 above, so why am I so enthusiastic about STUFF? To see that, let’s take this to the next level. What if the names in the imported data are of the form “Lastname, Firstname MiddleInitial”, with MiddleInitial being optional? Below, you’ll find the best I was able to do without STUFF, and than a version with STUFF – still not pretty, but not quite as awful as the first version, I’d say. If anyone is able to write a shorter version, I’d love to hear it!

CREATE TABLE BadData (FullName varchar(20) NOT NULL);

INSERT INTO BadData (FullName)

SELECT 'Clinton, Bill' UNION ALL

SELECT 'Johnson, Lyndon, B.' UNION ALL

SELECT 'Bush, George, H.W.';

-- Version 1, without STUFF

SELECT FullName,

LEFT(FullName, CHARINDEX(', ', FullName) - 1) AS LastName,

SUBSTRING(FullName,

CHARINDEX(', ', FullName) + 2,

CHARINDEX(', ', FullName + ', ',

CHARINDEX(', ', FullName) + 2)

- CHARINDEX(', ', FullName) - 2) AS FirstName,

CASE WHEN FullName LIKE '%, %, %'

THEN RIGHT(FullName, CHARINDEX(' ,', REVERSE(FullName)) - 1)

ELSE '' END AS MiddleInitial

FROM BadData

-- Version 2, with STUFF

SELECT FullName,

LEFT(FullName, CHARINDEX(', ', FullName) - 1) AS LastName,

STUFF(LEFT(FullName, CHARINDEX(', ', FullName + ', ',

CHARINDEX(', ', FullName) + 2) - 1),

1, CHARINDEX(', ', FullName) + 1, '') AS FirstName,

STUFF(FullName, 1,

CHARINDEX(', ', FullName + ', ',

CHARINDEX(', ', FullName) + 2), '') AS MiddleInitial

FROM BadData

As you have seen, learning to use STUFF when appropriate can make hard tasks easy, complex queries simple, and extremely complicated queries somewhat less complicated. This string function is a tool that every SQL Server developer should know. So the next time someone complains how SQL Server is severely lacking adequate string manipulation tools, you know what to do – just tell’m to stuff it!

The prime number challenge – great waste of time!

Hugo Kornelis — Sat, 23 Sep 2006 19:54:00 GMT

No sane person would even consider using SQL Server to construct a list of prime numbers. So just to prove that I’m not sane (as if there could be any doubt!), this post will be about finding prime numbers.

First a bit of history. Ward Pond wrote about efficient ways to populate a table with one million GUIDs. I posted a comment with a slightly more efficient algorithm. And that was quickly followed by a new post from Ward, tweaking my syntax even further. And that’s when Denis the SQL Menace lived true to his name by posting this comment:

“How about the next challenge is to return all 78498 prime numbers between 1 and 1000000?”

Now of course, this is a silly challenge. Not because prime numbers are silly, mind you. They are very useful to mathematicians, and many encryption algorithms wouldn’t even be possible without (large) prime numbers. The silly part is using SQL Server, a data manipulation tool, to calculate prime numbers. If you really need them, code a quick algorithm in a C++ program. Or buy a ready-made list with the first million or so prime numbers. So I attempted to resist the challenge.

Alas – the flesh is weak. So when I saw Ward’s reply to Denis’ challenge, I was no longer able to resist temptation. After all, Ward’s attempt is not only interesting – it is also very long, and apparently (with an estimated completion time of 1 to 2 days!!) not very efficient. I decided that I should be able to outperform that.

My assumptions are that a table of numbers is already available, and that this table holds at least all numbers from 1 to 1,000,000. (Mine holds numbers from 1 to 5,764,801), and that the challenge is to create and populate a table with the prime numbers from 1 to 1,000,000, in as little speed as possible. Displaying the prime numbers is not part of the challenge. For testing purposes, I replaced the upper limit of 1,000,000 with a variable @Limit, and I set this to a mere 10,000. That saves me a lot of idle waiting time!

As a first attempt, I decided to play dumb. Just use one single set-based query that holds the definition of prime number. Here’s the SQL:

DROP TABLE dbo.Primes

CREATE TABLE dbo.Primes

(Prime int NOT NULL PRIMARY KEY)

DECLARE @Start datetime, @End datetime

SET @Start = CURRENT_TIMESTAMP

DECLARE @Limit int

SET @Limit = 10000

INSERT INTO dbo.Primes (Prime)

SELECT n1.Number

FROM dbo.Numbers AS n1

WHERE n1.Number > 1

AND n1.Number < @Limit

AND NOT EXISTS

(SELECT *

FROM dbo.Numbers AS n2

WHERE n2.Number > 1

AND n2.Number < n1.Number

AND n1.Number % n2.Number = 0)

SET @End = CURRENT_TIMESTAMP

SELECT @Start AS Start_time, @End AS End_time,

DATEDIFF(ms, @Start, @End) AS Duration,

COUNT(*) AS Primes_found, @Limit AS Limit

FROM dbo.Primes

--select * from dbo.Primes

This ran in 1,530 ms on my test system. (And in case you ask – I also tested the equivalent query with LEFT JOIN; that took 11,466 ms, so I quickly discarded it). With @Limit set to 20,000 and 40,000, execution times were 5,263 and 18,703 ms – so each time we double @Limit, execution time grows with a factor 3.5. Using this factor, I can estimate an execution time of one to two hours.

That’s a lot better than the one to two days Ward estimates for his version – but not quite fast enough for me. So I decided to try to convert the Sieve of Eratosthenes to T-SQL. This algorithm is known to be both simple and fast for getting a list of prime numbers. Here’s my first attempt:

DROP TABLE dbo.Primes

CREATE TABLE dbo.Primes

(Prime int NOT NULL PRIMARY KEY)

DECLARE @Start datetime, @End datetime

SET @Start = CURRENT_TIMESTAMP

DECLARE @Limit int

SET @Limit = 10000

-- Initial fill of sieve;

-- filter out the even numbers right from the start.

INSERT INTO dbo.Primes (Prime)

SELECT Number

FROM dbo.Numbers

WHERE (Number % 2 <> 0 OR Number = 2)

AND Number <> 1

AND Number <= @Limit

-- Set @Current to 2, since multiples of 2 have already been processed

DECLARE @Current int

SET @Current = 2

WHILE @Current < SQRT(@Limit)

BEGIN

-- Find next prime to process

SET @Current =

(SELECT TOP (1) Prime

FROM dbo.Primes

WHERE Prime > @Current

ORDER BY Prime)

DELETE FROM dbo.Primes

WHERE Prime IN (SELECT n.Number * @Current

FROM dbo.Numbers AS n

WHERE n.Number >= @Current

AND n.Number <= @Limit / @Current)

END

SET @End = CURRENT_TIMESTAMP

SELECT @Start AS Start_time, @End AS End_time,

DATEDIFF(ms, @Start, @End) AS Duration,

COUNT(*) AS Primes_found, @Limit AS Limit

FROM dbo.Primes

--select * from dbo.Primes

The time that Eratosthenes takes to find the prime numbers up to 10,000 is 7,750 ms – much longer than the previous version. My only hope was that the execution time would not increase with a factor of 3.5 when doubling @Limit – and indeed, it didn’t. With @Limit set to 20,000 and 40,000, execution times were 31,126 and 124,923 ms, so the factor has gone up to 4. With @Limit set to 1,000,000, I expect an execution time of 15 to 20 hours.

Time to ditch the sieve? No, not at all. Time to make use of the fact that SQL Server prefers to process whole sets at a time. Let’s look at the algorithm in more detail – after the initial INSERT that fills the sieve and removes the multiples of 2, it finds 3 as the next prime number and removes multiples of 3. It starts removing at 9 (3 squared) – so we can be pretty sure that numbers below 9 that have not yet been removed will never be removed anymore. Why process them one at a time? Why not process them all at once? That’s what the algorithm below does – on the first pass of the WHILE loop, it takes the last processed number (2), finds the first prime after that (3), then uses the square of that number (9) to define the range of numbers in the sieve that are now guaranteed to be primes. It then removes multiples of all primes in that range. And after that, it repeats the operation, this time removing multiples in the range between 11 (first prime after 9) and 121 (11 square). Let’s see how this affects performance.

DROP TABLE dbo.Primes

CREATE TABLE dbo.Primes

(Prime int NOT NULL PRIMARY KEY)

DECLARE @Start datetime, @End datetime

SET @Start = CURRENT_TIMESTAMP

DECLARE @Limit int

SET @Limit = 10000

-- Initial fill of sieve;

-- filter out the even numbers right from the start.

INSERT INTO dbo.Primes (Prime)

SELECT Number

FROM dbo.Numbers

WHERE (Number % 2 <> 0 OR Number = 2)

AND Number <> 1

AND Number <= @Limit

-- Set @Last to 2, since multiples of 2 have already been processed

DECLARE @First int, @Last int

SET @Last = 2

WHILE @Last < SQRT(@Limit)

BEGIN

-- Find next prime as start of next range

SET @First =

(SELECT TOP (1) Prime

FROM dbo.Primes

WHERE Prime > @Last

ORDER BY Prime)

-- Range to process ends at square of starting point

SET @Last = @First * @First

DELETE FROM dbo.Primes

WHERE Prime IN (SELECT n.Number * p.Prime

FROM dbo.Primes AS p

INNER JOIN dbo.Numbers AS n

ON n.Number >= p.Prime

AND n.Number <= @Limit / p.Prime

WHERE p.Prime >= @First

AND p.Prime < @Last)

END

SET @End = CURRENT_TIMESTAMP

SELECT @Start AS Start_time, @End AS End_time,

DATEDIFF(ms, @Start, @End) AS Duration,

COUNT(*) AS Primes_found, @Limit AS Limit

FROM dbo.Primes

--select * from dbo.Primes

The time taken for the 1,229 primes between 1 and 10,000? A mere 266 ms!! With execution times like that, I saw no need to rely on extrapolation – I set @Limit to 1,000,000, hit the Execute button, sat back – and get the following result after 19 seconds:

Start_time End_time Duration Primes_found Limit

----------------------- ----------------------- ----------- ------------ -----------

2006-09-24 00:42:22.780 2006-09-24 00:42:41.750 18970 78498 1000000

From 1-2 days to just under 20 seconds – and this time, I didn’t even have to add an index!

Finally, to top things off, I tried one more thing. I have often read that SQL Server won’t optimize an IN clause as well as an EXISTS clause, especially if the subquery after IN returns a lot of rows – which is definitely the case here. So I rewrote the DELETE statement in the heart of the WHILE loop to read like this:

DELETE FROM dbo.Primes

WHERE EXISTS

(SELECT *

FROM dbo.Primes AS p

INNER JOIN dbo.Numbers AS n

ON n.Number >= p.Prime

AND n.Number <= @Limit / p.Prime

WHERE p.Prime >= @First

AND p.Prime < @Last

AND Primes.Prime = n.Number * p.Prime)

And here are the results:

Start_time End_time Duration Primes_found Limit

----------------------- ----------------------- ----------- ------------ -----------

2006-09-24 00:47:42.797 2006-09-24 00:48:01.903 19106 78498 1000000

Which just goes to prove that you shouldn’t believe everything you read, I guess. <g>

Relating unrelated rows

Hugo Kornelis — Fri, 14 Jul 2006 19:23:00 GMT

A recurring question in the newsgroups is: “How do I output unrelated rows side by side?”

I’ll use the AdventureWorks database as an example. Suppose that my manager wants a report listing, for each order, all products ordered and all reasons the customer has for ordering from AdventureWorks. Here’s a sample of how the report should look.

SalesOrderNumber	ProductID	Reason
SO51318	858	Other
SO31518	870	Price
SO31518	872	NULL
SO31518	997	NULL
SO31519	870	On Promotion
SO31519	999	Price

Getting a list of all products ordered for each order is a trivial affair:

SELECT soh.SalesOrderNumber, sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON sod.SalesOrderID = soh.SalesOrderID

-- For testing, use only six SalesOrders

WHERE soh.SalesOrderID BETWEEN 51315 AND 51320

ORDER BY soh.SalesOrderNumber, sod.ProductID;

And getting a list of all reasons for an order is only slightly more complicated:

SELECT soh.SalesOrderNumber, sr.[Name] AS Reason

FROM Sales.SalesOrderHeader AS soh

LEFT JOIN Sales.SalesOrderHeaderSalesReason AS sohsr

ON sohsr.SalesOrderID = soh.SalesOrderID

LEFT JOIN Sales.SalesReason AS sr

ON sr.SalesReasonID = sohsr.SalesReasonID

-- For testing, use only six SalesOrders

WHERE soh.SalesOrderID BETWEEN 51315 AND 51320

ORDER BY soh.SalesOrderNumber, sr.[Name];

(Note the use of an OUTER JOIN is required, because some orders have no reasons associated with them.)

But how to combine these two lists into a single query? Of course, we all know that the correct answer is: “Don’t. That’s formatting, and formatting is best handled at the front end.” It’s even incredibly easy to do this in the front end. Just open two connections to SQL Server (or get really funky and use MARS on a single connection J), send the two queries above to the server and use the standard balanced line algorithm to process the incoming rows. Easy. And the client will still have time to run some idle cycles while waiting for the next row to come in over your network.

But the correct answer is not always the practical one. What if the manager needs his report NOW, but there’s no one with ASP of VB skills available? In that case, I will use the database for formatting, since I happen to be better in writing SQL than in writing VB. So I will have to combine these two queries to get a single report as output. And that brings me right back to the question HOW to do that.

A common mistake is to just combine the two queries into this single query:

SELECT soh.SalesOrderNumber, sod.ProductID, sr.[Name] AS Reason

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON sod.SalesOrderID = soh.SalesOrderID

LEFT JOIN Sales.SalesOrderHeaderSalesReason AS sohsr

ON sohsr.SalesOrderID = soh.SalesOrderID

LEFT JOIN Sales.SalesReason AS sr

ON sr.SalesReasonID = sohsr.SalesReasonID

-- For testing, use only six SalesOrders

WHERE soh.SalesOrderID BETWEEN 51315 AND 51320

ORDER BY soh.SalesOrderNumber, sod.ProductID, sr.[Name];

But that doesn’t give me the results I need. For example, SalesOrder SO51319 is listed nine times instead of three. This is actually quite logical if you think about it – there are three products included in this order and three reasons, and since I didn’t specify any additional criteria in the JOIN clause, each of the products is combined to each of the reasons for a total of 3 * 3 = 9 rows.

If I want to get just the three rows, I’ll have to extend the JOIN clause so as to ensure that the “first” product gets joined to the “first” reason, the “second” product to the “second” reason, etc. But how do I (or rather: how does the database) know what the “first” product and the “first” reason are? The answer is that I must supply my own definition of “first”, ”second”, etc, then write the SQL to calculate these rankings. For this example, let’s use the order of the ORDER BY clause in the queries above to assign ranks. So the lowest numbered product and the alphabetically first reason get a rank of one, etc.

In SQL Server 2000, calculating a rank required the use of a subquery. SQL Server 2005 introduces the cool new RANK() function that I can use instead. I’ll get straight to the code, both for SQL Server 2005 and for SQL Server 2000, with some comments below.

-- Syntax for SQL Server 2005 only - uses the new RANK() function

SELECT soh.SalesOrderNumber, sod.ProductID, sr.[Name] AS Reason

FROM (SELECT sod1.SalesOrderID, sod1.ProductID,

RANK() OVER (PARTITION BY sod1.SalesOrderID

ORDER BY sod1.ProductID) AS Rank

FROM Sales.SalesOrderDetail AS sod1) AS sod

FULL JOIN (SELECT sohsr1.SalesOrderID, sr1.[Name],

RANK() OVER (PARTITION BY sohsr1.SalesOrderID

ORDER BY sr1.[Name]) AS Rank

FROM Sales.SalesOrderHeaderSalesReason AS sohsr1

INNER JOIN Sales.SalesReason AS sr1

ON sr1.SalesReasonID = sohsr1.SalesReasonID) AS sr

ON sr.SalesOrderID = sod.SalesOrderID

AND sr.Rank = sod.Rank

INNER JOIN Sales.SalesOrderHeader AS soh

ON soh.SalesOrderID = COALESCE (sod.SalesOrderID, sr.SalesOrderID)

-- For testing, use only six SalesOrders

WHERE soh.SalesOrderID BETWEEN 51315 AND 51320

ORDER BY soh.SalesOrderID, COALESCE(sr.Rank, sod.Rank);

-- Syntax for SQL Server 7.0 and up

SELECT soh.SalesOrderNumber, sod.ProductID, sr.[Name] AS Reason

FROM (SELECT sod1.SalesOrderID, sod1.ProductID,

(SELECT COUNT(*)

FROM Sales.SalesOrderDetail AS sod2

WHERE sod2.SalesOrderID = sod1.SalesOrderID

AND sod2.ProductID <= sod1.ProductID) AS Rank

FROM Sales.SalesOrderDetail AS sod1) AS sod

FULL JOIN (SELECT sohsr1.SalesOrderID, sr1.[Name],

(SELECT COUNT(*)

FROM Sales.SalesOrderHeaderSalesReason AS sohsr2

JOIN Sales.SalesReason AS sr2

ON sr2.SalesReasonID = sohsr2.SalesReasonID

WHERE sohsr2.SalesOrderID = sohsr1.SalesOrderID

AND sr2.[Name] <= sr1.[Name]) AS Rank

FROM Sales.SalesOrderHeaderSalesReason AS sohsr1

INNER JOIN Sales.SalesReason AS sr1

ON sr1.SalesReasonID = sohsr1.SalesReasonID) AS sr

ON sr.SalesOrderID = sod.SalesOrderID

AND sr.Rank = sod.Rank

INNER JOIN Sales.SalesOrderHeader AS soh

ON soh.SalesOrderID = COALESCE (sod.SalesOrderID, sr.SalesOrderID)

-- For testing, use only six SalesOrders

WHERE soh.SalesOrderID BETWEEN 51315 AND 51320

ORDER BY soh.SalesOrderID, COALESCE(sr.Rank, sod.Rank);

Both versions of the query use two derived tables. And each of those derived tables contains the output of one of the two queries I started with, plus an additional column to hold the rank (based on the ordering I defined). These two derived tables are the joined together on identical SalesOrderID and identical rank, using a FULL OUTER JOIN. Using a LEFT OUTER JOIN or RIGHT OUTER JOIN would not have been sufficient here, since there are orders with more products than reasons or even no reasons at all (e.g. SO15318, SO15320), but also orders with more reasons than products (e.g. SO51924 [not included in the test set!]). The result of this outer join is then combined (using INNER JOIN – that is safe since each order always has at least one product) with the SalesOrderHeader table to get the order number.

Okay, so we now have got a working solution. Let’s check how it performs, ‘kay? So I removed the WHERE clause from all my queries, added an INTO #Dummy clause to make sure I tested the speed of the server, not the speed of my front end (SSMS) formatting and displaying the data, and enclosed each of the queries in a loop to execute them five times (one execution with cold cache, after using CHECKPOINT, DBCC DROPCLEANBUFFERS, and DBCC FREEPROCCACHE; the other four with hot cache). Here are the rounded results when testing on my system, running SQL Server 2005 on Windows XP Professional. (Note that results on other systems will probably be different!)

Just the two basic queries, assuming that the front end will have ample time to perform the balanced line processing while waiting for the next row to arrive – 3.5 seconds.
The SQL Server 2005 version with the RANK() function – 4 seconds.
The SQL Server 7.0 and later version without RANK() – 14 seconds.

Conclusion: Doing this kind of formatting client side is still preferred. The queries are easier to grasp and hence easier to maintain, and it’s the method that gives the best performance. But if you have to do it server side, make sure to use the new RANK() function if you’re already on SQL Server 2005, rather than the version with subqueries. . Not only are they easier to read, It performs MUCH better than the subqueries that are required for the older versions of SQL Server, and it’s a lot easier to read.