SQL Prompt

SQL Prompt

Consider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)

It used to be that the EXISTS logical operator was faster than IN, when comparing data sets using a subquery. For example, in cases where the query had to perform a certain task, but only if the subquery returned any rows, then when evaluating WHERE EXISTS (subquery), the database engine could quit searching as Read more

SQL Prompt

Misuse of the scalar user-defined function as a constant (PE017)

Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to return a global constant: Unless you need to use these as constants in computed columns or constraints, it is generally safer Read more

SQL Prompt

Customizing the SQL Prompt built-in snippets: a better ALTER TABLE ADD (ata) snippet

Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for parameters, and you can invoke them directly from an SSMS query pane. SQL Prompt also comes with many useful built-in snippets, but sometimes we Read more

SQL Prompt

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

SQL Prompt’s Code Analysis feature helps you discover code issues and hidden pitfalls during code development, as you type. It also provides tips for improving your code, and includes links to documentation that offers advice on the about each of the supported rules, so you can decide on the best course of action. This is Read more

SQL Prompt

How to reformat a database in one operation

Inherited a database from another team? Changed your team policy on the way that you format SQL? What’s to stop you formatting the code of an entire database nicely, when you’re developing it? It can be done, but the process can take longer than you expect. This article will demonstrate a simple 3-step approach to Read more

SQL Prompt

How to Test SQL Server Functions and Procedures using SQL Prompt

When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a batch that is attached to the CREATE or ALTER script. This isn’t just to prevent you from making Read more

SQL Prompt

How to use the SQL Prompt snippet placeholders for selecting and copying text

There are four SQL Prompt snippet placeholders that are all about selection and copying of text: $PASTE$ Inserts the contents of the clipboard at that position. $SELECTEDTEXT$ Inserts the selected text. $SELECTIONSTART$ Indicates where you want the start of the new selection of code after you have executed the snippet $SELECTIONEND$ Specifies the end of Read more

SQL Prompt

Finding code smells using SQL Prompt: the SET NOCOUNT problem (PE008 and PE009)

SQL Prompt implements two static code analysis rules to check code for potential misuse of the SET NOCOUNT command: PE008 – SET NOCOUNT OFF is used PE009 – No SET NOCOUNT ON before DML Whenever you execute a query, a short message is returned to the client with the number of rows that are affected Read more

SQL Prompt

Finding code smells using SQL Prompt: old-style join syntax (ST001)

SQL Prompt implements a static code analysis rule, ST001, which will check code automatically, during development and testing work, for occurrences of non-ANSI standard JOIN syntax. The “old style” Microsoft/Sybase JOIN style for SQL, which uses the =* and *= syntax, has been deprecated and is no longer used. Queries that use this syntax will Read more

SQL Prompt

Finding code smells using SQL Prompt: procedures that lack schema-qualification (PE001)

SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE command, without the schema being specified. PE001 – Schema name for procedure is not specified Even where you don’t have to qualify the name of Read more