4

I have table ORDERS .Where there are Million rows in it .I need to add Pationed Index on Order to store data in one of two files based on the new IsLocked field.But when i checked table there is a Non clustered index on ISLocked Column.IsLocked field consists of 1 and O's.Can you guys suggest me best approach.

CREATE TABLE [Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderNo] [varchar](50) NULL,
    [AccountNo] [varchar](20) NULL,
    [CompleteDate] date null,
    [LastUpdateDate]) date null,
    [AccountID]  int NULL,
    [IsWarranty] [bit] NULL,
    [IsEmailAddressFound] [bit] NULL,
    [IsLocked] [bit] NOT NULL,
    [LinkOrderID] [int] NULL
)

But already there is a

CREATE NONCLUSTERED INDEX [IX_IsLockedAccountID] ON [coe].[Orders]
(
    [IsLocked] ASC,
    [AccountID] ASC
)
INCLUDE (     [OrderNo],
    [CompleteDate],
    [LastUpdateDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Orders    IX_IsLockedAccountID    NONCLUSTERED    72057594556710912    1    1146462

Can you giys please help me how to do partioning as i am completely new to Table partioning .

  • What are you actually trying to do? Partition the index? What have you tried? – Namphibian Mar 26 '14 at 4:54
7

Strictly speaking creating a non-clustered index on a partitioning scheme is done exactly as the clustered index, much as Thomas already showed you:

CREATE NONCLUSTERED INDEX <indexname> ON <tablename>
(<columns>)
INCLUDE (<columns>)
ON <partitioning scheme>;

Having a non-aligned non-clustered index is usually not recommend, see Special Guidelines for Partitioned Indexes:

Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. This is especially the case when the index is not aligned with its base table or is not aligned with its clustered index, if the table already has a clustered index applied to it.

Similar memory problems also manifest in queries, not only during build, specially if you use versions prior to SQL Server 2012.

But the real problem is the futility of partitioning a non-clustered index by a boolean. Aside from the general issues with misunderstanding partitioning and abusing this very expensive and invasive feature (see How To Decide if You Should Use Table Partitioning), partitioning on a boolean is non-sense, especially on a column that appear to be very volatile (changes often) and highly skewed (far more 0s that 1s). Filtered indexes are probably what you're after. See Filtered Indexes vs. Table Partitioning.

And, finally, having an 'isLocked' column is never a sign of healthy data modeling. In fact a big code smell. You probably need to read about application locks and/or using tables as queues.

  • Hi Remus - are you using the word "expensive" in the literal sense...as in cost of enterprise license? – Bill Anton Aug 7 '14 at 10:57
  • @BillAnton: no. I mean dev/ops cost, and not the last actual query runtime cost. – Remus Rusanu Aug 7 '14 at 14:48
1

It looks like you just want to build a clustered index on the table that targets the partitioning column

Assuming you have the partitioning function and schema defined as psBit(bit) you simply do this:

CREATE CLUSTERED INDEX CIX ON Orders (AccountId, IsLocked) 
ON psBit(IsLocked)

The "magic" is the ON part at the end of the statement. If IsLocked is a bit, its probably better to put it at the end.

May I ask why you are bothering with partitioning in this case? a 1M row table is tiny, there is really no reason to use partition for such small table.

Your Answer

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