SQL Server 2016 – Memory Optimization Advisor

August 8, 2016

Right-clicking on a table in SSMS 2016 includes an option for the ‘Memory Optimization Advisor’. This starts a wizard to assist in migrating a table to an In-Memory table structure. If the base table has any issues that would prevent it from being migrated to an in-memory structure, the wizard will identify and help to resolve those issues. Here’s a link to some issues that would prevent the migration.
A big issue will be tables with foreign keys defined. The keys will have to be dropped and then re-created, they won’t be migrated.
If all checks pass, then the table can be migrated. There is an option to migrate the data as well.

I posted on In-Memory tables for SQL Server 2014, although several changes were made for 2016. 2016 supports foreign keys, check contraints, outer joins, among other features.

SQL Server 2016 – Sample Databases

August 6, 2016

Microsoft has updated their sample database, retiring the old Adventure Works. The new set is for Wide World Importers.
Backups for the new database is at:

There is a standard database, plus a database warehouse version, as well as backups for different versions (Standard, Enterprise, etc.).

SQL Server 2016 – Query Store

July 9, 2016

Just a quick introduction to the Query Store feature. Query Store is a feature new to SQL Server 2016 that will capture data on query performance. Once it is enabled, we’ll have access to dashboards to track query plans as well as statistics on query execution.
I’m using SQL Server 2016 RTM Developer Edition.

Set Up:
Query Store is enabled at the database level. Going to the database properties you’ll see a ‘Query Store’ page. Setting ‘Operation Mode (requested)’ from ‘Off’ to ‘Read Write’ will enable this functionality. You’re also able to set the max space taken up, among other settings.
Once enabled, you can go back to SSMS and you’ll see a ‘Query Store’ tree under the database.

Under the ‘Query Store’ tree in SSMS, we’ll see four entries. These are for available dashboards to return data on our query performance and query plans.
– Regressed Queries: Returns queries that have become worse performing over time. We can select from certain criteria and view query plans to determine issues.
– Overall Resource Consumption: A summary of query stats for Duration, Execution Count, CPU Time and Logical Read over a set period of time.
– Top Resource Consuming Queries: We can select our criteria, such as duration, CPU Time, Physical Reads, etc. and see the worst performing queries by that criteria.
– Tracked Queries: Takes a query ID as a parameter – Returns query plans, the time the query was executed along with the average duration.

Simple Talk

SQL Server 2016 – More New T-SQL Features

June 30, 2016

An earlier post went over some new T-SQL features for SQL Server 2016. Here are two more new string functions that could be useful. I’m using SQL Server 2016 RTM Developer Edition.

The String_Split function will take a delimited list and return each value in a one column table.

declare @List varchar(100) = 'Matt Ryan,Julio Jones,Vic Beasley,Desmond Trufant';
select value from string_split(@List, ',');

String_Split – MSDN

This function will take a string and escape special characters for a certain format. Currently, the only format supported is for JSON.

select STRING_ESCAPE('She said "OK"', 'json');

String_Escape – MSDN

SSMS – New Version Setup

May 31, 2016

June 1 is the release date for SQL Server 2016, so plenty of developers will be downloading and setting up new instances. One pain point is installing a new version of SSMS(SQL Server Management Studio) and getting it configured. I recently upgraded SSMS from 2012 to 2016 RC3 at work so that I could connect to 2014 instances. Here are the steps I went through to customize SSMS after it was installed.

1) Tools=>Options=>Designers=>Table and Database Designers – Uncheck ‘Prevent saving changes that require table re-creation’.
By default, the table designer prevents you from making schema changes to a table that will force the table to be rebuilt.

2) Search and Replace – By default sends results to the ‘Find Results 1’ window – On that window, click the down arrow and select ‘Auto Hide’ to keep the window from displaying next time.
Some people may appreciate this functionality, but this isn’t something I ever use. When searching a script, I want to see the

3) Templates: I use templates as a starting point for several different things, such as creating a new stored procedure or a table. I wrote about templates in an earlier post.
All that’s involved is copying the templates from the old installation to the 2016 directory:
“C:\Users\{User}\AppData\Roaming\Microsoft\SQL Server Management Studio\13.0\Templates\Sql\CS\*.sql”
Where {User} is the Windows user name, and CS is the directory I created for the custom templates. SSMS needs to be closed and re-opened to pick up these changes.

4) Registered servers: The registered servers can be exported to the new edition.
From SSMS 2012 – Right click on your group to export=>Tasks=>Export
Save export file – Select if you want to save user names and passwords with the file.
Saves a regsrvr file.
In new SSMS, right click on Local Server groups=>Tasks=>Import.

5) Toolbar: Select down arrow at end of toolbar then ‘Add or remove buttons’ to select Icons to remove or add to toolbar, to remove anything you won’t use to de-clutter the toolbar.

6) Function Names: I like my function names in lower case. To set up for Intellisense to fill in function names as lower case:
Tools=>Options=>Text Editor=>Transact-SQL=>IntelliSense : ‘Casing for built-in function names’ – Lower Case.

There was one feature I used that wasn’t included in this edition. I frequently export data to a CSV file, so SSMS had a setting to surround any text that contained the delimiter(a comma) with quotes. Hopefully this will be restored in a future update.
1) Tools=>Options=>Query Results=>SQL Server=>Results to grid – ‘Quote strings containing list separators when saving .csv results”

SQL Server 2016 – Always Encrypted

April 27, 2016

One of the new features in SQL Server 2016 is Always Encrypted, which allows us to select certain columns in a table that contain sensitive information that we want to protect. Once we encrypt those columns, the data is protected on disk, in memory, and even in SSMS and SQL queries. The data will be encrypted in the application and will be protected throughout the round trip to the database. This is quite a bit more secure than TDE(Transparent Data Encryption) where the data was only protected on disk.

I’m using SQL Server 2016 CTP 3.
Setup is straightforward. First, we’ll create a table and enter some test data:

if object_id('dbo.EncryptTest') is not null
	drop table dbo.EncryptTest;

create table dbo.EncryptTest(
CustomerId int not null identity(1,1) primary key,
CustomerName varchar(20) not null,
SSN char(9) not null 

insert into dbo.EncryptTest(CustomerName, SSN) values('Customer 1', '000000000');
insert into dbo.EncryptTest(CustomerName, SSN) values('Customer 2', '000000001');
insert into dbo.EncryptTest(CustomerName, SSN) values('Customer 3', '000000002');

select * from dbo.EncryptTest;

In this example, we’ll encrypt the SSN column of the test table using the Encryption Wizard in SSMS.
In SSMS, right-click on table and select ‘Encrypt Columns…’ to start the Wizard.
After the splash page we’ll go to the ‘Column Selection’ screen. We’ll check the SSN column to encrypt. We’ll need to select an Encryption Type, either Deterministic or Randomized. With Deterministic, the same values will result in the same encrypted value, where with Randomized the same values will have different encrypted values. Determinitisic will allow equality operations and grouping, where Randomized may be a little more secure with no patterns to discern.
Here we can also generate a column encryption key or select one that already exists.
The next screen is ‘Master Key Configuration’. This will create a master key to protect the column encryption key. A new master key can be created or an existing one used. This master key is stored outside of the database, so we’ll need to also select somewhere to store it, either the ‘Windows certificate store’ on the local server or use the Azure Key Vault to store it in the cloud.
The next screen is ‘Validation’ where we select to either setup encryption now or to generate a Powershell script to set it up later. The setup needs to occur at a time where the table won’t be in use so that new records won’t be inserted. We’ll go ahead and run the setup now.
Next is the ‘Summary’ screen, so we’ll click ‘Finish’ to start the setup.

After the setup has completed, we can select from our test table and see that the SSN column has been encrypted.

Originally I had a unique constraint set up on the SSN column as well as a check contraint to ensure that 9 characters were entered. When going through the wizard, I had to remove the unique contraint to even be able to select the SSN column for encryption. The first time I got all the way through the wizard the setup failed and gave me an error saying that the encrypted column couldn’t have a check contraint.
In retrospect this makes sense, if SQL Server can’t read the encrypted data then it wouldn’t be able to enforce any sort of contraints on the table other than datatype and nullabllity.



March 30, 2016

When first learning SQL one of the most confusing concepts to understand is when to use WHERE and when to use HAVING in a query. Both keywords are used to filter records so they are similar but each has a situation where it should be used.

Let’s set up a table with some example data:

create table dbo.WidgetInventory (
WidgetId int not null primary key,
WidgetColor varchar(10) not null,
IsUsed bit not null

insert into dbo.WidgetInventory(WidgetId, WidgetColor, IsUsed) 
(1, 'Red', 1),
(2, 'Blue', 0),
(3, 'Red', 1),
(4, 'Yellow', 0),
(5, 'Yellow', 1),
(6, 'Red', 0);

So we have a supply of widgets in different colors. Some are used (IsUsed = 1) and others are new (IsUsed = 0).
Say that we need to write a query to identify used widgets in a particular color that we have more than one of.
Here’s a query to do that:

select WidgetColor, count(*) as WidgetCount
from dbo.WidgetInventory
where IsUsed = 1
group by WidgetColor
having count(*) > 1;

Since we only care about used widgets, we add the ‘where IsUsed = 1’ clause to limit our dataset to only used ones.
Then we group by the color and count the number of used widgets in each color. HAVING is used to operate on the aggregated value, in this case the count of the widgets.
The query result will be (‘Red’, 2) since Red is the only color for used widgets with more than one in stock.


Get every new post delivered to your Inbox.

Join 101 other followers