Post reply

SQL Server Default Constraints can be constants, functions or objects. What kind of objects other than functions?

  • October 3, 2018 at 7:28 am

    #363597

    Here, Specify Default Values for Columns 

    I see

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

    What kind of objects may be specified, other than constants or functions?

  • October 3, 2018 at 3:40 pm

    #2007981

    gbritton1 - Wednesday, October 3, 2018 7:28 AM

    What kind of objects may be specified, other than constants or functions?

    They worded this BOL entry poorly IMHO - the only "objects" you can use would be T-SQL scalar UDFs or a CLR Scalar UDF.
    Your options are an expression and or a T-SQL or CLR scalar UDF. I would not use a T-SQL scalar UDF for a constraint or computed column however - never. It will introduce some unexpected performance issues. Note this article: Another reason why scalar functions in computed columns is a bad idea. Though the article is not about scalar UDFs for default constraints - the same problems outlined will occur when using a scalar UDF for a default.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • October 4, 2018 at 7:02 am

    #2008036

    Alan.B - Wednesday, October 3, 2018 3:40 PM

    gbritton1 - Wednesday, October 3, 2018 7:28 AM

    What kind of objects may be specified, other than constants or functions?

    They worded this BOL entry poorly IMHO - the only "objects" you can use would be T-SQL scalar UDFs or a CLR Scalar UDF.
    Your options are an expression and or a T-SQL or CLR scalar UDF. I would not use a T-SQL scalar UDF for a constraint or computed column however - never. It will introduce some unexpected performance issues. Note this article: Another reason why scalar functions in computed columns is a bad idea. Though the article is not about scalar UDFs for default constraints - the same problems outlined will occur when using a scalar UDF for a default.

    Thanks Alan, I was thinking about DEFAULT constraints actually,  I see little harm in defaulting a date to GETDATE(), for example.  or am I missing something?

  • October 4, 2018 at 7:43 am

    #2008051

    gbritton1 - Thursday, October 4, 2018 7:02 AM

    Alan.B - Wednesday, October 3, 2018 3:40 PM

    gbritton1 - Wednesday, October 3, 2018 7:28 AM

    What kind of objects may be specified, other than constants or functions?

    They worded this BOL entry poorly IMHO - the only "objects" you can use would be T-SQL scalar UDFs or a CLR Scalar UDF.
    Your options are an expression and or a T-SQL or CLR scalar UDF. I would not use a T-SQL scalar UDF for a constraint or computed column however - never. It will introduce some unexpected performance issues. Note this article: Another reason why scalar functions in computed columns is a bad idea. Though the article is not about scalar UDFs for default constraints - the same problems outlined will occur when using a scalar UDF for a default.

    Thanks Alan, I was thinking about DEFAULT constraints actually,  I see little harm in defaulting a date to GETDATE(), for example.  or am I missing something?

    Nothing wrong with that.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply