Ques: 1. What
is an Index? Explain the different types of index.
Ans: An index is a performance enhancement method that allows faster retrieval of records from the table. An index creates an entry for each value thus making data retrieval faster.
While creating an index, we should remember the columns which will be used to make SQL queries and create one or more indexes on those columns.
Following are the available indexes.
a. Clustered index:
It sorts and stores the rows of data in the table or view, based on its keys. These are the columns included in the index definition. There can be only one clustered index per table because sorting of data rows can be done only in one order.b. Non-clustered index:
It contains the non-clustered index key value and each key value entry, in turn, has a pointer to the data row. Thus a non-clustered index contains a pointer to the physical location of the record. Each table can have 999 non-clustered indexes.c. Unique Index:
This indexing does not allow the field to have duplicate values if the column is unique indexed. It can be applied automatically when a primary key is defined.
Ques: 2. What are Constraints?
Explain the different Constraints available in SQL?
Ans: These
are the set of rules that determine or restrict the type of data that can go
into a table, to maintain the accuracy and integrity of the data inside the
table. Following
are the most frequent used constraints, applicable to a table:
- <NOT NULL> It restricts a column from holding a NULL value. It does not work on a table.
- <UNIQUE> It ensures that a field or column will only have unique values. It is applicable to both column and table.
- <PRIMARY KEY> uniquely identifies each record in a database table and it cannot contain NULL values.
- <FOREIGN KEY> It is used to relate two tables. The FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables.
- <CHECK CONSTRAINT> It is used to restrict the value of a column between a range. It performs a check on the values, before storing them into the database. It’s like condition checking before saving data into a column.
- <DEFAULT> It is used to insert a default value into a column.
Ques: 3. What are Triggers? What
are its benefits? Can we invoke a trigger explicitly?
Ans: The
trigger is a type of stored program, which gets fired automatically when some
event occurs. We write a Trigger as a response to either of the following
event:
- A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
- A database definition (DDL) statement (CREATE, ALTER, or DROP).
- A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
- SQL allows defining Trigger on the table, view, schema, or database associated with the event.
Following
are its benefits:
- Generating some derived column values automatically.
- Enforcing referential integrity.
- Event logging and storing information on table access.
- Auditing.
- Synchronous replication of tables.
- Imposing security authorizations.
- Preventing invalid transactions.
It
is not possible to invoke a trigger explicitly. It gets invoked automatically
if an event gets executed on the table having an association with the trigger.
Ques: 4. What
is the purpose of isolation levels in SQL?
Ans: Transactions use an isolation
level that specifies the extent to which a transaction must be isolated from
any data modifications caused by other transactions. These also help in
identifying which concurrency side-effects are permissible.
Please refer the below list for
more clarity on the different type of levels.i. Read Committed.
It ensures that SELECT query will use committed values of the table only. If there is any active transaction on the table in some other session, then the SELECT query will wait for any such transactions to complete. Read Committed is the default transaction isolation level.ii Read Uncommitted.
There is a transaction to update a table. But, it is not able to reach to any of these states like complete, commit or rollback. Then these values get displayed (as Dirty Read) in SELECT query of “Read Uncommitted” isolation transaction.iii. Repeatable Read.
This level doesn’t guarantee that reads are repeatable. But it does ensure that data won’t change for the life of the transaction once.iv. Serializable.
It is similar to Repeatable Read level. The only difference is that it stops Phantom Read and utilizes the range lock. If the table has an index, then it secures the records based on the range defined in the WHERE clause (like where ID between 1 and 3). If a table does not have an index, then it locks complete table.v. Snapshot.
It is similar to Serializable isolation. The difference is that Snapshot does not hold a lock on a table during the transaction. Thus allowing the table to get modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data. In case any data modification happens in other sessions then existing transaction displays the old data from Tempdb.
Ques: 5. How do we
Tune the Queries?
Ans: Queries can be tuned by
Checking the logic (table joins), by creating Indexes on objects in the where
clause, by avoiding full table scans. Finally use the trace utility to generate
the trace file, use the TK-Prof utility to generate a statistical a nalysis
about the query using which appropriate actions can be taken.
Ques: 6. What is the difference between BEFORE and AFTER in Database Triggers?
Ans: BEFORE triggers are usually used when validation needs to take place before
accepting the change. They run before any change is made to the database. Let’s
say you run a database for a bank. You have a table accounts and a table transaction.
If a user makes a withdrawal from his account, you would want to make sure that
the user has enough credits in his account for his withdrawal. The BEFORE
trigger will allow to do that and prevent the row from being inserted in
transactions if the balance in accounts is not enough.
AFTER
triggers are usually used when information needs to be updated in a separate
table due to a change. They run after changes have been made to the database
(not necessarily committed). Let’s go back to our back example. After a
successful transaction, you would want balance to be updated in the accounts
table. An AFTER trigger will allow you to do exactly that.
No comments:
Post a comment