Thursday 14 November 2013

SQL SERVER Notes - 4

28. What is the difference between the Clustered and Non-Clustered index ?
       Clustered index is physically re-orders the rows, but the Non-Clustered refers the separate index table which have the pointer of the records. Table can have only one clustered index , but a table can have many non-clustered index.

29. How to create a trigger on update a value in a table ?
      Click here to read about update trigger

30. How to create a trigger on database and drop a trigger ?
      Now Let we see how to create a trigger on database , in this example we are going to create a trigger on database level to track the changes takesplace in db. so we are enabling the trigger for create , drop and drop a stored procedure in database. Make a entry in table. Information are getting from a EVENTDATA() function which returns a xml file and also fetching the information of client who is executing this statement .

CREATE TABLE EVENTSTABLE
(

      EventDate  DATETIME,
      eventtype  NVARCHAR(100),
      EventDDL   NVARCHAR(MAX),
      EVENTDATA  XML,
      dbname     VARCHAR(40),
      schemaname VARCHAR(20),
      OBJECTname VARCHAR(100),
      hostname   NVARCHAR(50),
      ipaddress NVARCHAR(60),
      program   NVARCHAR(300),
      loginuser NVARCHAR(100)

)

-- DDL trigger

CREATE TRIGGER trig_db
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN

SET NOCOUNT ON

DECLARE @xml XML = EVENTDATA()
DECLARE @ipaddress VARCHAR(32)

/* Fetching the ipaddress of the client who is executing the code */
SELECT @ipaddress = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID

INSERT INTO EVENTSTABLE
(
      EventDate ,
      eventtype ,
      EventDDL  ,
      EVENTDATA ,
      dbname        ,   
      schemaname,
      OBJECTname,
      hostname  ,
      ipaddress ,
      program   ,
      loginuser
)
SELECT
      CURRENT_TIMESTAMP
      ,@xml.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')
      ,@xml.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)')
      ,@xml
      ,DB_NAME()
      ,@xml.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)')
      ,@xml.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)')
      ,HOST_NAME()
      ,@ipaddress
      ,PROGRAM_NAME()
      ,SUSER_SNAME()



SET NOCOUNT OFF


END

SELECT * FROM EVENTSTABLE

-- Create a Stored procedure in db , information is automatically track in table.

CREATE PROCEDURE sp_proc
AS
BEGIN
SELECT '1'
END

SELECT * FROM EVENTSTABLE

For drop a trigger.

DROP TRIGGER trig_db


31. Usage of Orderby , Group by , Having , Row_number, Rank, Dense_Rank  in TABLE ?

CREATE TABLE employee
(
id INT IDENTITY(1,1),
NAME VARCHAR(30),
age INT,
salary INT,
department varchar(40)
)

CREATE PROCEDURE insert_record(@name varchar(30),@age int,@salary int,@department varchar(40))
AS
BEGIN

 INSERT INTO employee
 (
       name,
       age,
       salary,
       department
 )
 SELECT     @name,
             @age,
             @salary,
             @department

END

EXEC insert_record 'danie',33,1000000,'Finance'
EXEC insert_record 'Jim',23,2000000,'Technical'
EXEC insert_record 'Alex',27,4000000,'Technical'
EXEC insert_record 'Jhon',43,1700000,'HR'
EXEC insert_record 'Cruz',28,900000,'HR'
EXEC insert_record 'Misa',23,1500000,'Technical'

-- Order the employee based on name
SELECT * FROM employee ORDER BY name

-- Grouping the employee based on department
SELECT COUNT(age) AS 'no of employees',department
FROM employee
GROUP BY department
HAVING department IN ('Technical','HR')

-- Rank the employee based on age and partition based on age and department
SELECT name , RANK() OVER(PARTITION BY age,department ORDER BY age DESC) FROM employee


SELECT name , row_number() OVER(PARTITION BY age,department ORDER BY age DESC) FROM employee

SELECT name , Dense_Rank() OVER(PARTITION BY age,department ORDER BY age DESC) FROM employee 







No comments:

Post a Comment