Hi, I have a requirement in which I want the event column to be indexed. However I wanted to know if it is possible to ORDER the event column in Descending order something like below:
CREATE INDEX my_index ON my_table (EVENT ORDER BY DESC) INCLUDE (v2); My Base table has EVENT column but it is not ordered, I am thinking if indexing EVENT column in Descending order will have me get away with the ORDERBY clause and reduce query time. Request for comments.
Thank you @Toshihiro Suzuki. I created the index something like
CREATE INDEX my_index ON my_table (col1, col2, col3 , EVENT DESC) INCLUDE (col4 , col5);
However the EVENT column is not sorted in descending order. Is there anything I am missing? Have I created the Index correctly?
One thing I wanted to highlight was my EVENT column datatype is VARCHAR....is that making EVENT column not to sort?
A VARCHAR column will be sorted differently than a numeric column, yes. However, the event column would need to be the leading (or only) column in your create index statement.
Otherwise, you would need to scan all records, buffering them, to construct the descending order. Try:
CREATE INDEX my_index ON my_table (EVENT DESC, col1, col2, ...) include (...)