SQL Server : explicitly create an index on a primary key and unique fields

Question

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?

Answer 1

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.

Answer 2

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)

Answer 3

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