Creating a Table (Tutorial)
Updated: April 18, 2016
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. To create a table, you must have the
CREATE TABLE permission, and the
ALTER SCHEMA permission on the schema that will contain the table. The
db_ddladmin fixed database role has these permissions.
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).
Enter the following code into a Query Editor window.
USE master; GO --Delete the TestData database if it exists. IF EXISTS(SELECT * from sys.databases WHERE name='TestData') BEGIN DROP DATABASE TestData; END --Create a new database called TestData. CREATE DATABASE TestData; Press the F5 key to execute the code and create the database.
In a Query Editor window, type and execute the following code to change your connection to the
USE TestData GO
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
ProductIDcolumn is the primary key of the table.
textare all data types. Only the
ProductionDescriptioncolumns 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,
dbois the default schema.
dbostands for database owner.
CREATE TABLE dbo.Products (ProductID int PRIMARY KEY NOT NULL, ProductName varchar(25) NOT NULL, Price money NULL, ProductDescription text NULL) GO