Create Table SQL

[obrienkev]

Hi all,

How do I code SQL for creating a table in MS SQL server??

SQL Server giver error - 'Incorrect syntax near auto_increment'

Here's what I have...

CREATE table location( location_ID int not null auto_increment primary key);

Also how do I set up a grant statement?

GRANT CREATE ON dbname.* TO training identified by 'training'

[Roger_Ramjet]

It is not called an autoincrement in ms sql it is an 'identity'. No idea how you write a create table query for it as I always use visual studio tools with sqlserver. You'll have to look at a Transact-SQL manual for that.

[Roger_Ramjet]

Forgot I have studio express on this machine so here is the manul page for

      SQL Server 2005 Books Online  

CREATE TABLE (Transact-SQL) Send Feedback

Creates a new table.

Transact-SQL Syntax Conventions

Syntax

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]

<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
[ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
| [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]
]
[ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]

<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]

<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor

| WITH ( < index_option > [ , ...n ] )

    ]
    [ ON { partition_scheme_name ( partition_column_name ) 
        | filegroup | "default" } ]

| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}

<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
]
| [ FOREIGN KEY ]
REFERENCES referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
]

< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]

            (column [ ASC | DESC ] [ ,...n ] ) 
    [ 
        WITH FILLFACTOR = fillfactor 
       |WITH ( <index_option> [ , ...n ] ) 
    ]
    [ ON { partition_scheme_name (partition_column_name)
        | filegroup | "default" } ] 
| FOREIGN KEY 
            ( column [ ,...n ] ) 
    REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
    [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
    [ NOT FOR REPLICATION ] 
| CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 

}

<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF}
| ALLOW_PAGE_LOCKS ={ ON | OFF}
}

IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. Both the seed and increment or neither must be specified. If neither is specified, the default is (1,1).

seed
Is the value used for the very first row loaded into the table.

increment
Is the incremental value added to the identity value of the previous row loaded.