Understanding SQL Server data types

DAY -5

Understanding SQL Server data types

SQL Server contains four distinct data type categories

1.      Numeric

2.      Data and Time

3.      Strings

4.      Other

Each of the four categories contains subcategories. All columns within a table, declared variables, and parameters must have a corresponding data type. A data type simply specifies what type of data can be placed into the object (column, variable, parameter, and so on). Database integrity depends heavily on appropriately scoped data types; therefore, you should not always depend or rely on an application to enforce data type usage.

Numeric data types

The numeric data type has two subcategories

1.      Exact

2.      Approximate

Exact data types fit within a finite range of numbers. The approximate subcategory is similar to the decimal and numeric data types in that one accepts a precision value, which is float. The other does not accept a precision value; instead, it can store up to seven digits, which includes digits on both sides of the decimal.

String data types

The string data type contains three subcategories

1.      Character

2.      Unicode

3.      Binary

Each contains three specific data types. The data types are similar in that each subcategory contains a fixed-length data type, a variable-length data type, and a data type that has been deprecated.

The character string subcategory will store non-Unicode data.

Ø  char(n) Fixed-length string data type with a string length between 1 and 8,000.

Ø  varchar(n) Variable-length string data type that can store up to 2 GB of data.

Ø  text Deprecated data type. Replace it with a varchar(max).

The Unicode string subcategory will store both Unicode and non-Unicode data.

Ø  nchar(n) Fixed-length string data type with a string length between 1 and 4,000.

Ø  nvarchar(n) Variable-length string data type that can store up to 2 GB of data.

Ø  ntext Deprecated data type. Replace it with nvarchar(max).

The binary string subcategory will store binary data.

Ø  binary(n) Fixed-length binary data type with a string length between 1 and 8,000.

Ø  varbinary(n) Variable-length binary data type with a string length up to 2 GB.

Ø  image Deprecated data type. Replace with varbinary(max).

Date and time data types

Date and time data types are used widely in SQL Server databases. They offer the convenience of storing the date and time in various ways. There are six date and time data types.

Ø  time(n) This data type stores the time of day without time-zone awareness based on a 24-hour clock.

Ø  date This data type stores a date value between 01-01-01 and 12-31-9999.

Ø  smalldatetime This data type stores a date and time value. The value of the date is between 1/1/1900 and 6/6/2079. The time precision is down to seconds. A value of 4/1/2012 11:15:04 can be stored using this data type.

Ø  datetime This data type is similar to smalldatetime, but it offers a larger date range and a higher level of precision with regard to time.

Other data types

SQL Server includes several other data types. Since the data types are typically used for advanced operations.

Ø  Cursor: - A temporary copy of data that will be used for recursive or iterative processes.

Ø  Row version: - This data type automatically generates an 8-byte value similar to 0x0000000000000001.rowversion replaces the timestamp data type, which has been deprecated. This data type is typically used to detect changes in data.

Ø  Hierarchyid: - This is a positional data type. It represents a position in a hierarchy. hierarchyid is used to organize data such as a bill of materials and organizational charts.

Ø  sql_variant:- This is the chameleon of data types. sql_variant can assume the identity of just about any data type in the list of SQL Server data types. Prior to performing any types of op­erations on it, you must convert it to the respective data type.

Ø  Xml You can store actual XML data using this data type.

Ø  Geospatial: - SQL Server supports two geospatial data types: GEOGRAPHY and GEOMETRY. GEOGRAPHY represents data in a round-earth coordinate system. GEOMETRY is a flat or planar data type in which you can store points, lines, and other geometric figures.

Ø  File stream: - This data type allows you to store common unstructured data such as documents and images. SQL Server has been coupled with the NTFS file system, allowing the storage of varbinary (max) on the file system.

Thanks & Regards  
Srikanth Goud-  Virtualization Administrator