SQL Server 2012 New Features and Programmability Enhancements

Estimated study time: 8 minutes. The release that reshaped window functions and high availability.

SQL Server 2012 was a major release that introduced features developers still rely on daily, especially around analytic queries and high availability.

Enhanced Window Functions

SQL Server 2012 added a full OVER() clause with framing, unlocking functions like LAG, LEAD, FIRST_VALUE, and LAST_VALUE:

SELECT EmpName, Salary,
       LAG(Salary) OVER (ORDER BY HireDate) AS PrevSalary
FROM Employees;

New T-SQL Functions

Several practical functions arrived in this release, including IIF() for inline conditionals and CHOOSE() for index-based value selection:

SELECT EmpName, IIF(Salary > 50000, 'High', 'Standard') AS SalaryBand
FROM Employees;

OFFSET-FETCH for Paging

Server-side pagination finally got native syntax, replacing awkward workarounds with ROW_NUMBER():

SELECT * FROM Employees
ORDER BY EmpID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

AlwaysOn Availability Groups

This was arguably the headline feature: AlwaysOn Availability Groups replaced database mirroring with a more flexible, multi-database failover solution supporting up to four secondary replicas.

Contained Databases

Contained databases reduced dependency on server-level logins and settings, making it easier to move a database between instances without breaking authentication.

💡 Tip: If you're still supporting a SQL Server 2012 environment, remember it reached end of extended support in 2022 — worth flagging for any compliance-sensitive projects.

Columnstore Indexes

SQL Server 2012 introduced the first version of columnstore indexes, dramatically speeding up large analytical queries on data warehouses (though they were read-only until later versions).