Quick! What's the difference between RANK, DENSE_RANK, and ROW_NUMBER?

-- Database by Doug
-- Douglas Kline
-- 10/12/2017
-- Quick! What's the difference between RANK, DENSE_RANK, and ROW_NUMBER?

-- in short, they are only different when there are ties...

-- here's a table that will help show the difference
-- between the ranking functions

-- note the [Score] column, 
-- it will be the basis of the ranking

SELECT   [Name],
         [Score]
FROM     (VALUES  ('A', 100),
                  ('B', 101), 
                  ('C', 101), 
                  ('D', 101), 
                  ('E', 102), 
                  ('F', 103) ) AS Temp([Name], [Score])
ORDER BY [Score] ASC

-- first, ROW_NUMBER

SELECT   [Name],
         [Score],
         ROW_NUMBER() OVER (ORDER BY [Score]) AS [row_number]
FROM     (VALUES  ('A', 100),
                  ('B', 101), 
                  ('C', 101), 
                  ('D', 101), 
                  ('E', 102), 
                  ('F', 103) ) AS Temp([Name], [Score])
ORDER BY [Score] ASC

-- row number gives every item a unique number
-- it does NOT recognize ties
-- no numbers are repeated, no numbers are skipped

-- next, RANK()

SELECT   [Name],
         [Score],
         ROW_NUMBER() OVER (ORDER BY [Score]) AS [row_number],
         RANK()       OVER (ORDER BY [Score]) AS [rank]
FROM     (VALUES  ('A', 100),
                  ('B', 101), 
                  ('C', 101), 
                  ('D', 101), 
                  ('E', 102), 
                  ('F', 103) ) AS Temp([Name], [Score])
ORDER BY [Score] ASC

-- RANK() recognizes "ties" by repeating the same rank value
-- but then skipping to the next row_number
-- note the skip from 2,2,2 to rank of 5

-- now look at dense rank

SELECT   [Name],
         [Score],
         ROW_NUMBER() OVER (ORDER BY [Score]) AS [row_number],
         RANK()       OVER (ORDER BY [Score]) AS [rank],
         DENSE_RANK() OVER (ORDER BY [Score]) AS [dense_rank]
FROM     (VALUES  ('A', 100),
                  ('B', 101), 
                  ('C', 101), 
                  ('D', 101), 
                  ('E', 102), 
                  ('F', 103) ) AS Temp([Name], [Score])
ORDER BY [Score] ASC

-- dense_rank handles ties
-- but does not "skip" ranks
-- note the 2, 2, 2 then 3

-- row_number, rank, and dense_rank 
-- are the SAME when there are no ties

-- to prove it, see this, where the Scores are all unique

SELECT   [Name],
         [Score],
         ROW_NUMBER() OVER (ORDER BY [Score]) AS [row_number],
         RANK()       OVER (ORDER BY [Score]) AS [rank],
         DENSE_RANK() OVER (ORDER BY [Score]) AS [dense_rank]
FROM     (VALUES  ('A', 100),
                  ('B', 101.0), -- added a decimal point 
                  ('C', 101.1), -- to break the ties
                  ('D', 101.2), 
                  ('E', 102), 
                  ('F', 103) ) AS Temp([Name], [Score])
ORDER BY [Score] ASC

-- Database by Doug
-- Douglas Kline
-- 10/12/2017
-- Quick! What's the difference between RANK, DENSE_RANK, and ROW_NUMBER?

One Bit per Atom

IBM has, in the laboratory, been able to store a single bit on a single atom.

http://thehackernews.com/2017/03/atom-data-storage.html

Ordering mixed Alpha and Digit Characters

Suppose you have a VARCHAR column that has a mix of alpha and digit characters. In other words, the values represent different types across rows.

Like this:

SELECT   mixedColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)

Which produces:

So how does this get sorted, by default?

SELECT   mixedAlphaColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedAlphaColumn)
ORDER BY mixedAlphaColumn

Which produces:

This gets ordered based on its ASCII code value. Since all ASCII digit characters come before alpha characters, digits will come before alpha when using ORDER BY.  You can see this with this code:

SELECT   mixedColumn,
         ASCII(LEFT(mixedColumn, 1)) AS [ASCII Value]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY mixedColumn

Which produces:

But what if we want to have the digit characters come after the alpha characters?

We could do this:

SELECT   mixedColumn,
         ASCII(LEFT(mixedColumn, 1)) AS [ASCII Value]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY mixedColumn DESC

Which produces this:

But what if we want the digits ordered ascending, and the alpha ordered ascending, but the alpha before the digits?

Here's the trick - the ISNUMERIC function.

SELECT   mixedColumn,
         ISNUMERIC(mixedColumn) AS [isNumeric]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY ISNUMERIC(mixedColumn),
         mixedColumn

Which produces:

And of course, we don't need to display the isNumeric column:

SELECT   mixedColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY ISNUMERIC(mixedColumn),
         mixedColumn

Which produces:

The ISNUMERIC function became available in SQL 2008. However, be careful, since certain characters that you might expect to be alpha are actually interpreted as digit, e.g. periods, commas, plus sign, etc.

Can I use NULL with the IN operator?

Can I use NULL with the IN operator?

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 9/8/2016
-- the IN operator and NULL
-- aka Can I use IN with NULL?



-- the short answer is no
-- but why?

-- a couple of simple examples

SELECT 1
WHERE  NULL IN (1, 2, 3)

-- notice that NULL is not IN a list that contains a NULL

SELECT 1
WHERE  NULL IN (NULL, 1, 2, 3)

-- ok, let's look at a table I have

USE ReferenceDB


-- this is very close to what I saw in a piece of code
SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '')


-- so are there zero-length strings? yes
SELECT  lastname,
        email
FROM    Person
WHERE   email = ''

-- are there NULLs? no?
SELECT  lastname,
        email
FROM    Person
WHERE   email = NULL

-- ah, for test for a NULL value, you need the IS operator
SELECT  lastname,
        email
FROM    Person
WHERE   email IS NULL

-- and more proof...
SELECT 1
WHERE  NULL = NULL

SELECT 1
WHERE  NULL IS NULL

-- but is an IN *really* just a translation of a sequence of OR'ed equalities?

SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '')

SELECT  lastname,
        email
FROM    Person
WHERE   email = NULL
   OR   email = ''

SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '', 'fred')

-- so, the query optimizer is smart enough to
-- remove the NULL from the IN list, since it doesn't matter