Solved

sql server 2005 - Partitioning tables

Posted on 2011-09-22
5
196 Views
Last Modified: 2012-05-12
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
Comment
Question by:shmz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Wizilling
ID: 36584741
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
 

Author Comment

by:shmz
ID: 36584779
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
 
LVL 13

Accepted Solution

by:
Wizilling earned 500 total points
ID: 36584806
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
 

Author Comment

by:shmz
ID: 36899851
Thanks, I let you know as soon as I get a chance to test this.
0
 

Author Closing Comment

by:shmz
ID: 37067464
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question