SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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();

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;

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]';

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
);

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

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

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







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums






SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems