Question About Tables In SQL Server

[dmaier]

I will start off by saying that I am a novice at SQL Server. I have been working with Access for awhile and tried to convert an Access table to SQL Server.

I have two questions:
I have a field which is an ID field and want it to be an autonumber field. How can I do that in SQL Server?

Also I have a field that is a short date type field(08/29/03) and want it to default to today's date when data is entered but also to have the option to change the date if necessary. Does anyone know how that can be done?

 

[dmhirsch]

The equivalent in SQL Server for autonumber is IDENTITY. You can set that as an option in Enterprise Manager, in table design.

Also in table design mode, you can set defaults. GETDATE() as a default will return today's date.

 

[jhall156]

How can you set the default to a system function. It doesn't return today's date when I try it. I believe Column Default Values must be constant expressions.

JHall

 

[SQLSister]

I use getdate() as a default value all the time. If you are using Enterprise Manager just type in getdate() where it says default value. Note that it will not supply values for existing records unless nulls are not allowed in the column.
You can also use alter table to set a default value programmically in query anlyzer. the syntax from books online is:
"ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES"

This particular sysntax will allow you to set a default value and populate it in a column that allows nulls.

You can also use create table if this is a new table that you want to set up with default values.