Tuesday, January 24, 2012

Description about SQL Server Data Types

SQL Server Data Types:Remove Confusion and Get Detailed Description

"SQL Server has a variety of data types, and when we want to implementation, 90% we get confused  that which one should be use at what time. Most misunderstandings arise from data type limitations rather than functionality. So dear friends I want to discuss you much more for the solution of this common problem"
  •  Differentiate char, varchar, nchar, and nvarchar:

Data Type Length Storage Size Max Characters Unicode
char Fixed Always n bytes 8,000 No; each character requires 1 byte
varchar Variable Actual length of entry in bytes 8,000 No; each character requires 1 byte
nchar Fixed Twice n bytes 4,000 Yes; each character requires 2 bytes
nvarchar Variable Twice actual length of entry in bytes 4,000 Yes; each character requires 2 bytes

Few general rules:
  1. Don’t use nchar or nvarchar unless you truly need it. (Unicode provides a unique number for up to 65,536 characters. ANSI, the one most of us are most familiar with, has only 256.) Unless you’re working with an international application, you probably don’t need a Unicode data type.
  2. Use the smallest data type necessary, but make sure it can accommodate the largest possible value.
  3. Use a fixed-length data type when the values are mostly about the same size.
  4. Use a variable length when the values vary a lot in size.
    •  Differentiate tinyint, smallint, int, bigint:

    Data type Minimum value Maximum value Storage size
    tinyint 0 255 1 byte
    smallint -32,768 32,767 2 bytes
    int -2,147,483,648 2,147,483,674 4 bytes
    bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807 8 bytes
    Note: Always use the smallest integer data type that accommodates the largest possible value.
    •  Differentiate numeric and decimal:

    There’s no difference between the numeric and decimal data types. Use them interchangeably or use one or the other to store integer and floating-point numbers scaled from 1 to 38 places, inclusive of both sides of a decimal. Use this data type when you need to control the accuracy of your calculations in terms of the number of decimal digits.
    Total characters (precision) Storage size
    1 - 9 5 bytes
    10 - 19 9 bytes
    20 - 28 13 bytes
    29 - 38 17 bytes
    •  Differentiate float and real:

    The only differences between float and real are their minimum and maximum values and their required storage.
    Data type n Minimum Value Maximum value Precision Storage size
    float(n) 1 - 24 -1.79E + 308 1.79 + 308 7 digits 4 bytes
    25 - 53 -1.79E + 308 1.79E + 308 15 digits 8 bytes
    real n/a -3.40E + 38 3.40E + 38 7 digits 4 bytes
    The real data type is the same as float(24) — a floating data type with 24 digits to the right of the decimal point.
    • Differentiate Smalldatetime and datetime:

    Both smalldatetime and datetime store a combination date and time value, but the minimum and maximum values, accuracy, and storage size are different.
    Data type Minimum value Maximum value Accuracy Storage size
    smalldatetime January 1, 1900 June 6, 2079 Up to a minute 4 bytes (the first 2 bytes store the date; the second 2 bytes store the time)
    datetime January 1, 1753 December 31, 9999 One three-hundredth of a second 8 bytes (the first 4 bytes store the date; the second 4 bytes store the time)
    •  Differentiate smallmoney and money:

    Use both smallmoney and money to store currency values. However, the minimum and maximum values for both differ. Both data types are accurate up to ten-thousandths of a monetary unit.
    Data type Minimum value Maximum value Storage size
    smallmoney -214,748.3648 214,748,3647 4 bytes
    money -922,337,203,685,477.5808 922,337,203.685,477.5807 8 bytes
    • Differentiate  bit data type(boolean):

    SQL Server doesn’t have a Boolean data type, at least not by that name. To store True/False, Yes/No, and On/Off values, use the bit data type. It accepts only three values: 0, 1, and NULL. (NULL is supported by SQL Server 7.0 and later.)
    •  Define text, ntext, and image:

    SQL Server is phasing out text, ntext, and image. There’s no way to know how long SQL Server will support the older data types. Upgrade legacy applications to varchar, nvarchar, and varbinary.
    • How do I assign a cursor or table data type?

    You don’t, at least not in the traditional manner. You don’t assign these data types to a column. You can use cursor and table only as variables:
    1. The cursor data type allows you to return a cursor from a stored procedure or store a cursor as a variable.
    2. The table data type returns a table from a stored procedure or stores a table as a variable for later processing.
      • SQL Server 2008 new data types:

      1. date stores only date values with a range of 0001-01-01 through 9999-12-31.
      2. time stores only time values with a range of 00:00:00.0000000 through 23:59:59.9999999.
      3. datetime2 has a larger year and second range.
      4. datetimeoffset lets you consider times in different zones.
      5. hierarchyid constructs relationships among data elements within a table, so you can represent a position in a hierarchy.
      6. spatial identifies geographical locations and shapes — landmarks, roads, and so on.


        Twitter Delicious Facebook Digg Stumbleupon Favorites More

        Powered by Code Imagine