TRY_PARSE

-- TRY_PARSE() function to translate value into specific datatype without throwing exceptions on failure

-- If the parse operation is successful, this function returns the resultant value; Otherwise, it returns NULL value.

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

TRY_CONVERT

-- Often in queries, we can see conversion errors like "conversion failed when converting from .... "
--If we wants to hide these errors and instead of exception, if we want a NULL value, we can use this TRY_CONVERT() function.

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

Throw

/*
In previous versions, by using TRY..CATCH block we used to catch the exceptions raised by application and by using RAISEERROR(), error has been thrown back to the called application. However RAISEERROR() has few drawbacks.

    It will not allow to throw the same exception number, we got in the code. Instead we need to create a custom exception and needs to to throw it.
    Because we are send the error separately, error line number also will change.

Now in SQL Server 2012, THROW command will solves these problems by allowing to throw the same exception we got in code */


BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR)
PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR)
PRINT 'Throwing error...';
THROW
END CATCH

Temp Tables Existance Check

--One of the breaking changes added in SQL Server 2012 is related to the creation of #TEMP tables.
--SQL Server 2012 assigns a negative value as the OBJECT ID of the #TEMP tables.

CREATE TABLE #cust (Col1 INT)
GO

-- Right Way:

IF OBJECT_ID('tempdb..#cust', 'U') IS NOT NULL
BEGIN
    PRINT 'Object Exists'
    -- Do Something
END


-- Wrong Way if used in SQL 2012

IF OBJECT_ID('tempdb..#cust') > 0
BEGIN
    PRINT 'Object Exists'
    -- Do Something
END

sp_describe_undeclared_parameters & sp_describe_first_result_set

USE [AdventureWorks2012]
GO

EXEC sp_describe_first_result_set
N'SELECT * FROM Sales.SalesOrderDetail', NULL, 1
GO


CREATE VIEW dbo.MyView
AS
SELECT [SalesOrderID] soi_v
,[SalesOrderDetailID] sodi_v
,[CarrierTrackingNumber] stn_v
FROM [Sales].[SalesOrderDetail]
GO

/* Now let us execute above stored procedure with various options.
You can notice I am changing the very last parameter which I am passing to the stored procedure.
This option is known as for browse_information_mode. */

--when BrowseMode is set to 1 the resultset describes the details of the original source database, schema as well source table.
--When BrowseMode is set to 2 the resulset describes the details of the view as the source database.

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 0;
GO

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 1;
GO

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 2;
GO

DROP VIEW MyView


-- It returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.

-- Build a query string that contains parameters
DECLARE @query NVARCHAR(MAX) = N'
SELECT
    object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR name = @name'

-- Retrieve metadata of @id and @name parameters
EXECUTE sp_describe_undeclared_parameters
    @tsql = @query

Sequence

/*
The new SEQUENCE type in SQL Server 2012 will allows you to create sequential numbers.
In general 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 the identity column before insertion of
record into table, we can use SEQUENCE type in SQL Server 2012.
*/

USE ExploringSQL
GO

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 (Id,Col1)
VALUES (NEXT VALUE FOR CustomSequence,'a')
INSERT INTO TEST2 (Id,Col2)
VALUES (NEXT VALUE FOR CustomSequence,'b')

SELECT * From Test1
SELECT * From Test2

DROP TABLE TEST1
DROP TABLE TEST2

Roll Up

-- COMPUTE BY REPLACED WITH ROLLUP IN SQL 2012

USE [ExploringSQL]
GO

CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT
)
GO

INSERT INTO tblPopulation VALUES('India', 'Delhi','East Delhi',9 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','South Delhi',8 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','North Delhi',5.5)
INSERT INTO tblPopulation VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Bangalore',9.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Manipal',1.5)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Mumbai',30)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Pune',20)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nagpur',11 )
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nashik',6.5)
GO

-- SELECT * FROM tblPopulation

SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country,[State],City
WITH ROLLUP
GO

SELECT Country,[State],City, [Population (in Millions)]
FROM tblPopulation
ORDER BY Country,[State],City
COMPUTE SUM([Population (in Millions)]) BY Country,[State]--,City
GO

DROP TABLE tblPopulation

PERCENTILE_CONT

/* This function will returns the continuous interpolated value at the specific offset

PERCENTILE_CONT() function will takes a parameter, which indicates the offset and it should be between 0 and 1. */

DECLARE @T TABLE
(
GroupID INT,
Number INT
)

INSERT INTO @T (GroupID,Number)
VALUES (1,10),(1,20),(1,30),(1,50),(1,60)

SELECT GroupID,Number,
PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY Number)
OVER (PARTITION BY GroupID) AS [InterpolatedAt0.4],
PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY Number)
OVER (PARTITION BY GroupID) AS [InterpolatedAt0.8]
FROM @T

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 interpolation, at given offset position.
*/

DECLARE @T TABLE
(
GroupID INT,
Number INT
)

INSERT INTO @T (GroupID,Number)
VALUES (1,10),(1,20),(1,30),(1,50),(1,60)

SELECT GroupID,Number,
            PERCENTILE_DISC(0.4) WITHIN GROUP (ORDER BY Number)
            OVER (PARTITION BY GroupID) AS [InterpolatedAt0.4],
                PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY Number)
                OVER (PARTITION BY GroupID) AS [InterpolatedAt0.8]
FROM @T

PERCENT_RANK

/*
PERCENT_RANK() function will returns the percentage value of rank of the element among its group.

PERCENT_RANK() function value will be

    For first element in its group, it will be 0.
    For last element in its group, it will be 1.
    For remaining elements, it will be ( No. Of Elements Before that Element ) / (Total Elements - 1)
*/

DECLARE @T TABLE
( 
Number INT
)
INSERT INTO @T (Number)
VALUES (10),(20),(30),(50)

SELECT Number,
PERCENT_RANK() OVER (ORDER BY Number) AS PERCENTRANK
FROM @T

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.

Example:

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

Pagination Support

-- Support for Pagination using ORDER BY along with OFFSET and FETCH NEXT

-- The new pagination support allows you to write queries that can return rows from specific position of the result set.

--For example: the following query returns rows 20 to 30 from the result set.

USE [AdventureWorks2012]
GO

SELECT  *  FROM [HumanResources].[Employee]
ORDER BY HireDate   
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;


SELECT   *  FROM [HumanResources].[Employee]
ORDER BY HireDate

OVER Clause is enhanced

USE [ExploringSQLR2]
GO

-- OVER Clause is enhanced to apply aggregate functions while applying "Order By"
-- Before SQL Server 2012, We can't use "Order By" clause, while we are using aggregate functions with OVER clause.

DECLARE @Companies TABLE
(  
    CompanyId int,
    [Year] int,
    Amount int
)
INSERT INTO @Companies (CompanyId,[Year],[Amount])
VALUES (1,2000,100000),(1,2001,200000),(1,2002,35000),
       (2,2000,50000),(2,2001,75000),(2,2002,35000)

SELECT * FROM @Companies

SELECT  CompanyId,[Year],Amount,
        SUM(Amount) OVER (PARTITION BY CompanyID
                            ORDER BY [Year]) AS CumulativeRevenue        
FROM @Companies


-- OVER Clause is enhanced with ROWS/RANGE Clause to limit the rows

DECLARE @Companies TABLE
( 
CompanyId int,
[Year] int,
Amount int
)

INSERT INTO @Companies (CompanyId,[Year],[Amount])
VALUES (1,2000,100000),(1,2001,200000),(1,2002,35000),(2,2000,50000),(2,2001,75000),(2,2002,35000)

SELECT * FROM @Companies

SELECT  CompanyId,[Year],Amount,
SUM(Amount) OVER (Partition by CompanyID
            ORDER BY [Year]
            ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS Next4YrsIncludingCurrentYrRevenue  ,

SUM(Amount) OVER (Partition by CompanyID
            ORDER BY [Year]
            ROWS  UNBOUNDED PRECEDING
) AS CumulativeRevenue,

SUM(Amount) OVER (Partition by CompanyID
            ORDER BY [Year]
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) AS Last5YrsRevenue

FROM @Companies

Date Functions

-- End of the Month

SELECT EOMONTH('12/25/2011')

--you can add months also using this function by passing duration in second parameter.

DECLARE @Currentdate DATETIME
SET @Currentdate = '12/12/2010'
SELECT EOMONTH ( @Currentdate,-2 ) AS Result;

-- DATEFROMPARTS function to create date based on specified month,year and day

DECLARE @Year INT,
@Month INT,
@Day INT
SET @Year = 2011
SET @Month = 11
SET @Day = 29

SELECT DATEFROMPARTS (@Year,@Month,@Day) as Result

-- TIMEFROMPARTS function to create time based on specified Hour,Minute and Second

DECLARE @Hour INT,
@Minute INT,
@Seconds INT,
@Fractions INT
SET @Hour = 15
SET @Minute = 20
SET @Seconds = 50
SET @Fractions = 500

SELECT TIMEFROMPARTS (@Hour, @Minute, @Seconds, @Fractions, 3) as Result

-- Note: Last Argument of this function should be integer constant and it indicates number of digits in milliseconds part. It can be between 3 to 7 digits.

-- This function returns a fully qualified DATETIME value based on the specified datetime parts such as year, month, day, hour, minute, seconds and milliseconds.

DECLARE
@Year INT = 2011,
@Month INT = 11,
@Day INT = 29,
@Hour INT = 15,
@Minute INT = 20,
@Seconds INT = 50,
@MilliSeconds INT = 0

SELECT DATETIMEFROMPARTS(
@Year,
@Month,
@Day,
@Hour,
@Minute,
@Seconds,
@MilliSeconds) as Result

LAG & LEAD

--This functions will return columns values from its previous rows in 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'


-- OLD METHOD:

SELECT U1.UserID, U1.UserName, U2.UserName AS PreviousUserName
FROM @Users U1
LEFT OUTER JOIN @Users U2 ON U2.UserID = U1.UserID - 1

-- USING LAG()

SELECT UserID,UserName,
LAG(UserName) OVER (ORDER BY UserName) AS PreviousRecordUserName
FROM @Users
ORDER BY UserName


-- LEAD(): This functions will return columns values from subsequent rows in 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'

-- OLD METHOD:

SELECT U1.UserID, U1.UserName, U2.UserName AS PreviousUserName
FROM @Users U1
LEFT OUTER JOIN @Users U2 ON U2.UserID = U1.UserID + 1

-- USING LEAD()

SELECT
UserId,UserName,
LEAD(UserName) OVER (ORDER BY UserName) AS NextUser
FROM @Users
ORDER BY UserName

Index Rebuild Online

USE TempDB
GO

CREATE TABLE TestTable
(ID INT, FirstCol NVARCHAR(10), SecondCol NVARCHAR(MAX))
GO

CREATE CLUSTERED INDEX [IX_TestTable]
ON TestTable
(ID)
GO

CREATE NONCLUSTERED INDEX [IX_TestTable_Cols]
ON TestTable
(FirstCol)
INCLUDE (SecondCol)
GO


ALTER INDEX [IX_TestTable_Cols] ON [dbo].[TestTable]
REBUILD WITH (ONLINE = ON)
GO

DROP TABLE TestTable
GO

IIF

DECLARE @Number1 INT,
        @Number2 INT,
        @true_value VARCHAR(20),
        @false_value VARCHAR(20)

SET @Number1 = 10
SET @Number2 = 20
SET @true_value = 'A is Big'
SET @false_value = 'B is Big'

SELECT IIF (@Number1 > @Number2, @true_value, @false_value) as Result

HASH BYTES Enhanced

-- HASH BYTES Enhanced

--Before SQL Server 2012, HashBytes function used to support following algorithms while encrypting a string.

--MD2,MD4,MD5,SHA and SHA1

--Now In SQL Server 2012, 2 more algorithms "SHA2256" and "SHA2512" has been added.

SELECT HASHBYTES('SHA2_256', 'Anitha Saradhi') AS 'SHA2256'
SELECT HASHBYTES('SHA2_512', 'Anitha Saradhi') AS 'SHA2512'

Format

/*
This function format the input value based on the specified format and optional culture input.
If the culture value is not specified, then the language of the current session is used.
This can be used to format the numbers and datetime values into required format.
*/

DECLARE @CurrentDate DATETIME,
        @Format NVarchar(20),
        @Culture NVarchar(10)

SET @CurrentDate = GETDATE(); 
SET @Format = 'dd-MM-yyyy:hh:mm:ss'
SET @Culture =  'ar-LB'

-- SELECT CONVERT(VARCHAR(20), GETDATE(),101)

SELECT FORMAT(@CurrentDate,@Format, @Culture)  as Result

SELECT FORMAT(@CurrentDate,@Format)  as Result

FORCESEEK Enhancement

-- FORCESEEK hint is extended to specify columns used to seek in the specified index

/*
Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table.
Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows,
the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset.
But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.

When SQL Server chooses seek over scan while retrieving record details, it will compare key value with search input, based on comparison result,
Query engine will move to appropriate page.
Suppose, index has multiple columns, if we don't want all key columns to consider, in SQL Server 2012, we can mention the index columns to consider when the index has multiple key columns.
*/

SELECT CompanyID,CompanyName,Amount
FROM COMPANIES
WITH (FORCESEEK(Idx_Company(CompanyID)))
WHERE CompanyID = 1