2

I added a calculated field to my table, and only want to update NULL rows (or "new" rows). Something is going awry, and every row in my table is being updated with the current DateTime when 1 row is inserted. This is the Create Table syntax, what must I do so that only the "new" rows are updated with the current DateTime

CREATE TABLE [dbo].[SE](
[dbID] [int] IDENTITY(1,1) NOT NULL,
[uqID] [varchar](31) NOT NULL,
[POD] [varchar](255) NULL,
[EC] [varchar](255) NULL,
[S1] [varchar](35) NULL,
[S2] [varchar](35) NULL,
[S3] [varchar](35) NULL,
[S4] [varchar](35) NULL,
[S5] [varchar](35) NULL,
[S6] [varchar](35) NULL,
[S7] [varchar](35) NULL,
[PSD] [varchar](50) NULL,
[DS] [datetime] NULL,
[CN] [varchar](max) NULL,
[autodate]  AS (getdate()),
PRIMARY KEY CLUSTERED 
(
[dbID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
7

You don't want a calculated column, instead just use a datetime (non-nullable) column, with a default constraint using getdate().

The syntax for the column in the table definition SQL is as follows:

[autodate] DATETIME NOT NULL DEFAULT(GETDATE())

A Computed Column in SQL Server is calculated when it is queried (unless it is PERSISTED, then it has a value stored on disk and can be indexed, however it is still computed). It is intended to be changeable, and be the result of calculations on another column or columns.

A DEFAULT Constraint simply inserts a default value into the column for any inserts, but then it is held as a fixed value just like any other data column.

| improve this answer | |
  • What is the difference in the calculated column and a non-nullable column as you listed? – SmallFries BigGuys Jan 26 '17 at 14:48
  • 5
    @SmallFriesBigGuys A computed column like the one you created is always evaluated at query time. A default value is only generated at insert time. – Aaron Bertrand Jan 26 '17 at 14:53
  • 2
    @SmallFriesBigGuys as Aaron Bertrand said. I've expanded my answer a bit to include some information & links – Ian_H Jan 26 '17 at 14:56

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.