SQL Server – XML / Path – GUID – Impedance Mismatch ( when used in sql:variable )

 

Background

Discovered a little problem this morning.

I tried searching on a GUID and was failing.

LAB

Use hardcoded GUID

Code


set nocount on
go

print replicate('*', 120)

print 'Hardcoded GUID - 8a99fb44-e3c0-42e6-a8d3-f7691c4f4fdf'
print ''

SELECT 
	  [sourced] = 'HardcodedGUID'
	, [GUID] = 	'8a99fb44-e3c0-42e6-a8d3-f7691c4f4fdf'
	, UserLog.details

from   [Admin].CourseManagerUserLog UserLog with (nolock)

WHERE  UserLog.[details].exist
       ('/in-car-lsn-status/@apptID[.="8a99fb44-e3c0-42e6-a8d3-f7691c4f4fdf"]') = 1

print replicate('*', 120)

go

Output

GUID-Hardcoded-Grid

Statistics I/O

GUID-Hardcoded-StatisticsIO

 

Use SQL Variable

Code


declare @appointmentID uniqueIdentifier
declare @appointmentIDAsString varchar(60)

set @appointmentID = '8a99fb44-e3c0-42e6-a8d3-f7691c4f4fdf'
set @appointmentIDAsString = @appointmentID

select 
		  [@appointmentID] = @appointmentID

print replicate('+', 120)

print 'sql:variable-@appointmentID'

SELECT 
			  [sourced] = 'sql:variable'
			, UserLog.*
from   Admin.CourseManagerUserLog UserLog with (nolock)
WHERE  UserLog.[details].exist ('/in-car-lsn-status[@apptID=sql:variable("@appointmentID")]') = 1

print replicate('+', 120)

print ''; print ''; print ''

print replicate('-', 120)

print 'sql:variable-@appointmentIDAsString'

SELECT 
			  [sourced] = 'sql:variable'
			, UserLog.*

from   Admin.CourseManagerUserLog UserLog with (nolock)

WHERE  UserLog.[details].exist ('/in-car-lsn-status[@apptID=sql:variable("@appointmentIDAsString")]') = 1

print replicate('-', 120)


 

Output:

plainGUID-GRID

 

Statistics I/O:

plainGUID-StatisticsIO

 

Use SQL Variable // Uppercase XML Attribute

Code



declare @appointmentID uniqueIdentifier
declare @appointmentIDAsString varchar(120)

set @appointmentID = '8a99fb44-e3c0-42e6-a8d3-f7691c4f4fdf'
set @appointmentIDAsString = cast(@appointmentID as varchar(120))

select 
		  [@appointmentID] = @appointmentID

print replicate('+', 120)

print 'sql:variable-@appointmentID//UpperCase[@apptID]'

SELECT 
			  [sourced] = 'sql:variable'
			, UserLog.details
from   Admin.CourseManagerUserLog UserLog with (nolock)
--WHERE  UserLog.[details].exist ('/in-car-lsn-status[@apptID=sql:variable("@appointmentID")]') = 1
WHERE  UserLog.[details].exist ('/in-car-lsn-status[upper-case(@apptID)=sql:variable("@appointmentID")]') = 1

print replicate('+', 120)


print replicate('=', 120)


SELECT 
			  [sourced] = 'sql:variable'
			, UserLog.details
from   Admin.CourseManagerUserLog UserLog with (nolock)
--WHERE  UserLog.[details].exist ('/in-car-lsn-status[@apptID=sql:variable("@appointmentID")]') = 1
WHERE  UserLog.[details].exist ('/in-car-lsn-status[upper-case(@apptID)=sql:variable("@appointmentIDAsString")]') = 1

print replicate('=', 120)


Output:

XMLValue-Uppercase-Grid

 

Query Plan:

 

XMLValue-Uppercase-QueryPlan

Statistics I/O:

XMLValue-Uppercase-StatisticsIO

 

Use SQL Variable // Lowercase SQL Variable

 

Code


declare @appointmentID uniqueIdentifier
declare @appointmentIDAsStringLowercase varchar(120)

set @appointmentID = '8a99fb44-e3c0-42e6-a8d3-f7691c4f4fdf'
set @appointmentIDAsStringLowercase = lower(cast(@appointmentID as varchar(120)))

select 
	[@appointmentIDAsStringLowercase] = @appointmentIDAsStringLowercase


print replicate('=', 120)


SELECT 
	  [sourced] = 'sql:variable:lowercase'
	, UserLog.details
from   Admin.CourseManagerUserLog UserLog with (nolock)
WHERE  UserLog.[details].exist
        ('/in-car-lsn-status[@apptID=sql:variable("@appointmentIDAsStringLowercase")]') = 1


print replicate('=', 120)

Output

XMLVariable-Lowercase-Grid

Query Plan

XMLVariable-Lowercase-QueryPlan

Statistics I/O

StatisticsIO

 

 

 

Compare XML.Value Versus Lowercase SQL Variable

Code

declare @appointmentID uniqueIdentifier
declare @appointmentIDAsStringLowercase varchar(120)

set @appointmentID = '8a99fb44-e3c0-42e6-a8d3-f7691c4f4fdf'
set @appointmentIDAsStringLowercase = lower(cast(@appointmentID as varchar(120)))

select 
		    [@appointmentID] = @appointmentID
		  , [@appointmentIDAsStringLowercase] = @appointmentIDAsStringLowercase


print replicate('+', 120)


SELECT 
			  [sourced] = 'XML-Attribute-Uppercase'
			, UserLog.details
from   [Admin].CourseManagerUserLog UserLog with (nolock)
WHERE  UserLog.[details].exist ('/in-car-lsn-status[upper-case(@apptID)=sql:variable("@appointmentID")]') = 1


print replicate('+', 120)
print replicate('=', 120)


SELECT 
			  [sourced] = 'sql:variable:lowercase'
			, UserLog.details
from   Admin.CourseManagerUserLog UserLog with (nolock)
WHERE  UserLog.[details].exist ('/in-car-lsn-status[@apptID=sql:variable("@appointmentIDAsStringLowercase")]') = 1


print replicate('=', 120)

 

Output:

GridOutput

Query Plan:

QueryPlan

Statistics I/O:

StatisticsIO

 

Statistics Time:

StatisticsTime

 

Tabulated

 

 

Item exist (‘/in-car-lsn-status[upper-case(@apptID)=sql:variable(“@appointmentID”)]’) exist (‘/in-car-lsn-status[@apptID=sql:variable(“@appointmentIDAsStringLowercase”)]’)
Query Plan
 Warning Type conversion in expression (CONVERT_IMPLICIT(nvarchar(64),[apptID:2].[value],0)) may affect “CardinalityEstimate” in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(nvarchar(max),[Expr1003],0)) may affect “CardinalityEstimate” in query plan choice
Statistics I/O
 xml_index_nodes_885578193_256000  Scan count 5080361, logical reads 20189184  Scan count 0, logical reads 3
 CourseManagerUserLog  Scan count 1, logical reads 98895  Scan count 2, logical reads 13
Statistics Time
CPU Time 21107 ms 0 ms
Elapsed Time 21470 ms 49 ms

 

 

Connect Items

  1. XQuery exist(‘… [@uniqueIdentifierColumn=sql:variable(“@uniqueIdentifierVariable”)) case sensitivity
    • ID :- 641455
    • Opened By : Softlion
    • Opened On: 2011.02.05
    • Status : Closed
    • Issue Explained:
      Note that the above query will canonicalize the uniqueidentifier to a string representation, that is upper-case. Since the untyped string value of the b element is compared as a string value, and string comparisons in XQuery are case-sensitive, you will only find one of the two b elements.
    • Workaround:
      This is the suggested workaround (working with SQL Server 2008 and later, where we added the upper-case function), assuming you cannot enforce upper-case value inside the XML — Michael
    • Version
      • Please keep in mind that work-around, upper-case,  is only applicable to SQL Server 2008 and later

 

Quick Summary

If we try to use GUID\Uniqueidentifiers in SQL:variable, we need to make sure that we make it lowercase and save it into another variable.

 

References

  1. uniqueidentifier in SQL xml type are upper case strings but C# Guid are lower case strings
    http://www.softlion.com/blogs/post/2008/11/20/test.aspx

 

XPATH

  1. XQuery Functions against the xml Data Type
    https://msdn.microsoft.com/en-us/library/ms189254.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s