Where to use SQL Server Cursor

                Cursor can be used when you need to manipulate data in a set on a row-by-row basis, however you can also use T-SQL WHILE loop, CASE expression and some system defined stored procedure like sp_MSforeachdb, sp_MSforeachdb etc.

IN SQL Server the cursor can be implemented by 6 step process as:

1. Declare cursor

DECLARE ins_cursor CURSOR

FOR

    Select statement…………….

2. Open cursor

OPEN ins_cursor

3. Fetch row from the cursor

FETCH NEXT FROM ins_cursor 

INTO @fileno,……….variable list

4. Process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

….

…..

…

FETCH NEXT FROM ins_cursor 

INTO @fileno@fileno,……….variable list

END

5. Close cursor

CLOSE ins_cursor

6. Deallocate cursor

DEALLOCATE ins_cursor;

I have used cursor first time when I need to insert data from one table of one database into tables of another database. So I am explaining same example here

--declare variables to use in logic

DECLARE @RID INT

DECLARE @FILENO VARCHAR(50),@ISSUEDATE DATETIME,@REQDATE DATETIME,@RCNVARCHAR(50),@FILEREMARKS VARCHAR(200)

--decalare cusrsor

DECLARE INS_CURSOR CURSOR

    FOR

    --select statement

    SELECT FILENO,REQCONTROLNO,CONVERT(DATETIME,REPLACE(REQDATE,'-','/'),103),CONVERT(DATETIME,REPLACE(ISSUEDATE,'-','/'),103) ,REMARKS FROM ISSUE WHERERECEIVEDDATE IS NULL

    --open cursor

OPEN INS_CURSOR

--Fetch row from the cursor

FETCH NEXT FROM INS_CURSOR

INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

--process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

--insert into first table

INSERT INTO FISS1.DBO.FILEREQUEST(REQDATE,REQSTATUS,RCN,CORDSTATUS,FILEREMARKS)

VALUES (@REQDATE,1,@RCN,1,@FILEREMARKS)

----assigning primary key(identity) value

SELECT @RID=@@IDENTITY

--insert into 2^nd table with foreign key @rid

INSERT INTO FISS1.DBO.ISSUE(REQID,FILENO,PRIORITY,PURPOSE,ISSUEDATE,STAUS)

VALUES(@RID,@FILENO,1,'GENERAL',@ISSUEDATE,1)

FETCH NEXT FROM INS_CURSOR INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

END

--close cursor

CLOSE INS_CURSOR;

DEALLOCATE INS_CURSOR;

Cursor Recommendations:

·         It is better to avoid using cursor because they consume memory for execution, so performance is less.

·         If you using cursor, then you should always close cursor after using it.

Further Reading

http://msdn.microsoft.com/en-us/library/ms181441.aspx

Thanks:queryingsql.com

How to find SQL Server Version,Edition,Server Name?

SQL Server provides a System Defined function SERVERPROPERTY(propertyname) .

By using this function you can find a number of things

Property Name	Description	syntax
Edition	Return SQL Server edition installed on machine.	select ServerProperty('edition')
EditionID	return Edition ID	select ServerProperty('editionid')
InstanceName	Return instance name if it is not default.In case of default return Null.	select ServerProperty('InstanceName')
ProductVersion	return Product version	select ServerProperty('ProductVersion')
BuildClrVersion	return version of the .NET framework Common Language Runtime (CLR)	select ServerProperty('BuildClrVersion')
EngineEdition	return 1 = Desktop 2 = Standard 3 = Enterprise 4 = Express 5 = SQL Azure	select ServerProperty('EngineEdition')
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, or an error.	select ServerProperty('IsClustered')
MachineName	Return machine name	select ServerProperty('MachineName')
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated	select ServerProperty('ResourceLastUpdateDateTime')
ProductLevel	Returns Level of the version of SQL Server instance 'RTM' = Original release version 'SPn' = Service pack version 'CTP', = Community Technology Preview version	select ServerProperty('ProductLevel')

Basics of Triggers in SQL Server

SQL Triggers:

A trigger is a procedural code like stored procedure that is automatically executed on certain actions (like insert, delete, and update) occurs on table, view or database. CREATE TRIGGER statement is used to create triggers.
A trigger can be fire on following statements

DDL statements like

• drop table, 
• alter table, 
• create table.

DML Statements like

• INSERT, 
• DELETE, and 
• UPDATE.

If you want to see triggers on your database.you can use below query

1. select * from databasename.sys.triggers   

Why use Triggers?
There are several reasons to use triggers.some of them listed below

• Log database activity
• Implement Business Rule
• Enforce referential integrity. Example: When you delete a customer, you can use a trigger to delete corresponding rows in the orders table.
• Triggers can access both old and changed values on insert, delete, update operations.

Syntax to write trigger

1. CREATE TRIGGER trigger_name  
2. ON  
3. table_name | view_name  
4. FOR | AFTER | INSTEAD OF  
5. [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]  
6.  AS  
7.  BEGIN  
8.  --INSERT INTO dbo.CustomeractivityHistory.......  
9.  --Your action statements.........  
10.  END  

Example:
The following trigger in below example is used to log insert activity of CustomerHistory table.

1. CREATE TRIGGER TRG_CUSTOMER_INSERT  
2.  ON dbo.CustomerDetails  
3. AFTER INSERT  
4. AS  
5. BEGIN  
6.  INSERT INTO dbo.CustomerHistory(UserID, ActionTaken, ActivityDate, Remarks)  SELECT i.CustomerID, 'INSERT', getdate(), 'Customer inserted into table'  FROM  Inserted i  
7. END    

DDL Triggers

DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

Example 1: 
The below trigger in example is used to prevent database from Drop on SERVER.

1. CREATE TRIGGER trg_preventionDrop  
2. ON all SERVER  
3.  FOR DROP_DATABASE  
4. AS  
5. print 'Drop Database not permitted'  
6. ROLLBACK tran  
7. Go   

Example 2:

to prevent table  from Drop on SERVER

1. CREATE TRIGGER trg_preventDropTable  
2.  ON all SERVER  
3. FOR DROP_TABLE  
4. as  
5. print 'Drop table not allowed'  
6. ROLLBACK tran    
7. Go  

DML Triggers

A DML trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view. DML triggers are used to enforce business rules when data is modified and to extend the integrity checking logic of Microsoft SQL Server constraints, defaults, and rules.

AFTER Trigger

Example:

1. CREATE TRIGGER TRG_CUSTOMER_INSERT  
2.  ON dbo.CustomerDetails  
3. AFTER INSERT  
4. AS  
5. BEGIN  
6.  INSERT INTO dbo.CustomerHistory(UserID, ActionTaken, ActivityDate, Remarks)  SELECT i.CustomerID, 'INSERT', getdate(), 'Customer inserted into table'  FROM  Inserted i  
7.  END    

INSTEAD OF Trigger

It is like BEFORE trigger in other RDBMS systems.In this case trigger fires before insert/update/delete actions occurred means no constraints checked.

1. CREATE TRIGGER TRG_CUSTOMER_INSERT    
2. ON dbo.CustomerDetails   
3. INSTEAD OF INSERT   
4. AS   
5. BEGIN    
6. INSERT INTO dbo.CustomerHistory(UserID, ActionTaken, ActivityDate, Remarks)  SELECT i.CustomerID, 'INSERT', getdate(), 'Customer inserted into table'  FROM  Inserted i    
7. END  

LOGON Triggers

Logon trigger are special type of trigger that fires on logon events occures in sql server. 

Syntax

1. CREATE TRIGGER trigger_logon_name   
2.  ON  
3.  ALL SERVER  [WITH ENCRYPTION] {FOR|AFTER} LOGON AS sql_statement [1...n ]    

---

Example 1: Write a Trigger to log activity on Database

Create a table that stores activity on database.

1. CREATE TABLE [dbo].[databaseActivityInfo]( [EVNTYPE] [varchar](100) NULL, [OBJNAME] [varchar](100) NULL, [EVNTDT] [datetime] NULL, [DBVERSION] [varchar](30) NULL ) ON [PRIMARY] GO  

Now write a trigger on database as

1. CREATE TRIGGER [TRG_db_customer]   
2. ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS   
3. AS  
4. DECLARE @v_Evntype VARCHAR(MAX);   
5. DECLARE @v_Objname VARCHAR(MAX);   
6. SELECT @v_Evntype = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'),  
7.        @v_Objname = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')   
8.        INSERT INTO databaseActivityInfo(EVNTYPE ,OBJNAME,EVNTDT,DBVERSION) VALUES(@v_Evntype,@v_Objname,GETDATE())   
9.  GO  

Example 2:
Trigger to prevent drop,alter table in database.

1. CREATE TRIGGER TRG_CUSTOMER_ALT_DROP   
2. ON   
3. DATABASE FOR DROP_TABLE, ALTER_TABLE   
4. AS   
5. PRINT 'You must disable Trigger " TRG_CUSTOMER_ALT_DROP " to drop or alter tables!'  
6.   
7. ROLLBACK