1

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)?

1

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

  • Didn't expect the solution to be this easy - maybe that explains exactly why I couldn't find anything about it. Enabled script indexes and partition schemes and now my script is output as expected. – Allmighty Sep 20 '19 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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