Table Partitioning With Multiple Keys Coulmns

Apr 18, 2006

Table partitioning with multiple key coulmns

Hi champs!

I have one table with around 600 million records.

I want to partition this table on 5 different coulumns.

year, month. coulumn3, coulumn4 and coulumn5.

How do I do this best?

What is the best way of doing this; is there any best practice on ths?

The obvious thing is to make a partition funktion based on the year and month coulmns, but I also want to partition on coulumn3, coulumn4 and coulumn5.



Many thanks

Kurlan

View 1 Replies


ADVERTISEMENT

How Can A Foregin Key Reference Multiple Coulmns Of Clustered Key

Sep 25, 2007



Hi there...

Sounds like a veryy silly question but I somehow got stuck with this & cant think anymore

Basically, I have got a table 'Order_Product' with Clustered Primary Key of columns 'Order_Number' & 'Product_Id'.
I am creating a new table Order_Discussion' with column 'Product', which is a Foreign Key & should reference 'Order_Number' & 'Product_Id' of table 'Order_Product'.

I am trying something like..

Create Table Order_Discussion (
Discussion_Id int,
Product int Foreign Key References Order_Product(Order_Number, Product_Id)
)

but its giving error..
More than one key specified in column level FOREIGN KEY constraint, table 'Order_Discussion'.

Any help will be greatly appreciated.
Cheers!!

View 6 Replies View Related

Query Table Based On Multiple Keys

Jul 23, 2005

Hey,I am having some confusion about how to formulate this particularquery.I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with thecolumns a1,a2,a4 forming the primary key. Table B again has 3 columnswith b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.All columns are of the same datatype in both tables. Now I want to getrows from table A which are not present in table B. Whats the best wayof doing this?Thanks--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict235166.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725

View 6 Replies View Related

Script For Creating Table With Multiple Primary Keys

Feb 1, 2007

Hi,

I am trying to execute following sql script in sql-server 2000 query analyzer

CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY,
course_id varchar(10) NOT NULL PRIMARY KEY)

Its give's me following error :-
Cannot specify multiple primary key constraint

Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.

Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys.
Will it allow me to have following records in the user_courses table?
user_id(P.K) course_id(P.K)
bob CRS235
alice CRS235
Tim CRS235
tom CRS635

So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?

Thanks,

View 4 Replies View Related

Analysis :: SSAS Design - Multiple Keys Of Same Dimension In Fact Table

Mar 19, 2012

I am developing a BI solution on SQL Server 2008 R2 and how to handle multiple referances to the same dimension from a fact table!

Here is the scenario;

Fact_Contracts (# M)
ServiceProvider_CompanyID, Client_CompanyID, Amount_USD 
Dim_Company( hundreds)
ID,  CityID, ProfessionID, CompanyName
Dim_City
ID, CityName
Dim_Profession
  ID, ProfessionName

As u can see there is two company references in my fact table, and the schema is in snowflake. My customer requirements state that the Contracts' amounts can be aggregated/filtered for/by, ServiceProviderCompany, its city/profession or ClientCompay, its city/profession.

First thing came in to my mind is to dublicate whole dimension structure (one for serviceproviders, one for clients), which i thought that there should be another way around?

View 5 Replies View Related

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

Apr 11, 2006

Hello again,

I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:

Code:

CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

class_title
VARCHAR(50)
NOT NULL,

class_grade
SMALLINT
NOT NULL
DEFAULT 6,

class_tardies
SMALLINT
NOT NULL
DEFAULT 0,

class_absences
SMALLINT
NOT NULL
DEFAULT 0,

CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)

This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:

Code:

CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

grade_id
INT
NOT NULL,

CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),

CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)

Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?

In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?

Thank you for your assistance.

View 1 Replies View Related

Multiple Levels Of Partitioning In EE

Jun 4, 2007

Hi All,

we are building a DW for a company that operates in 10 countries with the home country being the major portion of the data......



Previous efforts have always had the data separated by schemas and so to ask a question about a specific country required the schema number to be provided.



I am proposing that the 10 schemas, and therefore 10x the number of tables, indexes etc, be removed in favour of using partitioning.



However, we want to partition by country and by periods...that is we would like to create monthly partitions as normal.



No matter how I read the documentation and test this out, it seems to me that this multiple levels of partitioning can only be achieved if I create a field on the table that is some manipultion of the key for the company reporting structure and the period. I think I can take the first, add 10M and then add the period key.



But I am unsure if the optimiser is going to do it's partition elimination properly on such a calculated field.



Has anyone attempted such a multi-level partitioning scheme in SQL Server? I am thinking people must have as one level of partitioning was seen to be too restrictive many years ago.....



Thanks in Advance for your comments.



Best Regards

Peter Nolan

View 9 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

Partitioning A Table

Mar 27, 2008

i have a table named "user" in which user which are located at different places within a city are recorded.
i want to group user with respect to there location like users of northern region are recorded first then users of western region and so on.
tell me from horizontal and vertical partitioning wh technique is better or i should use some other technique.
thanks  for ur consideration.

View 5 Replies View Related

Table Partitioning

Nov 14, 2000

Hi,
I want to know more on table partitioning.I do not know where to get the right info.from.
I have a doubt - if a table is partitioned horizontally how does a query identifies where to pick up the data from i.e. from which part of partitioned table?

View 2 Replies View Related

Table Partitioning

Jun 16, 2008

i want to partition a table containing about 3 million rows. The partition column will be of datetime type.

following is the partition function i have used
create partition function MyPartFun
(datetime) as range left for values ('07/30/2007','09/30/2007','11/30/2007','01/30/2008','04/30/2008')


following is the partition scheme i have used
create partition scheme PartScheme as
partition MyPartFun all to ([primary])


i know how to add partition column while creating the table
But dont know how to add above partition scheme to an already populated table
Plz help...

View 2 Replies View Related

Table Partitioning

Apr 24, 2007

Hi,
I have a database created using Enterprise Manager Wizard.
For example datafile db1_data.mdf and log file db1_log file exists.
All the tables are created in datafile db1_data.mdf.
Now to improve performance I want to implement table partitioning.
Can anybody tell me howto implement it with existing strutcure.
Suppose there is table Mytable in which all update and delete actions are performed regularly.And it contains about 10,0000 records.
I want to partition the table so that it contains 5000 records.

Solution with example highly appreciated.

Satish

View 7 Replies View Related

Table Partitioning

Dec 13, 2007

Hi Experts,
I am new to Table Partitioning, Can any body guide me how to do table partitioning?
any way here is my scenario, we are having one database called "DATA" in SQL 2000 server and we have migrated to SQL 2005 by using backup and restore. and "DATA" is having about 15 tables and they are very very very big in size. and they dont have any index on a coulum name "DATETIME", but i want make table partition according to that perticular field "DATETIME" and right present we are having 6 months of data.
So, how to proceed further?
Your help will be appreciable..

View 1 Replies View Related

SQL Table Partitioning

Mar 19, 2007

 

i am trying to partition an sql table in sql server 2005, i created the partition schema and the data files that i want the data to be filled in after the partition. After the partition is finished sql gave me partition is successful , but i noticed that the size of data files i created has not increased and their sizes are the same.

notice: i have a clustered index on this table, so i dropped this index and recreated it 

 Bellow the script that i am using

 

 

and thank you for your help in advance

View 1 Replies View Related

What Does Mean Table Partitioning ?

Mar 7, 2008

Hello frnds....what does mean by this ?

View 1 Replies View Related

[Table Partitioning] What Is The Best Method?

Oct 11, 2006

Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Le

View 2 Replies View Related

Partitioning A Large Table - How Much Is Too Much?

Nov 14, 2007

Hi folks! I'm looking for advice on partitioning a large table. In the DDL below I've changed names to protect the guilty.

My table has this schema:


CREATE TABLE [dbo].[BigTable]
(
[TimeKey] [int] NOT NULL,
[SegmentID] [int] NOT NULL,
[MyVal] [tinyint] NOT NULL
) ON [BigTablePS1] (TimeKey) -- see below for partition scheme

alter table [dbo].[BigTable] add constraint [PK_BigTable]
primary key (timekey asc, SegmentID asc)

-- will evaluate whether this one is needed, my thinking is yes
-- based on the expected select queries.
create index NCI_SegmentID on BigTable(SegmentID asc)


The TimeKey column is sort of like a unix time. It's the number of minutes since 2001/01/01, but always floored to a 5 minute boundary. so only multiples of 5 are allowed.

Now, this table will be rather big. There are about 20k possible SegmentIDs. For every TimeKey from 2008/01/01 to 2009/01/01 (12 months), I'll have on the order of 20000 rows, one for each SegmentID.

For the 12 month period, there are 365*24*60/5=105120 possible TimeKey values. So the total rowcount is over 2 billion. (20k * 105120)

Select queries are expected to be something like this:


-- fetch just one particular row...
select MyVal from BigTable
where TimeKey=5555 and SegmentID=234234

--fetch for a certain set of SegmentID and a particular time...
select
b.SegmentID
,b.MyVal
from BigTable b
join OtherTable t on t.SegmentID=b.SegmentID
where b.TimeKey=5555
and t.SomeColumn='SomeValue'


Besides selects, also I need to be able to efficiently issue update statements against the table with new values in the MyVal column based on a range of TimeKey values (a contiguous span of a few days) and sets of about 1000 SegmentID. updates would always look like this:


update t
set t.MyVal=p.MyVal
from BigTable t
join #myTempTable p on t.TimeKey=p.TimeKey
and t.SegmentId=p.SegmentId


where #myTempTable would have order of 1000*24*60 rows in it, all with contiguous TimeKey values, and about 1000 different SegmentID values. #myTempTable also has a clustered pk on (timekey asc, SegmentId asc).

After the table is loaded, it would never get any inserts or deletes. only selects and updates.

Given the size, and the nature of the select and update queries, this table seems like a good candidate for partitioning. I'm thinking it makes sense to partition on TimeKey.

So my question is, is it stupid to create a separate partition for each day in the year long span of TimeKeys this table covers? That would mean 365 partitions in the partition function and partition scheme. Something like this:


CREATE PARTITION FUNCTION [BigTableRangePF1] (int)
AS RANGE LEFT FOR VALUES
(
3680640 + 0*1440, -- 3680640 is the number of minutes between 2001/01/01 and 2008/01/01
3680640 + 1*1440,
3680640 + 2*1440,
3680640 + 3*1440,
...snip...
3680640 + 363*1440,
3680640 + 364*1440,
3680640 + 365*1440
);
GO

CREATE PARTITION SCHEME [BigTablePS1]
AS PARTITION [BigTableRangePF1]
TO
(
[PRIMARY],[PRIMARY],[PRIMARY],
...snip...
[PRIMARY],[PRIMARY],[PRIMARY]
);
GO


does anyone have any experience with partitioned tables with so many partitions? Is a few hundred partitions too many? From my understanding of partitions, seems like having so many will be ok. Is it somehow worse than having hundreds of tables in a database?

Even with one partition for each day, I'll still have 24*60*20000/5 ~ 5m rows in each one.

5m seems like a manageable number. 2b does not.



elsasoft.org

View 2 Replies View Related

SQLServer Table Partitioning

Jun 5, 2007

Hi!I have a question:I already have a DB that uses partitions to divide data in USCounties, partitioned by state.Can I use TWO levels of partitioning?I mean... 3077 filegroups and 50 partition functions that addressthem, but can I use another function to group the 50 states?Thanks!Piero

View 15 Replies View Related

Table,index Partitioning

Apr 30, 2007

We had data in tables for multiple users (Logins) .Each user data is identified by a one column named €œUSER€?. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.


Each table has a column USER and we are queering data based on login user .this is the foreign key of USER table. Each view contains user column in where clause .So for every query we are searching all records .instead of that is there any way to get data with out searching all records.

I heard about table Partitioning, index Partitioning, view Partitioning. Are they helpful to boost my query performance?

And also let me know is there any good way of designing apart from above options

View 3 Replies View Related

Table Locking In Partitioning

Aug 10, 2006

Hi,

I have the following doubt about table lockinglocking in case of partitioning:-

Say we have 5 partition on the table Employee on the key Joining_Date and when we run 5 select queries on each of the parition in parallel will there be locking on the table when the 1st query is running or all the 5 queries can run in parallel. Basically, I am trying to see if parallelism and partitioning can work in sync or there will be locking at the table level if I don't specify any query hints?

Any help in this regard is highly appreciated.

Thanks,
Ritesh





View 1 Replies View Related

Multiple Foreign Keys

Jun 25, 2004

What are the possible issues I could run in to having multiple foreign keys in a table. Here is why I ask. I have a db (sql server) that has a participant table, a forum table, and a forum reply table. Every record in the forum reply table is associated with the forum table via a PK-FK relationship w/cascading updates/deletes. The participants who post in these tables are not tied back to the participant table via a PK-FK relationship w/cascading updates/deletes. Should they be?

The problem I ran in to is that one particpant was deleted from the participant table but a post with their partid still existed in the forum or forum reply tables.

My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right? If I am right, what do I have to be careful of if I do that?

Let me know! Thanks!!

View 1 Replies View Related

Multiple Foreign Keys

Nov 20, 2004

I am constructing a db in sql server 2000 that will score cross-country running meets. I have an individual results table that needs to only contain participants that are entered as participants but are specific to a certain race as well. Can I have this table be linked back to TWO other tables via the PK-FK relationship and what issues might I have doing that?

Thanks!

View 5 Replies View Related

Multiple Foreign Keys

Dec 30, 2004

I am having trouble creating multiple foreign keys on a table so that I can set up cascading update and cascading delete from two different primary tables. I am using the diagram to do this but when I try to save the diagram I get the following error.



ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.



What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?

Thanks!

View 2 Replies View Related

How To Use Multiple Keys As Primary Key

Jun 18, 2008

Hi Guys,

Can someone tell me what is the best way to check 2 tables lets say tb1 and tb2, to find those customers that are in tb1 but not in tb2 using four fields(cardno,spend,date,refno) as the primary key. Duplicates fields are in tb1

View 3 Replies View Related

ALTER DATABASE With Table Partitioning

Jan 15, 2007

Conrad writes "I'm currently working on Table Partitioning. I have done everything succesfull for partitioning, what I'm struggling with is to use
" SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " to get the last used FileGroup. Now this works just fine, but when I run the following script:
--Decalre variables
DECLARE @LastFilegroupName VARCHAR(50)
DECLARE @FilegroupName VARCHAR(50)

--Retuns the next FileGroup to be used
SET @LastFilegroupName = (select MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY')
SET @LastFilegroupName = Replace(@LastFilegroupName,'FileGrp','')
SET @FilegroupName = 'FileGrp' + CAST((@LastFilegroupName + 1) as varchar(10))

--Alter database statement
ALTER DATABASE VadivelTesting
ADD FILEGROUP @NewFG_Name

This script gives the following error "Incorrect syntax near '@NewFG_Name'."

When I give it a static name it works fine, but not with the variable.

Please can someone help me, I'm in struggeling with this one."

View 1 Replies View Related

Table Partitioning On A RAID 5 Server

Dec 2, 2006

Is there any benefit in creating seperate file groups for a partitionedtable on a multi-processor server with RAID5 and 1 Logical Drive?

View 8 Replies View Related

Mirroring :: Table Partitioning Implementation

Oct 30, 2015

In our environment we have some "Huge" tables with around 2 million records. Planning to implement table partition.We have 4 LUN's. 

View 3 Replies View Related

Can Partitioning A Table Boost My Performance?

Sep 28, 2007

I have an existing database with a table of about 50 milion records. There are also about 20 other tables, but they are alot smaller. The large table has a uniqueidentifier as it's Primary key (not sequential) and a forien key to a 'parent' table. The table also has a column telling when it was created. So, a bit simplified, it looks like:

ChildTable
---------------
Id uniqueidentifier <PK>
ParentId uniqueidentifier <FK>
CreationDate DateTime

ParentTable
-----------------
Id uniqueidentifier <PK>
CreationDate DateTime


Most of the questions accessing the Child table (the large table) is doing so by referensing the parent table, and not the CreatingDate, i.e.
SELECT *
FROM ChildTable
WHERE ParentId = '......'

All records with a specific ParentId will have very similiar CreationDates.

Now, my question is, will Partitioning the ChildTable boost performance for me? In case it will, what column(s) would define the Partitions? If I do it by CreationDate, a select-query like the one above will have to scan all partitions anyway, doesn't it? Doing it by Id isn't soo easy either I guess? If it helps, it might be possible to change the primary keys in the tables to have sequential guids.

Is there perhaps a performance tool to get help with suggestions about how to partition the table? Something like the 'Performance dashboard' reports, but for partitioning?

Regards Andreas



View 10 Replies View Related

Alter Table Partitioning Function

Mar 27, 2008


Hi,
I have a big table which is partitioned , I need to change the portioning function (including the column used by partition key). I don€™t want to change the table or use a temp table as there are a lot of dependent objects to this table

I wrote:

DECLARE @partition_count INT,@cmd VARCHAR(8000)
SELECT @partition_count=MAX(p.partition_Number) FROM sys.allocation_units A,sys.partitions P ,sys.data_spaces D
WHERE P.OBJECT_ID=OBJECT_ID('TABLEA')
AND A.container_id=P.Partition_id
AND A.data_space_id=D.data_space_id
SELECT @partition_count

WHILE (@partition_count>1)
BEGIN
SELECT @cmd='ALTER PARTITION FUNCTION MainPartitionFunction() MERGE range ('+CONVERT(VARCHAR(10),@partition_count-1)+')'
exec (@cmd)
SELECT @partition_count=@partition_count-1
End

Which will merge all the existing partitions to one. But I don€™t seem to be able to find a way change the existing funtion. ( in need to change the partition key all together)
Is there a way to disable or drop parition of the table all together (convert the table to a non-partitioned table) and then I can re define the parition function again.
Any ideas?
Shaunt

View 2 Replies View Related

Downside Of Multiple Primary Keys

Oct 11, 2006

Hi, from the "best practice" perspective, how convenient is to use multiple field primary keys in a table, how convenient is it to replace a multifield pk with an autoincrement field to represent such a table?
I tried using a calculated field product of appending the fields that cvompose the primary key, but MySQL for example, does not provide with that functionality, which makes me wonder it doing so in SQL Server is a good practice at all.


Thank you

View 1 Replies View Related

SQL 2012 :: Dynamic Partitioning Of Table Server

Dec 3, 2014

I need Dynamic Partition of SQL Table.

1. What is the best practice for partitioning (on date column)

2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data

3. Is creating partition occupies more disk space?

4. Is every partition would need index?

View 7 Replies View Related

Transact SQL :: Dynamic Partition For Partitioning Table

Jul 31, 2015

I am new to Partitioning tables. My scenario is as listed below.

I am getting Monthly Transaction data on Every First Monday of the Month and I want to do partition for those data.

For Example: Let's say I will get my next monthly data on August 3rd 2015 which is First Monday of the month of August.

I want those Transaction data to go in new partitioned FileGroup in my existing partitioned table. How can I do partition for this kind of scenario ? Can we create one or multiple Stored Procedure which will create New Partition and load data in that partition ? 

FYI, this monthly data will be loaded in Staging table and that table has LoadDate column which will have 2015-08-03 in it.  I am using SQL 2012 Enterprise edition.

View 17 Replies View Related

Partitioning Table Based On A Non Primary Key Column

Oct 25, 2007

We are facing few issues pertaining to creation of primary key on a non - partitioned column in sql server 2005. Herewith attaching the text file containing the detailed scenario.

Pls advice.


Pls find some of the scenario with the example given below:

We have done the following steps
1.Creating a Partition Function
CREATE PARTITION FUNCTION pf_EncounterODS_StateID (CHAR(2))
AS RANGE RIGHT FOR VALUES
(
'CA', -- CA
'MI', -- MI
'NM', -- NM
'OH', -- OH
'TX', -- TX
'UT', -- UT
'WA' -- WA
)
GO
2.Creating a Partition Schema
CREATE PARTITION SCHEME [ps_EncounterODS_StateID]
AS PARTITION pf_EncounterODS_StateID TO
(
[PRIMARY],
[ENC_DM_DATA_01], -- CA
[ENC_DM_DATA_03], -- MI
[ENC_DM_DATA_04], -- NM
[ENC_DM_DATA_05], -- OH
[ENC_DM_DATA_06], -- TX
[ENC_DM_DATA_07], -- UT
[ENC_DM_DATA_02] -- WA
)
GO

3.Creating a table using above partitioned schema

CREATE TABLE [dbo].[Fact] (
[ValidationErrorSID] [bigint] NOT NULL IDENTITY(1,1),
[ProcessID] [int] NULL ,
[StateID] [char] (2) NOT NULL ,
[ClaimID] [char] (15) NULL ,

) ON [ps_EncounterODS_StateID](StateID)

4.Creating a primary key on validationErrorSID column in the fact table
ALTER TABLE [Fact] ADD CONSTRAINT NQValidationError PRIMARY KEY CLUSTERED ([ValidationErrorSID])
Step 4 throws an error as
---------------------------------
Column 'StateID' is partitioning column of the index 'NQValidationError'. Partition columns for a unique index must be a subset of the index key.
If we include StateID along with ValidationErrorSID for index,then it works fine.But we need to have only ([ValidationErrorSID]) for indexing.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved