Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tonypelosi
Starting Member
6 Posts |
 Posted - 2006-08-15 : 05:31:12
|
Still in the world of SAMS Teach Yourself SQL in 24 Hours with SQL 2000. The book says:CREATE TABLE EMPLOYEE_TBL(EMP_ID VARCHAR(9) NOT NULL,LAST_NAME VARCHAR(15) NOT NULL,FIRST_NAME VARCHAR(15) NOT NULL,MIDDLE_NAME VARCHAR(15) NOT NULL,ADDRESS VARCHAR(30) NOT NULL,CITY VARCHAR(15) NOT NULL,STATE CHAR(2) NOT NULL,ZIP INTEGER(5) NOT NULL,PHONE CHAR(10),PAGER CHAR(10),CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)); Query Analyser says:Server: Msg 2716, Level 16, State 1, Line 3Column or parameter #8: Cannot specify a column width on data type int.If I take the (5) off the end of the INTEGER data type it runs through but how come you can't specify the width of an integer column?Ta muchly. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-15 : 05:34:01
|
| A SQL integer always takes 4 bytes of storage, ranging from -2147483648 to 2147483647.Peter LarssonHelsingborg, Sweden |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-08-15 : 05:35:30
|
| Integer datatype size is predefined by SQL Server as 4 Bytes and its static.Chirag |
|
|
|
tonypelosi
Starting Member
6 Posts |
Posted - 2006-08-15 : 05:56:36
|
| Couldn't it be construed as a a bit of a waster though if I only need 5 digits or are we breaking into the realms of "it just doesn't make that much difference."? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-15 : 05:59:20
|
| Don't consider using VARCHAR(5) if storing numbers only.INT still takes less storage. Instead place a check constraint that says value need to be between 1 and 99999.Peter LarssonHelsingborg, Sweden |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-08-15 : 08:10:33
|
quote:
Originally posted by tonypelosi
Still in the world of SAMS Teach Yourself SQL in 24 Hours with SQL 2000. The book says:CREATE TABLE EMPLOYEE_TBL(EMP_ID VARCHAR(9) NOT NULL,LAST_NAME VARCHAR(15) NOT NULL,FIRST_NAME VARCHAR(15) NOT NULL,MIDDLE_NAME VARCHAR(15) NOT NULL,ADDRESS VARCHAR(30) NOT NULL,CITY VARCHAR(15) NOT NULL,STATE CHAR(2) NOT NULL,ZIP INTEGER(5) NOT NULL,PHONE CHAR(10),PAGER CHAR(10),CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)); Query Analyser says:Server: Msg 2716, Level 16, State 1, Line 3Column or parameter #8: Cannot specify a column width on data type int.If I take the (5) off the end of the INTEGER data type it runs through but how come you can't specify the width of an integer column?Ta muchly.
As has been pointed out - that is a syntax error in to book. It might be worth looking for the website, and see of they have errata published.There are several 'integer' data type catering for different ranges:quote:
from BOLbigintInteger (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.intInteger (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.smallintInteger data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.tinyintInteger data from 0 through 255. Storage size is 1 byte.
As was mentioned, constraints can be used to ensure data is entered that matches a more specific range. Similarly, you could create a user defined data type (UDT's) - but that will probably be quite a bit further on in your education, and in my experience, aren't all that common - I've not had to use one.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-15 : 08:13:02
|
| From BOL :smallintInteger data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.U may use smallint if ur value doesn't exceed the above limits. [not good for all 5 digit #s though.]Srinika |
|
|
|
|
|
|
|
|
Stupid Q No.2