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





Two handy queries which can help you find things in your database

--Search for a specific string in object-definitions:
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%SELECT%'

--Find a column within the database:
SELECT AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName, AS ColumnName
FROM sys.tables AS t
WHERE LIKE '%Customer%'
ORDER BY SchemaName, ColumnName

SSRS Dashboarding: a ‘webdesign-look’

Couple of weeks ago, I shared my SSRS dashboard with the community on LinkedIn:

SSRS Dashboard shared on LinkedIn

Koos van Strien asked for the source (RDL) and restyled the dashboard with his ‘webdesign-look’. Pretty nice!

SSRS Dashboard response on LinkedIn

Koos discusses the changes he made to my dashboard on his blog:

Here are both dashboards in full size:

SSRS Dashboard Clint Huijbers


SSRS Dashboard Koos van Strien





Today I played around with the TRY_PARSE() function and it does work.
Though it’s an expensive one and as we all know, SQL Server isn’t built for handling strings 🙂

TRY_PARSE SQL Server 2012


DECLARE @dtSQL VARCHAR(10) = '09-2013-13';
SELECT TRY_CAST(@dtSQL AS datetime2) AS [TRY_CAST Result],
TRY_CONVERT(datetime2, @dtSQL) AS [TRY_CONVERT Result],
TRY_PARSE(@dtSQL AS datetime2) AS [TRY_PARSE Result];

More examples: TRY_CAST(), TRY_CONVERT() and TRY_PARSE() – Compare and Contrast

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:

SOLVED: Unable to retrieve column information / metadata (SSIS)

Here’s another annoying thing that might happen when a stored procedure is too ‘complex’ for SSIS:
"The metadata could not be determined because every code path results in an error..."
"Unable to retrieve column information from the data source. Make sure your target table in the database is available"

SSIS unable to retrieve column information metadata


Simply said, it needs help regarding the metadata.
One way to do this is to provide SSIS the necessary metadata information. As shown below:


Microsoft SQL Server 2012 extends the EXECUTE statement to introduce WITH RESULT SETS option which can be used to change the Column Name and Data Types of the result set returned by the execution of stored procedure.

Example Using WITH RESULT SETS Feature of SQL Server 2012

Example - Using WITH RESULT SETS Feature of SQL Server 2012
EXEC WithResultSets_SQLServer2012
[Employe Name] NVARCHAR(100),
[Employee City] NVARCHAR(20),
[Employee Postal Code] NVARCHAR(30)


The WITH RESULT SET Feature of SQL Server 2012 is a great enhancement to the EXECUTE Statement. This feature will be widely used by Business Intelligence Developers to execute a stored procedure with in an SQL Server Integration Services (SSIS) Package to return the result set with required Columns and modified data types.

Read more:

SSRS – Report Execution Analysis

A little while ago, I made a report to analyze the usage of SSRS report (users and rendering times).

Here’s how it works:
The vertical axis displays the total number of report executions, while the size (and (x) number) displays the total number of unique users.
And not to forget, color of the balloons is based on the average rendering time of that specific report.

Report Execution Analyses explanation

It is pretty straight forward on how to implement it.

Download the SQL Stored Procedures and SSRS report RDL-file here:

Other reports
Have a look at other reports I’ve published for my readers:

SQL Agent Job Schedule Timeline report


SQL Agent Job Status report


T-SQL Metadata Queries

Stumbled upon this great post by Marco Schreuder about how to collect source system metadata with T-SQL queries:
The blog post is a few years old, but still the queries might come in handy.

TSQL Metadata Queries

The queries:

1. Get column information from all tables
2. Get column information from all views
3. Get details of foreign key constraints
4. Get details of the indices created on user tables