Solved

sql server 2005 - Partitioning tables

Posted on 2011-09-22
5
197 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

617 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