How to Create a Database Using T-SQL
Published: Aug 12, 2013
IMPORTANT: The majority of the settings you’re about to see are actually taken from the system database “model”. The model database is, as its name implies, the “model” by which all others are created. Therefore if you change settings, initial sizes, growth rates, and recovery options in the model database, then those are the defaults you will be presented with when creating a new database from scratch following the method below. If you have some settings that you wish to apply generically to all databases, then simply change them on the model database.

Database Creation

Open SQL Server Management Studio and wait for the login screen to appear. Log in to your SQL Server instance using whatever credentials you have. In my case I’m logging into a local SQL Server using the sa account.

(Note: I wouldn’t normally use the sa account, but this is a brand new install at time of writing and I have not created any accounts yet)

Once inside, click on "New Query" in the top left of your screen (or use Ctrl+N)...

Once you've got your new query window ready, we can start to create our database.

First things first, we need some code that checks to see if the database already exists and if it does, it will drop the database...
if DB_ID('boredDBA') is not null
begin
  drop database boredDBA
end
go
Now we know that we are starting from a clean slate, we can begin to create our database.

We will add to this script in stages throughout the article, therefore if you are following along then either run the drop script above after running each new create script, or simply wait until the end and run the complete script at the bottom.

Basic Create Script

If you are confident that all the defaults in Model are correct then this couldn't be easier...
create database boredDBA
go
If you wish to see the current state of your database at any time and verify the size, growth, and both logical and physical filenames, simply run the following:
sp_helpdb 'boredDBA'
go

Logical Names & Physical Names

Logical names are the internal names of your data and log files, whereas the physical names are the names used by the file system.

This can be seen much more clearly in an example rather than in words. Here, "name" is the internal SQL Server logical name, and "filename" is the name used by the operating system...
create database boredDBA
on
(
    name = boredDBA_data,
    filename = 'D:\Microsoft SQL Server\Data\boredDBA.mdf'
)
log on
(
    name = boredDBA_log,
    filename = 'D:\Microsoft SQL Server\Logs\boredDBA_log.ldf'
)go

Initial Size & Autogrowth

This is an area you want to aim to get right at the start rather than deal with later as getting it wrong can be costly in terms of database performance due to both internal and external fragmentation of the database.

Internal fragmentation is an advanced topic to be discussed elsewhere, but external fragmentation is a less complex issue that I will cover with a quick example:

Let’s say you create a database, as above, with an initial size of 3MB and a growth rate of 1MB. The operating system will allocate you a 3MB chunk of hard drive and write your data file to it.

You then slowly start to load data to a total of 10MB. What happens is that once the database reaches 3MB, SQL Server requests another 1MB slot, so the operating system will allocate a 1MB piece of hard disk space. This happens a further 6 times. Therefore your database is now 10MB and you have 10MB of space on the hard disk.

However, the 10MB on the hard disk is in 8 separate files which, if the operating system is busy, could be considerably spread around the drive, therefore proving costly in terms of disk IO when trying to read and write data.

Planning ahead and starting with an initial size of 10MB would have solved this problem. Also, having a sensible autogrowth is crucial as if you’re loading 100MB of data at a time then growing by 1MB would be nonsensical.

Note: This type of logic is applicable to both the data and log files.

I would also recommend using fixed number growth instead of a percentage as this will help maintain internal fragmentation in the long run.

In my example, I’m going to be using my database for all manner of demonstrations and examples, I also have no shortage of space on my disk (another consideration, especially with regard to “unlimited growth”), so I am going to set my database as follows:
create database boredDBA
on
(
    name = boredDBA_data,
    filename = 'D:\Microsoft SQL Server\Data\boredDBA.mdf'
    size = 1000,
    maxsize = unlimited,
    filegrowth = 100
)
log on
(
    name = boredDBA_log,
    filename = 'D:\Microsoft SQL Server\Logs\boredDBA_log.ldf'
    size = 100,
    maxsize = unlimited,
    filegrowth = 50
)go

Recovery Model

This is something that you need to set when creating a new database. This defines how SQL Server will utilise the database log file.

I will do an article solely on Recovery Models at a later date (and place a link here).

For my purposes I do not need point in time recovery or complex backup strategies, therefore I’m going to change mine to SIMPLE…
alter database boredDBA set recovery simple
go
That's it. Check your database using sp_helpDB and you'll see that all the settings are as you had hoped:

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron