NULLs to Not NULLs

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Comments posted to this topic are about the item NULLs to Not NULLs

    Igor Micev,
    My blog: www.igormicev.com

  • rstruck

    SSC Rookie

    Points: 35

    Why would I want to change a Null to a Not Null. A little explanation of the script would be nice. What problem is it trying to solve?

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Good notice, and I saw that I haven't put all what I wanted with this script as an explanation.

    I have found many columns in many tables of many databases that are defined to allow NULLs but have non NULL values. That's actually an experience of working with constraints and when I got to understand what does mean to have a not nullable columns against nullabes in tables. The difference is big. This post (https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/) gives you a wide window on this importance.

    Script finds char types columns defined to allow NULL values but have non NULL values and generates the ALTER statements for the changes.

    And of course you have to check whether all generated ALTER statements will pass to be executed after checking with the other programmable parties that use the database.

    In my databases I found out columns without null values but defined to allow nulls. The data is being used for a couple of years, and no nulls are inserted. After a short talk with the application developers a decision felt down that we go with altering those columns.

    P.S. I inherited the databases' development.

    Igor Micev,
    My blog: www.igormicev.com

  • rstruck

    SSC Rookie

    Points: 35

    Thanks for the quick response. I will have to look at my tables and the constraints on them.

  • ScottPletcher

    SSC Guru

    Points: 98174

    I think you should get check all NULLable columns for a given table in a single pass. It could hurt severely hurt performance to have to scan the same table multiple times.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    ScottPletcher (2/22/2016)


    I think you should get check all NULLable columns for a given table in a single pass. It could hurt severely hurt performance to have to scan the same table multiple times.

    Yes, it's possible for improvements. That's why the discussion is for. Thanks.

    Igor Micev,
    My blog: www.igormicev.com

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    We've made creation mistakes, allowing nulls when we really want the data constrained.

Viewing 7 posts - 1 through 7 (of 7 total)

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