May 28, 2010 at 11:18 am
Create primary key in partitioned table is possible in SQL Server 2008?
My table is partitioned by date and I need to create primary key field CodID Int
Tks
Luis.
May 28, 2010 at 1:49 pm
lrodriguessp (5/28/2010)
Create primary key in partitioned table is possible in SQL Server 2008?My table is partitioned by date and I need to create primary key field CodID Int
As far as underlying global unique index is non-clustered I see no problem.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2010 at 2:06 pm
Tks replay
Do you help me in sytax?
This sintax is wrong:
CREATE TABLE Sales (
SalesID int not null
SalesDate datetime not null
CONSTRAINT PK_SalesID PRIMARY KEY CLUSTERED (CPFCNPJ)
)
on ps_Date(SalesDate)
ps_Date is my function scheme.
Tks,
Luis
May 28, 2010 at 2:08 pm
Tks replay
Do you help me in sytax?
This sintax is wrong:
CREATE TABLE Sales (
SalesID int not null
SalesDate datetime not null
CONSTRAINT PK_SalesID PRIMARY KEY CLUSTERED (SalesID)
)
on ps_Date(SalesDate)
ps_Date is my function scheme.
Tks,
Luis
May 28, 2010 at 2:18 pm
lrodriguessp (5/28/2010)
CREATE TABLE Sales (SalesID int not null
SalesDate datetime not null
CONSTRAINT PK_SalesID PRIMARY KEY CLUSTERED (CPFCNPJ)
)
on ps_Date(SalesDate)
CLUSTERED? Didn't we agree PK should be enforced by an underlying unique, global non-clustered index? 😀
Do this...
1st... Create your partitioned table.
2nd... Create PK on it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2010 at 2:39 pm
Can I create PK integer (nonclustered) and the partition function datetime?
Partition Function:
CREATE PARTITION FUNCTION pf_Date (datetime) AS
RANGE right FOR VALUES (2007-01-01', '2008-01-01', '2009-01-01' );
GO
Partition Scheme:
CREATE PARTITION SCHEME ps_Date AS
PARTITION pf_date
TO ([FG1], [FG1], [FG1], [FG1])
GO
This sintax is wrong:
CREATE TABLE Sales (
SalesID int not null
SalesDate datetime not null
CONSTRAINT PK_SalesID PRIMARY KEY NONCLUSTERED (SalesID)
)
on ps_Date(SalesDate)
Where the partition had been stored (physical file) ?
I'm not understand the correct syntax.
Thanks for your fast replay.
May 28, 2010 at 2:52 pm
lrodriguessp (5/28/2010)
Can I create PK integer (nonclustered) and the partition function datetime?Partition Function:
CREATE PARTITION FUNCTION pf_Date (datetime) AS
RANGE right FOR VALUES (2007-01-01', '2008-01-01', '2009-01-01' );
GO
Partition Scheme:
CREATE PARTITION SCHEME ps_Date AS
PARTITION pf_date
TO ([FG1], [FG1], [FG1], [FG1])
GO
This sintax is wrong:
CREATE TABLE Sales (
SalesID int not null
SalesDate datetime not null
CONSTRAINT PK_SalesID PRIMARY KEY NONCLUSTERED (SalesID)
)
on ps_Date(SalesDate)
Where the partition had been stored (physical file) ?
I'm not understand the correct syntax.
Thanks for your fast replay.
1- Partition Function
I do not think partition function is parsing at all, first limit lacks left parenthesis.
Also, for datetime datatype partition key Microsoft recommends to use yyyymmdd format. Please check BOL
2- Partition Scheme
If you want all partitions to go to FG1 filegroup I would use "ALL TO FG1" syntax and if FG1 turns out to be PRIMARY filegroup I would use ALL TO PRIMARY" syntax.
3- Create Table and PK
I strongly suggest to divide syntax in two, first create the table then add the PK
That way you will know when you have solved table partitioning issue and when PK is created or not.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 29, 2010 at 7:41 am
Perhaps some demo code will make the issues clearer to see:
CREATE PARTITION FUNCTION
PF_Date (DATETIME)
AS RANGE RIGHT
FOR VALUES ('2007-01-01', '2008-01-01', '2009-01-01' );
GO
CREATE PARTITION SCHEME
PS_Date
AS PARTITION PF_Date
ALL TO ([PRIMARY]); -- Change to [FG1] if you like
GO
-- === RUN THE FOLLOWING BATCHES ONE AT A TIME === --
-- Error: Column 'SalesDate' is partitioning column of the index 'PK dbo.Sales SalesID'. Partition columns for a unique index must be a subset of the index key.
CREATE TABLE dbo.Sales1
(
SalesID INTEGER NOT NULL,
SalesDate DATETIME NOT NULL,
CONSTRAINT [PK dbo.Sales1 SalesID]
PRIMARY KEY CLUSTERED (SalesID)
ON PS_Date(SalesDate)
);
GO
-- Error: Column 'SalesDate' is partitioning column of the index 'PK dbo.Sales SalesID'. Partition columns for a unique index must be a subset of the index key.
CREATE TABLE dbo.Sales2
(
SalesID INTEGER NOT NULL,
SalesDate DATETIME NOT NULL,
CONSTRAINT [PK dbo.Sales2 SalesID]
PRIMARY KEY NONCLUSTERED (SalesID)
ON PS_Date(SalesDate)
);
GO
-- Success, but primary key is on (SalesID, SalesDate)
CREATE TABLE dbo.Sales3
(
SalesID INTEGER NOT NULL,
SalesDate DATETIME NOT NULL,
CONSTRAINT [PK dbo.Sales3 SalesID]
PRIMARY KEY CLUSTERED (SalesID, SalesDate)
ON PS_Date(SalesDate)
);
GO
-- Success, the table (a heap) is partitioned on SalesDate
-- But the PK index is not partitioned (will prevent SWITCH operations for example)
CREATE TABLE dbo.Sales4
(
SalesID INTEGER NOT NULL,
SalesDate DATETIME NOT NULL,
CONSTRAINT [PK dbo.Sales4 SalesID]
PRIMARY KEY NONCLUSTERED (SalesID)
ON [PRIMARY]
)
ON PS_Date(SalesDate);
GO
-- Tidy up
DROP TABLE dbo.Sales3;
DROP TABLE dbo.Sales4;
DROP PARTITION SCHEME PS_Date;
DROP PARTITION FUNCTION PF_Date;
May 29, 2010 at 8:32 am
Exactly,
I have to use the primary key in the primary partition.
There will be a performance gain?
Thanks Paul.
Luis.
May 29, 2010 at 8:47 am
lrodriguessp (5/29/2010)
Exactly, I have to use the primary key in the primary partition.
Sorry, but that sentence does not make sense to me. 🙁
If you mean that you have to include the partitioning column in the primary key...then no, you don't. As my demo script shows, you can also create a non-partitioned index (primary key)...but it does mean you would have to drop that index before any SWITCH operations, and re-create it afterward. That is usually not as bad as it sounds, since SWITCH operations tend to be performed during a maintenance window anyway.
There will be a performance gain?
Luis, it depends on many factors. On 2005 you tend not to see much of a performance gain in general, though certain operations may benefit a little. Other operations (particularly those with a parallel plan) may well see reduced performance. My rule of thumb, in 2005 only, is to only partition data for maintenance reasons - not to improve performance.
In 2008, partitioning for performance is much more achievable due to the many improvements in that release. There are still some fairly subtle performance 'bear traps' to watch out for.
My advice is not to jump in to partitioning without a pretty deep knowledge of the subject, and some genuine testing - before implementation starts!
There are some good White Papers on this subject:
Also see Craig Freedman's blog entries:
http://blogs.msdn.com/b/craigfr/archive/2008/07/15/partitioned-tables-in-sql-server-2008.aspx
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply