Product articles SQL Prompt Query Performance
Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt
With SQL Prompt and Phil Factor's chk code snippet, you can, with a few clicks, get a list of all the SQL statements executed within a batch, in SSMS, their execution plans, and their execution statistics, such as duration, CPU, logical reads and so on. Read moreSQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)
Data in relational tables is never guaranteed to be returned in a particular order, so adding an ORDER BY to an INSERT INTO statement is meaningless and, pre-SQL Server 2012, could cause performance issues. If you really need to impose a particular order on rows in the table, use a Row_Number() window clause instead. Read moreChoosing Between Table Variables and Temporary Tables (ST011, ST012)
Phil Factor demonstrates the use of temporary tables and table variables, and offers a few simple rules to decide if a table variable will give better performance than a temp table (ST011), or vice-versa (ST012). Read moreWhen to use the SELECT…INTO statement (PE003)
SELECT…INTO is a useful shortcut for development work, especially for creating temporary tables. However, it no longer has a clear performance advantage and should be avoided in production code. It is better to use a CREATE TABLE statement, where you can specify constraints and datatypes in advance, making it less likely that inconsistencies will sneak into the data. Read moreConsider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)
Phil Factor explains why you should prefer use of EXISTS over IN, when comparing data sets using a subquery. While there is no longer any significant performance advantage, using NOT EXISTS will avoid unexpected results when the subquery’s source data contains NULL values. Read moreMisuse of the scalar user-defined function as a constant (PE017)
The incorrect use of a scalar UDF as a global database constant is a major performance problem and should be investigated whenever SQL Prompt spots this in any production code. Unless you need to use these global constants in computed columns or constraints, it is generally safer and more convenient to store the value in an inline table-valued function, or to use a view. Read moreHow to Test SQL Server Functions and Procedures using SQL Prompt
Phil Factor shows how to create a table of input values versus expected results, and then use it to unit test your SQL stored procedures and functions and verify that they always produce the correct results. He uses SQL Prompt to make this task much simpler. Read moreFinding code smells using SQL Prompt: the SET NOCOUNT problem (PE008 and PE009)
Generally, you should prevent rowcount messages being sent, by adding a SET NOCOUNT ON at the start of every stored procedure, trigger and dynamically executed batch. Phil Factor demonstrates, and explains the nuances and exceptions. Read moreFinding code smells using SQL Prompt: procedures that lack schema-qualification (PE001)
SQL Prompt will warn you if it spots use of EXECUTE, without specifying the schema in which the stored procedure resides, because it can cause slower execution times, or even result in running of the wrong procedure. Read moreRedgate Hub
-
Product Articles
Tips and how-to guides for Redgate products
-
University
Easy to follow video courses
-
Events
Join us online, or get sponsored
-
Forums
Ask, discuss, and solve questions about Redgate's tools
-
Community
Develop your skills and meet Redgate Advocates and Friends
-
Simple Talk
In-depth articles and opinion from Redgate's technical journal