Non-keyed columns in indexes in SQL 2005

I am confused about all the hype about including non-keyed columns in indexes in SQL Server 2005. I clearly understand the advantage of including a column in an index to avoid needing a bookmark lookup to retrieve the data. But I could do that in SQL 2000 - just add the column as a keyed column on the index. So what is the difference with having the column not part of the key? Non-keyed indexes make WHERE clauses slower if I have to do a table scan when I could otherwise do an index scan. If that is the only difference, then why would anyone ever want to use a non-keyed column in an index?

According to books online:
"They can be data types not allowed as index key columns."
"They are not considered by the Database Engine when calculating the number of index key columns or index key size."
"While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level""Computed columns that are deterministic and either precise or imprecise can be included columns"

In my 7 years of SQL Server, I have never run into a situation where I wanted an index on a column and could not add it because of the data type. I am not even sure which types are allowed (I know text and image aren't - but they aren't allowed in the new non-keyed columns feature either). I suppose there might be some special situation needing this.

The total index key size is 900 bytes. I have run into situations where this was stopped me from using an index I wanted, but it is rare, and never caused much of a problem. 900 bytes is usually more than enough.

Most of the size of the index is the leaf nodes, so this won't save significant disk space. It probably can make a small but significant performance difference doing index scans/seeks.
Including computed columns might save someone from denormalizing for speed, which saves maintenance, but again, I don't see this happening often enough to be signficant.


So, in summary, If a having a column as part of an index key clearly isn't useful for WHERE clauses, then by default it should be included as a non-key column. And there are some other situations where this is useful. But this is a small optimization, not a revelution.

static

Today I learned that static variables declared in a generic base class are shared for all instances of all derived classes (in C#). I wrote code a while back assuming a separate copy of the static variables would exist for each derived class without thinking too much about the issue. Today I wrote my 2nd derived class and quickly found this was not true. I did correctly assumed all versions of a generic class would share the same static variable, but I wasn't 100% of this until today. I asked another experience developer and he wasn't sure either. We both agree this would be a great interview question (even if they don't know or get it wrong, it shows a lot about where their ability is with object oriented code). Fortunately, only 1 derived class made it to production before I found this.

This also goes to show:

• No matter how well you think you know a language, you can always learn something new.
• Being aware of what you don't know is valuable.
• You should never make assumptions about things you can research and find out.

ReportViewer Control

If you get a "Page navigation is out of range" in a ReportViewer control, you should know that page navigation is a postback. Look at what you are doing in the PageLoad when IsPostBack == true.