Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2012 – Behavior Changes’

SQL SERVER 2012 – Behavior Changes – sqlcmd Utility

Posted in SQL SERVER, Tools, tagged , , , , on May 15, 2013| Leave a Comment »

SQL Server 2012 came up with a lot of new enhancements, breaking changes & behavioral changes. In my earlier article, I had discussed about one of the nice behavior changes in exists function. Today, we will discuss another behavioral change in Sqlcmd Utility when XML mode is on. This utility is one of the handy utilities when you wish to execute transact SQL or any other SQL operations on command prompt.

Given below are sqlcmd utility behavioral changes in earlier version SQL Server vs 2012.

S.No

Behavior in Earlier versions

Behavior in SQL Server 2012

1

If any string data having a single quote will be replaced with the & apos; escape sequence, it will not remain a valid XML and an XML parser will not give the same result.

If any string data having a single quote will not be replaced with & apos; escape sequence, it will remain a valid XML and an XML parser will give the same result.

2

If any column of a table having money data values with no decimal value, it will be converted to integer.

If any column of a table having money data values with no decimal value, it will show the 4 decimal digits.

Let me create an example to demonstrate this behavior changes.

--Create table
CREATE TABLE [dbo].[tbl_Employee]
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[First Name] [nvarchar](50) NULL,
[Last Name] [nvarchar](50) NULL,
[Salary] [money] NOT NULL
)
GO
--Insert record into table
INSERT INTO dbo.tbl_Employee ([First Name],[Last Name],[Salary])
VALUES ('Reuben','D''sa',5000)
GO
--Browse table
SELECT [Last Name],[Salary] FROM dbo.tbl_Employee
--OUTPUT

Bchange1.1

Lets execute the above query in sqlcmd utility with XML mode on as shown in the given below picture.
Bchange1.3

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – Behavior Changes – exist() function

Posted in Functions, SQL SERVER, tagged , , , , on March 20, 2013| 1 Comment »

I have been using exist() function for a long time. This is very handy when we need to check the existence of any data in the xml document. There is also a bug related to this function when it comes to NULL but this bug is fixed in SQL Server 2012.

Let me explain it with examples.
Example 1 :
Create the given below script in ealier version of SQL Server (2005 & 2008)

DECLARE @xmltest XML;
SET @xmltest = null;
SELECT COUNT(*) as [T_Count] WHERE @xmltest.exist('/raresql') = 0;
--OUTPUT

T_Count
———–
1

(1 row(s) affected)

Ooopps…… The expected result is 0 because ‘/raresql’ doesn’t exist in the @xmltest but it returned 1 .

Example 2 :
Lets create the same sample in SQL Server 2012 and view the output.

DECLARE @xmltest XML;
SET @xmltest = null;
SELECT COUNT(*) as [T_Count] WHERE @xmltest.exist('/raresql') = 0;
--OUTPUT

T_Count
———–
0

(1 row(s) affected)

The expected result is 0 because ‘/raresql’ doesn’t exist in the @xmltest and as expected the query returned 0 as well.

Conclusion :
The behavior issue with exist() function on xml datatype is fixed in SQL Server 2012. If the SQL Server is upgraded from earlier version to 2012 and if some scripting is added to correct this behavior in the earlier version, then these scripts should be removed.

Reference : MSDN

Read Full Post »