Binary Data

 Binary data is stored in a set of four data types.

                             Binary Data Types

 DATA TYPE           RANGE OF VALUES             STORAGE SPACE

 BIT                                   Null, 0, and 1                                           1 bit

 BINARY                         Fixed-length binary data                      Up to 8,000 bytes

 VARBINARY                  Variable-length binary                         Up to 8,000 bytes

XML Data Type

What is XML?

XML stands for Extensible Markup Language like HTML was designed to carry data, not to display data.

XML Data Type

 XML data type allows you to store and manipulate Extensible Markup Language (XML) documents natively. When storing XML documents, you are limited to a maximum of 2 GB, as well as a maximum of 128 levels within a document. Although you could store an XML  document in a character column, the XML data type natively understands the structure of XML data and the meaning of XML tags within the document. 

 Because the XML data type natively understands an XML structure, you can apply  additional validation to the XML column, which restricts the documents that can be stored 

based on one or more XML schemas. 

       XML schemas are stored within SQL Server in a structure called a schema collection. Schema collections can contain one or more XML schemas. When a schema collection is applied to an XML column, the only documents allowed to be stored within the XML column must fi rst validate to the associated XML schema collection. 

Spatial Data Types

 SQL Server 2008 supports two data types to store spatial data.

 Geometry
Geography

Geometric Data

 Geometric data is based on Euclidean geometry and is used to store points, lines, curves, and polygons. 

Geographic Data

Geographic data is based on an ellipsoid and is used to store data such as latitudes and longitudes.

SPECIAL COLUMNS.

You define spatial columns in a table using either the GEOMETRY or GEOGRAPHY data 

types. When values are stored in a spatial column, you have to create an instance using one 

of several spatial functions specific to the type of data being stored. A GEOMETRY column 

can contain one of seven different geometric objects with each coordinate in the definition 

separated by a space.

Geometry Data Type Definitions 

 Multi Polygon

 Contains the coordinates for multiple Polygons.

 Point

 Has x and y coordinates, with optional elevation and measure values.

 Line String

 A series of points that defines the start, end, and any bends in the line, with optional elevation and measure values.

 Polygon

 A surface defined as a sequence of points that defines an exterior boundary, along with zero or more interior rings. A polygon has at least three distinct points.

 Geometry Collection 

Contains one or more instances of other geometry shapes, such as a Point and a Line String.

MultiPoint

Contains the coordinates of multiple Points.
 Multi Line String

 Contains the coordinates of multiple Line Strings.

HIERARCHY ID Data Type

 The HIERARCHY ID data type is used to organize hierarchical data, such as organization charts, bills of materials, and flowcharts. The HIERARCHY ID stores a position within a tree hierarchy. By employing a HIERARCHYID, you can quickly locate nodes within a hierarchy as well as move data between nodes within the structure. 

Example

REFRENCES
http://msdn.microsoft.com/en-us//library/ms188362.aspx

http://technet.microsoft.com/en-us/library/ms179366(v=sql.105).aspx

DATA TYPES

The choice of data type is also the most fundamental performance decision you will ever make for a database. You need to select a data type that can store the data required by the business, but your data type should not consume a single byte of storage more than necessary.

 Numeric Data Types

Nine numeric data types ship with SQL Server 2008, and they are used to store integer,monetary, and decimal-based numbers.

 

Decimal Data Types

 Decimal data types have two parameter.

Precision 

The precision indicates the total number of digits that can be stored both to the left and to the right of the decimal.

Scale. 

The scale indicates the maximum number of digits to the right of the decimal point.

     For example, assigning a column the DECIMAL(8,3) data type allows SQL Server to store a total of eight digits in the column, with three of the digits to the right of the decimal point or values

 between -99999.999 and 99999.999.

 Decimal and Numeric Data Type Storage

 PRECISION                                      STORAGE SPACE

  1 to 9                                                    5 bytes

 10 to 19                                               9 bytes

 20 to 28                                               13 bytes

 29 to 38                                              17 bytes

  Character Data Types

SQL Server 2008 has four data types for storing character data, with the choice of which one to use depending upon whether you have fixed- or variable-length values and whether you

want to store Unicode or non-Unicode data....

DATA TYPE                     STORAGE SPACE

 CHAR(n)                          Non-Unicode, 1 byte per character
                                         defined by n, up to a maximum of

8,000 bytes.

 VARCHAR(n)                    Non-Unicode, 1 byte per character                                                      stored up to a maximum of

8,000 bytes

 NCHAR(n)                       Unicode, 2 bytes per character defi ned                                              by n, up to a maximum of

                                           4,000 bytes

 NVARCHAR(n)                Unicode, 2 bytes per character stored                                                 up to a maximum of 4,000 bytes

 Date and Time Data

 One of the biggest recent advances in SQL Server greatly expands the data types to store dates and times.

Small date time and datetime  data types store a date and a time together as a single value and have existed for several versions of SQL Server. The range of values stored for a DATETIME data type was rather limited for historical applications, so SQL Server 2008 introduced a DATETIME2 data type that provides better precision than either SMALLDATETIME or DATETIME, along with a much larger range of values.

 The DATETIMEOFFSET allows you to store a time zone for applications that need to localize dates and times.