how to set maximum length to int data type in sql-CodePudding

ALTER TABLE table_name ALTER COLUMN column_name [int] (4)  NULL;

unable to execute the script, please assist how can I add maximum length to already existed column for an int data type.

CodePudding user response：

If I understand correctly, you want to add a constraint to the column so that it cannot contain a value larger than 9999:

ALTER TABLE table_name
    ADD CONSTRAINT CK_column_name_RANGE CHECK (column_name >= 0 AND column_name <= 9999)

CodePudding user response：

SQL Server's integer data types use binary integers. The INT data type is a 32-bit signed number, which allows values from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). You cannot specify the width of such integers, except by choosing TINYINT or SMALLINT.

If you must somehow prevent numbers outside the range that fits in four digits from getting into your table, you can use the DECIMAL data type; it allows you to specify the digit count.

column_name DECIMAL(4,0)