Default Values for SQL Data Types in SQL Server Database

SQL Server programmers can create user defined data types, create defaults (database default value objects) and bind default values to user defined data types. What database data type defaults can be used for for a SQL developer? Assume that in your database tables where transactional data is stored, you always create InsertedDT field of DateTime SQL data type. And in the DDL (Data Definition Language) of the SQL table's CREATE script you assing GETDATE() as the default value of InsertedDT column.

In this case, a more convenient solution for SQL database programmer is to create a user defined Data Type of datetime. Then create a database Default object. And following these 2 steps, assing the Default to custom Data Type.

Wherever the new data type is used, the default value assigned will be used in INSERT commands in case an explicit value is not used for that column.
Let's explain this case with an example and sample SQL codes

Start with executing below CREATE DEFAULT script on SSMS connected to target database.


create default DefaultInsertDT as getdate();

Code

This default object can be viewed in Object Explorer window on SQL Server Management Studio under target database following nodes:
Programmability > Defaults

SQL Server Default value objects for user Data Types

Now database developer can create the user-defined data type for storing inserted datetime values of table row data.


create type InsertDT from datetime not null;

Code

Custom data types can be found if you drill-down following nodes on SSMS Object Explorer window:
Programmability > Types > User-Defined Data Types

User-Defined Data Types in SQL Server database

Although it is not possible to create Default value using GUI on SQL Server Management Studio, database developers can create Data Type using SSMS GUI options instead of executing Transact-SQL CREATE TYPE SQL command.

Right click on User-Defined Data Types node and select option New User-Defined Data Type...

create User Defined Data Type in SQL Server Management Studio

On New User-Defined Data Type screen, SQL developers can create type with desired SQL Server data type, configure its nullability (NULL or NOT NULL option) and bind a DEFAULT value.
Using the "..." button on the right of the Default input field in Binding section, SQL programmer can choose one among all defined default value objects in current SQL Server database.

create new SQL data type using SQL Server Management Studio

To make this default vaue assignment to user defined data type programmatically, following SQL command using system procedure sp_bindefault can be executed on Query Editor screen.
Please note, since I created both objects in "dbo" schema, I used this schema name in front of the data type and default name.


exec sys.sp_bindefault @defname=N'[dbo].[DefaultInsertDT]', @objname=N'[dbo].[InsertDT]';

Code

If the command is successfull, following message will be displayed as output of the SQL statement execution

Default bound to data type.
The new default has been bound to columns(s) of the specified user data type.

After SQL developer has created the Default and custom Data Type and later bind the default to the user-defined type, let's now create a sample database table using this new data type for one of its columns.


create table Transactions(

Id int identity(1,1),

InsertedBy nvarchar(10),

InsertedAt InsertDT

);

Code

Using INSERT commands, SQL programmer can populate sample database table with data


insert into Transactions (InsertedBy) values ('Kodyaz'),('Eralper');

Code

When we query the database table using SQL Select command, we can see that although we did not explicitely assign a value to InsertedAt column in INSERT command, because of the default value of the user-defined type InsertDT, this column has values.

user defined data type with default in SQL table