Data Types in SQL Server

Discussion in 'SQL Server' started by MinalS, Mar 25, 2016.

  1. There are several data types defined in SQL. It is necessary for the user to understand different data types available in SQL

    Numeric data types


    1. int: It contains range of values from -2,147,483,648 to 2,147,483,647. An integer data is saved. It requires 4 bytes of memory.
    2. bigint: The values are in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,907. Integer data is stored. It requires 8 bytes of memory
    3. smallint: The values are in between -32,768 to 32,767. Integer data is added. It needs to 2 bytes of memory.
    4. tinyint: The range of value is 0 to 255. It needs one byte of memory.
    5. bit: The value are in range 0 to 1.
    6. decimal: The value is between -10^38+1 to 10^38-1. The values with a fixed precision and scale are saved. It needs 5 to 17 bytes of memory.
    7. numeric: The value is between -10^38+1 to 10^38-1. The values with a fixed precision and scale are saved. It needs 5 to 17 bytes of memory.
    8. smallmoney: It needs -214,748.3648 to 214,748.3467. It is used for saving monetary data. It needs 8 bytes of memory.
    9. money: The range is -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Monetary data is stored. 4 bytes of memory are needed.
    10. real: The value is between -3.40E+38 to -1.18E-38, 0 and 1.18E-38 to 3.40E+38. It saves floating precision number. 4 bytes of memory are required.
    11. float: The value in range -1.79E+308 to 1.79E+308. Floating precision data is stored. 4 to 8 bytes of memory are required.

    Date and Time types


    1. date: The range is January 1,0001 to December 31,9999 A.D. The Date data is stored. 3 bytes of memory is needed.
    2. time: Values between 00:00:00:0000000 to 23:59:59:9999999. Time data is added. It stores 5 bytes of memory.
    3. smalldatetime: The values are in the range January 1,1900 to June 6,2079. The Date and time values are added to the memory. It requires 4 bytes of memory.
    4. datetime: The values are in range January 1, 1753 to December 31, 9999. The date and time values are saved. The 8 bytes of data are required.
    5. datetime2: The range in between January 1, 0001 to December 31, 9999. Date and time data is added. It needs 6 to 8 bytes of memory.
    6. datetimeoffset: The range in between January 1, 0001 to December 31, 9999. The date and time values related to the specific time zone are added. It needs 10 bytes of memory.

    String Data types


    1. char: Maximum of 8000 characters are saved. It stores fixed length non - Unicode characters.
    2. varchar: Maximum of 8000 characters are saved. It stores variable length non - Unicode characters.
    3. varchar ( max ): The maximum of 231 characters are saved. It saves variable length non – Unicode character.
    4. text: It saves variable length non – Unicode data. The maximum length of 2,147,483,647 characters is stored.

    Unicode character string types


    1. nvarchar: It has maximum length of 4000 characters. It stores variable length Unicode data. The data of n( length of data ) * 2 + 2 is saved.
    2. nchar: Maximum of 4000 characters are saved. It has a fixed length of Unicode data. The data of length n ( length of data ) * 2.
    3. ntext: The length of 2^30-1 characters can be used. It provides variable length of Unicode data. It stores data of variable size
    4. nvarchar ( max ): The maximum length of 231 characters are used.

    Binary Data types


    1. varbinary: Maximum of 8000 bytes are used. It saves variable length of binary data. The data of length n ( length of data ) + 2 is saved.
    2. binary: Maximum of 8000 bytes can be used. It stored fixed length of binary data.
    3. image: The maximum of 2,147,483,647 bytes are stored. It stored variable length binary data.
    4. varbinary ( max ): The maximum of 231 bytes are stored. It stores variable length of binary data.

    Miscellaneous data types


    1. table: It is used for saving the result set. The set is used for processing later. The values that are present in the table are temporary row set which are returned as table valued function. Data of variable size is stored.
    2. xml: It stores xml type variables and instances. Only Xml values are saved. The data can be of variable sizes.
    3. cursor: it stores stored procedures and variables OUTPUT parameters. The reference of cursor is saved. Data of variable size is stored.
    4. sql_variant: Maximum length of 8016 bytes can be used. Various data types ntext, image, timestamp, and sql_variant are used.
    5. uniqueidentifier: It is a 16 byte GUID. It stores data which uniquely identifies the rows in a table across the database.
    6. timestamp: The maximum of 8 bytes are saved. On every occurrence of row update due to timestamp, unique number is updated. 8 bytes of data can be stored.
    7. geometry: The two dimensional data is used. The data can be coordinates defining polygons, lines, points, etc.
    8. geography: The ellipsoidal data is used. The data can be latitude and longitude, coordinates defining polygons, lines, points, etc.
    9. hierarchyid: The hierarchical data is used. It contains file system, organizational structure.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice