1. When you create a trigger you have to identify event and action of your trigger but when you
create s.p you don't identify event and action
2.Trigger is run automatically if the event is occured but s.p don't run automatically but you have to
run it manually
3. Within a trigger you can call specific s.p but within a SP you cannot call a trigger.
4.Trigger execute implicitly whereas store procedure execute via procedure call from another block.
5.We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't
call a trigger from these files.
6. Stored procedure can take the input parameters, but we can't pass the parameters as an input to a
trigger.
Stored Procedure and Function in SQL Server
- Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
- Functions can have only input parameters for it whereas Procedures can have input/output parameters .
- Functions can be called from Procedure whereas Procedures cannot be called from Function.
-------------------------------------------------------------------------------------------------------------------
Indexes are used to speed-up query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow. On the other hand, if you create indexes, the database goes to that index first and then retrieves the corresponding table records directly.
There are two types of Indexes in SQL Server :
1. Clustered Index
2. Non-Clustered Index
Clustered Indexes
A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.
1. Clustered Index
2. Non-Clustered Index
Clustered Indexes
A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.
- A Table can have ONLY 1 Clustered Index.
- A Clustered Index always has Index Id of 0.
- A Primary Key constraint creates a Clustered Index by default.
- A Primary Key constraint can also be enforced by Nonclustered Index, You can specify the index type while creating Primary Key.
- If the table does not have Clustered Index it is referred to as a "Heap".
- The leaf node of a Clustered Index contains data pages of the table on which it is created.
- Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key.
A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.
When a query is issued against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes.
-
Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created.
- Nonclustered Indexes have Index Id > 0.
- A Unique Key constraint created a Nonclustered Index by default.
- A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key
- Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.
- A table may not have any Nonclustered Indexes.
- The leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row.
- When Clustered Index is not present leaf node points to Physical Location of the row this is referred to as RID. When a Clustered Index is present this points to Clustering Key (Key column on which Clustered Index is created).
