Necessary Insert Columns

Question

Necessary Insert Columns

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
October 21, 2022 at 12:00 am

#4098841

Comments posted to this topic are about the item Necessary Insert Columns

Viewing 6 posts - 1 through 5 (of 5 total)

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

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 1

Just to nit-pick: This will depend on the settings of ANSI_NULL_DEFAULT_OFF / ANSI_NULL_DEFAULT_ON for the session, or by the ANSI_NULL_DEFAULT database option. By default, these will be set such that columns that don't have nullability specified will allow NULLs.

As an application developer, I would have wished this to not be the case. Unnecessary NULLs have been the bane of my existence.

Just because you're right doesn't mean everybody else is wrong.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

Liked Rune's comment.

And that is why you should always explicitly specify NOT / NOT NULL when creating a table. From the context of the q, I could work out that you intended the omitted columns to be NULLable, but that's not automatic.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 3

Part of the QotD is knowing defaults. This was left off, as many people do this and the default is to allow NULLs.

As a good practice, however, I agree. You ought to always specify NULL/NOTNULL

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 4

Steve Jones - SSC Editor wrote:

Part of the QotD is knowing defaults. This was left off, as many people do this and the default is to allow NULLs.
As a good practice, however, I agree. You ought to always specify NULL/NOTNULL

This veers off into Inception territory, as you actually need to know what the default defaults are, or even the default default defaults. GAH!

Just because you're right doesn't mean everybody else is wrong.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 5

Rune Bivrin wrote:

Steve Jones - SSC Editor wrote:
Part of the QotD is knowing defaults. This was left off, as many people do this and the default is to allow NULLs.
As a good practice, however, I agree. You ought to always specify NULL/NOTNULL
This veers off into Inception territory, as you actually need to know what the default defaults are, or even the default default defaults. GAH!

Exactly. The database has defaults that override the "default", and something else overrides something else ... I still say the only really safe rule is:

ALWAYS specify NULL or NOT NULL when creating or altering a table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".