Default Date Column

[Manish Chauhan]

How can I create a table where the primary key is the dateime datatype and
I'd like to default it to the current dateime on SQL Server 2005?

[John Elliot]

On 1/19/2010 5:25 AM, Manish Chauhan wrote:
> How can I create a table where the primary key is the dateime datatype and
> I'd like to default it to the current dateime on SQL Server 2005?

Here is the create table statement.

CREATE TABLE MYTABLE(
dt Date default GetDate() primary key,
someThingElse varchar(100)
)

I do not recommend you make Date as your primary key - you will never be
able to insert records simultaneously.

JE

[Erland Sommarskog]

Manish Chauhan (Manish...@discussions.microsoft.com) writes:
> How can I create a table where the primary key is the dateime datatype and
> I'd like to default it to the current dateime on SQL Server 2005?

I don't know what you intend to use this table for, but a datetime
value will full time portion is not likely to be a choice for a primary
key. If you are modelling a point in time, keep in mind that datetime
has a resolution of 3.33 ms, so if events can happen close to each other,
there may be duplicates.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx