Some DBMSs, like MariaDB and MySQL, allow to create UNSIGNED columns. SQL Server doesn’t support unsigned integers. Let’s see what we can do instead.
SQL Server supports the following numeric types:
- TINYINT
- SMALLINT
- INT
- BIGINT
- FLOAT
- REAL
In some DBMSs, these datatypes can be qualified as UNSIGNED or SIGNED (which is the default). The practical effects of declaring a column with an UNSIGNED type are:
- It cannot contain values lower than 0;
- The maximum value is approximately multiplied by 2.
Values lower than zero can be obtained, in SQL Server, with a CHECK constrain.
If we also want a higher limit, we can use a bigger type (if the type is integer and not BIGINT).
For example, instead of declaring a column as UNSIGNED TINYINT, we can declare it in one of the following ways:
ALTER TABLE product
ALTER COLUMN qty {
qty INTEGER NOT NULL
CONSTRAINT chk_qty CHECK (qty > 0)
} WITH CHECK;
ALTER TABLE product
ALTER COLUMN qty {
qty BIGINT NOT NULL
CONSTRAINT chk_qty CHECK (qty > 0)
} WITH CHECK;The WITH CHECK clause tells SQL Server to check whether existing values are valid. If we’re sure that all values are valid, and we want to make the operation faster, we can use WITH NOCHECK. The default is WITH CHECK.