Types of Data

The main purpose, of course, for maintaining a SQL Server is to store data. All the previously discussed tools and objects simply make it easier to facilitate the main goal, data storage. Data maintained by the server falls into three main categories:

• Object definitions

• Metadata

• User application data

Object Definitions

We have discussed most of the actual object definitions themselves, and if you look into the master database, you can find the storage area for these definitions. Tables prefixed with "sys" store object definitions. There are many system tables, but the most commonly accessed and useful for development purposes are these:

• Common System Tables in Every Database (Including Master)

  • syscolumns: Contains a row for every column in every table, for every view, and for each parameter in a stored procedure.

  • sysindexes: Contains a row for each index and table in the database.

  • syscomments: Contains entries for each view, rule, default, trigger, constraint, and stored procedure. The text column contains the original SQL definition statements.

  • sysobjects: Contains a row for each object created within a database. In tempdb only, this table includes a row for each temporary object.

  • systypes: Contains a row for each system-supplied data type and each user-defined data type.

  • sysusers: Contains a row for each user or role in the database.

• Common System Tables Additionally Found in Master Database Only

  • syslogins: Contains a row for each login.

  • sysmessages: Contains a row for each system error or warning that can be displayed to the user.

  • sysdatabases: Contains a row for each database on the server.

  • sysprocesses: Holds information about processes running on the server.

  • sysremotelogins: Contains a row for each remote user allowed to call remote stored procedures on the server.

  • sysservers: Contains a row for each server that the current server can access as an OLE DB data source.

Don't ever delete any elements from the syscomments table. To hide or encrypt any definitions of objects, use the WITH ENCRYPTION clause during the creation of the object.

System tables can be queried like any other table, and in doing so you can obtain valuable information about the objects stored on a server. You can also develop applications that can aid in the development of data or for performing data conversions from one application to another.

Metadata

Metadata is arguably just another type of system data. Metadata differs in that it is statistical information, or data about data. Metadata describes the structure and meaning of data, as well as the structure and meaning of applications and processes. Metadata is abstract and has context.

Metadata study and storage is a major topic in itself and is beyond the context of the exam and consequently this book. If you would like more information on the topic, refer to Meta Data Services in SQL Server Books Online.

User Data

The most prevalent type of data stored on the server is, of course, user data. This is the data that makes up the information used in our business applications. Data is physically stored on the server inside large data files. Changes to the data first pass through log files before being placed by the server into these data files. These aspects are discussed further in Chapters 3 and 4.

Data in any form is stored on the server in one of a number of data types. Traditionally, data is divided into three basic categories: numeric, alphanumeric or character, and binary. Other data types exist, however, and several require special handling in applications and design considerations. Assigning a data type to an object defines four attributes of the object:

• The kind of data contained by the object

• The length of the data stored

• The precision of numeric data types

• The scale of numeric data types

In selecting an appropriate data type, you take into account the application, usage of the data, and future trends. In numeric data you must select a type that is large enough to store the data. In character data types you want to be careful not to waste storage unnecessarily by configuring the size of the data to be larger than necessary.

Numeric Data Types

Numeric data can be defined as one of the integer data types for whole-number storage or a type that accommodates decimal-point storage for real numbers. The bigint data type is an 8-byte integer type that is new to SQL Server 2000 and allows for the storage of very large integer numbers. Other integer data types support various smaller integers. The int data type uses 4 bytes of storage, the smallint data type uses 2 bytes, and the smallest integer data type, tinyint, uses only a single byte, supporting values from 0 through 255.

Decimal and numeric are functionally the same data type. They allow for the storage of a fixed precision and scale numeric data from 10³⁸+1 through 10³⁸1. Depending on the precision chosen, this data type uses from 5 to 17 bytes for the storage of values, as summarized in Table 1.1.

Table 1.1. Precision of Numeric Data

Decimal and Numeric Storage Sizes	Bytes
19	5
1019	9
2028	13
2938	17

Two data types are used for storage of monetary values: smallmoney and money. These values are stored with a scale of four decimal places. The smallmoney data type consumes 4 bytes and the money data type stores 8 bytes. Both currency data types use four decimal places.

Character Data Types

Character data types are subdivided into two categories depending on the byte size of the characters being stored. Traditionally, character data consumed 1 byte per character, allowing for 255 different characters. This standard was found to be inflexible and inadequate for working with international or extended character sets, so the Unicode standard was developed, in which each character uses 2 bytes of storage. This standard allows for approximately 64,000 different characters.

Each of these two data types has three variations for the storage of data: fixed-length, variable-length, and large character data. Non-Unicode data uses the char, varchar, and text data types, whereas Unicode data is stored in the nchar, nvarchar, and ntext types.

The char data type allows for the storage of fixed-length non-Unicode character data with lengths from 1 through 8,000. The varchar data type allows for the same sizes of data.

The primary difference between the two is that the varchar uses storage space more efficiently and uses only the space necessary to store the data value, regardless of the maximum size a variable has been configured to store. The nchar and nvarchar are essentially the Unicode implementation of char and varchar, and they allow for storage of up to 4,000 characters.

The text and ntext data types are used to store large variable-length character data. The text data type can store up to 2,147,483,647 characters, whereas ntext can store 1,073,741,823.

Binary Data Types

Several data types are used to store binary data. The smallest is the bit data type, which supports Boolean operations and stores values of 0 or 1 in a single storage bit. Other binary data types are used to store binary strings and are stored as hexadecimal values.

Binary data is stored using the binary, varbinary, and image data types. A column assigned the binary data type must have the same fixed length of up to 8KB. In a column assigned the varbinary data type, entries can vary in size. Columns of image data can be used to store variable-length binary data exceeding 8KB, such as Microsoft Word documents, Microsoft Excel spreadsheets, and images that include bitmaps and other graphic files.

Specialty Data Types

Many data types are used in special circumstances to store data that does not directly qualify as numeric, character, or binary. Data types are available to store time and date information, globally unique identifiers (GUIDs), cursors, and tables.

The T-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the T-SQL datetime data type.

Three data types support the storage of time and date information: datetime, smalldatetime, and timestamp. All three store dates and times, although the timestamp data type stores automatically generated binary values using 8 bytes of storage and is not used to store data.

Values with the datetime data type are stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, which is January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. 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. It stores them 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. Dates range from January 1, 1900, through June 6, 2079.

The uniquidentifier data type stores a 16-byte data value known as a globally unique identifier. The GUID takes on the string format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit. A GUID is long and obscure, but it has the advantage of being guaranteed to be unique throughout the world.

The sql_variant is a generic data type that stores values of various SQL Serversupported data types: except text, ntext, image, _timestamp, and sql_variant. It may be used in column definitions, as well as in parameters, variables, and return values of user-defined functions. A sql_variant can have a maximum length of 8,016 bytes.

The table data type is new to SQL Server in the 2000 release. It can be used to temporarily store a resultset for later use. The table data type is not used for defining column types within a structure; rather, it is used in functions, stored procedures, and batches.

The cursor data type is another data type that is used only in functions, stored procedures, and batches. Its primary purpose is to allow the storage of a pointer to a resultset. Attributes of a T-SQL server cursor, such as its scrolling behavior and the query used to build the resultset on which the cursor operates, are set up using a DECLARE CURSOR operation within the procedure.

User-Defined Data Types

User-defined data types are stored as database objects and are based on any of the system data types. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability.

Using these data types can help you create tables more quickly and can also help you control the data in a predictable manner. Often a user-defined data type is created in the Model database; it will then exist in all new user-defined databases created.

A lot of the functions, statements, and system stored procedures that accepted int expressions for their parameters in the previous SQL Server versions have not been changed to support conversion of bigint expressions to those parameters. SQL Server only converts bigint to int when the bigint value is within the range of the int data type. A conversion error occurs at runtime if the bigint expression contains a value outside the range.

Although this is not a big exam topic, you may get a question or two in dealing with the selection of the most appropriate or most efficient data type. Use the byte sizes as presented previously to help you make the correct type determination.