none
Data types in SQL Server

    Question

  • Hi,
    Can someone please tell me what are all the data types in MS SQL Server 2005 Express used for ?
    I have only used Access before and the data types in Access are -

    DATA TYPE    INFORMATION STORED
    Autonumber - A number that is assigned automatically and never changes thereafter.
    Currency   - Amount in the currency format chosen.
    Date/Time  - Date and Time. The Format property chosen on the general tab to date alone or time alone or both in different formats.
    Hyperlink  - Hyperlink addresses.
    Lookup     - Values that come from another table, a query, or a list of values that are supplied. Select the Lookup Wizard data type to set the lookup field automatically.
    Memo       - Large bodies of text - upto 64,000 characters in length.
    Number     - True numbers such as quantities.
    OLE Object - Any OLE object such as a picture, sound, or word processing document.
    Text       - Any written text upto 255 characters in length, numbers, hyphens and nonnumeric charaters.
    Yes/No     - Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off.


    I am familiar with the data types given above, and I couldn't find any similar data types in SQL Server. I do not have any programming knowledge and I use the GUI tools of SQL Server Management Studio Express and Visual Basic 2005 Express. All the data types I found there (listed below) seemed alien to me and I couldn't make head or tail out of it. Can someone please be kind enough to list out all the types of data stored under the information stored column, after the corresponding data type from SQL server, that I have mentioned below ? I need to know what exactly is stored in each data type. I have already checked the SQL Server 2005 books online for the data types but could not find anything of substance there.


    DATA TYPE           INFORMATION STORED
    bigint
    binary (50)
    bit
    char (10)
    datetime
    decimal (18,0)
    float
    image
    int
    money
    nchar (10)
    ntext
    numeric (18,0)
    nvarchar (50)
    nvarchar (max)
    real
    smalldatetime
    smallint
    smallmoney
    sql_variant
    text
    timestamp
    tinyint
    uniqueidentifier
    varbinary (50)
    varbinary (max)
    varchar (50) 
    varchar (max)
    xml


    Also can someone please tell me what are the data types in SQL Server that correspond to the all the data types from Access ?
    I especially need to know the data type for Yes/No (boolean); OLE Object; memo; and Lookup, in SQL Server.


    I would like to know if there is any data type or format for a data type for storing 'telephone numbers'. This is of special interst to me, as I would like to store data in particular fields in my 'contacts' database as telephone nos, and create a button on my forms that allows users to dial the selected tel no in the record by cliking the 'dial' button, which would dial the no using the windows dialer provided by Win XP, using a normal V9.0 voice/data/fax dialup modem. I have tired to insert a button in Access before, which opens an application, in this case, the windows dialer, and then the user can switch windows, get the required phone no from the record, switch back to the dialer, and enter the no and dial. But, instead of this tedious process, wouldn't it be easier if the tel no is stored as a separate data type instead of a normal number data type and all the user has to do is select the tel no in the record on the form and (maybe) right click it and select the option to dial on the menu that appears.
    Or, even if there is no right click menu, the user can select the tel no record, and then click on the dial button on the form and it would dial the no. I think the Windows Address Book has a similar feature. If there is a way to it, can someone please tell me about it ?


    I request you to please give me all the directions using the GUI tools of SQL Server Management Studio Express and Visual Basic Express, and not anything from T-SQL or any other programming language as I do not know anything about programming, and it wouldn't help me at all.
    You're speaking to a rookie here who has just used the GUI tools of MS Access before. But I can copy paste a bit of code here and there, and if I understand it, I might even try modifying it a bit to meet my requirements.

     

     

     

     

     

     

     

     

     

     

     


     

    Saturday, January 28, 2006 8:43 PM

Answers

  • Best bet is to use VARCHAR with appropriate length for each.  There is no specific types for them in SQL.  All logics to make sure data entered is in correct format should be handled by your application (email format validation, password rule validation, etc.).

     

    Monday, April 17, 2006 7:22 PM

All replies

  • Greetings.

    If you are to use SQL Server (any version) you MUST understand SQL. Changing from Access to SQL Server Express is not a good idea if you want to stay away from programming, either.

    There is nothing in your scenario I can see that makes me think you need to use SQL Server (Express), Access is a more logical choice for you. That is, unless you want to learn programming.

    Hope this helps

    Sunday, January 29, 2006 2:04 PM
  • I think in this case I might have to start learning some programming along the way. BTW, what programming languages, apart from T-SQL,  were you refering to, Gorm ? Because, I know that while T-SQL is mostly English-like syntax, and would be relatively easy for me to learn, I am very, very bad at maths and I guess that doesn't bode well for learning any programming, does it ?

    And, the reason I chose to switch from Access to SQL Server, was because I had begun to face many limitations in Access, especially in finance related databases. ( I will come back for more help in designing the structure of financial databases later ). 

    Meanwhile, while I'm learning, could someone please help me out with the datatypes ?

     

    Sunday, January 29, 2006 5:22 PM
  • 
    Hi Rishi,
     
    Here's a short description of SQL Server's datatypes. More information can easily be found in Books Online.
     
    Numeric datatypes
     
    For numeric information, you have three subcategories:
     
    Whole numbers: bit (0 or 1), tinyint (0 - 255), smallint (-32,768 - 32,767), int (approx -2 billion - 2 billion), bigint (approx -9 quintillion - 9 quintillion). A larger range comes with a larger storage size - which might induce a performance penalty. Also, the bit datatype has some funny behaviour on conversions, so you might wish to avoid it.
    Fixed point: numeric(n,m) or decimal(n,m). These are synonymous. The first number (n) gives the total number of digits; the second number (m) is the number of digits after the decimal point. So numeric(5,2) would be used to store values from -999.99 up to 999.99.
    Floating point: float or real (again, synonyms - sort of; see Books Online for the details). Of limited use, except in some scientific applications. Never use it for monetary amounts, because rounding in the base-2 notation that is used internally can cause strange behaviour.
     
    Date/time datatypes
     
    Datetime and smalldatetime. Datetime represents dates from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second. Smalldatetime represents dates from January 1, 1900 through June 6, 2079, with accuracy to the minute, but uses less storage size.
    There are no adta types for only date, only time, or for timespans.
     
    Character/text datatypes
     
    Char(n) and nchar(n) for fixed-length character strings of length n (shorter strings will be padded with spaces); varchar(n) and nvarchar(n) for variable-length character strings of maximum length n (uses n [or n*2 for nvarchar] bytes of storage, plus 2 extra bytes to store the current length); varchar(max) and nvarchar(max) are special versions of varchar and nvarchar for very long character strings (up to 2 billion bytes). The difference between char/varchar and nchar/nvarchar is that nchar/nvarchar accept Unicode characters, but use 2 bytes to store each character. There are also the text and ntext datatypes, but the are only included for compatibility with earlier versions, and they will disappear in a future version.
     
    Binary strings
     
    Binary(n), varbinary(n), and varbinary(max) are very much like char and varchar, except that they are used to store binary data instead of string data. And image, like text and ntext, exists for compatibility only.
     
    Other
     
    That leaves us with some special datatypes: cursor, table, sql_variant, uniqueidentifier, and xml. The xml datatype is new and will probably be used more and more over the coming months as it's capabilities are explored by the users; the other four have all proven to be useful is some situations - but none of them are relevant if you are new to SQL Server.
     
    For much more details on all datatypes, I suggest you consult Books Online.

    --
    Hugo Kornelis, SQL Server MVP
     

    Hi,
    Can someone please tell me what are all the data types in MS SQL Server 2005 Express used for ?
    I have only used Access before and the data types in Access are -

    DATA TYPE INFORMATION STORED
    Autonumber - A number that is assigned automatically and never changes thereafter.
    Currency - Amount in the currency format chosen.
    Date/Time - Date and Time. The Format property chosen on the general tab to date alone or time alone or both in different formats.
    Hyperlink - Hyperlink addresses.
    Lookup - Values that come from another table, a query, or a list of values that are supplied. Select the Lookup Wizard data type to set the lookup field automatically.
    Memo - Large bodies of text - upto 64,000 characters in length.
    Number - True numbers such as quantities.
    OLE Object - Any OLE object such as a picture, sound, or word processing document.
    Text - Any written text upto 255 characters in length, numbers, hyphens and nonnumeric charaters.
    Yes/No - Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off.


    I am familiar with the data types given above, and I couldn't find any similar data types in SQL Server. I do not have any programming knowledge and I use the GUI tools of SQL Server Management Studio Express and Visual Basic 2005 Express. All the data types I found there (listed below) seemed alien to me and I couldn't make head or tail out of it. Can someone please be kind enough to list out all the types of data stored under the information stored column, after the corresponding data type from SQL server, that I have mentioned below ? I need to know what exactly is stored in each data type. I have already checked the SQL Server 2005 books online for the data types but could not find anything of substance there.


    DATA TYPE INFORMATION STORED
    bigint
    binary (50)
    bit
    char (10)
    datetime
    decimal (18,0)
    float
    image
    int
    money
    nchar (10)
    ntext
    numeric (18,0)
    nvarchar (50)
    nvarchar (max)
    real
    smalldatetime
    smallint
    smallmoney
    sql_variant
    text
    timestamp
    tinyint
    uniqueidentifier
    varbinary (50)
    varbinary (max)
    varchar (50)
    varchar (max)
    xml


    Also can someone please tell me what are the data types in SQL Server that correspond to the all the data types from Access ?
    I especially need to know the data type for Yes/No (boolean); OLE Object; memo; and Lookup, in SQL Server.


    I would like to know if there is any data type or format for a data type for storing 'telephone numbers'. This is of special interst to me, as I would like to store data in particular fields in my 'contacts' database as telephone nos, and create a button on my forms that allows users to dial the selected tel no in the record by cliking the 'dial' button, which would dial the no using the windows dialer provided by Win XP, using a normal V9.0 voice/data/fax dialup modem. I have tired to insert a button in Access before, which opens an application, in this case, the windows dialer, and then the user can switch windows, get the required phone no from the record, switch back to the dialer, and enter the no and dial. But, instead of this tedious process, wouldn't it be easier if the tel no is stored as a separate data type instead of a normal number data type and all the user has to do is select the tel no in the record on the form and (maybe) right click it and select the option to dial on the menu that appears.
    Or, even if there is no right click menu, the user can select the tel no record, and then click on the dial button on the form and it would dial the no. I think the Windows Address Book has a similar feature. If there is a way to it, can someone please tell me about it ?


    I request you to please give me all the directions using the GUI tools of SQL Server Management Studio Express and Visual Basic Express, and not anything from T-SQL or any other programming language as I do not know anything about programming, and it wouldn't help me at all.
    You're speaking to a rookie here who has just used the GUI tools of MS Access before. But I can copy paste a bit of code here and there, and if I understand it, I might even try modifying it a bit to meet my requirements.


    Sunday, January 29, 2006 9:48 PM
  • Thanks a lot Hugo, you've been a great help.

    But I've still got a few questions -

    How do I store images in my database, and what datatype should I select for it, since you mention that the image datatype is only there for compatibility reasons and will dissappear in the near future ?

    What datatype should I select for storing Yes/No or the checkbox type of value. ( Like in Access, you could select the boolean data type and then you got a choice as to what it displayed in the table view, either a checkbox, or a Yes/No value.)

    What datatype should I select for storing a Lookup value from another table ?

    Are the money and small money datatypes used for storing currency values, as per the currency type set in Windows Regional Settings ?

    What are the timestamp and uniqueidentifier datatypes used for ?

    I couldn't find what I was looking for in Books Online, so I would be really grateful if you could also provide me with the relevant links for the information I am looking for in Books Online.

    Rishi.

    Thursday, February 02, 2006 7:26 PM
  • 
    Hi Rishi,
     
    That's a lot of questions!!
     
    >>How do I store images in my database, and what datatype should I select for it, since you mention that the image datatype is only there for compatibility reasons and will dissappear in the near future ?
     
    Use varbinary(max), the replacement datatype for image. You can do many things with varbinary(max) that you could never do with image; that's the reason why the image datatype will be phased out.
     
    >>What datatype should I select for storing Yes/No or the checkbox type of value. ( Like in Access, you could select the boolean data type and then you got a choice as to what it displayed in the table view, either a checkbox, or a Yes/No value.)
     
    I'd recommend a CHAR(1) with a CHECK constraint to limit the contents to 'Y' and 'N' (or 'T' and 'F'), plus a NOT NULL constraint. There are also a lot of people who recommend using a numeric datatype and use e.g. 1 for true and 0 for false, but I find that the character values are much more obvious when reviewing table data. I would recommend against using the BIT datatype. It is merely confusing, since it is somehow similar to a Boolean (the official name for Yes/No) datatype, yet doesn;t behave like a true Boolean at all.
     
    >>What datatype should I select for storing a Lookup value from another table ?
     
    In a real relational design, there are no "lookup" tables and "other" tables - just tables.
     
    During information analysis, you will determine if a list of valid values for a column should be modeled as a CHECK constraint, or as a table. I'll use some examples to explain.
     
    Case #1: You have to store a currency code. You will, of course, use the three-letter ISO standard codes. The company will only do business in US Dollas, European Euro's, or Japanese Yen; no change to this policy is expected. No data needs to be stored that is functionally dependant on the currency code. In this case, you'll use a hard-coded dropdown (or other input thingie) in the front-end and a CHECK constraint in the database:
     
    CREATE TABLE SomeTable
              (.....,
               CurrencyCode char(3) NOT NULL
                 CHECK (CurrencyCode IN ('USD', 'EUR', 'JPY')),
               ......
              )
     
    Case #2: You have to store a currency code. You will, of course, use the three-letter ISO standard codes. Because the list of accepted currencies is subject to frequent change, or because some other information is functionally dependant on the currency code, you design a table of Currencies. The ISO currency code will of course be the primary key, and hence also the datatype to use for referencing columns. The dropdown (or other input thingie) in the front-end will be built at run-time by reading the Currencies table; integrity is guarded in the DB by a FOREIGN KEY constraint:
     
    CREATE TABLE Currencies
              (CurrencyCode char(3) NOT NULL PRIMARY KEY,
            -- Other columns
              )
    CREATE TABLE SomeTable
              (.....,
               CurrencyCode char(3) NOT NULL
                 REFERENCES Currencies(CurrencyCode),
               ......
              )
     
    Case #3: Departments in the company are identified by name. These names can be up to 60 characters. For performance reasons, you decide to use a surrogate key for all references to the Departments table. You decide on an integer datatype with the IDENTITY property. The dropdown (or other input thingie) in the front-end will be built at run-time by reading the Departments table (make sure that the department name is shown; the front-end should remember the corresponding surrogate key value); integrity is guarded in the DB by a FOREIGN KEY constraint:
     
    CREATE TABLE Departments
              (DeptID int NOT NULL PRIMARY KEY IDENTITY,
               DeptName varchar(60) NOT NULL UNIQUE,
            -- Other columns
              )
    CREATE TABLE OtherTable
              (.....,
               DeptID int NOT NULL
                 REFERENCES Departments(DeptID)),
               ......
              )
     
    * Note: output should use views or stored procedure that return only DeptName; DeptID is intended for internal use only and must never be shown to the end user!!!!
     
    >>Are the money and small money datatypes used for storing currency values, as per the currency type set in Windows Regional Settings ?
     
    Better not use money and smallmoney at all.
     
    In some ways, they behave very much the same as decimal(19,4) / decimal(10,4). The main differences are that they are formatted somewhat different when output, and that they can introduce some very nasty rouding problems in certain calculations.
     
    DECLARE @a money, @b money,
            @c decimal(19,4), @d decimal(19,4)
    SET @a = 123.45
    SET @b = 67.89
    SET @c = 123.45
    SET @d = 67.89
     
    SELECT (@a / @b) * @b
    SELECT (@c / @d) * @d
     
    >>What are the timestamp and uniqueidentifier datatypes used for ?
     
    Timestamp is the most inadequately named datatype ever - the contents of a timestamp column have no relation whatsoever with current time. In SQL Server 2000, a new name (rowversion) was introduces. I believe (and hope!) that Microsoft intend to shift to using only the name rowversion and get rid of the name timestamp.
    If a table has a column with the rowversion (or timestamp) column, then that column will automatically be changed on every insert or update of the row. You can not set values to this column yourself. This is useful to implement optimistic locking: store the rowversion when reading data for display on the screen; when the user is finished making changes and wants to save them back to the DB, check if the rowversion is unchanged before updating the data. If it has changed, someone else has changed the data while the user was busy; frontend can take action (or ask user for action to take).
     
    The uniqueidentifier is used for storing globally unique identifiers (GUID) - a 16-byte value, usually represented in hexadecimal form, in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (e.g. 6F9619FF-8B86-D011-B42D-00C04FC964FF). They are designed for very specific scenario's where surrogate key values have to be generated at different locations. Typically only in replication scenario's.
     
    >>I couldn't find what I was looking for in Books Online, so I would be really grateful if you could also provide me with the relevant links for the information I am looking for in Books Online.
     
    Did you use Books Online on the Internet, or do you have them installed on your computer? In the first case, I recommend you to download the complete package and install them locally. I like the search capabilities of the "local install" Books Online version better than the search capabilities of the online version.

    --
    Hugo Kornelis, SQL Server MVP
    Thursday, February 02, 2006 8:36 PM
  • There appears to be quite alot of knowledge on this forum, I have 1 simple question I would like answered:

    How do I create a data record that auto increments? Or does that require additional coding?

    Thanks!

    Saturday, February 04, 2006 4:18 AM
  • 
    Hi owned,
     
    >>How do I create a data record that auto increments? Or does that require additional coding?
    Pick one of the numeric datatypes, then add the IDENTITY property.
     
    CREATE TABLE MyTable
                (KeyColumn int NOT NULL IDENTITY,
                 OtherCol varchar(20) NOT NULL,
                 PRIMARY KEY (KeyColumn),
                 UNIQUE (OtherCol)
                )
    go
    INSERT INTO MyTable (OtherCol)
    VALUES ('First')
    INSERT INTO MyTable (OtherCol)
    VALUES ('Second')
    SELECT * FROM MyTable
    go
    DROP TABLE MyTable
    go
     

    --
    Hugo Kornelis, SQL Server MVP
    Saturday, February 04, 2006 11:00 AM
  • Thanks Hugo,

    Although I couln't understand your examples since I don't know any programming ;-), that's allright. I got the info that I needed. You've been very helpful. And I've been refering to the Internet version of Books Online as I thought since the name was books 'Online', so the online version would be more comprehensive and would have more material. Anyway, 180 MB is a HUGE download size, so I don't think I'll be getting the offline version anytime soon.

    Monday, February 06, 2006 9:38 PM
  • Hi ! This is great forum for data type.

    I also have question:

    What is a good data type for email, password, Phone Number and ISBN number?

    Thanks!
    Saturday, April 15, 2006 5:22 PM
  • Best bet is to use VARCHAR with appropriate length for each.  There is no specific types for them in SQL.  All logics to make sure data entered is in correct format should be handled by your application (email format validation, password rule validation, etc.).

     

    Monday, April 17, 2006 7:22 PM
  • Another newbie joining the thread.  Two questions please.

     

    1. - I am trying to replicate a FoxPro 6.4 table in SQL Server Studio Express and am having trouble with their 'Memo' data type.  From my searches it appears that it would translate into NVARCHAR(MAX).  Is that correct?  (Of course it would be great if it could just be copied across, but FoxPro can't - bummer!)

     

    2. - Assuming it is - the next problem is that NVARCHAR(MAX) does not appear in the drop down list of available data types, as i am adding te column information.  How the heck can I add it to the drop down list?

    Wednesday, April 30, 2008 2:23 AM
  • Hi MigrationUser 1,

    As per your reply nvarchar requires 2n + 2 bytes whearas if you execute the bellow script

    Declare @name as nvarchar(20)
    set @name = "abc"
    select datalength(@name)

    The output is 6 and not 8. Can you clarify this behaviour.

    Thanks in advance.

    Vikram

    Monday, January 10, 2011 2:12 PM
  • I would expect that Memo in VFP should convert to varchar(max) (or nvarchar(max) if you need to store unicode data). Are you using SQL Server 2005+ ?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, January 10, 2011 2:31 PM
  • As per your reply nvarchar requires 2n + 2 bytes whearas if you execute the bellow script

    Declare @name as nvarchar(20)
    set @name = "abc"
    select datalength(@name)

    The output is 6 and not 8. Can you clarify this behaviour.

    The data length is 2n, but the variable itself uses two more bytes of system space to store the length.

    When calculating how it will fit on the page, use 2n + 2.

    Josh

     

    Tuesday, January 11, 2011 7:58 PM
  • I got this error message when i tried to convert the feild of nvarchar into image in order to store it picture.

    conversion from nvarchar to image is not supported on the connected database server.

    What do i need to do?...i think some application are runnig and using my server database because i installed them adn they are running..or do i add a new field then give it as image data type....delete the previous one...

    Regards,

    Hosseinel

    Saturday, April 21, 2012 2:30 PM