SQL Data Definition Language (DDL)

1. SQL Data Definition Language (DDL) Using Microsoft SQL Server SDL Data Definition Language (DDL)

2. Data definition language • SQL • Structured Query Language • ISO / ANSI standard • Microsoft has its own dialect of SQL, called T-SQL • SQL = DDL + DML • DDL = Data Definition Language • A part of SQL • Used to create objects in the database • Database, table, view, procedure, function, etc. • General syntax • Create object_type object_name • Create table someName … • Alter table someName • Drop table someName SDL Data Definition Language (DDL)

3. Create table general syntax • Create table someName( firstColumn datatype constraints, secondColumn datatype constraints, …. ) • http://msdn.microsoft.com/en-US/library/ms174979%28v=SQL.105%29.aspx SDL Data Definition Language (DDL)

4. Some data types for use in tables • Integers • Bit, Bigint, Int, SmallInt, TinyInt • Other numbers • Decimal/Numeric, Float • Money • Money, SmallMoney • Date and time • DateTime, DataTime2, SmallDateTime, DateTimeOffset, Date, Time • Text • Char, VarChar, Text, Nchar, NVarChar, Ntext • VarChar is variable length • Nxx is Unicode • Binary data: Pictures, etc. • Binary, VarBinary • XML • XML SDL Data Definition Language (DDL)

5. Special data types • Default • The field has a default value • Used if the user does not supply any data in the INSERT statement • Identity • Used to generate number • Usually for generation of primary key values • Works with int and bigint • Null / not null • Where null is allowed (or not) for the field • Computed columns SDL Data Definition Language (DDL)

6. Drop table • Deletes an existing table • All the data will be deleted as well! • No possible if other tables are referring (foreign keys) the deleted table • http://msdn.microsoft.com/en-us/library/ms173790.aspx SDL Data Definition Language (DDL)

7. Alter table • Used to change and existing table • Add / remove columns • Add / remove constraints • Fairly complex syntax • http://msdn.microsoft.com/en-us/library/ms190273.aspx • If the table is empty it might be easier to drop an re-create the table SDL Data Definition Language (DDL)

8. Naming object in the database • General syntax • serverName.DatabaseName.SchemaName.ObjectName • ServerName • The name of the server, like sqlexpress • DatabaseName • Name of database, like BookStore, Students, etc. • SchemaName • A database can contain several schemas • Object names must be unique within the schema • Default schema dbo (Database Owner) • ObjectName • Name of table, view, procedure, etc. SDL Data Definition Language (DDL)

9. Using the GUI Tool • Management Studio has a GUI tool for creating, altering and dropping table, etc. • Create the tables with relevant columns • Create a database diagram • Use the diagram to create foreign key relationships SDL Data Definition Language (DDL)