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.

Phil Factor

3 February 2018

Phil Factor

3 February 2018

Customizing the SQL Prompt built-in snippets: a better ‘ata’ snippet

Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for parameters, and you can invoke them directly from an SSMS query pane. SQL Prompt also comes with many useful built-in snippets, but sometimes... Read more

Phil Factor

25 January 2018

Phil Factor

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? It can be done, but the process can take longer than you expect. This article will demonstrate a simple 3-step approach... Read more

Phil Factor

23 January 2018

Phil Factor

23 January 2018

How to Test SQL Server Functions and Procedures using SQL Prompt

When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a batch that is attached to the CREATE or ALTER script. This isn’t just to prevent you from... Read more

Phil Factor

17 January 2018

Phil Factor

17 January 2018

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

There are four SQL Prompt snippet placeholders that are all about selection and copying of text: $PASTE$ Inserts the contents of the clipboard at that position. $SELECTEDTEXT$ Inserts the selected text. $SELECTIONSTART$ Indicates where you want the start of the new selection of code after you have executed the snippet $SELECTIONEND$ Specifies the end... Read more

Phil Factor

4 January 2018

Phil Factor

4 January 2018

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

SQL Prompt implements two static code analysis rules to check code for potential misuse of the SET NOCOUNT command: PE008 – SET NOCOUNT OFF is used PE009 – No SET NOCOUNT ON before DML Whenever you execute a query, a short message is returned to the client with the number of rows that are... Read more

Phil Factor

20 December 2017

1 Comment

Phil Factor

20 December 2017

1 Comment

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

SQL Prompt implements a static code analysis rule, ST001, which will check code automatically, during development and testing work, for occurrences of non-ANSI standard JOIN syntax. The “old style” Microsoft/Sybase JOIN style for SQL, which uses the =* and *= syntax, has been deprecated and is no longer used. Queries that use this syntax... Read more

Phil Factor

14 December 2017

Phil Factor

14 December 2017

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

SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE command, without the schema being specified. PE001 – Schema name for procedure is not specified Even where you don’t have to qualify the name... Read more

Phil Factor

16 November 2017

Phil Factor

16 November 2017

Generating test data in JSON files using SQL Data Generator

SQL Data Generator is adept at filling SQL Server databases with ‘spoof’ data, for use during development and testing activities. However, what if instead of a SQL Server database full of fake data, you need a JSON file? Perhaps you need to run some tests in MongoDB, or Azure Cosmos DB. Maybe you need... Read more

Phil Factor

2 October 2017

3 Comments

Phil Factor

2 October 2017

3 Comments

How to record T-SQL execution times using a SQL Prompt snippet

A SQL Prompt snippet is a pre-defined block of code, which we can insert at the current cursor position, or around currently-selected SQL text, in an SSMS query pane. DBAs use them to store all their most commonly-used checks and routines. It’s often useful to have your favorite troubleshooting scripts available to you within... Read more

Phil Factor

25 September 2017

Phil Factor

25 September 2017

Templates and Snippets in SSMS and SQL Prompt

In SSMS, we have access to templates that provide building blocks for creating various types of query, common routines to create various schema objects, or to do common DBA tasks such as running backups. We can also use SSMS T-SQL code snippets to define the basic structure for a block of code or a... Read more

Product learning

Posts by Phil Factor

Posts by

Phil Factor