Phil Factor 14 November 2018

Getting Excel Data as JSON: Rainfall and Sheep-Counting

5

Blogs

You might think that it is easy to get JSON data from a spreadsheet, and there are plenty of utilities around that are based on the idea that it is trivial. If such data was strictly tabular, then it might be. The problem is that any conversion tool makes assumptions about the way that data … Read more

5

Blogs

Phil Factor 08 November 2018

Setting up a simple Rest interface with SQL Server

10

Blogs

We’re going to set up a web service for a SQL Server database using node js on a windows server. This is intended for a mobile application, but has a variety of other uses where an ODBC connection isn’t possible. This service is purely done as a demonstration for people with a database background, so … Read more

10

Blogs

Phil Factor 27 September 2018

Extended Events Workbench

20

Database Administration

The Extended Events (or XEvents) feature has been part of SQL Server since 2008, but many database professionals struggle to get started using it. In this article, Phil Factor demonstrates several useful Extended Event sessions that measure just one thing in each. He then provides the code necessary to parse the resulting XML into something you can use.… Read more

20

Database Administration

Phil Factor 13 August 2018

Spoofing Data Convincingly: Doing the Shuffle and Zip

3

Blogs

Having spent a lot of my working life trying to preserve the integrity of data, there was a certain intriguing novelty in the idea of pseudonymizing data. One of the standard techniques of pseudonymization is that of shuffling data columns as though you are shuffling cards. The original values are kept but placed in the … Read more

3

Blogs

Phil Factor 08 August 2018

Spoofing Data Convincingly: Credit Cards

6

Blogs

I haven’t seen a SQL Server table with real unencrypted credit card numbers for several years, and I don’t know of any good reasons to have them stored that way. However, I’ve needed them in the past for testing a web application that had to take credit card details. Generating credit cards in a way … Read more

6

Blogs

Phil Factor 03 August 2018

The Crimes of Chicago: Working with Data in MongoDB

12

NoSQL databases like MongoDB are gaining popularity, but using the right tools for the job at hand is most important. In this article, Phil Factor demonstrates how to work with a MongoDB database and how to use PowerShell with MongoDB so that the process can be automated.… Read more

12

Phil Factor 18 July 2018

Spoofing Data Convincingly: Masking Continuous Variables.

5

Blogs

In any real numeric data from a database , you are only rarely going to see any sort of normal distribution of the values. Sales data will rise and fall according to the time of year and the economic cycle. The date of input of a record will vary with the workload. If you plot … Read more

5

Blogs

Phil Factor 17 July 2018

Spoofing Data Convincingly: Altering Table Data

6

Blogs

When you are developing an existing database, or demonstrating it, you nowadays need pseudonymised data, or even better, completely anonymized data. This data has to look right at first glance, and it needs to have the same distribution as the real data. Although we are yet to tackle continuous variables with complicated distributions such as … Read more

6

Blogs

Phil Factor 12 July 2018

Spoofing Data Convincingly for Database Development. An Introduction

11

Blogs

Many times I’ve been told, by developers who are using live data to develop a database, that it is impossible to anonymise or pseudonymize their data to comply with privacy legislation. One recurring explanation is that one can’t duplicate the distribution of data by faking it. In a sense, this is true, because if you … Read more

11

Blogs

Phil Factor 09 May 2018

Importing JSON Collections into SQL Server

16

T-SQL Programming

Microsoft introduced native support for JSON in SQL Server in version 2016. In this article, Phil Factor shows how to import JSON documents into to SQL Server database tables, even if there is a bit of complexity in the original data.… Read more

16

T-SQL Programming

Phil Factor 10 April 2018

SQL Server Closure Tables

11

T-SQL Programming

SQL Server has a couple of different ways to represent hierarchies, but they do not allow storing any additional information that is important to the relationship between objects. In this article, Phil Factor explains how to use closure tables to overcome this deficiency.… Read more

11

T-SQL Programming

Phil Factor 08 April 2018

SQL Code Smells

38

T-SQL Programming

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

38

T-SQL Programming

Phil Factor 07 March 2018

Using Stored Procedures in SQL Server that return several results.

25

Blogs

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

25

Blogs

Phil Factor 03 March 2018

Scripting the Description of Database Tables Using Extended Properties

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

11

Phil Factor 16 January 2018

An Easier Way of Pivoting Data in SQL Server

24

T-SQL Programming

There are several methods to pivot, or transpose, data in T-SQL, but most are difficult to understand and write. Phil Factor demonstrates how to take advantage of JSON support, introduced with SQL Server 2016, to create a function that makes pivoting data simple.… Read more

24

T-SQL Programming

Phil Factor 12 December 2017

Pivot Rotation and Matrix Transpose in SQL Server: A New Method?

5

Blogs

The question often comes up: how do you pivot data in SQL Server? In looking closer at the question, there are two similar effects that are wanted. One is the equivalent to a pivot table in Excel, the other is a matrix transposition, where a table is simply rotated, as if it were an array, … Read more

5

Blogs

Phil Factor 15 November 2017

The Power of Three and the Wooden Computer

21

Opinion Pieces

The mystery of one of the almost-forgotten pioneers of the computer starts Phil Factor on a quest to explore and simulate in SQL the arithmetic operations of the lost wooden computer of Thomas Fowler. It is a homage to a little-known genius and an illustration of some curious SQL techniques.… Read more

21

Opinion Pieces

Phil Factor 27 October 2017

Importing JSON Data from Web Services and Applications into SQL Server

29

T-SQL Programming

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

29

T-SQL Programming

Phil Factor 12 September 2017

Consuming hierarchical JSON documents in SQL Server using OpenJSON

19

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

19

Phil Factor 24 August 2017

When is the Data Deleted?

6

Editorials

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

6

Editorials

OVERVIEW

BY NEED

BY ROLE

BY INDUSTRY

Phil Factor