Post reply

whether it will boost performance of SQL server? Would be it Good Design?

  • December 6, 2010 at 3:24 am

    #1258938

    PaulB-TheOneAndOnly (12/5/2010)

    Do you know this for a fact? How do you know optimizer is looking at constraint definition and not to table statistics? table statistics are telling optimizer range value, cardinality and data distribution.

    ...

    I have to admit I mostly relied on the information I have read and haven't done any tests myself. I also admit that these things are probably not easy to verify. I will try to investigate this and maybe produce some examples. In the meantime I will keep my eyes on Gail's posts :).

    Cheers

    Istvan

  • December 6, 2010 at 9:35 pm

    #1259354

    Questions

    1) Instead of having Default value on column, insert explicitly default value.

    2) Keep column constraint as NULL(Eventhough it ll not have NULL at any time) & explicitly take care in coding that NULL value should not get inserted.)

    Answers

    1) inserting explicitly will actually reduce performance because it needs to store it in cache memory and then it will insert value to the corresponding column. Which means CPU cycles considerably (compared to default value) will be high. So i would prefer default value.

    2) Either of the case will not impact performance, but it will be process overhead because you yourself going inside dirty shoe.

  • December 8, 2010 at 2:55 pm

    #1260339

    vinothraj (12/6/2010)

    Questions

    1) Instead of having Default value on column, insert explicitly default value.

    2) Keep column constraint as NULL(Eventhough it ll not have NULL at any time) & explicitly take care in coding that NULL value should not get inserted.)

    Answers

    1) inserting explicitly will actually reduce performance because it needs to store it in cache memory and then it will insert value to the corresponding column. Which means CPU cycles considerably (compared to default value) will be high. So i would prefer default value.

    2) Either of the case will not impact performance, but it will be process overhead because you yourself going inside dirty shoe.

    1- Prove it.

    2- As already posted, if a column should be Not Null it should have a Not Null constraint at database level - if you bet on the application you will sooner or later go down in flames.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 21, 2022 at 7:36 pm

    #3986564

    Deleted

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply