SQL Server Denali – EOMONTH()

EOMONTH() returns the date value that represents the last day of the month for given date.
Syntax: EOMONTH( start_date [, months_to_add] )
Before Denali, to calculate the date value that represents the last day of the month we used:

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

In Denali, we can use EOMONTH function.

SELECT EOMONTH (GETDATE()) AS LastDay1, EOMONTH('2011-09-05') AS LastDay2

If we specify “months_to_add” argument, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.

SELECT EOMONTH(GETDATE(),-1) AS LastDayOfPreviousMonth, EOMONTH(GETDATE()) AS LastDayOfMonth, EOMONTH (GETDATE(),1)LastDayOfNextMonth

SQL Server Denali: ColumnStore() a new Index Function

It is special type of index which stored columns in separate set of data pages instead of rows stored in data pages.It is optimized for the improved and fast warehouse queries processing. In ordinary index rows are stored in disk pages but with columnstore index columns are stored in separate set of the disk pages, that why it is more faster then ordinary index. It is mostly optimized for queries which are used aggregation , warehouse processing and BI application. 


Syntax:
CREATE NONCLUSTERED COLUMNSTORE INDEX mycolumnstoreindex ON mytable (col1, col2, col3);


You can use the Object Explorer in Management Studio to create the index as follows:
Expand the tree structure for the table and then right click on the Indexes icon.
Select New Index and then Nonclustered columnstore index
Click Add in the wizard and it will give you a list of columns with check boxes.
You can either choose columns individually or click the box next to Name at the top, which will put checks next to all the columns. Click OK.
Click OK.

SQL Server Denali: CONCAT() Function

CONCAT() Function is new string function in SQL Server Denali. It returns a string that is the result of concatenating two or more string values (or values that can be converted to string).
Syntax: CONCAT(string1, string2, string3, ...)  
With CONCAT function, you can concatenate between 2 and 254 values. If you try to use CONCAT with only one value or with more than 254 values you will get an error.

Example:
select concat ('First' , ' ' ,'Second',' ','Three')
It will return "First Second Third"

CONCAT function has 2 advantages compared to the concatenating of strings using the “+” operator:
1. NULL values are implicitly converted to an empty string
2. All arguments are implicitly converted to string

SQL Server Denali– PARSE() and TRY_PARSE() conversion functions

PARSE() function converts expression (string value) to date/time and number data types if conversion is possible. If conversion isn’t possible it raises an error.

TRY_PARSE() function converts expression (string value) to date/time and number data types or return NULL if conversion isn’t possible. It basically identifies if the type specified is applicable for parsing or not and returns the appropriate status. 

We often see the error, "Conversion failed when converting the varchar value 'dsfds' to data type int.". Now we can avoid this error by using Try_Parse() function. If we are in doubt that there might be some invalid values, by using this TRY_PARSE() function, we can avoid this errors and it will generate NULL values for invalid values. You can convert data to different datatype using new try_parse() function introduced in SQL Server Denali. It returns data if parse is successful otherwise it returns null.

Example

SELECT PARSE('08' AS datetime)

-- Raised an error
--Msg 9819, Level 16, State 1, Line 1
--Error converting string value '15' into data type datetime using culture ''.

SELECT TRY_PARSE('08' AS datetime2)

--  Returned NULL

SELECT PARSE('8' AS NUMERIC(15,2))

-- Returned 8.00

SELECT TRY_PARSE('8' AS NUMERIC(15,2))

-- Returned 8.00

GO

SQL Server Denali: FIRST_VALUE() and LAST_VALUE()

In SQL Server Denali introduced these two analytic function. As the Name suggests First_Value() returns the first value in an ordered set of values and Last_Value() returns the Last value in an ordered set of values.


Example:First_Value
SELECT Cust_ID, Cust_Name, FIRST_VALUE(Cust_ID) OVER (ORDER BY Cust_ID) AS [First Value] FROM   Cust_Table Result:
Cust_ID   Cust_Name First Value
1               A                  1
2               B                  1
3               C                  1
4               D                  1


Example:Last_Value
SELECT Cust_ID, Cust_Name, LAST_VALUE(Cust_ID) OVER (ORDER BY Cust_ID) AS [Last Value] FROM   Cust_Table 
Result:
Cust_ID   Cust_Name Last Value
1               A                  4
2               B                   4
3               C                  4
4               D                  4