By need

By role

Phil Factor

Phil Factor

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 40 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. See also :

Follow on
Phil Factor
08 April 2018
Phil Factor
08 April 2018

SQL Code Smells

16
37
Some time ago, Phil Factor wrote his booklet 'SQL Code Smells', collecting together a whole range of SQL Coding practices that could be considered to indicate the need for a review of the code. It was published as 119 code smells, even though there were 120 of them at the time. Phil Factor has continued to collect them and the current state of the art is reflected in this article. SQL Prompt is committed to cover as many as possible of them. Phil has also updated his book which you can download here.… Read more
16
37
Phil Factor
07 March 2018
Phil Factor
07 March 2018

Using Stored Procedures in SQL Server that return several results.

4
12
Stored Procedures give you more freedom than functions, and so they would be the obvious way of developing processes in SQL Server. There is one longstanding problem with them though, although it is possible to send several results to the application from a stored procedure and read them without problems, you have big problems capturing more … Read more
4
12
Phil Factor
03 March 2018
Phil Factor
03 March 2018

Scripting the Description of Database Tables Using Extended Properties

7
11
Stored procedures, for example, are very easy to document. The comment block at the beginning stays with the code and a CREATE or ALTER script contains everything to reproduce the proc. SQL Server tables, however, are more difficult to document. You can use Extended Properties to document columns and constraints, but working with Extended Properties is difficult at best. Phil Factor demonstrates ways to easily add Extended Properties to your build scripts. … Read more
7
11
Phil Factor
27 October 2017
Phil Factor
27 October 2017

Importing JSON Data from Web Services and Applications into SQL Server

7
25
To support many applications, it makes sense for the database to work with JSON data, because it is the built-in way for a JavaScript or TypeScript application to represent object data. It can mean less network traffic, looser coupling, and less need for the application developer to require full access to the base tables of the database. However, it means that the database must do plenty of checks first before importing. Phil Factor explains how it can be easily done.… Read more
7
25
Phil Factor
12 September 2017
Phil Factor
12 September 2017

Consuming hierarchical JSON documents in SQL Server using OpenJSON

7
14
Over the years, Phil was struck by the problems of reading and writing JSON documents with SQL Server, and wrote several articles on ways of overcoming these problems. Now that SQL Server 2016 onwards has good JSON support, he thought that the articles would be forgotten. Not so, they continue to be popular, so he felt obliged to write about how you can use SQL Server's JSON support to speed the process up.… Read more
7
14
Phil Factor
24 August 2017
Phil Factor
24 August 2017

When is the Data Deleted?

3
6
Imagine that your business is providing a service to individuals, and you charge by the amount of usage. You are trading your service internationally and need to keep a record of who among your customers does what. You then produce invoices and keep accounts. Your customers pay via a third-party service. So far so good. … Read more
3
6
Phil Factor
03 August 2017
Phil Factor
03 August 2017

Statistics in SQL: Student’s t-test

0
6
Many undergraduates have misunderstood the name 'Students' in the t-test to imply that it was designed as a simple test suitable for students. In fact it was William Sealy Gosset, an Englishman publishing under the pseudonym Student, who developed the t-test and t distribution in 1908, as a way of making confident predictions from small sample sizes of normally-distributed variables. As Gosset's employer was Guinness, the brewer, Phil Factor takes a sober view of calculating it in SQL.… Read more
0
6
Phil Factor
01 August 2017
Phil Factor
01 August 2017

Pseudonymization and the Inference Attack

0
10
It is surprising that so much can be identified by deduction from data. You may assume that you can safely distribute partially masked data for reporting, development or testing when the original data contains personal information. Without this sort of information, much medical or scientific research would be vastly more difficult. However, the more useful the data is, the easier it is to mount an inference attack on it to identify personal information. Phil Factor explains.… Read more
0
10
Phil Factor
23 June 2017
Phil Factor
23 June 2017

Is It Time To Stop Using IsNumeric()?

5
22
The old system function IsNumeric() often causes exasperation to a developer who is unfamiliar with the quirks of Transact SQL. It seems to think a comma or a number with a 'D' in the midde of it is a number. Phil Factor explains that though IsNumeric has its bugs, it real vice is that it doesn't tell you which of the numeric datatypes the string parameter can be coerced into, and because it doesn't check for overflow. Phil comes to the rescue with a couple of useful alternatives, one of which works whatever version of SQL Server you have, and which tell you what datatype the string can be converted to.… Read more
5
22
Phil Factor
01 June 2017
Phil Factor
01 June 2017

Statistics in SQL: The Kruskal–Wallis Test

0
4
Before you report your conclusions about your data, have you checked whether your 'actionable' figures occurred by chance? The Kruskal-Wallis test is a safe way of determining whether samples come from the same population, because it is simple and doesn't rely on a normal distribution in the population. This allows you a measure of confidence that your results are 'significant'. Phil Factor explains how to do it.… Read more
0
4
Phil Factor
23 May 2017
Phil Factor
23 May 2017

SQL Server User-Defined Functions

2
15
User-Defined Functions (UDFs) are an essential part of the database developers' armoury. They are extraordinarily versatile, but just because you can even use scalar UDFs in WHERE clauses, computed columns and check constraints doesn't mean that you should. Multi-statement UDFs come at a cost and it is good to understand all the restrictions and potential drawbacks. Phil Factor gives an overview of User-defined functions: their virtues, vices and their syntax.… Read more
2
15
Phil Factor
05 May 2017
Phil Factor
05 May 2017

Visual Checks on How Data is Distributed in SQL Server

1
7
There are many reasons for wanting to know how data is distributed. Sometimes you just want a rough idea of the way that data is distributed in a column. You may think, wouldn't it be nice to have a SQL function that just showed you roughly what the distribution was, graphically, in the results pane. Phil Factor thought that was well and turned the vague wish into reality.… Read more
1
7
Phil Factor
13 April 2017
Phil Factor
13 April 2017

Statistics in SQL: Simple Linear Regressions

3
15
Although linear regressions can get complicated, most jobs involving the plotting of a trendline are easy. Simple Linear Regression is handy for the SQL Programmer in making a prediction of a linear trend and giving a figure for the level probability for the prediction, and what is more, they are easy to do with the aggregation that is built into SQL.… Read more
3
15