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 APPLICATION ROLE
CREATE ASYMMETRIC KEY
CREATE BROKER PRIORITY
CREATE COLUMNSTORE INDEX
CREATE CRYPTOGRAPHIC PROVIDER
CREATE DATABASE AUDIT SPECIFICATION
CREATE DATABASE ENCRYPTION KEY
CREATE EVENT NOTIFICATION
CREATE EVENT SESSION
CREATE FULLTEXT CATALOG
CREATE FULLTEXT INDEX
CREATE FULLTEXT STOPLIST
CREATE MASTER KEY
CREATE MESSAGE TYPE
CREATE PARTITION FUNCTION
CREATE PARTITION SCHEME
CREATE REMOTE SERVICE BINDING
CREATE RESOURCE POOL
CREATE SEARCH PROPERTY LIST (Transact-SQL)
CREATE SEQUENCE (Transact-SQL)
CREATE SERVER AUDIT
CREATE SERVER AUDIT SPECIFICATION
CREATE SPATIAL INDEX
CREATE SYMMETRIC KEY
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