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.

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…

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…

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”

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s