FORMAT()-Returns formatted value of specific format. let's check with datetime format with sample data.
--Native method
select convert(varchar(12),getdate(),112)
--New Method
select format(getdate(),'yyyyMMdd')
select format(getdate(),'yyyy')
select format(getdate(),'MM-dd-yyyy')
select format(getdate(),'MM/dd/yyyy')
With the help of FORMAT Function we can format datetime format without using Convert/Cast.
For more info FORMAT Function.
CONCAT()-Is introduced in Denali 2012, which can be used to concatenate two or more strings. This function requires 2 to 254 arguments.
let's check with following demo example.
--native method
select 'sql' + 'server'+ 'Denali'
--New Method
select concat('sql' ,'server', 'Denali')
Now we create test table to check how concat function works.
create table test12
(
id int,
name varchar(10),
city varchar(10)
)
insert into test12
select 1,null,'anand'
--native method
select (name+city) from test12
select (isnull(name,'')+city) from test12
--New Method
select concat(name,city) from test12
For more details go to CONCAT Function
SQL Server 2012 Code name Denali arrived with New Features:
Some of new features are listed below:
1. New Built-in functions(t-sql):
-PARSE
-TRY_CONVERT
-TRY_PARSE
-DATEFROMPARTS
-DATETIME2FROMPARTS
-DATETIMEFROMPARTS
-DATETIMEOFFSETFROMPARTS
-EOMONTH
-SMALLDATETIMEFROMPARTS
-TIMEFROMPARTS
-CHOOSE (Transact-SQL)
-IIF (Transact-SQL)
-CONCAT (Transact-SQL)
-FORMAT (Transact-SQL)
2. Analytical Functions(t-sql):
-CUME_DIST
-LAST_VALUE
-PERCENTILE_DISC
-FIRST_VALUE
-LEAD
-PERCENT_RANK
-LAG
-PERCENTILE_CONT
3. SQL Server Express LocalDB
4. New Query Optimizer hints:
-ForceScan
-ForceSeek(Enhanced)
5. Extended OVer clause Enhancements:
6. Xevent Enhancements:
7. Database Tuning Advisor(DTA) Enhancements:
8. New Dynamic Management Views(DMVs) and function:
9. Partition Support Increased:
10.Columnstore Indexes(Data Warehouse query):
I will explain all new functions in my future posts.
Check out this link for more details SQL Server CTP3 2012
