SQL Server 2016 Always Encrypted


SQL Server 2016 is yet to be released, but you can download the CTP versions. The mostly anticipated and marveled feature is Always Encrypted. Let me discuss few bits of this feature before getting into the technical side of it.
I got to know this right after reading an article about Microsoft had applied some court case to safeguard the information of one of its users, from the US laws. This is highly appreciated and I personally feel that Microsoft is at the forefront of protecting customer data. And I made a tweet.

If customer can secure their data without any control of public cloud vendors, even in the situations like powerful unauthorized people gaining access to your data results in reduced data theft. And it solves a headache for the public cloud vendors as well.

How it works

SQL Server 2016 Always Encrypted is a feature which allows the encryption and the decryption of the data in the client side, rather than in the database server itself. Since the encryption happens in the client side using a client driver data is secured not only at rest but also at transit, this makes the feature takes its pride name Always Encrypted.

Always Encrypted works as follows.

  • First we have to create Column Encryption Keys (CEK) and Column Master Keys (CMK) in the Always Encrypted section of the database

  • CEKs are symmetric keys and CMKs are asymmetric keys.
  • CEKs are stored in SQL Server where CMKs are stored outside the SQL Server. Read this MSDN article to get information about how to create these keys.
  • Create database with the table and specify the columns to be encrypted. Note that the encryption type (deterministic or randomized), encryption algorithm and the CEK to be used are specified.

  • In the demo I’ve created the CMK in the local certificate store of the machine, but you can keep the CMK wherever possible. Because SQL Server stores only the meta data of the CMK.
  • Now the database is ready. We need .NET 4.6 client application to access the data in the Always Encrypted enabled database. I summarized everything in this image.

  1. Application sends an INSERT statement, driver intercepts the statement and identifies the database it talks is an Always Encrypted feature enabled database. This identification happens because of the connection string property Column Encryption Setting=Enabled. So the driver asks the database to send the details of the encryption for the specific table.
  2. SQL Server returns column details, encrypted values of the CEK, CMK name and the path.
  3. Client driver retrieves the CMK using the meta received from the SQL Server. In this step driver gets the private key of the CMK, which is used to decrypt the encrypted CEK. (CEK is encrypted using CMK’s public key during the creation of CEK in the SQL Server, also the CEK is signed by the private key of the CMK) SQL Server does not store the CMK’s private key.
  4. Client driver encrypts the data using the decrypted CEK and send it to the SQL Server.
  • Read operations also work similar as SQL Server send the encrypted data along with the encryption details and CMK meta data information. Client driver then retrieves the CMK decrypts the CEK and the decrypts the data.
  • Client driver implements possible caching of the keys for performance.

Sample .NET application code for the above table

Management Features

You can see the definitions of the CMKs using this command. SQL Server stores the meta data of the CMKs

You can see the definitions of the CEKs using this command

Joining the above two along with the sys.column_encryption_key_values we can get the association relationship.

You can execute the following command to get the Always Encrypted meta data for the table.

Other useful reads

http://www.infoq.com/news/2015/06/SQL-Server-Always-Encrypted (read the comments for known FAQs)

http://blogs.msdn.com/b/sqlsecurity/archive/2015/06/04/getting-started-with-always-encrypted.aspx (Getting started with Always Encrypted)

http://sqlperformance.com/2015/08/sql-server-2016/perf-impact-always-encrypted (Performance of Always Encrypted)

You can use Azure Key Vault as the CMK store

https://thuru.net/2015/05/30/azure-key-vault-setup-and-usage-scenarios/ (Introduction to Azure Key Vault)

http://blogs.msdn.com/b/sqlsecurity/archive/2015/09/25/creating-an-ad-hoc-always-encrypted-provider-using-azure-key-vault.aspx?wt.mc_id=WW_CE_DM_OO_SCL_TW (Creating custom CMK provider, using Azure Key Vault)

How to create code snippets in SQL Server Management Studio (SSMS)

Recently I’ve been working on a project which requires plenty of stored procedures and custom logging. For error handling I don’t want to repeat my logging TSQL statements for each and every SP I write. I created a code snippet and let the IDE code for me. Follow these simple steps to create code snippets in SSMS. The following example inserts pure TSQL code without any parameters and at the end of this blog post I’ve mentioned how to create code snippets which include parameters using one of the built in code snippets of SSMS.

Code snippets are stored as XML files in particular format and imported in to the SSMS. These XML files have the extension of .snippet and you can find the built in code snippets in this location

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033

Let’s call this path as 1033 Snippet Root

In order to get the .snippet file template open one of the simplest built in code snippets from the 1033 Snippet Root. You can find this from the View folder. Under the View folder there’s a code snippet name Create View.snippet

Open it in a text editor, (Notepad++ would be a good choice). This is the structure of a basic TSQL code snippet XML file.

View Snippet

The snippet I created is far more simpler than the above as it does not require any parameters, so I do not need the <Declarations></Declarations> section. Under the <Snippet></Snippet> I have only the <Code></Code> block.














The above code snippet will get the error details of the current execution context and THROW it.

After creating the XML file save it with a .snippet extension. I saved the above as Default Throw.snippet Now we have to import the snippet into the SSMS.

Importing a code snippet into SSMS

Open SSM, go to Tools and then click Code Snippets Manager


Select a folder where you want to put your snippet (This folder structure is simply a categorization of the snippets). If you want you can add a new folder as well. In my case I wanted to put the snippet under My Code Snippets folder. Then click Import. Browse for the snippet file and import it.


That’s all, now we can use the our snippets in the TSQL editor of SSMS. In order to bring the snippet intellisense press Ctrl +K, Ctrl + X and choose the folder then your snippet and press TAB to insert it.


Recovering a SQL Server database from Suspect Mode

Yesterday my SharePoint environment didn’t respond I checked the DB server and got to know few SharePoint databases were in suspect mode. Now I want to make the databases up and running as usual in order to get my work done. This is a SQL Server thing. There could be several reasons for a database to go to the Suspect Mode. In my case my .ldf file was corrupted.

This forum thread explains more reasons why a database goes to Suspect Mode.


In order to get the database follow these steps. Login to the server using an account with sysadmin privileges. And set the target to database to EMERGENCY mode.




Then set the database to SINGLE USER mode in order to run the recovery.


Now you can run the following command to rebuild the transactional log file. Here data loss is allowed.

   1: DBCC CheckDB ([db name], REPAIR_ALLOW_DATA_LOSS)

Then set back the database to MULTI USER mode.



Sometimes you might run into problems where you cannot set the database to MULTI USER mode; SQL Server complains with this error “The database is in single-user mode, and a user is currently connected to it.”

The database is in single-user mode, and a user is currently connected to it.

Sometimes you might get the above error message from SQL Server when you try to change the user mode from MULTI to SINGLE.

You have to explicitly kill the connection in order to solve the problem. First you need the dbid of your database. Run the following command.

   1: SELECT name, database_id

   2: FROM sys.databases 

   3: WHERE name = 'db name'

Once you get the dbid you can check the connections to the database. Run the following command.

   1: SELECT spid FROM sysprocesses WHERE dbid = @dbid

spid is the connection process id. We have to kill all the spids  in order to run our command which sets the MULTI USER mode. For example think you get the spid as 10. Run the following command to kill the connection and set the database user mode.


   2: KILL 10



   5: GO

Hierarchy in TSQL – step by step guide

Hierarchy data type is introduced in SQL Server 2008. It is a CLR data type. This post gives a simple step by step guide to implement HierarchyID data type

Hierarchical navigation or simply the navigation between nodes could happen in 2 ways.

  1. Depth First
  2. Breadth First

Having said that, we’ll start the tutorial. Create a table as follows. Node is the HierarchyID type, NodeLevel column is a calculated column which contains the level of the Node in the hierarchical tree. ID and Name are custom data type for the information we have. Let’s try to model a organizational reporting hierarchy in using the table h.

   1: create table h (

   2: Node HierarchyID primary key clustered,

   3: NodeLevel AS Node.GetLevel(),


   5: Name VARCHAR(50) NOT NULL

   6: )

Since we’ve defined the Primary Key in the Node column, we get the Depth First index by default. To create a Breadth First index you can run the following query. In real scenarios you do not need to create both indexes, it depends on your requirement to decide which index to be created. Sometime you may need both as well.

   1: -- this creates the bfs index to the table.

   2: create unique index bfs_index

   3:  on h (NodeLevel,Node)

Now we’ll insert data.

   1: insert into h (Node, ID, Name)

   2:    values (HierarchyId::GetRoot(), 1, 'Thuru')

Here I’ve passed the HierarchyId::GetRoot() as the value for the Node. Few things to note here, we ask the SQL Server to give us the HierarchyID of the root node. We use the SQL CLR function GetRoot for this. The :: is marked because GetRoot is a static method inside the HierarchyID SQL CLR data type.

Mark this statement as our common SELECT statement. NodeText is the string representation of Node.

   1: select Node.ToString() AS NodeText, * FROM h


Now I insert a person in the next level.

   1: declare @parent hierarchyid = hierarchyid::GetRoot()

   2: insert into h (Node,id,Name) values

   3:     (@parent.GetDescendant(null,null),2,'Johnny')

   4: go

Here I get the parent Node value using the static function GetRoot(). GetDescendant() method returns the descendant Node value between the right and left sibling. In this case our top level node doesn’t have any children, apparently no siblings so we do pass null. After executing the above query now we have this structure.


Let’s add another person below ‘Thuru’. Note this time I get the parent node using the SELECT statement which asks for the Node of ‘Thuru’. And now I want to insert the new Node in the level of Johnny and after him. So for the new node the right sibling is Johnny and the left sibling is null. The query goes like this.

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @jhony hierarchyid = (select Node from h where name = 'Johnny')

   3: insert into h (Node, ID, Name) values (@parent.GetDescendant(@jhony,null), 3, 'Robert')




Now it’s a simple guess for you what should be done to insert a new node between Johnny and Robert.

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @jhony hierarchyid = (select Node from h where name = 'Johnny')

   3: declare @robert hierarchyid = (select Node from h where name = 'Robert')

   4: insert into h (Node, ID, Name) values (@parent.GetDescendant(@jhony,@robert), 4, 'Alex')

image image


Executing couple of inserts ….

   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @alex hierarchyid = (select Node from h where name = 'Alex')

   3: declare @robert hierarchyid = (select Node from h where name = 'Robert')

   4: insert into h (Node, ID, Name) values (@parent.GetDescendant(@alex,@robert), 5, 'Niki')

   5: go


   7: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   8: declare @alex hierarchyid = (select Node from h where name = 'Alex')

   9: declare @niki hierarchyid = (select Node from h where name = 'Niki')

  10: insert into h (Node, ID, Name) values (@parent.GetDescendant(@alex,@niki), 6, 'Steve')

  11: go


  13: select Node.ToString() AS NodeText, * FROM h

  14: go


image image


Now let’s add 2 children nodes for Steve. Execute the following query.

   1: declare @steveParent hierarchyid = (select Node from h where name = 'Steve')

   2: insert into h (Node, ID, Name) values (@steveParent.GetDescendant(null,null), 7, 'S1')

   3: go


   5: declare @steveParent hierarchyid = (select Node from h where name = 'Steve')

   6: declare @s1 hierarchyid = (select Node from h where name = 'S1')

   7: insert into h (Node, ID, Name) values (@steveParent.GetDescendant(@s1,null), 8, 'S2')

   8: go

image image


IsDescendantOf method and GetAncestor methods are useful in querying the structure.

The following query returns the immediate set of nodes of the specified node.


   1: declare @parent hierarchyid = (select Node from h where name = 'Thuru')

   2: declare @parentNodeLevel int = (select NodeLevel from h where name = 'Thuru')

   3: select Node.ToString() AS NodeText, *  from h where 

   4: Node.IsDescendantOf(@parent) = 'TRUE' and node != @parent and 

   5: NodeLevel = @parentNodeLevel + 1


In the above query I restricted the query to return only the immediate children using the NodeLevel column. And also notice I have opted out the parent node because IsDescendantOf method includes the parent node as well in the result.



Gets the ancestors of the specified node in the passed level, if we pass 0 it returns the siblings.

   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(2)

2 steps above.


Immediate parent

   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(1)



   1: declare @child hierarchyid = (select node from h where name = 'S1')

   2: select * from h where Node = @child.GetAncestor(0)




Deleting a node does not delete its children nodes. This results in orphaned children.

   1: delete from h where Name = 'Steve'

After deleting Steve if we execute our SELECT statement we get the following result


Note that S1 and S2 have the NodeText /1.1.1/1 and /1.1.1/2 where there’s no /1.1.1 resulting orphaned S1 and S2


Microsoft recommends to use proper stored procedures to match the business scenario to deal with the HierarchyID thus eliminating unwanted results like orphaned children.

Moving Nodes

The GetReparentedValue method is used to move the nodes to different locations.

   1: declare @newParent hierarchyid = (select Node from h where name = 'Johnny')

   2: update h set Node = Node.GetReparentedValue(Node.GetAncestor(1),@newParent)

   3: where name = 'S1'

Here we want to move the S1 as a child node of Johnny. We get the node value of Johnny and get the ancestors in the level of where Johnny is and update the Node of S1. This moves the S1 under Johnny leaving the hierarchy like this.


Inheritance in relational database

This article describes about the inheritance in the relational database and how we can handle that in the Entity Framework. First let me discuss about the database inheritance.

I searched the Internet and found there are databases which support inheritance natively. PostgreSQL has this feature, something similar to this.

INHERITS FROM (tablename[, othertable...])


Then I read few chapters from some books. I have given the same exact points that I read from the books. I made some statements bold as I have a personal strong vertical nodding with them. :p

From the Book SQL Server MVP Deep Dives

There’s both a technical and cultural impedance mismatch between the object world

and the database world. Relational databases don’t readily represent inheritance……

Too often the solution is to use an application layer object-relational mapper, such

as NHibernate or the Entity Framework. This provides a poor abstraction for the database……



From the Book Beginning Database design from Novice to Professional (Apress)

Inheritance in a data model is not as common as you might think at first. Humans are very good at categorizing things into hierarchies, and once people get hold of the idea of inheritance in data modeling, there can be a temptation to use it everywhere……..

“Is A a type of B?”

only meant that using inheritance might be a possible way of making sense of a problem.


I strongly believe on what is mentioned above. We do not need to use inheritance structure unless we have a very specific need for it. Because the inheritance in the OOP is very different from the inheritance (or what we call the inheritance relationship) in the database.

Let’s have a simple example of Person and  Student and Lecturer. image

In the context of OOP, most of the cases we have our super classes declared as abstract. So we could have the code as like this.

abstract class Person { }

class Student : Person { }

class Lecturer : Person { }


But in the databases we do not have something called abstract table. Look at this structure which has the inheritance modeled in the database level.


As you can see, though this looks like as it’s having some kind of inheritance it is not clean and not pure.

There can be only person who is neither a student nor a lecturer. There can be people who are students and lectures (of course this makes sense but think a scenario where a person can be either of them, not both)

And just by giving an Id we cannot say what type of a person he is. (we need to perform an OUTER JOIN for this) Just to make things more accurate we have to enforce some constraints.

First create another table with two columns (for minimum) with PersonTypeID and PersonType.



Add the PersonTypeID as Foreign Key (FK) to the Person table with NOT NULL constraint (Here we enforce that one person can be either Student or Lecturer)


So here Jonny and Russel are Lecturers and Thuru and Ann are students.

Still there are some works we have to do. Let me explain, we added the PersonTypeID column with as FK and enforced that a person can belong to one type but we didn’t enforce that a person cannot be in both. I can still have Thuru in lecturer table.

That means I have enforced the rule only in the Person table not in the Student table and Lecturer table.

To overcome this problem, we have to refer the PersonTypeID column in the Person table with the Student table and Lecturer table.

So we add a column in the Student table and in the Lecturer table which refers to the PersonTypeID of the Person table. But we cannot create FK in the student table or in the lecturer table since the PersonTypeID of the Person table is not unique. (as we know to the FK referring column should be unique) So to make them unique we have to create a unique constraint in the Person table.

Like the following.


Here the unique key can be constructed with the combination of PersonID and PersonTypeID. (or you can make the combination as Primary Key (PK) as well)

Then we can have the StudentID and PersonTypeID columns in the Student table and LecturerID and PersonTypeID columns in the Lecturer table as FKs of the PersonID and PersonTypeID of the Person table.

Here Student has the PersonTypeID = 1 and Lecturer has the PersonTypeID = 2. This is not going to change, so when creating the Student table (or Lecturer table) we can use the PERSISTED keyword in TSQL






CONSTRAINT FK_Student FOREIGN KEY (StudentID,PersonTypeID) REFERENCES Person(PersonID,PersonTypeID)


Here I have used the computed columns for the PersonTypeID, you can use the CHECK constraint as well. As our intention is to not allowing any other values than the corresponding PersonTypeID in the table.

Here is the complete diagram of the tables.


The complete TSQL Code



  PersonTypeName VARCHAR(50) UNIQUE






LastName VARCHAR(50),













  PersonTypeID AS 1 PERSISTED,

  CONSTRAINT FK_Student FOREIGN KEY (StudentID,PersonTypeID) REFERENCES Person(PersonID,PersonTypeID)






  PersonTypeID AS 2 PERSISTED,

  CONSTRAINT FK_Lect FOREIGN KEY (LecturerID,PersonTypeID) REFERENCES Person(PersonID,PersonTypeID)


So implementing the inheritance in the database is tricky and we have to be carful in the CRUD operations. It is recommended to write proper SPs with transactions to handle the situation.

Because though we have conceptually implemented the rules, there can be a situation where there is a Student record in the Person table without the corresponding row in the Student table. (that means we do not have the abstract behavior in the table)

Now let’s take a look where a person can be a student as well as a Lecturer. In this case we have the Person to PersonType as N : M relationship. Where a person can take many person types and one person type can have many persons (sorry here I have to put people, but that creates ambiguity in the context, so let us have plural of person as persons 😀 )

So we had to have a mapping table for the PersonID and the PersonTypeID.

That is the difference we are going to have. And the foreign key constraints of the Student table and the Lecturer table refers the mapping table.

So we can define more flexible inheritance scenarios in the relational databases, but this is full in the context of the database. And I still go with the first saying mentioned in the top of this post. Do not use unless you have a proper need for inheritance.

Some say they go for this kind of database design considering the future extendibility. Database extendibility is big area as far as I know, defining an inheritance structure is not enough for that. And I have very few ideas about that so I don’t discuss that here. Additional reading : http://www.saas-attack.com/DatabaseExtensibility/tabid/161/Default.aspx

This is a complex structure and when using the a ORM framework like EF it has plenty of limitations. I’m not aware of the EF much, so if anyone knows how to map this in the EF while keeping the constraints as they are, feel free to make a comment.

Let’s JOIN

JOIN is one of the common task we perform against databases. This post explains it using TSQL.


In a simple JOIN condition we normally do not specify the word INNER, so when we write just JOIN it means an INNER JOIN. INNER JOIN returns only the matching the rows of the tables.

The queries can be executed against the Adventure Works LT database

A simple INNER JOIN between two tables

SELECT SP.ProductID, SP.Name, SPC.Name AS ‘Category’
    FROM SalesLT.Product AS SP INNER JOIN SalesLT.ProductCategory AS SPC
        ON SP.ProductCategoryID = SPC.ProductCategoryID

INNER JOINs can be specified in the WHERE clause as well, but it is recommended to keep them in the FROM. This has no performance issues at all, because even when you specify the JOIN condition in WHERE clause SQL Server executes an INNER JOIN internally.


OUTER JOINS are used to return all rows from one table and matching rows from the other tables. It fills the NULLS where the matching rows cannot be found.

Again the keyword OUTER is optional, but we have to specify whether it is LEFT, RIGHT or FULL OUTER JOIN. When you specify LEFT OUTER JOIN the table in the left side of the JOIN clause is considered as the outer table; means that all rows from this table will be returned with the matching rows of the other table and NULLs replaces where the matching is not there.

Same applies to RIGHT OUTER JOIN, when you use the RIGHT OUTER JOIN the table in the right side of the JOIN is your outer table. So you can get the same results in both LEFT and RIGHT outer JOINs by changing the place of the tables.

SELECT SC.FirstName, SH.SubTotal FROM SalesLT.Customer AS SC
    INNER JOIN SalesLT.SalesOrderHeader AS SH
        ON SC.CustomerID = SH.CustomerID

This results only the Customers who have placed orders with their first name along with the sub total. (It returns 32 rows)


When specify a LEFT OUTER JOIN like this

SELECT SC.FirstName, SH.SubTotal FROM SalesLT.Customer AS SC
    LEFT OUTER JOIN SalesLT.SalesOrderHeader AS SH
        ON SC.CustomerID = SH.CustomerID

It returns all the rows of the Customer table with the sub total where filling NULLs against the customers who have not placed any orders. Since OUTER JOIN pulls all the rows from the outer table, this returns the exact number of rows that the Customer table has. (847 rows)



In this case if you specify the RIGHT OUTER JOIN it pulls all the rows from the OrderHeader table and matching rows from the Customer table. Since the OrderHeader table has FOREIGN KEY of CustomerID and it is NOT NULLABLE, it returns the same result as the INNER JOIN does.

As a generic decision we can say that if the table has a referential integrity defined with NOT NULL constraint, when we specify the OUTER JOIN based on that table (either LEFT or RIGHT) this gives the exact results as INNER JOIN does.


Apart from LEFT and RIGHT OUTER JOINs FULL OUTER JOIN returns all the rows from all the tables specified, by filling NULLs in both sides. This is useful in finding the unmatched rows when there is no referential integrity is defined among the tables.


CROSS JOIN is another type which is the Cartesian product. It matches all rows from one table with all rows from the other table, thus returning m * n rows where m is the number of rows of the first table and n being the number of rows in the second table.

Since CROSS matches every row with all the rows, it does not have an ON condition. (apparently it doesn’t need to have one)

SELECT SC.FirstName, SH.SubTotal FROM SalesLT.Customer AS SC
    CROSS JOIN SalesLT.SalesOrderHeader AS SH

The above query returns 21104 rows (847 rows from Customer table and 37 rows from OrderHeader table)


SELF JOIN can be used in the scenarios where the same table has to be joined with itself. For example if you want to find the name of the employees and the name of their supervisors (where the supervisor also an employee) you make SELF JOINs.

There is no any special keyword for SELF JOIN. (as it is not a special operation, it is just a special case where we can use all the other JOIN types)

SQL Server recommends to use CTEs when there are complex and recursive SELF JOINs scenarios occur.

Sequence Column and IDENTITY

When you design tables you need a column to be updated automatically with a sequence number, ultimately which you may use as a Primary key of the table. (Some we use other columns as primary key)

But in case if you haven’t included a sequence column how you can include one.

Take a look,

Create a table in a database


Insert 10 rows



Now the table is populated like this



We have to Edit the SeqNo column but no way, since we don’t have any way identify the row uniquely.

First delete the SeqNo column


Now Add an IDENTITY column


Now the work is done



To make the new SeqNo column as Primary Key


Work Done Winking smile

MSDN Trackback : http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/158d58b0-0f3a-4d34-bff5-b51d2fad5416