Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: SQL Database Data Type problem

    Hi everyone,

    I am totally new in SQL server databases and I would like some help.

    I would like to create a table with the following properties

    key Field: requestID integer(5)
    day varchar(5)
    period integer(2)
    location varchar(12)

    but the problem is that I was not able to find where I can specify the value of characters allowed to be entered!
    is there any way in SQL server 2008 that I can tell to the database that the following field can be lets say an Integer and it will have only 5 digits? like int(5)??

    Can anybody please help me with that?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Decimal(5, 0) might be more appropriate.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    how about varchar? what I have to put if I want varchar(5)?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Varchar(5) is fine, if you do not need to average, sum or do other math functions on the field.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    MCrowley is hinting at storing it as VARCHAR(5) means it's not a number anymore. You also lose cheap verification that only numbers are placed in that field.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Do you want to store data from a flat file in a table? As in The first 5 characters are numeric and hold the requestID, the next 5 characters hold the day, the next 2 are numeric and hold the period, ...?

    SQL server comes with some predefined integer data types.
    BIGINT: -2^63 (-9 e18) to 2^63-1 (+9 e18); 8 Bytes
    INT: -2^31 (-2 billion) to 2^31-1 (+2 billion); 4 Bytes
    SMALLINT: -2^15 (-32,768) to 2^15-1 (32,767); 2 Bytes
    TINYINT: 0 to 255; 1 Byte

    You must choose one that fits the range of possible values.

    Or you can use a DECIMAL data type: DECIMAL(p ,s).
    p is the precision, the maximum total number of decimal digits that can be stored, both before and after the decimal point. With values from 1 till 38, with default 18.
    s is the scale, the number of decimal digits after the decimal point. The scale must be a value from 0 through p, default 0.
    To store 12.3456, you need a DECIMAL(6, 4).
    To store 123456, you need a DECIMAL(6, 0) or DECIMAL(6).

    day varchar(5)
    What values will the column "day" store? Strings? Like 'Mon' or 'Thurs'? And what about Period?
    Best is to use the standard DATE data type. It will make your life a lot easier later on when you have to do date arithmetic (order by date, summarise per week or month or year, ... ).

    By the way, VARCHAR(5) is perfect SQL syntax. INTEGER(2) isn't.

    About naming a column, "day". You can do that, but using SQL reserved words for column names becomes a pain later on, as you will always have to surround them with "[" and "]": [day]. And it will obfuscate the meaning of your SQL scripts later on. Try to come up with a more descriptive name like StartDate or RequestDate ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •