Product learning

Posts by

Phil Factor

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

8 March 2018

8 March 2018

Investigating problems with ad-hoc queries using SQL Monitor

Phil Factor demonstrates the performance problems that overuse of ad-hoc queries can cause and then how SQL Monitor can warn us when they are the cause of these problems on your SQL Servers, using built-in metrics such as SQL Compilations/sec metric and Batch Requests/sec. Read more

1 March 2018

1 March 2018

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

15 February 2018

15 February 2018

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 to store the value... Read more

3 February 2018

3 February 2018

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

Phil Factor improves SQL Prompt's built-in ALTER TABLE ADD (ata) snippet so that it enforces certain coding standards, such as specifying whether the column accepts NULL values, and ensuring the new column is well-documented. Read more

25 January 2018

25 January 2018

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? Manually, the process can take longer than you expect. Phil Factor demonstrates a simple 3-step approach to reformatting a whole database... Read more

database formatting

Phil Factor

23 January 2018

Phil Factor

23 January 2018

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

17 January 2018

17 January 2018

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

Phil Factor demonstrates the power of placeholders in SQL Prompt Snippets, for selecting and copying text. They make it very easy to build queries by dragging-and-dropping from the SSMS object explorer, and also to save queries as reusable views or inline table valued functions. Read more

SQL Prompt snippets

Phil Factor

4 January 2018

Phil Factor

4 January 2018

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

20 December 2017

20 December 2017

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

There are no advantages to using old-style join syntax. If SQL prompt identifies its use in legacy code, then rewriting the statements to use ANSI-standard join syntax will simplify and improve the code. Read more

14 December 2017

14 December 2017

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

PE001

static code analysis

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Product learning

Posts by

Phil Factor

Investigating problems with ad-hoc queries using SQL Monitor

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

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

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

How to reformat a database in one operation

How to Test SQL Server Functions and Procedures using SQL Prompt

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

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

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

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

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Posts by Phil Factor

Posts by

Phil Factor