How can we help you today? How can we help you today?

Code Analysis feedback - EI003 false positive and too restrive PE006

I got some feedback for the code analysis tool on one of our bigger code bases which for a first use worked great.

1. false positive for rule "EI003" - Non-scalar subquery in place of a scalar

Repro. The analyse trips of the union in the following example

DELETE FROM
dbo.Person
WHERE
PersonID NOT IN (
SELECT
par.Person_ID
FROM
dbo.PersonAccountRole par
WHERE
par.Person_ID IS NOT NULL
UNION
SELECT
Buyer_Decision_Maker
FROM
dbo.TransactionNL
WHERE
Buyer_Decision_Maker IS NOT NULL
)

2. Misplaced warning (IMO) for rule PE006 - TABLE HINT is used

Repro. A readuncommitted hint (nolock) or any other isolation level hint is not in scope of this otherwise good advice.

SELECT * FROM Lookup.Person WITH (READUNCOMMITTED)

A nolock is common use for reports or diagnostic queries.

The description of this rule is

"
It is not a good idea to use any of the rich variety of table hints.
The SQL Server query optimizer usually selects the best index for a join, so these be used only as a last resort by experienced
developers and database administrators.
"

Agreed that, as in the the description, when a MERGE/LOOP/HASH join hint is used a warning is appropriate.

3. Is it possible to disable a rule with a comment so that the rule applies in general but not on some lines?

Kind Regards, Tom


BuckleyRE
0

Comments

5 comments

Sort by
  • way0utwest
    The disable a rule with a comment is a good idea. Passed on to development.
    way0utwest
    0
  • Michael Clark
    Hi @BuckleyRE,

    Thanks for getting in touch. We have logged your first two requests as bugs under references SP-6829 and SP-6830.

    Regarding your question "Is it possible to disable a rule with a comment so that the rule applies in general but not on some lines?", we have an open suggestion on our UserVoice portal to add this functionality.

    https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/32531393-code-analysis-disable-rule-for-specific-cases

    Would it be possible to vote/comment on it there so that we can collect feedback together?

    Best,
    Michael
    Michael Clark
    0
  • BuckleyRE
    I voted on the uservoice site.

    Is it possible to follow up on the bugs?
    BuckleyRE
    0
  • RichardL
    Hi @BuckleyRE

    Thanks for your post.

    The development team is now aware of this and will be assessing it going forward.
    We don't have a definitive time frame set for this to be implemented.
    Instead, please keep an eye on new versions and the release notes for SQL Prompt.

    I hope this information is helpful to you.

    Kind regards
    RichardL
    0
  • BuckleyRE
    Hi Richard. I thought so. Still, if people are willing to improve the product by giving feedback with a (I hope) working repro it would be encouraging to see what gets done with it. Also for the case the dev team considers it, let's say, low prio. Thanks for follow up though. Prompt is leading this space so I'm sure there are very capable people aboard.
    BuckleyRE
    0

Add comment

Please sign in to leave a comment.