How can I determine if a column is a temporal column-CodePudding

I have this table with temporal columns:

CREATE TABLE [dbo].[Profile](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CreatedDate] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [UpdatedDate] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (CreatedDate, UpdatedDate),
 CONSTRAINT [PK_Profile] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

I'd like to determine if one of my table columns is a "GENERATED ALWAYS" type of column, this does not work and returns 0

declare @TABLE_SCHEMA as nvarchar(255) = 'dbo'
declare @TABLE_NAME as nvarchar(255) = 'Profile'
declare @COLUMN_NAME as nvarchar(255) = 'CreatedDate'

select COLUMNPROPERTY(object_id('['   @TABLE_SCHEMA   '].['   @TABLE_NAME   ']'), @COLUMN_NAME, 'IsComputed')

CodePudding user response：

Based on another answer, I was able to figure it out, the code below will return 1 (for row last updated date) or 2 (for row start date) if it's a generated always column, or 0 if it's not

https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.generatedalwaystype?view=sql-smo-160

declare @TABLE_SCHEMA as nvarchar(255) = 'dbo'
declare @TABLE_NAME as nvarchar(255) = 'Profile'
declare @COLUMN_NAME as nvarchar(255) = 'CreatedDate'

select COLUMNPROPERTY(object_id('['   @TABLE_SCHEMA   '].['   @TABLE_NAME   ']'), @COLUMN_NAME, 'GeneratedAlwaysType')

CodePudding user response：

It looks like you are trying to determine if a column is a computed column in SQL Server. In the example code you provided, you are using the COLUMNPROPERTY function to check the IsComputed property of the column. This should return a value of 1 if the column is a computed column, and 0 if it is not.

In the specific case of the columns you listed in your question, it looks like they are both "generated always" columns. This means that their values are automatically generated by the database engine, based on the expressions specified in the AS clause of the column definition. These columns are considered to be computed columns.

To determine if a column is a generated always column in SQL Server, you can use the COLUMNPROPERTY function as follows:

declare @TABLE_SCHEMA as nvarchar(255) = 'dbo'
declare @TABLE_NAME as nvarchar(255) = 'Profile'
declare @COLUMN_NAME as nvarchar(255) = 'CreatedDate'

-- Check if the column is a computed column
if COLUMNPROPERTY(object_id('['   @TABLE_SCHEMA   '].['   @TABLE_NAME   ']'), @COLUMN_NAME, 'IsComputed') = 1
begin
    -- Check if the column is a generated always column
    if COLUMNPROPERTY(object_id('['   @TABLE_SCHEMA   '].['   @TABLE_NAME   ']'), @COLUMN_NAME, 'GeneratedAlwaysType') = 3
    begin
        -- The column is a generated always column
        print @COLUMN_NAME   ' is a generated always column.'
    end
    else
    begin
        -- The column is a computed column, but not a generated always column
        print @COLUMN_NAME   ' is a computed column, but not a generated always column.'
    end
end
else
begin
    -- The column is not a computed column
    print @COLUMN_NAME   ' is not a computed column.'
end

In this code, we first check if the column is a computed column using the COLUMNPROPERTY function and the IsComputed property. If the column is a computed column, we then check the GeneratedAlwaysType property of the column to determine if it is a generated always column. The GeneratedAlwaysType property can have the following values:

0: The column is not a generated always column. 1: The column is a generated always as row start column. 2: The column is a generated always as row end column. 3: The column is a generated always as row version column. In your example, the CreatedDate column is a generated always as row start column, and the UpdatedDate column is a generated always as row end column.

I hope this helps!