October 11, 2010 at 1:52 pm
Hi,
I was actually confused on the behaviour of UID's during comparison using '=' operator, wherein it tends to compare only the 1st UID, in a particular string.
Ideally it should not return the result set as done by the other data type as depicted in the code.
Is something a miss? Or is it something typical with UID's.
CREATE TABLE #UDI ( ID UNIQUEIDENTIFIER , MISC NVARCHAR(50) )
INSERT INTO #UDI VALUES ( NEWID(), 'ABC' )
INSERT INTO #UDI VALUES ( NEWID(), 'CDE' )
INSERT INTO #UDI VALUES ( NEWID(), 'DFR' )
INSERT INTO #UDI VALUES ( NEWID(), 'BLA' )
INSERT INTO #UDI VALUES ( NEWID(), 'SKE' )
INSERT INTO #UDI VALUES ( NEWID(), 'TERL' )
INSERT INTO #UDI VALUES ( NEWID(), 'VLE' )
SELECT * FROM #UDI
--DROP TABLE #UDI
/*Please replace those UID Values; because the NEWID() would generate diff values for diff machines*/
DECLARE @ID NVARCHAR(MAX) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0,D4872E8E-8AE8-4A2E-A3A4-564CFDEC953E'
DECLARE @MISC NVARCHAR(MAX) = N'ABC,CDE,DFR,BLA'
SELECT * FROM #UDI
WHERE ID = @ID
SELECT * FROM #UDI
WHERE MISC = @ID
October 11, 2010 at 2:23 pm
I'm a bit confused by your sample TSQL code.
The local variable @ID that you are initializing with what appears to be a comma separated string of UIDs would never be equal to a specific UID in your table.
If you wanted to find a number of rows matching a list of UIDs you could use this sort of scenario:
select *
from UID
where ID in ('0E984725-C51C-4BF4-9960-E1C80E27ABA0','D4872E8E-8AE8-4A2E-A3A4-564CFDEC953E')
but as you can see that is not the same as:
where ID = '0E984725-C51C-4BF4-9960-E1C80E27ABA0,D4872E8E-8AE8-4A2E-A3A4-564CFDEC953E'
The probability of survival is inversely proportional to the angle of arrival.
October 12, 2010 at 7:07 am
@sturner :
I totally understand that the query is not going to yield me proper results since it is a string of UID's and IN is the way to go, what I am not understanding is why is @ID comparing to the first GUID only ?
Ideally it should not return any result set for the UID comparison.
Is this something peculiar to UID? or related to Implicit/Explicit conversion which I may not be aware of ?
October 12, 2010 at 7:22 am
Okay, I understand your question now. It appears that the implicit conversion from (n)varchar() to uniqueidentifier involves only the first 32 valid hexadecimal characters and SQL server ignores the rest.
That is something I wasn't aware of until I tried it just now.
The probability of survival is inversely proportional to the angle of arrival.
October 12, 2010 at 11:02 am
This is an American joke I just had to add here. You both deserve time off for GUID behavior.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 12, 2010 at 11:11 am
The Dixie Flatline (10/12/2010)
This is an American joke I just had to add here. You both deserve time off for GUID behavior.
GROAN.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 11:16 am
sturner (10/12/2010)
Okay, I understand your question now. It appears that the implicit conversion from (n)varchar() to uniqueidentifier involves only the first 32 valid hexadecimal characters and SQL server ignores the rest.That is something I wasn't aware of until I tried it just now.
I wasn't aware of it either, but this shows it:
DECLARE @ID NVARCHAR(MAX) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0,D4872E8E-8AE8-4A2E-A3A4-564CFDEC953E'
select @ID, CONVERT(uniqueidentifier, @ID)
I wonder if this should be construed as a bug? Extra stuff in a conversion to datetime throws an error. Seems it should be consistent.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 11:34 am
WayneS (10/12/2010)
I wonder if this should be construed as a bug? Extra stuff in a conversion to datetime throws an error. Seems it should be consistent.
I've created a Connect item for this. Please go visit and cast your vote.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 1:27 pm
Not after you groaned at my joke.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 12, 2010 at 1:39 pm
I was pretty much clear to what i was trying to point at.! This was something which I observed in one of our older procs while retuning them and was trying to understand what exactly was happening.
October 12, 2010 at 1:43 pm
Respectfully, it wasn't clear. I read it the same way sturner did at first.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2010 at 4:10 am
WayneS (10/12/2010)
I wonder if this should be construed as a bug? Extra stuff in a conversion to datetime throws an error. Seems it should be consistent.
The documentation could be better, but the behaviour is consistent.
Books Online: Data Type Conversion (Database Engine):
"When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated."
That explains why only the first 36 characters of the string are used in the conversion to uniqueidentifer.
The example in that section includes a conversion from uniqueidentifier, but it could be made clearer that uniqueidentifier is considered a character data type for the purposes of type conversion. Perhaps that information exists somewhere else in BOL - I don't recall having seen it though.
Paul
October 13, 2010 at 4:38 am
ulteriorm (10/12/2010)
I totally understand that the query is not going to yield me proper results since it is a string of UID's and IN is the way to go...
You can't use IN directly with a variable. It is usually better to split your comma-separated list into a table first and then perform a join. The alternatives tend to be error-prone and slow - see the example below.
IF OBJECT_ID(N'tempdb..#UDI', N'U')
IS NOT NULL
DROP TABLE #UDI;
GO
CREATE TABLE #UDI
(
id UNIQUEIDENTIFIER
UNIQUE NOT NULL
ROWGUIDCOL,
misc VARCHAR(10) NOT NULL
);
INSERT #UDI
(id, misc)
VALUES ( {guid '2B7E3C00-8204-4DDE-B995-B2869732C64B'}, 'ABC' ),
( {guid 'E46B8313-F106-4F44-B8AC-1A8508E748D6'}, 'CDE' ),
( {guid 'FFA41F61-DA31-4478-9F07-D3105196FAAE'}, 'DFR' ),
( {guid '99956EED-629B-4BF7-97F1-EDB8A7E5B3F0'}, 'BLA' ),
( {guid 'D8923662-0AD5-4989-8E74-A48DB624F3BE'}, 'SKE' ),
( {guid 'A5C421C9-D15D-4FDB-938F-CEBEF87E7CD6'}, 'TERL' ),
( {guid 'DEC4272E-7240-4014-B93F-A7BDC51137ED'}, 'VLE' );
GO
SELECT U.id,
U.misc
FROM #UDI U;
GO
DECLARE @ID NVARCHAR(MAX) = N'FFA41F61-DA31-4478-9F07-D3105196FAAE,A5C421C9-D15D-4FDB-938F-CEBEF87E7CD6';
DECLARE @MISC NVARCHAR(MAX) = N'ABC,CDE,DFR,BLA';
SELECT U.id,
U.misc
FROM #UDI U
WHERE CHARINDEX(N',' + CONVERT(NCHAR(36), $ROWGUID) + N',', N',' + @ID + N',') > 0;
SELECT *
FROM #UDI U
WHERE CHARINDEX(N',' + U.misc + N',', N',' + @MISC + N',') > 0;
GO
DROP TABLE #UDI;
October 13, 2010 at 7:29 am
Paul, I think you missed something in the midgame posts. His question is really why it didn't throw a syntax error when he used = to compare a UNIQUEIDENTIFIER to a string that was too long and had commas in it. An implicit conversion takes place.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2010 at 7:47 am
The Dixie Flatline (10/13/2010)
Paul, I think you missed something in the midgame posts. His question is really why it didn't throw a syntax error when he used = to compare a UNIQUEIDENTIFIER to a string that was too long and had commas in it. An implicit conversion takes place.
Yes. I was reading between the lines for a better question to answer.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply