Blog

SQL Modernization and Cloud Migration Talk in Calgary

On 28 February 2017, I will be speaking at Microsoft Calgary as part of a roadshow for AMTRA Solutions. Lunch will be provided.

You can reserve your place for my session on Eventbrite, and there is no charge. The event starts at 9am.

The venue is at 110 9th Avenue SW (Suite 800), Calgary AB, T2P 0T1.

I will be discussing some of the new features of SQL Server and Azure SQL Database, in the context of modernizing your data platform infrastructure,  and there will be live demos.

After lunch, fellow MVP Kevin Kaminski will be discussing Windows 10 and its place in a modern and secure network. I’ll be staying to watch that session, which you can book for here.

Please stop by!

Testing for Object Existence: CREATE OR ALTER

For the longest time, T-SQL writers have had to wrestle with ways of testing for an object’s existence so that it can either be dropped and recreated, or modified as needed.

Last week we covered the new DROP ... IF EXISTS syntax. This week goes into how we handle changes to objects.

We’ve spent many hours of our lives fighting with an object existence check so that ALTER commands don’t fail.

The common implementation pattern now is to CREATE a dummy object with the appropriate name and then use ALTER to write the actual code. That way, future changes can just be done by using the ALTER keyword.

We can see this in a famous example: sp_WhoIsActive, an extremely popular stored procedure written by Adam Machanic, which I highly recommend installing when setting up a new SQL Server instance.

USE master;
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'sp_WhoIsActive')
EXEC ('CREATE PROC dbo.sp_WhoIsActive AS
SELECT ''stub version, to be replaced''');
GO
...
ALTER PROC dbo.sp_WhoIsActive (...);

Look at all that unnecessary code. It’s messy; it’s prone to errors. There are so many ways to do it, which makes it inconsistent, meaning that automatically searching through a code base isn’t reliable. Additionally, if we miss a single quotation mark, the entire script fails.

In a word: Ugh!

What happens if we forget to run the stub first? What happens if we have an existing object and run the CREATE accidentally, then we have to manually change it to an ALTER

(Side note: I spent a good few minutes one day a few years ago not remembering the ALTER keyword.)

SQL Server 2016 Service Pack 1 has finally added in a feature that many DBAs and database developers have been clamouring for: CREATE OR ALTER.

In the above example, the entire IF NOT EXISTS section can be replaced with:

USE master;
GO

CREATE OR ALTER PROC dbo.sp_WhoIsActive (...);

While this has been a long time coming, causing many anguished cries from people writing T-SQL scripts over the years, we are going to love using this new, small, yet significant, syntax.

Share your best ALTER story with me on Twitter, at @bornsql.

Testing for Object Existence: DROP … IF EXISTS

For the longest time, T-SQL writers have had to wrestle with ways of testing for an object’s existence so that it can either be dropped and recreated, or modified as needed.

This is especially common in the case of temp tables or table variables. If the object already exists when our script runs, the script will fail and leave our workflow in an inconsistent state.

Consider this script:

IF OBJECT_ID('tempdb..#doTheThing') IS NOT NULL
DROP TABLE #doTheThing;

CREATE TABLE #doTheThing (
ImportantColumn1 BIGINT,
ImportantColumn2 TINYINT,
ImportantColumn3 NVARCHAR(255)
);

With SQL Server 2016, we can now do this:

DROP TABLE IF EXISTS #doTheThing;

CREATE TABLE #doTheThing (
ImportantColumn1 BIGINT,
ImportantColumn2 TINYINT,
ImportantColumn3 NVARCHAR(255)
);

DROP ... IF EXISTS can be used on many objects, including DATABASE, FUNCTION, INDEX, PROCEDURE, ROLE, SCHEMA, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER and VIEW.

Share your object existence check nightmares with me on Twitter at @bornsql.

Configuration Manager Shortcut on Windows 10 and Server 2016

This is more for my own reference than anything.

On newer versions of Windows desktop and Windows Server, we may find that the shortcut to SQL Server Configuration Manager is missing.

According to this MSDN article, the reason for this change is that Configuration Manager is a Management Console snap-in:

Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows.

I think this is ridiculous because it does not maintain backward compatibility.

This is especially frustrating because the same article reminds us that all changes to SQL Server services should be managed through the Configuration Manager.

The workaround is to create our own shortcut as follows:

SQL Server Version Path for Shortcut
SQL Server 2008 / R2 C:\Windows\SysWOW64\SQLServerManager10.msc
SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc
SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc

Share your frustrations with the “modern” Windows UI with me on Twitter at @bornsql.

Temporal Tables and Hidden Period Columns

In my November 2015 post, An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean, I wrote:

The HIDDEN property is optional and will hide these columns from a standard SELECT statement for backward compatibility with our application and queries. You cannot apply the HIDDEN property to an existing column.

It turns out that this is no longer true. You can apply the HIDDEN property to an existing period column.

Let’s assume you have a temporal table containing two visible period columns, StartDate and EndDate, which you’d like to hide from a typical SELECT statement.

Using an ALTER TABLE ... ALTER COLUMN statement, simply place the ADD HIDDEN syntax after the period column name(s).

ALTER TABLE [dbo].[Account] ALTER COLUMN [StartDate] ADD HIDDEN;
ALTER TABLE [dbo].[Account] ALTER COLUMN [EndDate] ADD HIDDEN;

You can also remove this flag if you wish, using DROP HIDDEN:

ALTER TABLE [dbo].[Account] ALTER COLUMN [StartDate] DROP HIDDEN;
ALTER TABLE [dbo].[Account] ALTER COLUMN [EndDate] DROP HIDDEN;

This is a great improvement to an already fantastic feature of SQL Server 2016. Thanks to Borko Novakovic for this tip.

If you have any more temporal table tricks you want to share, find me on Twitter at @bornsql.

My surname is NULL

Last Wednesday on Twitter, Abayomi Obawomiye (@SQLAmerica) wrote:

I just met someone with the last name NULL today. I really want to ask if they had issues with the last name but worried might be offensive

Abayomi goes on further to say that the word “NULL” gets replaced with a space in any CSV files generated for reports, which got me thinking about this problem.

Then I realised I had already written about it. We make assumptions on a daily basis when it comes to working with data. In this case, Abayomi assumed that no one’s name could ever be “Null”.

Yes, I’ve made that assumption too.

This sparked a memory in the deep and rusty recesses of my brain. I remember reading an article about someone whose last name is Null. It took me just a few seconds to find the article on Wired Magazine’s website, and the pullout phrase is right in the headline:

Hello, I’m Mr. Null. My Name Makes Me Invisible to Computers

It turns out that Abayomi is not alone with this chance meeting. How many of us consider the possibility that legitimate data in our databases could match reserved keywords?

The author of that article, Christopher Null, has an amusing workaround:

My usual trick is to simply add a period to my name: “Null.” This not only gets around many “null” error blocks, it also adds a sense of finality to my birthright.

In my goal to build systems that are easier to use, that should adapt to the consumer, this is clearly problematic. We shouldn’t be forcing Christopher to change his own name to fit our database model.

How would I go about dealing with this problem? Clearly when exporting data to CSV or generating extracts for other other third-party use, this problem has to be solved.

Delimiters are Good

The easiest way I can think of is for data types to be delimited correctly. When exporting to CSV format, we would ensure that all strings be surrounded by quotation marks. This way, Christopher Null’s record would show up as follows (assuming last name, first name ordering):

"Null","Christopher"

Then the parsing process on the reporting side would import that into whichever system without stumbling over the value.

Another issue raised by Christopher in his Wired article is that his email address is rejected outright by Bank of America because it contains the word null before the @ sign.

First and Last Name columns are bad

I’m going to call myself out on the above example and say that assuming names can fit neatly into two columns is bad. Firstly, Cher and Madonna would complain.

Secondly, Vice Admiral Horatio Nelson, 1st Viscount Nelson, 1st Duke of Bronté, would run out of space before his name could be entered correctly.

This doesn’t even begin to address names that are outside of our mainly Western perspective. In South Africa, I saw names that few developers in North America would consider, making use of all manner of punctuation (including spaces).

My recommendation here is to have a 250-character NVARCHAR column called FullName and leave it at that. If you really want your mail merge software to send “personalised” emails or letters, add an additional PreferredName column, and make sure it’s properly delimited when exporting.

Christopher Null wouldn’t have to bat an eyelid here. It would solve for his problem.

(While I’m on this matter, don’t ask for Gender or Sex. You don’t need it. If you do, you’ll know how to ask properly.)

Email Validation is Stupid

My third rule for ensuring that Nulls can exist in this world with Wests and Obawomiyes is to stop validating email addresses!

Firstly, most developers use a regular expression to do it, which is wrong and terrible and slow, and (clearly) imperfect.

This is a particular bugbear of mine, since I have one character before the @ in my personal email address. Many systems fail when trying to validate it, including WestJet. My own workaround is to create a westjet@ alias so that I can receive email from WestJet.

The best way to validate an email address is to send an email to that address with a link for someone to click on. If they don’t click on the link to close the loop, the email address isn’t valid, and your well-designed system won’t allow them to continue to the next step unless that link is clicked.

Summary

Three simple recommendations could help the Nulls find visibility again and ensure data inserted into a system is not modified unnecessarily.

For more reading on this and similar problems, I recommend visiting Bobby Tables.

If you have any experience in this area, please feel free to contact me on Twitter, at @bornsql .

Temporal Tables and History Retention

I’m a huge fan of Temporal Tables in SQL Server 2016. I first wrote about them, in a four-part series in November 2015, before SQL Server was even released. I don’t always get this excited about new features.

However, it has some limitations. As part of this week’s T-SQL Tuesday, hosted by the attractive and humble Brent Ozar, I have discovered a Microsoft Connect item I feel very strongly about.

Adam Machanic, the creator of an indispensable tool, sp_WhoIsActive, has created a Connect item entitled Temporal Tables: Improve History Retention of Dropped Columns.

As my readers know, temporal tables have to have the same schema as their base tables (the number and order of columns, and their respective data types, have to match).

Where this breaks down is when a table structure has changed on the base table. The history table also needs to take those changes into account, which could potentially result in data loss or redundant columns in the base table.

Adam suggests allowing columns which no longer appear in the base table to be retained in the history table and marked as nullable (or hidden), and should only appear when performing a point-in-time query by referring to the column(s) explicitly.

I have voted for this suggestion, and at the time of writing, it has 16 upvotes. I encourage you to add your voice to this suggestion.

If you have any other suggestions, or wish to discuss temporal tables, please contact me on Twitter at @bornsql .

Data Platform MVP Award

I am pleased to announce that I was awarded the Data Platform MVP Award by Microsoft on 1 January 2017.

Nothing changes with my customers. I will continue to look after you with the same level of trust. Though, I may ask for a plate of cookies at my next meeting.

My friend Kenneth Fisher said it best, when he was awarded in October 2016:

I’ll be honest and say it still hasn’t really sunk in yet. At least in part because I don’t really feel like I’ve done anything special. Mostly I just write a blog.

Thank you to Microsoft and, more specifically, the SQL Server community for recognising my contributions. This is for you.

Note: new blog posts will come out on Wednesdays from now on.

2016 in review

Lots of interesting things happened this year in the world of data.

As 2016 draws to a close, I’d like to encourage you to find a piece of Azure SQL Database, or SQL Server, and play with it, understand it, and teach someone else how it works.

One of the projects I’m looking forward to in 2017, is dealing with a massive volume of data from Internet of Things (IoT) devices. SQL Server seems up to the task.

See you on the other side!

Remote Storage Sync and Restore updated

Recently, I presented my “Back up and Restore your Database with Azure Blob Storage” session to the Bellingham PASS Chapter.

There are two components to the Azure Blob Storage Sync and Restore utility: Sync and Restore.

I wrote the Sync tool to work around difficulties I’d experienced with Azure Blob Storage’s Infinite Lease, and limitations in AzCopy. Lately these problems have been made mostly redundant, but the Restore tool remains extremely valuable, and I use it regularly for testing backups.

I’m happy to announce that new versions of both the sync and restore components have been released today, and they have a new name.

Both include some new features, which I’d like to highlight below, mostly to the Restore tool.

New Features

Point-In-Time Restore

During the Bellingham session, one of the questions asked was whether the Restore tool could perform a point-in-time restore using the RESTORE ... STOPAT keyword.

You can now perform a point-in-time restore using the Restore tool, and it will only download the files from the latest backup that it needs, up to and including that point in time.

Note: This feature only works on the latest available backup. A feature enhancement will be included in a later release to perform a point-in-time restore for any backup, if the requisite files exist.

Download Indicator

It’s handy to know how fast a file is downloading, but there aren’t easy ways to do this on a command-line tool, and it can get confusing in a disaster recovery scenario if you don’t know if the download is stuck.

AzureBlobStorageRestore will now show your download progress, using a rotating cursor similar to one I first saw on the MS-DOS version of Doctor Solomon’s Antivirus.

Big shout-out to Daniel Wolf for his ProgressBar gist (available free under the MIT License).

Sort Order

Previously, the Restore tool would download files from smallest to largest, but this could be disconcerting in a disaster recovery scenario as it might look like files are missing.

Now files can be downloaded in alphabetical order, or the default of smallest to largest.

One More Thing

There’s a lot of emphasis on off-site storage these days, and not everyone uses Azure Blob Storage.

For example, one of my customers has a 5TB file share, mapped using UNC (in the format \\server\share) and wants to copy files there.

What if you already keep tape backups, managed by a company that takes your tapes off-site in a fireproof lockbox, and all you need to do is get the files to a network share to be backed up to tape?

What if you just want to copy your database backup files to a local network share as well as Azure Blob Storage?

What if you know you should be moving your backup files to a network share but keep forgetting to do so?

More importantly, what if your backup files are already on a local network share and you have a disaster recovery situation. Downloading from Azure Blob Storage will take too long, and you just want to restore the latest backup from a local network share.

Introducing File Storage Sync and Restore.

FileStorageSync

Like its older sibling, FileStorageSync synchronises the contents of a local directory with a network share using a UNC path (in the format \\server\share). You can provide a username and password as well, which is convenient for creating scheduled tasks. All other features from AzureBlobStorageSync are included.

FileStorageRestore

Like its older sibling, FileStorageRestore parses all the files in a UNC share, and downloads only the the latest full, differential and transaction log backup files required to perform a restore of your database.

It also includes Point-In-Time Restore (for the latest backup) and Sort Order customisation.

A download indicator is not provided in this version because local network speeds are usually so quick that it would add unnecessary overhead.

All other features from AzureBlobStorageRestore are included.

Note: A future enhancement which restores the latest backup from the UNC network share, without copying the files first, is coming soon. You will be able to restore your database as fast as possible.


If you want to play with the new features, you can find them in the GitHub repository.

This project is released free under the MIT License (free for commercial and non-commercial use), and all you have to do is give me credit.

Find me on Twitter at @bornsql if you have any thoughts. Feature requests can be submitted on the GitHub repository itself.