Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

persisted, computed column

Posted on 2013-01-16
5
Medium Priority
?
461 Views
Last Modified: 2013-01-16
Simple table with two ID's that come from two other tables.  IF SecondID is NULL, I want it to DEFAULT to the FirstID.  At first I thought I would do an AFTER INSERT trigger, but I really do try to avoid triggers, whenever I can.

So, I thought I'd try the computed column, persisted.


CREATE TABLE [dbo].[table](
      [uniqueID] [int] IDENTITY(1,1) NOT NULL,
      [FirstID] [int] NOT NULL,
      [SecondID] AS (CASE  
            WHEN [SecondID] IS NULL THEN [FirstID] ELSE [SecondID] END) PERSISTED,

My error:

Msg 402, Level 16, State 1, Line 7
The data types void type and void type are incompatible in the is operator.
Msg 1911, Level 16, State 1, Line 2
Column name 'SecondID' does not exist in the target table or view.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Please let me know, if you can.
0
Comment
Question by:dbaSQL
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38782668
you cannot do this with a computed column, at least not in the column itself.
a computed column is computed, and cannot store data "directly". it will only evaluate to the expression.

so, you could have ThirdID column
CREATE TABLE [dbo].[table](
      [uniqueID] [int] IDENTITY(1,1) NOT NULL,
      [FirstID] [int] NOT NULL,
      [SecondID] [int] NULL,
      [ThirdID] AS (CASE  
            WHEN [SecondID] IS NULL THEN [FirstID] ELSE [SecondID] END) PERSISTED, 

Open in new window


note: the expression could be simplified to :
    [ThirdID] AS ISNULL( [SecondID] , [FirstID] ) PERSISTED, 

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38782693
yep, i was just reading the same on a sqlservercentral post.  i don't want the third value, though.  as much as i don't like them, I'm thinking the after insert trigger is the way to go.  would you agree, angeliii?


CREATE TRIGGER [dbo].[trigger] ON [dbo].[table] AFTER INSERT
AS

      BEGIN
            UPDATE table
            SET SecondID = FirstID
                                   FROM inserted
            WHERE table.uniqueID = inserted.uniqueID
            AND inserted.SecondID IS NULL
      END

GO
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38782708
yes
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38782727
thank you, sir
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 38782729
very quick feedback, thank you.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

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 article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

772 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