Creating a Database (Tutorial)

Creating a Database (Tutorial)

SQL Server 2016

Other Versions

Applies To: SQL Server 2016 Preview

Like many Transact-SQL statements, the CREATE DATABASE statement has a required parameter: the name of the database. CREATE DATABASE also has many optional parameters, such as the disk location where you want to put the database files. When you execute CREATE DATABASE without the optional parameters, SQL Server uses default values for many of these parameters. This tutorial uses very few of the optional syntax parameters.

To create a database

1. In a Query Editor window, type but do not execute the following code:

2.  CREATE DATABASE TestData

3.  GO

4. Use the pointer to select the words CREATE DATABASE, and then press F1. The CREATE DATABASE topic in SQL Server Books Online should open. You can use this technique to find the complete syntax for CREATE DATABASE and for the other statements that are used in this tutorial.
5. In Query Editor, press F5 to execute the statement and create a database named TestData.

When you create a database, SQL Server makes a copy of the model database, and renames the copy to the database name. This operation should only take several seconds, unless you specify a large initial size of the database as an optional parameter.

Note

The keyword GO separates statements when more than one statement is submitted in a single batch. GO is optional when the batch contains only one statement.

Next Task in Lesson

Creating a Table (Tutorial)

See Also

CREATE DATABASE (SQL Server Transact-SQL)

Creating a Table (Tutorial)

Other Versions

 

Applies To: SQL Server 2016 Preview

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.

To create a database to contain the new table

• 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.
  

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
  GO
  

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 namedProductID, 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
     (ProductID int PRIMARY KEY NOT NULL,
      ProductName varchar(25) NOT NULL,
      Price money NULL,
      ProductDescription text NULL)
  GO