Built-in data types SQL Server 2000 ships with 27 built-in (system) data types
| Data Types | Description |
|---|---|
| bigint | Integer data from -2^63 through 2^63-1 |
| int | Integer data from -2^31 through 2^31 – 1 |
| smallint | Integer data from -2^15 through 2^15 – 1 |
| tinyint | Integer data from 0 through 255 |
| bit | Integer data with either a 1 or 0 value |
| decimal | Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 |
| numeric | Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 |
| money | Monetary data values from -2^63 through 2^63 – 1 |
| smallmoney | Monetary data values from -214,748.3648 through +214,748.3647 |
| float | Floating precision number data from -1.79E + 308 through 1.79E + 308 |
| real | Floating precision number data from -3.40E + 38 through 3.40E + 38 |
| datetime | Date and time data from January 1, 1753, through December 31, 9999,with an accuracy of 3.33 milliseconds |
| smalldatetime | Date and time data from January 1, 1900, through June 6, 2079,with an accuracy of one minute |
| char | Fixed-length character data with a maximum length of 8,000 characters |
| varchar | Variable-length data with a maximum of 8,000 characters |
| text | Variable-length data with a maximum length of 2^31 – 1 characters |
| nchar | Fixed-length Unicode data with a maximum length of 4,000 characters |
| nvarchar | Variable-length Unicode data with a maximum length of 4,000 characters |
| ntext | Variable-length Unicode data with a maximum length of 2^30 – 1 characters |
| binary | Fixed-length binary data with a maximum length of 8,000 bytes |
| varbinary | Variable-length binary data with a maximum length of 8,000 bytes |
| image | Variable-length binary data with a maximum length of 2^31 – 1 bytes |
| cursor | A reference to a cursor |
| sql_variant | A data type that stores values of various data types,except text, ntext, timestamp, and sql_variant |
| table | A special data type used to store a result set for later processing |
| timestamp | A database-wide unique number that gets updated every timea row gets updated |
| uniqueidentifier | A globally unique identifier |
User-defined data types
SQL Server 2000 supports user-defined data types too. User-defined data types provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. Using user-defined data type can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type. The user-defined data types are based on the system data types and can be used to predefine several attributes of a column, such as its data type, length, and whether it supports NULL values. To create a user-defined data type, you can use the sp_addtype system stored procedure or you could add one using the Enterprise Manager. When you create a user-defined data type, you should specify the following three properties:
- Data type’s name.
- Built-in data type upon which the new data type is based.
- Whether it can contain NULL values.
The following example creates a user-defined data type based on money data type named cursale that cannot be NULL:
EXEC sp_addtype cursale, money, 'NOT NULL' GO |
Both system and user-defined data types are used to enforce data integrity. It is very important that we put forth a lot of effort while designing tables: the better you design your tables, the more time you can work without any performance problems. In an ideal case, you never will update the structure of your tables.
Home