SQL Server Performance

What is the internal representation of all the datatypes.

Discussion in 'SQL Server 2008 General Developer Questions' started by baburk, Jul 14, 2010.

  1. baburk New Member

    Hi all,
    What is the internal representation of all the datatypes.
    Babu Kumaraasamy
  2. satya Moderator

    Can you explain further about what you mean by internal representation?
    As far as the SQL is concerned a data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
    As BOL too: SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are based on the system-supplied data types.
  3. baburk New Member

    I read that float is stored as binary and decimal, numeric is stored as number itself. So I want to learn how the value of each datatype is stored(Structured).
  4. ashish287 New Member

    i think to understand it more clearly you should read data structure and c language.
    It gives good information about data type and their design.
    Not sure how this forum can help you in this as its related to your own reading.
  5. satya Moderator

    Ok that gives bit more information, as you are aware SQL Server has variety of datatypes and more options you have that are confusing & misunderstanding arise from data type limitations rather than functionality.
    Here is the table list that can clarify few of your doubts which is extracted from SQL Server Books Online:
    Character data types

    Data TypeLengthStorage SizeMax CharactersUnicode
    charFixedAlways n bytes8,000No; each character requires 1 byte
    varcharVariableActual length of entry in bytes8,000No; each character requires 1 byte
    ncharFixedTwice n bytes4,000Yes; each character requires 2 bytes
    nvarcharVariableTwice actual length of entry in bytes4,000Yes; each character requires 2 bytes

    Integer data types

    Data typeMinimum valueMaximum valueStorage size
    tinyint02551 byte
    smallint-32,76832,7672 bytes
    int-2,147,483,6482,147,483,6744 bytes
    bigint-9,223,372,036,854,775,8089,223,372,036,854,775,8078 bytes
    Precision storage requirements

    Total characters (precision)Storage size
    1 - 95 bytes
    10 - 199 bytes
    20 - 2813 bytes
    29 - 3817 bytes
    Float and real data type restrictions

    Data typenMinimum ValueMaximum valuePrecisionStorage size
    float(n)1 - 24-1.79E + 3081.79 + 3087 digits4 bytes
    25 - 53-1.79E + 3081.79E + 30815 digits8 bytes
    realn/a-3.40E + 383.40E + 387 digits4 bytes

    Smalldatetime and datetime restrictions

    Data typeMinimum valueMaximum valueAccuracyStorage size
    smalldatetimeJanuary 1, 1900June 6, 2079Up to a minute4 bytes (the first 2 bytes store the date; the second 2 bytes store the time)
    datetimeJanuary 1, 1753December 31, 9999One three-hundredth of a second8 bytes (the first 4 bytes store the date; the second 4 bytes store the time)
    Smallmoney and money restrictions

    Data typeMinimum valueMaximum valueStorage size
    smallmoney-214,748.3648214,748,36474 bytes
    money-922,337,203,685,477.5808922,337,203.685,477.58078 bytes

    In addition to the above SQL Server 2008 gets new data types, date/time/datetime2/datetimeoffset/hierarcyid/spatial, for more information on these types BOL is your best friend http://sqlserver-qa.net/blogs/sql2008/archive/2009/02/04/5391.aspx fyi.

Share This Page