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?