SQL Server 2008 Interivew Questions

1. How to know how many tables contains empno as a column in a database?
   SELECT COUNT(*) AS Counter
   FROM syscolumns
   WHERE (name = 'empno')
2. Find duplicate rows in a table? OR I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated.
   SELECT sid, mark, COUNT(*) AS Counter
   FROM marks
   GROUP BY sid, mark
   HAVING (COUNT(*) > 1)
3. How to delete the rows which are duplicate (don’t delete both duplicate records).
   SET ROWCOUNT 1
   DELETE yourtable
   FROM yourtable a
   WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
   WHILE @@rowcount > 0
     DELETE yourtable
     FROM yourtable a
     WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
   SET ROWCOUNT 0
4. How to find 6th highest salary
   SELECT TOP 1 salary
   FROM (SELECT DISTINCT TOP 6 salary
   FROM employee
   ORDER BY salary DESC) a
   ORDER BY salary
5. Find top salary among two tables
   SELECT TOP 1 sal
   FROM (SELECT MAX(sal) AS sal
   FROM sal1
   UNION
   SELECT MAX(sal) AS sal
   FROM sal2) a
   ORDER BY sal DESC
6. Write a query to convert all the letters in a word to upper case
   SELECT UPPER('test')
7. Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.
   SELECT CEILING (7.1)
9. What is Index? It’s purpose?
   Indexes in databases are similar to indexes in books. In a database, an index allows the database program to find data in a table without scanning the entire table. An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value. Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key. For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C.
10. Explain about Clustered and non clustered index? How to choose between a Clustered Index and a Non-Clustered Index?
    There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way 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 nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
    Consider using a clustered index for:
• Columns that contain a large number of distinct values.
• Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
• Columns that are accessed sequentially.
• Queries that return large result sets.
  Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences:
• The data rows are not sorted and stored in order based on their non-clustered keys.
• The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.
• Per table only 249 non clustered indexes.
11. Disadvantage of index?
    Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much.
12. Given a scenario that I have a 10 Clustered Index in a Table to all their 10 Columns. What are the advantages and disadvantages?
    A: Only 1 clustered index is possible.
13. How can I enforce to use particular index?
    You can use index hint (index=<index_name>) after the table name.
    SELECT au_lname FROM authors (index=aunmind)
14. What is Index Tuning?
    One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.
    You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.
    Index Tuning is the process of finding appropriate column for non-clustered indexes.
    SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process.

Difference between Index defrag and Index rebuild?
When you create an index in the database, the index information used by queries is stored in index pages. The sequential index pages are chained together by pointers from one page to the next. When changes are made to the data that affect the index, the information in the index can become scattered in the database. Rebuilding an index reorganizes the storage of the index data (and table data in the case of a clustered index) to remove fragmentation. This can improve disk performance by reducing the number of page reads required to obtain the requested data
DBCC INDEXDEFRAG - Defragments clustered and secondary indexes of the specified table or view.

1.