If you’ve never worked with windowing functions they look something like this:

SELECT name, max(create_date) OVER 
-- No this isn't meant to make sense
(PARTITION BY lower(left(name,1)) ORDER BY owner_sid)
FROM sys.databases;

The other day someone mentioned that you could use ROW_NUMBER which requires the OVER clause without either the PARTITION BY or the ORDER BY parts. So let’s try that out.

-- Create a table to test with
CREATE TABLE Windowing (col1 int, col2 int);
INSERT INTO Windowing 
VALUES (1,1), (1,2), (1,3), (1,4), (1,5)
,(2,6), (2,7), (2,8), (2,9), (2,10);
GO

-- Test ROW_NUMBER without PARTITION BY or ORDER BY
SELECT ROW_NUMBER() OVER (), *
FROM Windowing;

So nope, doesn’t work. But can we get around it?

SELECT ROW_NUMBER() OVER (ORDER BY 1), *
FROM Windowing; 
GO
SELECT ROW_NUMBER() OVER (ORDER BY '1'), *
FROM Windowing; 
GO
SELECT ROW_NUMBER() OVER (ORDER BY DB_ID()), *
FROM Windowing; 
GO

Ok, so you can do it, but it takes some work. No integers, no constants at all, but a function that returns a constant value will do it. Not really sure why you would want to do this, since generally in order for a row number to be useful its done in order of some value. On the other hand, if you absolutely NEED a unique number value per row, don’t have a useful indexed column (a staging table that’s a heap maybe?), and you don’t want to pay for an expensive sort, this could be handy.

Let’s try one more for fun. MAX

SELECT col1, MAX(col2) OVER ()
FROM Windowing;

Now that works! However, now it doesn’t work with a GROUP BY making it less useful.

SELECT col1, MAX(col2) OVER ()
FROM Windowing
GROUP BY col1;

Unlike a simple MAX of course.

SELECT col1, MAX(col2)
FROM Windowing
GROUP BY col1;