1

We have two existing columns in our database named 'ClientIncome' and 'PartnerIncome'. We would like to add a new column named 'HouseholdIncome'

We would like to add the values from 'ClientIncome' and 'PartnerIncome' into the new 'HouseholdIncome' column?

Here is a small example of our database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Incomes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientIncome] [float] NOT NULL,
    [PartnerIncome] [float] NULL,
    [HouseholdIncome] [float] NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Incomes] ON 

GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (1, 130.77, NULL, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (2, 294.82, 339.28, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (3, 0, NULL, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (4, 0, 396.82, NULL)
GO
SET IDENTITY_INSERT [dbo].[Incomes] OFF
GO

The table we would like to update has around 5 million rows, so any help with adding this new column would be appreciated.

  • Note that there are several existing questions about adding columns to large tables, including specifically for SQL Server (dba.stackexchange.com/questions/4506/…). Arguably this question can stand alone, as the real solution is a computed column, but do please use the search feature before asking. – Jon of All Trades Jul 31 '18 at 20:53
6

Just change your table create statement to use a computed column.

CREATE TABLE [dbo].[Incomes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientIncome] [float] NOT NULL,
    [PartnerIncome] [float] NULL,
    [HouseholdIncome] AS COALESCE(ClientIncome, 0.) + COALESCE(PartnerIncome, 0.)
)

If your table is already created, you can add a computed column:

ALTER TABLE dbo.Incomes ADD HouseholdIncome 
    AS COALESCE(ClientIncome, 0.) + COALESCE(PartnerIncome, 0.);
  • 1
    And if the values aren't likely to change often, you may want to consider making it PERSISTED to actually save the value to disk. – LowlyDBA Jul 31 '18 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.