How to do partition on a non clustered index column? 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 .
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 &lt;indexname&gt; ON &lt;tablename&gt;
(&lt;columns&gt;)
INCLUDE (&lt;columns&gt;)
ON &lt;partitioning scheme&gt;;


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.