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

Shrink tempdb

There are several ways to Shrink TempDB:


Method 1:
Restart SQL Server, it will create new empty TempdB.


Method 2:
DBCC FREEPROCCACHE
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N’tempdb’ , 5000)
GO


Method 3:
Run the following Transact-SQL commands:
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = target_size_in_MB) 
--Desired target size for the data file
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = target_size_in_MB) 
--Desired target size for the log file

SQL Server Denali : Choose() Function

Choose() function returns an input argument from a list of input values at the desired index which is specified as an input arguement as well.
Syntax: CHOOSE (index, arg1, arg2 [, argN])
The first input argument to the Choose() function is "index" and it is in integer data type. The input arguments can be thought as an array with index starting from 1.
Example:
select choose (1,'a','b','c','d')
It will return 'a'.
If no Value exist at the specified index than it will return null.
select choose (5,'a','b','c','d')
It will return null.