Pages

Friday, November 8, 2013

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,

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.

Wednesday, November 6, 2013

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.

Monday, November 4, 2013

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,

WHILE @pages>@pageNumber BEGINSET @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+1END; GO

After SQL Server 2005 released, itcontains many newfeatures 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
(
SELECTROW_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
(
SELECTROW_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,

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.

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."