[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How can you create a 32 character unique identifier in a SQL 2005 view (newid() is forcing 36 characters)

Posted on 2008-10-06
9
Medium Priority
?
1,033 Views
Last Modified: 2012-06-21
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_ContractToCIAndService]
as
SELECT     TOP (100) PERCENT CONVERT(char(36), NEWID()) AS Unique_id, dbo.Contract.RecId AS ContractRecId, dbo.Contract.ContractID, dbo.Contract.ContractBillType, dbo.Contract.CompanyID, dbo.Contract.AddressID, dbo.Contract.Department, dbo.Contract.ContractType, dbo.Contract.SalesOrderNo,  dbo.Contract.PurchaseOrderNo, dbo.Contract.StartDate,
dbo.Contract.EndDate, dbo.Contract.DaysCovered, dbo.Contract.HoursCovered, dbo.Contract.PublicHolsCovered, dbo.Contract.BDMID, dbo.Contract.BillDay, dbo.Contract.Billed, dbo.Contract.BillFrequency, dbo.Contract.BillToAddressID, dbo.Contract.BillToCompanyID, dbo.Contract.SalesDocRef, FusionLink_1.TargetID AS ServiceRecId, dbo.ServiceCatalog.ServiceGroup,
dbo.ServiceCatalog.ServiceName, dbo.CI.RecId AS CIRecid, dbo.CI.CI_ID                      
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
 

0
Comment
Question by:aaromba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22656262
Hi aaromba,

You might consider building a hash index via CHECKSUM. Check out BOL.

Jim
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22656319
checksum is not necessary, and could potentially create dupes depending on how you use it.  If you're on 2005 (as you indicate), you can do this:

CREATE view [dbo].[vw_ContractToCIAndService]
as
SELECT     TOP (100) PERCENT CONVERT(char(36), NEWID()) AS Unique_id, dbo.Contract.RecId AS ContractRecId, dbo.Contract.ContractID, dbo.Contract.ContractBillType, dbo.Contract.CompanyID, dbo.Contract.AddressID, dbo.Contract.Department, dbo.Contract.ContractType, dbo.Contract.SalesOrderNo,  dbo.Contract.PurchaseOrderNo, dbo.Contract.StartDate,
dbo.Contract.EndDate, dbo.Contract.DaysCovered, dbo.Contract.HoursCovered, dbo.Contract.PublicHolsCovered, dbo.Contract.BDMID, dbo.Contract.BillDay, dbo.Contract.Billed, dbo.Contract.BillFrequency, dbo.Contract.BillToAddressID, dbo.Contract.BillToCompanyID, dbo.Contract.SalesDocRef, FusionLink_1.TargetID AS ServiceRecId, dbo.ServiceCatalog.ServiceGroup,
dbo.ServiceCatalog.ServiceName, dbo.CI.RecId AS CIRecid, dbo.CI.CI_ID     ,
UniqueKey = row_number() over(order by newid() asc)
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
 
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 22656374
You can do this -- change newid() to:

select replace(newid(),'-','')
-- it is my understanding this will already be what you need, but can explicitly case to varchar/char(32)

OR

select convert(varchar(32), replace(newid(),'-',''))
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22656383
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.  ;)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22656404
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.
0
 

Author Closing Comment

by:aaromba
ID: 31503699
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 :-)
0
 

Author Comment

by:aaromba
ID: 22656753
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
0
 

Author Comment

by:aaromba
ID: 22656767
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?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22657831
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question