Menu
I am adding a computed column from three fields. It will be a VARCHAR field. I need to specify the max length for the computed column. How can we specify that.
ALTER TABLE [MyTable] ADD CustomNumber AS [PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ]
Is there a way I can restrict it? I need to raise an error if it is more than 10 character long
Reference: Specify Computed Columns in a Table
324
To find the max value of a column, use the MAX() aggregate function; it takes as its argument the name of the column for which you want to find the maximum value. If you have not specified any other columns in the SELECT clause, the maximum will be calculated for all records in the table.
Each object has maximum size limitations. SQL maximum column name length limitation is 128 characters. If we create more than 128 characters, it shows an error.
METHOD 1 : USING T-SQL In this method, we need to first drop the computed column and then add it as a new column to accommodate the altered computed column in any table. Given below is the script. Once you drop and recreate the computed column, lets browse and check it again. -- Display the rows in the table.
A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs.
One way
CREATE TABLE [MyTable]
(
[PGM] VARCHAR(50),
[GRP] VARCHAR(50),
[PGMGRPSEQ] VARCHAR(50),
CustomNumber AS [PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ] PERSISTED CHECK (LEN(CustomNumber) <= 10)
)
Or if you don't want to persist the computed column just add a check constraint that repeats the expression.
CREATE TABLE [MyTable]
(
[PGM] VARCHAR(50),
[GRP] VARCHAR(50),
[PGMGRPSEQ] VARCHAR(50),
CustomNumber AS [PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ],
CHECK (LEN([PGM]+'-'+[GRP]+'-'+[PGMGRPSEQ] ) <= 10)
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
