CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2012
user3203331
user3203331

Reputation: 437

How can I partition index on a column if it is a bit field?

I had a Bit column in my table which consists of 0 and 1's. I need to create Partition index on that column. I tried with below queries but I am unable to do it. Can you please help me?

CREATE PARTITION FUNCTION IsLockedPF (bit)
AS RANGE RIGHT FOR VALUES (0,1)
GO
CREATE PARTITION SCHEME myPartitionScheme 
AS PARTITION IsLockedPF ALL TO ([PRIMARY]) 
GO
SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id


ALTER TABLE COE.OrdersTBU DROP CONSTRAINT PK_OrdersTBU
GO
ALTER TABLE COE.OrdersTBU ADD CONSTRAINT PK_OrdersTBU PRIMARY KEY NONCLUSTERED  (IsLocked)
   WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_TABLE1_partitioncol ON COE.OrdersTBU (IsLocked)
  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
  ON myPartitionScheme(IsLocked)
GO

My Error is below:

Msg 3728, Level 16, State 1, Line 1
'PK_OrdersTBU' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'coe.OrdersTBU' and the index name 'PK_OrdersTBU'. The duplicate key value is (0).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.

Upvotes: 1

Views: 607

Answers (2)

Metaphor
Metaphor

Reputation: 6405

It looks like you have a duplicate PK_OrdersTBU.

Upvotes: 0

Steve
Steve

Reputation: 5545

I am guessing that PK_OrdersTBU is not the name of a primary key but maybe an index. Try these:

If it is a PK, this should tell you it is:

select xtype from sysobjects where name = 'PK_OrdersTBU'

If that does not return any records, does this:

select * from sysindexes where name = 'PK_OrdersTBU'

If so, you have an index, and they get dropped differently.

Upvotes: 1

Related Questions