Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

UPDATED:

I'll make the question clearer as the first answer isn't quite what I was looking for.

How can I execute this create table statement and ensure that the two automatically created indexes are stored on MY_INDEX_FILEGROUP as I am assuming that these automatically created indexes will be stored on MY_DATA_FILEGROUP that the table data will be stored in.

CREATE TABLE Product.Product(
    ProductId tinyint identity(1,1) constraint PK_Product_Product_ProductId primary key,
    ProductName varchar(50) unique not null,
    SupportEnded date default null null
) ON MY_DATA_FILEGROUP
GO

is it possible to seperate the automatically generated indexes into a different filegroup, from the data?

And as a seperate but related question is it possible to enforce a constraint like PRIMARY KEY or UNIQUE whilst preventing the automatic creation of the associated index?

share|improve this question
2  
1) Why are you not happy with the current answer? - it will help us write useful answers if we know this, and B) No, you have to have the indexes - they're the (hidden) mechanism by which the constraints are enforced. –  Damien_The_Unbeliever Aug 2 '12 at 12:36
    
see ypercubes comment on the answer –  Danny R Aug 2 '12 at 12:43
add comment

migrated from stackoverflow.com Aug 2 '12 at 13:34

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 3 down vote accepted

How can I execute this create table statement and ensure that the two automatically created indexes are stored on MY_INDEX_FILEGROUP as I am assuming that these automatically created indexes will be stored on MY_DATA_FILEGROUP that the table data will be stored in.

The table will exist on whichever filegroup contains the clustered index, because the clustered index is the data.

By default, a primary key index is clustered, so if you specify a different filegroup for that, the table ends up there.

is it possible to seperate the automatically generated indexes into a different filegroup, from the data?

Yes, but don't. Bears will eat you.

And as a seperate but related question is it possible to enforce a constraint like PRIMARY KEY or UNIQUE whilst preventing the automatic creation of the associated index?

No, this is how SQL Server enforces the constraint behind the scenes. Technically, SQL could use any mechanism to enforce constraints; it just makes the most sense to use an index to do it.

share|improve this answer
    
thanks for a very complete answer, that's everything I wanted to know –  0xor1 Aug 2 '12 at 14:25
    
@Danny: You're welcome. Glad to help. –  Jon Seigel Aug 2 '12 at 14:30
2  
"Bears will eat you" - I am so stealing that phrase! –  HLGEM Aug 2 '12 at 15:30
add comment

1.You can specify filegroup for constraint in CREATE TABLE:

CREATE TABLE Product.Product(
ProductId tinyint identity(1,1) primary key,
ProductName varchar(50)  not null,
SupportEnded date default null null,
constraint UQ_Product_ProductName UNIQUE nonclustered(ProductName) on [MY_DATA_FILEGROUP]
)

2.Not in SQLServer. That makes sense for the most cases; however, sometimes it causes creating redundant indexes. More important thing for me that SQLServer does not differentiate between unique constraint (which is logical concept) and unique index (which is implementation details)

share|improve this answer
add comment

In this above create table script,why cant you make the required changes? And also they are not created automatically,u need execute the script in order to create those indexes.

Also when you make a column as primary key by default clustered index will be created.and its a good practice to have primary key as clustered index.

see the script below:

 CREATE TABLE Product.Product(
        ProductId tinyint identity(1,1) PRIMARY KEY NOT NULL,
        ProductName varchar(50) null,
        SupportEnded date default null
    ) ON MyFileGroup_Data
    go


    CREATE unique nonclustered index noninx_ProductName  on Product(ProductName ) on filegroup_name1
share|improve this answer
    
Your answer has no PRIMARY KEY and no Unique constraint. –  ypercube Aug 2 '12 at 12:37
1  
...And since the clustered index IS the table, putting the clustered index in the index file group means that the whole table is there. –  JNK Aug 2 '12 at 13:55
    
I edited the script pls check. –  AnandPhadke Aug 2 '12 at 14:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.