More about statistics

  Cardinality estimates are a prediction of the number of rows in the query result. The query optimizer uses these estimates to choose a plan for executing the query. The quality of the query plan has a direct impact on improving query performance. To improve cardinality estimates for expressions, follow these guidelines:

•  Whenever possible, simplify expressions with constants in them. The query optimizer does not evaluate all functions and expressions containing constants prior to determining cardinality estimates. For example, simplify the expression ABS(-100) to 100.
•  If the query predicate uses a local variable, consider rewriting the query to use a parameter instead of a local variable. The value of a local variable is not known when the query optimizer creates the query execution plan. When a query uses a parameter, the query optimizer uses the cardinality estimate for the first actual parameter value that is passed to the stored procedure.
• Consider using a standard table or temporary table to hold the results of multi-statement table-valued functions. The query optimizer does not create statistics for multi-statement table-valued functions. With this approach the query optimizer can create statistics on the table columns and use them to create a better query plan.
• Consider using a standard table or temporary table as a replacement for table variables. The query optimizer does not create statistics for table variables. With this approach the query optimizer can create statistics on the table columns and use them to create a better query plan.      
• If a stored procedure contains a query that uses a passed-in parameter, avoid changing the parameter value within the stored procedure before using it in the query. The cardinality estimates for the query are based on the passed-in parameter value and not the updated value. To avoid changing the parameter value, you can rewrite the query to use two stored procedures.

As data changes in your tables, the statistics - all the statistics - will be updated based on the following formula:

·         When a table with no rows gets a row

·         When 500 rows are changed to a table that is less than 500 rows

·         When 20% + 500 are changed in a table greater than 500 rows

By ‘change’ we mean if a row is inserted, updated or deleted.

The statistics themselves are stored within your database in a series of internal tables that include sysindexes. You can view some of the information about them using the system views sys.stats and sys.indexes, but the most detail is gleaned using a function, DBCC SHOW_STATISTICS.

Just remember that, when you create an index, part of the task of creating that index is to create the statistics using a full scan of the data. Since a rebuild of an index is effectively a ‘drop and recreate’ of the index, the statistics are also recreated. If you are rebuilding indexes, I would not recommend updating the statistics since this is extra work for a less effective statistic.

Isolation Levels - Practice session

Sno	Session 1	Session 2	Iso Level	Output	Isolated?	Comments
1	begin tran select * from persontmp waitfor delay '00:00:10' select * from persontmp commit	begin tran update persontmp set age=25 where personname='Shilpa' commit tran	read committed	got different ages for shilpa	No	Dirty Reads
2	begin tran select * from persontmp waitfor delay '00:00:10' select * from persontmp commit	begin tran insert into persontmp values ('Veera','M','AP',36) commit tran tran	read committed	got different number of rows	No	Photom Rows
3	begin tran select * from persontmp waitfor delay '00:00:10' select * from persontmp commit	begin tran update persontmp set age=27 where personname='Shilpa' commit tran	repeatable read	got same age for both queries for shilpa	Yes	NA
4	begin tran select * from persontmp waitfor delay '00:00:10' select * from persontmp commit	begin tran insert into persontmp values ('Veera','M','AP',36) commit ran	repeatable read	got different number of rows	No	Photom Rows
5	begin tran select * from persontmp waitfor delay '00:00:10' select * from persontmp commit	begin tran insert into persontmp values ('Veera','M','AP',36) commit ran	serializable	got same number of rows	Yes	NA
6	begin tran select * from persontmp waitfor delay '00:00:10' select * from persontmp commit	begin tran update persontmp set age=37 where personname='Shilpa' commit tran	serializable	got same age for both queries for shilpa	Yes	NA

Rebuild and Reorganize indexes

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.

You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, and order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases.

Rebuilding an index can be executed online or offline. An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings.

Response Time vs. Total Time

        Response time is the time it takes for a query to return the first record. Total time is the time it takes for the query to return all records. For an interactive application, response time is important because it is the perceived time for the user to receive visual affirmation that a query is being processed. For a batch application, total time reflects the overall throughput. 

Many query operations can be performed without having to store intermediate results. These operations are said to be pipelined. Examples of pipelined operations are projections, selections, and joins. Queries implemented with these operations can return results immediately. Other operations, such as SORT andGROUP-BY, require using all their input before returning results to their parent operations. These operations are said to require materialization. Queries implemented with these operations typically have an initial delay because of materialization. After this initial delay, they typically return records very quickly.

Queries with response time requirements should avoid materialization. For example, using an index to implement ORDER-BY yields better response time than does using sorting.

 Index the ORDER-BY / GROUP-BY / DISTINCT Columns for Better Response Time

The ORDER-BY, GROUP-BY, and DISTINCT operations are all types of sorting. The SQL Server Compact query processor implements sorting in two ways. If records are already sorted by an index, the processor needs to use only the index. Otherwise, the processor has to use a temporary work table to sort the records first. Such preliminary sorting can cause significant initial delays on devices with lower power CPUs and limited memory, and should be avoided if response time is important.

In the context of multiple-column indexes, for ORDER-BY or GROUP-BY to consider a particular index, the ORDER-BY or GROUP-BY columns must match the prefix set of index columns with the exact order. For example, the index CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC)can help optimize the following queries:

• ... ORDER BY / GROUP BY "Last Name" ...
• ... ORDER BY / GROUP BY "Last Name", "First Name" ...

It will not help optimize:

• ... ORDER BY / GROUP BY "First Name" ...
• ... ORDER BY / GROUP BY "First Name", "Last Name" ...

For a DISTINCT operation to consider a multiple-column index, the projection list must match all index columns, although they do not have to be in the exact order. The previous index can help optimize the following queries:

• ... DISTINCT "Last Name", "First Name" ...
• ... DISTINCT "First Name", "Last Name" ...

It will not help optimize:

• ... DISTINCT "First Name" ...
• ... DISTINCT "Last Name" ...

Partitioned View - Rules

Table Rules

Member tables are defined in the FROM clause in each SELECT statement in the view definition. Each member table must adhere to these rules:

• Member tables cannot be referenced more than once in the view.
  
• Member tables cannot have indexes created on any computed columns.
  
• Member tables must have all PRIMARY KEY constraints on an identical number of columns.
  
• Member tables must have the same ANSI padding setting. For more information about the ANSI padding setting, 

Column Rules

Columns are defined in the select list of each SELECT statement in the view definition. The columns must follow these rules.

• All columns in each member table must be included in the select list. SELECT * FROM <member table> is acceptable syntax.
  
• Columns cannot be referenced more than once in the select list.
  
• The columns must be in the same ordinal position in the select list
  
• The columns in the select list of each SELECT statement must be of the same type

Partitioning Column Rules

A partitioning column exists on each member table and, through CHECK constraints, identifies the data available in that specific table. Partitioning columns must adhere to these rules:

• Each base table has a partitioning column whose key values are enforced by CHECK constraints. The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.
  
• The partitioning column cannot be an identity, default or timestamp column.
  
• The partitioning column must be in the same ordinal location in the select list of each SELECT statement in the view. For example, the partitioning column is always the first column in each select list, or the second column in each select list, and so on.
  
• Partitioning columns cannot allow nulls.
  
• Partitioning columns must be a part of the primary key of the table.
  
• Partitioning columns cannot be computed columns.
  
• There must be only one constraint on the partitioning column. If there is more than one constraint, SQL Server ignores all the constraints and will not consider them when determining whether or not the view is a partitioned view.
  
• There are no restrictions on the updatability of the partitioning columns.

Instead of Triggers

       INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. This trigger fires before the changes happen, but the rows that would have been affected are available in the Inserted and Deleted tables.

The AFTER trigger might appear less efficient because it sometimes needs to undo work that has already been done. If you think your table will have numerous violations that your trigger will need to correct, you might want to choose the INSTEAD OF trigger. However, if the vast majority of your updates will be acceptable, the INSTEAD OF trigger will have more work to do and thus be less efficient.

 You can have only one INSTEAD OF trigger on each table for each action (INSERT, UPDATE, and DELETE), and you can't set a firing order for INSTEAD OF triggers.  Another difference is that INSTEAD OF triggers can never be recursive, regardless of the setting of the recursive triggers database option.

Also, you can't combine INSTEAD OF triggers and foreign keys with CASCADE on a table. For example, if the Sales table has a foreign-key constraint that references the Titles table and specifies CASCADE as the response to DELETE operations on Titles, you'll get an error message if you try to create an INSTEAD OF trigger for DELETE on Sales. However, you can have INSTEAD OF triggers for INSERT or UPDATE. Similarly, if you already have an INSTEAD OF trigger on Sales, you can't alter the table to add a foreign-key constraint with the CASCADE action for the same data-modification operation.

Triggers III

         A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

DML triggers are frequently used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the ALTER TABLE and CREATE TABLE statements. However, DRI does not provide cross-database referential integrity. 

If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.

The first and last AFTER triggers to be executed on a table can be specified by using sp_settriggerorder. Only one first and one last AFTER trigger for each INSERT, UPDATE, and DELETE operation can be specified on a table. If there are other AFTER triggers on the same table, they are randomly executed.

 If a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.

UPDATE () tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.

When a trigger fires, results are returned to the calling application, just like with stored procedures. To prevent having results returned to an application because of a trigger firing, do not include either SELECT statements that return results or statements that perform variable assignment in a trigger.  If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the start of the trigger to prevent the return of any result sets.

While writing a trigger, keep an eye on the following points:

·                  If you write a single trigger for multiple events, be very careful to ensure that your trigger does not execute for unwanted events.

·                  When writing update trigger, always check if your desired column is updated by using IF UPDATE(ColumnName).

·                  Be very careful in querying INSERTED and DELETED table.

·                  Try to avoid cursor from the trigger.

·                  Ensure that your trigger is not creating any deadlock/Infinite loop on your database.

User defined Functions- Limitations and Restrictions

• User-defined functions cannot be used to perform actions that modify the database state.
• User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.
• User-defined functions cannot return multiple result sets. Use a stored procedure if you need to return multiple result sets.
• Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.
• User-defined functions cannot call a stored procedure, but can call an extended stored procedure.
• User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.
• SET statements are not allowed in a user-defined function.

RaiseError Vs Throw : Exception Handling

RAISERROR	THROW
Version of the Sql Server in which it is introduced?
Introduced in SQL SERVER 7.0. And as per BOL,Microsoft is suggesting to start using THROW statement instead of RAISERROR in New Applications. RAISERROR can’t be used in the Sql Server 2014’s Natively compiled Stored Procedures.	Introduced in SQL SERVER 2012. THROW statement seems to be simple and easy to use than RAISERROR. THROW statement can be used in the Sql Server 2014’s Natively Compiled Stored Procedure.
Can re-throw the original exception that invoked the CATCH block?
NO. It always generates new exception and results in the loss of the original exception details. Below example demonstrates this: BEGIN TRY   DECLARE @result INT --Generate divide-by-zero error   SET @result = 55/0 END TRY BEGIN CATCH --Get the details of the error --that invoked the CATCH block  DECLARE    @ErMessage NVARCHAR(2048),    @ErSeverity INT,    @ErState INT  SELECT    @ErMessage = ERROR_MESSAGE(),    @ErSeverity = ERROR_SEVERITY(),    @ErState = ERROR_STATE()  RAISERROR (@ErMessage,              @ErSeverity,              @ErState ) END CATCH RESULT: Msg 50000, Level 16, State 1, Line 19 Divide by zero error encountered.	YES. To Re-THROW the original exception caught in the TRY Block, we can just specify the THROW statement without any parameters in the CATCH block. Below example demonstrates this: BEGIN TRY   DECLARE @result INT --Generate divide-by-zero error   SET @result = 55/0 END TRY BEGIN CATCH     THROW END CATCH RESULT: Msg 8134, Level 16, State 1, Line 4 Divide by zero error encountered. With above example it is clear that THROW statement is very simple for RE-THROWING the exception. And also it returns correct error number and line number.
Causes the statement batch to be ended?
Example: In the below example all the statement’s after RAISERROR statement are executed. BEGIN TRY  DECLARE @RESULT INT = 55/0 END TRY BEGIN CATCH  PRINT 'BEFORE RAISERROR'; --Get the details of the error --that invoked the CATCH block  DECLARE   @ErMessage NVARCHAR(2048),   @ErSeverity INT,   @ErState INT  SELECT   @ErMessage = ERROR_MESSAGE(),   @ErSeverity = ERROR_SEVERITY(),   @ErState = ERROR_STATE()  RAISERROR (@ErMessage,              @ErSeverity,              @ErState )  PRINT 'AFTER RAISERROR' END CATCH  PRINT 'AFTER CATCH' RESULT: BEFORE RAISERROR Msg 50000, Level 16, State 1, Line 19 Divide by zero error encountered. AFTER RAISERROR AFTER CATCH	Example: In the below example no PRINT statement’s after THROW statement are executed. BEGIN TRY   DECLARE @RESULT INT = 55/0    END TRY BEGIN CATCH   PRINT 'BEFORE THROW';   THROW;   PRINT 'AFTER THROW' END CATCH   PRINT 'AFTER CATCH' RESULT: BEFORE THROW Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered.
CAN SET SEVERITY LEVEL?
YES. The severity parameter specifies the severity of the exception.	NO. There is no severity parameter. The exception severity is always set to 16. (unless re-throwing in a CATCH block)
Requires preceding statement to end with semicolon (;) statement terminator?
NO.	YES. The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
CAN RAISE SYSTEM ERROR MESSAGE? The SYS.MESSAGES Table will have both system-defined and user-defined messages. Message IDs less than 50000 are system messages.
YES. With RAISERROR we can raise the System Exception.	NO. With THROW we can’t raise the System Exception. But when it used in CATCH BLOCK it can Re-THROW the system exception. THROW 40655, ‘Database master cannot be restored.’, 1 RESULT: Msg 35100, Level 16, State 10, Line 1 Error number 40655 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647
CAN RAISE user-defined message with message_id greater than 50000 which is not defined in SYS.MESSAGES table?
NO. If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.	YES. The error_number parameter does not have to be defined in sys.messages.