Phil Factor 24 December 2018

Saving Data to Various Formats, Using SQL

9

T-SQL Programming

You have many options when exporting data from a database. In this article, Phil Factor compares several methods including XML and array-in-array JSON for speed and file size.… Read more

9

T-SQL Programming

Phil Factor 17 December 2018

Constraining and checking JSON Data in SQL Server Tables

8

Blogs

So you have a database with JSON in it. Can you validate it? I don’t mean just to ensure that it is valid JSON, but ensure that the JSON contains values that are legitimate. Are NI values, postcodes or bank codes valid? Can the dates or GUIDs be successfully parsed? Are those integers really integers? … Read more

8

Blogs

Phil Factor 05 December 2018

Producing Data and Schemas in JSON array-of-array format.

4

Blogs

JSON was initially designed for the informal transfer of data that has no schema. It has no concept of a table, or of an array of identical arrays. This means that it must tell you each key for each object, even if the original data object was a table. With the happy minimum of key-value … Read more

4

Blogs

Phil Factor 25 November 2018

Transferring Data with JSON in SQL Server

16

T-SQL Programming

JSON is a viable option for transferring data between systems. It has the ability to include schema information along with the data which is an advantage over CSV files. In this article, Phil Factor demonstrates how he takes advantage of JSON when exporting or importing tables. … Read more

16

T-SQL Programming

Phil Factor 16 November 2018

How to validate JSON Data before you import it into a database.

6

Blogs

If you are, as you should be, checking JSON data in a whole lot of files before you import them into your database, you would do well to use JSON Schema, because you can run a number of checks such as regex checks that can’t be done any other way, and it is usually possible … Read more

6

Blogs

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

12

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

12

Blogs

Phil Factor 27 September 2018

Extended Events Workbench

24

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

24

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

17

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

17

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

39

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

39

T-SQL Programming

Phil Factor 07 March 2018

Using Stored Procedures in SQL Server that return several results.

30

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

30

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

OVERVIEW

BY NEED

BY ROLE

BY INDUSTRY

Phil Factor