Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need help on Partitioned Views

Author  Topic 

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 10:12:13
My table as follow,
CREATE TABLE DERPosi_201001
(
[TID] int primary key identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201001 CHECK (
recGrp BETWEEN '1/1/2010' and '1/31/2010'
)
)
CREATE TABLE DERPosi_201002
(
[TID] int primary key identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201002 CHECK (
recGrp BETWEEN '2/1/2010' and '2/31/2010'
)
)
CREATE TABLE DERPosi_201003
(
[TID] int primary key identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201003 CHECK (
recGrp BETWEEN '3/1/2010' and '3/31/2010'
)
)

I tried to create Partitioned Views as follow,
create view DERPosi with schemabinding
as
select TID,SeatN,Posi,recGrp from DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from DERPosi_201003;

Unfortunately, error was return as follow,
Cannot schema bind view 'DERPosi' because name 'DERPosi_201001' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

I'm stuck

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 11:02:04
try using schema names for tables as well as view like


create view schemaname.DERPosi with schemabinding
as
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201003;
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 11:49:30
quote:
Originally posted by visakh16

try using schema names for tables as well as view like


create view schemaname.DERPosi with schemabinding
as
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201003;




It return erros as follow,
Invalid object name 'schemaname.DERPosi_201001'.

why schemaname.DERPosi_201001 not only DERPosi_201010

but below will work,
create view dbo.DERPosi with schemabinding
as
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201003;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 11:52:16
quote:
Originally posted by Delinda

quote:
Originally posted by visakh16

try using schema names for tables as well as view like


create view schemaname.DERPosi with schemabinding
as
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201003;




It return erros as follow,
Invalid object name 'schemaname.DERPosi_201001'.

why schemaname.DERPosi_201001 not only DERPosi_201010


you should put your actual schema name not the text schemaname
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 11:56:00
quote:
Originally posted by visakh16

quote:
Originally posted by Delinda

quote:
Originally posted by visakh16

try using schema names for tables as well as view like


create view schemaname.DERPosi with schemabinding
as
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from schemaname.DERPosi_201003;




It return erros as follow,
Invalid object name 'schemaname.DERPosi_201001'.

why schemaname.DERPosi_201001 not only DERPosi_201010


you should put your actual schema name not the text schemaname



me tried,
create view dbo.DERPosi with schemabinding
as
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201003;

it's work.

So, that all? Now, my DERPosi is Partitioned Views?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 11:57:36
see
http://msdn.microsoft.com/en-us/library/aa933141(SQL.80).aspx
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 12:07:15
quote:
Originally posted by visakh16

see
http://msdn.microsoft.com/en-us/library/aa933141(SQL.80).aspx



Me tried run as follow,
insert into dbo.DERPosi(SeatN,Posi,recGrp) values(23,'010','1/27/2010')
insert into dbo.DERPosi(SeatN,Posi,recGrp) values(24,'10','3/27/2010')

it return error,
Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'ETICKETING.dbo.DERPosi' is not updatable because a partitioning column was not found.
Msg 4436, Level 16, State 12, Line 2
UNION ALL view 'ETICKETING.dbo.DERPosi' is not updatable because a partitioning column was not found.

I'm using SQL Server 2005 Standard Edition
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 12:11:51
did you read part under "Partitioning Column Rules"?
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 12:13:44
quote:
Originally posted by visakh16

did you read part under "Partitioning Column Rules"?



Yes. But i can't digest the logic, and fail to implement. Especially,
Partitioning columns must be a part of the primary key of the table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 12:22:18
quote:
Originally posted by Delinda

quote:
Originally posted by visakh16

did you read part under "Partitioning Column Rules"?



Yes. But i can't digest the logic, and fail to implement. Especially,
Partitioning columns must be a part of the primary key of the table.


Partition column is column whose values are base for division into different tables. they will have check constraint to ensure values are in allowable range
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 12:28:52
quote:
Originally posted by visakh16

quote:
Originally posted by Delinda

quote:
Originally posted by visakh16

did you read part under "Partitioning Column Rules"?



Yes. But i can't digest the logic, and fail to implement. Especially,
Partitioning columns must be a part of the primary key of the table.


Partition column is column whose values are base for division into different tables. they will have check constraint to ensure values are in allowable range



My table as follow,
CREATE TABLE DERPosi_201001
(
[TID] int primary key identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201001 CHECK (
recGrp BETWEEN '1/1/2010' and '1/31/2010'
)
)
CREATE TABLE DERPosi_201002
(
[TID] int primary key identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201002 CHECK (
recGrp BETWEEN '2/1/2010' and '2/31/2010'
)
)
CREATE TABLE DERPosi_201003
(
[TID] int primary key identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201003 CHECK (
recGrp BETWEEN '3/1/2010' and '3/31/2010'
)
)

You can see,
1. My primary is TID
2. my partion column is recGrp

My Partitioned Views as follow,
create view [dbo].[DERPosi] with schemabinding
as
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201001
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201002
union all
select TID,SeatN,Posi,recGrp from dbo.DERPosi_201003;

It return error when i execute
insert into dbo.DERPosi(SeatN,Posi,recGrp) values(23,'010','1/27/2010')
insert into dbo.DERPosi(SeatN,Posi,recGrp) values(24,'10','3/27/2010')

Did I missing something?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 12:32:41
where are check constraints on partition column? also it should be part of primary key of table
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 12:37:30
quote:
Originally posted by visakh16

where are check constraints on partition column? also it should be part of primary key of table



For DERPosi_201001, check constraint is Constraint ck_recGrp_201001 CHECK (
recGrp BETWEEN '1/1/2010' and '1/31/2010'

For DERPosi_201002, check constraint is Constraint ck_recGrp_201002 CHECK (
recGrp BETWEEN '2/1/2010' and '2/31/2010'

For DERPosi_201003, check constraint is Constraint ck_recGrp_201003 CHECK (
recGrp BETWEEN '3/1/2010' and '3/31/2010'

If me set recGrp is a primary key, how come i can insert data with same recGrp.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 12:39:35
quote:
Originally posted by Delinda

quote:
Originally posted by visakh16

where are check constraints on partition column? also it should be part of primary key of table



For DERPosi_201001, check constraint is Constraint ck_recGrp_201001 CHECK (
recGrp BETWEEN '1/1/2010' and '1/31/2010'

For DERPosi_201002, check constraint is Constraint ck_recGrp_201002 CHECK (
recGrp BETWEEN '2/1/2010' and '2/31/2010'

For DERPosi_201003, check constraint is Constraint ck_recGrp_201003 CHECK (
recGrp BETWEEN '3/1/2010' and '3/31/2010'

If me set recGrp is a primary key, how come i can insert data with same recGrp.


you can make a composite primary key involving date field also
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 12:51:00
quote:
Originally posted by visakh16

quote:
Originally posted by Delinda

quote:
Originally posted by visakh16

where are check constraints on partition column? also it should be part of primary key of table



For DERPosi_201001, check constraint is Constraint ck_recGrp_201001 CHECK (
recGrp BETWEEN '1/1/2010' and '1/31/2010'

For DERPosi_201002, check constraint is Constraint ck_recGrp_201002 CHECK (
recGrp BETWEEN '2/1/2010' and '2/31/2010'

For DERPosi_201003, check constraint is Constraint ck_recGrp_201003 CHECK (
recGrp BETWEEN '3/1/2010' and '3/31/2010'

If me set recGrp is a primary key, how come i can insert data with same recGrp.


you can make a composite primary key involving date field also



My composite primary key as follow,
alter table DERPosi_201001
add primary key(TID,recGrp)

alter table DERPosi_201002
add primary key(TID,recGrp)

alter table DERPosi_201003
add primary key(TID,recGrp)

Once me run,
insert into dbo.DERPosi(SeatN,Posi,recGrp) values(23,'010','1/27/2010')
insert into dbo.DERPosi(SeatN,Posi,recGrp) values(24,'10','3/27/2010')

it return errors,
Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'ETICKETING.dbo.DERPosi' is not updatable because a partitioning column was not found.
Msg 4436, Level 16, State 12, Line 2
UNION ALL view 'ETICKETING.dbo.DERPosi' is not updatable because a partitioning column was not found.
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 13:14:29
1. I removed TID as identity
2. Me make it TID and recGrp as Composite Primary Key

As a result, me execute
insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(1,23,'010','1/27/2010')
insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(10,24,'10','3/27/2010')

The result was success ...

tq Mr Visakh.

Your guide, is my inspiration
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 14:13:00
[code]
...
[recGrp] [datetime] not null,
Constraint ck_recGrp_201002 CHECK (
recGrp BETWEEN '2/1/2010' and '2/31/2010'
...
[/code]
All months now have 31 days? I'm surprised SQL didn't error on that ...
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-27 : 15:03:27
It does error out, just not when the table is created...

Create Table #testConstraint (
id int identity primary key,
somecol datetime,
Constraint testing CHECK (somecol BETWEEN '2010/02/01' and '2010/02/31)
)

Command completed successfully

insert into #testConstraint (somecol) values ('2010/02/20') -- perfectly valid datetime value

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The statement has been terminated.

The constraint only gets executed when a row is inserted/updated.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 15:59:56
Thanks for that Gail. Something for the O/P to fix ...

I hope they aren't used to using MySQL which, I believe, allows dates with any number of days in a month (although Gotchas like that remain in circulation long after they are fixed, so I may be maligning MySQL ...)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 03:09:57
quote:
Originally posted by Delinda

1. I removed TID as identity
2. Me make it TID and recGrp as Composite Primary Key

As a result, me execute
insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(1,23,'010','1/27/2010')
insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(10,24,'10','3/27/2010')

The result was success ...

tq Mr Visakh.

Your guide, is my inspiration


welcome
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-28 : 04:11:06
quote:
Originally posted by KristenI hope they aren't used to using MySQL which, I believe, allows dates with any number of days in a month


And the 0th day of the month.
When I worked with MySQL (4 I think) there was a note in the docs saying that this was by design and that it was not the DB's responsibility to validate data.

However now...
quote:
Starting from MySQL 5.0.2, MySQL gives warnings or errors if you try to insert an illegal date. By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. (See Section 5.1.7, “Server SQL Modes”.) You can get MySQL to accept certain dates, such as '2009-11-31', by using the ALLOW_INVALID_DATES SQL mode. (Before 5.0.2, this mode was the default behavior for MySQL.)


--
Gail Shaw
SQL Server MVP
Go to Top of Page
    Next Page

- Advertisement -