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?
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With