CODEWITHSUNDEEP
sql-server
Zwnow
Zwnow

Reputation: 23

SQL how to specify the int(size) when creating a table column?

I am trying to create a table for a MS SQL database for the first time. My query looks like this:

CREATE TABLE Products (
    ProductID INT(4) UNSIGNED AUTO_INCREMENT,
    PRIMARY KEY (ProductID)
);

I looked up on how to create a table and found a resource about SQL Data Types at w3schools here. It states that I can define a size after an integer, but it throws the following error:

Column ProductID has an invalid data type. The UNSIGNED Keyword also is marked as having "Incorrect Syntax near 'UNSIGNED'".

I proceeded to google and search on here for this exact error but wasn't able to find an answer quickly which is weird as this seems like a pretty common error?

Removing the size attribute from the integer works, but I want to define the column width.

I tried to create a table in a MS SQL Database and the query throws errors as explained in the details.

Upvotes: 1

Views: 7624

Answers (1)

Peter
Peter

Reputation: 510

Integers

In MSSQL you have different types of integer:

Signed

type min max bytes
bigint -9,223,372,036,854,775,808(-2^63) 9,223,372,036,854,775,807 (2^63-1) 8
int -2,147,483,648 (-2^31) 2,147,483,647 (2^31-1) 4
smallint -32,768 (-2^15) 32,767 (2^15-1) 2
tinyint -128 127 1

Unsigned

But all numeric types come with UNSIGENED ones that can be used to allow only positive values, doubling the size of positiv number just like you did:

unsigned type min max bytes
bigint 0 18’446’744’073’709’551’616 (2^64-1) 8
int 0 4’294’967’296 (2^32-1) 4
smallint 0 65’536 (2^16-1) 2
tinyint 0 255 1

Make it a habit to set (auto increment) ID's to unsigned, as you did btw., as they will never be negative and give more space.

If you want to have a larger one than INT, use BIGINT.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16

Upvotes: 3

Related Questions