Can you create a table with indexes at the same time?

Question

I'd like to create a table:

CREATE TABLE sfc.OpenId (
  Url VARCHAR(255) PRIMARY KEY,
  UserGuid uniqueidentifier NOT NULL references dbo.aspnet_users(userId),
)

...with an index on UserGuid.

Is it possible to create that index in the create table statement?

Answer 1

You can do that if the index on UserGuid is a unique index, via UNIQUE constraint. Otherwise, no.

Answer 2

Is it possible to create that index in the create table statement?

No, only constraints can be created within the CREATE TABLE syntax.

f not defined otherwise, the primary key will automatically be a CLUSTERED index - but that doesn't cover the userguid column. The CREATE INDEX syntax needs to be a separate statement otherwise.

Answer 3

Can you clarify why?

You can use transactions with DDL in SQL server and for most purposes this is equivalent to doing it at the same time.