How to use OFFSET and FETCH in SQL Server 2012?

OFFSET and FETCH {New Addition in SQL Server 2012}

In SQL Server 2012, Microsoft has introduced many new T-SQL features and one of the best features is paging.

We have been doing this data paging in previous versions of SQL server by writing a stored procedure or a complex query.

The General syntax is as follows:

SELECT * FROM <table>

ORDER BY <columns>

OFFSET <EXPR1> ROWS

FETCH NEXT <EXPR2> ROWS ONLY

These two new arguments in the SELECT statement's ORDER BY clause that let you retrieve a fixed number of rows:

• OFFSET <EXPR1> ROWS, which you use to specify the line number from which to start retrieving results
• FETCH NEXT <EXPR2> ROWS ONLY, which you use to specify how many lines to

Order by Offset:

Have a look below query, which only using the OFFSET.

SELECT FirstName, LastName FROM Employees

ORDER BY EmployeeID --If you comment this line, query will not execute

OFFSET 5 ROWS

If we use only offset with order by clause, the query excludes the number of records we mentioned in OFFSET n Rows.

In the above example, we used OFFSET 5 ROWS; here SQL will exclude first 5 records from the result and display the rest of all records in the defined order by clause.

Order By Offset With FETCH NEXT:

Again have a look below query, which is using the OFFSET with FETCH NEXT.

SELECT EmployeeID, FirstName, LastName FROM Employees

ORDER BY EmployeeID 

OFFSET 5 ROWS

FETCH NEXT 5 ROWS ONLY

If we use offset with fetch next, we can define how many records we need to exclude and include in single query process.

In the above example, SQL excludes first 5 records and will pick up 5 records afterwards. In other words, we can say that whenever we need to do paging we need 2 things - 1st, the page no. and 2nd the no. of records in each page.

Here OFFSET is used for page number and FETCH NEXT is the number of records in each page.

If you use Fetch Next with order by clause without Offset, SQL will generate an error – ‘We cannot use Fetch Next without Offset’.

The ORDER BY clause is not valid in views, inline functions, derived tables, and sub-queries, unless either the TOP or OFFSET and FETCH clauses are also specified.

When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses.

OFFSET and FETCH are not supported in indexed views or in a view that is defined by using the CHECK OPTION clause.

OFFSET and FETCH can be used in any query that allows TOP and ORDER BY with the following limitations:

•  The OVER clause does not support OFFSET and FETCH.
• OFFSET and FETCH cannot be specified directly in INSERT, UPDATE, MERGE, and DELETE statements, but can be specified in a sub-query defined in these statements.
• For example, in the INSERT INTO SELECT statement, OFFSET and FETCH can be specified in the SELECT statement.
• In a query that uses UNION, EXCEPT or INTERSECT operators, OFFSET and FETCH can only be specified in the final query that specifies the order of the query results.
•  TOP cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope

Using OFFSET and FETCH as a paging solution requires running the query one time for each "page" of data returned to the client application.

For example, to return the results of a query in 10-row increments, you must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to 20 and so on. 

Each query is independent and not related to each other in any way. This means that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client application is responsible for tracking state.

Note: As a best practice, expert recommend that you use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows sent to a client application.

Working with Ranking and Over in SQL Server 2012

In SQL Server 2005/2008 Microsoft introduced lots of new function like Ranking of results has many benefits and it is used in several scenarios of T-SQL. 

Most common example of ranking is to ranking Sales person by their sales. This type of ranking requires you to assign same rank to all those Sales persons who generated the same sales amount, this is called tie.

Ranking Function: Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are non-deterministic. 

SQL Server 2008 provides the following ranking functions:

·       ROW_NUMBER: Returns sequential number of a row within a partition of a result set, starting at 1 for first row in each partition.

·         RANK: Returns the rank (bigint) of each row within the partition of a result set.

·         DENSE_RANK: Same as RANK but without any gap in the ranking number.

·     NTILE: Distributes the rows in an ordered partition into a specified number of groups. Or we can say NTILE is actually used to assign a batch numbers or group numbers to the given result.

Over Clause: Determines the partitioning and ordering of the rowset before the associated ranking function is applied. Ranking or aggregate functions can be used the OVER clause for partitioning and ordering. The OVER clause cannot be used with the CHECKSUM aggregate function. 

Each ranking function, ROW_NUMBER, DENSE_RANK, RANK, NTILE use the OVER clause. 

You can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function.

SELECT *, ROW_NUMBER() OVER (ORDER BY CarName) AS RowNumber FROM [dbo].[CarInfo]

The above query orders the rows in the table by column CarName and assigns sequential row numbers (much like an identity column) to these rows.  In actual this plan scans the table, sorts it on column CarName, and then the sequence project operator assigns sequential numbers to each row.

--Now if you want row number on grouping.

SELECT *, ROW_NUMBER() OVER (PARTITION BY EngineType ORDER BY CarName) AS RowNum FROM [dbo].[CarInfo]

Since column EngineType has two values (Petrol and Diesel), this query breaks the rows in the table into two groups and assigns row numbers separately to each group and then sort the result by CarName.  Execute the query and see the result.

RANK and DENSE_RANK functions are similar - both in functionality and implementation as ROW_NUMBER.

The difference is that while the ROW_NUMBER function assigns a unique (and ascending) value to each row without regard for ties in the ORDER BY values, the RANK and DENSE_RANK functions assign the same value to rows that have the same ORDER BY value. 

SELECT CarBodyType, CarCompany ,ROW_NUMBER() OVER (ORDER BY EngineType) AS 'Row Number' ,RANK() OVER (ORDER BY EngineType) AS 'Rank' ,DENSE_RANK() OVER (ORDER BY EngineType) AS 'DenRank' ,NTILE(4) OVER (ORDER BY EngineType) AS 'Quartile' ,EngineType FROM [dbo].[CarInfo]

Here you can notice how the ROW_NUMBER function ignores the duplicate values for column EngineType and assigns the unique integers from 1 to 11 rows while the RANK and DENSE_RANK functions assigns the same value to each of the pairs of duplicate rows. 

In above result have a close look how the RANK function counts the duplicate rows even while it assigns the same value to each duplicate row whereas the DENSE_RANK function does not count the duplicate rows. 

For example, both the RANK and DENSE_RANK functions assign a rank of 1 to the first 4 rows, but the RANK function assigns a rank of 5 to the fifth row - as it is the fifth row - while the DENSE_RANK function assigns a rank of 2 to the fifth row - as it contains the second distinct value for column EngineType. 

Note that the maximum value returned by the DENSE_RANK function is exactly equal to the number of distinct values in column B.

NTILE function breaks an input set down into N equal sized groups.  To determine how many rows belong in each group, SQL Server must first determine the total number of rows in the input set.  

If the NTILE function includes a PARTITION BY clause, SQL Server must compute the number of rows in each partition separately.  Once we know the number of rows in each partition, we can write the NTILE function.

SELECT *, COUNT(*) OVER (PARTITION BY EngineType) AS Cnt FROM [dbo].[CarInfo]

SELECT *, NTILE(2) OVER (PARTITION BY EngineType ORDER BY CarCompany) AS NTile FROM [CarInfo]

SELECT *, NTILE(4) OVER (ORDER BY CarCompany) AS NTile FROM [CarInfo]

Over [Enhancement with SQL Server 2012]:

You saw the uses of Over Clause in the above examples. Microsoft introduced the OVER clause in SQL Server 2005, its use in aggregate functions was pretty limited – you could use the aggregate function with just the PARTITION BY clause.

Generally when we apply aggregate functions in over clause, it will apply to all rows in that group/partition. Suppose, if we wants to restrict the number of rows, it involves some complexity. 

Assume, to calculate Next 5yrs total revenue for a company, the number of rows needs to be considered are always that row and next 4 rows.

In SQL Server 2012, with the enhancements to the OVER clause following the ANSI standard, the new functionality allow us to use the ORDER BY clause in the OVER clause with aggregate functions and also new ROWS and RANGE clauses were introduced to limit rows. 

The ORDER BY allow us define the order of rows processing and the ROWS/RANGE clauses put limits on the rows being processed in partition.

The row limit can be specified by several methods:

·         <unsigned integer> PRECEDING - fixed number of preceding rows

·         CURRENT ROW - representing current row being processed

·         UNBOUNDED PRECEDING - all previous records

·         <unsigned integer> FOLLOWING - fixed number of following rows

·         UNBOUNDED FOLLOWING - all rows following current row

--In the following example, it will calculate the sales for Current year + next 4 year

SELECT BusinessEntityID, TerritoryID

    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD

    ,DATEPART(yy,ModifiedDate) AS SalesYear

    ,CONVERT(varchar(20),SUM(SalesYTD)

OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)

        ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5;

--Suppose, to calculate sum of last 5 yrs revenue for every row, replace line 7 code.

ROWS BETWEEN 4 PRECEDING AND CURRENT ROW

--Suppose to calculate sum of all preceding years(Cumulative sum), replace line 7 code.

--With UNBOUNDED PRECEDING the result is that the window starts at the first row of the partition.

ROWS UNBOUNDED PRECEDING

--This is a static window which aligned to the partition 

Rows Between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING

--We can also use the Range as follows 

RANGE BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

RANGE CURRENT ROW

The RANGE clause can be only used with the UNBOUNDED limit and CURRENT ROW.

The difference between ROWS and RANGE clause is - ROWS works with physical rows and RANGE works with range of rows based on the current row value in the terms of ORDER BY clause.

This means that for ROWS clause the CURRENT ROW represents the only current row being processed.

For RANGE the CURRENT ROW represents all the rows with the same value in the fields specified in the ORDER BY clause within current partition as the current row being processed.

So if we use RANGE and multiple rows have the same rank in the terms of order within the partition, then all those rows will represent current row.

When there is no ROWS/RANGE clause specified after the ORDER BY clause, then the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is used by SQL Server.

ROWS or RANGE what should I use? 

Here little confusion to use either ROWS or RANGE, the answer comes from the definition how the ROWS and RANGE clauses works. As described, ROWS works with each unique rows and RANGE handles multiple rows with the same order position as current row.

So in case the combination of fields specified in the ORDER BY clause does not uniquely specify the order of rows, then you should use RANGE, as the processing order of rows with the same order position is not guaranteed. 

In case the rows are uniquely identified, then ROWS should be used as there are no rows with the same order in the partition.

Do you know about CTE (Common Table Expression) in SQL Server?

The CTE (common table expression) was introduced into standard SQL 2005 in order to simplify various classes of SQL Queries for which a derived table just wasn't suitable. 

CTE can use as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs, as shown in the following syntax:

[WITH <common_table_expression> [,...]]

cte_name [(column_name [,...])]

AS (cte_query)

SQL Server supports two types of CTEs—recursive and non-recursive. 

A non-recursive CTE is one that does not reference itself within the CTE. 

Recursive CTE is the process in which the query executes itself.

--Non-recursive

WITH

  cteTotSales (EmployeeID, TotSales)

  AS

  (

    SELECT EmployeeID, SUM(UnitPrice*OrderQty)

    FROM SalesOrder

    GROUP BY EmployeeID

  )

SELECT

  e.FirstName + ' ' + e.LastName AS FullName, ts.TotSales

FROM Employees AS e

  INNER JOIN cteTotSales AS ts

       ON e.EmployeeID = ts.EmployeeID

ORDER BY ts.TotSales DESC

Here, cteTotSales is ETE name, with two column then select query which will fill data in the cteTotSales, then we can refer it as table with other table as we did in the above query.

--recursive

WITH

       Emp_CTE AS (

              SELECT EmployeeID, FirstName, LastName, ManagerID

              FROM Employees WHERE ManagerID IS NULL

       UNION ALL

              SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID

              FROM Employees e

              INNER JOIN Emp_CTE e_cte ON e_cte.EmployeeID = e.ManagerID

       )

SELECT * FROM Emp_CTE

In the above example Emp_CTE is a Common Expression Table; the record for the CTE is derived by the first SQL query before UNION ALL. 

The result of the query gives you the EmployeeID which don’t have ManagerID.

Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID.  This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee. This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID.

Today's Question: Find out the difference between CTE and Temp Table?

15 Most Famous SQL Server Trigger's Interview Question

1. How to specify the Trigger orders in SQL Server?

If you are working with multiple triggers then you want to specify the order in which the trigger will fire. But in SQL server you have limitation for that, you can only specify the first and last trigger to execute.

The first and last trigger to execute can be specified using the sp_settriggerorder stored procedure. This provides some degree of control over the order in which multiple triggers execute, but it is important to understand that triggers other than the first and last are executed in an undefined order.

DML Triggers:

There can be only one First and one Last trigger for each statement on a single table.

If a First trigger is already defined on the table, database, or server, you cannot designate a new trigger as First for the same table, database, or server for the same statement_type. This restriction also applies Last triggers.

DDL Triggers:

If a DDL trigger with database scope and a DDL trigger with server scope exist on the same event, you can specify that both triggers be a First trigger or a Last trigger. However, server-scoped triggers always fire first. In general, the order of execution of DDL triggers that exist on the same event is as follows:

1.       The server-level trigger marked First.

2.       Other server-level triggers.

3.       The server-level trigger marked Last.

4.       The database-level trigger marked First.

5.       Other database-level triggers.

6.       The database-level trigger marked Last.

LOGON Triggers:

Multiple triggers can be defined on the LOGON event. Any one of these triggers can be designated the first or last trigger to be fired on an event by using the sp_settriggerorder system stored procedure. SQL Server does not guarantee the execution order of the remaining triggers.

Important:

If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset by using sp_settriggerorder.

Learn more on Trigger order with Replication.

Example:

--The following example specifies that trigger trgInsertMessage be the first trigger to fire after an INSERT operation occurs on the Employee table.

sp_settriggerorder @triggername= '[dbo].[trgInsertMessage]', @order='First', @stmttype = 'INSERT';

--if you want to make it last you can do it as follows:

sp_settriggerorder @triggername= '[dbo].[trgInsertMessage]', @order='Last', @stmttype = 'INSERT';

--For DDL Trigger

sp_settriggerorder @triggername= 'ddlDatabaseTriggerLog', @order='First', @stmttype = 'ALTER_TABLE', @namespace = 'DATABASE';

@namespace: When triggername is a DDL trigger, specifies whether triggername was created with database scope or server scope. If triggername is a logon trigger, SERVER must be specified. If not specified, or if NULL is specified, triggername is a DML trigger.

2. How to Enable / Disable the triggers in SQL Server?

Why we have to Enable / Disable a trigger? – Very simple, if you created some trigger for any table, and due to some region you don’t want to fire some of the trigger, so in place of delete, you can disable it and later as per requirement enable it.

Enabling a trigger does not re-create it. A disabled trigger still exists as an object in the current database, but does not fire. Enabling a trigger causes it to fire when any events on which it was originally programmed are executed.

Triggers are disabled by using DISABLE TRIGGER. DML triggers defined on tables can be disabled or enabled by using ALTER TABLE.

To enable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.

To enable a DDL or logon trigger with server scope (ON ALL SERVER), a user must have CONTROL SERVER permission on the server.

To enable a DDL trigger with database scope (ON DATABASE), at a minimum, a user must have ALTER ANY DATABASE DDL TRIGGER permission in the current database.

-- Using Diabled command – DML Trigger

DISABLE TRIGGER dbo.MyTest1 ON dbo.NewContact;

--OR, use the ALTER TABLE DDL command with the ENABLE TRIGGER or DISABLE TRIGGER option:

ALTER TABLE dbo.Employee

ENABLE TRIGGER --or DISABLE TRIGGER

trgInsertMessage;

--Disabling a DDL trigger

DISABLE TRIGGER ProtectObjects ON DATABASE;

To view the enabled status of a trigger, use the OBJECTPROPERTY() function, passing to it the object ID of the trigger and the ExecIsTriggerDisabled option:

SELECT OBJECTPROPERTY(

       OBJECT_ID('dbo.trgInsertMessage'), 'ExecIsTriggerDisabled');

       -- o: enabled and 1: disabled

3. How to list all the Triggers?

The following query lists all the triggers in the database based on the sys.triggers catalog view. Contains a row for each object that is a trigger, with a type of TR or TA.

SELECT Sc.name + '.' + Ob.name as [table], Tr.name as [trigger],

       CASE (Tr.is_instead_of_trigger )

              WHEN 0 THEN 'after'

              WHEN 1 THEN 'instead of'

       END AS type,

       CASE (Tr.is_disabled)

              WHEN 0 THEN 'enabled'

              WHEN 1 THEN 'disabled'

       END AS status

FROM sys.triggers Tr

       JOIN sys.objects Ob

              ON Tr.parent_id = Ob.object_id

       JOIN sys.schemas Sc

              ON Ob.schema_id = Sc.schema_id

WHERE Tr.type = 'TR' and Tr.parent_class = 1

ORDER BY Sc.name + '.' + Ob.name, Tr.name

-- you can also use You can use syscomments and sysobjects to list the triggers

select xtype, object_Name(Parent_obj) Parent,

B.Name, text

from syscomments A

JOIN sysobjects B ON

A.id = B.id

Where xtype = 'TR'

4. How Triggers Execute with transactions in SQL Server?

DML Triggers execute in response to any data modifications so its affect the transactional state in which they're fired. Knowing these effects can prevent conflicts with constraints, locking, and blocking on the affected tables.

So when a modification occurs, every transaction invokes various checks in the following order:

1.       IDENTITY INSERT check.

2.       Nullability constraint.

3.       Data-type check.

4.       The statement is executed (INSERT, UPDATE, or DELETE).

5.       If an INSTEAD OF trigger is defined on the table, it executes in place of the actual modification.

Execution of DML will stop here.

6.       Any constraints on the table are checked. If constraints are violated, an error is returned and the process is stopped.

Like, Primary-key constraint, Check constraints, Foreign-key constraints and many more.

7.       DML execution and update to the transaction log.

8.       AFTER trigger execution:

The first and last trigger to fire can be specified; the rest of the AFTER triggers execute in an undefined order.

If a rollback transaction is encountered, all modifications must be rolled back.

9.       The transaction is committed.

A trigger executes once per INSERT, UPDATE, or DELETE statement even if multiple rows are modified. It is common for UPDATE and DELETE statements to affect multiple rows, but this is also possible with INSERT statements.

The inserted and deleted tables contain a copy of all affected rows.

5. What are Inserted and Deleted Tables in Trigger?

These two tables also known as magic tables of SQL Server.

When a DML trigger is executing, it has access to two memory-resident tables that allow access to the data that was modified: Inserted and Deleted. These tables are available only within the body of a trigger for read-only access, and they are automatically created and managed by SQL Server.

The structures of the inserted and deleted tables are the same as the structure of the table on which the trigger is defined.

For insert operations, all inserted rows are available in the inserted table.

For delete operations, all deleted rows are available in the deleted table.

For update operations, an update can be thought of as a delete followed by an insert. Both the inserted and deleted tables are available within an UPDATE trigger. The deleted table stores the records before modification (old values), and the inserted table stores records after they were updated (new values).

6. What are differences between DDL Triggers and DML Triggers?

In some ways, DDL triggers are similar to DML triggers, but there are some significant differences. Both types of triggers are executed in response to events, but the types of events are different. DML triggers execute in response to INSERT, UPDATE, and DELETE statements, where DDL triggers execute in response to DDL operations. DML triggers are scoped at the table level, where DDL triggers can be scoped at the database or server-instance level.

DML Triggers	DDL Triggers
Respond to INSERT, UPDATE, and DELETE statements	Respond to CREATE, ALTER, and DROP statements
Scoped per table	Scoped at database or server instance
Create inserted and deleted tables to give information  about the event that occurred	Don't create any tables. Information about the DDL operation is retrieved using the EVENTDATA() function
Can create INSTEAD OF and AFTER triggers	DDL triggers always execute after a DDL operation has occurred. You cannot create INSTEAD OF DDL triggers.

7. What are differences between Instead of Triggers and After Triggers?

SQL Server 2012 has two kinds of transaction triggers (DML Triggers): instead of triggers and after triggers. They differ in their purpose, timing, and effect, as detailed:

Instead of Triggers	After Triggers
DML statement simulated but not executed.	DML statement Executed, but can be rolled back in trigger.
It will execute before PK and FK constraints.	If will execute after the transaction is complete, but before it is committed.
You can create it on View or Table.	You can create it only on Table.
Only one per table and per event.	You can have multiple.

8. Who can create and execute the Triggers?

Only the table or view owner, members of the sysadmin fixed server role, or the dbowner or ddldmin fixed database roles have permission to create, alter, drop, enable, or disable triggers.

A trigger executes under the security context of the owner of their parent object unless modified with the EXECUTE AS clause when the trigger is created or altered.

9. What are Nested and Recursive Triggers?

Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER.

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF triggers can be nested regardless of this setting.

Nested Triggers - Nesting means, when a trigger fired, it will also cause another trigger to be fired. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. Triggers (DML and DDL) can be nested to a maximum of 32 levels.

If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled with error message.

To disable nested triggers, set nested triggers option of sp_configure to 0 (off). The default configuration allows for nested triggers.

--stop executing all the nested triggers.

sp_CONFIGURE 'nested_triggers', 0

RECONFIGURE

Recursive Triggers - A recursive trigger is a unique type of nested AFTER trigger. If a trigger executes a DML statement that causes itself to fire, then it's a recursive triggerSQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE. Recursive triggers enable the following types of recursion to occur:

·         Indirect recursion: With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger TR2 then fires and updates table T1.

·         Direct recursion: With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

--Alternate way to stop Trigger Recursion

ALTER DATABASE databasename

SET RECURSIVE_TRIGGERS ON | OFF

Note: Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0. Note that nested triggers is a server option, whereas recursive triggers is a database option.

Note: Triggers execute within a transaction, a failure at any level of a set of nested triggers cancels the entire transaction, and all data modifications are rolled back. Include PRINT statements in your triggers so that you can determine where failure has occurred.

 10. What is Deferred Name Resolution?

In one of my training class, a developer asked me “when he creates any Stored Procedure  with table does not exist in database, the SP will creates without any error, while executing it gives run time error. However, if there is any valid table from database is referenced in SP with incorrect column name it will not let user create SP and gives error” why?.

SQL Server allows for Transact-SQL stored procedures, triggers, and batches to refer to tables that do not exist at compile time. This ability is called deferred name resolution.

Follow the link for more detail: http://msdn.microsoft.com/en-us/library/ms190686(v=sql.110).aspx

11. Difference between Triggers and Constraints?

Triggers support all of the functionality of constraints. But constraints are automatically enforced by the database engine and require less overhead than do triggers. Therefore, constraints should be used if possible, unless the application needs are more complex than can be enforced by constraints.

Enforcing entity integrity should always be done using constraints. Entity integrity refers to the uniqueness of rows. Primary keys and unique constraints are nearly always the best way of enforcing entity integrity.

When evaluating the use of constraints or triggers, always use constraints if they can meet the functional needs of the application. A trigger should be considered if custom error messages are required in response to error conditions, or in situations where more complex logic is required than constraints can provide.

12. Can we return Results in Triggers?

Yes, you can return results from trigger. There are three kinds of return values that are interesting:

·         Result sets

·         Raiserror messages

·         Rowcount messages

Also note that this is an overall system configuration. So it will disallow results from triggers in all of your databases.  It is never a good idea to return data from a trigger, but it is actually pretty useful for testing.

LOGON triggers always disallow results sets to be returned, and this behavior is not configurable. If a LOGON trigger does generate a result set, the trigger fails to execute and the login attempt that fired the trigger is denied.

We are enabling an advance feature ‘disallow results from triggers’ to 1 to enable this feature in the earlier version of SQL Server and it is also recommended by SQL Server to make it 1.

sp_configure 'disallow results from triggers', 1

GO

RECONFIGURE

When set to 1, the disallow results from triggers option is set to ON. The default setting for this option is 0 (OFF). If this option is set to 1 (ON), any attempt by a trigger to return a result set fails.

Note: The ability to return results from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this.

13. What are uses of WITH APPEND?

WITH APPEND only applies when you are running in 6.5 compatibility mode (which can be set using sp_dbcmptlevel). SQL Server 6.5 and prior did not allow multiple triggers of the same type on any single table. . Once one update (or insert, or delete) trigger was created, that was it — you couldn’t create another trigger for the same type of action.

With SQL Server 7.0, the rules changed substantially. No longer do we have to worry about how many triggers we have for one type of action query.

When running our database in 6.5 compatibility modes, though, we run into a problem - our database is still working on the notion that there can only be one trigger of a given type on a given table. WITH APPEND gets around this problem by explicitly telling SQL Server that we want to add this new trigger even though we already have a trigger of that type on the table.  

14. What are trigger options (WITH ENCRYPTION and EXECUTE AS)?

The DDL/DML trigger creation options, ENCRYPTION and EXECUTE AS, both ensure the security of system-level auditing triggers.

WITH ENCRYPTION: Obfuscates the text of the CREATE TRIGGER statement.

This works just as it does for views and sprocs. If you add this option, no one will be able to view your code (not even you!). This is particularly useful if you are going to be building software for commercial distribution, or if you are concerned about security and don’t want your users to be able to see what data you’re modifying or accessing. Obviously, you should keep a copy of the code required to create the trigger somewhere else, in case you want to re-create it sometime later.

Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.

WITH ENCRYPTION cannot be specified for CLR triggers.

The following DDL trigger will be encrypted when stored:

CREATE TRIGGER DDL_DDL_Level_Sample

ON ALL SERVER

WITH ENCRYPTION

FOR DDL_EVENTS

AS

Your code

EXECUTE AS: As with stored procedures, triggers can be executed under a different security context. Instead of the user who issued the DDL command that caused the DDL trigger to fire, the trigger can execute as one of the following security contexts:

·         Caller: Executes as the person executing the DDL command that fires the DDL trigger

·         Self: Executes as the person who created the DDL trigger

·         login_name: Executes as a specific login

Visit the link for more detail: http://msdn.microsoft.com/en-us/library/ms188354.aspx

15. Tell me few uses of DDL triggers.

1.    Saving a log of schema modifications for a database
2.    Saving a log of permission changes for a database
3.    Disallowing certain actions such as DROP statements for production databases
4.    Building an index modification log
5.     Auditing server-wide activity such as creating databases or linked servers.
6.     DDL triggers are useful for auditing server-level and database changes. They can easily find out which objects were changed, who changed them, and even undo unauthorized changes. 
7.    DDL triggers fire as the result of some server-level or database schema–level event. Where DML triggers respond to data changes, DDL triggers respond to schema changes. 
8.  Before developing DDL trigger, you must decide the scope of trigger - server or database level events will fire a trigger.