Do you need to specify all fields in an insert

When performing an insert lets say from C# into a SQL Server table (using parameterized sql statements), do you need to specify every table field in the insert statement?

I noticed that the fields that I do not specify in the insert default to the defa开发者_运维知识库ults set in the table. I don't know if that's good or bad in my insert statement to leave out fields and let the defaults take care of setting the fields I don't care about. It must be ok because it works.

You need to specify all those field for which you want to insert a value. You do not have to specify all fields in the table!

As you noticed, any of the fields that you do not specify and that do have a default constraint on them will be set to that defined default value. It's a "good thing" (tm) - for sure! This allows you to write less T-SQL insert code - all the defined defaults will be set already. I find this to be a great feature of SQL Server (and lots of other relational databases, too) - you can initialize things like "last modified" date fields to "today" upon insert without having to specifically add those fields to your INSERT statement.

Any fields that are neither part of your INSERT statement, nor have a default value defined, will be left NULL.

Any fields that are defined as NOT NULL must be either part of the list of fields in your INSERT statement (so that you give them a specific NON NULL value), or they have to have a default constraint on them.

This is not a behavior specific to C# or .NET.

With most databases, any fields that are omitted from an INSERT statement are assigned the default values, or if nullable are stored as NULL. This is standard, and expected behavior.

As for whether this is good or bad - it depends. Personally, I prefer to specify the values of all fields in any table I am inserting into so that future maintainers realize that I chose to insert the values by intent. However, in some cases, there are calculations or trigger-based columns which the application cannot set - in which case I allow the database to handle this.

If you really want to be thorough and clear - you can perform all of your INSERTs through stored procedures - this way the knowledge is captured in the database layer and can be leveraged by any applications that interact with the database.

Yes it is ok to leave out fields in an insert statement if the defaults are good enough for you.

Well seeing as it works, its ok if its what you want to achieve. If you have nullable fields and fields with default values, and nothing to put in them - well thats just fine.

This is what defaults values are there for. They help you to

• save your time
• avoid writing unnecessary code

It is fine if the default values are the values that are correct for the record you are inserting. What concerns me is that you talk about them as fields that you don't care about. You should care what goes into them; it can lead to serious data problems to not care. Those fields are there for a reason; you need to understand what the defaults mean and if they are appropriate.