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.

3 August 2018

3 August 2018

Practical PowerShell Processes with SQL Change Automation

Starting from the object-level source code for a database, Phil Factor shows how SCA can create a NuGet build package, use it to migrate target database to the same version, and publish web-based database documentation, a report of what changed on the target and the results of a static code analysis assessment. Read more

Nuget

automated database deployment

25 July 2018

25 July 2018

SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)

If you declare a column alias using equals SQL Prompt will raise a violation of a style rule (ST002). It's best to follow the ANSI-SQL92 standard of assigning aliases using the AS keyword. If the alias is not a standard identifier, delimit it with double quotes. using single quotes has long been deprecated (DEP021). Read more

12 July 2018

12 July 2018

SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)

If you want to use TOP with an expression or subquery, or in INSERT, UPDATE, MERGE, and DELETE statements, then use of brackets is required, so it's a good habit to adopt everywhere. Read more

4 July 2018

4 July 2018

Deploying Multiple Databases from Source Control using SQL Change Automation

How to automatically build multiple test databases, from source control, and then fill them with standard test data sets, using SQL Change Automation, BCP and some PowerShell magic. Read more

Source control

automated database deployment

Phil Factor

29 June 2018

Phil Factor

29 June 2018

SQL Change Automation with PowerShell Scripts: getting up-and-running

Provides a PowerShell automation script that will build a SQL Server database from the scripts in source control, document the database and then deploy it as a NuGet package, using it to update the schema of a live database. Read more

PowerShell

automated database deployment

Phil Factor

22 June 2018

Phil Factor

22 June 2018

SQL Code Analysis from a PowerShell Deployment Script

This article shows how to define SQL code analysis rules using SQL Prompt and them run them automatically from a PowerShell script, displaying the detected code issues in a handy HTML report. Read more

18 May 2018

18 May 2018

Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement (BP006)

Using TOP in a SELECT statement, without a subsequent ORDER BY clause, is legal in SQL Server, but meaningless because asking for the TOP x rows implies that the data is guaranteed to be in a certain order, and tables have no implicit logical order. You must specify the order. Read more

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

static code analysis

SQL code smells

SQL Prompt

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Product learning

Posts by

Phil Factor

Practical PowerShell Processes with SQL Change Automation

SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)

SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)

Deploying Multiple Databases from Source Control using SQL Change Automation

SQL Change Automation with PowerShell Scripts: getting up-and-running

SQL Code Analysis from a PowerShell Deployment Script

Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement (BP006)

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)

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Posts by Phil Factor

Posts by

Phil Factor