MS SQL Server

What is Sql:

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.

Why SQL:

·         Allows users to access data in relational database management systems.

·         Allows users to describe the data.

·         Allows users to define the data in database and manipulate that data.

·         Allows to Embed with in other languages using SQL modules, libraries & pre-compilers.

·         Allows users to create and drop databases and tables

Instances in SQL:

An instance of the Database Engine is a copy of the sqlservr.exe executable that runs                              as an operating system service each instance manages several system databases and one or more user databases. Each computer can run multiple instances of the Database Engine.

*Instances are installations in sql server

*There are two types of instances

Default=The name of the computer is the name of the instance id

Named=You must provide a unique instance id

Instances Compete For

*Cpu

*Memory

*Disk space

*Single Instances Are Appropriate For

-Situations where having a single server for security and management is best

-Serving multiple databases to single user

-All data bases should be on the same version of sql server

-One instance of sql server can have 1000 different data bases

*Multiple Instances Are Appropriate For

-Situations where having single server for all databases

-Data bases need to be on different versions of sql server

-Same data base needs to be exist on different version of sql server for testing

*Multiple instances can be

-Mixed versions

*Sql server 2000, 2005,2008

-There can be only one default instance

*Instances name must be

-Unique

-First character must be a letter

-Should not contain underscores or #

-16 characters or less

-Cannot use the word 'default' any where

Types of Databases:

User Defined Databases Created by user

System Databases:

Master Database: Records all the system-level information for an instance of SQL Server.

MSdB Database: Is used by SQL Server Agent for scheduling alerts and jobs.

Model Database: Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

Temp Database: Is a workspace for holding temporary objects or intermediate result sets.

Creating Data Base:

Create database Ravi on primary

(

Name=Ravi,

Filename='d:\raju\sqlserverdb\data\ravi.mdf',

Size=50mb,

Maxsize=unlimited,

File growth=10%

)

Log on

(

Name=Ravi_log,

Filename='d:\raju\sqlserverdb\data\ravi_log.ldf',

Size=10mb,

Maxsize=50mb,

File growth=10%

)

Go

Data Types in Sql:

1) Char= Fixed length n-defines the string length and must be a value from 1to8000 the storage size is n bytes

2) Varchar= Variable length non-Unicode string data n defines the string length and can be a value from 1 t0 8000 max indicates that maximum storage size is 2^31-1 bytes

3) Nchar=Fixed length Unicode string data n defines the string length and can be a values from 1to 4000

4) Nvarchar=Variable length Unicode string data n defines the string length and can be a value from 1to 4000

5) Text=Variable length non-Unicode and string length of 2^31-1 bytes

6) Ntext=Variable length Unicode with a max string length of 2^30-1

Collations in Sequel Server:

*A collation encodes the rules governing the proper use of characters for either a language such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).

*Collation is the set of rules that determines how data is compared and sorted

*Collation cannot be changed without rebuilding databases

*Collation can be selected during installation

Each SQL Server Collation Specifies Three Properties:

*The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.

*The sort order to use for non-Unicode character data types (char, varchar, and text).

*The code page is used to store non-Unicode character data.

*Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types. And character width.

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and á is 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently  it is width sensitive.

SELECT name, description FROM fn_helpcollations()

Data languages in sql:

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - To create objects in the database
• ALTER - Alters the structure of the database
• DROP - Delete objects from the database
• TRUNCATE - Remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - Add comments to the data dictionary
• RENAME - Rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - Retrieve data from the a database
• INSERT - Insert data into a table
• UPDATE - Updates existing data within a table
• DELETE - Deletes all records from a table, the space for the records remain

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - Gives user's access privileges to database
• REVOKE - Withdraw access privileges given with the GRANT command

TCL

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

• COMMIT - Save work done
• SAVEPOINT - Identify a point in a transaction to which you can later roll back
• ROLLBACK - Restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Creation Of Tables:

Create Table Teacher information

(

No int,

Teacherid int,

Name varchar(20),

Class int

)

Insertion Of Values:

Insert Into Teacher information Values (1, 501,'jaya', 10)

Insert Into Teacher information Values (2, 501,'jaya', 9)

Constraints:

CHECK

 DEFAULT

 NOT NULL

 PRIMARY KEY

 FOREIGN KEY

 UNIQUE

AUTO INCREMENT

AUTO INCREMENT

Create Table Saaa

(

No int identity (2, 4),

Name varchar(20)

)

Insert Into Saaa(name) Values('hari')

Insert Into Saaa(name) Values('ravi')

Insert Into Saaa(name) Values('vishnu')

Insert Into Saaa(name) Values('rakesh')

Insert Into Saaa(name) Values('naveen')

Select * From Saaa

NOT NULL -Indicates that a column cannot store NULL value

UNIQUE -Ensures that each row for a column must have a Unique value

CHECK -Ensures that the value in a column meets a specific condition

DEFAULT -Specifies a default value when specified none for this column

NOT NULL

Create Table Man

(

Id int not null,

Name varchar(20)

)

Insert Into Man Values(1,'hari')

Insert Into Man(name)Values('sai')

Result--

(1 row(s) affected)

Msg 515, Level 16, State 2, Line 7

Cannot insert the value NULL into column 'id', table 'ravi.dbo.man'; column does not allow nulls. INSERT fails.

The statement has been terminated

UNIQUE

Create Table Man

(

Id int unique,

Name varchar(20)

)

Insert Into Man Values(1,'ravi')

Insert Into Man Values(1,'sai')

Result—

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 7

Violation of UNIQUE KEY constraint 'UQ__man__3213E83EE0F957CA'. Cannot insert duplicate key in object 'dbo.man'. The duplicate key value is (1).

The statement has been terminated.

CHECK

Create Table Man

(

Id int check(id>3),

Name varchar(20)

)

Insert Into Man values(4,'ravi')

Insert Into Man values(2,'sai')

Result—

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 8

The INSERT statement conflicted with the CHECK constraint "CK__man__id__6E01572D". The conflict occurred in database "ravi", table "dbo.man", column 'id'.

The statement has been terminated.

DEFAULT

Create Table Man

(

Id int,

Name varchar(20),

City varchar(20) Default 'gvd'

)

Insert Into Man(id, name) values(1,'hari')

Select * From Man

PRIMARY KEY

·         primary key does not accept null values

·         Only one primary key will be there in a table

·         It helps in the concepts of joins

·         It helps to remove bad data

Create Table Satya

(

Empid int constraint pk_satya_empid primary key ,

Empname varchar(10)

)

Alter Table Satya Drop constraint pk_satya_empid --drop constraint

Alter Table Satya Add constraint pk_satya_empid primary key(empid)--adding primary key constraint

FOREIGN KEY

·         Foreign key accepts multiple null values

·         More than one foreign key can be there in a table

Create table emptab

(

empid int primary key,

empname varchar(10),

empdeptno int constraint FK_emptab_deptno references dept(empdeptno),

empsal float

)

Table Hints In Sql:

*Sometimes your query executes very slowly. One of the ways to increase the queries performance is using the SQL Server table hints

Types:

NOLOCK or READUNCOMMITTED: Use the NOLOCK or READUNCOMMITTED table hint if you need the highest concurrency

Example-- SELECT * FROM titles WITH (NOLOCK)

*In DELETE/UPDATE queries it should be totally avoided as it can produce junk results.

READCOMMITTED:--Consider using the READCOMMITTED table hint to specify that statements cannot read data that has been modified but not committed by other transactions.

REPEATABLEREAD:--Use the REPEATABLEREAD table hint if you need more consistency

NOWAIT: Consider using the NOWAIT table hint if you need to return a message when a lock is encountered on the table.

XLOCK: Consider using the XLOCK table hint to take and hold exclusive locks until the transaction completes

READPAST:

Use the READPAST table hint if you need to return only the pasted rows.When the READPAST table hint is used, the locked rows will be skipped

Example--SELECT * FROM titles a JOIN title author b WITH (READPAST) ON a.title_id = b.title_id

ROWLOCK

This hint specifies that a shared row lock will be taken instead of shared page or table lock

Example--SELECT * FROM titles WITH (ROWLOCK)

Page lock:

The PAGLOCK hint specifies that a shared page lock will be taken instead of table lock each page size is 8kb,after that it will  be stored in segment and after that in chapter

SELECT * FROM titles a JOIN title author b WITH (PAGLOCK) ON a.title_id = b.title_id

TABLOCK:

The TABLOCK hint specifies that a shared lock will be taken on the table until the end ofthe Transact-SQL statement

SELECT * FROM titles a JOIN title author b WITH (TABLOCK) ON a.title_id = b.title_id

Padding in sql:

Padding is the process which adds the given data with the message to make confirm a certain length.

It can be to the left side or right side of the message. Suppose your message is rashed, and you want to add padding to the left side and the length you need is 10, and you want to fill the length with ‘O’then the result will be: ‘0000rashed’. In the same way, if you want right padding, the result will be ‘rashed0000’.

Left padding

Create function [dbo].[LPAD]

(

@pad_value varchar(500),

@pad_length INT,

@pad_with varchar(10)

 )

Returns varchar(5000)

As

BEGIN

Declare @value_result varchar(5000)

Select @value_result= replace(str(@pad_value,@pad_length),' ',@pad_with)

Return @value_result

END

Right padding

Create function [dbo].[RPAD]

(

@pad_value varchar(500),

@pad_length int,

@pad_with varchar(10)

)

Returns varchar(5000)

As

BEGIN

Declare @valueResult varchar(5000)

Select@valueResult=@pad_value+replace(replace(str(@pad_value,@pad_length),' ',@pad_with),@pad_value,'')

Return @valueResult

END

Temp Tables

·         Temp tables result can be used by multiple users

·         Temp table will be stored in tempdb database

·         Temp table can do all ddl operations it allows creating indexes, dropping, altering etc.

      

      Local Temporary Tables

Create table #Emp01

(

Empno int,

Name varchar(20)

)

Insert Into #Emp01 Values (1,'hari')

Insert Into #Emp01 Values(2,'sai')

Insert Into #Emp01 Values(3,'rao')

Insert Into #Emp01 Values(4,'ravi')

Insert Into #Emp01 Values(5,'pratap')

S   

       Select * From #Emp01

Global Temporary Tables

Create Table ##emp01

(

Empno int,

Name varchar(20)

)

Insert Into ##Emp01 Values (1,'hari')

Insert Into ##Emp01 Values(2,'sai')

Insert Into ##Emp01 Values(3,'rao')

Insert Into ##Emp01 Values(4,'ravi')

Insert Into ##Emp01 Values(5,'pratap')

Select * From ##Emp01

Local Temp Table

Local temp tables are only available to the current connection for the user and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Table variables

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.

Table variables cannot have Non-Clustered Indexes

You cannot create constraints in table variables

Declare @Emp01 Table

(

Empno int,

Name varchar(20)

)

Insert into @Emp01 Values (1,'hari')

Insert into @Emp01 Values (2,'sai')

Insert into @Emp01 Values (3,'rao')

Insert into @Emp01 Values (4,'ravi')

Insert into @Emp01 Values (5,'pratap')

Select * From @Emp01

Declare @@Emp01 Table

(

Empno int,

Name varchar(20)

)

Insert into @@Emp01 Values (1,'hari')

Insert into @@Emp01 Values (2,'sai')

Insert into @@Emp01 Values (3,'rao')

Insert into @@Emp01 Values (4,'ravi')

Insert into @@Emp01 Values (5,'pratap')

Select * from @@emp01

Select * Into #Emp10 from Emptab11

Select * From #Emp10

CTE:

Common Table Expression

A cte can be thought as a temporary result set that is defined with in the execution scope of a single select, insert update, and delete or create view statement

Select * from Tbldepartment

Create Table Tbldepartment

(

Deptid int,

Dname varchar(20)

)

Insert into Tbldepartment Values (1,'it')

Insert into Tbldepartment Values (2,'payroll')

Insert into Tbldepartment Values (3,'hr')

Insert into Tbldepartment Values (4,'admin')

Create Table Tblemployee

(

Id int,

Name varchar(20),

Gender varchar(20),

Deptid int

)

Insert into Tblemployee Values (1,'raju','male', 1)

Insert into Tblemployee Values (2,'sai','male', 1)

Insert into Tblemployee Values (3,'satya','male', 2)

Insert into Tblemployee Values (4,'srikan','male', 2)

Insert into Tblemployee Values (5,'harika','female', 3)

Insert into Tblemployee Values (6,'subbu','male', 4)

Select * From Tblemployee

With Employee count (dname,deptid,totalemployees)

As

(

Select tbldepartment.dname,tbldepartment.deptid,count(*) as totalemployees

From tblemployee

Join tbldepartment on tbldepartment.deptid=tblemployee.deptid

Group by tbldepartment.dname,tbldepartment.deptid

)

Select dname,totalemployees

From employeecount

Where totalemployees>=2

*To create common table expression we use keyword with---

*Cte is introduced in sql server 2005 a cte is a temporary result set that can be referenced with in a,

Select, insert, update, or delete statement that immediately follows cte

With employeecount(deptid,totalemployees)

As

(

Select deptid,count(*) as totalemployees

From tblemployee

Group by deptid

)

Select dname,totalemployees

From tbldepartment join employeecount

On tbldepartment.deptid=employeecount.deptid

Order by totalemployees

We can create multiple ctes using a single with clause

If cte is based on one base table then it is possible to update the underlying base table

With employee_name_gender

As

(

Select id,name,gender from tblemployee

)

Update employee_name_gender

Set gender='female' where id=1

If a cte is based on more than one table and if the update effects only one base  table then update is allowed

With Employeesbydepartment

As

(

select id,name,gender,dname from tblemployee

join tbldepartment

on tbldepartment.deptid=tblemployee.deptid

)

Update employeesbydepartment set gender='male' where id=1

If a cte is based on multiple tables and if the update statement effects more than one base table then Update is not allowed

With Employeebydepartment

As

(

Select id,name,gender,dname from tblemployeejoin tbldepartment on tbldepartment.deptid=tblemployee.deptid

)

Update Employeebydepartment set gender='male',dname='it' where id=1

Result= "View or function 'employeebydepartment' is not updatable because the modification affects multiple base tables."

Views:

·         Views are virtual or imaginary tables

·         View is just a select statement which has been saved in the database

·         Advantage of view is that it can join data from several tables

·         Views does not contain any data

·         Views reduce the effort of writing queries to access specify column every time

·         Views are created to hide some columns from the user for security reason

Create Table Rajj

(

Empno int,

Empname varchar(20),

Empsalary float

)

Insert into Rajj Values (1,'raju', 1000)

Insert into Rajj Values (2,'raj', 8000)

Insert into Rajj Values (3,'aju', 1500)

Insert into Rajj Values (4,'ramu', 6000)

Insert into Rajj Values (5,'rahul', 7000)

Insert into Rajj Values (6,'rajiv', 8000)

Insert into Rajj Values (7,'hari', 9000)

Select * From Rajj

Create View Vraj as

Select Empname,Empsalary from Rajj

Select * From Vraj

Delete Vraj

where Empname='raju'

Update Vraj

Set Empname ='rajs' where Empname='raj'

Insert Into Vraj Values('ravi',31000)

Drop view Vraj

Indexes:

An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes; they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).So you should only create indexes on columns (and tables) that will be frequently searched against

Types:

There are two types of indexes

1) Clustered Indexes----we can create only one index in clustered indexes

2) Non Clustered Indexes---we can create 0-255 indexes in Non clustered indexes

Clustered Index

Create Table Studenti

(

Studentno int,

Name varchar(20),

Class varchar(20)

)

Insert into Studenti Values (1,'raju', 10)

Insert into Studenti Values (2,'hari', 9)

Insert into Studenti Values (4,'rao', 11)

Insert into Studenti Values (7,'swathi', 12)

Insert into Studenti Values (3,'sagar', 8)

Insert into Studenti Values (6,'vinay', 8)

Insert into Studenti Values (5,'varma', 7)

Select * From Studenti

Create Clustered Index Ckj_Studenti_Studentno

on Studenti(studentno)

Select * From Studenti with (Index (ckj_studenti_studentno))

Drop Index Ckj_studenti_studentno on Studenti

Drop Table Student

Non Clustered Index:

Create Non clustered Index ckj_studenti_studentno on Studenti(studentno)

Create Non clustered Index ckj_studenti_class on studenti(class)

Select * From Studenti with (Index (ckj_studenti_studentno))

Create Table #Studenti

(

Studentno int,

Name varchar(20),

Class varchar(20)

)

Insert into #Studenti Values (1,'raju', 10)

Insert into #Studenti Values (2,'hari', 9)

Insert into #Studenti Values (4,'rao', 11)

Insert into #Studenti Values (7,'swathi', 12)

Insert into #Studenti Values (3,'sagar', 8)

Insert into #Studenti Values (6,'vinay', 8)

Insert into #Studenti Values (5,'varma', 7)

Drop Table #Studenti

Select * From #Studenti

Create Clustered Index Ck_#studenti_studentno

On #Studenti(studentno)

Select * from #studenti with (index (ck_#studenti_studentno))

·         we can create clustered and Non clustered indexes on temporary tables

·         we can create only clustered index in temporary variables

Declare @@Raju Table

(

Empno int,

Name varchar(20),

Place varchar(20),

Unique clustered (empno)

)

Insert into @@Raju Values (1,'raju','vsp')

Insert into @@Raju Values (3,'hari','hyd')

Insert into @@Raju Values (4,'sai','alm')

Insert into @@Raju Values (2,'ravi','bvrt')

Select * From @@Raju

Composite Index

An index that consists of more than one column is referred as composite index. Both clustered and non-clustered indexes can be composite index

IF EXISTS (Select name From sys.indexes Where name = N'IX_Employee_Composite')

DROP INDEX IX_Employee_Composite ON [Employee];

GO

CREATE INDEX IX_Employee_Composite ON [Employee] (Emp_Code,Name)

GO

Clustered Table:

Data is stored in order based on the clustered index key Data can be retrieved quickly based on the clustered index key, if the query uses the indexed columns Data pages are linked for faster sequential access Additional time is needed to maintain clustered index based on INSERTS, UPDATES and DELETES Additional space is needed to store clustered index tree These tables have a index_id value of 1 in the sys.indexes catalog view

 

Heap Table:

Data is not stored in any particular order

Specific data cannot be retrieved quickly, unless there are also non-clustered indexes Data pages are not linked, so sequential access needs to refer back to the index allocation map (IAM) pages

Since there is no clustered index, additional time is not needed to maintain the index since there is no clustered index; there is not the need for additional space to store the clustered index tree

These tables have a index_id value of 0 in the sys.indexes catalog view

To create a heap, create a table without a clustered index.

If a table already has a clustered index, drop the clustered index to return the table to a heap.

To remove a heap, create a clustered index on the heap.

Table Seek:

·         Table seek is good for large tables (large No. of  rows)

·         It is an operator for searching for Indexed Table.

Table Scan:

·         Table scan is good for the small table (few no. of rows like 10-50 rows)

·         It is an operator for any heap table (which is not having any clustered index). Record will search row by row.

Differences Between Table Seek And Table Scan

A table scan returns the entire table or index. A Table Seek efficiently returns rows from one or more ranges of an index based on a predicate

For Example

Select order date from orders where order key=2--

Table Scan

With a scan we need each row in the orders table ,evaluate the predicate “where order key=2” and if the predicate is true (i.e,if the row qualifies)return the row

Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total no of rows in the table thus a scan is efficient strategy if the table is small or if most of the rows qualify for the predicate

Table Seek

If we have an index on order key ,a seek may be a better plan, we use the index to navigate directly to those rows that satisfy the predicate Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total no of rows in the table. Thus a seek is generally a more efficient strategy if we have highly selective seek predicate that Is if we have a seek predicate that eliminates a large fraction of the table

JOINS CONCEPT:

*Joins are used to combine rows from 2 or more tables based on a common field between them

Types of Joins:

Inner Join: Returns all rows when there is at least one match in BOTH tables

LEFT Outer JOIN: Return all rows from the left table, and the matched rows from the right table

RIGHT Outer JOIN: Return all rows from the right table, and the matched rows from the left table

FULL Outer JOIN: Return all rows when there is a match in ONE of the tables

Self-Join: SELF JOIN is used to join a table to itself

*The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY.

CROSS APPLY: Returns only those rows from left table expression (in its final     output) if it matches with right table expression. It is same as inner join

OUTER APPLY: Returns all the rows from left table expression irrespective of its match with the right table expression.it is same as left outer join

Create Table Emptab11

(

Empid int ,

Empname varchar(10),

Empdeptno int ,

Empsal float

)

Create Table Dept11

(

Empdeptno int ,

Empdeptname varchar(10)

)

Select * From Emptab11

Insert into Emptab11 Values (1,'hari', 10, 2000)

Insert into Emptab11 Values (2,'jagan', 20, 3000)

Insert into Emptab11 Values (3,'psycho', 30, 6000)

Insert into Emptab11 Values (4,'satya', 40, 10000)

Insert into Emptab11 Values (5,'naren', 50, 12000)

Insert into Emptab11 Values (6,'vikas', 60, 14000)

Insert into Emptab11 Values (7,'rakesh', 20, 9000)

Insert into Emptab11 Values (8,'vikki', 30, 7000)

Insert into Emptab11 Values (9,'jai', 40, 4000)

Insert into Emptab11 Values (11,'ramu', 80, 1200)

Insert into Emptab11 Values (12,'kiran', 90, 1300)

Insert into Dept11 Values (10,'sales')

Insert into Dept11 Values (20,'devel')

Insert into Dept11 Values (30,'transport')

Insert into Dept11 Values (40,'manager')

Insert into Dept11 Values (50,'clerk')

Insert into Dept11 Values (70,'president')

Select * From Dept11

Select * From Emptab11

Inner Join Example

Select * from Emptab11 Inner Join dept11 on Emptab11.Empdeptno=Dept11.Empdeptno

Right Outer Join Example

Select * From Emptab11 eT Right Outer Join Dept11 d on et.Empdeptno=d.Empdeptno

Left Outer Join Example

Select * From Emptab11 et left Outer Join Dept11  d on et.Empdeptno=d.Empdeptno

Full Outer Join Example

Select * From Emptab11 et Full Outer Join Dept11  d on et.Empdeptno=d.Empdeptno

Self-Join Example

Create Table Manager11

(

Empid int,

Empname varchar(20),

Empmgr int

)

Insert into manager11 Values (101,'balu', 103)

Insert into manager11 Values (102,'vinod', 101)

Insert into manager11 Values (103,'kiran', 101)

Insert Into manager11 Values (104,'raju', 102)

Insert Into manager11 Values (105,'satya', 102)

Insert Into manager11 Values (106,'teja', 103)

Insert Into manager11 Values (107,'vinay', 104)

Select * From Manager11

Select b.Empname,a.Empname as Manager from Manager11 a join Manager11 b on a.Empid=b.Empmgr 

Cross Apply Example

Select * From Emptab11 d

CROSS APPLY

(

Select * From Dept11 E

Where E.Empdeptno = D.Empdeptno

) A

Outer Apply Example

Select * From Emptab11 d

Outer APPLY

(

Select * From Dept11 E

Where E.Empdeptno = D.empdeptno

) A

Difference Between Set And select In Sql Server:

·         SELECT: Designed to return data.

·         SET: Designed to assign values to local variables.

·         While comparing their performance in loop SELECT statement gives better performance then SET

·         SET is slower than SELECT. The reason is that each SET statement runs individually and updates on values per execution, whereas the entire SELECT statement runs once and update all the values in one execution.

·         SET can only assign one variable at a time, SELECT can make multiple assignments at once

·    When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all keeping the variable unchanged.

Synonyms in Sql:

Synonym provides an alternative name to the table

USE tempdb;

GO

CREATE SYNONYM My Employee

FOR  AdventureWorks2012.

GO

Subquery:

·         A subquery is simply a select statement that returns a single value and can be nested inside a select, update, insert or delete statement

·         Subqueries are always enclosed in parenthesis and are also called as inner queries

·         It is possible to nest a subquery inside another sub query

·         The columns from a table that is present only inside a subquery cannot be used in the select list of outer query

Create Table Tblproducts

(

Id int primary key,

Name varchar(20),

Description varchar(30)

)

Insert Into Tblproducts Values(1,'tv','31incheslcd')

Insert Into Tblproducts Values(2,'laptop','very thin acer laptop')

Insert Into Tblproducts Values(3,'desktop','high performence desktop')

Select * From Tblproducts

Create Table Tblproductsales

(

Id int primary key,

Productid int,

Unitprice int,

Quantitysold int

)

Insert Into Tblproductsales Values(1,3,450,5)

Insert Into Tblproductsales Values(2,2,250,7)

Insert Into Tblproductsales Values(3,3,450,4)

Insert Into Tblproductsales Values(4,3,450,9)

Select * From Tblproductsales

Select Id,Name,Description From Tblproducts where idnot In (select distinct productid from tblproductsales)

Using Joins

Select Tblproducts.Id,Name,Description

From Tblproducts

Left Join Tblproductsales

On Tblproducts.id=Tblproductsales.productid

Where Tblproductsales.productid is null

Select Name,(Select sum(quantitysold) From Tblproductsales where productid=tblproducts.id) as quantitysold

From Tblproducts Order By Name

Select Name,sum(quantitysold) as Qnty

From Tblproducts

Left Join Tblproductsales

On Tblproducts.id=Tblproductsales.productid

Group by Name

Types:

Correlated sub query

* If the subquery depends on the outer query for its values then that subquery is called  as correlated subquery in the where clause of the subquery below 'product ID' column gets its value from tblproducts table that is present in the outer query

Select Name,(select sum(quantitysold) From Tblproductsales Where   Productid=Tblproducts.id) As quantitysold

From Tblproducts

Order By Name

So, here the subquery dependent on the outer query for its value hence this subquery is a correlated subquery

Correlated subquery cannot be executed independently of the outer query

For Example

Select * From Tblproducts

Select * From Tblproductsales

Non Correlated Subquery

When subquery not dependent on outer query for its values then it is called non correlated subquery

Subquery can be executed independently .once the sub query is executed the results of the sub query

Then substituted with in parenthesis for use by the outer query .in an non correlated sub query

Sub query’s usually executed once

Nested Sub Queries

*A subquery can be nested inside other subqueries. SQL has an ability to nest queries within one another.

A subquery is a SELECT statement that is nested within another SELECT statement and which return intermediate results. SQL executes innermost subquery first, then next level -

For Example

Create Table Orders

(

Ord_num int,

Order_amount int,

Advance_amount int,

Ord_date varchar(20),

Cust_code varchar(10),

Agent_code varchar(20)

)

Insert into Orders Values (200100, 1000, 600,'2008-01-08','c00015','a003')

Insert into Orders Values (200110, 3000, 500,'2008-04-15','c00019','a010')

Insert into Orders Values (200107, 4500, 900,'2008-08-30','c00007','a010')

Insert into Orders Values (200112, 2000, 400,'2008-05-30','c00016','a007')

Insert into Orders Values (200113, 4000, 600,'2008-06-10','c00022','a002')

Insert into Orders Values (200102, 2000, 300,'2008-05-25','c00012','a012')

Select * From Orders

Create Table Customer

(

Cust_code varchar(10),

Cust_name varchar(20),

Cust_city varchar(20),

Working_area varchar(20),

Cust_country varchar(20),

Grade int,

Opening_amt int,

Receive_amt int,

Payment_amt int,

Outstanding_amt int,

Phone_no int,

Agent_code varchar(20)

)

Insert Into Customer Values ('C00013', 'Holmes', 'London', ‘London’,'UK', 2, 6000, 5000, 7000, 4000, 77979,'A003')

Insert Into Customer Values ('C00001', 'Micheal','New York','New York','USA', 2,3000,5000,2000, 6000, 7878, 'A008')

Insert Into Customer Values ('C00020', 'Albert', 'New York', 'New York', 'USA', 3, 5000, 7000, 6000, 6000, 9698,’A008’)

Insert Into Customer Values ('C00025','Ravindran','Bangalore','Bangalore','India', 2, 5000, 7000, 4000, 8000, 4523,'A011'

Insert Into Customer Values ('C00024','Cook','London', 'London','UK', 2, 4000, 9000, 7000, 6000, 9909,'A006')

Select * From Customer

Create Table Agent

(

Agent_code varchar(10),

Agent_name varchar(20),

Working_area varchar(20),

Commission int

)

Insert Into Agent Values('A007','Ramasundar','Bangalore',0.15)

Insert Into Agent Values('A003' ,'Alex','London', 0.13)

Insert Into Agent Values('A008','Alford','New York',0.12)

Insert Into Agent Values('A011', 'Ravi Kumar','Bangalore',0.15)

Insert Into Agent Values('A010','Santakumar', 'Chennai',0.10)

Select * From Agent

Select Ord_num,Ord_date,Order_amount,Advance_amount From orders

Where order_amount>2000

And ord_date<'2008-11-11'

And advance_amount <

Any (Select outstanding_amt From Customer Where Grade=3

And Cust_Country<>'India'

And opening_amt<7000

And EXISTS

(Select *From agent Where commission<0.20))

Stored procedures:

A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again a stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

More Number of SQL Statements Can Execute the Single Stored Procedure.

It will reduce the client/Server Network Traffic.

Types:

System Defined Stored procedures:

These stored procedure are already defined in Sql Server

sp_rename= It is used to rename an database object like stored procedure, views, table etc.

sp_changeowner=It is used to change the owner of an database object.

sp_help=It provides details on any database object.

sp_helpdb=It provide the details of the databases defined in the Sql Server.

sp_helptext=It provides the text of a stored procedure reside in Sql Server

sp_depends=It provide the details of all database objects that depends on the specific database object.

Create Table Tblemployee

(

Id int,

Name varchar(20),

Gender varchar(10),

Departmentid int

)

Insert into Tblemployee Values (1,'rao','male', 2)

Insert into Tblemployee Values (2,'sai','male', 1)

Insert into Tblemployee Values (3,'geeta','female', 1)

Insert into Tblemployee Values (4,'sanvi','female', 1)

Insert into Tblemployee Values (5,'satya','male', 1)

Insert into Tblemployee Values (6,'ravi','male', 2)

Insert into Tblemployee Values (7,'pranavi','female', 2)

Insert into Tblemployee Values (8,'bujji','male', 1)

Insert into Tblemployee Values (9,'shiva','male', 1)

Insert into Tblemployee Values (10,'hari','male', 2)

Select * From Tblemployee

Create procedure Spgetemployeebygender

@gender varchar(20),

@departmentid int

As

Begin

select name,gender,departmentid from tblemployee where gender=@gender and departmentid=@departmentid

End

Execute Spgetemployeebygender 'female',1

Create procedure Spgetemployeebygend

@gender nvarchar(20),

@employeecount int output

As

Begin

Select @employeecount=count (id) from tblemployee where gender=@gender

End

Declare @employeecount int

Exec spgetemployeebygend 'female',@employeecount out

Print @employeecount

Extended Stored Procedures

An extended stored procedure (xp) is a dynamic link library that runs directly in the address space of SQL Server and is programmed using the SQL Server Open Data Services API. You can run extended stored procedures from the Query Analyzer

Sp_MSgetversion:

This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version, run:
EXEC master..Sp_MSgetversion

Xp_subdirs:
This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with Xp_dirtree, Xp_subdirs returns only those directories whose depth = 1.
This is the example:
EXEC master..xp_subdirs ‘C: MSSQL7′

Xp_enumcodepages:
This extended stored procedure can be used to list of all code pages, character sets and their description for your SQL Server. To see this, list, run:
EXEC master..xp_enumcodepages

Xp_enumdsn:
This extended stored procedure returns a list of all system DSNs and their descriptions. To get the list of system DSNs, run:
EXEC master..xp_enumdsn

Xp_enumerrorlogs:
This extended stored procedure returns the list of all error logs with their last change date. To get the list of error logs, run:
EXEC master..xp_enumerrorlogs 


Xp_enumgroups:
This extended stored procedure returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:
EXEC master..xp_enumgroups


Xp_fileexist:
You can use this extended stored procedure to determine whether a particular file exists on the disk or not. The syntax for this xp is:
EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]
For example, to check whether the file boot.ini exists on disk c: or not, run:
EXEC master..xp_fileexist ‘c:boot.ini’

Xp_fixeddrives:
This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive. To see the list of drives, run:
EXEC master..xp_fixeddrives

Xp_getnetname:
This extended stored procedure returns the WINS name of the SQL Server that you’re connected to. To view the name, run:
EXEC master..xp_getnetname

Xp_readerrorlog:
This extended stored procedure returns the content of the error log file. You can find the errorlog file in the C: MSSQL7Log directory, by default. To see the text of the error log file, run:
EXEC master..xp_readerrorlog

Functions in sql:

1) Scalar functions

2) In line table valued functions

3) Multi statement table valued functions

Scalar Function:

May or may not have parameters but always return single (scalar) value the returned value can be any datatype, except text, ntext, image, cursor and timestamp

*To create a function we use the following syntax

           

Create Function Function_name(@parameter1 datatype,@parameter2 datatype)

Returns data_type

As

Begin

Function body

Return return_datatype

End

Create Function Bomonth

(

@dateIN  Datetime

)

Returns Datetime

As

Begin

Declare @Result Date

Select @Result = DATEADD (mm, -3, @datein)

Return @Result

End

 In line Table Valued Function:

Returns a table, we specify a table as a return type instead on any scalar datatype the function body is not enclosed between begin and end block, the structure of the   table that gets returned is determined by the select statement within the function

Create table Tblemployee

(

Id int,

Name varchar(20),

Dateofbirth varchar(20),

Gender varchar(20),

Departmentid int

)

Insert into Tblemployee1 Values (1,'sai','17/10/2005','male', 1)

Insert into Tblemployee1 Values (2,'ravi','10/2/2001','male', 1)

Insert into Tblemployee1 Values (3,'sakshi','4/4/2004','female', 2)

Insert into Tblemployee1 Values (4,'vishnu','3/2/2000','male', 3)

Select * From Tblemployee1

Create function Fn_employeebygender(@gender varchar(20))

Returns Table

As

Return (select id,name,dateofbirth,gender,departmentid from tblemployee1

Where gender=@gender)

To call The Function

Select * From Fn_employeebygender('male')

Select * From Fn_employeebygender('male') where name='sai'

Create table Tbldepartment1

(

Id int,

Departmentname varchar(20),

Location varchar(20),

Departmenthead varchar(20)

)

Insert into Tbldepartment1 Values (1,'it','london','rick')

Insert into Tbldepartment1 Values (2,'payroll','delhi','ron')

Insert into Tbldepartment1 Values (3,'hr','newyork','christie')

Insert into Tbldepartment1 Values (4,'otherdepartment','sydney','cindrella')

Select * From Tbldepartment1

Table Returned By Function Can Be Used In Joins

Select name,gender,departmentname from Fn_employeebygender('male') eJoin tbldepartment1 on d.id=e.departmentid

Multi Statement Table Valued Functions

Multi statement table valued functions are very similar to inline

Table valued functions, with a few differences

Create Table Tblemployee1

(

Id int,

Name varchar(20),

Dateofbirth varchar(20),

Gender varchar(20),

Departmentid int

)

Insert into Tblemployee1 Values (1,'sai','17/10/2005','male', 1)

Insert into Tblemployee1 Values (2,'ravi','10/2/2001','male', 1)

Insert into Tblemployee1 Values (3,'sakshi','4/4/2004','female', 2)

Insert into Tblemployee1 Values (4,'vishnu','3/2/2000','male', 3)

Select * From Tblemployee1

In line Table Valued Function

Create Function Fn_n_getemployee()

Returns Table

As

Return

(Select id,name,dateofbirth from tblemployee1)

To call The Function

Select * From Fn_n_getemployee()

We can update the inline table valued function

Update Fn_n_getemployee() set name='saivishnu' where id=1

Multi Statement Table Valued Function

Create Function Fn_m_getemployee()

Returns @table table (id int,name varchar(20),dateofbirth varchar(20))

As

Begin

Insert into @table

Select id, name, dateofbirth from tblemployee1

Return

End

To Call the Function

Select * From Fn_m_getemployee()

SQL Aggregate Functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

SQL Scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

Examples

·         Select Upper (ename) from emp

·         Select lower (ename) from emp

·         Select Ename from Emp where ename like'%s%'

·         Select Deptno,count(*) from emp group by deptno

·         Select Job,count(*) from emp group by job

·         Select Deptno,sum(sal) from emp group by deptno

·         Select Deptno,max(sal)from emp group by deptno

·         Select Job,sum(sal) from emp group by job

·         Select Job,min(sal) from emp group by job

·         Select Max (saL) FROM EMP

·         SELECT MIN (SAL) FROM EMP

·         SELECT AVG (SAL) FROM EMP

·         Select Avg(sal) from emp

·         Select Count (*) from emp

·         Select Sum (sal) from emp

·         Select Upper ('jagannadh')

·         Select lower ('hari')

·         Select Left ('hari',2)

·         Select Right ('abhinaya',4)

·         Select Ltrim(left('         psycho',10))

·         Select Rtrim(right('      haripsycho',7))

·         Select Isnumeric('raju')

·         Select Isnumeric(10)

·         Select Abs (-10*20.13*13.15)

·         Select Abs (10*20.13*13.15)

·         Select Reverse ('raju')

·         Select Getdate()

·         Select Convert (date,getdate())

·         Select Datepart(dd,'12/10/2014')

·         Select Datediff(YY,'1999/2/22','2010/2/27')

·         Select Datename(mm,getdate())

·         Select Dateadd(dd,2,getdate())

·         Select Dateadd(dd,2,convert(date,getdate()))

·         Select Round (457,3)

·         Select Round (477,2)

Conditional Statements:

Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a record. Using the EXISTS keyword is a great way to accomplish this

The EXISTS function takes one parameter which is a SQL statement. If any records exist that match the criteria it returns true, otherwise it returns false

If Exists

IF Exists (Select 1 from sys.objects where name='PersonSpouse' and type ='U')

BEGIN

Drop table dbo.PersonSpouse

END

GO

In SQL Server, the IF...ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE.

If Else

IF Exists (select * from sys.objects where name = 'person')

Print 'Table Already Exists'

Else

Print 'Need to create this table'

Go

Case Statement:

Evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Simple CASE Expression

This compares an expression to a set of simple expressions to find the result. This expression compares an expression to the expression in each WHEN clause for equivalency. If the expression with in the WHEN clause is matched, the expression in the THEN clause will be returned.

Syntax

1.      CASE expression

2.      WHEN expression1 THEN Result1

3.      WHEN expression2 THEN Result2

4.      ELSE ResultN

5.      END

Searched CASE expressions

This expression evaluates a set of Boolean expressions to find the result. This expression allows comparison operators, and logical operators AND/OR with in each Boolean expression.

Syntax

6.      CASE

7.      WHEN Boolean_expression1 THEN Result1

8.      WHEN Boolean_expression2 THEN Result2

9.      ELSE ResultN

10.  END

Create Table Raju

(

Id int,

Name varchar(20),

City varchar(20)

)

Insert into Raju Values (1,'raju','vzm')

Insert into Raju Values (2,'sai','vskp')

Insert into Raju Values (3,'ravi','cpp')

Insert into Raju Values (4,'jagan','alm')

Select Id, Case Name When 'raju' Then 'rajus' End,city From raju

Select Id, Case Name When 'raju' Then 'rajus' when 'sai' then 'sais' End,city From Raju

Loop Statement:

Sets a condition for the repeated execution of a SQL statement or statement block the statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords

Declare @c int,

@n int,

@fact int;

Select @c=1,

Select @n=6,

Select @fact=1

While (@c<=@n)

Begin

Select @fact = @fact * @c;

Select @c=@c+1

End

Select @fact

Triggers:

Types:

*In sql server there are 3 types of triggers

DML Triggers

DDL Triggers

LOG ON Triggers

*DML triggers are fired automatically in response to dml events (insert, update and delete)

DML Triggers Can Be Classified In To Two Types

1) After Triggers

2) Instead Of Triggers

*After trigger fires after the triggering action the insert, update and delete statements causes an after trigger to fire after respective statements completes execution

*Instead of trigger fires instead of triggering action the insert, update and delete statements

Causes an instead of trigger to fire instead of respective statement execution

Drop table tblemployee

Create Table Tblemployee

(

Id int,

Name varchar(20),

Salary int,

Gender varchar(20),

Deptid int

)

Insert into Tblemployee Values (1,'jhon', 5000,'male', 3)

Insert into Tblemployee Values (2,'sai', 3000,'male', 2)

Insert into Tblemployee Values (3,'ravi', 6000,'male', 1)

Insert into Tblemployee Values (4,'raki', 9000,'male', 1)

Select * From Tblemployee

Create Table Tblemployeeaudit

(

Id int,

Auditdata varchar(50)

)

Create trigger Tr_tblemployee_forinsert

On Tblemployee

For insert

As

Begin

Insert into Tblemployeeaudit values (1,'new employee with id is added at’)

End

Insert into Tblemployee Values (5,'ravi', 8000,'male', 4)

Select * From Tblemployeeaudit

Note--There will be inserted table in sql server created for the purpose of triggers it is available only in the context of trigger the structure of inserted table will be identical to the actual table

For Example

Create Trigger Tr_tblemployee_forinsert

On Tblemployee

For insert

As

Begin

Select * From Inserted

End

Note-triggers are actually special kind of stored procedures that execute automatically

In response to the dml events

Create Trigger Tr_tblemployee_forupdate

On Tblemployee

For update

As

Begin

Select * from Inserted

Select * from Deleted

End

Update Tblemployee set name='ramesh', salary=15000, gender='female' where id=3

Drop trigger Tr_tblemployee_forupdate

Instead Of Insert Trigger—

Select * From Tblemployee

Create Table Tbldept

(

Deptid int,

Deptname varchar(20)

)

Insert into Tbldept Values (1,'it')

Insert into Tbldept Values (2,'payroll')

Insert into Tbldept Values (3,'hr')

Insert into Tbldept Values (4,'consultant')

Select * from Tbldept

Drop table Tbldept

Create View VWemployeedetails

As

Select Id,Name,Gender,Deptname

From Tblemployee Join Tbldept

On Tbldept.deptid=Tblemployee.deptid

Drop View VWemployeedetails

Select * From VWemployeedetails

Insert Into VWemployeedetails Values (6,'rambo','male','hr')

Error

View or function 'vwemployeedetails' is not updatable because the modification affects multiple base tables.''

Create Trigger Tr_vwemployeedetails_insteadofinsert

On VWemployeedetails

Instead of insert

As

Begin

Select * From Inserted

Select * From Deleted

End

Alter Trigger Tr_vwemployeedetails_insteadofinsert on VWemployeedetails

Instead Of Insert

As

Begin

Declare @deptid int

Select @deptid=deptid from tbldept

Join inserted on

Inserted.deptname=tbldept.deptname

If (@deptid is null)

Begin

Raise error('invalid departmentname',16,1)

Return

End

Insert into tblemployee(id,name,gender,deptid)

Select id,name,gender,@deptid

From inserted

End

Insert Into VWemployeedetails Values (8,'rama','male','hr')

Instead Of Delete Trigger

Delete from vwemployeedetails where id in (1, 2)

Create Trigger Tr_vwemployeedetails_insteadofdelete

On VWemployeedetails

Instead Of Delete

As

Begin

Delete tbleemployee

From tblemployee join deleted

On tblemployee.id=deleted.id

End

Select * From VWemployeedetails

Cursors in Sql Server:

*Rdbms including sql server are very good at handling data in sets .for example the following 'update' query, updates a set of rows that matches the condition in the where clause at the same time

Ex---update tblproduct sales set unit price=50 where product id=101 however ,if there is ever need to process the rows on a row by row basis then we use cursors, cursors are very bad for performance and should be avoided always most of the time cursors can be replaced by joins

Create Table Tblproduct

(

Id int,

Name varchar(20),

Description varchar(30)

)

Insert into Tblproduct Values (1,'product1','product1description')

Insert into Tblproduct Values (2,'product2','product2description')

Insert into Tblproduct Values (3,'product3','product3description')

Insert into Tblproduct Values (4,'product4','product4description')

Insert into Tblproduct Values (5,'product5','product5description')

Insert into Tblproduct Values (6,'product6','product6description')

Insert into Tblproduct Values (7,'product7','product7description')

Insert into Tblproduct Values (8,'product8','product8description')

Insert into Tblproduct Values (9,'product9','product9description')

Select * From Tblproduct

Drop Table Tblproductsales

Create Table Tblproductsales

(

Id int,

Productid int,

Unitprice int,

Quantitysold int

)

Insert into Tblproductsales Values (1, 6, 55, 3)

Insert into Tblproductsales Values (2, 5, 565, 4)

Insert into Tblproductsales Values (3, 6, 505, 8)

Insert into Tblproductsales Values (4, 7, 955, 3)

Insert into Tblproductsales Values (5, 9, 355, 3)

Insert Into Tblproductsales Values (6, 9, 255, 6)

Insert into Tblproductsales Values (7, 8, 155, 3)

Insert into Tblproductsales Values (8, 6, 558, 2)

Insert into Tblproductsales Values (9, 7, 556, 1)

Select * From Tblproductsales

Select count(*) From Tblproduct

Select count(*) From Tblproductsales

Declare @productid int

Declare @name varchar(20)

Declare productcursor cursor for

Select id,name from tblproduct where id<=10

Open productcursor

Fetch next from productcursor into @productid,@name

While (@@fetch_status=0)

Begin

Print 'id='+cast(@productid as varchar(20))+ 'name-'+ @name

Fetch next from productcursor into @productid,@name

End

Close productcursor

Deallocate productcursor

DBCC Commands:

*Database Consistency Checker (DBCC) commands can gives valuable insight into what’s going on inside SQL Server system.

DBCC commands have powerful documented functions and many undocumented capabilities. Current DBCC commands are most useful for performance and troubleshooting exercises.to check the physical and logical consistency of a Microsoft SQL Server database. These commands are also used to fix existing issues .They are also used for administration and file management

*Some Of the Dbcc Commands

DBCC DBREINDEX

This statement is used to recreate the indexes for a particular table

USE ravi

GO

DBCC DBREINDEX (studenti, ckj_studenti_studentno)

GO

DBCC DBREPAIR

This statement is used to drop or delete a damaged database .now it is not being used  and it is replaced by drop database

Dbcc dbrepair(database_name, ltmignore)

 Database name: The name of the database for which you want to clear the secondary truncation point.

 Ltmignore Deactivates the secondary truncation point in the named database.

DBCC INDEXDEFRAG

This statement is used to defragment the clustered and secondary indexes Associated with the particular table

DBCC INDEXDEFRAG (ravi, studenti, ckj_studenti_studentno);

GO

DBCC SHRINKDATABASE

This statement is used to reduce the size of a database. This statement reduces the physical size of the database log file

  DBCC SHRINKDATABASE (SCHOOL, TRUNCATEONLY);

DBCC SHRINKFILE

This statement is used to reduce the size of a data file or log file of a particular database

DBCC SHRINKFILE (DataFile1, 7)

DBCC UPDATEUSAGE

This statement is used to correct inaccuracies in the page and row statistics in the views.

DBCC UPDATEUSAGE (ravi,[department])

GO

DBCC CLEANTABLE

This statement is used to remove spaces occupied by columns when they are removed.

DBCC CLEANTABLE (ravi,[department], 0)

WITH NO_INFOMSGS;

GO

DBCC DROPCLEANBUFFERS

This statement is used to drop clean buffers from the buffer pool

DBCC DROPCLEANBUFFERS [WITH NO_INFOMSGS]

WITH NO_INFOMSGS suppresses all informational messages.

DBCC FREEPROCCACHE

This statement is used to remove all elements from the procedure cache

DBCC INPUTBUFFER

This statement is used to display the last statement stored in the buffer

DBCC OPENTRAN

This statement is used to display information about the oldest open transaction

DBCC CHECKCONSTRAINTS

This statement is used to check integrity of specific constraints

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

DBCC CHECKDB

This statement is used to check integrity and allocation of specific    objects in a database. It also performs DBCC CHECKALLOC, DBCC CHECKTABLE and

DBCC CHECKCATALOG in that particular order

DBCC CHECKDB (ravi)

DBCC CHECKIDENT

This statement is used to check identity value of specified table

DBCC CHECKIDENT ([department])

DBCC CHECKTABLE

This statement is used to check the integrity of a table

DBCC CHECKTABLE ([department])

DBCC CHECKALLOC 

Checks the consistency of disk space allocation structures for a specified database

Dbcc checkalloc(ravi)