Blog

SQL Server 2017 Concatenation With STRING_AGG

Jun 20, 2017

One of the new less publicized features in SQL Server 2017 is STRING_AGG. This new feature can take an expression of string values and concatenate them with a specified separator.

There’s not much more to say about it other than that really, so lets look at an example. Imagine we have a user table and we want to provide our application with a comma separated list of all the usernames in the table…

CREATE TABLE dbo.[User]
(
    Id INT IDENTITY PRIMARY KEY,
    Username NVARCHAR(20)
)

INSERT INTO dbo.[User](Username)
VALUES 
    ('LukeCage'),
    ('JessicaJones'),
    ('BruceWayne')

SELECT STRING_AGG(ISNULL(Username,'Not Specified'),' , ') 
FROM dbo.[User]

This kind of concatenation has always been a bit of a pain before SQL Server 2017, usually achieved using xml methods or variables and coalesce, now it couldnt be any easier.

SQL Server Installed and Running on macOS In Less Than 5 Minutes (With Video)

Jun 19, 2017

SQL Server 2017 is the first version of SQL Server that will also run natively on Linux and on macOS via Docker. There has never been a quicker way to get SQL Server installed and running in a development environment than using this new Docker image.

This is a video of the whole process which on a slow connection and an old MacBook air takes 4 minutes and 7 seconds (This is amazing!)

Let’s run through these steps, you’ll need to have Docker installed to follow along.

  1. Change the Docker settings to use 4gb of RAM as this is a requirement for SQL Server. To do this right click the Docker icon in the menu bar, click preferences and on the advanced tab change RAM to 4gb. Once set click the apply & restart button then wait for Docker to restart.

    Docker Settings

  2. Run the following command to pull down the SQL Server Docker image

    docker pull microsoft/mssql-server-linux

  3. Now we just need to run this image. Let’s also mount a volume so any data we create gets persisted between container restarts this is done by including the -v argument in the Docker run command…

    Docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=(Str0ngP4ss)’ -p 1433:1433 -v sqlvolume:/var/opt/mssql -d microsoft/mssql-server-linux

Let’s break down what this command is doing…

  • -e ‘ACCET-EULA=Y’ : This accepts the EULA and without this SQL Server will not start. This is normally done in the UI For the SQL Server installer but in this case as there is no installer it’s done in the Docker Run command.
  • -e ‘SA_PASSWORD=(Str0ngP4ss)’ = : Here we specify the SA account password that we will use to connect to this server.
  • -p 1433:1433 : This publishes port 1433 on the container to port 1433 on the host to allow us to connect to SQL Server.
  • -v sqlvolume : This tells our container to mount a volume so we can store files in that path and they will be stored on the host to survive restarts of the container (By default containers persist no data and will start in a clean state every time)

At this point we now have a SQL Server instance running in our new container. On macOS the best ways to connect to this are either via VS Code wth the MSSQL extension or SqlCmd for a command line option. Whichever tool you choose you just need to point it to localhost with a username of sa and a password of whatever you specified in your Docker Run command.

For example if you have sqlcmd installed from the link above you can run the following command to query our new SQL Server 2017 instance running inside of docker.

sqlcmd -S localhost -U sa -P “(Str0ngP4ss)” -Q “SELECT TOP 10 Name FROM sys.Objects”

Connecting to SQL with SqlCmd

SQL Server 2017 Graph Data Features In Action

Jun 12, 2017

The Wikipedia Graph Database page has the following definition…

In computing, a graph database is a database that uses graph structures for semantic queries with nodes, edges and properties to represent and store data. A key concept of the system is the graph (or edge or relationship), which directly relates data items in the store. The relationships allow data in the store to be linked together directly, and in many cases retrieved with one operation.

Let’s take a look at a sample use case for a Graph Database to learn wha this means and where it’s useful…

Imagine a system like facebook where friends and friends of friends content can appear in your feed. Representing the friend of friend hierarchy is quite difficult in a relational database, especially when you consider you may then want to go down further levels to recommend friends of friends of friends…. Let’s create a graph schema for this using the new graph features in SQL Server 2017

CREATE TABLE dbo.Person (
  Id INTEGER PRIMARY KEY, 
  FirstName NVARCHAR(100),
  LastName NVARCHAR(100)
) AS NODE;

CREATE TABLE dbo.Friend AS EDGE

Notice the AS NODE on the Person Table, that’s the new syntax that marks this table as a node in our graph. Then note the AS EDGE on the FriendTable, Edge Tables are used to create links between Nodes.

Let’s then create our list of people who at this point have no relationship between them…

INSERT INTO dbo.Person 
VALUES 
    (1,'Claire','Template'),
    (2, 'Luke','Cage'),
    (3,'Jessie','Jones'),
    (4,'Tony','Stark'),
    (5,'Matt','Murdock')

If we then want to define Friend links between our person entries we insert node id’s into the friend table. Let’s imagine we want to link Claire Temple (Id : 1) and Luke Cage (Id : 2) as friends…

INSERT INTO dbo.Friend 
VALUES 
(
    (SELECT $node_id FROM Person WHERE id = 1), 
    (SELECT $node_id FROM Person WHERE id = 2)
);

To get the node id we have to look it up in the Person node table with the inner select. Let’s then also make Claire a friend of Jessie and make Luke a friend of Matt.

INSERT INTO dbo.Friend 
VALUES 
((SELECT $node_id FROM Person WHERE id = 1), (SELECT $node_id FROM Person WHERE id = 3)),
((SELECT $node_id FROM Person WHERE id = 2), (SELECT $node_id FROM Person WHERE id = 5));

In this case we have direct friend links from Claire to Luke and Jessie, then through the link with Luke we have a friend of friend relationship to Matt.

Friend Graph

In this image the circles represent our Node table and the lines the Edge table.

If we want to see all of Claire’s friends we can do this…

SELECT 
    FriendOfPerson.FirstName + ' ' + FriendOfPerson.LastName Friend
FROM 
    Person Person, 
    Person FriendOfPerson, 
    Friend
WHERE 
    MATCH(Person-(Friend)->FriendOfPerson)
    AND person.FirstName='Claire'

Notice the match syntax here

Match(Person-(Friend)->FriendOfPerson)

We’re saying for the Person Node follow al Friend edge links to person entires.

Friends Results

We can get a list of Friends of Friends by then matching on friend again…

SELECT 
    FriendOfFriendOfPerson.FirstName + ' ' + FriendOfFriendOfPerson.LastName FriendOfFriend
FROM 
    Person Person, 
    Person FriendOfPerson, 
    Person FriendOfFriendOfPerson,
    Friend,
    Friend FriendOfFriend
WHERE
    MATCH(Person-(Friend)->FriendOfPerson-(FriendOfFriend)->FriendOfFriendOfPerson)
    AND person.FirstName='Claire'

Friends Of Results

AS you can see the new graph syntax make navigating through different levels of a graph a lot simpler than the many to many representation you’d have if you tried to implement this in a relational database.

Just to flex the graph database features a bit more let’s now imagine that we want to list all the friends Claire and Luke have in common…

SELECT 
    Person.FirstName,
    Person2.FirstName,
    FriendOfPerson.FirstName
FROM 
    Person Person, 
    Person FriendOfPerson, 
    Friend,
    Person Person2, 
    Friend Friend2
WHERE 
    MATCH
    (
        Person-(Friend)->FriendOfPerson<-(Friend2)-Person2
    )
    AND person.FirstName='Claire'
    AND person2.FirstName = 'Luke'

Notice in this match statement we have the flow going both ways -> and <-. This is saying get me all of Claire’s Friends and for each of them get me all their friends where their friends name is luke. The inserts we ran when we created our friend edge records above have no common friends between these two people, try adding a common friend using the insert syntax above and running this query again to see the matches.

In our case we didn’t define any fields in our edge table and it exists purely as an edge. You can however add additional fields to these tables to give more information, for example we could store DateOfFriendship in the Friend Edge table to store the date the edge was created. That would allow us to find all Friend connections made in a specific period. One thing to note here is that you can’t currently update edge records so if any changes you need to make then you will have to delete thr record and re create it. Let’s clear out our Friend table and reinitialize it with this new field and data…

TRUNCATE TABLE Friend

ALTER TABLE Friend ADD DateOfFriendShip DATETIME

INSERT INTO dbo.Friend 
VALUES 
    ((SELECT $node_id FROM Person WHERE id = 1), (SELECT $node_id FROM Person WHERE id = 2),'20160101'),
    ((SELECT $node_id FROM Person WHERE id = 1), (SELECT $node_id FROM Person WHERE id = 3),'20140705'),
    ((SELECT $node_id FROM Person WHERE id = 2), (SELECT $node_id FROM Person WHERE id = 5),'20100605');

So our graph now looks a bit like this…

Graph with edge data

We can then query all Claire’s friends made before 2016 by just adding a where filter on that date column like a normal SQL query..

SELECT 
    FriendOfPerson.FirstName + ' ' + FriendOfPerson.LastName Friend
FROM 
    Person Person, 
    Person FriendOfPerson, 
    Friend
WHERE 
    MATCH(Person-(Friend)->FriendOfPerson)
    AND person.FirstName='Claire'
    AND Friend.DateOfFriendship < '20160101'

As expected we get one result as Jessie is Claire’s only friend made before 2016.


subscribe via RSS