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:

  • Server.
    • Database. Aka catalog.
      • Schema. Aka database owner.
        • Table. The various "subjects" in a database.
          • Row (aka record).
          • Column (aka field or attribute).
          • Table controls:
            • Constraint . Allow SQL Server to automatically enforce database integrity.
            • Rule . Out dated way of applying a constraint to a column.
            • Default . Specify a column value if one is not given.
            • Trigger . Like stored procedures that fire whenever UPDATE, INSERT, or DELETE is attempted on specified tables.
            • User-Defined Data Types [this link goes to another page] .
        • View . Like virtual tables.
        • Index . Structures associated with tables and views that speed retrieval of rows.
        • Stored Procedure [this link goes to another page]. A precompiled collection of SQL statements that can be called.
        • User-Defined Functions [this link goes to another page] .
        • Security
          • Logins
          • Users
          • Roles
          • Groups

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:

  • master . master has all the system level information stored in system tables. This includes listings of all the databases on that instance of SQL Server and the paths of all the files that compromise each database. master also has all the system store procedures.
  • tembdb . tembdb is where all temporary databases and procedures park. This wiped and recreated between restarts. Temp stuff is also cleared between sessions.
  • model . This is the template used whenever a new database is made.
  • msdb . msdb is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.

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:

  • Table name can be up to 128 characters long; Temp tables up to 116.
  • Up to two billion tables per database.
  • Up to 1,024 columns per table.
  • No theoretical limit on the number of row or table size.
  • Up to 8,060 bytes per row.
  • A table can only have one clustered index.
  • A table can only have one column, with a data type of uniqueidentifier , can be designated as the ROWGUIDCOL.

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:

  • NOT NULL. Specifies that a column does not accept nulls.
  • PRIMARY KEY. Specifies the column or columns whose values uniquely identify a row in a table. Primary key columns are automatically NOT NULL. Primary keys default to CLUSTERED indexes. Clustered indexes are physical ordering hence a table may have only one clustered index.
  • UNIQUE. Specifies that a column has all unique values. Unique columns default to UNCLUSTERED indexes. A table may have up to 249 nonclustered indexes. While index speed up look ups, they slow down inserts and updates. Consider using the Index Tuning Wizard.
  • CHECK. Limits the domain of acceptable values for a column.
  • FOREIGN KEY. Aka a DRI constraint (Declarative Relational Integrity). Identifies relationships between tables. A foreign key in a table points to a candidate key (usu. a primary key) in another table. A table can have up to 253 FOREIGN KEY 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:

  • When CREATE TABLE is done, use the DEFAULT keyword.
  • The out dated way is to CREATE DEFAULT and then bind it to a column using sp_binddefault .

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:

  • Include COMPUTE or COMPUTE BY clauses.
  • Include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.
  • Include the INTO keyword.
  • Reference a temporary table or a table variable.

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:

  • To focus on particular data.
  • To simplify data manipulation by using view instead of sticking in the whole select statement.
  • To reuse a select statement.
  • To import and export data.
  • To combine partitioned data, i.e. data from different data sources.

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:

  • Clustered . There can only be one clustered index per table/view. The data rows are stored in the same order as the clustered index key. A clustered index is analogous to the table of contents at the front of a book. Clustered indexes are good for columns often searched by value range.
  • Non-clustered . The order of the data rows is not related to the order of the non-clustered index keys. A non-clustered index is analogous to an index at the back of a book. Non-clustered indexes are not good for columns that change very frequently or for more than a few columns.

There are two ways to make indexes:

  • As part of the CREATE TABLE statement while using one of the following keywords:
    • PRIMARY KEY
    • UNIQUE
    • CLUSTERED
    • NONCLUSTERED
  • With CREATE INDEX. Here is the syntax:
    CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX IndexName
        ON {Table | View} (Column [ASC | DESC] [, ...])
    [WITH IndexOption [, ...]]
    [ON filegroup]
    
    /*
    Where IndexOption is {
        PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB
    }
    */
    

Here are some notes on indexes:

  • Fill factor specifies how densely packed an index is stored. If it is low, then it can shrink and expand more easily.
  • Indexes can speed up SELECTs, UPDATEs, and DELETEs but this has to be balanced with the overhead of maintaining indexes. Make indexes for columns that are looked up frequently.
  • If you make an index on a view, then the view is no longer virtual since the data and the index have to be stored.
  • A table or view can have up to 249 non-clustered indexes. Data warehouses can use lots of indexes since the data is relatively static. Very small tables should not use indexes other than PRIMARY KEY or UNIQUE. Try using the the Index Tuning Wizard to decide about indexes. This wizard is processor intensive so it should not be  run from the machine with the database on it.
  • Up to 16 columns can be included in an index. "Narrow" indexes have fewer columns; "Wide" indexes have more.

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


GeorgeHernandez.comSome rights reserved