EdgeOfKnowWare (2/22/2016)
No, that's not quite what I was trying to do.The problem I had (which lead me to this post via Google) was that I had several different rankings dependent upon several different columns. I didn't want to have to read through the table each time for each column, but I was having the same problem the original poster had. When I was testing my solution, I tried the code you supplied (great idea, by the way), but it didn't give me a full ranking. In the test data that I was using, I wanted ranks from 1-10 for values that IS NOT NULL, but when I used that particular snippet of code, it gave me 1-5 for the columns that had data and no ranking for NULL values. Not quite what I was after.
So, I had to partition the NTILE, one partition for NULL values, and one partition for NOT NULL values. Then, it my select statement for insert, I use the CASE statement. If it is NULL, then don't enter a ranking. If it is NOT NULL, enter a ranking.
Make sense?
Yep that makes sense and what you posted is a good solution. Another way you can do what you are trying to do is like this:
SELECT *, 0,
NTILE(4) OVER (ORDER BY (SELECT NULL)) As Q -- using "ORDER BY (SELECT NULL)" eliminates the sort here
FROM #t
WHERE val IS NULL
UNION ALL
SELECT *, 1,
NTILE(4) OVER (ORDER BY val) As Q -- the POC index will handle sorting here
FROM #t
WHERE val IS NOT NULL;
It's a little more code but will get you the same results as your query but without the need to sort the NULL values (No reason to sort a bunch of NULLs).
If you add a POC index (see this Itzek Ben Gan article for more info about POC indexes), run what I posted along with your solution, you'll see that my solution is able to utilize the POC index and eliminate all the sorts from the query plan. Partitioning by a case statement, on the other hand, will almost certainly guarantee a sort operator which you don't want. Here's all the code with comments:
IF OBJECT_ID('tempdb.dbo.#t', 'U') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t
(
ID INT IDENTITY(1,1) PRIMARY KEY,
val INT NULL
);
INSERT INTO #t SELECT NULL;
INSERT INTO #t SELECT NULL;
INSERT INTO #t SELECT NULL;
INSERT INTO #t SELECT 5;
INSERT INTO #t SELECT 7;
INSERT INTO #t SELECT 8;
INSERT INTO #t SELECT 9;
INSERT INTO #t SELECT 9;
INSERT INTO #t SELECT 11;
INSERT INTO #t SELECT 12;
-- The P.O.C. Index
CREATE NONCLUSTERED INDEX nc_t_temp ON #t(val) INCLUDE (ID);
GO
-- Run both queries with "Show Actual Execution plan turned on"
-- Original Solution
SELECT *,
CASE WHEN val IS NULL THEN 0 ELSE 1 END,
NTILE(4) OVER (PARTITION BY CASE WHEN val IS NULL THEN 0 ELSE 1 END ORDER BY val) As Q
FROM #t;
-- Alternate solution which will not require any sorting
SELECT *, 0,
NTILE(4) OVER (ORDER BY (SELECT NULL)) As Q -- using "ORDER BY (SELECT NULL)" eliminates the sort here
FROM #t
WHERE val IS NULL
UNION ALL
SELECT *, 1,
NTILE(4) OVER (ORDER BY val) As Q -- the POC index will handle sorting here
FROM #t
WHERE val IS NOT NULL;
Just a little food for thought. 😉
Edit.... Further simplified my solution by replacing "CASE WHEN val IS NULL THEN 0 ELSE 1 END" with a static value.
-- Itzik Ben-Gan 2001