SQL Prompt

SQL Prompt

Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8)

In SQL, if you declare a string in any of the four formats, CHAR, NCHAR, VARCHAR or NVARCHAR, without specifying its length, the string is given the length of one character. If you coerce a string, using CAST or CONVERT, and make the same mistake, it gets a length of thirty characters. Why one and Read more

SQL Prompt

The risks of using EXECUTE (‘SQL Script’)

Using dynamic SQL is unavoidable at times, but it is reckless to execute dynamic SQL that is created directly from strings that include values that change at execution time. It can allow SQL Injection and it is also inefficient. SQL Prompt’s code analysis rule, BP013 will alert you to use of Execute(<string>), to execute a Read more

SQL Prompt

How to Apply Non-Standard SQL Formatting Using SQL Prompt

I use SQL Prompt to format my code; I’ve defined my standard style that lays out the code just the way I like it, for most cases. I also have a couple of alternative styles for specific tasks, such as comparing two versions of the same code. Occasionally, however, the standard formatting just won’t work Read more

SQL Prompt

The Whys and Wherefores of Untrusted or Disabled Constraints

Having untrusted or disabled FOREIGN KEY or CHECK constraints isn’t nearly as bad a fault as not having defined any in the first place. However, it does cause issues with data consistency and integrity, since you can’t be certain that ever row of data complies with the conditions of the constraint. Moreover, neither can the Read more

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