SQL Server 2012 TSQL enhancements – Part 1


SQL Server 2012 is the major release of Microsoft database server. In this release Microsoft has added few T-SQL enhancements to make common tasks much easier. These features can be leverage in writing efficient SQL query.

In this article we will cover new string functions introduced to replace CAST,CONVERT,DATEPART functions in some cases.

You can run below code snippets in query analyzer.

Let’s start with TSQL 2012 – Part 1…

String Functions:

1. CONCAT

a. Concatenate two or more string values.

b. Till SQL 2008 we leverage “+” operator in string expressions to concatenates two or more character, columns or binary strings. E.g. “FirstName + ‘ ‘ + LastName”

c. This function accepts comma separated values that implicitly converts to string type and concatenate them into single string.

Syntax – CONCAT ( string_value1, string_value2 [, string_valueN ] )

Code snippets:
PRINT ‘Current Time ‘ + GETDATE()
Throws an error message.

PRINT ‘Current Time ‘ + CAST(GETDATE() AS VARCHAR(20))
We have to explicitly convert DATETIME to string type as SQL only accepts string values in concatenation.

PRINT CONCAT(‘Current Time ‘, GETDATE())

SELECT CONCAT(1, 2, 3, 4, 5)

SELECT CONCAT(‘Concat’, ‘ ‘, ‘Example’)

2. FORMAT

a. Returns a value formatted into specified format.

b. Accepts the culture parameter which is optional.

c. This function can be mostly used to convert Date/Time and number values as string. Alternate solution for CAST/CONVERT function.

Syntax – FORMAT ( value, format [, culture ] )

Code Snippets:
DECLARE @date DATETIME = GETDATE()

SELECT FORMAT(@date, ‘dd’)

SELECT FORMAT(@date, ‘yyyy/mm/dd’)

SELECT FORMAT(@date, ‘hh:mm’)

Feel free to share your comments on this post.

Advertisements

One thought on “SQL Server 2012 TSQL enhancements – Part 1

  1. Pingback: SQL Server 2012 TSQL enhancement – Part 2 | SQL Server Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s