Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "CREATE TABLE" Statement - Creating New Tables

By: FYIcenter.com

(Continued from previous topic...)

How to create new table with "CREATE TABLE" statements?

This is the second tutorial of a quick lesson on creating database objects with Transact-SQL statements. This lesson shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this lesson is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This tutorial assumes that you are running SQL Server Management Studio Express.

To create a table, you must provide a name for the table, and the names and data types of each column in the table. It is also a good practice to indicate whether null values are allowed in each column.

Most tables have a primary key, made up of one or more columns of the table. A primary key is always unique. The Database Engine will enforce the restriction that any primary key value cannot be repeated in the table.

For a list of data types and links for a description of each, see Data Types (Transact-SQL).

Note: The Database Engine can be installed as case sensitive or non-case sensitive. If the Database Engine is installed as case sensitive, object names must always have the same case. For example, a table named OrderData is a different table from a table named ORDERDATA. If the Database Engine is installed as non-case sensitive, those two table names are considered to be the same table, and that name can only be used one time.

Before you create the table in this tutorial, execute the USE command to change the database context to the TestData database. Otherwise, you will create the table in the database you were connected to earlier. That was probably your default database. Unless your default database has been changed, the default database is the master database. You should not create objects in the master database.

Switch the Query Editor connection to the TestData database - In a Query Editor window, type and execute the following code to change your connection to the TestData database.

USE TestData

To create a table - In a Query Editor window, type and execute the following code to create a simple table named Products. The columns in the table are named ProductID, ProductName, Price, and ProductDescription. The ProductID column is the primary key of the table. int, varchar(25), money, and text are all data types. Only the Price and ProductionDescription columns can have no data when a row is inserted or changed. This statement contains an optional element (dbo.) called a schema. The schema is the database object that owns the table. If you are an administrator, dbo is the default schema. dbo stands for database owner.

CREATE TABLE dbo.Products
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription text NULL)

(Continued on next topic...)

  1. How to use Transact-SQL statements to access the database engine?
  2. How to create new databases with "CREATE DATABASE" statements?
  3. How to create new table with "CREATE TABLE" statements?
  4. How to insert and update data into a table with "INSERT" and "UPDATE" statements?
  5. How to read data in a table with "SELECT" statements?
  6. How to create a login to access the database engine using "CREATE LOGIN" statements?
  7. How to create a user to access a database using "CREATE USER" statements?
  8. How to create a view and a stored procedure using "CREATE VIEW/PROCEDURE" statements?
  9. How to grant a permission using "GRANT EXECUTE" statements?
  10. How to delete database objects with "DROP" statements?

Related Articles:


Other Tutorials/FAQs:


Related Resources:


Selected Jobs: