Are both queries equivalent?

Question

I am confused whether these queries are equivalent? What is the difference?

ALTER TABLE agent1   
MODIFY commission DEFAULT .05; 

ALTER TABLE agent1
add constraint myconst default(commission=0.05) 

Answer 1

Your syntax may be from MySQL or Oracle. In SQL Server these are the two ways to add a constraint to an existing column:

(taken from Command for adding a default constraint)

    ALTER TABLE [Common].[PropertySetting] 
             ADD DEFAULT ((1)) FOR [Active];
    ALTER TABLE [Common].[PropertySetting] 
             ADD CONSTRAINT [DF_PropertySetting_Active] 
             DEFAULT ((1)) FOR [Active];

If you don't provide a constraint name SQL Server will create one for you.

This will list all the constraints on table x8:

SELECT OBJECT_NAME(object_id) AS Name,
       SCHEMA_NAME(schema_id) AS  [Schema] ,
       [type_desc] AS [Type]
FROM [sys].[objects]
WHERE [type_desc] LIKE '%CONSTRAINT' AND OBJECT_NAME(parent_object_id)='x8'

Constraint                      Schema      Type
    DF_x8_name                  dbo         DEFAULT_CONSTRAINT
    DF_x8_date1                 dbo         DEFAULT_CONSTRAINT
    DF__x8__x6__117F9D9         dbo         DEFAULT_CONSTRAINT
    DF_PropertySetting_Active   dbo         DEFAULT_CONSTRAINT
    t55                         dbo         CHECK_CONSTRAINT

As you can guess every one of the constraints (except for the third) was explicitly named when it was created. SQL Server created the third name.

You can use built in system views to get a list of all check and/or default constraints by issuing:

select * from sys.check_constraints 
 or
select * from sys.default_constraints