Hi,

I have pretty basic questions on indexes and sequences. I hope somebody could still help me with my questions. I have request for a new table which is going to be independently searched on two different STATUS columns the values of which would be 'Y/N'. I am sure indexes would not be a good idea on those columns but can anybody tell me if a view would be appropriate?
My another question is this table has a requirement of a unique key which needs a technically assigned sequence. Based on the flagged columns the data in this table would be deleted. If the status of the column is Y the data from the table will be deleted since the order is completed and no longer is needed for transaction. This data is stored in a separate table for future reference. My problem is whether I should assign a sequence or shall I create the column as generated by identity (always).

Any suggestion would help

Thanks in advance

Not necessarily. Why are you sure of that? Did you analyse plans of your queries?

Not likely. A view is just a query stored in the system catalog. If the query is inefficient, it will be just as inefficient in the form of a view.


If you don't need to control the generation of values, make it an identity column.

Hi n_i...thanks for the reply. I forgot to mention the tables are going to be fairly small maybe 15000 - 30000 records a month and the records will be cleaned periodically. The records
will be cleaned based on 'Y' value of one column and the other query/queries will pull all the fields from the table based on the 'Y' and 'N' values of the other
columns.
Between sequence or generated by identity --- like I said the table is going to be cleaned once the order status changes to 'Y' coz then that order is
complete and need not to be in that table anymore. In case of identity if my ids are 1,2,3 and I delete 3 from the table I will be left with 1,2 and then when a
new record is inserted it will 1,2,4 whereas in case of sequence the new inserted record can be calculated based on max(ID)+1 to achieve the order 1,2,3
Does that make sense?

No, it does not, to me anyway. Why do you need consecutive IDs?