Numeric data types should be used for storing numeric data, for data on which you want to perform numeric comparisons or arithmetic operations. Numeric data types can be divided into two groups: integers and decimals.

Character data types are used to store any combination of letters, numbers and symbols. Single quotes have to be used when entering character data. SQL Server 2005 has five types of character data types: CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR.

SQL Server 2005 has two data types for storing date and time information: DATETIME and SMALLDATETIME. DATETIME uses 8 bytes. SMALLDATETIME uses 4 bytes of storage. Internally, the DATETIME and SMALLDATETIME values are stored completely differently from how you enter them or how they are displayed. They are stored as two separate components, a date component and a time component.

When creating primary keys, do not consider using the DATETIME and SMALLDATETIME data types. From a performance standpoint, it is better to use a data type that uses less space for a primary key. The less the space used for a primary key, the smaller the table and index, and the less I/O overhead will be required to access the primary key.

Among other data types available in SQL Server 2005 are BINARY, IMAGE, BIT, TABLE, SQL_VARIANT, UNIQUEIDENTIFIER, and the XML data type (one of SQL Server 2005’s newest enhancements).

Here we present some general rules that you can follow to determine which data type to use to define a column:

One way to insert values into one row of a table is to use the INSERT INTO command with the VALUES option. The INSERT INTO .. VALUES option needs the column list and all the columns in the correct order.

The general syntax for the INSERT INTO .. VALUES option is:

INSERT INTO TableName
VALUES ('character_attribute_value', numeric_attribute_value, ...)

We will first illustrate inserting data with the INSERT INTO .. VALUES option using the Names table we created in the preceding section. So, type the following in the query editor:

INSERT INTO Names
VALUES ('Joe Smith')

where:

Then click the Execute button. You will get a message that will tell you how many rows were inserted by the query:

(1 row(s) affected)

Now, if you type the following SQL query:

SELECT *
FROM    Names

You will get:

fullname
--------------------
Joe Smith
 
(1 row(s) affected)

The INSERT INTO .. VALUES option appends rows to a table (that is, rows are added to the end of the table). So, if you use the INSERT INTO .. VALUES option again as follows:

INSERT INTO Names
VALUES ('Sudip Kumar')

And then type:

SELECT *
FROM    Names

You get this result:

fullname
--------------------
Joe Smith
Sudip Kumar
 
(2 row(s) affected)

If you created a table with n attributes (columns), you usually would have n values in the INSERT INTO .. VALUES statement, in the order of the definition of the columns in the table. For example, to insert into the Employee table that you created earlier, the INSERT INTO .. VALUES statement to insert a row would have to match column for column and would look like this:

INSERT INTO Employee
VALUES ('Joe Smith', '123 4th St.', 101, 2500)

Note that character data is entered with single quotes around it. Numeric data does not use quotes (as shown by 101 and 2500).

Now if you type:

SELECT *
FROM    Employee

You get the following:

names                address              employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith            123 4th St.          101             2500.00
 
(1 row(s) affected)

An INSERT that looks like the following is incorrect, because it does not include all four columns of the Employee table:

INSERT INTO Employee
VALUES ('Joe Smith', '123 4th St.')

You may INSERT a row with less than all the columns by naming the columns you want to insert into, like this:

INSERT INTO Employee (names, address)
VALUES ('Joe Smith', '123 4th St.')

In this case, the row will contain nulls or default values for the values left out, which you will see if you type:

SELECT *
FROM Employee

This will give:

names                address              employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith            123 4th St.          101             2500.00
Joe Smith            123 4th St.          NULL            NULL
 
(2 row(s) affected)

An INSERT that looks like the following is incorrect, because it does not have the values in the same order as the definition of the table:

INSERT INTO Employee
VALUES (2500, 'Joe Smith', 101, '123 4th St.')

If for some reason the data had to be entered in this order, the previous statement could be corrected by specifying the column names, as shown here:

INSERT INTO Employee (salary, names, employee_number, address)
VALUES (2500, 'Joe Smith', 101, '123 4th St.')

At this point, typing:

SELECT *
FROM Employee

would give us the following output:

names                address              employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith            123 4th St.          101             2500.00
Joe Smith            123 4th St.          NULL            NULL
Joe Smith            123 4th St.          101             2500.00
 
(3 row(s) affected)

You may actually include the keyword, null, if the address and the salary were unknown:

INSERT INTO Employee
VALUES ('Joe Smith', null, 101, null)

Now having added four rows to our table, type:

SELECT *
FROM Employee

This query will give the following output:

names                address              employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith            123 4th St.          101             2500.00
Joe Smith            123 4th St.          NULL            NULL
Joe Smith            123 4th St.          101             2500.00
Joe Smith            NULL                 101             NULL
 
(4 row(s) affected)

To delete all the rows in the Employee table as well as in the Names table, type:

DELETE FROM Employee

Then:

DELETE FROM Names

We will revisit the DELETE command later in the chapter.

For the rest of this chapter, we will set up our Employee table with more meaningful data. Suppose we deleted all the test rows from the previous examples with a DELETE statement and then suppose we used the INSERT INTO .. VALUES option to insert valid data into the Employee table, making it look like this:

names                address              employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith            123 4th St.          101             2500.00
Pradeep Saha         27 Shillingford      103             3300.00
Sumit Kumar          95 Oxford Rd         105             1200.00
Joya Das             23 Pesterfield Cr    114             2290.00
Terry Livingstone    465 Easter Ave       95              3309.00
 
(5 row(s) affected)

With the INSERT INTO .. VALUES option, you insert only one row at a time into a table. With the INSERT INTO .. SELECT option, you may (and usually do) insert many rows into a table at one time.

The general syntax for the INSERT INTO .. SELECT option is:

INSERT INTO target_table(column1, column2, column3, ...)
 "SELECT clause"

We will first illustrate inserting with the INSERT INTO .. SELECT by populating the Names table (the one that you created earlier in this chapter and then removed all rows from with a DELETE FROM Names). To copy all the names from the Employee table into the Names table, type the following:

INSERT INTO Names(fullname)
  SELECT  names
  FROM    Employee

And now if you type:

SELECT *
FROM   Names

you will get the following five rows of output:

fullname
--------------------
Joe Smith
Pradeep Saha
Sumit Kumar
Joya Das
Terry Livingstone
 
(5 row(s) affected)

We do not have to copy all the names from the Employee table to the Names table. For example, we could restrict the INSERT .. SELECT like this:

INSERT INTO Names(fullname)
  SELECT names
  FROM   Employee
  WHERE  salary > 2600

This would give us only the following two rows in Names:

fullname
--------------------
Pradeep Saha
Terry Livingstone
 
(2 row(s) affected)

As with the INSERT INTO .. VALUES option, if you create a table with n columns, you usually would have n values in the INSERT INTO .. SELECT option in the order of the table definition, or you would have to name the columns you are inserting. For example, suppose we have a table called Emp1, created with three columns:

Emp1 (addr, sal, empno)

The columns, addr, sal, empno, stand for address, salary, and employee number, respectively.

Now suppose that we want to load the existing empty table called Emp1 from the Employee table with the appropriate columns.

An INSERT INTO .. SELECT statement would look like this:

INSERT INTO Emp1(addr, sal, empno)
  SELECT address, salary, employee_number
  FROM   Employee

The Emp1 table would now have the following five rows:

addr                 sal          empno
-------------------- ------------ -----------
123 4th St.          2500.00        101
27 Shillingford      3300.00        103
95 Oxford Rd         1200.00        105
23 Pesterfield Cr    2290.00        114
465 Easter Ave       3309.00        95
 
(5 row(s) affected)

If we created a table, Emp2, with identical columns (or attributes) as Emp1, we could use the following INSERT to load data from table Emp1 to Emp2:

INSERT INTO Emp2
   SELECT *
      FROM Emp1

The Emp2 table would now have the same data as the Emp1 table. This is one way of creating a backup table.

Again, note that the Emp2 table has to exist (be created with the same columns and types) before loading it with the INSERT INTO .. SELECT option.

One caution must be pointed out, however. An erroneous INSERT INTO .. SELECT could succeed if the data types of the SELECT match the data types of the columns in the table to which we are inserting. For example, say we execute the following statement (remember that both sal and empno are numeric types):

INSERT INTO Emp1 (addr, sal, empno)
  SELECT address, employee_number, salary
  FROM   Employee

This INSERT will succeed because the data types match. The following output results after executing the previous INSERT statement:

addr                 sal          empno
-------------------- ------------ -----------
123 4th St.          101.00        2500
27 Shillingford      103.00        3300
95 Oxford Rd         105.00        1200
23 Pesterfield Cr    114.00        2290
465 Easter Ave       95.00         3309
 
(5 row(s) affected)

The wrong information has been inserted in Emp1’s columns. The employee_number from Employee has been inserted into the sal column in Emp1, and the salary of Employee has been inserted into the empno column of Emp1. So, be careful and line up or match up the columns (attributes) in the INSERT INTO and SELECT statements when using an INSERT INTO .. SELECT.

As you might have already guessed from the INSERT INTO .. VALUES section, you do not have to insert the whole row with an INSERT INTO..SELECT. You may load fewer columns than a whole row of Employee with INSERT .. SELECT. Once again, if we delete all rows from Emp1, and then execute a statement like this:

INSERT INTO Emp1 (addr, sal)
  SELECT  address, salary
  FROM    Employee

This INSERT would leave the other column, empno (of the Emp1 table), with nulls as shown here:

SELECT *
FROM   Emp1

This query produces the following output:

addr                 sal          empno
-------------------- ------------ -----------
123 4th St.          2500.00      NULL
27 Shillingford      3300.00      NULL
95 Oxford Rd         1200.00      NULL
23 Pesterfield Cr    2290.00      NULL
465 Easter Ave       3309.00      NULL
 
(5 row(s) affected)

In conclusion, you must be careful with the INSERT INTO .. SELECT option, because, unlike the INSERT INTO .. VALUES option (which inserts one row at a time), you almost always insert multiple rows, and if types match, the insert will take place whether it makes sense or not.

You may add columns to a table with little difficulty. The general syntax for adding a column to a table is:

ALTER TABLE 
 Tablename
ADD column-name type

For example, to add a column called bonus (a SMALLMONEY column) to the Employee table, you type in the following:

ALTER TABLE Employee
ADD bonus SMALLMONEY

This command alters the table definition of the Employee table, as shown in Figure 3-3 (to get Figure 3-3, click on the + beside the Employee table and then click on the + beside Columns--in the Object Explorer on the left side of your screen):

Figure 3-3. Column added to Employee table

When columns are added to existing tables, they will initially contain null values. Data may be added to the new column using an UPDATE command.

In SQL Server 2005, you can change a column’s data type with existing data in it, provided that the new column data type will accommodate the existing data. The general syntax for changing a column’s data type in a table is:

ALTER TABLE Tablename
ALTER COLUMN column-name new_type

For example, to change the data type of the bonus column from SMALLMONEY to FLOAT, you would type the following:

ALTER TABLE EMPLOYEE
ALTER COLUMN bonus FLOAT

This query would produce the table definition of the Employee table shown in Figure 3-4.

Figure 3-4. Altered column’s data type for bonus column in the Employee table

Changing a column’s length in a table

You may want to change the size of a column in a table. You typically make a column larger, and SQL Server 2005 will not have a problem with that, because larger columns will accommodate existing data. But, if you want to make a column smaller (which is unusual), sometimes SQL Server 2005 will let you do it and other times it will not.

When will SQL Server 2005 allow you to reduce the length of your column without any problems?

• When you do not have any data in that column yet (it’s all NULL).

• When all the data in that column is still less than the size you are changing the column to.

If you try to reduce the column size to a size where you would be cutting off some of the data, SQL Server 2005 will give you an error and will not let you do it.

For example, if you type in the following ALTER TABLE command, trying to change the names column of the Employee table to a size of 5 (where you would be losing some data):

ALTER TABLE Employee
ALTER COLUMN names VARCHAR(5)

You will get the following error message:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

And, upon viewing the table definition of the Employee table, you will find that the column size of the names column was not altered.

If, however, you type:

ALTER TABLE 
 Employee
ALTER COLUMN names VARCHAR(19)

You will get the message:

Command(s) completed successfully.

Now if you look at the table definition of the Employee table, you will see that the names column has been changed to a size of 19 characters, as shown in Figure 3-5.

Figure 3-5. Altering a column’s length in the Employee table

But before you can view this change, you may have to refresh the Employee table.

SQL Server 2005 allowed this reduction in column size, as all the data in the names column was less than 19 characters in length.

Before you proceed to the following section, please change the size of the names column back to 20.

The following is the general syntax for deleting a column from a table:

ALTER TABLE Tablename
DROP column column-name

For example, to delete the column called bonus from the Employee table, type the following:

ALTER TABLE Employee
DROP column bonus

This query produces the definition of the Employee table shown in Figure 3-6, which matches the original design for the table shown in Figure 3-2.

Figure 3-6. Design of Employee table after dropping a column

We will discuss a few other uses of the ALTER TABLE command in subsequent chapters. For example, you can use it to define or change a default column value, enable or disable an integrity constraint, manage internal space, and so on.