SQL Server 2012 has quite a few new Transact SQL Commands. We are going to discuss Top 5 of these SQL statements, here are they:
Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012! A Sequence object is an object that provides functionality similar to Identity (Autonumber) column. The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value of the ID column before you insert records. We are including some examples here that you can use with SQL Server 2012.
–By Itself
create sequence dbsequence
start with 1 increment by 5
select next value for dbsequence
Returns 1
select next value for dbsequence
Returns 6
drop sequence dbsequence
–With a table
create sequence idsequence
start with 1 increment by 3
create table Products_ext
(
id int,
Name varchar(50)
)
INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, ‘ProductItem’)
select * from Products_ext
/* If you run the above statement two types, you will get the following:-
1 ProductItem
4 ProductItem
*/
drop table Products_ext
drop sequence idsequence
There are a number of new date functions in Microsoft SQL Server 2012. We are going to discuss two of the following functions:
DATEFROMPARTS function takes three integer parameters and builds a date data type out of them. Here is some SQL 2012 Transact SQL that shows you the DATEFROMPARTS date function in action:
declare
@year int,
@month int,
@day int
set @year=2012
set @month=9
set @day=28
select datefromparts(@year, @month, @day) as mydate
/*
mydate
———-
2012-09-28
*/
EOMONTH Function: Many applications need the last day of the month for a specific month. EOMONTH Function lets you do just that. Lets check out the following Sql statements:
declare @mydate datetime
set @mydate=’07/23/12′
select eomonth(@mydate)
2012-07-31
Other useful datetime functions in SQL Server 2012 are:
-TIMEFROMPARTS
-DATETIMEFROMPARTS
-SMALLDATETIMEFROMPARTS
-DATETIMEOFFSETFROMPARTS
Just like the Data functions, SQL Server 2012 has new features in regards to Logical functions as well. We will take a look at two of them: CHOOSE and IIF SQL functions.
CHOOSE is a simple function and returns the specified index from a list of values. Here are two examples:
Select CHOOSE(2, ‘Kash’, ‘Money’) –Money is second value
Select CHOOSE(1, ‘Kash’, ‘Money’) –Kash is first value
IIF function which is quite popular in MS Office applications is not available in SQL Server 2012. This function takes three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result. In other words, it kinda works like a case statement. Here is the TSQL syntax with examples:
–select iif(first argument, second argument, third argument)
select IIF(100>50, ‘true’, ‘false’)
Returns true
select IIF(100<50, ‘true’, ‘false’)
Returns false
When you need to combine different text values in SQL Server 2012, you can use the new feature CONCAT. This function helps you concatenate two or strings together. Here is my name with CONCAT function!!
SELECT CONCAT (‘Kash’, ‘Money’, ‘Millionaire’)
Returns KashMoneyMillionaire
–Also can be used in a table, using employees from Northwind database
select * from employees
select concat(firstname, ‘ ‘, lastname) as FullName
from employees
Using FORMAT command is especially helpful when you are working with dates in SQL Server 2012. Syntax is as follows:
FORMAT (value, format [,culture])
Lets go ahead and see the Format command in action.
SELECT
GETDATE(), –native date
FORMAT (GETDATE(), ‘d’), –without leading zero
FORMAT (GETDATE(), ‘dd/MM/yyyy’) –custom format
/*
Output for this is:
2012-08-20 10:36:59.247 8/20/2012 20/08/2012
*/
If you would like to learn more, check out our page on SQL Server 2012 Tutorial.