Sunday 25 August 2013

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






No comments:

Post a Comment