Partitioned View - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Partitioned View

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-22 : 15:38:10

thought I'd leave Jon alone.

Now can ANYONE, see the difference between these 2?

This one I got from msdn, and works fine:


CREATE TABLE Orders2000
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_Orders2000
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_Orders2000_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_Orders2000_orderdate
      CHECK(orderdate >= '20000101' AND orderdate < '20010101'))

CREATE TABLE Orders2001
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime    NOT NULL,
  CONSTRAINT PK_Orders2001
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_Orders2001_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_Orders2001_orderdate
      CHECK(orderdate >= '20010101' AND orderdate < '20020101'))

CREATE TABLE Orders2002
( orderid        int               NOT NULL,
  customerid  varchar(5)  NOT NULL,
  orderdate    datetime     NOT NULL,
  CONSTRAINT PK_Orders2002
    PRIMARY KEY(orderdate, orderid),
  CONSTRAINT UQ_Orders2002_orderid
    UNIQUE(orderid),
  CONSTRAINT CHK_Orders2002_orderdate
      CHECK(orderdate >= '20020101' AND orderdate < '20030101'))

GO
CREATE VIEW Orders
AS
SELECT * FROM Orders2000
UNION ALL
SELECT * FROM Orders2001
UNION ALL
SELECT * FROM Orders2002
GO


INSERT INTO Orders(orderId, customerId, Orderdate)
SELECT 1, 'BRETT', '2002/12/25'

SELECT * FROM ORDERS

SELECT * FROM Orders2000

SELECT * FROM Orders2001

SELECT * FROM Orders2002

But this one I wrote doesn't, and I've been staring at it soooo long, they look identical to me, but SOMETHING has got to be different I even added the silly range thing in the contraint to make the look identical..


CREATE TABLE US_Component (
	  ComponentID  varchar(5) NOT NULL, CountryCd varchar(3) NOT NULL, col1 int
	, CONSTRAINT pk_US 
	  PRIMARY KEY (CountryCd, ComponentId) 
	, CONSTRAINT uni_US UNIQUE (ComponentId)
	, CONSTRAINT ck_US CHECK (CountryCd >= 'US' AND CountryCd <= 'US')
) on [PRIMARY]

GO

CREATE TABLE UK_Component (
	  ComponentID varchar(5) NOT NULL, CountryCd varchar(3) NOT NULL, col1 int
	, CONSTRAINT pk_UK 
	  PRIMARY KEY (CountryCd, ComponentId) 
	, CONSTRAINT uni_UK UNIQUE (ComponentId)
	, CONSTRAINT ck_UK CHECK (CountryCd >= 'UK' AND CountryCd <= 'UK')
) on [PRIMARY]

GO

CREATE TABLE OTH_Component (
	  ComponentID varchar(5) NOT NULL, CountryCd varchar(3) NOT NULL, col1 int	
	, CONSTRAINT pk_OTH 
	  PRIMARY KEY (CountryCd, ComponentId) 
	, CONSTRAINT uni_OTH UNIQUE (ComponentId)
	, CONSTRAINT ck_OTH CHECK (CountryCd <> 'US' AND CountryCd <> 'UK')
) on [PRIMARY]
GO

CREATE VIEW Component
AS
SELECT ComponentID, CountryCD, col1 FROM US_Component UNION ALL
SELECT ComponentID, CountryCD, col1 FROM UK_Component UNION ALL
SELECT ComponentID, CountryCD, col1 FROM OTH_Component
GO

INSERT INTO Component(ComponentId, CountryCD, col1) SELECT 'T001','US',100
GO

DROP VIEW Component
GO
DROP TABLE US_Component
GO
DROP TABLE UK_Component
GO
DROP TABLE OTH_Component
GO

Brett

8-)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-07-22 : 20:29:41

Brett,

You have read BOL haven't you?

Specifically...

quote:

Each base table has a partitioning column whose key values are enforced by CHECK constraints. The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.

By using the NOT EQUAL operator in OTH_Component, you have violated this rule....

To get around this you must write it using only those operators..

Here are the CHECK constraints that worked..

UK_Component = CHECK (CountryCd= 'UK')
US_Component = CHECK (CountryCd= 'US')
OTH_Component = CHECK (CountryCd BETWEEN 'a' AND 'UJ' OR CountryCd BETWEEN 'UL' AND 'UR' OR CountryCd BETWEEN 'UT' AND 'zzz')

DavidM

"SQL-3 is an abomination.."

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 09:15:44

quote:

Brett,

You have read BOL haven't you?

Yes, but obviously not close enough...that's my first shot at partitioning...

EDIT: In SQL Server

But PLEASE! That's Pretty silly.

To do the same thing logicaly. OOOOOOOOOHHH I forgot, this is MS.

Thanks though...works like a charm now.


CREATE TABLE US_Component (
	  ComponentID  varchar(5) NOT NULL, CountryCd varchar(3) NOT NULL, col1 int
	, CONSTRAINT pk_US 
	  PRIMARY KEY (CountryCd, ComponentId) 
	, CONSTRAINT uni_US UNIQUE (ComponentId)
	, CONSTRAINT ck_US CHECK (CountryCd >= 'US' AND CountryCd <= 'US')
) on [PRIMARY]

GO

CREATE TABLE UK_Component (
	  ComponentID varchar(5) NOT NULL, CountryCd varchar(3) NOT NULL, col1 int
	, CONSTRAINT pk_UK 
	  PRIMARY KEY (CountryCd, ComponentId) 
	, CONSTRAINT uni_UK UNIQUE (ComponentId)
	, CONSTRAINT ck_UK CHECK (CountryCd >= 'UK' AND CountryCd <= 'UK')
) on [PRIMARY]

GO

CREATE TABLE OTH_Component (
	  ComponentID varchar(5) NOT NULL, CountryCd varchar(3) NOT NULL, col1 int	
	, CONSTRAINT pk_OTH 
	  PRIMARY KEY (CountryCd, ComponentId) 
	, CONSTRAINT uni_OTH UNIQUE (ComponentId)
	, CONSTRAINT ck_OTH CHECK (CountryCd BETWEEN 'a' AND 'UJ' 
	OR CountryCd BETWEEN 'UL' AND 'UR' 
	OR CountryCd BETWEEN 'UT' AND 'zzz') 
) on [PRIMARY]
GO

--ck_OTH CHECK (CountryCd <> 'US' AND CountryCd <> 'UK')

CREATE VIEW Component
AS
SELECT ComponentID, CountryCD, col1 FROM US_Component UNION ALL
SELECT ComponentID, CountryCD, col1 FROM UK_Component UNION ALL
SELECT ComponentID, CountryCD, col1 FROM OTH_Component
GO

INSERT INTO Component(ComponentId, CountryCD, col1) SELECT 'T001','US',100
GO

DROP VIEW Component
GO
DROP TABLE US_Component
GO
DROP TABLE UK_Component
GO
DROP TABLE OTH_Component
GO

Brett

8-)

EDIT2: Not bad for a farmer..Thanks again David.

Brett

8-)

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-23 : 10:48:18

quote:

To do the same thing logicaly.

Ahh but it is not the same really. If you use <> then anything could go into that partition that is not equal to the criteria. This would not necessarily distinguish it from the anything in another partition that is not equal to a different criteria.

-------
Moo. :)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 13:30:26

Are you telling me that:

ck_OTH CHECK (CountryCd BETWEEN 'a' AND 'UJ'
OR CountryCd BETWEEN 'UL' AND 'UR'
OR CountryCd BETWEEN 'UT' AND 'zzz')

Is not the same as:

ck_OTH CHECK (CountryCd <> 'US' AND CountryCd <> 'UK')

Or more specifically

ck_OTH CHECK (CountryCd BETWEEN CHAR('00) AND 'UJ'
OR CountryCd BETWEEN 'UL' AND 'UR'
OR CountryCd BETWEEN 'UT' AND CHAR('999999')

In any case, do you think you can just say > or <?

I'll give it a shot.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 13:36:32

Well THAT didn't work....

SQL Scrub

Brett

8-)

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-24 : 04:45:47

quote:

Are you telling me that:

ck_OTH CHECK (CountryCd BETWEEN 'a' AND 'UJ'
OR CountryCd BETWEEN 'UL' AND 'UR'
OR CountryCd BETWEEN 'UT' AND 'zzz')

Is not the same as:

ck_OTH CHECK (CountryCd <> 'US' AND CountryCd <> 'UK')

Well, er, yes. I mean, it's not. You know that your country codes fall within a certain range (as defined in the BETWEENS), SQL Server does not know this. Let me show you what I'm getting at -

ck_not_us (countrycd <> 'US')

ck_not_uk (countrycd <> 'UK')

ck_not_es (countrycd <> 'ES')

Into which one of those tables does the country ES go? What about AU? It might be valid for a check, but not to make it a suitable check for partitioning.

-------
Moo. :)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 10:55:15

[dead horse]
Not to beat a dead horse (what was roy rodger's horses name)...

But if I'm Inclusive in all of my initial partitions, and totally exclusive in the last of everything that was included in the initial partiton, that would equal to 1 whole set.

No overlaps, no exclusions, the sum of all the parts would equal 1.

[/dead horse]

but I get your point, and since it's not doable....

I was just able to do this on the high and low end ranges for ssn using high values (hex 'FF') on the top and low value (hex('00') on the bottom in DB2

But that was still a range like you pointed out

Thanks.

Goo learning experience today (well every day actually)

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources