July 24, 2012 at 12:33 am
Hi all,
I have 3 tables that contains some texts.
CREATE TABLE Article
(
Id INT IDENTITY(1, 1) ,
article NVARCHAR(640) ,
UUID UNIQUEIDENTIFIER
)
go
ALTER TABLE dbo.Article ADD CONSTRAINT PK_Article PRIMARY KEY CLUSTERED( id)
GO
CREATE TABLE News
(
Id INT IDENTITY(1, 1) ,
news NVARCHAR(640) ,
UUID UNIQUEIDENTIFIER
)
go
ALTER TABLE dbo.News ADD CONSTRAINT PK_News PRIMARY KEY CLUSTERED( id)
GO
CREATE TABLE Email
(
Id INT IDENTITY(1, 1) ,
email NVARCHAR(640) ,
UUID UNIQUEIDENTIFIER
)
go
ALTER TABLE dbo.Email ADD CONSTRAINT PK_Email PRIMARY KEY CLUSTERED( id)
My application use these texts(News, Article and email) and send them to customers by SMS. 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 .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.
I find this but It doesnt work ! :
DECLARE @uu UNIQUEIDENTIFIER = 'EF2557C5-7ED1-4B2F-B0DD-693D30E47F26'
SELECT @uu
DECLARE @Id BIGINT = ( SELECT CONVERT(BIGINT, CONVERT (VARBINARY(16), @uu, 1))
)
SELECT @Id
SELECT CONVERT(UNIQUEIDENTIFIER, CONVERT(VARBINARY(16), @Id, 1))
July 24, 2012 at 1:08 am
A uniqueidentifier (GUID) contains text, so it's only logical that you cannot convert it directly to an INT.
What you can do is create a mapping table. Generate your GUID and insert it into a table containing an IDENTITY column. The GUID will be associated with an INT, which you can use in your SMS. When the INT comes back, you can do a simple lookup on the table to retrieve your GUID.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 5, 2012 at 7:13 pm
Actually a UNIQUEIDENTIFIER (AKA GUID) is a number. 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. If you could convert it to a number-looking thing you would end up with a number up to 39 digits I think. (Someone please check my match.)
So, converting from the UNIQUEIDENTIFIER to a number isn't going to help you a whole lot. Since you're posting in a SQL 2012 forum, you might want to consider using the new sequence feature. That will give you a pool of sequential numbers so you don't get the same one in any particular table. 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. 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.
There are probably some other ways to handle this but the UNIQUEIDENTIFIER is probably a bit on the overkill side.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
September 18, 2012 at 8:17 pm
You could also just use an IDENTITY column starting with 1 million to start with nice, simple 6 digit numbers.
--Jeff Moden
September 18, 2012 at 8:22 pm
Jeff Moden (9/18/2012)
You could also just use an IDENTITY column starting with 1 million to start with nice, simple 6 digit numbers.
If you start with a million it is a 7 digit number. Remind me not to let you count money for me. 😉
September 19, 2012 at 6:40 am
Lynn Pettis (9/18/2012)
Jeff Moden (9/18/2012)
You could also just use an IDENTITY column starting with 1 million to start with nice, simple 6 digit numbers.If you start with a million it is a 7 digit number. Remind me not to let you count money for me. 😉
Heh... correct. It was before coffee and I had "zero" thoughts. 😀
--Jeff Moden
September 25, 2012 at 4:05 am
Lynn Pettis (9/18/2012)
Jeff Moden (9/18/2012)
You could also just use an IDENTITY column starting with 1 million to start with nice, simple 6 digit numbers.If you start with a million it is a 7 digit number. Remind me not to let you count money for me. 😉
I'm happy for Jeff doing my mortgage interest or tax calculations 🙂
September 25, 2012 at 4:30 am
Actually, it is impossible to convert GUID into numeric in T-SQL.
The largest exact numeric data type in SQL Server has maximum precision of 38.
To convert GUID you need 39...
September 25, 2012 at 8:10 am
How does CHAR(16) suit you?
SELECT
[Start GUID] = d.aguid,
z.[To Character],
[back to GUID] =
CAST(
STUFF(STUFF(STUFF(STUFF(
STUFF(CONVERT(CHAR(34),CONVERT(BINARY(16),z.[To Character],0),1),1,2,'')
,9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')
AS uniqueidentifier)
FROM (SELECT aguid = newid()) d
CROSS APPLY (SELECT vID = CAST(REPLACE(aguid,'-','') AS CHAR(32))) x
CROSS APPLY (
SELECT [To Character] = CAST(
CAST(master.dbo.fn_cdc_hexstrtobin(left(x.vID,16)) AS CHAR(8)) +
CAST(master.dbo.fn_cdc_hexstrtobin(right(x.vID,16)) AS CHAR(8))
AS CHAR(16))
) z
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 25, 2012 at 8:58 am
farax_x (7/24/2012)
...On the other hand sending UNIQUEIDENTIFIER to customer is not acceptable so I need to convert it to int ...
ChrisM@Work (9/25/2012)
How does CHAR(16) suit you?...
I don't think that CHAR(16) looking like "±WëÑA˜ŸŸý[‚B" or ":œÜÀ)ëM°úã™(i½" will be more appropriate for sending to customers...
:w00t:
September 25, 2012 at 9:01 am
Eugene Elutin (9/25/2012)
farax_x (7/24/2012)
...On the other hand sending UNIQUEIDENTIFIER to customer is not acceptable so I need to convert it to int ...
ChrisM@Work (9/25/2012)
How does CHAR(16) suit you?...
I don't think that CHAR(16) looking like "±WëÑA˜ŸŸý[‚B" or ":œÜÀ)ëM°úã™(i½" will be more appropriate for sending to customers...
:w00t:
Heh I disagree - it adds a mysterious tone of it's own 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 27, 2012 at 7:38 am
This is not a problem unique to SQL 2012 but is more generic design. Even so ...
The "mapping" table is a great approach. You would have identities from all three tables plus a date/time column (UTC of course) that says when the message went out along with the itdentifier for this row. The GIUD on the mapping table is what you send out. This one GUID finds the intersection (node) that relates back to the other three. Am I right Joe?
Then we might want to talk about tables that have an IDENTITY column and a GUID. A GUID is a perfectly good row Id so long as you don't use it as your clustered index. (read elswhere for why that is). Hey SSMS is a wonderful tool but don't let its "traditions" get in the way of good design.
ATBCharles Kincaid
February 23, 2016 at 11:17 am
Apologies for responding to an old thread, but I had a similar problem; though not exact. I needed to store a GUID as an int, but a GUID is 16 bytes while an int is 4. Specifically with agent jobs on a server (which use a GUID as an ID). Here's my solution, which hashes the guid then puts it into a space that is the same size as int.
SELECT
HASHBYTES('SHA1', cast(SJ.job_id as nvarchar(36))) % (power(cast(2 as bigint), cast(31 as bigint))-1) as inthash
,SJ.job_id
FROM
msdb.dbo.SYSJOBS SJ
GROUP BY
HASHBYTES('SHA1', cast(SJ.job_id as nvarchar(36))) % (power(cast(2 as bigint), cast(31 as bigint))-1)
,SJ.job_id
order by
HASHBYTES('SHA1', cast(SJ.job_id as nvarchar(36))) % (power(cast(2 as bigint), cast(31 as bigint))-1)
For casting back and forth, you really can't since SHA1 is a 1 way hash. However, you don't actually need to cast it back according to the original question in this thread. You need a quick way to find the specific row. That can be done by either querying for the output like:
SELECT
*
FROM
msdb.dbo.SYSJOBS SJ
WHERE
HASHBYTES('SHA1', cast(SJ.job_id as nvarchar(36))) % (power(cast(2 as bigint), cast(31 as bigint))-1) = -154843 /* or whatever value */
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. If you need really fast, you can also persist the computed value and add an index. Lots of other options too like using a view or what-have-you.
Note that because we are turning a 16 byte value into one that is only 4 bytes, the value space is shrinking by 75%. That means the possibility for collisions exist, and will manifest with a pretty good probability at around 100k values. For the specific question being asked, you can also use other pieces of information to reduce the possibility of collisions, such as the phone number the SMS was sent to and received from. Date (or date range) is a good posibility too since it is unlikely people will ask for an article that is > X days old.
No matter what though, there's still a possibility that a collision will happen, so make sure the code handles it. Such as checking if >1 row matched, and erroring gracefully.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply