Pivot Function

Normalization principle dictate that we keep our tables as narrow as possible, i.e. only few columns wide to keep the table schema simple and performant.

It does create a problem when you need to display data in a columnar format or pivot format. For example take a look at the following query...

SELECT C.Name, O.Total As Total,O.Year
FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID 
The result will look something like this...

 

 

 

 

but what if you want to display the information in a columnar format, like an excel spreadsheet with Names going vertically but years going horizontally.

Using Pivot function you can achieve just that. Take a look at the query below...

SELECT * FROM
(SELECT C.Name, O.Total As Total,O.Year
FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID
) A

PIVOT
( SUM(Total) FOR Year IN ([2006],[2007])

) AS TotalAmt

The result will be as follows...

 Data is displayed in much more readable and organized manner.


PIVOT function allows you to pivot a column for another column, as we did in the above example, i.e. pivot Total for Year.

You may be wondering about using nested query. If you don't use nested query you will get a duplicate field error. This is because as you can see from the previous result set, there are multiple customers with same name and we are joining two tables (Orders and Customers) on Customer ID field which results in duplicate field error when trying to pivot.

Thank you.

 

Transparent Data Encryption (TDE)

Yesterday, we reviewed multiple encryption strategies available to secure data. Starting with SQL Server 2008, Microsoft introduced Transparent Data Encryption (TDE). Today, we will review TDE in detail.

TDE is also knows as data at rest encryption because the data is encrypted while saving to data store using keys. Anyone without keys will be unable to use data, even if they were able to get hold of it. TDE is a real-time I/O encryption / decryption technique. When data is being saved to the data store, both data and log files are encrypted. Conversely, when being read, the data is decrypted in real time.

The encryption / decryption process uses a database encryption key, also called as DEK. This key is stored in the database boot record to ensure it is available for recovery.

Database Encryption Key is a symmetric key which is secured by a certificate stored in the master database. It could also be an asymmetric key which is protected by an EKM module. Both AES and 3DES algorithms can be used to encrypt/decrypt data at this level. TDE encryption is performed at the page level. Page is encrypted before saving to data store and decrypted when read from the data store.

If you move the database from one server to another, you must decrypt the database first, move the database, restore and then re-enable TDE on the destination server.

When you enable TDE, make sure to backup the certificate and the associated private key. If the certificate ever becomes unavailable, you will need the backups of both the certificate and the private key to read the data.

Even if you remove TDE, make sure to keep the certificate handy because some transaction logs may still be encrypted.

Below is the architectural illustration of TDE

Following commands can be used to enable TDE

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strongpassword>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE <Your_Database>;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE <Your_Database>
SET ENCRYPTION ON;
GO

Thank you.

Data Encryption

SQL Data Encryption Techniques

With several recent high profile hacks many companies, especially the ones that operate in healthcare space or retail space are worried about whether they are HIPPA and PCI compliant.

Most organizations have taken steps to ensure physical security and have deployed robust tools such as expensive firewalls, SSL and digital code signing techniques.

But, often the data at the database layer is left un-encrypted and even though there are various options to encrypt the data, there is often confusion about the optimal strategy.

Today, we will briefly discuss various options available to encrypt the data at the database level.

1. Transparent Data Encryption (TDE) - first introduced in SQL 2008.
2. Custom Encryption - Programmatically encrypting sensitive data before saving to database.
3. Using SQL Views / Triggers without having to modify your code.
4. Using third party tools that encrypt and decrypt the data seamlessly using encryption keys.
5. Drive Level encryption - Using third party tools or built in OS level encryption you can encrypt the drive where the data is stored.

Options 2 thru 5 are self-explanatory, in the next post we will discuss TDE in more detail.

Thank you.