Chapter 4: Implementing SQL Server Databases

1
Chapter 4 Implementing SQL Server Databases
Tables
2
Overview
P143

• Creating and Managing a SQL Server Database
• Identifying Data Types
• Creating and Managing Tables

3
Creating and Managing a SQL Server Database
P144

• Factors to consider when creating databases
• Default Sysadmin, dbcreator
• Creator becomes the owner
• Maximum of 32,767 per server
• Follow naming rules

4
Create Database Statement
P145

• Creating a Database Defines
• The name of the database
• The size of the database
• The files where the database will reside

CREATE DATABASE SampleON PRIMARY (
NAMESampleData, FILENAME'c\Program
Files\..\..\Data\Sample.mdf', SIZE10MB,
MAXSIZE15MB, FILEGROWTH20) LOG ON (
NAMESampleLog, FILENAME 'c\Program
Files\..\..\Data\Sample.ldf', SIZE3MB,
MAXSIZE5MB, FILEGROWTH1MB)COLLATE
SQL_Latin1_General_Cp1_CI_AS
5
Setting Viewing Database Options
P146-149

• Set Database Options By Using
• SQL Server Enterprise Manager
• ALTER DATABASE statement
• Database Option Categories
• Auto options
• Cursor options
• Recovery options
• SQL options
• State options

6
Retrieving Database Information
P148

• Determine Database Properties by Using the
  DATABASEPROPERTYEX Function
• SELECT DATABASEPROPERTYEX (pubs,useraccess)
• SELECT DATABASEPROPERTYEX (pubs,recovery)
• Use System Stored Procedures to Display
  Information About Databases and Database
  Parameters
• sp_helpdb
• sp_helpdb database_name
• sp_spaceused objname

7
Managing Databases
P148-150

• Managing Data and Log File Growth
• Monitoring and Expanding a Transaction Log
• Shrinking a Database or File
• Dropping a Database

8
Managing Data and Log File Growth
P148-150

• Using Automatic File Growth
• Expanding Database Files
• Adding Secondary Database Files

ALTER DATABASE Sample MODIFY FILE ( NAME
'SampleLog', SIZE 15MB) GO ALTER DATABASE
Sample ADD FILE (NAME SampleData2,
FILENAME'c\Program Files\..\..\
Data\Sample2.ndf', SIZE15MB,
MAXSIZE20MB) GO
9
Monitoring and Expanding a Transaction Log
P148-150

• 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

10
Shrinking a Database or File
P148-150

• 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)
11
Dropping a Database
P150

• Methods of Dropping a Database
• SQL Server Enterprise Manager
• DROP DATABASE statement
• 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

DROP DATABASE Northwind, pubs
12
Identifying Data Types
P155

• System-supplied Data Types
• Creating and Dropping User-defined Data Types
• Guidelines for Specifying Data Types

13
System-supplied Data Types
P155-159

• Numeric
• Integer
• Exact numeric
• Approximate numeric
• Monetary
• Date and Time
• Character and Unicode Character
• Binary
• Other

14
Creating and Dropping User-defined Data Types
P160
Creating
Dropping
EXEC sp_droptype city
15
Creating and Managing Tables
P163-167

• Creating Tables in a SQL Server Database
• Determine Column Nullability
• Define Default Values
• Autonumbering and Identity Columns

16
Determine Column Nullability
P163-164

• In general avoid allowing Null values
• Make querying complex
• Cant be used to distinguish one row from another
• Not allowed in Primary Key constraint or IDENTITY
  property
• Consider using a Default instead

17
Generating Column Values
P163-167

• Using the Identity Property
• System generated sequential values
• Using the NEWID Function and the
  uniqueidentifier Data Type

18
Using the Identity Property
P163-167

• Requirements for Using the Identity Property
• Only one identity column is allowed per table
• Use with integer, numeric, and decimal data types
• Retrieving Information About the Identity
  Property
• Use IDENT_SEED and IDENT_INCR for definition
  information
• Use _at__at_identity to determine most recent value
• Managing the Identity Property

19
Using the NEWID Function and the
uniqueidentifier Data Type
P168

• These Features Are Used Together
• Ensure Globally Unique Values
• Use with the DEFAULT Constraint

20
Methods for Creating a Table
P169

• Creating a Table
• Column Collation
• Specifying NULL or NOT NULL
• Computed Columns
• Dropping a Table

21
Modifying Tables in a SQL Server Database
P171
ALTER TABLE CategoriesNew ADD Commission money
null
ADD
Customer_name
Sales_amount
Sales_date
Customer ID
Commission

DROP
ALTER TABLE CategoriesNew DROP COLUMN
Sales_date
22
Generating Scripts

• Generate Schema as a Transact-SQL Script
• Maintain backup script
• Create or update a database development script
• Create a test or development environment
• Train new employees
• What to Generate
• Entire database into single script file
• Table-only schema
• Table and index schema

23
Recommended Practices
24
Review

• Creating and Managing a SQL Server Database
• Identifying Data Types
• Creating and Managing Tables

25
Lab Implementing SQL Server Databases Tables
26
Lab Implementing SQL Server Databases Tables

• Exercise 1
• Pages 150-154 Creating and Managing a Database
• Exercise 2
• Pages 160-162 Identifying Column Data Types
• Exercise 3
• Pages 172-174 Creating and Managing Tables in a
  SQL Server Database