In this blog, I’d like to share some of the introduced language features in versions 2012 up to 2017 and explain how you can use them in your products. This blog focuses only on Transact-SQL syntax features you can use in your own application logic. Features such as new database management functions, new data types, new types of database objects or optimization features will be saved for another time.
SQL Server 2012
Eventually we will set the minimal version of SQL Server to SQL Server 2012. This provides us with the opportunity to use language features introduced in SQL Server 2012 in the Software Factory, the Intelligent Application Manager and in Indicium and the GUI’s when accessing the databases.The following features are discussed:
FETCH OFFSET
THROW
TRY_CONVERT
IIF
CHOOSE
CONCAT
LEAD and LAG, FIRST_VALUE and LAST_VALUE and others
Window frame of the OVER-clause.
PARSE
TRY_PARSE
DATEFROMPARTS and others
EOMONTH
The offset & fetch clause
To find a specific page of data, the generic approach would be using a row_number() function to provide each record with an index from 1 to n and filter the results on a specific page:code:
-- SQL Server 2008r2
select t1.name
from (
select row_number() over (order by name) as row_nr,
name
from customer) t1
where row_nr between 100 and 200;
The offset and fetch function makes this a lot easier. This clause is placed right after the order-by clause.
code:
-- SQL Server 2012
select name
from customer
order by name offset 100 rows fetch next 100 rows only;
Only using offset is possible too. Only using the fetch statement makes no sense, a top statement should be used at that point.
The throw statement
When you use try-catch in a procedure but you want to make sure the user is provided with the original error, 2008r2 and earlier syntax would be a bit weird to re-throw the original error:code:
-- SQL Server 2008r2
begin try
-- Do some risky code
select 1/0;
end try
begin catch
declare @error_message nvarchar(4000);
declare @error_severity int;
declare @error_state int;
set @error_message = error_message();
set @error_severity = error_severity();
set @error_state = error_state();
-- Do something to restore the situation
print('oh dear');
raiserror(@error_message, @error_severity, @error_state);
end catch
The throw statement gives you the option to re-throw the error that got caught in the catch block. Aditionally, the original error code will be retained. A manual raiserror as seen in the code above will set the state to 50000 which will make it harder to detect and translate for the GUI.
code:
-- SQL Server 2012
begin try
-- Do some risky code
select 1/0;
end try
begin catch
-- Do something to restore the situation
print('oh dear');
throw;
end catch
The try_convert statement
There is no proper way to convert partially correct data in a single select statement in SQL Server 2008r2. The only way to find out that data cannot be converted is by using a try-catch mechanism, row by row.The try_convert function is a welcome addition to the language and allows you to process both convertable and inconvertable data in one statement.
code:
declare @data table (random_data varchar(100));
insert into @data values ('1');
insert into @data values ('2,00');
insert into @data values ('3');
insert into @data values ('ants');
insert into @data values ('4.50');
select try_convert(numeric(10, 2), random_data)
from @data;
In the result set of the statement above, null-values will be present where conversion failed (values: 2,00 and ants).
The iif and choose functions
Tired of using case statements for simple scenarios in select clauses? The iif-function makes it a lot easier to choose between two values based on a binary (yes/no) condition.code:
declare @data table (type_of_data bit, data_a varchar(100), data_b varchar(100));
insert into @data values (0, 'Right', 'Wrong');
insert into @data values (0, 'Good', 'Bad');
insert into @data values (1, 'Not good', 'Ok');
-- SQL Server 2008r2
select case when type_of_data = 0 then data_a else data_b end
from @data;
-- or:
select case type_of_data when 0 then data_a else data_b end
from @data;
-- SQL Server 2012
select iif(type_of_data = 0, data_a, data_b)
from @data;
The choose-function works better for non-binary case statements with a lot of options. Based on the provided index, one of the values will be chosen.
code:
declare @month int = datepart(month, getdate());
select choose(@month,
'January',
'February',
'March',
'April',
'May',
'June',
'July',
'August',
'September',
'October',
'November',
'December');
The concat function
Concat makes it easy to concatenate strings, especially when built from different data types.code:
declare @temperature numeric(10, 2) = 30.55
declare @humidity numeric(10, 2) = 77.12
-- SQL Server 2008r2
select 'The temperature is ' + cast(@temperature as varchar) + ' and the humidity is ' + cast(@humidity as varchar);
-- SQL Server 2012
select concat('The temperature is ', @temperature, ' and the humidity is ', @humidity);
The Lead & Lag window functions
These functions can be used to get the value from a previous or next row, without having to access the table twice.The following example uses lag to get a previous value.
code:
declare @revenue table (revenue_year int, revenue numeric(10, 2));
insert into @revenue values (2011, 150000.00);
insert into @revenue values (2012, 140000.00);
insert into @revenue values (2013, 190000.00);
insert into @revenue values (2014, 210000.00);
insert into @revenue values (2015, 225000.00);
-- SQL Server 2008r2
select
t1.revenue_year,
t1.revenue - coalesce((select t2.revenue from @revenue t2 where t2.revenue_year = t1.revenue_year - 1), 0.00) as increase
from @revenue t1;
-- or:
select
t1.revenue_year,
t1.revenue - coalesce(t2.revenue, 0.00) as increase
from @revenue t1
left join @revenue t2
on t2.revenue_year = t1.revenue_year - 1;
-- SQL Server 2012
select
revenue_year,
revenue - lag(revenue, 1, 0.00) over (order by revenue_year) as increase
from @revenue;
As with most window functions, partitioning clauses can be used to limit the data in which the window function operates.
code:
declare @revenue table (company_id int, revenue_year int, revenue numeric(10, 2));
insert into @revenue values (1, 2011, 150000.00);
insert into @revenue values (1, 2012, 140000.00);
insert into @revenue values (1, 2013, 190000.00);
insert into @revenue values (1, 2014, 210000.00);
insert into @revenue values (1, 2015, 225000.00);
insert into @revenue values (2, 2011, 14000.00);
insert into @revenue values (2, 2012, 14444.00);
insert into @revenue values (2, 2013, 12111.00);
insert into @revenue values (2, 2014, 14555.00);
insert into @revenue values (2, 2015, 16000.00);
insert into @revenue values (3, 2011, 2000000.00);
insert into @revenue values (3, 2012, 2000000.00);
insert into @revenue values (3, 2013, 2150000.00);
insert into @revenue values (3, 2014, 2150000.00);
insert into @revenue values (3, 2015, 2350000.00);
-- SQL Server 2008r2
select
t1.company_id,
t1.revenue_year,
t1.revenue - coalesce((select t2.revenue from @revenue t2 where t2.company_id = t1.company_id and t2.revenue_year = t1.revenue_year - 1), 0.00) as increase
from @revenue t1;
-- or:
select
t1.company_id,
t1.revenue_year,
t1.revenue - coalesce(t2.revenue, 0.00) as increase
from @revenue t1
left join @revenue t2
on t2.company_id = t1.company_id
and t2.revenue_year = t1.revenue_year - 1;
-- SQL Server 2012
select
company_id,
revenue_year,
revenue - lag(revenue, 1, 0.00) over (partition by company_id order by revenue_year) as increase
from @revenue;
The first_value and last_value function basically do the same, but as the name suggests they take the first value or last value in a group. The ‘distance’ parameter of lead and lag is not needed.
code:
-- SQL Server 2012
select
company_id,
revenue_year,
revenue,
first_value(revenue) over (partition by company_id order by revenue_year) as first_revenue
from @revenue;
A few more window functions have been added, namely cume_dist, percent_rank, percentile_cont and percentile_disc. These functions can be used to determine the cumulative distribution or relative ranking of records in a set according to values and determine or calculate the values of percentiles in a set. If you ever need those, go look’em up!