SQL Server default values: why with one or two par

问题:

if you run this script to retrieve all default value definations in a database:

select 
    c.name as columnname, t.name as tablename, 
    d.definition as value, d.name as constraintname
from
    sys.default_constraints d
    join sys.columns c
        on d.parent_column_id = c.column_id
        and d.parent_object_id = c.object_id
    join sys.tables t
        on c.object_id = t.object_id

you would get a lot default values like:

(getdate())
((0))
('')
('2099-12-31')

My question is, why are there parentheses? Are they nessessary? Why some values have one pair of them, others have two? Is the count to be followed exactly when scripting T-SQL?

回答1:

It's just how SQL stores them internally.

They aren't needed except for when IN is expanded to (.. OR ...) for some reason.

You should try a check constraint with ANDs and ORs. Lordy lord.

回答2:

This answer and gbn's answer suffice. However, FWIW, I noticed a pattern, in my databases at least, for when SQL Server stores the default values in single vs. double parentheses.

• Double parentheses for numbers (all integers in my case).
• Single parentheses for non-numbers (i.e. strings and functions).
• All definitions stored in at least a single set of parentheses.
• No definitions stored in more than two sets of parentheses.

While this may not affect functionality, etc., as a programmer, it feels good to know there is a pattern.

Below is a query to check defaults in a database against these rules (it may not be perfect, but it's a start).

-- Find number of distinct defaults. This should match the number of records in following query 
-- to ensure all are accounted for.
select count(*) as NumberOfDistinctConstraints 
from (select distinct [definition] from sys.default_constraints) t
;

-- Find defaults that follow and don't follow the rules.
;with c1 as (
    select [definition] as DefaultValue, 3 as NumberOfParentheses
    from sys.default_constraints dc
    where [definition] like '(((%'

    union

    select [definition], 2
    from sys.default_constraints dc
    where [definition] like '(([^(]%'

    union

    select [definition], 1
    from sys.default_constraints dc
    where [definition] like '([^(]%' --and ([definition] like '(N''%' or [definition] like '(''%')

    union

    select [definition], 0
    from sys.default_constraints dc
    where [definition] like '[^(]%'
)
, c2 as (
    select 
        DefaultValue
        , NumberOfParentheses
        , case
            when 
                NumberOfParentheses >= 3 -- None exists.
                or NumberOfParentheses = 0 -- None exists.
                or (
                    -- Any double parentheses not followed by a digit or negative sign.
                    NumberOfParentheses = 2 
                    and substring(DefaultValue, 3, 1) not like ('[0-9]') 
                    and substring(DefaultValue, 3, 1) not like ('-')
                )
                or (
                    -- Any single parenthesis followed by a digit or negative sign.
                    NumberOfParentheses = 1 
                    and (
                        substring(DefaultValue, 2, 1) like ('[0-9]') 
                        and substring(DefaultValue, 2, 1) like ('-')
                    )
                )
            then
                0
            else 1
        end as FollowsTheRules
    from c1
)
select *
from c2
--where FollowsTheRules = 0
order by FollowsTheRules asc, NumberOfParentheses desc, DefaultValue asc
;

I checked a few databases and these rules held up. However, I'd be interested to see if others see the same results.