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.