none
Alter calculated column in a table

    Question

  • Hi!

    I would like to alter a calculated column in table Testkalkyl (see my example below) that divides Column total in table Testkalkyl with column BTA in Testgr table if

    Id in Testkalkyl = Id in Testgr.  Is that possible and would the altered column be updated when there is changes in BTA or total columns?

    Best regards

    Arne Olsson

    USE TEMPDB;
    
    CREATE TABLE #Testgr
    (
    Id Varchar(5), 
    Name Varchar(100),
    BTA bigint
    );
    
    INSERT INTO #Testgr (Id, Name, BTA)
    VALUES ('10001','KALLE',1000),
    	('10002','Nisse',2000),
    	('10003','Maja',1000),
    	('10004','Lisa',2000);
     
    CREATE TABLE #Testkalkyl
    (
    Id Varchar(5),
    Name Varchar(100),
    Total FLOAT
    );
    
    INSERT INTO #Testkalkyl
    VALUES('10001','KALLE',10000),
    	('10002','Nisse',20000),
    	('10003','Maja',20000),
    	('10005','NoMatch',50000);

    Arne Olsson
    Tuesday, April 12, 2011 3:21 PM

Answers

  • Yes, you can create a computed column for your specifications and you can even make it persistent. When you retrieve the data from your table, it will get the correct value in the computed column.

    Alternative solution will be creating a view with the computed column.

    If your computation involves multiple tables, then the only way to create computed column is to have a user defined function. Therefore a view will be preferable in your scenario.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, April 12, 2011 3:25 PM
    Moderator
  • I Agree With Naomi.

    Doing the column presistent should be in case of performance issue (WHERE, etc...) or if you want to add a constraint on it.

    Also, you should avoid to use FLOAT since they have rounding issues, use fixed precision values like for example DECIMAL.





    • Marked as answer by Arne Olsson Thursday, April 14, 2011 3:52 AM
    Tuesday, April 12, 2011 5:49 PM
  • http://dimantdatabasesolutions.blogspot.com/2008/09/computed-colimn-is-persisted.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Arne Olsson Thursday, April 14, 2011 3:45 AM
    Wednesday, April 13, 2011 8:13 AM
    Answerer
  • Actually, you can't have a computed filed in that particular case, since all field shoud be in the same table, but you could crreate a view like:

    create view [Sample]
    
    as
    
    select g.*, k.Total, k.Total / g.BTA AS Ratio
    
    from dbo.Testgr g 
    
    	inner join dbo.Testkalkyl k on g.Id = k.Id 
    GO
    SELECT * from dbo.Sample 

     

    Also, "Name" looks to be repeated, means your model is not normalized.


    • Marked as answer by Arne Olsson Thursday, April 14, 2011 3:52 AM
    Wednesday, April 13, 2011 11:51 AM
  • Here a sample with computed not persistent filed:

    USE [tempdb]
    GO
    CREATE TABLE [dbo].[OrderDetail](
    	[OrderDetailID] [int] IDENTITY (1,1) NOT NULL,
    	[OrderID] [int] NOT NULL,
    	[ProductID] [int] NOT NULL,
    	[UnitPrice] [money] NOT NULL,
    	[Quantity] [int] NOT NULL,
    	[Rebate] [money] NOT NULL,
    	[Total] AS (([UnitPrice]*[Quantity])*((1)-[Rebate])) /*PERSISTED NOT NULL*/,
     CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
    (
    	[OrderDetailID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[OrderDetail] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Rebate]) VALUES (1, 1, 5.2000, 2, 0.0100)
    INSERT [dbo].[OrderDetail] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Rebate]) VALUES (1, 2, 4.2500, 25, 0.1000)
    INSERT [dbo].[OrderDetail] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Rebate]) VALUES (2, 2, 4.2500, 10, 0.2000)
    
    For persitent, uncomment the code
     


    • Proposed as answer by Naomi NModerator Wednesday, April 13, 2011 1:54 PM
    • Marked as answer by Arne Olsson Thursday, April 14, 2011 3:53 AM
    Wednesday, April 13, 2011 12:01 PM

All replies

  • Yes, you can create a computed column for your specifications and you can even make it persistent. When you retrieve the data from your table, it will get the correct value in the computed column.

    Alternative solution will be creating a view with the computed column.

    If your computation involves multiple tables, then the only way to create computed column is to have a user defined function. Therefore a view will be preferable in your scenario.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, April 12, 2011 3:25 PM
    Moderator
  • I Agree With Naomi.

    Doing the column presistent should be in case of performance issue (WHERE, etc...) or if you want to add a constraint on it.

    Also, you should avoid to use FLOAT since they have rounding issues, use fixed precision values like for example DECIMAL.





    • Marked as answer by Arne Olsson Thursday, April 14, 2011 3:52 AM
    Tuesday, April 12, 2011 5:49 PM
  • Hi!

    Thanks for your answer. As i am a beginner i wonder if i could get help on how the code for this should loook like?

    best regards!

    Arne


    Arne Olsson
    Wednesday, April 13, 2011 8:09 AM
  • http://dimantdatabasesolutions.blogspot.com/2008/09/computed-colimn-is-persisted.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, April 13, 2011 8:12 AM
    Answerer
  • http://dimantdatabasesolutions.blogspot.com/2008/09/computed-colimn-is-persisted.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Arne Olsson Thursday, April 14, 2011 3:45 AM
    Wednesday, April 13, 2011 8:13 AM
    Answerer
  • Actually, you can't have a computed filed in that particular case, since all field shoud be in the same table, but you could crreate a view like:

    create view [Sample]
    
    as
    
    select g.*, k.Total, k.Total / g.BTA AS Ratio
    
    from dbo.Testgr g 
    
    	inner join dbo.Testkalkyl k on g.Id = k.Id 
    GO
    SELECT * from dbo.Sample 

     

    Also, "Name" looks to be repeated, means your model is not normalized.


    • Marked as answer by Arne Olsson Thursday, April 14, 2011 3:52 AM
    Wednesday, April 13, 2011 11:51 AM
  • Here a sample with computed not persistent filed:

    USE [tempdb]
    GO
    CREATE TABLE [dbo].[OrderDetail](
    	[OrderDetailID] [int] IDENTITY (1,1) NOT NULL,
    	[OrderID] [int] NOT NULL,
    	[ProductID] [int] NOT NULL,
    	[UnitPrice] [money] NOT NULL,
    	[Quantity] [int] NOT NULL,
    	[Rebate] [money] NOT NULL,
    	[Total] AS (([UnitPrice]*[Quantity])*((1)-[Rebate])) /*PERSISTED NOT NULL*/,
     CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
    (
    	[OrderDetailID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[OrderDetail] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Rebate]) VALUES (1, 1, 5.2000, 2, 0.0100)
    INSERT [dbo].[OrderDetail] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Rebate]) VALUES (1, 2, 4.2500, 25, 0.1000)
    INSERT [dbo].[OrderDetail] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Rebate]) VALUES (2, 2, 4.2500, 10, 0.2000)
    
    For persitent, uncomment the code
     


    • Proposed as answer by Naomi NModerator Wednesday, April 13, 2011 1:54 PM
    • Marked as answer by Arne Olsson Thursday, April 14, 2011 3:53 AM
    Wednesday, April 13, 2011 12:01 PM