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…
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 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.
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.
Run the following command to pull down the SQL Server Docker image
docker pull microsoft/mssql-server-linux
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…
-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”
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
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…
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…
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.
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.
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…
Notice the match syntax here
We’re saying for the Person Node follow al Friend edge links to person entires.
We can get a list of Friends of Friends by then matching on friend again…
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…
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…
So our graph now looks a bit like this…
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..
As expected we get one result as Jessie is Claire’s only friend made before 2016.