Office Training

IT Training

Welcome to Lesson 6 of the TSQL Tutorial. In this lesson we will cover create statements. I hope you enjoy the lesson.

Select Statements

CREATE

The CREATE statement is used to create a database, table, or index.

Usage: (creating a database)

CREATE DATABASE database name
Example: We want create database called Northwind.
CREATE DATABASE Northwind
Result: (You should get a confirmation that the database was created.
         The message will be dependant on the database you use.)
The CREATE DATABASE process is allocating 0.63 MB on disk 'Northwind'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'Norhtwind_log'.
Usage: (creating a table)
 
CREATE TABLE table
(
        column1 data_type,
        column2 data_type,
        ...
) 
Example: We want to create the employees table that we used in the earlier lessons.
         (Notice that certain dataypes like varchar will require you to put in a maximum 
          length.  In our script below, the column LastName is created as a varchar with
          a maximum length of 50 characters.)
        
CREATE TABLE Employees 
(
        EmployeeID int,
        LastName varchar (50),
        FirstName varchar (50),
        Title varchar (50),
        BirthDate datetime,
        HireDate datetime,
        Address varchar (60),
        City varchar (50)
)
Result: (You should get a confirmation that the table was created.
         The message will be dependant on the database you use.)
In SQL Server the message is: The command(s) completed successfully.
If we open the table, it is empty.

EmployeeID

LastName

FirstName

Title

BirthDate

HireDate

Address

City

 

 

 

 

 

 

 

 

Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.


Usage: (creating a simple Index)

CREATE INDEX indexname
ON tablename (columnname)

Example:

CREATE INDEX ind_firstname
ON employees (FirstName)

Usage: (creating a unique Index)
            A unique index tells the database that each record (row) cannot be the same value. 
             A unique index would be appropriate for a column like EmployeeID.

CREATE UNIQUE INDEX indexname
ON tablename (columnname)