Tag: xml

Operand type clash?

From time to time you might come across this error.

Msg 206, Level 16, State 2, Line 13
Operand type clash: int is incompatible with date

But what causes it?  It is similar to a type mismatch error in the fact that there is a comparison of two data points that are of different data types they are not compatible. 

Let’s recreate this error.  The code below will create a temp table and then insert a row.  The final step will read from the table.  Notice that the HireDate column is a Date data type while the comparison in the WHERE clause is comparing the Hiredate column to in TINYINT data type.

–Create Table

CREATE TABLE #Test (
ID INT,
HireDate DATE,
)

–Insert a row

INSERT INTO #Test(ID,HireDate)
VALUES (102,6-6-2005)

–Read the data and recreate the error

SELECT  *
FROM #Test
WHERE HireDate = 14

As you may know, SQL Server will attempt to automatically convert the data to a similar data type using the data type precedence list.  From Microsoft, below is the order of the precedence.  For example, if SQL Server sees a comparison of a VARCHAR and an INT data type, SQL Server will attempt to convert the VARCHAR to an INT data type because INT is higher in the list.

          1. user-defined data types (highest)
          2. sql_variant
          3. xml
          4. datetimeoffset
          5. datetime2
          6. datetime
          7. smalldatetime
          8. date
          9. time
          10. float
          11. real
          12. decimal
          13. money
          14. smallmoney
          15. bigint
          16. int
          17. smallint
          18. tinyint
          19. bit
          20. ntext
          21. text
          22. image
          23. timestamp
          24. uniqueidentifier
          25. nvarchar (including nvarchar(max) )
          26. nchar
          27. varchar (including varchar(max) )
          28. char
          29. varbinary (including varbinary(max) )
          30. binary (lowest)

In our example, SQL Server is attempting to convert the TINYINT value of 14 to a DATE data type. Of course, this isn’t possible and the error will be thrown.

Now we know what can cause this error, what do we do to fix it?  The solution is to address the data type issue.  This can be done a number of ways, including using the CONVERT\CAST functions or even changing the data type in the tables.  

This error can not only happen in SELECT statements, it can will happen on other DML statements as well.

If you run this statement against the above temp table, you will get the same error.

— Insert a new row and receive the error

INSERT INTO #Test(ID,HireDate)
VALUES (102,13)

Hopefully this helps you out a bit and thanks for visiting my blog!

 

 

 

Select Distinct and XML data

As you know, the DISTINCT key word can be used to eliminate duplicate rows from the result set of a query.  

For example:

If you run this query against the AdventureWorks2014 database you will get 19614 rows with many duplicates.

SELECT City
FROM [AdventureWorks2014].[Person].[Address]

As you can see in the above results set there are duplicates.  Albany appears  4 times. However, my goal is to simply see what cities the database has addresses in.

I can address this by using the DISTINCT keyword.  The code below show that you simply add it just after the SELECT keyword.  Here is a link for more information on DISTINCT.

SELECT DISTINCT City
FROM [AdventureWorks2014].[Person].[Address]
ORDER BY City

The result set below shows that Albany only appears one time, duplicates have been removed.

Well, what if the result set contains XML data?  Will the DISTINCT work the same way?  The answer is no.  

Since I was working on an example for my next performance class, I decided to use an example from that session.  In the query below the qp.* part of the column list will return a column named query_plan which is an XML data type.

SELECT DISTINCT pa.,cp.,qp.*
FROM
(SELECT plan_handle
FROM sys.dm_exec_cached_plans) cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle ) qp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) As pa
WHERE is_cache_key = 1 AND pa.attribute = ‘set_options’

However, when I add the DISTINCT keyword I get the error below.

So what do I do about.  You can simply use the CONVERT function to convert that datatype from XML to VARCHAR(MAX) like below.

SELECT Distinct qp.dbid
, qp.encrypted
, CONVERT(VARCHAR(MAX),qp.query_plan)
FROM
(SELECT plan_handle
FROM sys.dm_exec_cached_plans) cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle ) qp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) As pa
WHERE is_cache_key = 1 AND pa.attribute = ‘set_options’


Just a disclaimer, I didn’t analyze this for performance because the class is about an unrelated topic. If you plan on using this in production code, please assess the impact on performance.

I will put this into the “You learn something new everyday” category!   As always, it is always fun to learn and I hope you did as well.  Thank you for visiting my blog.