SqlTutorial-1 : Lesson-12 : Class-1 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Database Operations

Before creating database, it is better to know some aspects of Database Server that helps you to define database;

• The maximum size of a database name is 128 characters.
• The maximum number of databases managed by a single SQL server instance is 32,767.
• Each database can contain a maximum of 32,7725 files.
• SQL Server stores data on the data file in 8 kb blocks, known as pages. A Page is the smallest unit of (I/O) use to transfer data to and from disk.
• A row of data in SQL Server data is limited to 8060-byte maximum. With SQl Sever 2005, there are some exception to this limitation. If the table contains column that data types text/image, varchar, binary , SQL server store the exceeded data in a separate data structure.

Technical Information(Optional):

• A database is made up of at least two files: Data file and Log file (.LDF)
• Data file stores the database objects such as tables, indexes, stored procedures etc.
• Transaction log file tracks/records changes to the data
• There are two types of Data file: Primary Files(.mdf), Secondary (.ndf).
• For any database, there is one and only one PRIMARY file (.mdf) and LOG(.ldf) file but may have multiple SECONDARY files (.ndf).
• PRIMARY file contains entire database objects ( tables, views, indexes etc) and references to Secondary data files if exists. While the SECONDARY file is the optional splitted portion of data file. It contains excess data objects of PRIMARY file.
• By default, each database is a member of Primary file-group. However, you can define and select a different file-group.

• Typically, administrator creates databases. If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself.
• Many GUI-tools are avialable in market in order to define and customize database;
  • Microsoft's SQL Server Management Studio (SSMS) & VisualStudio-IDE
  • 3rd-party: Quest Toad | SQLPrompt | RazorSQL | SQuirreL | DbVisualizer | postgreSQL etc
  • If you are working SQl Server then, I recommend (Ms.SSMS) as it is fully-futured but it is commercial not free-bee. For free-bee it is better to use the Quest Toad it has so many features such as interoperable with any databases

1. Creating Database

Our first step is to create the database itself. Let's use the CREATE DATABASE command to set up our database:

Syntax

CREATE DATABASE db_name
             [ON [PRIMARY] file_spec1 {, file_spec2} ...]
             [LOG ON file_spec3 {, file_spec4 ...]
             [COLLATE collation_name]
     [FOR {ATTACH|ATTACH_REBUILD_LOG}]

Examples:

(1) Simple Example: Creating new database with default file specifications.

 CREATE DATABASE pubs
 GO

1. If you are not specify the PRIMARY and LOG files, then SQL server implicitly provided it at default location. %Drive:\ Program Files\Microsoft SQL Server\MSSQL.1\DATA\k.mdf
2. You can find the locations of all database files using sqlcmd> select name, physical_name from sys.master_files; go

(2)Complex Example: Creating new database with file specifications:

CREATE DATABASE Archive
  ON PRIMARY
    ( NAME = Arch1, FILENAME = 'c:\archdat1.mdf',SIZE = 100MB,
      MAXSIZE = 200, FILEGROWTH = 20%),
    ( NAME = Arch2, FILENAME = 'c:\archdat2.ndf', SIZE = 100MB,
      MAXSIZE = 200, FILEGROWTH = 20%)
  LOG ON
    ( NAME = Archlog1,FILENAME ='c:\archlog1.ldf',  SIZE = 100MB,
      MAXSIZE = 200,FILEGROWTH = 20%)

1. For the cause of NTFS security, C drive may not allow SQL to create file. So, set the permission anyone to READ and WRITE C drive in ACLs
2. A database has one and only one PRIMARY(.mdf) and one and only one LOG(.ldf) file but may has multiple SECONDARY(.ndf) files

2. Altering Database

Syntax:

ALTER DATABASE db_name
  ADD FILE file_spec1 [TO FILEGROUP group_name1]
    | ADD LOG FILE file_spec2
    | REMOVE FILE 'file_name'
    | MODIFY FILE (NAME = old_name, NEWNAME = new_name ...)
    | CREATE FILEGROUP group_name2
    | DROP FILEGROUP filegroup_name3
    | SET option_specifications [WITH terminations]

Where Option_Specifications:
       { ONLINE | OFFLINE | EMERGENCY } //state options
       { SINGLE_USER | RESTRICTED_USER | MULTI_USER } // user_access_option
       { READ_ONLY | READ_WRITE} // update option

 

  Examples:

(1) Adding PRIMARY FILE to existing database.

create database projects;
ALTER DATABASE projects
    ADD FILE (NAME=projects_dat1, FILENAME = 'C:\DATA\projects1.mdf',
      SIZE = 10, MAXSIZE = 100,FILEGROWTH = 5)

(2) Increasing the size of database file.

ALTER DATABASE projects
  MODIFY FILE(NAME = 'projects_data1,SIZE = 20MB)

(3) Renaming the database.

ALTER DATABASE oldDB MODIFY NAME = newDB;
 // OR //
 EXEC sp_renameDB 'oldDB','newDB'

(4) Enabling / Disabling database (Making database OFFLINE | ONLINE)

SELECT DATABASEPROPERTYEX('pubs', 'status')    //ONLINE
ALTER DATABASE pubs SET OFFLINE; GO 
SELECT DATABASEPROPERTYEX('pubs', 'status') //OFFLINE

USE pubs // Error! Cannot be open it is OFFLINE
ALTER DATABASE pubs SET ONLINE
Use pubs // Changed database Context to pubs

(5) Making Database READ_ONLY / READ_WRITE

ALTER DATABASE
pubs SET READ_ONLY
/////  Checking ////
CREATE TABLE table1(col varchar(2)); //Error!
 // OR //
SELECT DATABASEPROPERTYEX('pubs', 'updateability')

 ALTER DATABASE pubs
 SET READ_WRITE 
CREATE TABLE table1(col varchar(2)); // OK

(6) SINGLE_USER | RESTRICTED_USER | MULTI_USER

ALTER DATABASE pubs SINGLE_USER
// OR //
ALTER DATABASE pubs SET SINGLE_USER WITH NO_WAIT
Note: SINGLE_USER NO WAIT means no simultaneous multiple connections are allowed.

3. Using databases:

Example-1: Using existing database (here pubs)

CREATE DATABASE pubs
GO
USE pubs 
GO
drop database pubs;
GO

4. Finding existance of databases

You can determine existance of particular databases by in two ways:

Examples

(1) Determining existing of DB by query Sys-tables

If EXISTS(select * from sys.dattabases where name='pubs')
BEGIN raiserror('Dropping existing pubs database ....',0,1);
  DROP database pubs;
END

(2) Determining existing of DB by its IDENTIFIER

IF DB_ID('pubs') IS NOT NULL
BEGIN
  raiserror('Dropping existing pubs database ....',0,1);
  DROP database pubs;
END

5. Dropping databases.

You can drop the databases using DROP-Clause.

Examples

(1) Simple Drop: Droping existing database

CREATE DATABASE pubs;
GO
USE pubs;
GO
drop database pubs;
GO

(2) Complex-Drop: Droping with validating database

It is recommended that validate existing of database prior to drop it.

If EXISTS(select * from sys.dattabases where name='pubs')
BEGIN
  raiserror('Dropping existing pubs database ....',0,1);
  DROP database pubs;
END
ELSE
   raiserror('Database doesn't exist....',0,1);