New T-SQL Functions in SQL Server 2012

Getting Start and End of the Month made easy in 2012:

--End of the Month

SELECT EOMONTH(GETDATE())

--Start of the Month

SELECT DATEADD(D,1,EOMONTH(GETDATE(),-1))

TRY_PARSE and PARSE Functions:

TRY_PARSE returns the result of the expression, translated to the requested data type, or null if the cast fails.

Detecting nulls with TRY_PARSE:

Select TRY_PARSE ('01/01/1900' as Int) As Result;

While PARSE returns the result of the expression, translated to the requested data type.

SELECT PARSE('Monday, 13 December 2010' AS datetime) AS Result;

Using IIF with TRY_PARSE:

Select IIF(TRY_PARSE ('01/01/1900' as Int) IS NULL,1,0) As Result;

Functions SOME, ANY and ALL:

Compares a scalar value with a single-column set of values. SOME and ANY are equivalent.

The following statements create a simple table and add the values of 10, 20, 30, 40 and 50 to the ID column:

CREATE TABLE #TESTFn

(TESTID int) ;

GO

INSERT #TESTFn VALUES (10) ;

INSERT #TESTFn VALUES (20) ;

INSERT #TESTFn VALUES (30) ;

INSERT #TESTFn VALUES (40) ;

INSERT #TESTFn VALUES (40) ;

The following query returns TRUE because 25 is less than some of the values in the table:

IF 25 < SOME (SELECT TESTID FROM #TESTFn)

PRINT 'TRUE'

ELSE

PRINT 'FALSE' ;

The following query returns FALSE because 25 is not less than all of the values in the table.

IF 25 < ALL (SELECT TESTID FROM #TESTFn)

PRINT 'TRUE'

ELSE

PRINT 'FALSE' ;

Add a record for every day of a given date range

declare @Months int = -5 --variable defined to go back number of months one wanted

declare @StartDate date = DATEADD(d,1,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())+@Months, -1)) -- to get first day of the month

declare @EndDate date = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()),-1); -- to get last day of the month

WITH Dates([Date]) AS (

--Select First day in range

SELECT CONVERT(DATETIME,@StartDate) AS [Date]

UNION ALL

--Add a record for every day in the range

SELECT DATEADD(DAY, 1, [Date]) FROM Dates WHERE Date < CONVERT(DATETIME,@EndDate))

select * from Dates Option (maxrecursion 0)

Without Option (maxrecursion 0) will result in error, as the maximum recursion by default allowed in a CTE is 100.

How to do a case sensitive GROUP BY or data COMPARISON?

SELECT t.Name INTO #table

FROM (

       SELECT 'temp' AS Name

       UNION all

       SELECT 'TEMP' AS Name

       UNION all

       SELECT 'Temp' AS Name

       ) t

 Following query result in all 3 rows:

SELECT * FROM #table WHERE name = 'Temp'

 However, following query results in only the matched row as I have used the use an case sensitive collation:

SELECT * FROM #table WHERE name COLLATE Latin1_General_CS_AS = 'Temp'

 Similarly for group by, without case sensitive collation:

SELECT COUNT(1) FROM  #table WHERE name = 'Temp'

GROUP BY name

 With case sensitive collation:

SELECT count(1) FROM  #table WHERE name = 'Temp'

GROUP BY name COLLATE Latin1_General_CS_AS

SQL Server Management Studio (SSMS) Tips and Tricks

Change the status bar in the "New Query" window to red when connected to production or green when connected to development.

Open SSMS and in the "Connect to Server" dialog click the "Options" button.  Select the "Connection Properties" tab and check the box next to "Use custom color".  Select your desired color and that's it!

Refresh IntelliSense - If you create a new table/column and IntelliSense just doesn't seem to be working?

Refresh IntelliSense:

Don't have intellisense enabled? Go to Tools, Options.  Text Editor, Transact-SQL, IntelliSense.

Vertical Selection in Management Studio:

Sometimes you may only want to select text vertical compared to the normal text selection done by holding down the Shift Key. To do that, you can do SHIFT+ALT and drag your mouse to the selection of your text.