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.SQLException: [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 Given (ngiven@hotmail.com) writes:
[quoted text, click to view]

The range for an int is from -2^31 to 2^31-1, that is a 32-bit signed
number.

[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server SP3 at

[quoted text, click to view]


[quoted text, click to view]

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