So do you think deciding between a clustered or non clustered index is difficult? Enter the world of Sybase IQ my friend

I have been messing around with Sybase IQ for a bit now. Sybase IQ is a columnar databases; data is stored in columns not in rows. This makes retrieval very fast but inserts are slower than a traditional RDBMS.

You are looking at the data in your table, most of the queries are ranges queries but every now and then a query gets executed for exactly one row. How would you index this? This is a very tough decision, right? You have 2 types of indexes to choose from, and if the index is a composite (multi column) index then you also have to worry about which column to place first so that you get a lean mean index seek.

With Sybase IQ you have these indexes to choose from

The Default column index
For any column that has no index defined, or whenever it is the most effective, query results are produced using the default index. This structure is fastest for projections, but generally is slower than any of the three column index types you define for anything other than a projection. Performance is still faster than most RDBMSs since one column of data is fetched, while other RDBMSs need to fetch all columns which results in more disk I/O operations.

The Low_Fast (LF) index type
This index is ideal for columns that have a very low number of unique values (under 1,000) such as sex, Yes/No, True/False, number of dependents, wage class, and so on. LF is the fastest index in Sybase IQ.

The High_Group (HG) index type
The High_Group index is commonly used for join columns with integer data types. It is also more commonly used than High_Non_Group because it handles GROUP BY efficiently.

The High_Non_Group (HNG) index type
Add an HNG index when you need to do range searches.
An HNG index requires approximately three times less disk space than an HG index requires. On that basis alone, if you do not need to do group operations, use an HNG index instead of a HG index.

The Compare (CMP) index type
A Compare (CMP) index is an index on the relationship between two columns. You may create Compare indexes on any two distinct columns with identical data types, precision, and scale. The CMP index stores the binary comparison (<, >, or =) of its two columns.

The Containment (WD) index type
This index allows you to store words from a column string of CHAR and VARCHAR data.

The Date (DATE) index type
The Time (TIME) index type
The Datetime (DTTM) index type
Three index types are used to process queries involving date, time, or datetime quantities:

The JOIN Index (Linear joins and Star joins)
Join indexes usually provide better query performance than when table joins are first defined at query time (ad hoc joins). In many situations, however, you can gain optimal performance on joined columns without creating join indexes.

Yes, a lot more than two, Sybase IQ does not have clustered or non clustered indexes. You can specify clustered index but it will be ignored.

What do you think? Do we need more indexes in SQL Server? What about BitMap indexes?