We all know that CURRENT_TIMESTAMP is ANSI and carries the Celko stamp of approval while GETDATE() is proprietary. So now run the following line of code
CREATE TABLE duh2(id DATETIME CONSTRAINT df_duh DEFAULT CURRENT_TIMESTAMP)
No problem right? Let's take a look at what is actually created, run the query below
SELECT object_definition(id) FROM sysobjects WHERE name = 'df_duh'
Interesting, the object definition for the constraint shows getdate() not current_timestamp.
Let's verify that by using sp_help, run the query below and scroll all the way down, look at the constraint_keys column
Same thing,it shows getdate()
What happens when you script out the table?
/****** Object: Table [dbo].[duh2] Script Date: 02/22/2009 20:58:41 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[duh2](
[id] [DATETIME] NULL CONSTRAINT [df_duh] DEFAULT (GETDATE())
) ON [PRIMARY]'
As you can see SQL Server changes CURRENT_TIMESTAMP to GETDATE().
Is this a bug/feature or something else? What if you use the same table on more than one RDBMS and want to quickly script the table from SQL Server so that you can create it on somewhere else?