I saw a talk on optimizing SQL at the Austin .NET User Group on Monday night.

Big News: MSSQL Server 2014 is here!

1. clustered indexes: There can be only one of these per database table as it keeps the physical ordering of rows. (Sorting by Guids is ineffective and useless. It is best to put the primary key elsewhere.)
2. nonclustered indexes: This is also a list of keys like a clustered index providing a quick way to get to a record (much quicker than just crawling the whole of the table) but as the database table itself is not ordered by a nonclustered index the ordering must be kept elsewhere in a supporting file.
3. columnstore indexes: See this. (these were not really explained in the talk)

These are the three kinds of indexes, and thus the interview question: "What may you do at the server to make queries faster?" ...could be answered with "add an index" ...but beware. As you add indexes you increase the amount of File I/O hijinks and this may ultimately lead to a performance hit instead of a performance boon. Whenever a table is updated, so too must the flat files holding the nonclustered indexes for the table. So, how do you put your finger on the sweet spot, the happy medium between indexing nothing and indexing everything? The answer is to use the Tuning Advisor. It will run every possible combination of indexes and let you know both which is the best and how much of a performance gain (a percentage) you will get over what you had to start out with. One should use the SQL Server Profiler first in advance of the Tuning Advisor. Pick "New Trace..." from the file menu here. When prepping the trace in the steps to come use the "Tuning" template. You may pick "Database Engine Tuning Advisor..." from the "Tools" menu to get at the desired analysis. I could not tell from the talk if one strictly needs to save the trace out to a table at "Column Filters..." first before running the Tuning Advisor. Perhaps so. Queries break down into:

1. table scans: have to crawl everything in a table to find what they are looking for without the help of an index
2. index scans: also touch every row in a table but also utilize indexes making them speedier than table scans
3. index seeks: find what they need from an index exclusively and only delve into the table or tables in question at the rows which match what the query is fishing for

The speaker was Anil Desai. He suggested that if one has an ORM implementation yet needs to do a complicated query that one might have the query materialize in a view and then just work against the view with the ORM as one might a table. I found this a fascinating contrast to Ryan Vice's suggestion (as denoted here) that one might just sidestep using an ORM when the queries get crazy in CQS.