The choice of the datatype determines the kind of data that can be stored in the column and the maximum length of data that can be stored in the column. SQL Server provides different type of data. They are:
Numeric Data types:
SQL Server provides many ways to store numeric values, which provide flexibility in precision, range of values, and data storage size. Numeric type falls into various categories like int, smallint, tinyint, money, smallmoney, and bit. To store decimal values datatypes like float,real, decimal and numeric are use.
“Bigint” is the new datatype in SQL Server 2000 under this category.
Character Data type:
There are three valid datatypes for storing strings. They are:
Char: this is used for storing fixed-length strings. Columns defined as char will store blanks to fill out a fixed number of characters. The maximum length of a character column is 8000 bytes.
Varchar: this is used for storing variable length strings. Columns defined as varchar will truncate blanks to save space.
Text: this is used for storing of virtually unlimited size(upto 2 gigabytes of text per row.
Binary Data types:
These datatypes store strings consisting of binary values.i.e, haxadecima numbers instead of characters. They are:
Binary: Stores binary data of fixed lengh with a maximum lengh of 8,000 bytes
Varbinary: Stores variable lengh binary data with a maximum length of 8,000 bytes
Image: SQL Server provides a mechanism for storing binary data more than 8000 bytes using image datatypes. For Example, the photograph of the employees can be stored.
SQL Server enables to store date and time values. Columns using datetime or smalldatetime will store both date and time.
Unicode standard includes all the characters that are defined in the various character sets. Using Unicode data types, a column can store any character that is defined by the Unicode standard. Unicode data is stored using nchar, nvarchar, ntext datatypes.
Cursor,TimeStamp, Unique Identifier and Bit.
SQL Server 2000 introduces three new data types. bigint is an 8-byte integer type. sql_variant is a type that allows the storage of data values of different data types. table is a type that allows applications to store results temporarily for later use. It is supported for variables, and as the return type for user-defined functions
After a table is created, you can change many of the options that were defined for the table when it was originally created, including:
- Columns can be added, modified, or deleted. For example, the column name, length, data type, precision, scale, and nullability can all be changed, although some restrictions exist. For more information.
- PRIMARY KEY and FOREIGN KEY constraints can be added or deleted.
- UNIQUE and CHECK constraints and DEFAULT definitions (and objects) can be added or deleted.
- An identifier column can be added or deleted using the IDENTITY or ROWGUIDCOL property.