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]
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