Azure SQL Database – Version and Compatibility Level

In the on-premises world, when we install a SQL Server on our own servers, we decide on SQL Version (2012, 2014….), Edition (Standard, Enterprise, Express…) and we set the database Compatibility Levels according to our applications’ specs. But what is it like with the Azure SQL Database (PaaS)? The answer is – it’s very different so let’s go through the key points to have in mind:
Continue reading


SQL Server in Azure

There are two flavors of SQL Server in Azure: one is the traditional SQL Server platform hosted in Azure virtual machines, also described as Infrastructure as a Service (IaaS). The other is called Azure SQL Database, database-as-a-service or Platform as a Service (PaaS).  there are several important differences in functionality and features, so let’s look at them:

Continue reading

Using SSIS Lookup Transformation in ETL Packages

Lookup is one of many transformations available in SQL Server Integration Services (SSIS). Its purpose is to perform a JOIN of two datasets: input columns from a data source and columns in a reference dataset – a table, view or a result of a query. Lookup can be used in various scenarios but this post focuses on how to use it to implement a typical ETL package that performs incremental loads into a Data Warehouse.

Here’s a simplified description of how Lookup works: it tries to match each incoming record with one or more records in the reference table. If there is a match, the incoming record is sent to the match output. If there is no match, Lookup generates an error by default but it can also be configured to send the data to the no match output. There are some more considerations that will be described later on. Continue reading

The MERGE Statement – One Statement for INSERT, UPDATE and DELETE

Starting with SQL Server 2008, we can use the MERGE statement to solve some every day scenarios in more elegant ways. A short definition of this new feature would be: it allows us to write a single statement to synchronize data in two tables by executing any combination of INSERT, UPDATE and DELETE operations according to specified criteria. The result of this statement isn’t something that wasn’t possible before, but the difference is that we don’t have to write more complex stored procedures that include multiple JOINs and statements like IF EXISTS. Continue reading

Database Compatibility Level 101

Each SQL Server database has a setting called Compatibility Level that determines how T-SQL commands are interpreted and what features are supported for that database. A single SQL instance can host multiple databases with different compatibility levels, so SQL statements that work on one database might not work on the other. Each version of SQL Server introduces a new compatibility level and supports two previous levels for backward compatibility: Continue reading