Note that because we are turning a 16 byte value into one that is only 4 bytes, the value space is shrinking by 75%. Lots of other options too like using a view or what-have-you. If you need really fast, you can also persist the computed value and add an index. Since I am dealing with system tables, I am not going to modify them, but for user tables, you can add a computed column with the hash definition for an easy join/clause. HASHBYTES('SHA1', cast(SJ.job_id as nvarchar(36))) % (power(cast(2 as bigint), cast(31 as bigint))-1) = -154843 /* or whatever value */ That can be done by either querying for the output like: You need a quick way to find the specific row. However, you don't actually need to cast it back according to the original question in this thread. HASHBYTES('SHA1', cast(SJ.job_id as nvarchar(36))) % (power(cast(2 as bigint), cast(31 as bigint))-1)įor casting back and forth, you really can't since SHA1 is a 1 way hash. HASHBYTES('SHA1', cast(SJ.job_id as nvarchar(36))) % (power(cast(2 as bigint), cast(31 as bigint))-1) as inthash Here's my solution, which hashes the guid then puts it into a space that is the same size as int. Specifically with agent jobs on a server (which use a GUID as an ID). I needed to store a GUID as an int, but a GUID is 16 bytes while an int is 4. There are probably some other ways to handle this but the UNIQUEIDENTIFIER is probably a bit on the overkill side.Īpologies for responding to an old thread, but I had a similar problem though not exact. If you can figure out which table to query before you hit the database it might save your SQL Server a little bit of work. If you posted in the 2012 forum by mistake you might want to consider using the IDENTITY value with a prefix letter depending on which table it's coming from like A123 for #123 from the Article table. That will give you a pool of sequential numbers so you don't get the same one in any particular table. Since you're posting in a SQL 2012 forum, you might want to consider using the new sequence feature. So, converting from the UNIQUEIDENTIFIER to a number isn't going to help you a whole lot. If you could convert it to a number-looking thing you would end up with a number up to 39 digits I think. The problem farax_x is running into it that a BIGINT is a 64 integer while the UNIQUEIDENTIFIER is 128 bits so trying to convert the UNIQUEIDENTIFIER to a BIGINT results in an overflow error. SELECT CONVERT(UNIQUEIDENTIFIER, CONVERT(VARBINARY(16), 1))Īctually a UNIQUEIDENTIFIER (AKA GUID) is a number. SELECT BIGINT = ( SELECT CONVERT(BIGINT, CONVERT (VARBINARY(16), 1)) On the other hand sending UNIQUEIDENTIFIER to customer is not acceptable so I need to convert it to int and when It comes back convert the number to UNIQUEIDENTIFIER again.ĭECLARE UNIQUEIDENTIFIER = 'EF2557C5-7ED1-4B2F-B0DD-693D30E47F26' I want to send a code(number) at the end of the text that customer can send it back for some transactions so I need unique Id in all tables (UUID) that help me to find the exact text. My application use these texts(News, Article and email) and send them to customers by SMS. I have 3 tables that contains some texts.ĪLTER TABLE dbo.Article ADD CONSTRAINT PK_Article PRIMARY KEY CLUSTERED(id)ĪLTER TABLE dbo.News ADD CONSTRAINT PK_News PRIMARY KEY CLUSTERED(id)ĪLTER TABLE dbo.Email ADD CONSTRAINT PK_Email PRIMARY KEY CLUSTERED(id)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |