Sql-server – How to find out if an index is aligned

partitioningscriptingsql server

I'm struggling to find any confirmation within sql server or sql management studio that my index is in fact aligned with my partition.

I'm creating a primary key constraint on a partitioned table:

CREATE PARTITION FUNCTION [pfDATA](bigint) AS RANGE RIGHT FOR VALUES (0);
CREATE PARTITION SCHEME [psDATA] AS PARTITION pfDATA TO ([P_CURRENT], [P_CURRENT]);
CREATE TABLE [dbo].[DATA](
    [DATAID] [bigint] NOT NULL,
    [VALUE1] [int] NOT NULL,
    [VALUE2] [int] NOT NULL,
    [VALUEetc] [int] NOT NULL
    CONSTRAINT [PK_DATA] PRIMARY KEY CLUSTERED 
    (
        [DATAID] ASC
    ) ON psDATA(DATAID)
) ON psDATA(DATAID);

SQL management studio doesn't seem to give me any feedback visually about the characteristics of this index.

What I normally do when the application doesn't tell me what I need to know visually, is let the application generate a CREATE script (for the key/index or table, it doesn't matter in this case). What I expect to see is the partition scheme being mentioned in the constraint clause, but what I get is no mention of any partitioning:

CREATE TABLE [dbo].[DATA](
    [DATAID] [bigint] NOT NULL,
    [VALUE1] [int] NOT NULL,
    [VALUE2] [int] NOT NULL,
    [VALUEetc] [int] NOT NULL
    CONSTRAINT [PK_DATA] PRIMARY KEY CLUSTERED 
    (
        [DATAID] ASC
    )
);

Is there any way to check if my indexes are constructed correctly (and are in fact, aligned)?

Best Answer

I guess you want to see partitions in scripted objects. All you need to do is to go to Tools-Options-Scripting and play with checkboxes there: enter image description here

Related Question