Unique on Partition View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique on Partition View

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2007-09-12 : 10:26:16

Here is my code to build the parition view.
at the end, i try to add a unique index, but it fail.
why ?

alter database CheckParti
ADD FILEGROUP fgALLINT32_PrimaryOnly_0250

alter database CheckParti
ADD FILEGROUP fgALLINT32_PrimaryOnly_0500

alter database CheckParti
ADD FILEGROUP fgALLINT32_PrimaryOnly_0750

alter database CheckParti
ADD FILEGROUP fgALLINT32_PrimaryOnly_1000
------------------------------------------

alter database CheckParti
ADD FILE
(
NAME = fnALLINT32_PrimaryOnly_0250,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\fnALLINT32_PrimaryOnly_0250.NDF',
SIZE = 12 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 3 MB
) TO FILEGROUP fgALLINT32_PrimaryOnly_0250

alter database CheckParti
ADD FILE
(
NAME = fnALLINT32_PrimaryOnly_0500,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\fnALLINT32_PrimaryOnly_0500.NDF',
SIZE = 12 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 3 MB
) TO FILEGROUP fgALLINT32_PrimaryOnly_0500

alter database CheckParti
ADD FILE
(
NAME = fnALLINT32_PrimaryOnly_0750,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\fnALLINT32_PrimaryOnly_0750.NDF',
SIZE = 12 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 3 MB
) TO FILEGROUP fgALLINT32_PrimaryOnly_0750

alter database CheckParti
ADD FILE
(
NAME = fnALLINT32_PrimaryOnly_1000,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\fnALLINT32_PrimaryOnly_1000.NDF',
SIZE = 12 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 3 MB
) TO FILEGROUP fgALLINT32_PrimaryOnly_1000
------------------------------

create partition function PF_ALL_INT32_PrimaryOnly( INT )
AS RANGE LEFT
FOR VALUES (
250000000, -- 1
500000000, -- 2
750000000 -- 3
)

CREATE PARTITION SCHEME PS_ALL_INT32_PrimaryOnly
AS PARTITION PF_ALL_INT32_PrimaryOnly
TO ( fgALLINT32_PrimaryOnly_0250, fgALLINT32_PrimaryOnly_0500,
fgALLINT32_PrimaryOnly_0750, fgALLINT32_PrimaryOnly_1000
)

CREATE TABLE [dbo].[ALL_INT32_PrimaryOnly] (
[RunNum] [int] NOT NULL,
[PrimaryID] [int] NOT NULL identity(1,1),
[FromPrevPrimary] [int] NULL
)
ON PS_ALL_INT32_PrimaryOnly( [RunNum] )

alter table [dbo].[ALL_INT32_PrimaryOnly]
add constraint [PK_ALL_INT32_PrimaryOnly] Primary Key Clustered ( [RunNum] ASC )
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 100
)
ON PS_ALL_INT32_PrimaryOnly( [RunNum] )
go

create unique index UI_ALL_INT32_PrimaryOnly on [ALL_INT32_PrimaryOnly]( PrimaryID ASC )
with FILLFACTOR = 100
on PS_ALL_INT32_PrimaryOnly
go

Error:
Partition function 'PF_ALL_INT32_PrimaryOnly' uses 1 columns
which does not match with the number of partition columns used to partition the table
or index.

help ?
thank
Noam

Noam Graizer

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-12 : 20:23:21

It's a partitioned table not a partitioned view.
see
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
read the bit about indexes - in particular.
Unique indexes must contain the partitioning column as an indexed column.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources