SQL

Increase SQL performance?

• Every index increases the time takes to perform INSERTS, UPDATES and
  DELETES, so the number of indexes should not be too much. Try to use
  maximum 4-5 indexes on one table, not more. If you have read-only table,
  then the number of indexes may be increased
  Keep your indexes as narrow as possible. This reduces the size of the index
  and reduces the number of reads required to read the index.
  (2) Try to create indexes on columns that have integer values rather than character
  values.
  (3) If you create a composite (multi-column) index, the order of the columns in
  the key are very important. Try to order the columns in the key as to enhance
  selectivity, with the most selective columns to the leftmost of the key.
  (4) If you want to join several tables, try to create surrogate integer keys for this
  purpose and create indexes on their columns.
  (5)Create surrogate integer primary key (identity for example) if your table will
  not have many insert operations.
  (6)Clustered indexes are more preferable than nonclustered, if you need to select
  by a range of values or you need to sort results set with GROUP BY or ORDER
  BY.
  (7)If your application will be performing the same query over and over on the
  same table, consider creating a covering index on the table.
  (8)You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans
  of Large Tables" trace to determine which tables in your database may need
  indexes. This trace will show which tables are being scanned by queries instead
  of using an index.
  

What’s difference between "Optimistic" and "Pessimistic" locking ?
In pessimistic locking when user wants to update data it locks the record and till then no one can update data. Other user’s can only view the data when there is pessimistic locking. In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days browser based application is very common and having pessimistic locking is not a practical solution.


What is a candidate key ?
A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. During database design you can pick up one of the candidate keys to be the primary key.
For example, in the supplier table supplierid and suppliername can be candidate key but you will only pick up supplierid as the primary key.

What are the different types of joins? What is the difference between them ?
INNER JOIN Inner join shows matches only when they exist in both tables.
Example, in the below SQL there are two tables Customers and Orders and the inner join in made on Customers Customerid and Orders Customerid.So this SQL will only give you result with customers who have orders.If the customer does not have order it will not display that record.
SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN Left join will display all records in left table of the SQL statement.In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the

RIGHT OUTER JOIN if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID278

RIGHT OUTER JOIN Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values.
For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table. SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

What are indexes? What is the difference between clustered and nonclustered indexes?
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quickly. There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way in which records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.