uniqueidentifier VS varchar(50)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

uniqueidentifier VS varchar(50)

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-26 : 16:46:38

i have a table with 200million rows.
for some reason, one of the columns was built as varchar(50)
and contains values of type NEWID().
now i want to fixex this column type to
varchar(36) or to uniqueidentifier.
will this save me space on HD?
1 more importent thing : i have an index on this column.

Thanks
Peleg

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-05-26 : 16:50:29

Uniqueidentifier is stored as 16 bytes. The varchar/string representation is 36 bytes. If it's supposed to store NEWID()'s only then the best thing to do is alter the column as uniqueidentifier.

You'll have to drop any indexes or constraints on that column and recreate them after you change the data type.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-26 : 17:33:06

first thanks
second : how come that a NEWID()'s which is 36 bytes long, is stored on 16 bytes only?

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 17:39:26

Does that column JOIN to anything?

You could be having fairly serious performance problems with the implicit cast if that is the case (although I'm not sure there is an IMplicit cast from Varchar to GUID, is there?)

Changing type is going to require creating temporary table, copying data over, dropping old table, and rename. on 200M rows that will be a BIG job!

Perhaps alternative would be to:

Append a new GUID column called NewColumn
Update that column with GUID equivalent of OldColumn
Drop index on OldColumn
Drop OldColumn
Rename NewColumn to OldColumn
Re-create the index

I *think* that doesn't require any complete table re-create.

Space saving will be difference between 16-byte GUID and 36-String TIMES TWO (because you have index on the column), and the new index will get more entries per page, and thus use feer pages, so save a bit more there too

200M Rows * (36-16) * 2 = 8GB saving. Probably a bit more than that by the time some extra housekeeping has been factored in - anyways, between about 8GB and 10GB - assuming the GUID is on every row and it isn't using a Filtered Index

If the GUID values are NOT on every row (i.e. some rows are NULL) then re-creating the index to be a filtered index (WHERE MyColumn IS NOT NULL) would save some bytes for sure.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-05-27 : 03:24:23

Kristen, thnaks for the info.
can you just explain how 36 chars of NEWID() are hold as 16 bytes?

Kristen
Test

22859 Posts

Posted - 2010-05-27 : 04:46:12

Binary

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources