Problem with partitioned views

Problem with partitioned views

Post by Igor Marchenk » Wed, 27 Sep 2000 04:00:00



                                Hello !

    I am trying to use partitioned views in SQL2000 Final edition available
for download from Micorsoft site . I have setup check constraints .When I
run following script in SQL 7.0 (SP2) it works fine.Query optimizer doesn't
scan "wrong" tables.In SQL2000 Query optimizer always scans all tables in
the view.Could anybody help me ?

Here is the script I was using :

CREATE TABLE [dbo].[tblEnglish_short] (
 [EntryID] [int] NOT NULL ,
 [Entry] [nvarchar] (450)  ,
 [LanguageID] [smallint] NOT NULL ,
 [EntryCategoryID] [smallint] NOT NULL ,
 [TechnicalStatusID] [tinyint] NOT NULL ,
 [Capitalitation] [bit] NOT NULL ,
 [OwnerID] [smallint] NOT NULL ,
 [CreatedByID] [smallint] NOT NULL ,
 [CreatedDate] [datetime] NOT NULL ,
 [EntryComments] [varchar] (255) ,
 [NumberOfWords] [smallint] NULL ,
 [NumberOfCharacters] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSpanish_short] (
 [EntryID] [int] NOT NULL ,
 [Entry] [nvarchar] (450)  ,
 [LanguageID] [smallint] NOT NULL ,
 [EntryCategoryID] [smallint] NOT NULL ,
 [TechnicalStatusID] [tinyint] NOT NULL ,
 [Capitalitation] [bit] NOT NULL ,
 [OwnerID] [smallint] NOT NULL ,
 [CreatedByID] [smallint] NOT NULL ,
 [CreatedDate] [datetime] NOT NULL ,
 [EntryComments] [varchar] (255),
 [NumberOfWords] [smallint] NULL ,
 [NumberOfCharacters] [smallint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblEnglish_short] WITH CHECK ADD
 CONSTRAINT [DF__tblEnglis__Langu__79A81403] DEFAULT (1) FOR [LanguageID],
 CONSTRAINT [DF__tblEnglis__Capit__7B905C75] DEFAULT (0) FOR
[Capitalitation],
 CONSTRAINT [DF__tblEnglis__Creat__7C8480AE] DEFAULT (getdate()) FOR
[CreatedDate],
 CONSTRAINT [PK_TBLENGLISH_SHORT] PRIMARY KEY  CLUSTERED
 (
  [EntryID]
 )  ON [PRIMARY] ,
 CONSTRAINT [UK_English_short_UnitName] UNIQUE  NONCLUSTERED
 (
  [Entry]
 )  ON [PRIMARY] ,
 CONSTRAINT [CK_English_short_LanguageID] CHECK ([LanguageID] = 1),
 CONSTRAINT [CK_English_short_UnitID] CHECK ([EntryID] >= 100000000 and
[EntryID] <= 149999999)
GO

ALTER TABLE [dbo].[tblSpanish_short] WITH CHECK ADD
 CONSTRAINT [DF__tblSpanis__Langu__014935CB] DEFAULT (3) FOR [LanguageID],
 CONSTRAINT [DF__tblSpanis__Capit__03317E3D] DEFAULT (0) FOR
[Capitalitation],
 CONSTRAINT [DF__tblSpanis__Creat__0425A276] DEFAULT (getdate()) FOR
[CreatedDate],
 CONSTRAINT [PK_TBLSPANISH_SHORT] PRIMARY KEY  CLUSTERED
 (
  [EntryID]
 )  ON [PRIMARY] ,
 CONSTRAINT [UK_Spanish_short_UnitName] UNIQUE  NONCLUSTERED
 (
  [Entry]
 )  ON [PRIMARY] ,
 CONSTRAINT [CK_Spanish_short_LanguageID] CHECK ([LanguageID] = 3),
 CONSTRAINT [CK_Spanish_short_UnitID] CHECK ([EntryID] >= 300000000 and
[EntryID] <= 349999999)
GO

CREATE VIEW dbo.v_ShortEntries
AS
SELECT     *
FROM         dbo.tblEnglish_short
UNION ALL
SELECT     *
FROM         tblSpanish_short

select * from v_ShortEntries where languageid=1

This statement should scan tblSpanish_short because there is
DF__tblSpanis__Langu__014935CB constraint .As I said it works correctly in
SQL 7.0 (SP2) and doesn't in SQL2000.

                        Thank you in advance,

                                                    Igor.

 
 
 

1. Urgent: Problem in Partition View

We had n partition tables . A view was created with UNION
ALL on all these partition tables. Each of these
partition tables has a check constraint on date field.
Since one of the tables had a wrong range of date given,
we dropped that underlying table and recreated it
correctly. Dropped and Recreated the UNION ALL Partition
View.
But when we try to update the view now, we get the
following error
"UNION ALL VIEW <view name> IS NOT UPDATE BECAUSE A
PARTITIONING COLUMN WAS NOT FOUND".
The Partition column is also a part of primary key of the
partition table.
Any help will be very much appreciated.
Thanks in Advance,
Joyce

2. BI tool Evaluation/Selection Matrix

3. HELP ! Problem distributing partitioned views

4. Olap via HTTP - help pls

5. Problem with Partition View

6. Production Assurance Management Analyst,WESTERVILLE,OH

7. Problem with Partition Views

8. Sorting problems with FM 3.

9. Problems updating partitioned views in SQL2000

10. HELP ! Problem distributing partitioned views

11. Partitioned views vs. partitioned tables

12. Partitioned views - how to size partitions?

13. O7 to O8 Partitioned Views to Partitioned Tables