SQL Server Performance

Need update statement with conditional operator

Discussion in 'T-SQL Performance Tuning for Developers' started by s_niladri, Apr 24, 2006.

  1. s_niladri New Member

    Hello All,
    Suppose there is a table with the following data

    Table Name : Door
    Door_Id Door_Status
    1 0
    2 0
    3 1
    4 1
    5 1
    6 0
    7 1
    8 1
    9 0
    10 1

    We have to update the table such a way if Door_id = 1 it can update all the record.

    For door_id =1 it can update all the records
    For door_id =2 it can update record no: 2,4,6,8,10 the records
    For door_id =3 it can update record no: 3,6,9 the records
    For door_id =4 it can update record no: 4,8 the records
    For door_id =5 it can update record no: 5,10 the records
    For door_id =6-10 it can update only one record which is equivalent to corresponding
    record no.

    so for example if door_id=2 I need to update row no 2,4,6,8 & 10 for door_status.
    if door_status is 0 make it to 1 or if it is 1 make it 0.

    How to do that in a single update statement??

    Need all of your utmost help.


  2. Adriaan New Member

    [Comment withdrawn - Adriaan]
  3. Luis Martin Moderator

    Well, I would like to drop it. But I'll wait for others Moderators oppinions.

    Luis Martin

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte

    All postings are provided “AS IS” with no warranties for accuracy.

  4. Madhivanan Moderator

    s_niladri, what did you try?<br />Look for case in sql server help file<br />Also dont post the same question in different forums using different titles [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. mmarovic Active Member

    I must add, I am disappointed with microsoft interview questions.
  6. Madhivanan Moderator

    Not only in Microsoft, also in other companies most of the interview questions seem like that. Most of them are based on Triggers, Cursors, etc for the beginners [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. mmarovic Active Member

    My problems with such question is that they don't reflect real problems you have to solve, they are tricky made-up problems.
  8. Adriaan New Member

    Like when the question is "how to improve query" when the answer is to change the data structure.
  9. mmarovic Active Member

    I didn't like that question either, but now I think I will use the same question to test understanding of indexes. People usually read about sargs and indexes and remember that index is useful if query contains starting index columns, but quite often they don't understand why. Based on answer of that question you can see if one really understands how indexes work or not.
  10. druer New Member

    I'm not sure how an index will help with the question of "how to improve query". If I'm reading the question and the desire correctly wouldn't you have to use "character_field like '%value%' or something, and in case of LIKE with starting wildcard, I thought the optimizer can't use indexes anyway.

    I was thinking that turning on FULLTEXT "stuff" would do the trick, but still not sure.

    Hope it helps,

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.
  11. mmarovic Active Member

    The correct answer (IMO) besides changing the table structure is that adding an index would improve a query on specific server that was the first element in the tree. In that case column like @server + '%' would be used.
  12. mmarovic Active Member

    However if the question was really "how to improve the query" instead of "how to improve the query performance", then the question was missleading at least.
  13. druer New Member

    \serverlocation codePathNameDate ReceivedTime ReceivedFileName

    Now to find out the following query with select statement ,lot of string function need to be use to get the results. Supoose there are million of million rows in this table. So if we follow the above approach it will become very slow.

    So how to optimize the query to get results in a mimimum time....

    1>List all the files name for a particular Date?
    2>List all the files name for a particular location?
    3>List all the files name for a particular Server?
    4>List all the files name for a particular Path?

    So the only one that could be sped up would be the SERVER query since it would the first part of the query.

    How about INDEXED views where there would be a different VIEW for the DATE, LOCATION and PATH aspects of the data?

    Hope it helps,

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.
  14. mmarovic Active Member

    Yes indexed view would solve the problem, but it doesn't make sense. Why don't we just change table structure instead of creating indexed view to mitigate poor table design? I think I mentioned indexed view in a original thread.
    quote:Without changing table structure or creating indexed view splitting varchar into multiple columns you can't do much to optimize the query. You can add an index to improve performance of query 3 and that's all.
    Another possibility is to create computed columns and index them, but this is still changing table structure. Computed columns wouldn't be materialized in table row but they would be materialized in indexes.
    Actually the question as formulated doesn't make sense, however we don't know if it was originaly formulated like that.
  15. satya Moderator

    If the question is related to get an answer for the interview questions, then please refrain posting such questions. As mentioned the user must get the answer using their skills in SQL Server and not from forums.

    Apart from that topic for the other discussion in this thread, please continue in the other post.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page