Declaring Datatypes for a Column in a Table

Declare the datatype of a new column in a create table or alter table statement.

create table [[database.]owner.]table_name 
		(column_name datatype [identity | not null | null]
			[, column_name datatype [identity | not null |
				null]]...)

alter table [[database.]owner.]table_name 
		add column_name datatype [identity | null 
		[, column_name datatype [identity | null]...

Example 1

For example:

create table sales_daily
    (stor_id char(4)not null,
     ord_num numeric(10,0)identity,
     ord_amt money null)

You can also declare the datatype of a new column in a select into statement, use convert or cast:

select convert(double precision, x), cast ( int, y) into 
    newtable from oldtable