Triggers allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.
Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the insert, delete and update operation. |
Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.
Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data. |
There are four types of triggers.
1. Insert
2. Delete
3. Update
4. Instead of |
SQL Server users constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints.
Unique, Default, Check, Primary Key, Foreign Key, Not Null. |
Delete statement removes rows of a table one by one & delete triggers on that table fires. But Truncate removes all rows by deallocating the data pages assigned to the table & only these deallocation are recorded in the transaction log. |
Primary key are used with Foreign key to enforce referential integrity. Unique constraints allows nulls to be inserted into the field. But there can't be null in Primary key. |
you can write following sql statement
select category.*, categoryparent.categoryparent from category, categoryparent where category.categoryparentid = categoryparent.autoid
I am assuming here that category.categoryparentid (foreign key) is the value of categoryparent.autoid (primary key). |
|
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table. |
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only. |
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course. |
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book. |
8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications". |
Implicit Transaction is the auto commit. There is no beginning or ending of the transaction.
Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction
Commit Transaction and
Rollback Transation
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction. |
You can use Having Clause with the GROUP BY function in query and WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. |
Use following code
Supported in SQL Server 2000 and 2005
exec sp_renamedb "test", "test1"
Supported in SQL Server 2005 and later version
ALTER Database "test1" Modify Name="test" |