Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Create INDEX within CREATE TABLE DDL

    Hi

    Minor and inconsequential but sometimes you just gotta know:

    Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement.

    e.g. I have:

    Code:
    CREATE TABLE MyT
     (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, 
     MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])
    which creates a table with a PK and unique constraint.
    I would like (pseudo SQL):
    Code:
    CREATE TABLE MyT
     (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, 
     MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT])
    No big deal - just curious Once I know I can stop scouring BOL for clues.

    Tks in advance
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think so. I don't recall seeing any syntax that allows this. Non-clustered indexes are separate objects from the table, and that is probably why they need to be created separately, and can be dropped separately as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    I don't think so. I don't recall seeing any syntax that allows this. Non-clustered indexes are separate objects from the table, and that is probably why they need to be created separately, and can be dropped separately as well.
    Cheers BM - didn't think of it like that - that does kind of make sense - you can't create an object dependent on another object before the first object exists. Or something similar but more felicitous
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •