Redgate Hub

Topic: Query Performance

SQL Prompt (12)

All databases

All levels

SQL Prompt logo

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

Phil Factor suggests a philosophy of "the SQL query optimizer knows best" when it comes to choosing the right execution plan. Use hints as a last resort, and evaluate them carefully whenever SQL Prompt warns you of their presence in your SQL code. Read more

SQL Prompt logo

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 more

SQL Prompt logo

SQL 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… Read more

SQL Prompt logo

Choosing 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 more

SQL Prompt logo

When 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… Read more

SQL Prompt logo

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

Phil Factor explains why you should prefer use of [NOT] EXISTS over [NOT] 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 more

SQL Prompt logo

Misuse 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… Read more

SQL Prompt logo

How 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 more

SQL Prompt logo

Finding 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 more

SQL Prompt logo

Finding 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 more

Security and compliance

Database monitoring and observability

Database change management

Productivity and workflow automation

AI data readiness

Database modernization

Efficiency and cost optimization

Cloud migration and workload optimization

Redgate Flyway

Redgate Monitor

SQL Toolbelt Essentials

Redgate Hub

Topic: Query Performance

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

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

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

Choosing Between Table Variables and Temporary Tables (ST011, ST012)

When to use the SELECT…INTO statement (PE003)

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

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

How to Test SQL Server Functions and Procedures using SQL Prompt

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

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

2026 State of the Database Landscape

Introduction to PostgreSQL for the data professional

Security and compliance

Database monitoring and observability

Database change management

Productivity and workflow automation

AI data readiness

Database modernization

Efficiency and cost optimization

Cloud migration and workload optimization

Redgate Flyway

Redgate Monitor

SQL Toolbelt Essentials

Topic: Query Performance

Cookies on red-gate.com