Creating And Altering Tables In Microsoft SQL SERVER 2000

At this point, you have a solid foundation on how to create our own database using the complex CREATE DATABASE syntax, which will come in handy during real time application development, or for verifying and editing codes. In today’s tutorial we will fine-tune our skills on creating a database and discuss on the concepts of adding tables to our database. The easiest way to create a database is to use SQL Server Enterprise Manager, which provides a graphical front end to Transact-SQL commands and stored procedures that actually create the database and set its properties. Open Enterprise Manager’s, Database Properties dialog box, which represents the Transact-SQL CREATE DATABASE command for creating a new user database. Only someone with the sysadmin role or a user who's been granted CREATE DATABASE permission by a DBA can issue the CREATE DATABASE command.

When you create a new user database, SQL Server copies the model database, which—as you learned earlier—is simply a template database. A new user database must be 1 MB or greater in size, and the primary data file size must be at least as large as the primary data file of the model database. Kindly remember, if Enterprise Manager is used to create a database called newdb, the default logical and physical names will be different than if you use the CREATE DATABASE command. Enterprise Manager will give the data file the logical name of newdb_Data (instead of just newdb), and the physical file will have the name newdb_data.mdf.

In this section, we will be studying the syntax to create our own tables. We will also take a look at how to make use of the Enterprise Manager to help us with this, only after we know how to do it ourselves. For it is always better to understand the basics of how it works. {mospagebreak title=Create Table Made Easy} The first part of creating a table is pretty much the same as creating any object. Remember the syntax we already learned, well here it is again

Create <object type><object name>

Since a table is what we want we can be more specific:

Create table customer

With Create Database, we could have stopped with just these first three key words, and SQL Server would have built a database based on the guidelines established in the model database. With CREATE TABLE syntax however there is no “model” table (unlike “model” database) set up in the Server to mimic it, we have to provide all the specifications in the form of columns, data types and special operator.

Create Table [database_name.[owner].]table_name
(<Column name> <data type>
[ [DEFAULT <constant expression>]
|[IDENTITY [ (seed, increment) [ NOT FOR REPLICATION]]]
[ROWGUIDCOL]
[COLLATE <collation name>]
[Null| NOT NULL]
[<Column constraints>]
[column_name as computed_column_expression]
[<Table constarints>]
[,…n]
)
[ON {<filegroup>|DEFAULT}]
[TEXTIMAGE_ON {<filegroup >|DEFAULT]

Now that’s a handful, believe me it still has sections taken out of it for simplicity sake! Don’t worry; let’s look at it part by part starting with the second line.

Bit By Bit

Table and Column Names: The rules for naming tables and columns are in general, the same rules that apply for all database objects. Technically speaking SQL Server refers to this as the rules for identifiers, these rules are pretty simple, what I want to touch here is some notions about naming your objects. There are tons of standards out there for naming database objects; the rules, which I like to practice, are pretty simple and are as follows:

1.For each word in the name, capitalize the first letter and use small case for the remaining letters.
2.Keep the name short, but at the same time long enough to be descriptive.
3.Limit the use of abbreviations. Choose abbreviations, which will be understood by all like “No” for number or “ID” for identification.
4.When building a table based on other tables include the name of parent tables as well. Say you have a table called films and another called actress. And you want to tie them together using a table called “FilmActress”
5.When you have two words in the column name, you could join then with out any separator based on the fact you capitalize the first letter of each new word e.g. EmployeeID or use separator like underscore (“_”). I leave this to you.


Data Types: SQL Server 2000 has several new data types, most adding new capacity to existing data types. For example, the addition of new larger “int” data type or “table” data type. It is beyond the scope to discuss data types here, I am assuming you have a fair idea of the various data types used in general like Bit, Bigint, Int, SmallInt, Money, SmallMoney, float, DateTime, Char, VarChar, text, Image etc. So, will not go into more details, but if you have any doubts you can refer to the web site Microsoft.com and search on SQL Server 2000 data types.

Defaults: It is beyond the scope of this article to cover “defaults” fully, but for now, it is enough to say that this is a value you want to be used for any row that are inserted without a user-supplied value for that particular column. The default if you use one should immediately follow the data type.

Identity:This is a very powerful concept in database design. When you make a column an identity column, SQL Server actually automatically assigns a sequenced number to this column with every row you insert. SQL Server starts counting from what is called the seed value and the amount that value is increased or decreased will depend on increment value. Example as the seed is 2 and increment is 2 so you start counting from 2, and then add 2 each time hence it will be 2, 4, 6, 8, 10 and so on.

Not For Replication:This parameter determines whether a new identity value for the new database is assigned when the column is published to another database via replication or if it will still remain the same. There is much more to it but for the time being this is enough.

ROWGUIDCOL:This is quite similar to identity column in that it is usually used to uniquely identify each row in a table. The main difference between these is to what extent the system will go to ensure truly unique values. Instead of using the normal number count SQL Server uses what is called as a GUID or the Globally Unique Identifier. GUID’s are generated using a combination of information–each of which is designed to be unique in either space or time. So statistically speaking GUID’s are unique in space and time. While identity values are unique across time. SQL uses a special function NEWID() to return a value of GUID.

Collate It works pretty much as it did for create database command (refer part I) with primary difference being, collate in the above syntax is define at column level rather than the database level.

Null/Not Null It states whether the column in question accepts null values are not. The default when you set SQL Server is to set a column Not Null, if you don’t specify nullability.

Column ConstraintsIt is beyond the scope of this article to discuss about constraints here. In short, we can say they are restrictions and rules that you place on individual columns about data that can be inserted into that column.

Computed ColumnsNew with version 7.0 was the ability to have what was called “virtual” column. That is, “Computed Columns” are columns, that do not have any data of its own, but whose value is derived on the fly from other columns in the table.

Table Constraints Are quite similar to column constraints because both of them place restriction on the data that can be inserted into a table. What separates them is that they may be based on more than one column. We will cover table-level constraints including PRIMARY KEY and FOREIGN KEY constraints later.

OnWell, the ON clause in a table definition is a way to state which filegroup you want the table to be located.

TextImage_OnThe only difference between this and “On” clause is that it allows specific parts of the table to be yet a different filegroup. This clause is valid only if the table definition contains text, ntext, or image columns in it.

Creating A Table

Now that we are done with all the boring part, let us get into action, and build a few tables. Our Employees table is going to be the first table in the database, which we will be putting together to track our company’s profile. We are going to add in a USE <Database Name> line prior to my CREATE code so that we make sure that when we run the script, the table is created in the proper database. Any script you create for regular use with a particular database must include USE command to ensure that you are really creating, altering and dropping the objects in the database you intended. We will write a simplified table to start with, but in real time coding it would be much more complex. Below is the syntax to create a table named “Employees” to be stored in a database named “Accounting”.

USE Accounting
CREATE TABLE Employees
{
EmployeeId           int                  IDENTITY       NOT NULL,
EmployeeName     varchar(30)                           NOT NULL,
Address1               varchar(25)                           NOT NULL,
Address2               varchar(20)                           NOT NULL,
City                        varchar(20)                           NOT NULL,
State                      varchar(2)                            NOT NULL,
Zip                         varchar(10)                          NOT NULL,
Contact                  varchar(25)                          NOT NULL,
Phone                    char(15)                               NOT NULL,
Salary                     money                                 NOT NULL,
HireDate                smalldatetime                       NOT NULL,
TerminationDate    smalldatetime                       NULL,
Department            varchar(25)                         NOT NULL                             
}

Once you have built the table, we want to make sure it was indeed created, and it has all the columns and types we expected. To do this you could make use of several commands, but perhaps the best on is SP_HELP syntax.

EXEC sp_help <object name>

To specify the table object that we created, try executing the following code:

EXEC sp_help Employees                   

The EXEC command is used in two different ways. This rendition is used to excute a stored procedure-in our case, a system stored procedure. Technically speaking, you can excute a stored procedure by calling it without using EXEC keyword. The problem is that it will work if the sproc being called is the first statement of any kind of batch. Just running sp_help Employees could have worked in place of the above code, but if you try to run a select statement before this it would blow up on you.

After executing the command you find that you get back several things in the result set one after the other. The information retrieved includes separate result sets for:

•Table name, its owner, type of tables (system vs. user) and creation date.
•Column names, data types, nullability, size and collation.
•The identity column (if one exists) including the start seed and increment value.
•The RowGUIDCol if it exists.
•Filegroup information
•Index names (if any) types and the columns included.
•Constraints names (if any) types and the columns to which it is bound.
•Foreign Key (if any) name and columns.
•The name of Schema bound views if you have defined one.


Now you could try and create a few sample tables based on the above code to include columns like Employee First and last name, middle initial, social security number, job title, manager id etc. If you feel confident enough go ahead and write a few more tables to the database.

Finally folks we have learned how to create a database from scratch using the easy and hard way, and how to add tables to our database. In the next part we will see how to use ALTER statements, if we want to modify our existing database or change a column in a table or change a data type in the table. Although this part of coding is a little cumbersome, trust me if you master this the rest and T-SQL will be a piece of cake. blog comments powered by Disqus