Partitioning In SQL Server 2005

[sendba]

All,

I have created the patitioning like the one which I have attached.
My question is why partitioning on Varchar field is not reliable. The 200 and 2000 fall under the same partition.

How to solve this problem
Any inputs much Appreciated

Thanks

Sen


create partition function pt_fn_test( varchar(10))
as range right for values('100' , '200' , '300' , '400', '500',
'600', '700' , '800' , '900', '1000')

create partition scheme pt_sh_test
as partition pt_fn_test
to (
Partition1,Partition2, Partition3,Partition4,Partition5,
Partition6,Partition7,Partition8,Partition9,Partition10,
Partition11)


create table test_varchar
(eno varchar(10),
ename varchar(100))
on pt_sh_test(eno)

insert into test_varchar values ('10', 'A')
insert into test_varchar values ('100', 'A')
insert into test_varchar values ('200', 'A')
insert into test_varchar values ('300', 'A')
insert into test_varchar values ('400', 'A')
insert into test_varchar values ('500', 'A')
insert into test_varchar values ('600', 'A')
insert into test_varchar values ('700', 'A')
insert into test_varchar values ('800', 'A')
insert into test_varchar values ('900', 'A')
insert into test_varchar values ('1000', 'A')
insert into test_varchar values ('1999', 'A')
insert into test_varchar values ('2000', 'A')


select $PARTITION.pt_fn_test ('10') 1
select $PARTITION.pt_fn_test ('100') 2
select $PARTITION.pt_fn_test ('200') 4
select $PARTITION.pt_fn_test ('300') 5
select $PARTITION.pt_fn_test ('400') 6
select $PARTITION.pt_fn_test ('500') 7
select $PARTITION.pt_fn_test ('600') 8
select $PARTITION.pt_fn_test ('700') 9
select $PARTITION.pt_fn_test ('800') 10
select $PARTITION.pt_fn_test ('900') 11
select $PARTITION.pt_fn_test ('1000') 3
select $PARTITION.pt_fn_test ('2000') 4
select $PARTITION.pt_fn_test ('3000') 5
select $PARTITION.pt_fn_test ('4000') 6

 

[SQLBI]

From what i gather, it's all to do with how the data is sorted.

In your example, '200' falls between '200' and '300'. When sorted, both '200' and '2000' fall between '200' and '300' and are added to the 4th partition.

I can't see another way of altering this behaviour without changing from a string partition function to a numeric one which may not fit with the data you're working with.



Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

 

[mrdenny]

In order to get the data partitioned the way you want you will need to change the column to being a number. Because you have defined the column as a string value you will need get the data partitioned in the way that SQL is partitioning it.

Because it's a string value SQL treats the numbers as text not as numeric values. When it does text ordering it's done based on first character, then second character.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sendba]

The problem I am facing is the table has only one unique column and based on that column I am building the partition. Unfortunately the unique column is varchar and is there any solution for this.

One more question:
I have another table which has an int value as unique column, based on the column I built the partition.
I have created a clustered index on the partitioned column.
I have to built another unique index on the other column which is a varchar(20) field.
When I try to execute the create unique index command I get an error.

Please help

Thanks
Sen

 

[mrdenny]

What is the error you get when you create the index?

Why are you creating a unique index on the varchar field? In uniqueness required on the field?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sendba]

The error is
Msg 1908, Level 16, State 1, Line 2
Column 'A' is partitioning column of the index 'B_INDEX'. Partition columns for a unique index must be a subset of the index key.

The column has to be unique otherwise we will be in bad shape.

Please help

Thanks
Sen

 

[mrdenny]

From the error it reads to me that you have to include both partitioned and non-partitioned columns as columns within the index.

If that doesn't work for you, perhaps a unique constraint will work?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sendba]

Can you please let me know what you mean by Unique Constraint.
If I create a unique constraint will it not create a unique index.If it creates a unique index the data where it is stored in partition or in another filegroup.

Please let me know

Sen

 

[mrdenny]

When you create a constrainst it does not create an index (except for the primary key).

The data is stored within the same filegroups as the table data.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sendba]

Denny

Can you please forward me the script of creating a contraint without an index creation.

thanks for your help

Sen

 

[sendba]

Here is an example

CREATE TABLE [dbo].[Test_temp](
ColA [int] IDENTITY(1,1) NOT NULL,
ColB [char](11) ,
ColC[char](16) )


ALTER TABLE [dbo].[Test_temp] WITH NOCHECK ADD
CONSTRAINT [PK_[Test_temp]] PRIMARY KEY CLUSTERED
(
ColA
) WITH FILLFACTOR = 90 on pt_scheme(ColA)
GO

CREATE unique INDEX idx_C ON [dbo].[Test_temp](ColC) on pt_scheme([ColA])
GO

I need to create a unique index on Column C