April 19, 2005 at 6:55 am
Hey all,
I have a small problem. We have a row in our DB which is populated with a newID(). This is used to link (via code) to other tables which have the same ID.
Now we want to start replication and we need a GUID row. This row is ideal, but not set up as a GUID. But we cant set it.
If we created another DB - identical to the current but with the row set to GUID. Then copied the data over, will the GUID remain intact (i.e. if a certain row has a certain ID, will this ID remain?).
If it doesnt, then it wont link to the other table.
.... Or is there another way to create this GUID (i dont want another column, as this means code changes and i will probably get hit from above (the coder is my boss))
TIA
Dan
April 19, 2005 at 7:47 am
Am I understanding you right?
SET NOCOUNT ON
CREATE TABLE GUID_T
(
guid VARCHAR(50) DEFAULT NEWID() PRIMARY KEY
, col1 VARCHAR(30)
)
CREATE TABLE GUID_NEW
(
guid UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY
, col1 VARCHAR(30)
)
INSERT INTO GUID_T (col1) VALUES ('Frank')
INSERT INTO GUID_NEW SELECT * FROM GUID_T
INSERT INTO GUID_NEW (col1) VALUES ('Kalis')
SET NOCOUNT OFF
SELECT * FROM GUID_T
SELECT * FROM GUID_NEW
DROP TABLE GUID_T, GUID_NEW
guid col1
-------------------------------------------------- ------------------------------
DDCF5D85-DF50-4282-9220-D47FF03F18CF Frank
(1 row(s) affected)
guid col1
------------------------------------ ------------------------------
C7AFE883-23AA-4546-B726-00F432BAAED4 Kalis
DDCF5D85-DF50-4282-9220-D47FF03F18CF Frank
(2 row(s) affected)
Once the row has a certain uniqueidentifier, it won't change. This is no timestamp.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 19, 2005 at 7:52 am
Sorry i didnt explain myself well
Current DB below
Non guid col1
------------------------------------ ------------------------------
C7AFE883-23AA-4546-B726-00F432BAAED4 Kalis
DDCF5D85-DF50-4282-9220-D47FF03F18CF Frank
Now my question is after the moving of the data, if i specify that the GUID field must be the same as it was before the move, will it allow me to keep the above or will it issue a new number so i could end up with the below
guid col1
------------------------------------ ------------------------------
Df8DSDAS-44dd-9586-B726-00F432BAAED4 Kalis
SADSAD84-DS44-7745-9220-D47FF03F18CF Frank
TIA
Dan
April 19, 2005 at 7:57 am
Sorry, but I think this is exactly what I have posted, or? The table GUID_T has a VARCHAR(50) for the GUID. The table GUID_NEW has the UNiQUEIDENTIFIER data type. When you move the data from one to the other, it will stay the same. Even though the DEFAULT NEWID() is declared. As this will only get into action, when you don't explicitely specifa a value to be inserted. Am I missing something here?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 20, 2005 at 2:47 am
If you restore a backup of your existing database to the new database that you wish to replicate, you can add a row guid to you existing column (assuming that the datatype is uniquidentifier) using:
ALTER TABLE dbo.mytable
ALTER COLUMN myguid
ADD ROWGUIDCOL
This will preserve your primary and foreign key guid's (as would Frank's example above) but with less code.
Remember, guid's are not like identity fields, and require no special treatment. Think more along the lines of a datetime field with a default of GETDATE().
June 22, 2005 at 6:40 am
But when i create a GUID it defaults to 16 char. The GUID that was created (as yours is) is 36 chars. It wont fit?
D
June 22, 2005 at 6:50 am
A GUID is 16 bytes, which when displayed as a varchar is displayed as 36 characters of text. Using hexadecimal notation each byte is represented by 2 characters. This would take you to 32 characters, and SQL Server inserts 4 extra dashes, which takes you to 36.
The following is valid:
INSERT INTO dbo.mytable
(myguid)
VALUES ('B9F7ECBC-E9AA-4819-A64E-A365082CE422')
GO
SELECT * FROM dbo.mytable GO
Where myguid is of type uniqueidentifier.
June 22, 2005 at 7:50 am
So what would happen if i had a table with Varchars in which look like ID`s, and changed em to UnqiueID?
June 22, 2005 at 9:20 am
This should cause no problems, you can either get SQL to implicitly convert, e.g.
INSERT INTO #mytable VALUES (myguid) SELECT mytextguid FROM #mytexttable
or explicitly convert yourself, e.g.
INSERT INTO #mytable VALUES (myguid) SELECT CAST(mytextguid AS uniqueidentifier) FROM #mytexttable
You can even do this all in one table. Here is a full example script to run in Query Analyzer, which does much the same as Frank's example above...
-- Create the table CREATE TABLE #mytable ( mytextguid varchar(36) ) GO
-- Add the text format Guids
INSERT INTO #mytable (mytextguid)
VALUES ('C7AFE883-23AA-4546-B726-00F432BAAED4')
INSERT INTO #mytable (mytextguid)
VALUES ('DDCF5D85-DF50-4282-9220-D47FF03F18CF')
GO
-- Alter the table to add a guid column ALTER TABLE #mytable ADD myguid uniqueidentifier DEFAULT (newid()) GO
-- Transfer the text guid to the proper guid column UPDATE #mytable SET myguid = CAST(mytextguid AS uniqueidentifier) FROM #mytable GO
-- At this stage you can compare the new guid with the old text SELECT mytextguid, myguid, CASE WHEN CAST(myguid AS varchar(36)) = mytextguid THEN 'Match' ELSE 'Error' END AS ConvertionCheck FROM #mytable GO
-- Now make the guid column a ROWGIUD ALTER TABLE #mytable ALTER COLUMN myguid ADD ROWGUIDCOL GO
-- Drop the old text guid column ALTER TABLE #mytable DROP COLUMN mytextguid GO
-- Now show the final results SELECT * FROM #mytable GO
-- Drop the temp table DROP TABLE #mytable GO
Be aware however that not all programs represent guids in the same way, such as MS-Access. This shouldn't cause a problem if you avoid mixing and matching different convertions.
Ed
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply