sq18

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.