How to Encrypt and Decrypt Text in SQL Server (2008+)

Here’s a simple example on how to encrypt (and decrypt) values in SQL Server:

How to Encrypt and Decrypt Text in SQL Server


--How to Encrypt and Decrypt Text in SQL Server (2008+)
--Example by Clint Huijbers

@Key1 NVARCHAR(50) = N'8g87g8ag4r8hinsg^%$#F&^F&^F^F&ÛIBOUG(%*^R&$%&#%^C5'
,@Key2 NVARCHAR(50) = N'&*^%R%^**F$F*OUYBYUB*F%74d654d7f685f65f56f6v6vc88d'
,@ValueToEncrypt VARCHAR(150) = 'This is the value that will be encrypted by the built-in function of SQL Server with two keys and 128-bits encryption :) blablablablablablablablablabl'
,@EncryptedValue VARBINARY(350) --Increase the VARBINARY size when you increase the lengh of @ValueToEncrypt!

SELECT @EncryptedValue = ENCRYPTBYPASSPHRASE((@Key1+N'||'+@Key2),@ValueToEncrypt)
SELECT @EncryptedValue





SQL Server 2014 Developer Edition is now a free download

As of the 31st of March, SQL Server 2014 Developer Edition is now a free download:

SQL Server 2014 Dev Download VS

Quote from MSDN:

SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.

Visual Studio Dev Essentials is Microsoft’s most comprehensive free developer program ever, with everything you need to build and deploy your app on any platform, including state-of-the-art tools, the power of the cloud, training, and support.

Ohh….and this is also a nice one:

SQL Server 2016 Developer Edition, when released later this year, will also be free.

Read the full post on the SQL Server Blog:


Evaluation Editions:
Just want to download an evaluation edition?:
Microsoft® SQL Server® 2014 & 2016 Evaluation
Microsoft® SQL Server® 2012 Evaluation

SQL Server 2014 Dev Download





Pause SQL Server service before restarting

Did you know that by pausing the SQL Server service before restarting the instance we allow end users to continue their work uninterrupted and we also stop any new connections to the instance? This is a nicer way of telling people to “get out” of the database in order for the server to be rebooted. I wouldn’t leave the server paused for 60 minutes of course, but I would rather use this method than forcibly disconnect users and rollback their transactions.

When a server is paused you will see messages similar to this in the SQL Server error log:

Error: 17142, Severity: 14, State: 0.
SQL Server service has been paused. No new connections will be allowed. To resume the
service, use SQL Computer Manager or the Services application in Control Panel.

Error: 18456, Severity: 14, State: 13.
Login failed for user ''. Reason: SQL Server service is paused.
No new connections can be accepted at this time. [CLIENT: ]

Next time you are worried about rebooting during the day think about the pause button instead. It might be a nice compromise for your end-users.

Thank Thomas Larock for his blog post:

SQL Server Features List (all versions)

Normally you can find everything on Google, but there is literary no overall list of SQL Server features per version/release. So…I had to make the list myself starting from SQL Server 2012.
Did I forget a feature? Drop a comment.

SQL Server 2012:
– AlwaysOn Availability Groups
– AlwaysOn Failover Cluster Instances
– Non-Clustered Columnstore Indexes
– Data Quality Services (DQS)
– Tabular Model (SSAS)
– FileTable Storage
– Power View
– SQL Server Data Tools (SSDT)
– Contained Databases
– User-Defined Server Roles
– Enhanced Auditing Features
– Sequence Objects (sequence keys)
– Distributed Replay (capture a workload on a server and replay)

SQL Server 2014:
– In-Memory OLTP/Tables (256GB)
– Managed Backup to Azure
– Azure VMs for Availability replicas
– SQL Server Data Files in Azure
– Clustered / Updateable Columnstore Indexes
– Resource Governor for I/O
– Delayed durability
– Buffer Pool Extension (SSD)
– Incremental statistics
– Lock priority of online operations
– AlwaysOn Availability Groups (enhanced with support for additional secondary replicas and Windows Azure integration)
– Database Backup Encryption
– SQL Server Data Tools for Business Intelligence (SSDT-BI)
– Native Compiled Stored Procedures (“WITH NATIVE_COMPILATION”)
– T-SQL commands/statements (for example: SELECT INTO)

SQL Server 2016:
– Always Encrypted
– Row Level Security
– Dynamic Data Masking
– Data Compression / Decompression
– In-Memory Table Enhancements (2TB, FK, Constraints, ALTER, TDE, Triggers)
– Improvements on Columnstore Indexes (Parallel insert, Nonclustered B-tree indexes on top of the columnstore, Read/write nonclustered columnstore on top of a regular clustered index, REORGANIZE)
– Stretch Database
– Polybase
– Datazen
– Built-in R Support
– New SSRS Visualizations (‘Power BI’-like)
– Compare Execution Plans
– Multiple TempDB Database Files
– Live Query Statistics
– Native JSON Support
– Temporal Tables
– Query Store Built-in Reporting
– Natively compiled stored procedures can be nested

SQL Server Management Data Warehouse (MDW)

The SQL Server Management Data Warehouse (MDW) is one of the most underappreciated features of SQL Server. But it’s a really handy feature which logs WaitStats and more, with almost no overhead (< 1% CPU). And…it does generate some nice reports which you could use to monitor your server (out of the box):

SQL Server 2012 Master DWH

How to enable it
For more info (and how to enable it), please visit:

AdventureWorks 2014 download (link)

Microsoft released the new AdventureWorks for SQL Server 2014.

By default, sample databases and sample code are not installed during the installation of SQL Server and you have to search, find and download the sample databases.

Sample databases

On Codeplex you can download the AdventureWorks databases for SQL Server.

Relational Databases (OLTP):
AdventureWorks2012 Data File
AdventureWorks 2012 OLTP Script
AdventureWorks2012-Full Database
AdventureWorks2012 CS Data File
AdventureWorks 2012 CS OLTP Script
AdventureWorks 2012 LT Script

Databases (DW)
AdventureWorksDW2012 Data File

AdventureWorks Multidimensional Models SQL Server 2012
Analysis Services Tutorial SQL Server 2012

SSAS Tabular
AdventureWorks Tabular Model SQL Server 2012
AdventureWorks Internet Sales Tabular Model SQL Server 201

Here are some other samples for SQL Server (thanks to Hennie de Nooijer):
SQL Server 2014 RTM In-Memory OLTP Sample. Samples of the new features of SQL Server : esource Governor IO, Online Operations, and the Columnstore Index.
Adventure Works for SQL Server 2012
SQLSkills databases
Arcane Code. Upgrade for SQL Server 2014
AdventureWorks for SQL Azure

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

SQL 2014: Clustered Columnstore Indexes

Well…here are my first thoughts on the new clustered columnstore indexes in SQL Server 2014.


The SQL Server in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing. Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.

‘Columnstore Indexes Described’ on MSDN:

I’ve read that one before 🙂
So…a bit oversimplified perhaps,but….SQL2014 minus SQL2012 equals ‘clustered columnstore indexes’?
Naaahhh….there’s more! SQL Server 2014 – InMemory Tables, Indexes and Stored Procedures

Jeremiah Peschka about the topic

SQL Server 2014 columnstore fixed the read-only problem by introducing a writable clustered columnstore. When the index is created as a clustered columnstore index (it’s the only index on the table) SQL Server will use a writable deltastore to hold data. This advantage over SQL Server 2012 has prompted many teams to migrate to SQL Server 2014.

The downside of both SQL Server columnstore implementations is that they’re designed for pure data warehouse workloads. If you wanted to combine an OLTP workload with a data warehouse workload on the same system, you’d have some work head of you to build something that would work with both regular indexes and columnstore indexes. At its core, SQL Server columnstore technology is a data warehouse technology.

Too bad that clustered columnstore indexes are only available in the Developer (whoohoo) and Enterprise editions of SQL Server 2014.

While both companies have produced an in-memory analytic feature of one form or another, direct comparisons are difficult. Microsoft SQL Server columnstore indexes are designed to improve pure data warehouse implementations. Oracle Database In-Memory, on the other hand, is intended to help DBAs get the most out of the hardware they already have. Many business users want to report on live data and this feature makes live reporting a possibility.

…and it’s way cheaper 😀

Worth upgrading?
The ‘What’s new’ list for SQL Server 2014 is dominated by the in-memory OLTP feature, which can deliver remarkable performance benefits, provided that you have the Enterprise edition and a suitable application. This makes it a must-have upgrade for those who can use it, but less compelling otherwise, although improved Azure integration is also a significant benefit.
The updateable clustered columnstore index is an important data warehousing feature. Another feature worth a mention is the Buffer Pool Extension, which lets you use SSD (Solid State Drive) storage as extra memory.


In-memory OLTP
In-memory OLTP, combined with native code stored procedures, can deliver remarkable performance improvements.
The core of SQL Server is little changed in this release, and while that is no bad thing considering what a solid product it is, it does make this a minor release in the event that you are not in a position to make good use of the big new features – especially for Standard edition users for whom some new features do not apply.
None of this takes away from the excellence of the product, which has a broad and deep range of features as well as strong performance.

More technical info about columnstore indexes:

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

Microsoft® SQL Server® 2014 Feature Pack

Microsoft released the ‘Microsoft® SQL Server® 2014 Feature Pack’:

The Microsoft SQL Server 2014 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft SQL Server. It includes the latest versions of:
– Tool and components for Microsoft® SQL Server 2014.
– Add-on providers for Microsoft SQL Server 2014

Download it here:
Microsoft® SQL Server® 2014 Feature Pack

Looking for the 2012 version?:
Microsoft® SQL Server® 2012 Feature Pack

The OData Source component for SSIS 2014 is included in the feature pack.
For SSIS 2012 see below:

Microsoft OData Source for SQL Server 2012 provides the capability of consuming OData services to SQL Server 2012 Integration Services (SSIS). Users can use it in an SSIS package to extract data from an OData service and then perform further ETL processing.

The Open Data protocol (OData) lets you access a data source, such as a database, by browsing to a specially constructed URL. This allows for a simplified approach for connecting to and working with data sources that are hosted within an organization.

There are many OData sources available in enterprise and in cloud:
– SharePoint Foundation 2010
– SharePoint Server 2010
– SQL Azure
– Windows Azure Table Storage
Windows Azure Marketplace
– SQL Server Reporting Services
Microsoft Dynamics CRM 2011
– Windows Live

Microsoft® OData Source for Microsoft SQL Server® 2012

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!: