Let me preface this by saying that I am a great admirer of SQL Prompt, from a productivity point of view but also as I think that it could be used as a learning aid for people trying to learn T-SQL.
With that I mind I'd like to point something out...
Given the following code:
USE tempdb;
CREATE TABLE dbo.DummyTable (
DummyId int IDENTITY,
PartitionId tinyint NOT NULL
);
INSERT INTO dbo.DummyTable (
PartitionId
)
SELECT TOP (1000)
number % 5
FROM [master].dbo.spt_values
WHERE [type] = N'P';
SELECT DummyId,
PartitionId,
ROW_NUMBER() OVER (PARTITION BY PartitionId ORDER BY DummyId DESC) AS LastN
FROM dbo.DummyTable;
and a new TSQL user is given a request to find the last 2 rows per PartitionId so they decide to use a Windows Function.
Given the autocomplete features of SQL Prompt, they see the option to use the column LastN in the WHERE clause.
Not only that but SQL Prompt auto-expands this column out to its windows function and
doesn't highlight it as incorrect syntax
Nothing is given to the user to indicate that this isn't supported, the only indication is when they attempt to run the code and it errors out
As I said at the start; I love using SQL Prompt, especially as a teaching tool but I think something should be done about the above. Either not allowing this to be selected as an option in the dropdown options, or highlighting it as an error.
Apologies if this is the wrong place or already highlighted.
UPDATE:
Just a follow up to confirm that this still occurs in
Version 9.0.1.3179.
The new Code Analysis also does not highlight this.
Just a follow up to confirm that this still occurs in Version 9.0.1.3179.
With that I mind I'd like to point something out...
Given the following code:
USE tempdb; CREATE TABLE dbo.DummyTable ( DummyId int IDENTITY, PartitionId tinyint NOT NULL ); INSERT INTO dbo.DummyTable ( PartitionId ) SELECT TOP (1000) number % 5 FROM [master].dbo.spt_values WHERE [type] = N'P'; SELECT DummyId, PartitionId, ROW_NUMBER() OVER (PARTITION BY PartitionId ORDER BY DummyId DESC) AS LastN FROM dbo.DummyTable;and a new TSQL user is given a request to find the last 2 rows per PartitionId so they decide to use a Windows Function.
Given the autocomplete features of SQL Prompt, they see the option to use the column LastN in the WHERE clause.
Not only that but SQL Prompt auto-expands this column out to its windows function and doesn't highlight it as incorrect syntax
Nothing is given to the user to indicate that this isn't supported, the only indication is when they attempt to run the code and it errors out
As I said at the start; I love using SQL Prompt, especially as a teaching tool but I think something should be done about the above. Either not allowing this to be selected as an option in the dropdown options, or highlighting it as an error.
Apologies if this is the wrong place or already highlighted.
UPDATE:
Just a follow up to confirm that this still occurs in Version 9.0.1.3179.
The new Code Analysis also does not highlight this.