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.

11 May 2018

11 May 2018

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 code analysis

Phil Factor

2 May 2018

Phil Factor

2 May 2018

Pseudonymizing your data with SQL Data Generator

Starting from a database view, as the basis for a typical sales reports, Phil factor shows how to generate a data-masked version of this report, which the Tax Men can safely pore over. Read more

18 April 2018

18 April 2018

Avoid use of the MONEY and SMALLMONEY datatypes (BP022)

Use of the MONEY and SMALLMONEY datatypes can lead to unintentional loss of precision, due to rounding errors, during calculations. It is generally far better to use the DECIMAL, a.k.a. NUMERIC, type. Read more

12 April 2018

12 April 2018

Custom Metrics for Detecting Problems with Ad-hoc Queries

Overuse of ad-hoc queries by applications is a common source of SQL Server performance problems. The symptoms include high CPU and memory pressure. Phil Factor offers a simple custom metric to monitor the percentage of ad-hoc queries being executed on a database, and shows how SQL Monitor can detect when the problem is happening,... Read more

19 March 2018

19 March 2018

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 likely that inconsistencies will... Read more

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

Redgate Test Data Manager

Flyway

Redgate Monitor

Overview

Test Data Management

Automate

Monitor

Product learning

Posts by

Phil Factor

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

Pseudonymizing your data with SQL Data Generator

Avoid use of the MONEY and SMALLMONEY datatypes (BP022)

Custom Metrics for Detecting Problems with Ad-hoc Queries

When to use the SELECT…INTO statement (PE003)

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

Redgate Test Data Manager

Flyway

Redgate Monitor

Overview

Test Data Management

Automate

Monitor

Posts by Phil Factor

Posts by

Phil Factor