SQL Server Denali CTP3-1

Downloading Link: https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx
This is a new version of SQL Server launched by AKAMAI. I have mentioned few features of this new technology 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)
11.Support online index rebuild with varchar(max), nvarchar(max), and varbinary(max) columns.

I have posted some linkes below what i have learned,

1. IIF - Returns one of two objects, depending on the evaluation of an expression
2. EOMONTH - Returns End Date of Month
3. New analytical functions, First_Value & Last_Value
4. CONCAT - Used to concatenate two or more strings
5. COLUMNSTORE Index
6. PERCENT_RANK() and CUME_DIST()
7. FORMAT - Returns formatted value from specified format of DATETIME
8. TIMEFROMPARTS
9. DATEFROMPARTS
10. PERCENTILE_CONT() and PERCENTILE_DISC()
11. sys.dm_server_services
12. LEAD() and LAG()
13. Partition support increased
14. Supports Rebuild online index with varchar(max),nvarchar(max) and varbinary(max) columns
15. TRY_CONVERT
16. PARSE ()
17. TRY_PARSE()
18. FORCESEEK and FORCESCAN
-----------========================================================--------------
1. TRY_CONVERT() - Returns a value to be cast to the specified data type if the cast succeeds; otherwise, returns null.
2. PARSE() - Returns the result of an expression, translated to the requested data type.
3. TRY_PARSE() - Returns the result of an expression, translated to the requested data type if succeeds and return null if fails.

Let us understand then in brief with examples. First i would like to start with TRY_CONVERT(), It has require three arguments, first is data_type(length) in which value cast, second is exprssion value and third style which is optional.

Examples:

01.SELECT TRY_CONVERT(FLOAT,'test')

02. 

03.SELECT TRY_CONVERT(CHAR(2),'test')

04. 

05.SELECT TRY_CONVERT(DATETIME,'4/5/2011')

06. 

07.SELECT TRY_CONVERT(DATETIME2,'4-5-2011')

08. 

09.SELECT TRY_CONVERT(VARCHAR(12),'4-5-2011')

10. 

11.SELECT TRY_CONVERT(TIME,'115056')

12. 

13.SELECT TRY_CONVERT(TIME,'4-5-2011')

14. 

15.SELECT

16.CASE WHEN TRY_CONVERT(BIGINT,'2011-06-09') IS NULL

17.THEN 'Failed'

18.ELSE 'Succeeded'

19.END

20. 

21.SELECT IIF (TRY_CONVERT(DECIMAL(10,2),'22.5') IS NULL,'Failed','Succeeded' )

22.GO

Now we will see next conversion function PARSE() which has require three parapeters, first is string_value which allows to enter nvarchar(4000) value and must be valid representation for requested datatype otherwise PARSE will raised error. Second is data_type which representing datatype requested for the result and third is culture which is optional and take a current language of session as default. But here are some limitation of the datatype which can used for requested result. In first example it parsing string value to datetime and bigint dataype and in second example parsing string value to numeric and datetime datatype and you can see there resulted output also.

01.SELECT PARSE('Monday, 13 December 2010' AS DATETIME)

02. 

03.SELECT PARSE('Monday, 13 December 2010' AS BIGINT)

04. 

05.SELECT PARSE('Monday, 13 December 2010' AS TIME)

06. 

07.SELECT PARSE('18' AS TIME)

08. 

09.SELECT PARSE('18' AS NUMERIC(18,2))

10. 

11.SELECT PARSE('18' AS DATETIME)

12.GO




At last we will review TRY_PARSE() which is same as PARSE() but TRY_PARSE() returns the result of an expression, translated to the requested data type if succeeds and return null if fails. It require same arguments as PARSE(). You can see the resulted output with both of the function here. We will test TRY_PARSE() and PARSE() function withy same parapeters and compare output as well.

01.SELECT PARSE('15' AS datetime)

02.SELECT TRY_PARSE('15' AS datetime2)

03. 

04.SELECT PARSE('4/4/2011' AS datetime2)

05.SELECT TRY_PARSE('4/4/2011' AS datetime2)

06. 

07.SELECT PARSE('18.2' AS BIGINT)

08.SELECT TRY_PARSE('18.2' AS BIGINT)

09. 

10.SELECT PARSE('18' AS NUMERIC(18,2))

11.SELECT TRY_PARSE('18' AS NUMERIC(18,2))

12.GO