Saturday, 6 May 2017

Uniqueidentifier in sql

[RowGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_DataType_RowGuid]  DEFAULT (newid()),
uniqueidentifier data type can be initialized to a value from newid() function or  xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example,  A4CEA197-BE32-4619-BDCF-219A05CA7237.
SELECT ROWGUIDCOL, [RowGuid] FROM [Application].[DataType]
RowGuid                           RowGuid
------------------------------------ ------------------------------------
1AB9A568-B7B5-4DB6-A623-FA05CEDBAD85 1AB9A568-B7B5-4DB6-A623-FA05CEDBAD85
SELECT ROWGUIDCOL from Marketo.CustomerLead -- having 1 uniqueidentifier, no error
SELECT ROWGUIDCOL from Marketing.Customer --  having 2 uniqueidentifier, Ambiguous column name 'rowguidcol'.
Know all tables having more than one uniqueidentifier column
select table_schema,table_name,data_type, count(*) from information_schema.columns

group by table_schema,table_name, data_type having data_type = 'uniqueidentifier' and count(*) > 1