DDL (Data Definition Language). These SQL statements define the structure of a database, including rows, columns, tables, indexes, and database specifics such as file locations. DDL SQL statements are more part of the DBMS and have large differences between the SQL variations.

Intro

A database is broken up into database objects. Here is the basic structure:

A server usually has one instance (installation) of SQL Server but it may have more. Each instance of SQL Server is required to have these four databases:

A collation specifies the sort order for text data types (char, varchar, text for non-Unicode and nchar, nvarchar, and ntext for Unicode) and the code page used for non-Unicode data. Prior to SQL Server 2000, all objects within an instance of SQL Server had the same collation. SQL Server 2000 allows different objects to have different collations, down to the column level, ie each column in a table can have a different collation. See also my section on Character Sets.

Here is the syntax for dropping various objects from a database. The object being dropped may need to be qualified. Only one table can be dropped at a time. Rules need to be unbound using the system procedures sp_unbindrule before being dropped.

DROP
{ DATABASE | DEFAULT | FUNCTION | INDEX | PROCEDURE |
  RULE | STATISTICS | TABLE | TRIGGER | VIEW
}
Object [, ...]

EG Drop

DROP TABLE ScrapTable

Table

Tables are the various "subjects" in a database.

Here is the basic syntax for CREATE TABLE in SQL Server:

CREATE TABLE [database_name.[owner_or_schema]. | owner_or_schema.]table_name(
    { ColumnDefinition | ComputedColumnDefinition  } [TableConstraint] 
    [,...]
)
[ON { PartitionSchemeName(PartitionColumnName) | Filegroup | "DEFAULT" }]
[TEXTIMAGE_ON { Filegroup | "DEFAULT" }]
[;]
--ColumnDefinition is:
ColumnName DataType
[COLLATE CollationName]
[NULL | NOT NULL]
[
    [CONSTRAINT ConstraintName] DEFAULT ConstantExpression |
    IDENTITY [(seed, increment)] [NOT FOR REPLICATION]
] 
[ROWGUIDCOL]
[ColumnConstraint]

Here is the basic syntax for CREATE TABLE  in MySQL:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_option ...]
    [select_statement_with_data_for_the_table]
-- create_definition is:
    column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) [reference_definition]
  | CHECK (expr)

 -- column_definition is:
    col_name data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string'] [reference_definition]

Here are basic table stats for SQL Server:

Consider making and storing CREATE TABLE code as part of documentation.

Here is basic code to alter a table. This will vary greatly depending upon the DBMS.

ALTER TABLE [database_name.[owner]. | owner.]table_name {    ALTER COLUMN column_name new_data_type |
    ADD column_name AS data_type NULL |
    DROP COLUMN column_name
}

Temporary tables (syntax: #LocalTempTable or ##GlobalTempTable) can be created and used just like a regular table or by simply inserting into it without creating it. A local table is visible only to the connection that made the table; global is available to all sessions. Many things that used to be done with temp tables can now be done with the temp data type. Consider using the table data type instead of temp tables because the scopes is more clearly defined. EG: declare @tbl table(ID int, Name char);.

EG Table

Here is a simple table:

CREATE TABLE tblScrap (
tblScrapID int,
col2 char(5)
)

Constraint

Constraints allow SQL Server to automatically enforce database integrity. Constraints are preferred over triggers, rules, and defaults. Constraints can cover an entire table or specific columns in a table. Here is they syntax used in CREATE TABLE:

--ColumnConstraint is
[CONSTRAINT ConstraintName]
{ [NULL | NOT NULL]
| [ {PRIMARY KEY | UNIQUE}
    [CLUSTERED | NONCLUSTERED]
    [WITH FILLFACTOR = fillfactor]
    [ON {filegroup | DEFAULT}]
  ] 
| [ [FOREIGN KEY]
    REFERENCES RefTable [(RefColumn)]
    [ON DELETE {CASCADE | NO ACTION}]
    [ON UPDATE {CASCADE | NO ACTION}]
    [NOT FOR REPLICATION]
  ] 
| CHECK [NOT FOR REPLICATION] (LogicalExpression)
}
--TableConstraint is:
[CONSTRAINT ConstraintName]
{ [ {PRIMARY KEY | UNIQUE} 
    [CLUSTERED | NONCLUSTERED]
    {(Column [ASC | DESC] [,...])}
    [WITH FILLFACTOR = Fillfactor]
    [ON {Filegroup | DEFAULT}] 
  ] 
| FOREIGN KEY [(Column [,...])] 
  REFERENCES RefTable [(RefColumn [,...])]
  [ON DELETE {CASCADE | NO ACTION}]
  [ON UPDATE {CASCADE | NO ACTION}]
  [NOT FOR REPLICATION]
| CHECK [NOT FOR REPLICATION] (SearchConditions)
}

There are five classes of constraints:

EG Constraint

A column constraint and a table constraint are applied here.

CREATE TABLE tblOrder (
    OrderID int PRIMARY KEY,
    ShippingID int NOT NULL
        FOREIGN KEY REFERENCES tblShipping(ShippingID)
        ON DELETE CASCADE,
    Date datetime,
    Tag varchar(32) NONCLUSTERED,
    CONSTRAINT OrderID CHECK (OrderID BETWEEN 0 and 10000 )
)

Rule

Rules are out dated, but they were a way of applying a constraint to a column. The idea was to CREATE RULE and then bind it to a column using sp_bindrule.

EG Rule

CREATE RULE rulIDChk AS @id BETWEEN 0 and 10000
GO
CREATE TABLE tblOrder (
    OrderID int PRIMARY KEY,
    ShippingID int NOT NULL
        FOREIGN KEY REFERENCES tblShipping(ShippingID
        ON DELETE CASCADE,
    Date datetime
)
GO
sp_bindrule rulIDChk, 'tblOrder.OrderID'
GO

Default

Defaults specify a column value if one is not given. Defaults values are anything that evaluate into constants. There are two ways to make defaults:

SQL-92 niladic functions are commonly used for DEFAULT. Here are what the niladic functions would return:

SQL-92 niladic function Value returned
CURRENT_TIMESTAMP Current date and time.
CURRENT_USER Name of user performing insert.
SESSION_USER Name of user performing insert.
SYSTEM_USER Name of user performing insert.
USER Name of user performing insert.

EG Default

CREATE DEFAULT defStart AS getdate()
GO
CREATE TABLE tblX (
    Date datetime DEFAULT getdate(), --preferred
    Date2 datetime,
    Date3 datetime DEFAULT CURRENT_TIMESTAMP
)
GO
sp_binddefault defStart, 'tblX.Date2
GO

Trigger

Triggers are like stored procedures that fires whenever UPDATE, INSERT, or DELETE is attempted on specified tables. Here is the syntax:

CREATE TRIGGER TriggerName 
ON {Table | View} 
[WITH ENCRYPTION] 
{FOR | AFTER | INSTEAD OF} {[DELETE][, ][INSERT][, ][UPDATE]}
[WITH APPEND]
[NOT FOR REPLICATION]
AS 
[ { IF UPDATE (Column) [{AND | OR} UPDATE (Column)] [...] 
  | IF (COLUMNS_UPDATED() {BitwiseOperator} UpdatedBitmask)
       {ComparisonOperator} ColumnBitmask [...] 
  }
]
sqlstatement [...]

EG Trigger

CREATE TRIGGER reminder
ON tblY
FOR INSERT, UPDATE 
AS RAISERROR (50009, 16, 10)
CREATE TRIGGER tgr_TableName_Ins
ON TableName
AFTER UPDATE
AS
BEGIN
    insert into TriggerLog
    (Stamp, Act, TableName) values (getdate(), 'ins', 'TableName');
END

View

Views are like virtual tables. A view returns a result set, with rows and columns, and can be used just like a table in SQL statements. The data compromising a view is pulled from the source table when needed. Here is the syntax:

CREATE VIEW [DatabaseName.][Owner.]ViewName [(ColumnName[, ...])] 
[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA }[, ...]]
AS
SelectStatement 
[WITH CHECK OPTION]

The SelectStatement cannot:

WITH CHECK OPTION ensures that all data modifications executed using the view adhere to the criteria set by the SelectStatement.

Views are used for various reasons:

EG View

This is a simple view. Note that calculated columns must be named in the CREATE VIEW line if not explicitly aliased in the SelectStatement.

CREATE VIEW vwSeeMe (ColA, AvgPrice)
AS
    SELECT ColA, AVG(price)
    FROM tbl1
    GROUP BY ColA

Index

Indexes are structures associated with tables and views that speed retrieval of rows. An index has keys that correspond to one or more columns in its table or view. Tables and views without indexes are not ordered in a particular order and are called heaps. A table may have more than one index, just as a book may have an index for names and another for places.

There are two types of indexes:

There are two ways to make indexes:

Here are some notes on indexes:

EG Index

CREATE TABLE Folk (
    FolkID int PRIMARY KEY, --This makes index 1
    Name varchar(100) CLUSTERED --This makes index 2
    EMail varchar(100)
)
GO
CREATE INDEX ixFolk ON Folk(EMail) --This makes index 3
GO

Page Modified: (Hand noted: 2008-05-12 17:47:00Z) (Auto noted: 2010-12-24 22:47:34Z)