1 / 9

SQL Data Definition Language (DDL)

SQL Data Definition Language (DDL). Using Microsoft SQL Server. 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

melia
Download Presentation

SQL Data Definition Language (DDL)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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)

More Related