• Sue_H - Wednesday, January 17, 2018 1:24 PM

    ca.zerbe-heinrich - Wednesday, January 17, 2018 12:27 PM

    Hi all!

    Using this oportunity to wich you a happy new year!

    Now my question!
    We are developing a C# application, using MS SQL database for the objects and data (Entity Framework for ObjectMapping, as far as I know).

    Now I am faced with a database Migration which redefines one column (RAW data format, from NULL to NOT NULL) in the biggest table inside the database (my assumption is: about 80% to 85% of the used data).

    Because the database itself (recovery model FULL) increases by the size of that table (GUID and RAW column), I told the developers, that we can not "sell" this to any customer, but use a "CONSTRAINT" (check for not null or > 0)) instead.

    The fast response was.
    We do not care, we develop. It's your problem.
    AND FOR PERFORMANCE REASONS, the database management system will perform better when the table definition for the RAW column is "not null", instead of the added CHECK CONSTRAINT (checking the data for being not null).

    Can you verify this Statement?

    Hope you have a quick answer for me!

    Kind regards,
    C.-A.

    The argument itself doesn't make a lot of sense to me personally. How does using the not null property vs using a constraint lead to size issues that have some impact to customers - especially when either way, some value is going to be entered? And likely the same value whether with a constraint or not null?
    I'd be more concerned about using Entity Framework. 

    Sue

    Hi Sue!

    Sorry,If I did not make myself clear enough.
    - we have one table (structure: 2 columns: GUID (PK), RAW (NVARBINARY (NULLABLE))),
    - it's filled up with data (around 80% of the whole database)
    - we migrate the database (and during those steps, development also changed the structure of the RAW column to (NOT NULL)
    - as far as I can imagine, in the Background the Database Management System (MS SQL) creates a copy of the RAW column which is NOT NULLable, and copies the values, as alse Drops the old column and rename it (at least that's what I would do)
    In this case, the migration of the database leads to an "doubled" size of the database (even just temporary, because you can shrink it afterwards) and it takes some time.
    My Suggestion was, not to change the structure , but add the CHECK CONSTRAINT (for not being NULL or 0).
    The Response from development was:
    - It's implemented like this, we do not care about space.
    - the Database Management System performs better if the structure is changed, instead of keeping the old structure with the added CONSTRAINT.

    My quetion was, because I do not know if the dbms really performs slower or the query optimizer cares more about the "structure" than "constraints" and i do not find something about it in the world wide web, if anyone can verify this Statement of development - regarding the performance.

    C.-A.