Injured but fearless Pigeon

Difference between Trigger and Stored Procedures

 This topics is picked from Microsoft Business Intelligence (Data Tools) - Triggers.

Difference between Trigger and Stored Procedures

Contents	Triggers	Stored Procedures
Implementation	Triggers can only be implemented on tables or views. A trigger cannot be written within a stored procedure.	Stored Procedure in independent code that can be specific to database. A stored procedure can be written within a trigger
Event and action	Triggers are event and action based to perform some specific tasks and they are free to run automatically whenever the event is occurred on the table on which the trigger is defined	They are not based on the event and action. They are not able to run automatically and depend on the other manual process.
Execution Mode	They execute implicitly.	A stored procedure must be called explicitly.
Schedule Mode	This is not possible for triggers because they are event and action based.	Stored procedures can be easily scheduled through a job to execute on a predefined time.
Transactions	Transactions are not allowed in Triggers.	Transaction statements like begin transaction, commit transaction, and rollback can be used easily inside a stored procedure.
Parameters	This feature is not applicable in triggers.	Stored Procedures may or may not have one or more input/ output parameters to perform some tasks.
Front end applications	This feature is not applicable for triggers.	Stored Procedures can be easily called from the front end (.asp files, .aspx files, .ascx files, .rdl etc.)
Return Value specific	A trigger cannot return a value.	Stored procedures can return values.
Debugging	Print commands cannot work inside the trigger.	Print commands can work inside a stored procedure for debugging purposes.
Nesting	Directly you cannot call another trigger within a trigger.	A stored procedure can be called from inside another stored procedure.
Task Specific	The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.	Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks.

To know more about SQL - Triggers.

SSRS – Chart based Drill-down Report

SSRS – Chart based Drill-down Report: A chart based drill down report is similar to row drill down report but main difference is that it’s based on the category groups and will...

Naughty Kids - Priceless childhood

SSRS - Column Based Drill Down

Microsoft Business Intelligence (Data Tools): SSRS - Column Based Drill Down Report: A Column Drill Down report is similar to row drill report but main difference is that it’s based on the columns and will provide the add...

SQL - Partition Indexes

Microsoft Business Intelligence (Data Tools): SQL - Partition Indexes: Table partitions are the most important feature of the SQL and provide a way to spread a single table over multiple partitions. In this w...

SQL – Indexes

Microsoft Business Intelligence (Data Tools): SQL – Indexes:

Indexes in a database are analogous to indexes that you find in a book. Indexes can help queries to find data quickly in a database. An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of a table. An index consists of column values from one table, and that those values are stored in a data structure.  The index also stores pointers to the table row so that other column values can be retrieved – read on for more details on that. It is important to understand that a database index does not store the values in the other columns of the same table.

SQL - RESOURCE_SEMAPHORE

What is RESOURCE_SEMAPHORE in SQL?

RESOURCE_SEMAPHORE: 

Occurs when a query memory request cannot be granted immediately because of other concurrent queries. High waits and wait times can indicate excessive number of concurrent queries or excessive memory request amount. 

COMMON for DSS like workload and large queries such as hash joins; must wait for memory quota (grant) before it is executed. 

Difference between Filtered and Indexed View

Difference between Filtered Index and Indexed View

Filtered indexes have the following advantages over indexed views-

Criteria	Filtered Index	Indexed Views
Maintenance Costs	Reduced index maintenance costs because the query processor uses fewer CPU resources to update a filtered index	The query processor uses more CPU resources to update a Indexed View.
Plan Quality	Improved plan quality because during query compilation, the query optimizer considers using a filtered index in more situations	They are not so benificial in the query optimizer as Filtered Index
Online index rebuilds	A Filtered indexes while they are available for queries	Online index rebuilds are not supported for indexed views
Non-unique indexes	Filtered indexes can be non-unique	Indexed views must be unique
Only One Table	A Filtered Index is created on column(s) of a particular table.	Index Views can be created on column(s) from multiple base tables.
Simple WHERE criteria	A Filtered Index can not use complex logic in its WHERE clause, for example the LIKE clause, NOT IN, OR and dynamic / non-deterministic predicates like WHERE col >= DATEADD(DAY, -1, GETDATE()) are not allowed, only simple comparison operators are allowed.	This limitation does not apply to indexed views and you can design your criteria as complex as you want.

Slow Indexes

The Oracle database is rather verbose in this respect and has three distinct operations that describe a basic index lookup:

INDEX UNIQUE SCAN

The INDEX UNIQUE SCAN performs the tree traversal only. The Oracle database uses this operation if a unique constraint ensures that the search criteria will match no more than one entry.

INDEX RANGE SCAN

The INDEX RANGE SCAN performs the tree traversal and follows the leaf node chain to find all matching entries. This is the fallback operation if multiple entries could possibly match the search criteria.

TABLE ACCESS BY INDEX ROWID

The TABLE ACCESS BY INDEX ROWID operation retrieves the row from the table. This operation is (often) performed for every matched record from a preceding index scan operation.

SQL - Indexes Interview Questions

How many clustered indexes can a SQL Server table have?
A table can contain only one clustered Index. 

Do I need to have a clustered index on every table?
SQL Server doesn’t need to have a clustered index on every table. In absence of a clustered index, SQL Server uses a heap as table store.

How many indexes on a SQL Server table can be queried as fast as a clustered index?
You are not limited to one index. Any index can be as fast as a clustered index. Adding INCLUDE columns to a non-clustered index doesn’t affect anything else than this particular index. There is no penalty that hurts all other indexes!

How many indexes can support an index-only scan?
The simple answer is: as many as you like. 

Why and where do we need full text index?

What is the difference between noise words and stop words? 


when should be we noise words vs stop words?

To know more on SQL - Indexing. Please visit SQL - Indexex

SQL - Sparse Columns and Column Sets

What are the Sparse Columns in SQL?

The SPARSE column property is a special, NULL-friendly column option - introduced with SQL Server 2008.  

Sparse column data storage is extremely efficient for NULL values. In fact, a NULL value requires no space at all in a sparse column - MSDN states that "when the column value is NULL for any row in the table, the values require no storage." However, the storage requirement for a non-NULL value is increased by up to 4 bytes when the SPARSE column property is used. Given that trade-off, Microsoft recommends not using sparse columns unless the percentage of NULL values in a column is high enough that a 20 percent to 40 percent storage savings gain would result. 

The ratio of NULLs to real values that would warrant implementing a sparse column differs for each data type.

Sparse Column Limitations 

A few things to keep in mind when considering using sparse columns: 

• Sparse columns cannot have default values, and must accept NULL values 

• A computed column cannot be SPARSE 

• Sparse columns do not support data compression 

• A sparse column cannot be a primary key 

Column Sets 

An interesting enhancement to a table that uses sparse columns is a column set. A sparse column set gathers all sparse columns into a new column that is similar to a derived or computed column, but with additional functionality – its data can be updated and selected from directly. A column set is calculated based on the sparse columns in a table, and it generates an untyped XML representation of all sparse columns and values (NULL or otherwise). 

What is an index

What is an index?

An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of a table. An index consists of column values from one table, and that those values are stored in a data structure.

What is full table scan?

Full table scan (also known as sequential scan) is a scan made on a database where each row of the table under scan is read in a sequential (serial) order and the columns encountered are checked for the validity of a condition.

What kind of data structure is an index?

B- trees are the most commonly used data structures for indexes. The reason B- trees are the most popular data structure for indexes is due to the fact that they are time efficient – because look-ups, deletions, and insertions can all be done in logarithmic time. And, another major reason B- trees are more commonly used is because the data that is stored inside the B- tree can be sorted.

Can you create a clustered index on a column with duplicate values?

Yes and no. Yes, you can create a clustered index on key columns that contain duplicate values. No, the key columns cannot remain in a non-unique state. Let me explain. If you create a non-unique clustered index on a column, the database engine adds a four-byte integer (a uniquifier) to duplicate values to ensure their uniqueness and, subsequently, to provide a way to identify each row in the clustered table.

What are the disadvantages of a hash index?

Hash tables are not sorted data structures, and there are many types of queries which hash indexes can not even help with. For instance, suppose you want to find out all of the employees who are less than 40 years old. How could you do that with a hash table index? Well, it’s not possible because a hash table is only good for looking up key value pairs – which means queries that check for equality.