How can you create a 32 character unique identifier in a SQL 2005 view (newid() is forcing 36 characters)
I am creating a view to join 3 primary tables (Contract, CI and ServiceCatalog), which have many to many relationships via a links table (FusionLink). Each record in the primary tables has a unique identifier (RecId) and each record in the links table has a unique identifier, however due to the joins, there is no column from any of the tables that I can use as a unique identifier in the resulting view.
I tried using the newid() function that SQL Server provides, but this creates a 36 character identifier - my receiving application can only use a max of 32 characters as its unique identifier and is therefore ignoring records in my view.
There are no combinations of columns in my primary table that will work as a unique identifier - is there another way to generate a unique identifier (or incrementing value of some sort) in a view? Or perhaps a way of reducing the newid() option to 32 characters?? My view is currently written as (note the newid() has been converted as my receiving application cannot handle datatype uniqueidentifier):
CREATE view [dbo].[vw_ContractToCIAndS
as
SELECT TOP (100) PERCENT CONVERT(char(36), NEWID()) AS Unique_id, dbo.Contract.RecId AS ContractRecId, dbo.Contract.ContractID, dbo.Contract.ContractBillT
dbo.Contract.EndDate, dbo.Contract.DaysCovered, dbo.Contract.HoursCovered,
dbo.ServiceCatalog.Service
FROM dbo.FusionLink AS FusionLink_1 INNER JOIN
dbo.Contract ON FusionLink_1.SourceID = dbo.Contract.RecId INNER JOIN
dbo.ServiceCatalog ON FusionLink_1.TargetID = dbo.ServiceCatalog.RecId LEFT OUTER JOIN dbo.CI INNER JOIN dbo.FusionLink ON dbo.CI.RecId = dbo.FusionLink.SourceID ON dbo.Contract.RecId = dbo.FusionLink.TargetID
I tried using the newid() function that SQL Server provides, but this creates a 36 character identifier - my receiving application can only use a max of 32 characters as its unique identifier and is therefore ignoring records in my view.
There are no combinations of columns in my primary table that will work as a unique identifier - is there another way to generate a unique identifier (or incrementing value of some sort) in a view? Or perhaps a way of reducing the newid() option to 32 characters?? My view is currently written as (note the newid() has been converted as my receiving application cannot handle datatype uniqueidentifier):
CREATE view [dbo].[vw_ContractToCIAndS
as
SELECT TOP (100) PERCENT CONVERT(char(36), NEWID()) AS Unique_id, dbo.Contract.RecId AS ContractRecId, dbo.Contract.ContractID, dbo.Contract.ContractBillT
dbo.Contract.EndDate, dbo.Contract.DaysCovered, dbo.Contract.HoursCovered,
dbo.ServiceCatalog.Service
FROM dbo.FusionLink AS FusionLink_1 INNER JOIN
dbo.Contract ON FusionLink_1.SourceID = dbo.Contract.RecId INNER JOIN
dbo.ServiceCatalog ON FusionLink_1.TargetID = dbo.ServiceCatalog.RecId LEFT OUTER JOIN dbo.CI INNER JOIN dbo.FusionLink ON dbo.CI.RecId = dbo.FusionLink.SourceID ON dbo.Contract.RecId = dbo.FusionLink.TargetID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Of course, the integer returned by the row_number() will be faster to search on and take up less real estate..but then again..I'm just giving mwvisa1 a hard time. ;)
LOL, I didn't see you were using row_number(). I do like that.
I just saw the need for 32 character newid() and posted my comment as I only saw the CONVERT(char(36), NEWID()) was still in your post. ;) That is what I get for not reading your whole post well.
I just saw the need for 32 character newid() and posted my comment as I only saw the CONVERT(char(36), NEWID()) was still in your post. ;) That is what I get for not reading your whole post well.
ASKER
I decided to go with chapmadew's solution because of the hint on faster searching (I need all the help I can get to speed this up, is going to be a few million rows!), although I did have to amend the suggested create statement to get it to work (removed the original CONVERT(char(36)... part). Also tested mwvisa1's solution out and was easy and accurate, as I say, jsut the hint on the searching tipped it for me :-)
ASKER
I used the row_number solution provided by chapmendew, but have found that each time I do a select, the rows in the view are given a different number - i.e. I can't use the same row number to find a particular row on an ongoing basis. Is this expected behaviour? Would mwvisa1's solution behave in the same way? I need a constant identifier for a particular row in the view, as it is going to be used in other relationships as primary/foreign key
ASKER
I've just tested the other option
select convert(varchar(32), replace(newid(),'-',''))
and it assigns a new id for a row each time - is there any way of configuring a fixed identifier for a row in the view?
select convert(varchar(32), replace(newid(),'-',''))
and it assigns a new id for a row each time - is there any way of configuring a fixed identifier for a row in the view?
For a fixed identifier, then you will have to construct the key using a formula that is based on something static on each record like the actual primary keys.
Alternatively, create a column on the actual table filling it with either of the methods and have it default to newid() or autoincrement; however, since you have multiple tables you will probably want to go with the former suggestion which could be a mathematical formula on the PK from each of the tables.
Alternatively, create a column on the actual table filling it with either of the methods and have it default to newid() or autoincrement; however, since you have multiple tables you will probably want to go with the former suggestion which could be a mathematical formula on the PK from each of the tables.
You might consider building a hash index via CHECKSUM. Check out BOL.
Jim