RSS

SQL Server 2008 Data Types !!

15 Oct

SQL-Server-2012

In SQL Server, each column, local variable, expression, and parameter has a related data type. 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.

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. User-defined types obtain their characteristics from the methods and operators of a class that you create by using one of the programming languages support by the .NET Framework.

Data Types:

  1. Bigint ||

    • Size = 8 bytes.
    • Range = -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).
    • Tips = The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.
  2. Binary ||
    • Size = Fixed Length.
    • Range = Binary(n) .. Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.
    • Tips = Binary data types of either fixed length or variable length.
  3. Bit ||
    • Size = 1 bit.
    • Range = An integer data type that can take a value of 1, 0, or NULL.
    • Tips = The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
  4. Char ||
    • Size = Fixed Length.
    • Range = Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.
    • Tips = Are string data types of either fixed length or variable length.
  5. Date ||
    • Size = 3 bytes, fixed.
    • Range = 0001-01-01 through 9999-12-31 (January 1, 1 A.D. through December 31, 9999 A.D.).
    • Accuracy = One day.
    • Default value = 1900-01-01.
    • Character length = 10 characters.
    • Default string literal format = YYYY-MM-DD.
  6. DateTime ||
    • Size = 8 bytes.
    • Date range = January 1, 1753, through December 31, 9999.
    • Time range = 00:00:00 through 23:59:59.997
    • Accuracy = Rounded to increments of .000, .003, or .007 seconds.
    • Character length = 19 positions minimum to 23 maximum.
    • Default value = 1900-01-01 00:00:00
  7. DateTime2 ||
    • Size = 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.
    • Date range = 0001-01-01 through 9999-12-31 (January 1,1 A.D. through December 31, 9999 A.D.).
    • Time range = 00:00:00 through 23:59:59.9999999
    • Accuracy = 100 nanoseconds.
    • Character length = 19 positions minimum (YYYY-MM-DD hh:mm:ss ) to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000).
    • Default value = 1900-01-01 00:00:00
    • Default string literal format = YYYY-MM-DD hh:mm:ss[.fractional seconds].
  8. DateTimeOffset ||
    • Size = 10 bytes, fixed is the default with the default of 100ns fractional second precision.
    • Date range = 0001-01-01 through 9999-12-31 (January 1,1 A.D. through December 31, 9999 A.D.).
    • Time range = 00:00:00 through 23:59:59.9999999
    • Accuracy = 100 nanoseconds.
    • Character length = 26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
    • Default value = 1900-01-01 00:00:00 00:00
    • Default string literal format = YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
  9. Decimal ||
    • Size = 19 bytes.
    • Range = Fixed precision and scale numbers. When maximum precision is used, valid values are from – 10^38 +1 through 10^38 – 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
    • Tips=
      • p (precision) = The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
      • s (scale) = The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
  10. Float ||
    • Size = 4 to 8 bytes. Depends on the value of n.
    • Range = – 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
    • Tips = Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
  11. Geography ||
    • The geography spatial data type, geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.
    • The geography type is predefined and available in each database. You can create table columns of type geography and operate on geography data in the same manner as you would use other system-supplied types.
  12. Geometry ||
    • Microsoft SQL Server supports a set of methods for the geometry spatial data type. These methods include methods on geometry that are defined by the Open Geospatial Consortium (OGC) standard and a set of Microsoft extensions to that standard.
    • The error tolerance for the geometry methods can be as large as 1.0e-7 * extents. The extents refer to the approximate maximal distance between points of the geometry object.
    • The geometry type is predefined and available in each database. You can create table columns of type geometry and operate on geometry data in the same manner as you would use other CLR types. Can be used in persisted and non-persisted computed columns.
  13. HierarchyID ||
    • The built-in hierarchyid data type makes it easier to store and query hierarchical data. hierarchyid is optimized for representing trees, which are the most common type of hierarchical data.
    • Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Hierarchical relationships exist where one item of data is the parent of another item. Examples of the hierarchical data that is commonly stored in databases include the following:
      • An organizational structure.
      • A file system.
      • A set of tasks in a project.
      • A taxonomy of language terms.
      • A graph of links between Web pages.
    • Use hierarchyid as a data type to create tables with a hierarchical structure, or to describe the hierarchical structure of data that is stored in another location. Use the hierarchyid functions in Transact-SQL to query and manage hierarchical data.
    • Tips = for more info see this (HierarchyID Data Type in SQL Server 2008)
  14. Image ||
    • Size = Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
  15. Int ||
    • Size = 4 bytes.
    • Range = -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).
    • Tips = The int data type is the primary integer data type in SQL Server.
  16. Money ||
    • Size = 8 bytes.
    • Range = -922,337,203,685,477.5808 to 922,337,203,685,477.5807
    • Tips = Data type that represents monetary or currency values. The money data type is accurate to a ten-thousandth of the monetary units that they represent.
  17. NChar ||
    • Size = Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000 characters. The storage size is two times n bytes (n × 2). When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character.
    • Tips = String data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
  18. NText ||
    • Variable-length Unicode data with a maximum string length of 2^30 – 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered. The ISO synonym for ntext is national text.
  19. Numeric ||
    • Size = 19 bytes.
    • Range = Fixed precision and scale numbers. When maximum precision is used, valid values are from – 10^38 +1 through 10^38 – 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
    • Tips=
      • p (precision) = The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
      • s (scale) = The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
  20. NVarChar ||
    • Size = Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.
    • Tips = String data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
  21. Real ||
    • Size = 4 bytes.
    1. Range = – 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38
    1. Tips = Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
  22. SmallDateTime ||
    • Size = 4 bytes, fixed.
    • Date range1900-01-01 through 2079-06-06 (January 1, 1900, through June 6, 2079).
    • Time range = 00:00:00 through 23:59:59
    • Accuracy = One minute
    • Character length = 19 positions maximum
    • Default value = 1900-01-01 00:00:00
  23. SmallInt ||
    • Size = 2 bytes.
    • Range = -2^15 (-32,768) to 2^15-1 (32,767)
  24. SmallMoney ||
    1. Size = 4 bytes.
    2. Range = – 214,748.3648 to 214,748.3647
    3. Tips = The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.
    4. You can know more about currencies from Here.
  25. Sql_Variant ||
    1. Size = The maximum length of the actual base type value is 8,000 bytes.
    2. Tips =
      1. A data type that stores values of various SQL Server-supported data types.
      2. sql_variant can be used in columns, parameters, variables, and the return values of user-defined functions. sql_variant enables these database objects to support values of other data types.
      3. A column of type sql_variant may contain rows of different data types. For example, a column defined as sql_variant can store int, binary, and char values.
      4. A sql_variant data type must first be cast to its base data type value before participating in operations such as addition and subtraction.
      5. sql_variant can be assigned a default value. This data type can also have NULL as its underlying value, but the NULL values will not have an associated base type. Also, sql_variant cannot have another sql_variant as its base type.
      6. A unique, primary, or foreign key may include columns of type sql_variant, but the total length of the data values that make up the key of a specific row should not be more than the maximum length of an index. This is 900 bytes.
      7. A table can have any number of sql_variant columns.
  26. Text ||
    1. Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
  27. Time ||
    • Size = 5 bytes, fixed, is the default with the default of 100ns fractional second precision.
    • Time range = 00:00:00.0000000 through 23:59:59.9999999
    • Accuracy = 100 nanoseconds.
    • Character length = 8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn)
    • Default value = 00:00:00
  28. TimeStamp ||
    • Size = 8 bytes.
    • Tips=
      • Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.
      • Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. This property makes a rowversion column a poor candidate for keys, especially primary keys. Any update made to the row changes the rowversion value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.
  29. TinyInt ||
    • Size = 1 byte.
    • Range = 0 to 255.
  30. UniqueIdentifier ||
    • Size = 16 bytes.
    • Tips =
      • Is a 16-byte GUID.
      • A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
        • By using the NEWID function.
        • By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.
      • Comparison operators can be used with uniqueidentifier values. However, ordering is not implemented by comparing the bit patterns of the two values. The only operations that can be performed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be used on the uniqueidentifier data type.
      • Merge replication and transactional replication with updating subscriptions use uniqueidentifier columns to guarantee that rows are uniquely identified across multiple copies of the table.
  31. VarBinary ||
    • Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.
  32. VarChar ||
    • Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying.
  33. XML ||
    • Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type.
    • The stored representation of xml data type instances cannot exceed 2 gigabytes (GB) in size.
 
Leave a comment

Posted by on October 15, 2012 in MS SQL Server

 

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: