none
Use Computed Column as Primary Key

    Question

  • I am trying to make my computed column (QuoteID) the primary key.  QuoteID is a concatenation of two fields as you can see below.  Below is how SQL Server 2008 scripts the CREATE TABLE without QuoteID being the primary key.  For some reason, I'm able to set QuoteID as the primary key in the designer but when I save it I get the message below, why?  I know you can have computed columns as a primary key according to MSDN here.  How do I get this to save?

    QuoteNumber is not nullable and is an identity field so it will never be null.

    RevisionNumber has a default value of 1 so it will never be null.

    How in the world will QuoteID ever be null?

    USE [QuoteGenerator]
    GO
    
    /****** Object:  Table [dbo].[Quotes]    Script Date: 07/13/2013 19:25:05 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Quotes](
    	[QuoteID]  AS (([QuoteNumber]+'-')+[RevisionNumber]) PERSISTED,
    	[QuoteNumber] [int] IDENTITY(1,1) NOT NULL,
    	[RevisionNumber] [tinyint] NOT NULL
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[Quotes] ADD  CONSTRAINT [DF_Quotes_RevisionNumber]  DEFAULT ((1)) FOR [RevisionNumber]
    GO

    Thanks in advance


    Ryan


    • Edited by Ryan0827 Saturday, July 13, 2013 11:35 PM typo and forgot hyperlink
    Saturday, July 13, 2013 11:33 PM

All replies

  • Try this create instead - you are not declaring your computed column as not null

    CREATE TABLE [dbo].[Quotes](
    	[QuoteID]  AS (([QuoteNumber]+'-')+[RevisionNumber]) PERSISTED NOT NULL,
    	[QuoteNumber] [int] IDENTITY(1,1) NOT NULL,
    	[RevisionNumber] [tinyint] NOT NULL
    ) ON [PRIMARY]
    


    Chuck Pedretti | Magenic – North Region | magenic.com

    Sunday, July 14, 2013 1:27 AM
  • And if you mean you want QuoteID to be a string with the value of QuoteNumber, followed by a '-', followed by the value of RevisionNumber, you must cast/convert those int's to a character type, for example

    [QuoteID]  AS ((CAST([QuoteNumber] As varchar(12))+'-')+CAST([RevisionNumber] AS varchar(12))) PERSISTED NOT NULL,

    If you don't do the cast, then you have an expression with two integers and a string.  So SQL converts the string to an integer.  '-' can legally be converted to an int (it gets the value 0).  So QuoteID becomes an integer equal to QuoteNumber + 0 + RevisionNumber or just QuoteNumber + RevisionNumber.

    So QuoteNumber = 7, RevisionNumber = 2 will give you a QuoteNumber = 9.

    Tom 

    Sunday, July 14, 2013 4:02 AM
  • Maybe this is a duplicate thread with this one:

    Computed Colum possible to be Primary Key Column? 

    Please search forum before making a new thread. According that thread refer to this link:

    Creating UNIQUE and PRIMARY KEY Constraints on Computed Columns

     


    http://sqldevelop.wordpress.com/


    • Edited by Saeid Hasani Friday, September 06, 2013 3:04 PM
    • Proposed as answer by Saeid Hasani Friday, September 06, 2013 3:04 PM
    Sunday, July 14, 2013 4:43 AM
  • Why would you want to make a computed column PRIMARY KEY?  It is a bad idea.

    You can use INT IDENTITY(1,1) SURROGATE PRIMARY KEY if there is no better candidate.  SEQUENCE object is available in SQL Server 2012 which can be used to populate a PRIMARY KEY column.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration




    Sunday, July 14, 2013 7:08 AM
  • Why would you want to make a computed column PRIMARY KEY?  It is a bad idea.

    You can use INT IDENTITY(1,1) SURROGATE PRIMARY KEY if there is no better candidate.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration



    A better solution given his table structure would be to just make a 2 part primary key on QuoteNumber,RevisionNumber. I'm not really sure what purpose the calculated column serves since it just concats 2 fields into a string.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Sunday, July 14, 2013 1:19 PM
  • There is nothing wrong with a two-column key! But using IDENTITY is completely wrong. Why do you think that the count of physical insertion attempt to one disk is part of a valid logical model?? 

    CREATE TABLE Quotes
    (quote_nbr INTEGER NOT NULL,
     quote_revision SMALLINT NOT NULL,
     PRIMARY KEY (quote_nbr, quote_revision),
     ..);

    You will probably need to lookup this feature. It is ANSI/ISO Standard SQL. 

    CREATE SEQUENCE quote_seq
    AS SMALLINT  
       START WITH 000 
       INCREMENT BY 1 
       NO CYCLE; 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, July 14, 2013 2:07 PM
  • Hi Joe,

    It takes time.  SEQUENCE object was introduced with SQL Server 2012.  Our mindset is not there yet!  We have 2 decades of INT IDENTITY SURROGATE PRIMARY KEY applications in our brains!

    Thanks.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Sunday, July 14, 2013 3:05 PM
  • Thanks to all for the replies.  I do have a few updates and follow up questions.

    1.)  Convenience is the only reason I am considering a computed column (QuoteID) as my primary key as opposed to a composite key of QuoteNumber & RevisionNumber.  This key will be spread across multiple tables and I figure writing the SQL would be a little cleaner.  Plus, in my application I can bind the QuoteID to control versus having to concatenate QuoteNumber and RevisionNumber all the time.

    2.)  I'm open minded about keeping the composite key and ditching the idea of using a computed column, but before I do, why is a computed column a bad idea if it's persisted and can be indexed?  Microsoft doesn't seem to have a problem with it.

    3.)  I need the identity column (QuoteNumber) as a counter.  As users add quotes to the database I have to keep the new, old, and deleted quote numbers unique because customers will reference this quote number.  I don't believe I can use the SEQUENCE object because a.) I'm on SQL Server 2008 and b.) what if QuoteNumber 1234 is emailed to a customer by salesperson #1, then salesperson #1 deletes the quote.  Then salesperson #2 creates a quote just after QuoteNumber 1234 was deleted.  I believe SEQUENCE would assign salesperson #2 QuoteNumber = 1234 assuming 1234 is next in the sequence, which is not good because I would have two different quote with the same QuoteNumber.  If this is correct, then that is why IDENTITY is the way to go here.  I'm open to other suggestions though.

    4.)  Here is the code used to create the table, which works now.  Why the designer wouldn't allow me to do this?  Yes, I added a few more fields.  Note:  Even though I use CAST when creating the table, SQL Server translates the QuoteID function to use CONVERT.

    ([QuoteID]  AS ((CONVERT([varchar](14),[QuoteNumber],0)+'-')+CONVERT([varchar](3),[RevisionNumber],0)) PERSISTED NOT NULL

    USE [QuoteGenerator]
    GO
    
    /****** Object:  Table [dbo].[Quotes1]    Script Date: 07/14/2013 09:53:06 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    SET ARITHABORT ON
    GO
    
    CREATE TABLE [dbo].[Quotes](
    	[QuoteID]  AS CAST([QuoteNumber] AS varchar(14)) + '-' + CAST([RevisionNumber] AS varchar(3)) PERSISTED NOT NULL,
    	[QuoteNumber] [int] IDENTITY(1,1) NOT NULL,
    	[RevisionNumber] [tinyint] NOT NULL,
    	[ProjectName] [varchar](50) NOT NULL,
    	[PDFBackupFile] [varchar](max) NULL,
    	[Status] [varchar](25) NOT NULL,
    	[PublishedDate] [datetime] NULL,
    	[EmailedDate] [datetime] NULL,
    	[ExpirationDate] [date] NULL,
    	[SoldDate] [date] NULL,
    	[Owner] [varchar](50) NOT NULL,
    	[Creator] [varchar](50) NOT NULL,
    	[CreationDate] [datetime] NOT NULL,
    	[ModifiedUser] [varchar](50) NOT NULL,
    	[ModifiedDate] [datetime] NOT NULL,
     CONSTRAINT [PK_QuoteID] PRIMARY KEY CLUSTERED 
    (
    	[QuoteID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[Quotes] ADD  CONSTRAINT [DF_Quotes_RevisionNumber]  DEFAULT ((1)) FOR [RevisionNumber]
    GO


    Ryan


    • Edited by Ryan0827 Sunday, July 14, 2013 5:41 PM editted section 1
    Sunday, July 14, 2013 5:24 PM
  • >I'm open minded about keeping the composite key and ditching the idea of using a computed column, but before I do, why is a computed column a bad idea if it's persisted and can be indexed?  Microsoft doesn't seem to have a problem with it.

    I don't even like composite PRIMARY KEY. I like to keep it simple:

    1. INT IDENTITY SURROGATE PRIMARY KEY

    2. INT WITH SEQUENCE OBJECT (SS 2012) SURROGATE PRIMARY KEY

    The advantage of surrogate is that you never have to change it since a meaningless number.

    How do you FOREIGN KEY to a computed column?

    How do you JOIN to composite PK?  Multiple columns in FK table with multiple ON clause predicates. What a waste of human brain power.

    With INT SURROGATE PK, everything is very simple. Take a look at Production.Product ProductID. There is a huge gap in the ProductID-s but who cares?  It is simple to use as FK, simple to JOIN.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration



    Sunday, July 14, 2013 5:51 PM
  • Thanks to all for the replies.  I do have a few updates and follow up questions.

    1.)  Convenience is the only reason I am considering a computed column (QuoteID) as my primary key as opposed to a composite key of QuoteNumber & RevisionNumber.  This key will be spread across multiple tables and I figure writing the SQL would be a little cleaner.  Plus, in my application I can bind the QuoteID to control versus having to concatenate QuoteNumber and RevisionNumber all the time.

    2.)  I'm open minded about keeping the composite key and ditching the idea of using a computed column, but before I do, why is a computed column a bad idea if it's persisted and can be indexed?  Microsoft doesn't seem to have a problem with it.

    Using a computed column is no worse than a new IDENTITY column for the PK, and has the benefit of being human-readable and the benefit of inserting into a location near the other revisions for that quote.  

    The downside is that you create multiple access paths to your table data.  You will still need a unique index on (QuoteNumber,RevisionNumber) so you end up with a bigger, more complicated data structure by adding the additional key.   The bigger issue is that you are conflating the Quote and QuoteRevision into a single table, which is the source of your ambivalence toward the compound key.

    3.)  I need the identity column (QuoteNumber) as a counter.  As users add quotes to the database I have to keep the new, old, and deleted quote numbers unique because customers will reference this quote number.  I don't believe I can use the SEQUENCE object because a.) I'm on SQL Server 2008 and b.) what if QuoteNumber 1234 is emailed to a customer by salesperson #1, then salesperson #1 deletes the quote.  Then salesperson #2 creates a quote just after QuoteNumber 1234 was deleted.  I believe SEQUENCE would assign salesperson #2 QuoteNumber = 1234 assuming 1234 is next in the sequence, which is not good because I would have two different quote with the same QuoteNumber.  If this is correct, then that is why IDENTITY is the way to go here.  I'm open to other suggestions though.

    Both SEQUENCE and IDENTITY will not generate the same ID twice.  If an ID is generated and then not inserted, rolled back or deleted, it will leave a gap.

    David



    David http://blogs.msdn.com/b/dbrowne/

    Sunday, July 14, 2013 6:16 PM
  • >Using a computed column is no worse than a new IDENTITY column for the PK,

    Hi David,

    Can you explain the advantages of computed column as PK?  Thanks.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



    Saturday, July 20, 2013 11:40 PM
  • >Using a computed column is no worse than a new IDENTITY column for the PK,

    Hi David,

    Can you explain the advantages of computed column as PK?

    The computed column retains some of the advantages of the compound key.  It's human-readable and rows that share the same leading bits are stored in physical proximity.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, July 21, 2013 12:16 AM
  • How can you use a computed PK for FK reference from another table?  The big deal about surrogate primary key is that a meaningless number with no reason whatsoever to change.

    BTW - I never heard of a computed PK before. Is this a theoretical discussion or you actually designed databases with computer PK-s?


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



    Sunday, July 21, 2013 12:26 AM
  • How can you use a computed PK for FK reference from another table?  The big deal about surrogate primary key is that a meaningless number with no reason whatsoever to change.

    BTW - I never heard of a computed PK before. Is this a theoretical discussion or you actually designed databases with computer PK-s?


    I would rather use a compound key. I have seen "intelligent keys" used, but am not really a fan.  


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, July 21, 2013 2:21 AM