INT Size

[Nathan Given]

Hello All,

Short Version:

How large can a MS SQL Server INT become?

Long Version:

I have this SQL statement...

CREATE TABLE admin_tokens (
user_id varchar(27) NOT NULL default '',
elt varchar(16) NOT NULL default '',
token_index int(11) NOT NULL default '0'
CONSTRAINT PK_admin_tokens PRIMARY KEY (user_id, token_index)
);
And I get the following error:

java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Column or parameter #3: Cannot specify a column width
on data type int.

Is this a MS SQL server issue only? If I remove the (11) part it
works fine... but I'm not sure how large the int can be... I want to
make sure the database stops working in a couple of years.

Thanks!
--
Nathan

[Erland Sommarskog]

Nathan Given (ng****@hotmail .com) writes:

Hello All,

Short Version:

How large can a MS SQL Server INT become?
The range for an int is from -2^31 to 2^31-1, that is a 32-bit signed
number.
Long Version:

I have this SQL statement...

CREATE TABLE admin_tokens (
user_id varchar(27) NOT NULL default '',
elt varchar(16) NOT NULL default '',
token_index int(11) NOT NULL default '0'
CONSTRAINT PK_admin_tokens PRIMARY KEY (user_id, token_index)
);
And I get the following error:

java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Column or parameter #3: Cannot specify a column width
on data type int.

Is this a MS SQL server issue only? If I remove the (11) part it
works fine... but I'm not sure how large the int can be... I want to
make sure the database stops working in a couple of years.

I don't know the full story about the int(11), but I've seen it
posts which appears to have been about MySQL. (People who use MySQL
posts this group for some reason.) So it may be proprietary for MySQL.
Then again, it could be in the ANSI Standard for all I know.

In any case, int(11) is not supported in SQL Server. Instead you have
these integer types to choose from:

tinyint - unsigned 8-bit (0-255)
smallint - signed 16-bit (-32768 - 32767)
int - signed 32-bit
bigint - signed 64-bit.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve Kass]

Erland Sommarskog wrote:

In any case, int(11) is not supported in SQL Server. Instead you have
these integer types to choose from:

tinyint - unsigned 8-bit (0-255)
smallint - signed 16-bit (-32768 - 32767)
int - signed 32-bit
bigint - signed 64-bit.

While they are not technically integer types, these SQL Server types can also be used to store integer values:

NUMERIC(1), NUMERIC(2) and so on up to NUMERIC(38). In SQL Server, NUMERIC(k) is the same as NUMERIC(k,0), DECIMAL(k), or DECIMAL(k,0).

The (11) allowed by MySql is an "optional display width specification". Types that know what they are supposed to look like? Sounds like a
bad idea to me, non-portability aside.

Steve Kass
Drew University