SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need help on Partitioned Views
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 01/27/2010 :  10:12:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/27/2010 :  11:02:04  Show Profile  Reply with Quote
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 - 01/27/2010 :  11:49:30  Show Profile  Reply with Quote
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;

Edited by - Delinda on 01/27/2010 11:54:01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/27/2010 :  11:52:16  Show Profile  Reply with Quote
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 - 01/27/2010 :  11:56:00  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/27/2010 :  11:57:36  Show Profile  Reply with Quote
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 - 01/27/2010 :  12:07:15  Show Profile  Reply with Quote
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

India
52317 Posts

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

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 01/27/2010 :  12:13:44  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/27/2010 :  12:22:18  Show Profile  Reply with Quote
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 - 01/27/2010 :  12:28:52  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/27/2010 :  12:32:41  Show Profile  Reply with Quote
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 - 01/27/2010 :  12:37:30  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/27/2010 :  12:39:35  Show Profile  Reply with Quote
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 - 01/27/2010 :  12:51:00  Show Profile  Reply with Quote
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 - 01/27/2010 :  13:14:29  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 01/27/2010 :  14:13:00  Show Profile  Reply with Quote

...
[recGrp] [datetime] not null,
Constraint ck_recGrp_201002 CHECK (
recGrp BETWEEN '2/1/2010' and '2/31/2010'
...

All months now have 31 days? I'm surprised SQL didn't error on that ...
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/27/2010 :  15:03:27  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 01/27/2010 15:04:06
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/27/2010 :  15:59:56  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/28/2010 :  03:09:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/28/2010 :  04:11:06  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000