New Functions in SQL Server 2012 (Denali) - 1
Published on
13,946 Points
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below:
PERCENT_RANK() function will returns the percentage value of rank of the values among its group.
PERCENT_RANK() function value always in between 0 and 1.
PERCENTILE_CONT() function will takes a parameter, which indicates the offset and it should be between 0 and 1.
Suppose, in a group of values 10,20,30,40,50,60 PERCENTILE_CONT() function will returns any value between 10 and 50. This value will be calculated based on insertion, at given offset position.
PERCENTILE_DISC() function will takes a parameter, which indicates the offset and it should be between 0 and 1.
Suppose, in a group of values 10,20,30,40,50,60 PERCENTILE_DISC() function will returns any value between 10 and 50. This value will be calculated based on insertion, at given offset position.
LAG() will return columns values from its previous rows and LEAD() will return column value from its next rows from the same result set without the use of a self-join.
Cumulative Distribution is statistical function (i.e probability) .
This function give the probability of outcome. i.e if you throw a dice then the output may be between 1 to 6.
So, possibility fir outcome 1 will be 1/6 = 0.1666
and possibility fir outcome 2 will be 2/6 = 0.3333
First value and Last value functions gives a first and last value from its' group respectively.
Concat function concat(merge) to string in single as like in .Net. In current version of SQL Server we are doing things as FirstName + ' ' + LastName
EOMONTH() returns the last date of month of given date
IIF() returns either true value or false value based on the specified Boolean expression.
CHOOSE() function will returns the value based on the specified index from the list of values.
Parse() function will parse an value into specified datatype. This function relies on CLR. It will take some performance overhead. Use this function only to convert strings to/from datetime and numeric values.
The function is very useful when you are converting value from one datatype to another. If it can not be converted then it will return NULL value rather then exception. But it is based on culture.
The function is very useful when you are converting value from one datatype to another. If it can not be converted then it will return NULL value rather then exception.
Convert Day, Month, Year etc values to Date.
Similar to DATETIMEFROMPARTS()
Similar to DATETIMEFROMPARTS()
Format values based on the culture.
SEQUENCE is like a new enhancement in Identity.
Identity columns can be used to uniquely identify records in a table. But if we wants to maintain identity columns across multiple tables, or if we need identity column value before insertion of record into table, we can use SEQUENCE.
When you want to change the data type of resulting column then EXECUTE : WITH RESULT SETS is very useful.
In 2008 you are able to achieve pagination using Row_Number()
In Denali (SQL Server 2012)
RAISERROR in the format RAISERROR integer 'string' is discontinued.
Rewrite the statement using the current RAISERROR(…) syntax.
Two new algorithms are added to SQL Server Security portion
"SHA2256" and "SHA2512" as like MD2,MD4,MD5,SHA and SHA1.
In denali THROW function is implemented as like .NET. Now everyone can Throw error to calling application.
Implemented code snippets feature to increase the usability and speed of coder. You can create your own code snippets and publish to using SSMS tools menu for future use.
To add a new code snippet,
Create the snippet file with .snippet extension(Its advisable to take existing snippet file and modify it)
Go to Tools -> Code Snippets Manager
Select the folder, under which you wants to add code snippet and click on Add button and select the snippet file.
Now using SSMS 2012, you can open multiple Query windows at a time. Easily you can drag the query window and can place that query window where ever we want.
SQL Devloper often use some external tools to manage their task list, where they will add tasks and will follow the tasks according to the task list.
This Tool is included in SQL Server 2012, where you can add/delete tasks and set the priority of these tasks.
In add task using SSMS,
Go to View -> Task List, which opens task list, where you can add a new task by clicking on Tick mark icon. You can set the priority by clicking on first column in that task.
In earlier versions of SQL Server - SSMS there is no facility to zoom their view. To do that you have to change the Font size to cop with the problem.
Now in SQL Server - SSMS 2012, you can get the Zoom in and Zoom out facility.
SQL Server SSMS 2012 has improved debugging facility. You can hit the break point based on condition. It is not possible in older version. It is the good features to trace the error point.
i.e You are iteration loop and you have to break at 5th iteration then Conditional break is very useful.
In built code snippets like Conditional and Iterative operator like IF, WHILE , BEGIN etc. When you are ready with you code and wish that code execute based on condition or iterate it until some condition then you can use Surround With option.
1. Right Click and choose “Surround with”, which shows IF,BEGIN,WHILE options. Or Alternatively press Ctrl+k,Ctrl+s
2. Choose any of the block, that you wants to include in your stored procedure/query
If you are very familiar with Visual Studio short cuts and want the same for SSMS 2012 then you can import/export short cuts from Visual Studio to SSMS 2012.
You can copy multiple code/items in Clipboard and use then at any time.
1. From a query window, copy the query content by pressing Ctrl+C
2. Copy another query content by pressing Ctrl+C
3. To access recently copied content, press Ctrl+Shift+V
4. Now to access the first copied content, press Ctrl+Shift+ V two times.
Thanks,
Alpesh
1. PERCENT_RANK()
PERCENT_RANK() function will returns the percentage value of rank of the values among its group.
PERCENT_RANK() function value always in between 0 and 1.
declare @t table
(
Num int
)
insert into @t values (10),(20),(30),(50)
select Num,Percent_Rank() over (order by Num) as PrcntRank
from @t
/*Output
Num PrcntRank
10 0
20 0.333333333333333
30 0.666666666666667
50 1
*/
2. PERCENTILE_CONT()
PERCENTILE_CONT() function will takes a parameter, which indicates the offset and it should be between 0 and 1.
Suppose, in a group of values 10,20,30,40,50,60 PERCENTILE_CONT() function will returns any value between 10 and 50. This value will be calculated based on insertion, at given offset position.
declare @t table
(
GrpId int,
Num int
)
insert into @t values (1,10),(1,20),(1,30),(1,50)
select *,
PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY GrpID) as [InterPolatedAt0.4],
PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY GrpID) as [InterPolatedAt0.8]
from @t
/*
GrpId Num InterPolatedAt0.4 InterPolatedAt0.8
1 10 22 28
1 20 22 28
1 30 22 28
1 50 22 38
*/
3. PERCENTILE_DISC()
PERCENTILE_DISC() function will takes a parameter, which indicates the offset and it should be between 0 and 1.
Suppose, in a group of values 10,20,30,40,50,60 PERCENTILE_DISC() function will returns any value between 10 and 50. This value will be calculated based on insertion, at given offset position.
declare @t table
(
GrpId int,
Num int
)
insert into @t values (1,10),(1,20),(1,30),(1,50)
select *,
PERCENTILE_DISC(0.4) WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY GrpID) as [InterPolatedAt0.4],
PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY GrpID) as [InterPolatedAt0.8]
from @t
/*
GrpId Num InterPolatedAt0.4 InterPolatedAt0.8
1 10 20 50
1 20 20 50
1 30 20 50
1 50 20 50
*/
4. LAG() AND LEAD()
LAG() will return columns values from its previous rows and LEAD() will return column value from its next rows from the same result set without the use of a self-join.
DECLARE @Users TABLE(
UserID INT IDENTITY,
UserName VARCHAR(20)
)
INSERT INTO @Users (UserName)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'
SELECT
*,
LAG(UserName) OVER (ORDER BY UserName) AS Prev,
LEAD(UserName) OVER (ORDER BY UserName) AS Next
FROM @Users
ORDER BY UserName
/*
UserID UserName Prev Next
----------- -------------------- -------------------- ----------------------
1 A NULL B
2 B A C
3 C B D
4 D C NULL
*/
5. CUME_DIST()
Cumulative Distribution is statistical function (i.e probability) .
This function give the probability of outcome. i.e if you throw a dice then the output may be between 1 to 6.
So, possibility fir outcome 1 will be 1/6 = 0.1666
and possibility fir outcome 2 will be 2/6 = 0.3333
6. FIRST_VALUE() AND LAST_VALUE()
First value and Last value functions gives a first and last value from its' group respectively.
declare @Salaries table
(
DeptId int,
Sal int
)
insert into @Salaries values (1,23),(1,25),(1,21),(2,35),(2,15)
select DeptID,Sal,last_value(Sal) over (partition by DeptID order by Sal) as LastSalary.
First_value(Sal) over (partition by DeptID order by Sal) as FirstSalary.
from @Salaries
DeptID Sal LastSalary FirstSalary
1 21 25 21
1 23 25 21
1 25 25 21
2 15 35 15
2 35 35 15
7. CONCAT()
Concat function concat(merge) to string in single as like in .Net. In current version of SQL Server we are doing things as FirstName + ' ' + LastName
SELECT CONCAT(@lastname, ' ', @middlename, ' ', @firstname)
8. EOMONTH()
EOMONTH() returns the last date of month of given date
SELECT EOMONTH('12/06/2011')
OutPut:
2011-12-31 00:00:00.0000000
9. IIF()
IIF() returns either true value or false value based on the specified Boolean expression.
SELECT IFF (@Number1 > @Number2, @true_value, @falsevalue)
10. Choose()
CHOOSE() function will returns the value based on the specified index from the list of values.
SELECT CHOOSE (@Index, @FirstValue, @SecondValue, @ThirdValue)
11. Parse()
Parse() function will parse an value into specified datatype. This function relies on CLR. It will take some performance overhead. Use this function only to convert strings to/from datetime and numeric values.
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result
GO
SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result
/*Output
2010-12-13 00:00:00.0000000
*/
12. TRY_PARSE()
The function is very useful when you are converting value from one datatype to another. If it can not be converted then it will return NULL value rather then exception. But it is based on culture.
SELECT TRY_PARSE('2011-01-01' AS datetime2 USING 'en-US') AS Result
GO
SELECT TRY_PARSE('Test' AS datetime2 USING 'en-US') AS Result
13. TRY_CONVERT()
The function is very useful when you are converting value from one datatype to another. If it can not be converted then it will return NULL value rather then exception.
SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
/*
2010-12-31 00:00:00.0000000
NULL
*/
14. DATETIMEFROMPARTS()
Convert Day, Month, Year etc values to Date.
SELECT DATETIMEFROMPARTS(
@Year,
@Month,
@Day,
@Hour,
@Minute,
@Seconds,
@MilliSeconds)
15. TIMEFROMPARTS()
Similar to DATETIMEFROMPARTS()
16. DATEFROMPARTS()
Similar to DATETIMEFROMPARTS()
17. Format()
Format values based on the culture.
DECLARE @CurrentDate DATETIME, @Format NVarchar(20), @Culture NVarchar(10)
SET @CurrentDate = GETDATE(); -- (12/6/2011)
SET @Format = 'dd-mm-yyyy'
SET @Culture = 'en-US'
SELECT FORMAT(@CurrentDate,@Format, @Culture)
18. SEQUENCE()
SEQUENCE is like a new enhancement in Identity.
Identity columns can be used to uniquely identify records in a table. But if we wants to maintain identity columns across multiple tables, or if we need identity column value before insertion of record into table, we can use SEQUENCE.
CREATE SEQUENCE CustomSequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000;
GO
CREATE TABLE TEST1
(
Id int,
Col1 varchar(100)
)
GO
CREATE TABLE TEST2
(
ID int,
Col2 varchar(100)
)
GO
INSERT INTO TEST1 values (NEXT VALUE FOR Custom_Sequence,'a')
INSERT INTO TEST2 values (NEXT VALUE FOR Custom_Sequence,'b')
SELECT * From Test1
SELECT * From Test2
19. EXECUTE : WITH RESULT SETS
When you want to change the data type of resulting column then EXECUTE : WITH RESULT SETS is very useful.
EXECUTE YourProcedure
WITH RESULT SETS
(
(
Col1 INT,
Col2 BIGINT
)
);
20. PAGINATION
In 2008 you are able to achieve pagination using Row_Number()
Select Name , Address, ROW_NUMBER() OVER (ORDER BY NAME) RN FROM TABLE
WHERE RN Between 2 AND 5
In Denali (SQL Server 2012)
SELECT
*
FROM Table
ORDER BY ID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
21. RAISERROR
RAISERROR in the format RAISERROR integer 'string' is discontinued.
Rewrite the statement using the current RAISERROR(…) syntax.
22. HashBytes
Two new algorithms are added to SQL Server Security portion
"SHA2256" and "SHA2512" as like MD2,MD4,MD5,SHA and SHA1.
23. THROW
In denali THROW function is implemented as like .NET. Now everyone can Throw error to calling application.
24. Code Snippets
Implemented code snippets feature to increase the usability and speed of coder. You can create your own code snippets and publish to using SSMS tools menu for future use.
To add a new code snippet,
Create the snippet file with .snippet extension(Its advisable to take existing snippet file and modify it)
Go to Tools -> Code Snippets Manager
Select the folder, under which you wants to add code snippet and click on Add button and select the snippet file.
25. Multi Monitor Support
Now using SSMS 2012, you can open multiple Query windows at a time. Easily you can drag the query window and can place that query window where ever we want.
26. Task List
SQL Devloper often use some external tools to manage their task list, where they will add tasks and will follow the tasks according to the task list.
This Tool is included in SQL Server 2012, where you can add/delete tasks and set the priority of these tasks.
In add task using SSMS,
Go to View -> Task List, which opens task list, where you can add a new task by clicking on Tick mark icon. You can set the priority by clicking on first column in that task.
27. Zoom/Magnify
In earlier versions of SQL Server - SSMS there is no facility to zoom their view. To do that you have to change the Font size to cop with the problem.
Now in SQL Server - SSMS 2012, you can get the Zoom in and Zoom out facility.
28. Debugging Enhancement
SQL Server SSMS 2012 has improved debugging facility. You can hit the break point based on condition. It is not possible in older version. It is the good features to trace the error point.
i.e You are iteration loop and you have to break at 5th iteration then Conditional break is very useful.
29. Surrond With
In built code snippets like Conditional and Iterative operator like IF, WHILE , BEGIN etc. When you are ready with you code and wish that code execute based on condition or iterate it until some condition then you can use Surround With option.
1. Right Click and choose “Surround with”, which shows IF,BEGIN,WHILE options. Or Alternatively press Ctrl+k,Ctrl+s
2. Choose any of the block, that you wants to include in your stored procedure/query
30. KeyBoard ShortCuts
If you are very familiar with Visual Studio short cuts and want the same for SSMS 2012 then you can import/export short cuts from Visual Studio to SSMS 2012.
31. Multiple ClipBorad
You can copy multiple code/items in Clipboard and use then at any time.
1. From a query window, copy the query content by pressing Ctrl+C
2. Copy another query content by pressing Ctrl+C
3. To access recently copied content, press Ctrl+Shift+V
4. Now to access the first copied content, press Ctrl+Shift+ V two times.
Thanks,
Alpesh
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.