Forgotten Features of Sql Server

Well, not literally – these are features I tend to overlook because I don’t often need them. If you’re the same, this may be helpful. It isn’t a complete list (I’ve forgotten the others) but I’ll try to add to it as things come to mind.

Columns with Null Values
If you’re in control of the design you don’t need to have lots of columns with lots of null values. So you may not often need to:
Use Sparse Columns
Create Filtered Indexes
Use Column Sets
A filtered index recently came in very useful as a special column constraint, so I may be using them more in future.

Computed Columns
They’re documented as part of the CREATE TABLE statement. In recent versions of Sql Server they can be indexed, and also defined as PERSISTED, meaning that the data is stored physically. This can have a good effect on performance, and now that storage is so cheap the trade-off may be worth it. A persisted computed column can also be defined as NOT NULL.

In Sql Server 2016 we have the new JSON features but we’re still waiting for the native JSON type. In the meantime computed columns can be a help. I’m working on a blog about that.

Query Performance
This one helps you to increase the use of a table index by tying in other columns:
Create Indexes with Included Columns
The next feature is not likely to be one you’ve forgotten if you’re involved in data warehousing, but you might want to check the new features if you’re working in Sql Server 2016.
Columnstore Indexes Guide

Model Database
The model database is a system database that comes with every on-premises version of Sql Server. It provides the default configuration for any new database created on the server. When I first came across it almost twenty years ago, my development practices didn’t involve much dropping and creating of databases. I’ve ignored it ever since.

I took another look because now that I do drop and create databases in development it’s a handy way to populate a new database with utilities that speed up my coding. There’s still the alternative of scripting the changes in of course, but the handy thing about the model db is that there’s no effort required. The CREATE DATABASE statement brings everything in.

Finally, don’t forget Kendra Little’s excellent video “5 TSQL Features You’re Missing Out On”.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.