Add Constraints to the Existing Table

ALTER TABLE B ADD CONSTRAINT ID2_2  FOREIGN KEY(ID2) REFERENCES DBO.A(ID1)  ON DELETE CASCADE ON UPDATE CASCADE
Aboe:
B->Table Name
ID2_2 -> Foreign Key Name
DBO.A(ID1) -> A-Reference Table.ID1 That table Primary Key
I am implemented cascade delete and Update  while creating the constraints.

Dont use SP_ Prefix while creating the StoredProcedure-SQL Server

Reason:

Dont use sp_ prefix while creating the Storedprocedure.Because it degrades the system performance.while creating like this it first scan the master database…After that only it returns to check our concern database.

Purpose of SET NO COUNT ON inside the stored Procedure-SQL Server

If u dont use SET NO COUNT ON inside the stored procedure the DONE_IN_PROC Message each time send to the caller..For the Concern insert,delete,update operation taken.
This is the Performance issue.In order to overcome the use
SET NO COUNT ON inside the Stored Procedure

Create Transaction Inside the Stored Procedure-SQL

The Query as Follows:

CREATE TABLE A(ID1 INT IDENTITY,VALUE INT CONSTRAINT PK_A PRIMARY KEY NOT NULL)

CREATE TABLE B(ID2 INT IDENTITY,VALUE2 INT CONSTRAINT PK_B PRIMARY KEY NOT NULL)

CREATE TABLE C(ID3 INT IDENTITY,VALUE3 INT CONSTRAINT PK_C PRIMARY KEY NOT NULL)
Procedure:
CREATE PROCEDURE VALUE_INSERT
(
@FIRST INT=NULL,
@SECOND INT=NULL,
@THIRD INT=NULL)
AS
SET NOCOUNT ON
BEGIN TRAN T1
INSERT INTO A VALUES(@FIRST)
IF(@@ERROR!=0)
BEGIN
ROLLBACK [...]

Types Of Cursor-SQL Server

1.Insensitive Cursor
2.Scroll Cursor
3.Forward-Only
4.Static Cursor
5.Keyset Cursor
6.Dynamic Cursor
1.Insensitive Cursor:
Insensitive cursor Example without using the looping structure
select * from table1
declare cursor_table1 insensitive cursor for select name1,salary from table1//Cursor declaration
create procedure p_table1 as
declare @name1 varchar(50)declare @salary integer
open cursor_table1 fetch next from cursor_table1 into @name1,@salary print @name1 print @salaryclose cursor_table1
exec p_table1
2.Scroll Cursor:

In This scroll cursor we can have more options [...]

Inner,Outer,Cross Join-SQL Server

create table table11(id integer identity,name varchar(50),address varchar(50))
insert into table11 values(‘d’,’coimbatore’)
select * from table11
create table table22(id integer identity,name varchar(50),salary integer)
insert into table22 values(‘q’,3000)
select * from table22
outer join three types they are left,right,full outer join
Left Join:
select table11.name,table22.name from table11 left join table22 on table11.name=table22.name
Right join:
select table11.name,table11.address ,table22.name from table11 right join table22 on table11.name=table22.name
Full Outer join:
select table11.name,table11.address,table22.name,table22.salary [...]

To Select Odd and Even number of records From Table-SQL Server

To select odd numbers of records:

select distinct id1,name1,name2 from a where (id1%2)=1
To Select even numbers of record:

select distinct id1,name1,name2 from a where (id1%2)=0

Here id1 is a Identity column of a Table a
id1-Identity Column
a-TableName

To get Current Time in SQL Server

The query as Follows:

select CONVERT(char(5), CURRENT_TIMESTAMP,8)

Update Salary Based on Particular Range-SQL Server

update table1
set salary=case when salary between 1000 and 1500 then salary+10
when salary between 2000 and 2500 then salary+56
when salary between 3000 and 3500 then salary+10
when salary between 5000 and 5500 then salary +10
end
table-TableName
salary-Column Name

Types Of Constraints-SQL Server

1.Primary Key
2.Foreign key or references integirity
3.Unique
4.Check
5.NOt null
6.Default
The unique key allows only one null values.But the primary key does,nt allows a null values
By default The primary key is clustered index.
By default the unique key is NOn Clustered index.
Clustered Index:
It alters the way in which the data is srored in the database.
Non Clustered Index:
It does not alters [...]