48 results found
-
Please re-implement rule DEP001
We extensively use this rule in our organization.
1 vote -
SQL Rule/Issue/Error Search
Like SQL Search, but SQL Error/Issue/Rule violation.
This would list all issues that exist in a database.
Example: "Non-scalar subquery in place of a scalar value"Because these issues can cause major slowdown in queries, and it would be great to have a way to search a whole database to find these rather than having to open all the views, procedures, and functions to find all these things.
2 votes -
Detect variable declared in potentially unreachable scope
Unlike a proper programming language TSQL allows you to declare a variable inside an IF block (or some other scope) and then use the variable outside that IF block, even if the IF block is not entered during run time. This can lead to errors if the code outside the IF is testing for certain values.
It would be good if Code Analysis could detect this as a potentially dangerous coding structure.
E.g.
IF 1 = 0
BEGIN
DECLARE @Var VARCHAR(50) = '123';
END;
SELECT @Var1 vote -
New Code Analysis Rule: Flag system named constraints
It is generally bad practice to use system named constraints and there should be a rule for flagging them when there is DDL code creating one.
3 votes -
Check is left and right are the same, and always true: like Where [Column]=[Column]
Check if left and right are the same, and so always true:
like Where [Column]=[Column]
or Where @var1 = @var 1 ..this is a big mistake .. just happened to me ..
5 votes -
Compare the output and speed of two stored procedures
Add a new window that compares the output (and speed) of two stored procedures using the same parameters (ignoring parameters that are missing on either side).
When fixing performance problems or adding new parameters to existing stored procedures, we often need to rewrite large portions.
After successfully speeding it up, we need to test if the results are still correct.
One way to do this is to compare the output of the new procedure with the output of the old one using different parameters. This involves storing results in temp tables, sorting them, output to a text file and using…2 votes -
Debugger for SQL Code
After removing of SQL Code debugging features beginning with Management Studio 18, it would be quite welcome to have this feature in SQLPrompt.
Other competitors are already providing debugging.9 votes -
"Manage Code Analysis Rules..." should also be available from the "Options..."-menu
All setup-options, except "Manage Code Analysis Rules..." seems to also be available from the "Options..." menu, which is nice ...
1 vote -
Warn against reserved words in column names
Consider a table
CREATE TABLE [dbo].[ItemGroups](
[Group] [VARCHAR](25) NOT NULL,
[Description] [VARCHAR](25) NOT NULL)SELECT Group from dbo.ItemGroups ->
Incorrect syntax near the keyword 'Group'.
Would be nice to get a warning when a column name is a reserved word. Square brackets make the syntax valid but unnecessary if a column is named more accurately, e.g. ItemGroupCode
5 votes -
Ability to exclude certain objects (Procedures/Functions/Files...etc.) from being processed for a rule
Currently there is no way to tell Code Analysis to ignore this specific object for a specific rule which has led to several false positives and wasted time re-investigating how to address the issue in the object only to come to the same conclusion as before that this is one of the rare cases where breaking that rule is ok.
My example is we have one stored procedure out of a thousand where the use of a NVARCHAR(2) makes sense and is actually more efficient then switching to a NCHAR(2) for what the code needs to do. Unfortunately the procedure…
4 votes -
Suggest data truncation
Consider I've got a variable of varchar(35)
I perform an update on a column of varchar(10), it should suggest that there will be a truncation
2 votes -
Subquery Column Validation Hint
There's an obscure coding bug that I've seen multiple times in my 20 years of working with SQL Server, and it can be particularly difficult to spot as it doesn't cause a syntax error, yet the havoc it wreaks can be widespread. Consider the following scenario with two tables; they are similar but have differing column names. Now say I want to insert records from StagingTable into FinalTable, but only if they don't already exist there. Yes, there are other ways to handle this like Merge, Left Outer Join etc., but this is still commonly used and you will no…
1 vote -
New code analysis rule: predicate data type agreement
Please create a rule that detects when the data type of input parameters, local variables, and/or expressions do not match the data type of the column in a predicate.
For example, if we have
a Person table, with column LastName varchar(60) NOT NULL, and
in the body of a stored procedure which has an input parameter defined as @LastName nvarchar(100)... and a predicate like WHERE Person.LastName = @LastName [join, apply, correlation, and functions also need this]
It would be wonderful - save me massive amounts of time - if SQLPrompt could flag this and help reduce implicit conversions.
Thank you!
2 votes -
Suggest casting to match data types in joins
In a large query, it can be a hassle to check every join to make sure the data types match up exactly (for example, nvarchar(50) <-> varchar(100)). These differences can have huge impacts on query performance in some circumstances, or lead to confusing results if data types are converted in an unexpected way. A suggestion to call out boolean statements comparing mismatched data types could be extremely useful.
3 votes -
Remove PE019 (Consider using EXISTS instead of IN), or allow it to be disabled
Following the suggestions of rule PE019 (Consider using EXISTS instead of IN) leads to performance issues, by creating correlated subqueries. Even the documentation of the rule admits that there is no longer a performance benefit of doing what is suggested. Please remove the rule or allow the rule to be disabled across a team. At the moment, the disabling of rules is done per developer, and a developer who did not know to switch off this rule, followed the advice, creating a performance problem.
3 votes -
Add Option to Prevent Cross-DB Analysis
Add option to prevent SQL Prompt from looking across DBs for joins. We have DB Servers with 5000 DBs on them. SQL Prompt struggles to try and evaluate possible cross DB joins. It'd be nice to be able to turn that off so that it only tries to do analysis on the currently selected DB.
1 vote -
Code Analysis Selection Background Colour
Could we have the ability to change the background colour when Code Analysis detects an issue with the code. My scheme is black background and light coloured fonts. When code analysis detects an issue, the code becomes unreadable. Below has an example:
https://forum.red-gate.com/discussion/86480/code-analysis-selection-background-colour#latest
3 votes -
Subquery may return more than one row when filtering on primary key.
At the moment a warning is given "Non-scalar subquery in place of a scalar" when trying to assign a variable to the result of a query filtered by the primary key column. In this case only one result can be returned so the warning appears inaccurate and wrapping the result in a TOP 1 { } ORDER BY statement seems an overkill just to remove the warning. This is kind of a special case however where the subquery contains only one table filtered with "=" on the primary key column, but it is a very common one.
Example:
DECLARE @X…6 votes -
PE006 is Too Noisy for Temp table Parallel INSERT
We use the WITH (TABLOCK) hint extensively for loading temp tables - see https://www.sqlshack.com/use-parallel-insert-sql-server-2016-improve-query-performance/
PE006 is far too picky - it yells at every such statement in our high-volume hybrid solution. It would be good to "silence" this kind of rule-breakage so we can see any REAL violations on persisted tables.
Please augment to utterly ignore temp tables.
1 vote -
Provide the option to create custom rules.
Open the Rules Engine API so that we can create our own organizational specific rules.
For example, I would like to be able to flag datetime, getdate(), getutcdate().
12 votes
- Don't see your idea?