November 23, 2015 at 9:55 am
I have issues with Partitioning Syntax.
I have been able to do it before but I can't get it right.
Any help would be greatly appreciated.
CREATE PARTITION FUNCTION Yearly_Date_Range_pf (Smalldatetime)
AS RANGE LEFT FOR VALUES
(N'19301231',N'19401231',N'19971231',N'19981231',N'19991231', N'20011231',N'20021231',N'20031231', N'20041231',N'20051231',N'20061231',N'20071231',N'20081231',N'20091231',N'20101231',
N'20111231',N'20121231', N'20131231',N'20141231',N'20151231')
As Partition YearlyDateRange_pf
All To ([Primary]);
Create Partition Scheme Yearly_Date_Range_ps
As Partition Yearly_DateRange_pf
All To ([Primary]);
ALTER TABLE xactControlPoint
DROP CONSTRAINT PK_xactControlPoin
ALTER TABLE xactControlPoint
ADD CONSTRAINT pk_xactControlPoint PRIMARY KEY (beginDate,xactControlPointID)
ON Scheme Yearly_Date_Range_ps
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 23, 2015 at 10:41 am
There are a couple problems that seem to be copy/paste issues, and then a couple others.
First, the As Partition YearlyDateRange_pf All To ([Primary]) bit at the end of the CREATE PARTITION FUNCTION statement is from the CREATE PARTITION SCHEME statement, and should be removed.
Second, the CREATE PARTITION SCHEME statement references a test_monthlyDateRange_pf function, not the YearlyDateRange_pf created by the first statement.
Third, when you're adding your constraint, you do ON Scheme YearlyDateRange_ps, when that should not include the word "Scheme", and should reference the column that will be used to map to the appropriate partition, so it should be ON YearlyDateRange_ps(beginDate).
A fourth, non-syntax problem is that you're only specifying dates for a RANGE LEFT on a column that allows times. With the current definition, a beginDate of '19301230 11:00 AM' would go in partition 1, while a beginDate of '19301231 11:00 AM' would go in partition 2. If you don't actually have times in your data, then you might get away with this, but I would specify the actual maximum smalldatetime value for the year instead of just the date portion anyway.
Cheers!
November 23, 2015 at 11:02 am
Thanks!
Could you please take a crack at correcting the syntax?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 23, 2015 at 2:33 pm
Jacob Wilkins (11/23/2015)
There are a couple problems that seem to be copy/paste issues, and then a couple others.First, the As Partition YearlyDateRange_pf All To ([Primary]) bit at the end of the CREATE PARTITION FUNCTION statement is from the CREATE PARTITION SCHEME statement, and should be removed.
Second, the CREATE PARTITION SCHEME statement references a test_monthlyDateRange_pf function, not the YearlyDateRange_pf created by the first statement.
Third, when you're adding your constraint, you do ON Scheme YearlyDateRange_ps, when that should not include the word "Scheme", and should reference the column that will be used to map to the appropriate partition, so it should be ON YearlyDateRange_ps(beginDate).
A fourth, non-syntax problem is that you're only specifying dates for a RANGE LEFT on a column that allows times. With the current definition, a beginDate of '19301230 11:00 AM' would go in partition 1, while a beginDate of '19301231 11:00 AM' would go in partition 2. If you don't actually have times in your data, then you might get away with this, but I would specify the actual maximum smalldatetime value for the year instead of just the date portion anyway.
Cheers!
How do I correct the following issue?
First, the As Partition YearlyDateRange_pf All To ([Primary]) bit at the end of the CREATE PARTITION FUNCTION statement is from the CREATE PARTITION SCHEME statement, and should be removed.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 24, 2015 at 3:10 am
As far as the following:
Second, the CREATE PARTITION SCHEME statement references a test_monthlyDateRange_pf function, not the YearlyDateRange_pf created by the first statement.
That was a copy and paste bolo.
I created the test variation and I had no problems.
I'm just wondering what I need to do to resolve the problem.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 24, 2015 at 3:34 am
Thanks!
This is itL
CREATE PARTITION FUNCTION Yearly_Date_Range_pf (Smalldatetime)
AS RANGE LEFT FOR VALUES
(N'19301231',N'19401231',N'19971231',N'19981231',N'19991231', N'20011231',N'20021231',N'20031231', N'20041231',N'20051231',N'20061231',N'20071231',N'20081231',N'20091231',N'20101231',
N'20111231',N'20121231', N'20131231',N'20141231',N'20151231')
As Partition YearlyDateRange_pf
All To ([Primary]);
Create Partition Scheme Yearly_Date_Range_ps
As Partition Yearly_DateRange_pf
All To ([Primary]);
ALTER TABLE xactControlPoint
DROP CONSTRAINT PK_xactControlPoin
ALTER TABLE xactControlPoint
ADD CONSTRAINT pk_xactControlPoint PRIMARY KEY (beginDate,xactControlPointID)
ON Scheme Yearly_Date_Range_ps
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 24, 2015 at 3:47 am
Jacob Wilkins (11/23/2015)
Third, when you're adding your constraint, you do ON Scheme YearlyDateRange_ps, when that should not include the word "Scheme", and should reference the column that will be used to map to the appropriate partition, so it should be ON YearlyDateRange_ps(beginDate).
You still haven't made this correction
ON Scheme Yearly_Date_Range_ps
should be
ON Yearly_Date_Range_ps(beginDate)
And you still haven't removed the
As Partition YearlyDateRange_pf
All To ([Primary]);
from the CREATE PARTITION FUNCTION
Make those changes and if there are more questions please also post the DDL for the table.
November 24, 2015 at 7:16 am
The create Create Partition Scheme fails.
CREATE PARTITION FUNCTION Yearly_Date_Range_pf (Smalldatetime)
AS RANGE RIGHT FOR VALUES
(N'19301231',N'19401231',N'19971231',N'19981231',N'19991231', N'20011231',N'20021231',N'20031231', N'20041231',N'20051231',N'20061231',N'20071231',N'20081231',N'20091231',N'20101231',
N'20111231',N'20121231', N'20131231',N'20141231',N'20151231')
GO
Create Partition Scheme Yearly_Date_Range_ps
As PartitionYearly_Date_Range_pf
All To ([Primary]);
Go
Incorrect syntax near the keyword 'All'.
The DDL is as follows:
CREATE TABLE [dbo].[xactControlPoint](
[xactControlPointID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[xactControlPointTypeID] [int] NOT NULL,
[call_id] [int] NOT NULL,
[contr_id] [int] NULL,
[xactTransactionIDValue] [varchar](50) NULL,
[beginDate] [datetime] NOT NULL,
[userName] [varchar](250) NULL,
[notes] [varchar](2000) NULL,
[reservationFlg] [bit] NULL,
[rowUpdateDate] [datetime] NULL,
[rowUpdateID] [int] NULL,
[Target_Followup_Date] [datetime] NULL,
CONSTRAINT [PK_xactControlPoint] PRIMARY KEY CLUSTERED
(
[xactControlPointID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG1]
) ON [FG1]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [IDX__xactControlPoint__Callid_xactControlPointTypeID] Script Date: 11/24/2015 9:14:06 AM ******/
CREATE NONCLUSTERED INDEX [IDX__xactControlPoint__Callid_xactControlPointTypeID] ON [dbo].[xactControlPoint]
(
[call_id] ASC,
[xactControlPointTypeID] ASC
)
INCLUDE ( [beginDate]) 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 [FG2]
GO
/****** Object: Index [IDX_xactControlPoint] Script Date: 11/24/2015 9:14:06 AM ******/
CREATE NONCLUSTERED INDEX [IDX_xactControlPoint] ON [dbo].[xactControlPoint]
(
[xactControlPointTypeID] ASC
)
INCLUDE ( [call_id],
[beginDate]) 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 [FG1]
GO
/****** Object: Index [IDX_xactControlPoint_xactControlPoint] Script Date: 11/24/2015 9:14:06 AM ******/
CREATE NONCLUSTERED INDEX [IDX_xactControlPoint_xactControlPoint] ON [dbo].[xactControlPoint]
(
[xactControlPointTypeID] ASC
)
INCLUDE ( [call_id],
[beginDate]) 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 [FG1]
GO
/****** Object: Index [IN_CallID] Script Date: 11/24/2015 9:14:06 AM ******/
CREATE NONCLUSTERED INDEX [IN_CallID] ON [dbo].[xactControlPoint]
(
[call_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG2]
GO
/****** Object: Index [IN_ContrID] Script Date: 11/24/2015 9:14:06 AM ******/
CREATE NONCLUSTERED INDEX [IN_ContrID] ON [dbo].[xactControlPoint]
(
[contr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG2]
GO
/****** Object: Index [IX_ControlPointTypeID] Script Date: 11/24/2015 9:14:06 AM ******/
CREATE NONCLUSTERED INDEX [IX_ControlPointTypeID] ON [dbo].[xactControlPoint]
(
[xactControlPointTypeID] ASC
)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 [FG1]
GO
/****** Object: Index [IX_xactControlPoint__K3_K4] Script Date: 11/24/2015 9:14:06 AM ******/
CREATE NONCLUSTERED INDEX [IX_xactControlPoint__K3_K4] ON [dbo].[xactControlPoint]
(
[call_id] ASC,
[contr_id] ASC
)
INCLUDE ( [xactControlPointTypeID],
[beginDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TE
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 24, 2015 at 7:22 am
Create Partition Scheme Yearly_Date_Range_ps
As PartitionYearly_Date_Range_pf
All To ([Primary]);
Go
You're missing a space. The partition function is named Yearly_Date_Range_pf, and you left out the space between the PARTITION keyword and the name of the partition function.
A quick look at https://msdn.microsoft.com/en-us/library/ms179854.aspx, comparing their examples would have shown you that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2015 at 7:36 am
GilaMonster (11/24/2015)
Create Partition Scheme Yearly_Date_Range_ps
As PartitionYearly_Date_Range_pf
All To ([Primary]);
Go
You're missing a space. The partition function is named Yearly_Date_Range_pf, and you left out the space between the PARTITION keyword and the name of the partition function.
A quick look at https://msdn.microsoft.com/en-us/library/ms179854.aspx, comparing their examples would have shown you that.
I execute:
Create Partition Scheme Yearly_Date_Range_ps
As PartitionYearly_Date_Range_pf
All To ([Primary]);
Go
And I get the following:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'All'.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 24, 2015 at 7:55 am
Yes, because
GilaMonster (11/24/2015)
Create Partition Scheme Yearly_Date_Range_ps
As PartitionYearly_Date_Range_pf
All To ([Primary]);
Go
You're missing a space. The partition function is named Yearly_Date_Range_pf, and you left out the space between the PARTITION keyword and the name of the partition function.
A quick look at https://msdn.microsoft.com/en-us/library/ms179854.aspx, comparing their examples would have shown you that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply