T-SQL for Data Definition
Vu Tuyet Trinh

Hanoi University of Technology
1
MicrosoftMicrosoft
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
MicrosoftMicrosoft
Data Definition Language

Create
used to create databases and their objects.

Use
allows you to specify the database you wish to work with within

Maintain historical data for report
generation

Safeguard data against
administrative error

Safeguard data against user error
SQL Server
Enterprise Edition
MicrosoftMicrosoft
Creating a New Database

Factors to consider

Default: Sysadmin, dbcreator

Creator becomes the owner

Maximum of 32,767 per server

Follow naming rules
MicrosoftMicrosoft
Creating a New Database

Some arguments:

The name of the database

The size of the database


Recovery options

SQL options

State options
MicrosoftMicrosoft
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]
MicrosoftMicrosoft
Attaching an Existing Database
MicrosoftMicrosoft
Creating a Snapshot Database
MicrosoftMicrosoft

Monitoring and Expanding a Transaction Log

Monitoring the log

Monitoring situations that produce extensive
log activity

Mass loading of data into indexed table

Large transactions

Performing logged text or image operations

Expanding the log when necessary
MicrosoftMicrosoft
Shrinking a Database or File

Shrinking an Entire Database

Shrinking a Data File in the Database

Shrinking a Database Automatically
Set autoshrink database option to true
DBCC SHRINKDATABASE (Sample, 25)
DBCC SHRINKFILE (Sample_Data, 10)
MicrosoftMicrosoft
Dropping a Database
DROP DATABASE Northwind, pubs

Methods of Dropping a Database