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.
- CREATE
- ALTER
- DROP
Data Manipulation Language (DML) is a vocabulary used to add, modify, query, or remove data from a SQL Server database.
- SELECT
- INSERT
- UPDATE
- DELETE
Data Control Language (DCL) is a language used to control access to data in a database.
- GRANT
- REVOKE
- DENY
Transaction Control Language (TCL) is a language used to control access to data in a database.
- BEGIN
- COMMIT
- ROLLBACK
- SAVE
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<span style="color: #008000;">-- 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</span> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<span style="color: #008000;">-- 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 ) </span> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<span style="color: #008000;">-- -- 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</span> |
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.
Please check out SQL BLOG or SQL SERVER CENTRAL. While others might have covered these topics, the application of the ideas might be different from author to author. Experience plays a big role in good design.
Nice article. Its realy good. More info help me.
It is difficult to find educated people on this particular topic, but you seem like you realize very well what youre covering! Many thanks
I assemble the web page link of your respective blog page in my Facebook or myspace Wall structure. good blog indeed.,
Maybe you are so neat! I really won’t believe Ive understand something like the following previous to. Flexible to uncover anyone with some unique thoughts on this unique subject. realy appreciate outset the following up. this fabulous website is a thing that is definitely desired on the internet, someone with a amount of creativity. important project for providing something new to online!
Bookmarked! Thanks for an amazing post, will read your others posts.
This design is spectacular! You most certainly know how to keep a reader entertained. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Excellent job. I really enjoyed what you had to say, and more than that, how you presented it. Too cool!
Greetings from Florida! I’m bored to tears at work so I decided to browse your blog on my iphone during lunch break. I enjoy the information you present here and can’t wait to take a look when I get home. I’m surprised at how fast your blog loaded on my phone .. I’m not even using WIFI, just 3G .. Anyhow, wonderful blog!
Enjoyed studying this, very good stuff, thankyou . “Nothing happens to any thing which that thing is not made by nature to bear.” by Marcus Aurelius Antoninus.
I discovered your blog site on google and check a few of your early posts. Continue to keep up the superb operate. I simply extra up your RSS feed to my MSN Information Reader. Seeking forward to studying more from you afterward!…