TSQL DDL

							T-SQL for Data Definition


        Vu Tuyet Trinh
      trinhvt-fit@mail.hut.edu.vn
Hanoi University of Technology

                                    1
  Overview of Transact-SQL

     Based on AINSI SQL 92 standard
     Composing of three categories
           Data Manipulation Language (DML)
           Data Definition Language (DDL)
           Data Control Language (DCL)
     Having some Microsoft specific extensions
           Beyond relational data
           .net framework integration




Microsoft
  Data Definition Language

     Create
       used to create databases and their objects.
     Use
       allows you to specify the database you wish to work with within
       your DBMS.
     Alter
       used to modify the definition of it without deleting it
     Drop
       used to remove entire database objects



Microsoft
  Overview of Database Objects




Microsoft
  Outline

     Data Definition Language
     Managing Databases
     Data Types
     Managing Tables
     Managing other SQL Server Objects




Microsoft
  Databases in SQL Server

     Database
       Storing data and other database
         objects


     Database Snapshot
           Maintain historical data for report
            generation                            SQL Server
                                                  Enterprise Edition
           Safeguard data against
            administrative error

           Safeguard data against user error
Microsoft
  Creating a New Database
     Factors to consider
           Default: Sysadmin, dbcreator
           Creator becomes the owner
           Maximum of 32,767 per server
           Follow naming rules




Microsoft
  Creating a New Database

     Some arguments:
         The name of the database
         The size of the database
         The files where the database will reside
          CREATE DATABASE Sample
          ON
            PRIMARY ( NAME=SampleData,
            FILENAME='c:\Program Files\..\..\Data\Sample.mdf',
            SIZE=10MB,
            MAXSIZE=15MB,
            FILEGROWTH=20%)
          LOG ON
            ( NAME=SampleLog,
            FILENAME= 'c:\Program Files\..\..\Data\Sample.ldf',
            SIZE=3MB,
            MAXSIZE=5MB,
            FILEGROWTH=1MB)
Microsoft
          COLLATE SQL_Latin1_General_Cp1_CI_AS
  Setting & Viewing Database Options

           Set Database Options By Using:
               SQL Server Management Studio
               ALTER DATABASE statement
           Database Option Categories
               Auto options
               Cursor options
               Recovery options
               SQL options
               State options



Microsoft
  Retrieving Database Information
           Determining database properties by using the
            DATABASEPROPERTYEX Function
               SELECT DATABASEPROPERTYEX (‘pubs’,’useraccess’)
               SELECT DATABASEPROPERTYEX (‘pubs’,’recovery’)


           Using system stored procedures to display
            information about databases and its parameters
               sp_helpdb
               sp_helpdb database_name
               sp_spaceused [objname]



Microsoft
  Attaching an Existing Database




Microsoft
  Creating a Snapshot Database




Microsoft
  Managing Databases

     Shrinking a Database or File
     Dropping a Database




Microsoft
  Shrinking a Database or File
           Shrinking an Entire Database
       DBCC SHRINKDATABASE (Sample, 25)


           Shrinking a Data File in the Database

        DBCC SHRINKFILE (Sample_Data, 10)

           Shrinking a Database Automatically
            Set autoshrink database option to true




Microsoft
  Dropping a Database
               Methods of Dropping a Database
                   SQL Server Enterprise Manager
                   DROP DATABASE statement
            DROP DATABASE Northwind, pubs
               Restrictions on Dropping a Database
                   While it is being restored
                   When a user is connected to it
                   When publishing as part of replication
                   If it is a system database



Microsoft
  Outline

     Data Definition Language
     Managing Databases
     Data Types
     Managing Tables
     Managing other SQL Server Objects




Microsoft
  Data Types

     System data types
     User-defined data type
           CLR types
     Spatial data
     Filestreams
     XML




Microsoft
  System Data Types
     Exact numeric
           bit, tinyint, smallint, int, bigint, numeric, decimal, smallmoney, money
     Approximate numeric
           float, Real
     Date & Time
           datetime, smalldatetime
     Character string
           char, varchar, text
     Unicode character string
           nchar, nvarchar, ntext
     Binary character string
           binary, varbinary, image
     Others
           sql_variant, timestamp, xml…..
Microsoft
Microsoft   20
Microsoft   21
Microsoft   22
  Creating Tables

     Determining column & data type
     Determining column nullability
     Defining column default values




Microsoft
  Example


   CREATE TABLE dbo.Categories
       (CategoryID               int IDENTITY
                                 (1,1)          NOT NULL,
            CategoryName         nvarchar(15)   NOT NULL,
            Description          ntext          NULL,
            Picture              image          NULL)




Microsoft
  Modifying Table Definition

  ALTER TABLE table_name
     {[ALTER COLUMN column_name
          {DROP DEFAULT
          |SET DEFAULT constant_expression
          |IDENTITY [(seed,increment)]}
     |ADD
          {< column_definition >|< table_constraint>} [ ,...n ]
     |DROP
          {[ CONSTRAINT ] constraint_name
          | COLUMN column }
      ]}




Microsoft
  Example
      ADD ALTER TABLE CategoriesNew
                      ADD Commission money null


            Customer_name Sales_amount   Sales_date   Customer ID   Commission




                                         DROP
 ALTER TABLE CategoriesNew
    DROP COLUMN Sales_date

Microsoft
  Outline

     Data Definition Language
     Managing Databases
     Data Types
     Managing Tables
     Managing other SQL Server Objects




Microsoft
  Views
      What is a view?
         Creating a virtual collection of records from existing tables
         Being used for security and/or performance issues
      Creating view
      CREATE VIEW [schema_name.]view_name [(column[,...n])]
          [ WITH <view_attribute> [ ,...n ] ]
      AS
          select_statement
          [ WITH CHECK OPTION ] [ ; ]
      <view_attribute>::={[ENCRYPTION]
                            [SCHEMABINDING]
                            [ VIEW_METADATA ]}
      Removing view

DROP VIEW { view_name } [ ,...n ]
Microsoft
  Others CREATE/ALTER/DROP… command

     CREATE/ALTER/DROP   RULE …
     CREATE/ALTER/DROP   PROCEDURE …
     CREATE/ALTER/DROP   FUNCTION …
     CREATE/ALTER/DROP   TRIGGER …
     CREATE/ALTER/DROP   USER …
     …



  For further detail information, see [Microsoft SQL Server Books Online ]




Microsoft
  Summary

     Data definition language
        Creating and managing database object
         Create, Use, Alter, Drop,
        SQL Server database objects
         database, table, view, index, …
     Simplifying the management of more complicated data
           Relational data
           User-defined Data
               CLR types

           Spatial data
           Filestreams
           XML



Microsoft                                                   30
Microsoft