ITEC 3365 Section 9 SQL CreateAlter Data Definition Commands Dr' Michael L' Gibson ILT Department Co

1
ITEC 3365 Section 9SQL Create/Alter
DataDefinition CommandsDr. Michael L.
GibsonILT DepartmentCollege of
TechnologyUniversity of Houston
2
Create Database SQL Data Definition Syntax

• CREATE DATABASE database_name ON      lt
  filespec gt ,...n      , lt filegroup gt
  ,...n LOG ON lt filespec gt ,...n
  COLLATE collation_name FOR LOAD FOR
  ATTACH
• lt filespec gt
• PRIMARY ( NAME logical_file_name ,
      FILENAME 'os_file_name'      , SIZE
  size      , MAXSIZE max_size UNLIMITED
       , FILEGROWTH growth_increment )
  ,...n
• lt filegroup gt
• FILEGROUP filegroup_name lt filespec gt ,...n

3
Create Database Syntax Arguments

• database_name Is the name of the new database.
• Database names must be unique within a server and
  conform to the rules for identifiers.
• database_name can be a maximum of 128 characters,
  unless no logical name is specified for the log.
• If no logical log file name is specified,
  Microsoft SQL Server generates a logical name
  by appending a suffix to database_name.
• This limits database_name to 123 characters so
  that the generated logical log file name is less
  than 128 characters.

4
Create Database Syntax Arguments

• ON
• Specifies that the disk files used to store the
  data portions of the database (data files) are
  defined explicitly.
• The ON keyword is followed by a comma-separated
  list of ltfilespecgt items defining the data files
  for the primary filegroup.
• The list of files in the primary filegroup can be
  followed by an optional, comma-separated list of
  ltfilegroupgt items defining user filegroups and
  their files.

5
Create Database Syntax Arguments

• n
• Is a placeholder indicating that multiple files
  can be specified for the new database.

6
Create Database Syntax Arguments

• LOG ON
• Specifies that the disk files used to store the
  database log (log files) are explicitly defined.
• The keyword is followed by a comma-separated list
  of ltfilespecgt items defining the log files.
• If LOG ON is not specified, a single log file is
  automatically created with a system-generated
  name and a size that is 25 percent of the sum of
  the sizes of all the data files for the database.

7
Create Database Syntax Arguments

• FOR LOAD
• This clause is supported for compatibility with
  earlier versions of Microsoft SQL Server.
• The database is created with the dbo use only
  database option turned on, and the status is set
  to loading.
• This is not required in SQL Server version 7.0
  because the RESTORE statement can recreate a
  database as part of the restore operation.

8
Create Database Syntax Arguments

• FOR ATTACH
• Specifies that a database is attached from an
  existing set of operating system files.
• There must be a ltfilespecgt entry specifying the
  first primary file.
• The only other ltfilespecgt entries needed are
  those for any files that have a different path
  from when the database was first created or last
  attached.
• A ltfilespecgt entry must be specified for these
  files. The database attached must have been
  created using the same code page and sort order
  as SQL Server.
• Use the sp_attach_db system stored procedure
  instead of using CREATE DATABASE FOR ATTACH
  directly.
• Use CREATE DATABASE FOR ATTACH only when you must
  specify more than 16 ltfilespecgt items.

9
Create Database Syntax Arguments

• FOR ATTACH - continued
• If you attach a database to a server other than
  the server from which the database was detached,
  and the detached database was enabled for
  replication, you should run sp_removedbreplication
  to remove replication from the database.

10
Create Database Syntax Arguments

• collation_name
• The physical storage of character strings in
  Microsoft SQL Server 2000 is controlled by
  collations.
• A collation specifies the bit patterns that
  represent each character and the rules by which
  characters are sorted and compared.
• collation_name specifies the default collation
  for the database.
• Collation name can be either a Windows collation
  name or a SQL collation name.
• If not specified, the database is assigned the
  default collation of the SQL Server instance.

11
Create Database Syntax Arguments

• PRIMARY
• Specifies that the associated ltfilespecgt list
  defines the primary file.
• The primary filegroup contains all of the
  database system tables.
• It also contains all objects not assigned to user
  filegroups.
• The first ltfilespecgt entry in the primary
  filegroup becomes the primary file, which is the
  file containing the logical start of the database
  and its system tables.
• A database can have only one primary file.
• If PRIMARY is not specified, the first file
  listed in the CREATE DATABASE statement becomes
  the primary file.

12
Create Database Syntax Arguments

• NAME
• Specifies the logical name for the file defined
  by the ltfilespecgt.
• The NAME parameter is not required when FOR
  ATTACH is specified.

13
Create Database Syntax Arguments

• logical_file_name
• Is the name used to reference the file in any
  Transact-SQL statements executed after the
  database is created.
• logical_file_name must be unique in the database
  and conform to the rules for identifiers.
• The name can be a character or Unicode constant,
  or a regular or delimited identifier.

14
Create Database Syntax Arguments

• FILENAME
• Specifies the operating-system file name for the
  file defined by the ltfilespecgt.

15
Create Database Syntax Arguments

• 'os_file_name'
• Is the path and file name used by the operating
  system when it creates the physical file defined
  by the ltfilespecgt.
• The path in os_file_name must specify a directory
  on an instance of SQL Server.
• os_file_name cannot specify a directory in a
  compressed file system.
• If the file is created on a raw partition,
  os_file_name must specify only the drive letter
  of an existing raw partition.
• Only one file can be created on each raw
  partition.
• Files on raw partitions do not autogrow
  therefore, the MAXSIZE and FILEGROWTH parameters
  are not needed when os_file_name specifies a raw
  partition.

16
Create Database Syntax Arguments

• SIZE
• Specifies the size of the file defined in the
  ltfilespecgt.
• When a SIZE parameter is not supplied in the
  ltfilespecgt for a primary file, SQL Server uses
  the size of the primary file in the model
  database.
• When a SIZE parameter is not specified in the
  ltfilespecgt for a secondary or log file, SQL
  Server makes the file 1 MB.

17
Create Database Syntax Arguments

• size
• Is the initial size of the file defined in the
  ltfilespecgt.
• The kilobyte (KB), megabyte (MB), gigabyte (GB),
  or terabyte (TB) suffixes can be used.
• The default is MB.
• Specify a whole number do not include a decimal.
  
• The minimum value for size is 512 KB. If size is
  not specified, the default is 1 MB.
• The size specified for the primary file must be
  at least as large as the primary file of the
  model database.

18
Create Database Syntax Arguments

• MAXSIZE
• Specifies the maximum size to which the file
  defined in the ltfilespecgt can grow.

19
Create Database Syntax Arguments

• max_size
• Is the maximum size to which the file defined in
  the ltfilespecgt can grow.
• The kilobyte (KB), megabyte (MB), gigabyte (GB),
  or terabyte (TB) suffixes can be used.
• The default is MB.
• Specify a whole number do not include a decimal.
  
• If max_size is not specified, the file grows
  until the disk is full.
• Note - The Microsoft Windows NT S/B system log
  warns the SQL Server system administrator if a
  disk is almost full.

20
Create Database Syntax Arguments

• UNLIMITED
• Specifies that the file defined in the ltfilespecgt
  grows until the disk is full.

21
Create Database Syntax Arguments

• FILEGROWTH
• Specifies the growth increment of the file
  defined in the ltfilespecgt.
• The FILEGROWTH setting for a file cannot exceed
  the MAXSIZE setting.

22
Create Database Syntax Arguments

• growth_increment
• Is the amount of space added to the file each
  time new space is needed.
• Specify a whole number do not include a decimal.
  
• A value of 0 indicates no growth.
• The value can be specified in MB, KB, GB, TB, or
  percent ().
• If a number is specified without an MB, KB, or
  suffix, the default is MB.
• When is specified, the growth increment size is
  the specified percentage of the size of the file
  at the time the increment occurs.
• If FILEGROWTH is not specified, the default value
  is 10 percent and the minimum value is 64 KB.
• The size specified is rounded to the nearest 64
  KB.

23
Create Database Syntax Arguments

• Remarks
• You can use one CREATE DATABASE statement to
  create a database and the files that store the
  database.
• SQL Server implements the CREATE DATABASE
  statement in two steps
• SQL Server uses a copy of the model database to
  initialize the database and its meta data.
• SQL Server then fills the rest of the database
  with empty pages, except for pages that have
  internal data recording how the space is used in
  the database.
• Any user-defined objects in the model database
  are therefore copied to all newly created
  databases.
• You can add to the model database any objects,
  such as tables, views, stored procedures, data
  types, and so on, to be included in all databases.

24
Create Database Syntax Arguments

• Remarks continued
• Each new database inherits the database option
  settings from the model database (unless FOR
  ATTACH is specified).
• For example, the database option select
  into/bulkcopy is set to OFF in model and any new
  databases you create.
• If you use ALTER DATABASE to change the options
  for the model database, these option settings are
  in effect for new databases you create.
• If FOR ATTACH is specified on the CREATE DATABASE
  statement, the new database inherits the database
  option settings of the original database.

25
Create Database Syntax Arguments

• Remarks continued
• A maximum of 32,767 databases can be specified on
  a server.
• There are three types of files used to store a
  database
• The primary file contains the startup information
  for the database.
• The primary file is also used to store data.
• Every database has one primary file.
• Secondary files hold all of the data that does
  not fit in the primary data file.
• Databases need not have any secondary data files
  if the primary file is large enough to hold all
  of the data in the database.
• Other databases may be large enough to need
  multiple secondary data files, or they may use
  secondary files on separate disk drives to spread
  the data across multiple disks.
• Transaction log files hold the log information
  used to recover the database.
• There must be at least one transaction log file
  for each database, although there may be more
  than one.
• The minimum size for a transaction log file is
  512 KB.

26
Create Database Syntax Arguments

• Remarks continued
• Every database has at least two files, a primary
  file and a transaction log file.
• Although 'os_file_name' can be any valid
  operating system file name, the name more clearly
  reflects the purpose of the file if you use the
  following recommended extensions.

27
Create Database Example

• CREATE DATABASE Temp_Database
• ON (NAME TempDatabase',
• FILENAME'd\kangkitty\Temp_Database.mdf',
• SIZE 2,
• FILEGROWTH10)
• LOG ON (NAME Temp_Database_Log',
• FILENAME'd\ kangkitty\Temp_Database.ldf',
• SIZE 2,
• FILEGROWTH10)
• COLLATE SQL_Latin1_General_CP1_CI_AS
• GO

28
Create Database Table Example

• USE TempDatabase
•  
• CREATE TABLE Temp_Table
• (
• Temp_Property1 int IDENTITY (1,1) NOT NULL,
• Temp_Property2 int NOT NULL,
• Temp_Property3 datetime NOT NULL,
• Temp_Property4 money NOT NULL,
• Temp_Property5 char (4) NULL
• )
• GO

29
Constraints

• Constraints allow you to define the way
  Microsoft SQL Server 2000 automatically
  enforces the integrity of a database.
• Constraints define rules regarding the values
  allowed in columns and are the standard mechanism
  for enforcing integrity.
• Using constraints is preferred to using triggers,
  rules, and defaults.
• The query optimizer also uses constraint
  definitions to build high-performance query
  execution plans.

30
Column and Table Constraints

• Constraints can be column constraints or table
  constraints
• A column constraint is specified as part of a
  column definition and applies only to that column
  (the constraints in the earlier samples are
  column constraints).
• A table constraint is declared independently from
  a column definition and can apply to more than
  one column in a table.

31
Constraint Types

• NOT NULL specifies that the column does not
  accept NULL values.
• CHECK constraints enforce domain integrity by
  limiting the values that can be placed in a
  column.
• PRIMARY KEY constraints identify the column or
  set of columns whose values uniquely identify a
  row in a table.
• FOREIGN KEY constraints identify the
  relationships between tables.

32
Check Object

• The Check object represents the attributes of a
  single Microsoft SQL Server 2000 integrity
  constraint.

33
Creating and Modifying PRIMARY KEY Constraints

• A single PRIMARY KEY constraint can be
• Created when the table is created, as part of the
  table definition.
• Added to an existing table, provided that no
  other PRIMARY KEY constraint already exists (a
  table can have only one PRIMARY KEY constraint).
• Modified or deleted, if it already exists.
• For example, you may want the PRIMARY KEY
  constraint of the table to reference other
  columns, or you may want to change the column
  order, index name, clustered option, or fill
  factor of the PRIMARY KEY constraint.
• It is not possible to change the length of a
  column defined with a PRIMARY KEY constraint.

34
Creating and Modifying FOREIGN KEY Constraints

• FOREIGN KEY constraints can be
• Created when the table is created, as part of the
  table definition.
• Added to an existing table provided that the
  FOREIGN KEY constraint is linked to an existing
  PRIMARY KEY constraint or UNIQUE constraint in
  another (or the same) table.
• A table can contain multiple FOREIGN KEY
  constraints.
• Modified or deleted if FOREIGN KEY constraints
  already exist.
• For example, you may want the table's FOREIGN KEY
  constraint to reference other columns.
• It is not possible to change the length of a
  column defined with a FOREIGN KEY constraint.

35
Create Database SQL Data Definition Syntax

• CREATE DATABASE database_name ON      lt
  filespec gt ,...n      , lt filegroup gt
  ,...n LOG ON lt filespec gt ,...n
  COLLATE collation_name FOR LOAD FOR
  ATTACH
• lt filespec gt
• PRIMARY ( NAME logical_file_name ,
      FILENAME 'os_file_name'      , SIZE
  size      , MAXSIZE max_size UNLIMITED
       , FILEGROWTH growth_increment )
  ,...n
• lt filegroup gt
• FILEGROUP filegroup_name lt filespec gt ,...n

36
Alter Database or Table

• Adds or removes files and file-groups from a
  database table.
• Can also be used to modify the attributes of
  files and file-groups, such as changing the name
  or size of a file or designating primary and/or
  foreign key properties for a database table.
• ALTER DATABASE provides the ability to change the
  database name, file-group names, and the logical
  names of data files and log files.
• ALTER DATABASE supports the setting of database
  options.
• The DATABASEPROPERTYEX function can be used to
  retrieve current settings for database options.

37
Alter Table Command

• ALTER TABLE table ALTER COLUMN column_name
       new_data_type ( precision , scale )
           COLLATE lt collation_name gt
           NULL NOT NULL          ADD
  DROP ROWGUIDCOL           ADD         
  lt column_definition gt           column_name
  AS computed_column_expression          ,...n
       WITH CHECK WITH NOCHECK ADD
           lt table_constraint gt ,...n
       DROP          CONSTRAINT
  constraint_name              COLUMN column
  ,...n      CHECK NOCHECK CONSTRAINT
           ALL constraint_name ,...n
       ENABLE DISABLE TRIGGER         
  ALL trigger_name ,...n

38
Alter Table Command - continued

• lt column_definition gt      column_name
  data_type      DEFAULT constant_expression
  WITH VALUES      IDENTITY (seed ,
  increment ) NOT FOR REPLICATION
                ROWGUIDCOL      COLLATE lt
  collation_name gt      lt column_constraint gt
  ...n

39
Alter Table Command - continued

• lt column_constraint gt      CONSTRAINT
  constraint_name      NULL NOT NULL
           PRIMARY KEY UNIQUE
               CLUSTERED NONCLUSTERED
               WITH FILLFACTOR fillfactor
               ON filegroup DEFAULT
                        FOREIGN KEY
              REFERENCES ref_table ( ref_column
  )              ON DELETE CASCADE NO
  ACTION              ON UPDATE CASCADE
  NO ACTION              NOT FOR REPLICATION
                        CHECK NOT FOR
  REPLICATION             ( logical_expression )
      

40
Alter Table Command - continued

• lt table_constraint gt      CONSTRAINT
  constraint_name      PRIMARY KEY UNIQUE
           CLUSTERED NONCLUSTERED
           ( column ,...n )          WITH
  FILLFACTOR fillfactor          ON
  filegroup DEFAULT         
              FOREIGN KEY              ( column
  ,...n )             REFERENCES ref_table
  ( ref_column ,...n )              ON
  DELETE CASCADE NO ACTION             
  ON UPDATE CASCADE NO ACTION
               NOT FOR REPLICATION         
  DEFAULT constant_expression              FOR
  column WITH VALUES             CHECK
  NOT FOR REPLICATION             (
  search_conditions )     

41
With Check With Nocheck Entries

• Specifies whether the data in the table is or is
  not validated against a newly added or re-enabled
  FOREIGN KEY or CHECK constraint.
• If not specified, WITH CHECK is assumed for new
  constraints, and WITH NOCHECK is assumed for
  re-enabled constraints.

42
Add Constraint Entry

• Used to add a constraint to a database, table, or
  column.
• Often used to add a primary key or foreign key
  constraint to a database table.
• Also used to specify a domain type specification
  for a property (column) of a table.

43
Clustered Entry

• A clustered entry designates the physical order
  of data in a table.

44
ON Primary Entry

• Makes the primary file-group the default
  file-group if another file-group was made the
  default earlier.

45
Go Command

• Signals the end of a batch of Transact-SQL
  statements to the Microsoft SQL Server
  utilities.
• SQL Server utilities interpret GO as a signal
  that they should send the current batch of
  Transact-SQL statements to SQL Server.
• The current batch of statements is composed of
  all statements entered since the last GO, or
  since the start of the ad hoc session or script
  if this is the first GO.

46
Alter Command Examples

• Alter Table Temp_Table1 With Nocheck
• Add Constraint PK_Temp_Table1 Primary Key
  Clustered
• (Property1_ID, Property2_ID)
• On Primary
• Go
• Alter Table Temp_Table2 With Check
• Add Constraint FK_ Temp_Table2
• Foreign Key
• (Temp_Table2_ID)
• References Temp_Table3 (Temp_Table3_ID)
• GO