Setting Default Values for Columns in SQL Server

Using default values for columns in SQL Server can have several benefits. For example, default values can simplify data entry by providing a predefined value for a column when no value is specified by the user. Default values can also enforce business rules or data integrity by ensuring that a column always has a valid value.

Timestamps: DateTime with getDate() default value

One of the common design decisions in SQL server database is how to store date and time values. One of the best practices is to use datetime with default set to getDate() function. This function returns the current system date and time as a datetime value. By using this default, you can ensure that every row in your table has a consistent and accurate timestamp that reflects when it was inserted or updated.

Example:

CREATE TABLE [Customer](
  id int NOT NULL IDENTITY(1,1),
  name varchar(255),
  email varchar(255),
  modified datetime DEFAULT(getdate()),
  CONSTRAINT customer_pkey PRIMARY KEY(id)
)
GO

In Luna Modeler database modeling tool, you can define the table structure visually, and add the default value to a column properties section. See the screenshot below:

Datatype Alias

If you want to work more comfortably, you can create a Datatype alias in Luna Modeler.

You add this object type to the diagram by clicking the Other button on the main toolbar or from the diagram context menu.

For the newly created object set the name, e.g. tmstmp and write the full datatype specification datetime DETAULT(getdate()) to the definition field.

You can then use your tmstmp datatype alias in a similar way to the classic datatype.

Benefits of using datatype aliases in Luna Modeler

• You can easily recognize the column in the diagram because the name of the data type is not datetime, but a name you define, in this case tmstmp.
• You can easily navigate to a table/column using the Find function.

#