The CREATE DATABASE SQL statement is part of the Data Definition Language (DML) defined by Codd and is used define (CREATE), modify (ALTER) or remove (DROP) databases along with there associated files.
Before I talk about crafting a database, I want to classify SQL statements by four known groups.
Data Definition Language (DDL) statements are SQL statements that support the definition or declaration of database objects.
Data Manipulation Language (DML) is a vocabulary used to add, modify, query, or remove data from a SQL Server database.
Data Control Language (DCL) is a language used to control access to data in a database.
Transaction Control Language (TCL) is a language used to control access to data in a database.
I am going to use a presentation that I did at the local Souther New England SQL Server Users Group as the basis of our future talks.
Hypothetical Business Problem:
What if you were a SQL Server developer at a local consulting company tasked with creating a database for a local Boy Scout of America (BSA) Troop. You are responsible for designing the data storage solution and access methods for the .NET Web designers. Where would you begin?
Today, I am going to talk about defining databases, file groups and schemas. Each database has at least one data file (*.mdf) and one transaction log file (*.ldf). Additional data files (*.ndf) can be added per file group so that tables can be placed in seperate files for parallel access.
The snipet below is our first crack at creating the database via TSQL. The code detects if the database already exists and drops the database. Then, the code creates a database with one data file and one log file. Please note, the minimum size, maximum size and file growth can all be specified at this time.
-- Which database to use. USE [master] GO -- Delete existing databases. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BSA') DROP DATABASE [BSA] GO -- Add new databases. CREATE DATABASE [BSA] ON PRIMARY ( NAME = N'BSA_PRI_DAT', FILENAME = N'C:\SNESSUG\DATA\BSA-PRIMARY.MDF' , SIZE = 10240KB , MAXSIZE = 51200KB , FILEGROWTH = 15%) LOG ON ( NAME = N'BSA_LOG1', FILENAME = N'C:\SNESSUG\LOG\BSA-LOG1.LDF' , SIZE = 5120KB , MAXSIZE = 25600KB , FILEGROWTH = 5120KB ) GO
While this code is useful, we want the ability to load balance table access with different file groups. The snipet below alters the database by adding an additional filegroup, data file and log file. If this was a real life example, we would want to have the data files and log files on different physical disks.
This is assuming you have direct attached storage. With today RAID arrays and SAN environments, the actual storage might be abstracted from the DBA.
-- Add a new file group ALTER DATABASE [BSA] ADD FILEGROUP [STAGE] GO --Add a data file to the file group ALTER database [BSA] ADD FILE ( NAME = N'BSA_STG_DAT', FILENAME = N'C:\SNESSUG\DATA\BSA-STAGE.MDF' , SIZE = 10240KB , MAXSIZE = 51200KB , FILEGROWTH = 15%) TO FILEGROUP STAGE GO -- Add a log file to the database ALTER DATABASE [BSA] ADD LOG FILE ( NAME = N'BSA_LOG2', FILENAME = N'C:\SNESSUG\LOG\BSA-LOG2.LDF' , SIZE = 5120KB , MAXSIZE = 25600KB , FILEGROWTH = 5120KB )
Last but not least, we are going to create two schemas to seperate the tables from each other. This allows security to be admistered at this level if need be. Using descriptive schema names allows a new developer on the team to understand how the tables fit into the overall logical model. We will be creating a RECENT schema that contains current data and STAGE schema for any future ETL jobs that we write.
-- -- Which database to use. USE [BSA] GO -- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'RECENT') DROP SCHEMA [RECENT] GO -- Add new schema. CREATE SCHEMA [RECENT] AUTHORIZATION [dbo] GO -- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'STAGE') DROP SCHEMA [STAGE] GO -- Add new schema. CREATE SCHEMA [STAGE] AUTHORIZATION [dbo] GO
The ALTER SCHEMA statement transfers ownership of one table from one schema to another. Therefore, to rename a schema, one needs to create a new schema, transfer ownership of the objects, and delete the old schema.
The key points to remember from this post is that every database has two files (data & log). Also, schemas can be used to segregate large numbers of tables by functional names and security. I will continue this business solution next time by talking about server logins and database users.
Cloud TagsAFTER ALTER TABLE ALTER TRIGGER CPAN perl modules CREATE DATABASE create function create procedure CREATE TABLE create trigger create view database administrator database developer DATA TYPES DDL DECLARE DELETE DROP DATABASE DROP TRIGGER DROP VIEW execute EXISTS FORMAT free code INSERT ISNULL John F. Miner III PASS perl script REPLACE SELECT sp_help sp_helptext SQL Server SQL Server Management Studio SSMS. string function sys.databases sys.objects sys.schemas TRIGGERS TRUNCATE TABLE TSQL UPDATE USER DEFINED VIEW vb script