January 16, 2014

Create Database in SQL Server With Different Configuration Options




In this article, we will see how to create a database in SQL Server. We will also explore various options while creating a database.

Creating SQL Server databases can be achieved by two different ways as explained below -

1. You can make use of SQL Server Management Studio (SSMS) and Create Database wizard to create the database.

2. You can create the database programmatically by writing scripts.

If you are using the database wizard for creating the database, you will have limited options while creating your database. Most developers and administrators prefer to create the databases programmatically by writing scripts. These scripts can then be easily deployed on other SQL Server instances where you want to create the same database with the same configurations.

In this article, we will explore some configuration options as described below -
  1. Create database using database wizard
  2. Create database using script
  3. Create database with files and file groups
  4. Configuring File Stream during database creation
  5. Configuring recovery model during database creation
  6. Creating database with Collation option

Create database using database wizard

To start with, we will opt for SQL Server Management Studio [SSMS]. Once you open SSMS, in an Object Explorer window, we will right click the Databases folder and create a new database as shown below -

create-db-wiz

This step will show you a New Database window. Fill the required details as shown below -

newdb-window

You can give a database name and set the owner of the database. Also look at the database files. Check out the initial size of the database. In my case, it is 5 MB. The question is where is this size coming from?

Creating simple database using script

In SQL Server the "Model" [System Database] acts as a template for all the databases. Whatever the settings of Model, the same settings are applied to your new database. For example, let's add a simple table to our Model database and then create a PurchaseOrderDatabase. The table script is as shown below -

model-table

Note: All these scripts shown in this article can be downloaded from here

After creation of the table, create a database as shown in below script -

crdbscrpt

After creating the database, let's see how to select all the available databases under a given SQL Server instance. You can find the same under a view available under master database - sys.databases. The output of the sys.databases is as shown below -

sel-databases

You can now expand the PurchaseOrderDatabase using Object Explorer and check the tables folder. You will find "TestTable" created under Model database.

obexp1

This table got copied into our PurchaseOrderDatabase, as Model works as a template for the custom databases. You can also make use of 'sp_helpdb' stored procedure as shown below -

sphelp

The result of sp_helpdb stored procedure gives the output shown as above. It shows the information about the database as well as the files created for the database for both PurchaseOrderDatabase as well as Model database. So that’s about the Model database!

Create database with files and file groups

While writing the Create Database script in our query, we did not mention any other options. We will explore some options that can be used while creating the database. Let's first drop the PurchaseOrderDatabase so that we can create the same database with different options.

We will create the database using data files and file group options. Let's write the script as shown below to create a database PurchaseOrderDatabase -

db1-script

When you create a database, you have to provide at least two files - Primary file and Transaction Log File. Also you have to specify at least one file group while creating the database. In the above script, we have specified the size of the primary data file, maximum size of the database and file growth in Mega bytes. You can specify the size of the file in KB, MB, GB or TB. The default is MB.

You can also create the database using multiple data files with the extension .ndf which are also known as secondary data files as shown below -

db2-srpt

Configuring File Stream during database creation

Now let's create a database with File stream. File Stream is used to store unstructured data like documents and images on NTFS file system with the help of VARBINARY(MAX) type. By default, the file stream is not enabled on SQL Server. To enable the file stream access level on SQL Server, write the following command -

enable-filestrm-acclevl

After enabling the filestream access level, let's create a database with the file stream option as shown below -

file-streamdb

To test the file stream database, we will create a table with File Stream and will try to insert some data in the same, as shown below -

insertfs

While creating the database, you can specify the database recovery model. There are three options which you can avail for recovery model -

1. Simple - In this recovery model, there is no log backup. Also you cannot use Log shipping or point-in-time restores.
2. Full - In this recovery model, all backup types are permitted. Point-in-time recovery is possible.
3. Bulk Logged - In this recovery model, all backup types are permitted. No point-in-time recovery is supported. Recovery is possible at the end of any backup.

Configuring recovery model during database creation

We will now create a database with recovery options. But before that, we will see what is the default recovery option set on our database. For this, we will again make use of sys.databases system view as shown below -

existing-recmodel

The output is as shown below -

recoutput1

Below is a script for database creation with the recovery options -

recquery1

Creating database with Collation option

Finally, we have the last option to explore, i.e. create a database with collation option. SQL Server supports number of Collations.

Many cultures around the world use different character sets. Simply put, a collation encodes the rules that govern the proper use of characters for an alphabet or language.

Let's create a database with our choice of Collation. Before that we will also see what is the default collation set on our databases as well as all the available Collation with the help of sys.fn_helpcollations(). The queries are as below -

collation-query

The query for creating a database with the different collations is as shown below -

collation-dbscript

And that’s it!!

Conclusion

In this article, we have seen how to create a database in SQL Server using a wizard and by writing scripts using SQL Server Management Studio [SSMS] with various options like files and file groups, with file stream option, with recovery model and with collation option.

The scripts shown in this article can be downloaded from here


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter



 
  Feedback:

comments

0 Responses to "Create Database in SQL Server With Different Configuration Options"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions