SQL Server : Sample Scripts to create database, table & Index

It is very common to deal with situation where you have to create database and tables using T-SQL.

Here are the sample scripts for same which can be used as reference.

First check the data and log file location for any database if there is:

  

Use YourExistingDB

go

sp_helpfile

-- Create Dabase

use master

go

create database Test

on Primary (Name = test_data1, FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012B\MSSQL\DATA\test_data1.mdf'),

Filegroup Test_Secondary

(Name = test_Data_2, FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012B\MSSQL\DATA\test_Data_2.ndf'),

Filegroup Test_Archive

(Name = test_data3 , FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012B\MSSQL\DATA\test_data2.ndf')

Log ON (Name = Test_Log, FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012B\MSSQL\Log\test_log.ldf')

Go

-- Create Table

use Test

Go

Create Table dbo.Employee

(emp_id int,

emp_fname varchar (20),

emp_lname varchar (30))

on test_secondary

go

-- Create Index

use Test

Go
Create clustered index CIX_emp_Id on Test.dbo.Employee (emp_id)

Hope it will help you.

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)