Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Specify Default Values for Columns

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

You can specify a default value that will be entered in the column in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. If you do not assign a default value and the user leaves the column blank, then:

  • If you set the option to allow null values, NULL will be inserted into the column.

  • If you do not set the option to allow null values, the column will remain blank, but the user will not be able to save the row until they supply a value for the column.

In This Topic

Limitations and Restrictions

  • If your entry in the Default Value field replaces a bound default (which is shown without parentheses), you will be prompted to unbind the default and replace it with your new default.

  • To enter a text string, enclose the value in single quotation marks ('); do not use double quotation marks (") because they are reserved for quoted identifiers.

  • To enter a numeric default, enter the number without quotation marks around it.

  • To enter an object/function, enter the name of the object/function without quotation marks around it.



Requires ALTER permission on the table.

Arrow icon used with Back to Top link [Top]

To specify a default value for a column

  1. In Object Explorer, right-click the table with columns for which you want to change the scale and click Design.

  2. Select the column for which you want to specify a default value.

  3. In the Column Properties tab, enter the new default value in the Default Value or Binding property.

    Note Note

    To enter a numeric default value, enter the number. For an object or function enter its name. For an alphanumeric default enter the value inside single quotes.

  4. On the File menu, click Save table name.

Arrow icon used with Back to Top link [Top]

To specify a default value for a column

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
    INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
    ALTER TABLE dbo.doc_exz
    ADD CONSTRAINT col_b_def
    DEFAULT 50 FOR column_b ;

For more information, see ALTER TABLE (Transact-SQL).

Community Additions

© 2015 Microsoft