SQL Server Blog

As with most other people I know, I have been working with several companies lately around security in the wake of the GDPR law changes. As an offshoot of this I figured I would write a few posts surrounding some of the data security features within SQL Server that I’ve been playing with. The first of these is Row Level Security.

This was something which came up recently when discussing storage of XML columns within a SQL Server database and the large amounts of space it can take up. Most of us know about the compression which has been around for years now, those being Row level and Page level compression… but since SQL Server 2016 there has been a new option that you may wish to try.

Also note the use of the word “MAY”. This is a great feature but, as we all know, there’s no such thing as a free lunch which I’ll explain in due course.

This was something I was asked about the other day which I’d not really given any thought to but which was causing others a great deal of confusion…

We’re all aware that the default file type for a SQL Server backup file is “.bak”, but does it have to be?

Well the answer is no. It can actually be anything you like. SQL Server doesn’t care what the file extension is as long as the backup file itself is correctly formatted.

Had I written this post just a few versions ago when in-memory was released as part of SQL Server 2014 then this would have been a very long post indeed. However, I’m pleased to say that, just a few iterations later, this feature has been improved considerably and is definitely something I now consider when looking to re-architect or tune an environment.

Having said that, there are still a couple of flaws that I do need to point out. Well, not flaws, there are design reasons behind them, but they can be an instant red flag to people when considering an in-memory solution.

In my system I’ve reset my configuration to defaults:

Following on from the few posts I’ve written about in-memory tables I wanted to mention this useful tip you can use in order to potentially speed up your temp tables whether that be within stored procedures or maybe within an ETL process.

Let’s look at the example of a stored procedure:

This is a tricky topic because indexing an in-memory table isn’t the same as indexing a disk-based table. With disk based tables we have clustered or nonclustered indexes to work with and these are the indexes we all know and love. They simply order the data we’re interested in and therefore we can easily understand how SQL uses them to seek and scan. However, this is very much not the case with in-memory indexes.

You know what in-memory tables are, and you have your in-memory filegroup, so now you need to know how to create one…

First thing to note is that all in-memory tables MUST have at least one index. With that in mind, let’s jump right in:

I didn’t know whether to place this in its own post or not, but in the end thought it was wiser than hiding this inside another, loosely related, post…

Much as you would like to, you cannot simply open a database and create in-memory objects… SQL Server will give you the following error:

This post has been a long time coming as this was a feature added in SQL Server 2014, just over 4 years ago now. Despite having learnt the ins and outs of the feature at the time and thinking it was a potential great leap forward, facts are that I rarely ever used it in any live situation, therefore other posts always came to the fore instead (as it’s a lot easier to knock up demos of features you’re currently using than trying to invent something completely from scratch with no use case). There’s also a LOT to explain with in-memory as it’s a complex beast and it seemed a waste to write so much about a feature that wasn’t really being used.