Using Uniqueidentifiers in NOT IN Clause

Question

Post reply

Using Uniqueidentifiers in NOT IN Clause

Rajesh-151362

SSC Veteran

Points: 235
More actions
June 16, 2004 at 3:11 am

#161063

Hi
I have records in a table (employeesTable) which has column (employeeID) with UniqueIdentifier datatype. I want to build a query something like:
SELECT * FROM employeesTable
          WHERE employeeID
          NOT IN ('40EA29E3-1F57-4727-B7ED-1BC5915620C8',
                      '1152ce71-4783-4257-8145-8a33487df146',
                      '68774CEF-3367-46B2-9DAC-13BD33F79D72',
                      'B0D64947-BE80-4491-B2A9-7E0E58E03D09');

Regards

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 1

Rajesh

What problem are you having?

I would say that if there's a lot of data, using a character id field isn't generally recommended since it will be a fair bit slower than an integer field - and a guid in particular seems like a bit of overkill.

Rajesh-151362 SSC Veteran Points: 235 More actions · Answer 2

Hi

I didnt have much choice as the data was designed very long back. Its not possible to change it now as it will affect many other systems as well.

I sorted it out finally! While playing around with cast function, I did something like this.

SELECT * FROM employeesTable

WHERE employeeID

NOT IN (CAST('40EA29E3-1F57-4727-B7ED-1BC5915620C8' as uniqueidentifier),

CAST('1152ce71-4783-4257-8145-8a33487df146' as uniqueidentifier),

CAST('68774CEF-3367-46B2-9DAC-13BD33F79D72' as uniqueidentifier),

CAST('B0D64947-BE80-4491-B2A9-7E0E58E03D09' as uniqueidentifier)

Thanks for the suggestion. I will remember it while designing a better system.

Regards