Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    31

    Unanswered: Questions on Indexes and Sequences

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dbsam View Post
    I am sure indexes would not be a good idea on those columns
    Not necessarily. Why are you sure of that? Did you analyse plans of your queries?

    Quote Originally Posted by dbsam View Post
    can anybody tell me if a view would be appropriate?
    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.


    Quote Originally Posted by dbsam View Post
    My problem is whether I should assign a sequence or shall I create the column as generated by identity (always).
    If you don't need to control the generation of values, make it an identity column.

  3. #3
    Join Date
    Dec 2009
    Posts
    31
    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?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dbsam View Post
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •