• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

sql server 2005 - Partitioning tables

Hi,

I am partitioning one of the tables in my database.
Lets say I have a global company with employees in multiple countries.
country is a FK in Employee table.

I have already created the partition function, partition scheme and file group.

Employee table looks like this:

 
CREATE TABLE [dbo].[Employee](
	[Emp_ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
	[Emp_Num] [int] NOT NULL,
	[Type] [nvarchar](10) NULL,
	[Quarter] [int] NULL),
	CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[Emp_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Open in new window


How can I add below line to the create table query above?

  ON partScheme_Country(partFunc_Country)

Thanks in advance
0
shmz
Asked:
shmz
  • 3
  • 2
1 Solution
 
WizillingCommented:
CREATE TABLE [dbo].[Employee](
      [Emp_ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
      [Emp_Num] [int] NOT NULL,
      [Type] [nvarchar](10) NULL,
      [Quarter] [int] NULL),
      CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
      [Emp_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON partScheme_Country(partFunc_Country)
GO
0
 
shmzAuthor Commented:
Wizilling, not sure about the brackets in the query?

CREATE TABLE [dbo].[Employee](
      [Emp_ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
      [Emp_Num] [int] NOT NULL,
      [Type] [nvarchar](10) NULL,
      [Quarter] [int] NULL), ---I shall remove this bracket it was  a mistake
      CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
      [Emp_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

--where is the opening bracket?
) ON partScheme_Country(partFunc_Country)
GO
0
 
WizillingCommented:
oh right.. please get rid of the extra closing bracket.

CREATE TABLE [dbo].[Employee](
      [Emp_ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
      [Emp_Num] [int] NOT NULL,
      [Type] [nvarchar](10) NULL,
      [Quarter] [int] NULL),
      CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
      [Emp_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ON partScheme_Country(partFunc_Country)
GO
0
 
shmzAuthor Commented:
Thanks, I let you know as soon as I get a chance to test this.
0
 
shmzAuthor Commented:
Thanks
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now