What's New in SQL Server for the Developer: SQL Server 2012

How many of us have written multiple lines just to do something simple in SQL Server? I have. More than once.
In my session, "What's New in SQL Server for the Developer", I always start with a show of hands, and usually the room has everything from SQL Server 2000 all the way up to 2025, and a few folks running Azure SQL. That's a big spread. It's also exactly why I built the session the way I did. A lot of us are still writing T-SQL like it's 2008, even though the engine underneath has had better tools for years.
So I'm turning my session into a series, one post per version, with the examples from my slides. Today we start where the session starts: SQL Server 2012.
A Little Background
For background, SQL Server 2012 was a huge release for developers. It gave us inline logic, safer conversions, culture-aware formatting, a real way to throw errors, sequences, and native paging. All of these still work in SQL Server 2025 and Azure SQL, so if you're on any supported version, you can use them right away.
Let's break it down.
IIF
IIF stands for Immediate If. It's a shorthand CASE expression: one condition, a value when it's true, and a value when it's false.
DECLARE @FirstArgument INT = 10;
DECLARE @SecondArgument INT = 20;
SELECT
IIF(@FirstArgument > @SecondArgument, 'TRUE', 'FALSE') AS [TrueFalse],
IIF(@FirstArgument > @SecondArgument, 1, 0) AS [BitResult];You can nest them too. Here's a quick grading example:
DECLARE @StudentGrades TABLE
(
Name VARCHAR(100),
Mark INT
);
INSERT @StudentGrades VALUES ('Dilbert', 98);
INSERT @StudentGrades VALUES ('Pointy-Haired Boss', 64);
INSERT @StudentGrades VALUES ('Dogbert', 83);
INSERT @StudentGrades VALUES ('Wally', 75);
INSERT @StudentGrades VALUES ('Asok', 65);
SELECT
Name,
IIF(Mark BETWEEN 90 AND 100, 'A Grade',
IIF(Mark BETWEEN 80 AND 89, 'B Grade',
IIF(Mark BETWEEN 70 AND 79, 'C Grade',
IIF(Mark BETWEEN 66 AND 69, 'D Grade',
IIF(Mark < 65, 'Failed', 'E Grade'))))) AS Grade
FROM
@StudentGrades;Keep in mind that IIF compiles down to a CASE expression under the hood. There's no performance difference. It's just less typing, and it's a lot easier to read in a SELECT list.
CHOOSE
CHOOSE works like an ENUM in application code. You pass it an index, and it returns the value at that position from the list.
SELECT TOP 5
DATEPART(DD, l.[Date]) AS [Date],
CHOOSE(DATEPART(MM, l.[Date]), 'January', 'February', 'March', 'April', 'May', 'June',
'July', 'August', 'September', 'October', 'November',
'December') AS [Month],
DATEPART(YYYY, l.[Date]) AS [Year]
FROM
dbo.[Log] AS l
ORDER BY
l.Id DESC;It gets fun when you pair it with IIF. Here I'm counting logged issues by month, flagging the holiday season, and mapping each month to a weather season, all in one pass:
SELECT
COUNT(l.Id) AS LoggedIssues,
CHOOSE(DATEPART(MM, l.[Date]), 'January', 'February', 'March', 'April', 'May', 'June',
'July', 'August', 'September', 'October', 'November',
'December') AS [Month],
DATEPART(YYYY, l.[Date]) AS [Year],
IIF(DATEPART(MM, l.[Date]) = 1 OR DATEPART(MM, l.[Date]) BETWEEN 10 AND 12,
'Holiday Season', 'Regular Season') AS HolidaySeason,
CHOOSE(MONTH(l.[Date]), 'Winter', 'Winter', 'Spring', 'Spring', 'Spring', 'Summer',
'Summer', 'Summer', 'Autumn', 'Autumn', 'Autumn', 'Winter') AS WeatherSeason
FROM
dbo.[Log] AS l
WHERE
l.[Date] IS NOT NULL
GROUP BY
DATEPART(MM, l.[Date]),
DATEPART(YYYY, l.[Date]),
MONTH(l.[Date])
ORDER BY
1 DESC;Check this out! Here are CHOOSE, IIF, and CASE side by side doing the exact same job. All three return the same answer. Notice that the CHOOSE version is two lines.
DECLARE @DaysOfWeek TABLE (DayOfWeek INT);
INSERT @DaysOfWeek (DayOfWeek) VALUES (1), (2), (3), (4), (5), (6), (7);
SELECT
[DayOfWeek],
CHOOSE([DayOfWeek], 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
'Saturday') AS [Choose-Function],
IIF([DayOfWeek] = 1, 'Sunday',
IIF([DayOfWeek] = 2, 'Monday',
IIF([DayOfWeek] = 3, 'Tuesday',
IIF([DayOfWeek] = 4, 'Wednesday',
IIF([DayOfWeek] = 5, 'Thursday',
IIF([DayOfWeek] = 6, 'Friday', 'Saturday')))))) AS [IIF-Function],
CASE [DayOfWeek]
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END AS [Case-Function]
FROM
@DaysOfWeek;One thing to note: if the index is out of range (0 or 13 in the month example), CHOOSE returns NULL. It won't throw an error, so a bad value can slip through quietly.
TRY_CONVERT and TRY_PARSE
How many times has one bad row killed an entire load? CONVERT throws an error the moment it hits a value it can't convert. TRY_CONVERT returns NULL instead, and your query keeps going.
SELECT
CASE WHEN TRY_CONVERT(FLOAT, 'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
GO
SET DATEFORMAT dmy;
SELECT TRY_CONVERT(DATETIME2, '12/31/2010') AS Result;
GOThe first query returns 'Cast failed'. The second returns NULL, because the date format is set to day/month/year and there's no 31st month.
TRY_PARSE does the same thing for strings going to numbers and dates. I like to use it to filter out the junk before I convert, instead of trusting ISNUMERIC:
SELECT id, somevalue FROM dbo.TestTry;
-- This fails as soon as it hits a value like 'abc'
SELECT CONVERT(INT, id) AS IntegerValue, somevalue FROM dbo.TestTry;
-- This only returns the rows that are actually integers
SELECT
TRY_PARSE(id AS INT) AS IntegerValue,
somevalue
FROM
dbo.TestTry
WHERE
TRY_PARSE(id AS INT) IS NOT NULL;However, there is a catch: ISNUMERIC returns 1 for things like '$', '.', and '1e5', which then blow up on CONVERT to INT. That's why I stopped using it as a filter. Also, TRY_PARSE runs through the .NET Common Language Runtime (CLR), so it's slower than TRY_CONVERT. Reach for TRY_CONVERT first, and save TRY_PARSE for when you need its culture support.
FORMAT
FORMAT is one of my favorites from this release. It returns a value as a string using a .NET format mask, and you can pass a culture to get the output for another language or region.
SELECT
FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO Formatted Date],
FORMAT(GETDATE(), 'MMMM dd, yyyy hh:mm:ss') AS [Custom Formatted Date],
FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [Full ISO],
FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [Long-hand Date (EN)],
FORMAT(GETDATE(), 'MMMM dd, yyyy', 'fr-FR') AS [French Date];
DECLARE @d DATETIME = '05/01/2015';
SELECT
FORMAT(@d, 'D', 'en-US') AS [US English Result],
FORMAT(@d, 'D', 'en-gb') AS [Great Britain English Result],
FORMAT(@d, 'D', 'de-de') AS [German Result],
FORMAT(@d, 'D', 'zh-cn') AS [Chinese (Simplified PRC) Result];Notice the third parameter. Any FORMAT call can take a culture, so the same date comes back in English, German, or Chinese without a lookup table.
It's not just dates. Here's time in 12 and 24 hour formats, currency, fixed point, percentages, padded decimals, and hex:
DECLARE @1224hour DATETIME = '5/9/2001 4:54:08 PM';
SELECT
FORMAT(@1224hour, 'h:m:s') AS [12hr],
FORMAT(@1224hour, 'hh:mm:ss') AS [12hr Leading Zero],
FORMAT(@1224hour, 'HH:mm:ss') AS [24hr],
FORMAT(@1224hour, 'HH:mm:ss', 'fr-FR') AS [24hr French];
SELECT
FORMAT(1222460.7, 'C', 'en-US') AS [US Currency],
FORMAT(1222460.7, 'C', 'en-GB') AS [UK Currency];
DECLARE @fp FLOAT = 18934.1879;
SELECT
FORMAT(@fp, 'F') AS F,
FORMAT(@fp, 'F1') AS F1,
FORMAT(@fp, 'F3') AS F3,
FORMAT(-@fp, 'F0') AS F0;
DECLARE @p FLOAT = .2468013, @dp FLOAT = 34.5;
SELECT
FORMAT(@p, 'P') AS PercentValue,
FORMAT(@p, 'P', 'hr-HR') AS CroatiaPercentValue,
FORMAT(@dp, '0.0') AS OneDecimalPlace,
FORMAT(@dp, '0000') AS PaddedNoDecimal,
FORMAT(@dp, '0000.00') AS PaddedDecimalPlace;
DECLARE @h INT = 123456789;
SELECT
FORMAT(@h, 'X') AS Hex,
FORMAT(@h, 'x') AS HexLower,
FORMAT(@h, 'X12') AS Hex12;You can even give positive, negative, and zero values their own format by separating the masks with a semicolon:
DECLARE @posValue FLOAT = 1234, @negValue FLOAT = -1234, @zeroValue FLOAT = 0;
SELECT
FORMAT(@posValue, '##;(##)') AS Positive,
FORMAT(@negValue, '##;(##)') AS Negative,
FORMAT(@zeroValue, '##;(##);N/A') AS Zero;Here's my favorite one: phone numbers. Store the phone number as plain digits and format it on the way out. You get a smaller column, no dashes or parentheses to clean up, and the application can show it any way it wants.
DECLARE @phonenumber BIGINT = 9045551234;
SELECT
FORMAT(@phonenumber, '(###) ###-####') AS PhoneNumberFormat1,
FORMAT(@phonenumber, '###-###-####') AS PhoneNumberFormat2,
FORMAT(@phonenumber, '###\.###\.####') AS PhoneNumberFormat3;Notice the backslash in the third mask. That escapes the period, so FORMAT treats it as a literal instead of a decimal point.
A couple of things to keep in mind. First, use BIGINT for phone numbers. INT tops out at 2,147,483,647, so any area code above 214 won't fit. Second, FORMAT also runs through the CLR, and it's noticeably slower than CONVERT on large result sets. Use it for presentation, not inside a big ETL job.
THROW
Before 2012, raising your own error meant RAISERROR, and usually a custom message added to sys.messages first. THROW lets you raise a custom error number and message inline. A bare THROW inside a CATCH block re-throws the original error, line number, and all.
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorState INT;
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @RecordCount INT = 1, @UpdateCount INT = 0;
IF @RecordCount <> @UpdateCount
BEGIN
SELECT @ErrorMessage = N'Record count does not match update count.',
@ErrorNumber = 500001,
@ErrorState = 1;
THROW @ErrorNumber, @ErrorMessage, @ErrorState;
END;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT 'FAILURE. AUTOMATIC ROLLBACK TRANSACTION';
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;Keep in mind that custom error numbers must be 50000 or higher, and the statement right before THROW must end with a semicolon. That one catches everybody at least once.
SEQUENCE
An identity column belongs to one table. A SEQUENCE is its own object, so multiple tables, databases, or processes can all pull from the same number line.
Here's a real world example. We built a notes system spread across different databases, and every note ID had to be unique no matter where it was written. Later we stood up 10 servers, each starting at a different number and incrementing so the keys never collided. With identity columns, that's painful. With a sequence, it's a few lines.
CREATE SEQUENCE dbo.LogSequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE -2147483648
MAXVALUE 2147483647
CACHE;
GO
DECLARE @NextSequence INT;
SET @NextSequence = NEXT VALUE FOR dbo.LogSequence;
INSERT INTO dbo.LogTable (LogId, LogValue)
VALUES (@NextSequence, '@NextSequence will have a value of ' + CAST(@NextSequence AS VARCHAR(20)) + '.');
SELECT current_value FROM sys.sequences WHERE name = 'LogSequence';You can also use a sequence as a column default, and you can start it at the bottom of the INT range to double your available keys:
CREATE SEQUENCE dbo.TicketSequence AS INT
START WITH -2147483648
INCREMENT BY 1;
GO
CREATE TABLE dbo.ServiceTickets
(
TicketID INT PRIMARY KEY CLUSTERED DEFAULT NEXT VALUE FOR dbo.TicketSequence,
Department CHAR(1) NOT NULL CHECK (Department IN ('M', 'F'))
);
INSERT INTO dbo.ServiceTickets (Department) VALUES ('M');Need a sequence on one server to pick up where another left off, say for a staging to production migration? Read the current value, drop it, and recreate it with that value as the new start:
DECLARE @CurrentMax INT, @CreateSequence NVARCHAR(MAX);
SELECT @CurrentMax = CAST(current_value AS INT)
FROM sys.sequences
WHERE name = 'LogSequence';
DROP SEQUENCE dbo.LogSequence;
SET @CreateSequence = N'CREATE SEQUENCE dbo.LogSequence AS INT
START WITH ' + CAST(@CurrentMax + 1 AS NVARCHAR(20)) + N'
INCREMENT BY 1
MINVALUE -2147483648
MAXVALUE 2147483647
CACHE;';
PRINT @CreateSequence;
EXEC (@CreateSequence);Two options worth knowing. CYCLE tells the sequence to start over from the minimum value once it hits the maximum, which is handy for rotating through a fixed set of numbers like 1 through 5. NO CYCLE, the default, throws an error instead. CACHE keeps a block of values in memory so SQL Server doesn't pull from disk for every new number, which helps when bulk loading.
CREATE SEQUENCE dbo.RotatingSequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CACHE 50
CYCLE;However, there is a catch: sequences can have gaps. A rolled-back transaction still burns the number, and with CACHE turned on, an unexpected shutdown can skip the cached values that were never handed out. If the business needs gapless numbers (invoice numbers, for example), a sequence isn't the right tool.
OFFSET and FETCH
Remember paging with ROW_NUMBER inside a CTE? Here's the old way:
DECLARE @PageNumber INT = 1, @PageSize INT = 10;
;WITH LogTable AS
(
SELECT
l.Id, l.Date, l.Exception, l.HostName, l.Level, l.Logger, l.Message,
ROW_NUMBER() OVER (ORDER BY l.Date DESC) AS RowNumber
FROM
dbo.[Log] AS l
)
SELECT
nl.Id, nl.Date, nl.Exception, nl.HostName, nl.Level, nl.Logger, nl.Message
FROM
LogTable AS nl
WHERE
RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize)
ORDER BY
nl.Date DESC;And here's the new way:
DECLARE @PageNumber INT = 1, @PageSize INT = 10;
SELECT
l.Id, l.Date, l.Exception, l.HostName, l.Level, l.Logger, l.Message
FROM
dbo.[Log] AS l
ORDER BY
l.Date DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;When I compared the two execution plans, the CTE version scanned the whole table, while the OFFSET/FETCH plan was about half the size.
Notice the OFFSET math. Page 1 needs to skip zero rows, so it's (@PageNumber - 1) * @PageSize. If you write OFFSET @PageNumber ROWS.
Also keep in mind that OFFSET/FETCH requires an ORDER BY.
EOMONTH
The last one from 2012 is small, but I use it all the time. EOMONTH returns the last day of the month, and the optional second parameter moves forward or backward by months.
DECLARE @date DATETIME = GETDATE();
SELECT
EOMONTH(@date) AS [This Month],
EOMONTH(@date, 1) AS [Next Month],
EOMONTH(@date, -1) AS [Last Month];No more DATEADD(DAY, -1, DATEADD(MONTH, 1, ...)) gymnastics.
However, there is a catch: EOMONTH returns a DATE with no time. If you filter a DATETIME column with WHERE OrderDate <= EOMONTH(@date), you'll miss everything after midnight on the last day of the month. Use WHERE OrderDate < DATEADD(DAY, 1, EOMONTH(@date)) instead.
Conclusion
SQL Server 2012 shipped a long time ago, and I still run into code written as if none of this exists. If you're on any supported version of SQL Server or Azure SQL, every function in this post is already sitting there waiting for you.
Next up in the series is SQL Server 2016, which brought JSON support, temporal tables, Query Store, and a whole set of security features.
If you try any of these, I'd love to hear how it went. If you have any questions, feel free to reach out or comment below!



Comments