SQL Prompt

SQL Prompt

SQL Prompt Code Analysis: A Hint is Used (PE004-7)

“Because the SQL Server query optimizer typically selects the best execution plan, we recommend that hints be used only as a last resort by experienced developers and database administrators” — Microsoft SQL Server Documentation Really, there should be no discussion about this, because the above warning, in the documentation, summarizes it so well. However, I Read more

SQL Prompt

SQL Prompt Code Analysis: Table does not have clustered index (BP021)

With a few exceptions, every table should have a clustered index. However, they are not always essential for performance. The value of a clustered index depends on the way a table is used, the typical pattern of queries, and how it the table is updated. More important for a table is that it should have Read more

SQL Prompt

SQL Prompt as a Layout Tool: A Survival Guide

Here is some SQL code to reveal the highest-performing salespeople at AdventureWorks and listing their managers. It produces the following resultā€¦ So, the code works, but it is ugly. If I need to understand and improve the code, I’ll first need to get it into a readable form. I have SQL Prompt, so I can Read more

SQL Prompt

Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt

This article is all about using a SQL Prompt snippet to create and run a test harness on a batch of SQL code, to provide performance data. Behind the scenes, the snippet creates and starts an Extended Events event session, inserts and runs the text of the batch of SQL you’ve selected in SSMS, and Read more

SQL Prompt

SQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)

Avoid using theĀ IsNumeric() function, because it can often lead to data type conversion errors, when importing data. SQL Prompt Code Analysis rules include an Execution rule, E1029, which will alert you to use of this function, in your T-SQL. If you’re working on SQL Server 2012 or later, it’s much better to use the Try_Convert()or Read more

SQL Prompt

SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meaningless. Use a Row_Number() window clause instead, if you need to impose a particular order on rows in the Read more

SQL Prompt

SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)

There was a time when column aliases in SQL Server and Sybase were only declared by an assignment expression, and some people are still fond of the old way of doing it, in much the same way as some of us still like line-dancing, or singing madrigals dressed in tights. Nowadays, the other relational database Read more

SQL Prompt

SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)

The syntax of the TOP clause insists on a bracketed, or parenthesized, expression as its first argument, so that TOP 34 should, strictly speaking be TOP (34), although if you are just supplying a numeric constant, such as 34, as an argument then either style will work. Early incarnations of TOP could use only a Read more

SQL Prompt

Improving legacy code using SQL Prompt code analysis

Code analysis is a useful technique for identifying potential issues in your code base, and ensuring that it adheres to your pre-defined standards, so that you can release your software product with more confidence. SQL Prompt 9.2 and later also allows you to view a whole list of code issues detected in a SQL script, Read more

SQL Change Automation

SQL Code Analysis from a PowerShell Deployment Script

Database code analysis becomes more important as the team doing the database development gets bigger and more diverse in skills. Hard-working database developers sometimes check-in ‘temporary’ development code, by mistake, so it is always good to have a way of flagging up SQL Code issues and ‘smells’ that are agreed to be incompatible with ‘production’ Read more