Sql Server material 1

Data Types

In a Database, 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, data and time data, binary strings, and so on.

Integer Types: To hold the Integer values it provides with tinyint, smallint, int and bigint data types with sizes 1, 2, 4 and 8 bytes respectively.

Boolean Type: To hold the Boolean values it provides with bit data type that can take a value of 1, 0, or NULL.

Note: The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Decimal Types: To hold the decimal values it provides with the following types:

            -decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

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.

Storage sizes of Decimal and Numeric types vary, based on the precision.

Precision	Storage bytes
1 – 9	5
10-19	9
20-28	13
29-38	17

Note: numeric is functionally equivalent to decimal.

 

-float [ ( n ) ] and real

-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. Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

n value	Precision	Storage size
1-24	7 digits	4 bytes
25-53	15 digits	8 bytes

Monetary or Currency Types: To hold the Currency values it provides with the following types which takes a scale of 4 by default:

           

money	-922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
smallmoney	- 214,748.3648 to 214,748.3647	4 bytes

Date and Time Values: To hold the Date and Time values of a day it provides with the following types:

           

Data type	Range	Accuracy
datetime	January 1, 1753, through December 31, 9999	3.33 milliseconds
smalldatetime	January 1, 1900, through June 6, 2079	1 minute

Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
The smalldatetime data type stores dates and times of day with less precision than datetime. The Database Engine stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight.
String Values: To hold the string values it provides with the following types:

char [ ( n ) ]

Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes.

varchar [ ( n | max ) ]

Variable-length, non-Unicode character 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 data entered + 2 bytes.

text

It was equal to varchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varchar(max) instead.

Unicode Data types for storing Multilingual Characters are nchar, nvarchar and ntext where n stands for national.

nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes.

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes.

ntext

It was equal to nvarchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use nvarchar(max) instead.

Binary Values: To hold the binary values likes images, audio clips and video clips we use the following types.

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.

varbinary [ ( n | max) ]

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.

Image

It was equal to varbinary(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varbinary(max) instead.

• Use char, nchar, binary when the sizes of the column data entries are consistent.
• Use varchar, nvarchar, varbinary when the sizes of the column data entries vary considerably.
• Use varchar(max), nvarchar(max), varbinary(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

Other Types: Apart from the above it provides some additional types like -

timestamp: Is a data type that exposes automatically generated, unique binary numbers within a database. The storage size is 8 bytes. You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.

Uniqueidentifier: Is a 16-byte GUID which is initialized by using the newid() function or converting a string constant in the form of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx which is used to guarantee that rows are uniquely identified across multiple copies of the table.

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.

Next >>>