Category T-SQL

Process XML in SQL Server using OPENXML

Aim: In my last blog post, I recorded a sample query for processing JSON in SQL Server so that I can refer back to it & tailor it to my needs when I need it in future. In this post, I’m going to repeat that exercise for XML. I’ve downloaded a sample of XML from […]

Process JSON in SQL Server using OPENJSON

Aim: Very occasionally I have to process JSON in SQL Server, usually as part of a data warehouse task. Because I don’t do this often, I find I have to rebuild my knowledge of the relevant statement every time. So I’ve decided to record a sample T-SQL query that I can refer back to & […]

DROP IF EXISTS

In SQL Server 2016, DROP IF EXISTS was introduced to T-SQL for certain objects. Below are the ones that I use most frequently, alongside the pre-SQL Server 2016 version for dropping the objects after checking that they exist. Database: — SQL2016: DROP DATABASE IF EXISTS testDatabase; GO — Pre-SQL2016: IF EXISTS (SELECT * FROM sys.databases […]

Generate random data

The following is a quick & easy way to generate some random data in a temp table. It’s not very complex but it can be useful for quick testing purposes. Notes: NEWID creates a UniqueIdentifier datatype with a length of 36 characters (including hyphens). GO x (where x is an integer) will execute the batch […]

Filter Data in Outer Joins

Aim: To clarify the difference between filtering data in a WHERE clause or within the OUTER JOIN. When using an OUTER JOIN, there will be a difference in the results depending on whether the data is filtered in a WHERE clause or within the OUTER JOIN itself. The below queries have been run against a copy […]

Replace Multiple String Patterns

I recently came across an interesting way of using the REPLACE function in SQL Server to replace multiple (different) string patterns. Usually, we use the REPLACE function in the following way if we want to replace multiple string patterns. DECLARE @string AS VARCHAR(MAX)  = ‘This is a banana. It may be found in the fruitbowl. Beside […]

Matrix Multiplication Using SQL

This week I found out how to perform matrix multiplication using SQL. I don’t think I’ve touched matrix multiplication since university so first I gave myself a quick refresher in the mathematics. To multiply two matrices (A x B), e.g. First, determine the size of the resulting matrix which will have the same number of […]

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!