SQL Server: Data Types

The char and varchar data types both allow character data up to 8,000 characters. The char data type is fixed-length, and the varchar data type is variable-length. Creating the Code column as char(3) will allow the Code column to store a fixed-length three-character alphanumeric code. Creating the Description column as varchar(30) allows for some storage savings for rows with descriptions shorter than 30 characters. DBA/Developer should use a char data type when values in a column will be a consistent length and a varchar data type when the length of values will vary.For the SeatsAvail column, a tinyint data type would allow the required values but minimize storage. A tinyint data type can store an integer value between 0 and 255 with a single byte. The bit data type would be used to represent a Boolean value, such as whether the event is public or private. The Rating column is defined as decimal (4,2). This allows the column to store a decimal value with a maximum of four total digits, with two digits to the right of the decimal place. This will allow DBA/Developer to store Rating values between 0.00 and 99.99. DBA/Developer should not use the following CREATE TABLE statement:

CREATE TABLE Event (EventID int,
Code varchar(3),
Description varchar(30),
SeatsAvail
int,
TypeFlag int,
Rating decimal(4,2));

A varchar data type stores variable-length data, rather than fixed-length data as required for the Code column in this scenario. The SeatsAvail column does not minimize storage because it is defined as an int data type. An int requires four bytes of storage. In this scenario, a tinyint would be sufficient. The TypeFlag does not minimize storage because it uses an int data type. Because only two possible values need to be stored, a bit could represent this with less storage. DBA/Developer should not use the following CREATE TABLE statement:

CREATE TABLE Event (EventID int,Code char(3),

Description varchar(30),
SeatsAvail
smallint,
TypeFlag bit,
Rating decimal(3,2));

The SeatsAvail column does not minimize storage because it is defined as a smallint data type. A smallint data type requires two bytes of storage and can hold values between -32,768 and 32,767. Atinyint uses one byte and can store values between 0 and 255, which would have been sufficient in this scenario. The Rating column will not be able to store the required value because only three total digits are allowed. With this column definition, the Rating column could only hold values between 0.00 and 9.99. DBA/Developer should not use the following CREATE TABLE statement:

CREATE TABLE Event (EventID int,Code char(3),

Description varchar(30),
SeatsAvail
tinyint,TypeFlag bit,
Rating decimal(3,2));

With this statement, the Rating column will not be able to store the required value because only three total digits are allowed. With this column definition, the Rating column could only hold values between 0.00 and 9.99.