Files and Commands in MSSql server

Basically MSSql server contains 3 meager domains like
1. development environment (using SSMS)
2. Administration part (in server level)
3. business intelligence (using BIDS or VSS)

1. Development Environment:

Topics:  

files: there are #types of files in MSSql server  

1. Primary data file: when ever we are supplied the files to the database with .mdf extension those files are Primary data files

2. Secondary data files : when ever we are supplied the files to the database with .ndf (files with other than .mdf, .ldf )extension those files are Secondary data files . 

3. Transaction log files : when ever we are supplied the file to the data base with .ldf extension  those files are Transaction log files

ex:

CREATE DATABASE TEST --user defined database

ON  --referce primary file group

(NAME='test_data',-- data file logical name

FILENAME='d:\data\test_data.mdf',-- data file physical file (primary data file)

SIZE=3,-- minimum data file size in MB/GB

FILEGROWTH=10%  -- 

),

(NAME='test_data1',

FILENAME='d:\data\test_data1.ndf',-- (Secondary data file)

SIZE=3,

FILEGROWTH=10%)

log on 

(NAME='test_log',

FILENAME='d:\data\test_log.ldf', --(transaction log file)

SIZE=3,

FILEGROWTH=10%)

 commands:

 DDL COMMAND:(DATA DEFINITION LANGUAGE): 

This is used for define the database Structures and schema  or relations

1. CREATE: to create objects in the database.                                                                              .  .    create table tbl_emp(id int ,name varchar(10))   
2. ALTER: alters the structure of the database objects.
3. DROP : Deletes the object from database.
4. TRUNCATE: Removes all records from a table , including all spaces allocated for the records are removed.
5. COMMENT - add comments to the data dictionary.
6. RENAME - rename an object.

DML COMMAND:(DATA MANIPULATION LANGUAGE) :

used for manipulate the data in the database 

1. SELECT - retrieve data from the a database
2. INSERT - insert data into a table
3. UPDATE - updates existing data within a table
4. DELETE - deletes all records from a table, the space for the records remain

DCL COMMANDS: (DATA CONTROL LANGUAGE): controlling data permissions

1. GRANT - gives user's access privileges to database
2. REVOKE - withdraw access privileges given with the GRANT command

TCL COMMANDS: (TRANSACTION CONTROL LANGUAGE)

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

1. COMMIT - save work done
2. SAVEPOINT - identify a point in a transaction to which you can later roll back
3. ROLLBACK - restore database to original since the last COMMIT
4. SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Syantax:

SET TRANSACTION ISOLATION LEVEL

    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

[ ; ]

EX: 

USE AdventureWorks2012;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT * 
    FROM HumanResources.EmployeePayHistory;
GO
SELECT * 
    FROM HumanResources.Department;
GO
COMMIT TRANSACTION;
GO

http://mssqldevelopersaran.blogspot.com