SQL Create

A brief discussion of the SQL CREATE command and what it can create.

Most database systems provide a way for you to create database objects via a WYSIWYG interface. For example, Microsoft's SQL Server has SQL Server Management Studio (SSMS). SSMS gives you a kind of graphical representation of your database system. You can browse through your databases, view the tables and their contents etc.

Despite having a tool like SSMS to make these tasks easier, there can be good reasons for wanting to perform some of these tasks programatically. Possible reasons could include:

Your application allows users to create objects on the fly.
You have multiple environments (for example development, staging, production). It's much easier, and less error prone, to run a script against 3 environments than to open up SSMS and repeat the steps in 3 environments — especially if you have lots of changes.
You don't have access to SSMS (or your database system's equivalent).

Fortunately, SQL has a number of CREATE commands that enable you to programatically create database objects including the database, its tables and more.

Here are the CREATE commands supported by SQL Server:

CREATE AGGREGATE
CREATE APPLICATION ROLE
CREATE ASSEMBLY
CREATE ASYMMETRIC KEY
CREATE BROKER PRIORITY
CREATE CERTIFICATE
CREATE COLUMNSTORE INDEX
CREATE CONTRACT
CREATE CREDENTIAL
CREATE CRYPTOGRAPHIC PROVIDER
CREATE DATABASE
CREATE DATABASE AUDIT SPECIFICATION
CREATE DATABASE ENCRYPTION KEY
CREATE DEFAULT
CREATE ENDPOINT
CREATE EVENT NOTIFICATION
CREATE EVENT SESSION
CREATE FULLTEXT CATALOG
CREATE FULLTEXT INDEX
CREATE FULLTEXT STOPLIST
CREATE FUNCTION
CREATE INDEX
CREATE LOGIN
CREATE MASTER KEY
CREATE MESSAGE TYPE
CREATE PARTITION FUNCTION
CREATE PARTITION SCHEME
CREATE PROCEDURE
CREATE QUEUE
CREATE REMOTE SERVICE BINDING
CREATE RESOURCE POOL
CREATE ROLE
CREATE ROUTE
CREATE RULE
CREATE SCHEMA
CREATE SEARCH PROPERTY LIST (Transact-SQL)
CREATE SEQUENCE (Transact-SQL)
CREATE SERVER AUDIT
CREATE SERVER AUDIT SPECIFICATION
CREATE SERVICE
CREATE SPATIAL INDEX
CREATE STATISTICS
CREATE SYMMETRIC KEY
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE USER
CREATE VIEW
CREATE WORKLOAD GROUP
CREATE XML INDEX
CREATE XML SCHEMA COLLECTION

We're not going to cover all these here but, over the next few lessons, we'll cover some of the most common CREATE commands.