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 .
31. Usage of Orderby , Group by , Having , Row_number, Rank, Dense_Rank in TABLE ?
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
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