It used to be bad enough just to keep up with the new features and when to use them. Lately, a new wrinkle has been added to the mix that I have absolutely no appreciation for and, to be honest, wish that Microsoft would stop doing.

Here's one classic example that I'm doing a lightning presentation for...

First, we create some test data... a simple column of whole dates...

--===== If the test table already exists,

-- drop it to make reruns in SSMS easier

IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Create a million random dates as the DATETIME

-- datatype for the 2010 decade and store them in

-- a Temp Table. This only takes a second.

SELECT TOP 1000000

SomeDate = DATEADD(dd,

ABS(CHECKSUM(NEWID()))

% DATEDIFF(dd,'2010','2020'),

'2010')

INTO #MyHead

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

Then, we compare the FORMAT function to the classic CONVERT function...

PRINT '--===== CONVERT mm/dd/yyyy (101) ==========';

SET STATISTICS TIME,IO ON;

DECLARE @Bitbucket CHAR(10);

SELECT @Bitbucket = CONVERT(CHAR(10),SomeDate,101) --mm/dd/yyyy

FROM #MyHead;

SET STATISTICS TIME,IO OFF;

GO

PRINT '--===== FORMAT MM/dd/yyyy w/o Datatype Match =========='

SET STATISTICS TIME,IO ON;

DECLARE @Bitbucket CHAR(10);

SELECT @Bitbucket = FORMAT(SomeDate,'MM/dd/yyyy')

FROM #MyHead;

SET STATISTICS TIME,IO OFF;

GO

PRINT '--===== FORMAT MM/dd/yyyy w/ Datatype Match =========='

SET STATISTICS TIME,IO ON;

DECLARE @Bitbucket NCHAR(10);

SELECT @Bitbucket = FORMAT(SomeDate,N'MM/dd/yyyy')

FROM #MyHead;

SET STATISTICS TIME,IO OFF;

GO

... and the results clearly demonstrate my exasperation with many so called "improvements"...

--===== CONVERT mm/dd/yyyy (101) ==========

Table '#MyHead_____________________________________________________________________________________________________________000000000023'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 860 ms, elapsed time = 855 ms.

--===== FORMAT MM/dd/yyyy w/o Datatype Match ==========

Table '#MyHead_____________________________________________________________________________________________________________000000000023'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 39343 ms, elapsed time = 41775 ms.

--===== FORMAT MM/dd/yyyy w/ Datatype Match ==========

Table '#MyHead_____________________________________________________________________________________________________________000000000023'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 38782 ms, elapsed time = 40208 ms.

[font="Arial Black"]Seriously???? 44 Times Slower??? :sick:[/font]

Another fine example is for running totals. See the following URL...

http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/#codesyntax_1

Yeah, I know that's a while ago and I haven't tested it since Wayne wrote that but, seriously??? Running totals that are more than 7 times slower than a Quirky Update? What did they do behind the scenes? Write it as a recursive CTE??? :Whistling::sick: