Bit
Integer data type 1, 0, or NULL.
Binary and VarBinary
Binary data types of either fixed-length (binary) or variable-length (varbinary).
binary [ ( n ) ] Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.
varbinary [ ( n ) ] Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for varbinary is binary varying.
Use binary when column data entries are consistent in size.
Use varbinary when column data entries are inconsistent in size.
Character Data
Character data consists of any combination of letters, symbols, and numeric characters. For example “rajanikanth”,’9777”
In Microsoft SQL Server 2000, character data is stored using the char, varchar, and text data types. Use varchar when the entries in a column vary in the number of characters they contain, but the length of any entry does not exceeds 8 kilobytes (KB). Use char when every entry for a column has the same fixed length (up to 8 KB). text data can be used to store ASCII characters longer than 8 KB.
To store international character data in SQL Server, use the nchar, nvarchar, and ntext data types.
Char[n]: Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. In SQL-92 synonym for char is character.
Varchar[(n)] : Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
Use char when the data values in a column are expected to be consistently close to the same size.
Use varchar when the data values in a column are expected to vary considerably in size.
Using char and varchar data
Varchar and char can store the data composed of
1)upper and lower case characters
2)numerals
3)special characters such as @,&,!.
Character constants must be enclosed in single quotation marks (') or double quotation marks ("). Enclosing a character constant in single quotation marks is recommended. Enclosing a character constant in double quotation marks is sometimes not allowed when the QUOTED IDENTIFIER option is set to ON.
Example : Transact-SQL example sets a character variable to a value:
DECLARE @MyCharVar CHAR(25)
SET @MyCharVar = 'india'
nchar(n) : Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes .in SQL-92 synonyms for nchar are national char and national character.
nvarchar(n) : Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
ntext : Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters.
Text: text is a non unique code data and with maximum length of 231-1 (2,147,483,647) characters.
Image: Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Bigint :Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.
Int : Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer
Small int : Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.
tinyint : Integer data from 0 through 255. Storage size is 1 byte.
Decimal :Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1
Money : Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit
Small money : Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
Float[(n)] : float [ ( n ) ] : Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53
Real : Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes.
Datetime : Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds.
Smalldatetime : Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.
Cursor : The operations that can reference variables and parameters having a cursor data type are:
• The DECLARE @local_variable and SET @local_variable statements.
• The OPEN, FETCH, CLOSE, and DEALLOCATE cursor statements.
• Stored procedure output parameters.
• The CURSOR_STATUS function.
Sqlvarient : A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.
Table: A special data type used to store a result set for later processing.
Timestamp : timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
Uniqueidentifier : A globally unique identifier (GUID).
A column or local variable of uniqueidentifier data type can be initialized to a value in two ways:
• Using the NEWID function.
• Converting from a string constant in the following 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 are allowed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators are allowed. All column constraints and properties except IDENTITY are allowed on the uniqueidentifier data type.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|