SQL Server Indexing Interview Questions 

Indexes are used to increase speed at which we find information in tables. If there are no indexes defined on a column which we search, then we must do a table scan, meaning we look through the entire contents of a column to find the result. If we have an index built on something we search, say a phone number, last name, id number, etc then the system looks at a hash that tells where our data should be located, and we don't have to look through the entire table, we just jump straight to where it is located.

Imagine putting names alphabetically in a list. If I give you a name, you look at them and quickly move to where the item is located. We are indexing our information by name. If we did not, then it would be more like having all of the names in a hat. You would have to look at each item to know when you have all that match.

Views are simply a different representation of data residing in the database. If we store last name and first name in separate fields, but want them to be together when the user uses them, then we can create a view to put the two together and show it as one. We can also do table joins for users so they never have to worry about how data is stored, and we can also only output part of entire tables. Let's say we have employee information for government, which is open record, but our employees are still required to give their SSN. Instead of giving access to the entire table contents, we could write a view to show only the information allowed to be publicly available, and remove the SSN from the results.

For the column Custno and in a Customer table

Multiple columns index or covered index should be ordered as most selective column on left and gradually decreasing in selectivity, as they go right. In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index. Non-key columns, can help to create cover indexes.By including non-key columns, you can create non-clustered indexe—that cover more queries.

The database engine does not consider non-key columns when calculating the number of index key columns or index key size. Non-key columns can be included in non-clustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Another advantage is that using non-key column in index you can have index data types not allowed as index key columns generally

The more unique the values are in a column, the more an index will be beneficial. There is a cost associated with maintaining indexes though—so, don't get carried away. But, joining or frequent searches on a column make them good candidates to consider for an index.

By Definition : An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.
This is common for both Oracle and SQL Server
By default, Oracle creates B-tree indexes.

If u wanna know how it is stored internally, try google with "Oracle" Index and "SQL Server" Index. Check the following links.

Indexes help you tune your Sql Server database so queries and stored procedures run as efficiently as possible. Indexes help Sql Server organize and find the data in your tables so that it doesn't have to scan or “manually“ sort every record in your table to give you the information in your request.

In general, you tend to create indexes based on fields used in your WHERE, GROUP BY, and ORDER BY Clauses to give Sql Server a heads-up on how you will want your data presented in the future. I almost grimace in suggesting such a practice, because you don't want to go crazy with indexes. They come with a price in terms of disk space and performance on inserts and updates. However, as a rule of thumb, inspect your WHERE, GROUP BY, and ORDER BY clauses to give you some information to help you tune Sql Server and add your

I want to do an SQL query. The 'where' clause uses 'greater-than' a target value. I want to retrieve exactly one and only one row, namely, the very row exactly after my target value. The catch is, I want it to be efficient; I don't want the select to find hundreds of records first, then serve up the single row I want; I want it to find the row I want (reminder: it's already an indexed row). Can SQL do this, and do it efficiently like an ISAM read would do?

It's a little extra space reserved so things can more easily be inserted into the index later. The point of the fill factor is to reserve space on an index page to avoid page splitting, a very inefficient operation. A page split is the internal mechanism the server uses to make room for a new element in a file. If an index page splits, the system consumes more disk I/O to read the index allocation map as it tries to locate the next segment on a disk. New pages are not created contiguously, so fill factors leave a little room to grow and hopefully minimize—or at least postpone—page splitting.

If you add a primary key, SQL Server automatically creates an index off of the key field.

If you want to add an index on a different field, you can run the following t-sql:

CREATE INDEX indexname ON tablename(columnname)

double check your index has been created by running the following:
EXEC sp_helpindex tablename

the advantages :
if your table has large record variant (large selectivity). it will speed up your 'select' query .

the disadvantages :
1. if your table has low selectivity eg. field with male/female record, or state for 1 million people , it will slow down your 'select' query.
2. there's some very little tiny 'performance impact' in 'delete','update','insert' query. (you can ignore this)
3. if your table has very little record, it will slow down (a little) your query . (you can ignore this)

Test your table performance in using/not using index with some query analyzer.

I'm looking for a wiki system which runs on apache, php and mysql. It must be open source but more importantly, it should not allow public to access content, and registration must be monitored, and the private wiki must not have pages indexed by search engine.
Any idea what i should use? Thank you.

Full text search is supported by all editions except SQL Server 2005 Express Edition, namely:

SQL Server 2005 Enterprise Edition
SQL Server 2005 Developer Edition
SQL Server 2005 Standard Edition
SQL Server 2005 Workgroup Edition
SQL Server 2005 Express Edition with Advanced Services

The proc sql functionality allows one to access oracle tables using sql like commands. Does it also leverage the indices in the oracle tables so as to optimize the queries? Or should I do my joins and complex data manipulations separately before using SAS?

Thanks.

I have a huge table I want to query but there are multiple fields I want to search. How do I set up an index on multiple fields in a table?

Could someone explain sql indexes to me in plain terms and then maybe technical "inner work" terms. Thanks a lot if you can.