New T-SQL functions in SQL Server 2012

New T-SQL functions in SQL Server 2012:

--PARSE()

--Parse Currency Symbol

SELECT PARSE('$100' AS MONEY USING 'EN-US') AS Currency

--Result = 100.00

--Parse DATETIME

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS US_Date

--Result = 2010-12-13 00:00:00.0000000

--CONCAT()

SELECT CONCAT('Firstname',' ','Surname') AS MyName

--Result = Firstname Surname

--CHOOSE()

DECLARE @Letter INT = 4

SELECT CHOOSE(@Letter, 'A','B','C','D', 'E','F')

--Result = D

--IIF()

DECLARE @Letter INT = 5

SELECT IIF(@Letter % 2 > 0,'ODD','EVEN')

--Result = ODD

--Get Date for the provided yy,MM,dd

SELECT DATEFROMPARTS(1999,2,3)

--GET LastDate of Month

SELECT EOMONTH(GETDATE(),0)

SQL 2012 Snippet Feature to Create Syntax for Objects

SQL 2012 new feature includes Snippet to Create Syntax for adding new objects.
When a user click (Ctrl + K, Ctrl + X). The below snippets occurs providing option to select objects like Table, Stored Procedure, etc.

SQL 2012 FORMAT() Function

Formatting in T-SQL - SQL 2012

SELECT
 FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO_Date],
 FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [ISO_Date_time],
 FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [EN_DateFormat],
 FORMAT(GETDATE(), 'MMMM dd, yyyy', 'fr-FR') AS [French_Format],
 FORMAT(22.7, 'C', 'en-US') AS [US_Currency],
 FORMAT(22.7, 'C', 'en-GB') AS [UK_Currency],
 FORMAT(99 * 2.226, '000.000') AS [Decimal],
 FORMAT(12345678, '0,0') AS [Thousand Separator]
;

SQL 2012 Throw function

As like in C#, a new function THROW is introduced in SQL 2012 to make try and catch error method more simple instead of using RAISEERROR.

BEGIN TRY BEGIN TRANSACTION -- Start the transaction
DELETE FROM Customers
   WHERE EmployeeID = @EmpID -- Commit the change
COMMIT TRANSACTION
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
 THROW
END CATCH

Pagination OFFSET and FETCH Commands in SQL 2012

A new built-in functions for pagination is introduced in SQL 2012. Using this we can skip 'n' number of top rows and retrieve other rows.

CREATE TABLE dbo.Employee
(
ContactId int IDENTITY(1,1) NOT NULL,
FirstName varchar(60),
LastName varchar(60),
Phone varchar(60),
Email nvachar(100)
LocationID int
);

INSERT INTO dbo.Employee
Select firstname, lastname, phone, EmailId, LocationID
FROM dbo.EmployeeAddress

The below query skip top 100 rows  and retrieves the next 10 records:
SELECT ContactId, FirstName, LastName, Phone
FROM dbo.Employee
ORDER BY ContactId
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY;

Configure Change Data Capture Parameters in SQL Server

After enabling CDC in SQL Server (see: http://mahadevanrv.blogspot.in/2011/05/change-data-capture-in-sql-server.html). We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.

Before configureing one should understand the basic terms in CDC Configuration:

 

Polling interval – (Default 5 seconds) the amount of time to wait between log scans

Maxtrans – (Default 500) the number of transactions to grab with each scan

Maxscans – (Default 10) the number of scans performed between each polling interval

Retention – (Default 72 hours, 4320 mins, 3 days). The period for which the new/updated/deleted data have to be retrieved and displayed in CDC tables. 

Execute the below query to get the CDC configured values:

SELECT * from msdb.dbo.cdc_jobs


Execute the below query to change capture instances:

EXEC sys.sp_cdc_change_job @job_type = 'capture'
       ,@maxtrans = 501
       ,@maxscans = 10
       ,@continuous = 1
       ,@pollinginterval = 5


Execute the below query to change retention period:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'
                          ,@retention = 4320 -- Number of minutes to retain (72 hours)
                          ,@threshold = 5000


Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc.

'Sequence' New Feature in SQL 2012

Sequence is an user defined object that generates a sequence of a number.

/****** Create new Sequence Object ******/
 CREATE SEQUENCE FirstSequence
 START WITH 1
 INCREMENT BY 1;


/****** Create Temp Table ******/
 DECLARE @Employee TABLE
 (
 ID int NOT NULL PRIMARY KEY,
 Employee nvarchar(100) NOT NULL
 );

/****** Insert Some Data ******/
 INSERT @Employee (ID, Employee)
 VALUES (NEXT VALUE FOR MySequence, 'Maha'),
 (NEXT VALUE FOR MySequence, 'Sam'),
 (NEXT VALUE FOR MySequence, 'Ram');

/****** List the Data ******/
SELECT * FROM @Employee;

The output will be as follows:-------------
ID  Employee
-------------
1 Maha
2   Sam
3 Ram
-------------