What's new in SQL server 2012 - Date time

What's new in SQL server 2012 - Date time

                                                Date & Time is main area when we talk about database or database development. I have discuss many issue in SQL Server Data & time data type. if you need to read it again then following blog post will reach you there, 

• ISDATE Function
• Minimum year value

 well, today I'm going to discuss about SQL Server 2012 newly added Date & time related features.Before start this topic hope you will notice I have discuss about ISDATE function in my above blog link. As a solution of SQL Server 2012 development team will introduce following new functions, 

• TRY_CONVERT

                          This is very good move of SQL Server 2012 because T-SQL has very poor data validation,those issue will be a good solution above Try_convert function.   

USE tempdb
GO
SELECT CASE WHEN TRY_CONVERT(int, '1,200.00') IS NULL THEN 'False' ELSE 'True' END AS ResultOut;
GO
SELECT CASE WHEN TRY_CONVERT(datetimeoffset, '2013-11-08 23:59:59.3593750 +05:30') IS NULL THEN 0 ELSE 1 END AS is_date;
GO

• TRY_PARSE 

                      This function also more likely above Try_convert function. 

USE tempdb
GO
SELECT TRY_PARSE('100' AS int) AS ResultOut;
GO
SELECT TRY_PARSE('100.1' AS int) AS ResultOut;
GO
SELECT TRY_PARSE('100.0' AS int) AS ResultOut;
GO

                    only two different i have notice above both functions as follows,  

• Try_parse only allows string as input.
• try_parse just parse values but try_convert convert the values like this,

SELECT TRY_PARSE('100.0' AS int) AS ResultOut;  --True
GO
SELECT TRY_CONVERT(int,'100.0') AS ResultOut; --Error
GO

• PARSE

                This also more likely TRY_PARSE function. only different is if values not match then it will returns error,

USE tempdb
GO
SELECT PARSE('100' AS int) AS ResultOut; --True
GO
SELECT PARSE('100.1' AS int) AS ResultOut; --False
GO
SELECT PARSE('€345,98' AS money USING 'de-DE') AS ResultOut;
GO
SELECT PARSE('345,98' AS money USING 'ja-JP') AS ResultOut;
GO

• DATEFROMPARTS 

                               This is very simple function, it will concatenate year , month and day. And validate it too,

USE tempdb
GO
SELECT DATEFROMPARTS ( 2013, 11, 30 ) AS ResultOut;
GO
SELECT DATEFROMPARTS ( 2013, 11, 31 ) AS ResultOut;--error
GO
SELECT DATEFROMPARTS ( 2012, 02, 29 ) AS ResultOut;
GO
SELECT DATEFROMPARTS ( 2013, 02, 29 ) AS ResultOut;--error
GO

• DATETIME2FROMPARTS , DATETIMEFROMPARTS & DATETIMEOFFSETFROMPARTS 

                                          These functions mostly same as above function, after seen of Syntax you will realize it,

Syntax         
   

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

           

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

 
Following example will help you more clearly,

USE tempdb
GO
SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 ) AS ResultOut;
GO
SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS ResultOut;
GO
SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS ResultOut;
GO

• EOMONTH 

                     This is one of my favourite function.Earlier SQL Server versions if you need to get end of the moth I have used following SQL statement,

USE tempdb
GO
DECLARE @date datetime= '11/20/2013';
SELECT DATEADD (mm,1,@date-(DAY(@date)-1))-1;
GO

  But now you have no need to write above painful SQL statements you can use as follows,

USE tempdb
GO
DECLARE @date datetime= '11/20/2013';
SELECT EOMONTH ( @date ) AS ResultOut;
GO 

SQL Server MERGE + IGNORE_DUP_KEY = ON

SQL Server MERGE + "IGNORE_DUP_KEY = ON"

                                                                         I'm huge fan of SQL Server Merge command and I'm no hesitation to recommended SQL Server Merge DML statement, if you have INSERT,UPDATE and DELETE at once and with validate not exists records.But recently one of our developer reported Insert records will fail with Duplicate key error but they already have "IGNORE_DUP_KEY = ON" their target table.Initially I was hope we can insert duplicate records without any error if you have  "IGNORE_DUP_KEY = ON" but I was wrong that time. Just I have read SQL Server MERGE  Syntax and remarks on MSDN. And I seen Microsoft mention clearly "If IGNORE_DUP_KEY is set to ON for any unique indexes on the target table, MERGE ignores this setting." Well... so no argument on that :) . Long time back (around 5 years ) I have publish article on SQLServerCentral.com(one of my favourice web site) about "Understanding the MERGE DML Statement in SQL Server 2008" and I have not include this on my article. Any way I'm recommended you to read this article if you also still new with SQL Server MERGE command.

Well let's try to reproduce "duplicate key" Error     

USE [tempdb]
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U')) BEGIN
    DROP TABLE [dbo].[tblOffsetAndFetch];
END  
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U'))
BEGIN
    CREATE TABLE [dbo].[tblOffsetAndFetch](
        [id] [int] NOT NULL,
        [name] [varchar](50) NULL,
        [cDate] [datetime] NULL,
        [intData] [int] NULL,
        [decimalData] [decimal](21, 5) NULL,
        [floatData] [float] NULL,
    PRIMARY KEY CLUSTERED
    (
        [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
END
GO
SELECT * FROM [dbo].[tblOffsetAndFetch];
GO
MERGE [dbo].[tblOffsetAndFetch] AS A
USING (SELECT '01' [id],'Records 01' [name],Getdate() [cDate],01 [intData],1.1 [decimalData],1.11 [floatData]
UNION
SELECT '01' [id],'Records 02' [name],Getdate() [cDate],02 [intData],2.2 [decimalData],2.22 [floatData]) AS I
ON (A.id = I.id)
WHEN NOT MATCHED
THEN INSERT VALUES(I.id, I.[name], I.[cDate], I.[intData], I.[decimalData], I.[floatData]);
GO
SELECT * FROM [dbo].[tblOffsetAndFetch];
GO

Results,

 (0 row(s) affected)
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__tblOffse__3213E83FABB9779A'. Cannot insert duplicate key in object 'dbo.tblOffsetAndFetch'. The duplicate key value is (1).
The statement has been terminated.

(0 row(s) affected)

Well! hope now you will understand the above error occur situation.And I have found some helpfull artical on SQL Server MERGE DML statement and I hope it will help you all, 

•  Indexed view is not updated on data changes in base table
• If you are using indexed views and MERGE, please read this!

Cheers...
and Most welcome your comments and ideas...

What's new in SQL server 2012

What's New In SQL Server 2012 - OFFSET and FETCH

                                                    Earlier days(SQL Server 2000) hope you all remember when we need to display reports with page break, For a example if you have 100 records but you need to display 5 rows per page then how much difficult to write T/SQL script for that?, hope you all remember following SQL statements,

SQL Server 2000(T-SQL)    

USE tempdb
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U')) BEGIN
    DROP TABLE [dbo].[tblOffsetAndFetch];
END   
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U'))
BEGIN
    CREATE TABLE [dbo].[tblOffsetAndFetch](
        [id] [int] PRIMARY KEY,
        [name] [varchar](50),
        [cDate] [datetime] NULL,
        [comments] [varchar](200) NULL
    ) ON [PRIMARY];
END
GO
INSERT INTO [dbo].[tblOffsetAndFetch]([id],[name],[cDate],[comments])
SELECT id,name,crdate,name+'| Type - '+xtype FROM sys.sysobjects;
GO
 

--For SQL server 2000
DECLARE @noofrows        smallint;
DECLARE @pages            smallint;
DECLARE @pageNumber        smallint;
DECLARE @ssql            varchar(8000);
SET @noofrows=5;
SET @pageNumber=0;
SET @pages=((SELECT COUNT(*) FROM [dbo].[tblOffsetAndFetch])/@noofrows)+CASE WHEN ((SELECT COUNT(*) FROM [dbo].[tblOffsetAndFetch])%@noofrows)>1 THEN 1 ELSE 0 END;

SELECT * FROM [dbo].[tblOffsetAndFetch] ORDER BY id;

WHILE @pages>@pageNumber BEGIN
    SET @ssql='SELECT TOP '+CAST(@noofrows AS varchar(3))+' * FROM [dbo].[tblOffsetAndFetch] M WHERE M.id NOT IN(SELECT TOP '+CAST(@noofrows*@pageNumber AS varchar(3))+' D.id FROM [dbo].[tblOffsetAndFetch] D ORDER BY id);';
    EXEC(@ssql);
    SET @pageNumber=@pageNumber+1
END;   
GO

After SQL Server 2005 released, it contains many new features like

ROW_NUMBER() according to row_number function you can convert above task 

very simple way as follows,

--For SQL server 2005 and 2008R2

;WITH T (RowNumber,id,name,cDate,comments)
AS
 (
  SELECT ROW_NUMBER() OVER(ORDER BY A.id) AS RowNumber,A.id, A.name, A.cDate, A.comments 
  FROM [dbo].[tblOffsetAndFetch] A
 ) 
SELECT * FROM T WHERE RowNumber BETWEEN 0 AND 5;

  
;WITH T (RowNumber,id,name,cDate,comments)
AS
 (
  SELECT ROW_NUMBER() OVER(ORDER BY A.id) AS RowNumber,A.id, A.name, A.cDate, A.comments 
  FROM [dbo].[tblOffsetAndFetch] A
 ) 
SELECT * FROM T WHERE RowNumber BETWEEN 6 AND 10; 

Well After SQL Server 2012 released, SQL Server Development team has done many new addition to 

SQL Server like OFFSET and FETCH.With Using above commands you can fulfill above task with following way,

--1st 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

--2nd 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

--3rd 5 rows
SELECT * 
FROM [dbo].[tblOffsetAndFetch]
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Now you will notice how much easy with new "OFFSET and FETCH" commands in SQL server. 

mySQL you have limit function and PL/SQL rownumber column you can help to do above task in same output results. 

But this time I hope SQL server more smarter than Oracle(Im not sure about new PL/SQL commands in Oracle 12c) 

Let me explain about execution plan for this,

Well! now you can realize how much performance impact on SQL server new commands.

Cheers... 
and Most welcome your comments and ideas... 

SQL Server : A system assertion check has failed...

SQL Server : A system assertion check has failed

                                                                    Last Week we have to face SQL Server error. while executing SQL Server(T/SQL) script(Script contains SQL Server Cursor with Update). It's really confuse me after restart(physical server restart not SQL server service) database server (luckily this error occur in our development database environment) it's fine, but again it's coming.

Full Error and details as follows,

Location:     pageref.cpp:1340

Expression:   0 == pageFull

SPID:         55

Process ID:   1041

Msg 3624, Level 20, State 1, Line 60

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

After execute CHECKDB it wont display any errors. 

DBCC CHECKDB WITH NO_INFOMSGS;
GO

 And I have found SQL Server Hotfix related about this error. And Microsoft mention cause of this error is "The issue occurs because the length of the key data is bigger than the length of the compressed key value buffer."

 KB980832

 Download

Prerequisites

You must have SQL Server 2008 Service Pack 1 installed to apply this hotfix.

Related Information

Following related information i hope will help you all,

• MSDN Forum  
• SQLservercentral Forum
• John Huang's Blog

Cheers... 
and Most welcome your comments and ideas...