SQL Server 2012 introduced IIF and CHOOSE functions and I completely missed they even existed until recently. They make some quite messy CASE statements go away. Lets have a look…

Let’s imagine we have a need to display something different in our select depending on the value of a field in a table. In the case of this example I’ll use a variable rather than a table. If my variable is equal to ‘Test’ then I want to return ‘Woop’ if it’s not I’ll return ‘Oh No’. I’ve always used CASE statements for this previously and that looks a bit like this…

DECLARE @Testing NVARCHAR(4) = 'Test'
SELECT CASE WHEN @Testing = 'Test' THEN 'Woop' ELSE 'Oh No' END

This is fine but lets see if we can reduce some of the clutter with IIF. IIF takes an expression and true/false values, If the expression evaluates to true then the true value is returned else it’s the false value…

DECLARE @Testing NVARCHAR(4) = 'Test'
SELECT IIF(@Testing = 'Test','Woop','Oh No')

Let’s now turn our heads to the true function. Time for another example…. Let’s imagine we have a forum and we store the amount of posts each user has made, we then convert that into a level 1-5. We then want to return a status for each user depending on their level. Using a CASE statement we could do this…

DECLARE @UserLevel INT = 4
SELECT 
	CASE @UserLevel 
	WHEN  1 THEN 'Newbie'
	WHEN 2 THEN 'Regular'
	WHEN 3 THEN 'Chatterbox'
	WHEN 4 THEN 'Solcial Butterfly'
	WHEN 5 THEN 'Admin'
END

The CHOOSE statement can massively reduce the code needed to do this, It takes an index and a list of items, it then returns the item at the specified index or NULL if there is no item at that index…

SELECT CHOOSE(4,'Newbie','Regular','Chatterbox','Solcial Butterfly','Admin')

The REPLACE function in SQL Server has until now been quite limited. SQL Server 2017 has introduced a new TRANSLATE function that addresses many of these shortcomings, namely the need to daisy chain replace calls to replace multiple characters.

These two functions are however quite different in how they work and what they output.

• REPLACE will replace all occurrences of a set of characters with another set of characters.
• TRANSLATE will one by one replace each character in the match expression with the character in that same position on the replace side. This means both expressions need to be the same length

Let’s look at some examples…

SELECT REPLACE('testinf','testinf','testing')
SELECT TRANSLATE('testinf','testinf','testing')

In the above example both functions will return ‘testing’, so what’s so good about translate? Let’s take something a bit more complex so we can see when translate makes more sense. Imagine we want to swap all periods and commas with a space…

Using REPLACE that would look like this…

SELECT REPLACE(REPLACE('testing, 123.','.',' '),',',' ')

Using replace for this gets pretty ugly as we have to chain replace methods. I’ve seen procedures that chain several or more replaces to clean data. Let’s look at how this is done with replace…

SELECT TRANSLATE('testing, 123.','.,','  ')

Much neater, each character in the match expression is matched with a space on the replace side and swapped out.

The ability to export a script of database objects has for a long time been a feature of SQL Server Management Studio. With the new OSS cross platform tooling from Microsoft we can now have ths feature everywhere. mssql-scripter allows you to export schema and or data from SQL Server from pretty much any terminal

I do a lot of my SQL Server sandbox stuff on macOS using a Docker image of SQL Server 2017 with VS Code and the mssql plugin as my editor. With the new mssql-scripter tool I can now export SQL scripts of my database objects and data directly from the VS Code terminal.

For example here is my VS Code window listing all the databases in my Docker hosted SQL Server 2017 server…

From here I can open the terminal in VS Code and run the mssql-scripter tool to generate a new SQL Script called InitialDb.sql in my working directory…

Once this has run I get my new script file and inside is the SQL Script to create that database and all it’s objects…

This tool supports a great deal of arguments for specifying what you want to script from the database. For example we can Script Schema only, data only, Schema and Data then we can go down to exactly what types of objects to script for example Statistics, logins etc….

To get a full list of what mssql-scripter supports at the console run…

mssql-scripter –help

Here’s a small set of the arguments that –help lists…

Instructions for installing mssql-scripter on each platform can be found on their GitHub installation page