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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
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
Our intrepid learner selects that “LastN” option and SQL Prompt auto completes the line to…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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.
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…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
Done!
One thought on “You Cannot Rely Solely on Third-Party Tools”