none
computed_column_expression with uniqueidentifier

    Question

  • I want to use computed_column_expression to replace UNIQUE NONCLUSTERED CONSTRAINT (i recieve Error after more than 16 rows) with uniqueidentifier

    can someone give me aexample how to alter my table?

    ALTER TABLE [SourceReferences] ADD  CONSTRAINT [IX_SourceReferences_Fields_Unique] UNIQUE NONCLUSTERED 
    (
    	[ApID] ASC,
    	[BID] ASC,
    	[ID] ASC,
    	[seID] ASC,
    	[pID] ASC,
    	[ndID] ASC,
    	[Subject] ASC,
    	[tID] ASC,
    	[lID] ASC,
    	[mID] ASC,
    	[lID] ASC,
    	[onID] ASC,
    	[ntID] ASC,
    	[TrialID] ASC,
    	[ModelID] ASC,
    	[DiseaseID] ASC	
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO
    


    Tuesday, October 29, 2013 3:09 AM

Answers

  • An index can contain a max of 16 columns.  From the title of you post, it looks like you are asking for an example of a computed column on uniqueidentifier columns as a workaround.  Below is an example casting each to varbinary(16) (using column name xID for the duplicated lID column).

    ALTER TABLE [SourceReferences]
    ADD ComputedColumn AS (
    	CAST([ApID] AS varbinary(16))
    	+ CAST([BID]  AS varbinary(16))
    	+ CAST([ID]  AS varbinary(16))
    	+ CAST([seID]  AS varbinary(16))
    	+ CAST([pID]  AS varbinary(16))
    	+ CAST([ndID]  AS varbinary(16))
    	+ CAST([Subject]  AS varbinary(16))
    	+ CAST([tID]  AS varbinary(16))
    	+ CAST([lID]  AS varbinary(16))
    	+ CAST([mID]  AS varbinary(16))
    	+ CAST([xID]  AS varbinary(16))
    	+ CAST([onID]  AS varbinary(16))
    	+ CAST([ntID]  AS varbinary(16))
    	+ CAST([TrialID]  AS varbinary(16))
    	+ CAST([ModelID]  AS varbinary(16))
    	+ CAST([DiseaseID]  AS varbinary(16))
    	);
    
    ALTER TABLE [SourceReferences] 
    	ADD  CONSTRAINT [IX_SourceReferences_Fields_Unique] UNIQUE NONCLUSTERED 
    	(ComputedColumn);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, October 29, 2013 3:28 AM
  • The duplicate key value is (<NULL>).

     

    I assumed the columns were NOT NULL.  Since you have NULL values, you can specify an alternate value for NULL:

     

    ALTER TABLE [SourceReferences]
    ADD ComputedColumn AS (
    	  COALESCE(CAST([ApID] AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([BID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([ID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([seID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([pID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([ndID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([Subject]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([tID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([lID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([mID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([xID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([onID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([ntID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([TrialID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([ModelID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([DiseaseID]  AS varbinary(16)), 0x00)
    	);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, October 29, 2013 12:23 PM

All replies

  • An index can contain a max of 16 columns.  From the title of you post, it looks like you are asking for an example of a computed column on uniqueidentifier columns as a workaround.  Below is an example casting each to varbinary(16) (using column name xID for the duplicated lID column).

    ALTER TABLE [SourceReferences]
    ADD ComputedColumn AS (
    	CAST([ApID] AS varbinary(16))
    	+ CAST([BID]  AS varbinary(16))
    	+ CAST([ID]  AS varbinary(16))
    	+ CAST([seID]  AS varbinary(16))
    	+ CAST([pID]  AS varbinary(16))
    	+ CAST([ndID]  AS varbinary(16))
    	+ CAST([Subject]  AS varbinary(16))
    	+ CAST([tID]  AS varbinary(16))
    	+ CAST([lID]  AS varbinary(16))
    	+ CAST([mID]  AS varbinary(16))
    	+ CAST([xID]  AS varbinary(16))
    	+ CAST([onID]  AS varbinary(16))
    	+ CAST([ntID]  AS varbinary(16))
    	+ CAST([TrialID]  AS varbinary(16))
    	+ CAST([ModelID]  AS varbinary(16))
    	+ CAST([DiseaseID]  AS varbinary(16))
    	);
    
    ALTER TABLE [SourceReferences] 
    	ADD  CONSTRAINT [IX_SourceReferences_Fields_Unique] UNIQUE NONCLUSTERED 
    	(ComputedColumn);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, October 29, 2013 3:28 AM
  • thanks 

    i have this error

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.SourceReferences' and the index name 'IX_SourceReferences_Field_Unique'. The duplicate key value is (<NULL>).

    Tuesday, October 29, 2013 4:38 AM
  • Hi.

    One or more columns have NULL on it.

    you have to use the isnull function when you are creating your computedcolumn

    Exemple:

    ComputedColumn  as (ISNULL(CONVERT(VARCHAR(20),ID01),'')
    				   +ISNULL(CONVERT(VARCHAR(20),ID02),'')	
    				   +ISNULL(CONVERT(VARCHAR(20),ID03),'')
    				   +ISNULL(CONVERT(VARCHAR(20),ID04),'')
    				   ...
    				   +ISNULL(CONVERT(VARCHAR(20),ID16),''))
    
    


                                          



    karepa

    Tuesday, October 29, 2013 5:15 AM
  • The duplicate key value is (<NULL>).

     

    I assumed the columns were NOT NULL.  Since you have NULL values, you can specify an alternate value for NULL:

     

    ALTER TABLE [SourceReferences]
    ADD ComputedColumn AS (
    	  COALESCE(CAST([ApID] AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([BID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([ID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([seID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([pID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([ndID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([Subject]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([tID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([lID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([mID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([xID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([onID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([ntID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([TrialID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([ModelID]  AS varbinary(16)), 0x00)
    	+ COALESCE(CAST([DiseaseID]  AS varbinary(16)), 0x00)
    	);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, October 29, 2013 12:23 PM