You Cannot Rely Solely on Third-Party Tools

Loving an instrument DESPITE it’s faults, not because it doesn’t have any…

Words: 400

Time to read: ~ 2 minutes

I meant to talk about this a while ago but got caught up in different projects so I’m only getting around to it now.

If you have read some of my posts before then you will know that I am a fan of Redgate’s tools, in particular SQL Prompt.

In most of the cases with Redgate, I appreciate what they do because it is a great learning tool for Accidental DBAs or those just learning SQL Server.

Then I found a problem…

First, let us set up our environment to show what I’m talking about. Let’s use the tempdb database and create a dummy table with 1000 rows.


USE tempdb;
IF OBJECT_ID(N'dbo.DummyTable', N'U') IS NOT NULL
DROP TABLE dbo.DummyTable;
GO
CREATE TABLE dbo.DummyTable (
DummyId int IDENTITY(1, 1)
CONSTRAINT PK_DummyTable PRIMARY KEY,
PartitionId tinyint NOT NULL
);
GO
INSERT INTO dbo.DummyTable (
PartitionId
)
SELECT TOP (1000)
number % 5
FROM [master].dbo.spt_values
WHERE [type] = N'P';
GO
SELECT DummyId,
PartitionId,
ROW_NUMBER() OVER (PARTITION BY PartitionId ORDER BY DummyId DESC) AS LastN
FROM dbo.DummyTable;

InitialDataSelect
Also…no ORDER BY, not ordered by PK

Now, a new learner of SQL comes along with the requirement to find the last 2 rows per PartitionId.

They are diligent and enthusiastic and have just read about Windows Functions. They think to themselves

Wow! This is great! I can do this with Windows Functions!

They also work for a company that has invested in RedGate’s SQL Prompt so they know that they can rely on SQL Prompt to help iron out any inconsistencies in their script.

So they take the SELECT script above and type in WHERE… and the auto complete pops up

WhereAutoComplete
Sweet! Auto-complete!

Our intrepid learner selects that “LastN” option and SQL Prompt auto completes the line to…


SELECT DummyId,
PartitionId,
ROW_NUMBER() OVER (PARTITION BY PartitionId ORDER BY DummyId DESC) AS LastN
FROM dbo.DummyTable
WHERE ROW_NUMBER() OVER (PARTITION BY PartitionId ORDER BY DummyId DESC) <= 2;

They run that code and they get…

Msg 4108, Level 15, State 1, Line 26
Windowed functions can only appear in the SELECT or ORDER BY clauses.

ErrorMessage
Technically they can appear anywhere, they only WORK there though…

Now, we know that they’ll encounter this because the column LastN is a Windows Function which is defined in the SELECT clause, which is after the WHERE clause in the Logical Processing Order

 (1) FROM
(2) ON
(3) JOIN
(4) WHERE  **
(5) GROUP BY
(6) WITH CUBE or WITH ROLLUP
(7) HAVING
(8) SELECT  **
(9) DISTINCT
(10) ORDER BY
(11) TOP

However, the main points are that (1) they don’t yet know this, and (2) they have unfortunately been steered wrong by their tools.

Who you gonna call?

In this case it’s easy, log onto Redgate Hub, head to the Product Forms and then to the SQL Prompt section and log a bug report.

https://forum.red-gate.com/discussion/comment/149726#Comment_149726

I cannot wait until this little leak is locked up!

If you want to know a way around this, then I’d normally use a CTE…


WITH MahQuery AS (
SELECT DummyId,
PartitionId,
ROW_NUMBER() OVER (PARTITION BY PartitionId ORDER BY DummyId DESC) AS LastN
FROM dbo.DummyTable
)
SELECT DummyId,
PartitionId,
LastN
FROM MahQuery
WHERE LastN <= 2;

CTE_WindowsFunction
CTE is right for ME

Done!

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

One thought on “You Cannot Rely Solely on Third-Party Tools”

Leave a Reply

%d bloggers like this: