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
Solved

persisted, computed column

Posted on 2013-01-16
5
453 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 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

840 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