Partitioned View Performance Question

Hi,
I've got a view that union's 3 tables together on the same server. The base
tables all have a constraint on a date field like:
Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
The view simply "union-all"'s the tables together.
When I do a query like this:
select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
then the graphical query plan clearly shows it going directly to the
clustered index on Table06 as expected
However, when I query like this:
select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
The graphical plan shows it scanning all three base tables which is not what
I hoped would happen.
Could anyone explain why this is? It really hurts performance when I query
the view in a stored-proc.
Thanks
NickNick Dawson (no.thanks@.nowhere.com) writes:
> I've got a view that union's 3 tables together on the same server. The
> base tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
First of all, use the date format YYYYMMDD. The above constraints will
fail if the connection uses a language where Jan and Dec are not month
names.
Second, write the constraints as
Date >= '20060101' AND Date < '20070101'
since there is no date type in SQL Server, your constraints loses a
day as far as SQL Server is concerned.

> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06',
> 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
Do you really get poor performance, or do you just think that the query
plan looks bad?
The query plan is expected. Since SQL Server does not know the value of the
variable before-hand, it must have a plan that involves all tabeles.
But if everything is working OK, you will see that the plan operators
has a STARTUP EXPR. Furthermore, if you run a query with SET STATISTICS ON,
you will see that for two the tables there are 0 logical reads. What
happens is that the SQL Server prunes two of the tables at run-time.
There is one thing that is very important with partioned views, and that
is that the CHECK constraints are *trusted*. It is not sufficient that
they are enabled. If you use a tool like Enterprise Mangager to change
the constraint, it is likely that it use something like:
ALTER TABLE tbl ADD constraint ckc WITH NOCHECK CHECK (Date...)
WITH NOCHECK means that SQL Server should not check whether existing
values conforms to the constraint. This sames time, but leads to the
constraint being not trusted, because there could be non-conformant
data.
To find if you have any constraints that are not trusted do:
SELECT name, xtype, object_id(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What happens when @.d is fed to the proc as a datetime value and you
eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
Hi,
I've got a view that union's 3 tables together on the same server. The base
tables all have a constraint on a date field like:
Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
The view simply "union-all"'s the tables together.
When I do a query like this:
select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
then the graphical query plan clearly shows it going directly to the
clustered index on Table06 as expected
However, when I query like this:
select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
The graphical plan shows it scanning all three base tables which is not what
I hoped would happen.
Could anyone explain why this is? It really hurts performance when I query
the view in a stored-proc.
Thanks
Nick|||Aplogies Tom, a typo there. I always query the view and not the table
@.d is fed as a datetime value, with a default, to the sp.
The show stats i/o does correctly indicate that it doesn't scan the
underlying tables it doesn't need. Like this:
Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
read-ahead reads 7178.
Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
That's great.
However, if I query the view (outside the sp) like this:
select * from MyView
where [Date] = '09/Feb/06'
the graphical plan it indicates it's spending over 50% of it's time checking
the 2004 and 2005 tables that it doesn't need. The plan also indicates that
it's having to "convert" the date/time parameter.
If I call the select like this:
SELECT *
FROM MyView
WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
It doesn't convert the date, and it goes directly to one underlying table
and the graphical plan doesn't even touch the archive tables.
We've notice some end of day processing taking longer since this view has
been created, and we think it's because it's not optimially selecting from
the "current" table but scanning the archive tables too much.
Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
> What happens when @.d is fed to the proc as a datetime value and you
> eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a view that union's 3 tables together on the same server. The
> base
> tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what
> I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
> Thanks
> Nick
>
>|||That's odd. have you tried declaring a datetime variable, assigning it the
date and then selecting from the view, using the parameter?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
Aplogies Tom, a typo there. I always query the view and not the table
@.d is fed as a datetime value, with a default, to the sp.
The show stats i/o does correctly indicate that it doesn't scan the
underlying tables it doesn't need. Like this:
Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
read-ahead reads 7178.
Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
That's great.
However, if I query the view (outside the sp) like this:
select * from MyView
where [Date] = '09/Feb/06'
the graphical plan it indicates it's spending over 50% of it's time checking
the 2004 and 2005 tables that it doesn't need. The plan also indicates that
it's having to "convert" the date/time parameter.
If I call the select like this:
SELECT *
FROM MyView
WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
It doesn't convert the date, and it goes directly to one underlying table
and the graphical plan doesn't even touch the archive tables.
We've notice some end of day processing taking longer since this view has
been created, and we think it's because it's not optimially selecting from
the "current" table but scanning the archive tables too much.
Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
> What happens when @.d is fed to the proc as a datetime value and you
> eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a view that union's 3 tables together on the same server. The
> base
> tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what
> I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
> Thanks
> Nick
>
>|||Tom,
Give this a bash:
CREATE TABLE [dbo].[tmp1] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmp2] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
'20070101')
GO
No need to insert any data.
Now run this:
select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
00:00:00', 102)
And look at the graphical plan. Looks good to me, as it's only looking at
tmp2 table. This is what I was hoping was happening.
Whereas this:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
Looks at both the tables.
As does this:
declare @.Date datetime
select @.Date = '2006.02.02'
select * from alltmp where DateCol = @.Date
select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
I'm a bit lost! Any ideas are really appreciated.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
> That's odd. have you tried declaring a datetime variable, assigning it
> the
> date and then selecting from the view, using the parameter?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Aplogies Tom, a typo there. I always query the view and not the table
> @.d is fed as a datetime value, with a default, to the sp.
> The show stats i/o does correctly indicate that it doesn't scan the
> underlying tables it doesn't need. Like this:
> Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
> read-ahead reads 7178.
> Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> That's great.
> However, if I query the view (outside the sp) like this:
> select * from MyView
> where [Date] = '09/Feb/06'
> the graphical plan it indicates it's spending over 50% of it's time
> checking
> the 2004 and 2005 tables that it doesn't need. The plan also indicates
> that
> it's having to "convert" the date/time parameter.
> If I call the select like this:
> SELECT *
> FROM MyView
> WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
> It doesn't convert the date, and it goes directly to one underlying table
> and the graphical plan doesn't even touch the archive tables.
> We've notice some end of day processing taking longer since this view has
> been created, and we think it's because it's not optimially selecting from
> the "current" table but scanning the archive tables too much.
> Thanks
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
>
>|||The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
Tom,
Give this a bash:
CREATE TABLE [dbo].[tmp1] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmp2] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
'20070101')
GO
No need to insert any data.
Now run this:
select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
00:00:00', 102)
And look at the graphical plan. Looks good to me, as it's only looking at
tmp2 table. This is what I was hoping was happening.
Whereas this:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
Looks at both the tables.
As does this:
declare @.Date datetime
select @.Date = '2006.02.02'
select * from alltmp where DateCol = @.Date
select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
I'm a bit lost! Any ideas are really appreciated.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
> That's odd. have you tried declaring a datetime variable, assigning it
> the
> date and then selecting from the view, using the parameter?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Aplogies Tom, a typo there. I always query the view and not the table
> @.d is fed as a datetime value, with a default, to the sp.
> The show stats i/o does correctly indicate that it doesn't scan the
> underlying tables it doesn't need. Like this:
> Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
> read-ahead reads 7178.
> Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> That's great.
> However, if I query the view (outside the sp) like this:
> select * from MyView
> where [Date] = '09/Feb/06'
> the graphical plan it indicates it's spending over 50% of it's time
> checking
> the 2004 and 2005 tables that it doesn't need. The plan also indicates
> that
> it's having to "convert" the date/time parameter.
> If I call the select like this:
> SELECT *
> FROM MyView
> WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
> It doesn't convert the date, and it goes directly to one underlying table
> and the graphical plan doesn't even touch the archive tables.
> We've notice some end of day processing taking longer since this view has
> been created, and we think it's because it's not optimially selecting from
> the "current" table but scanning the archive tables too much.
> Thanks
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
>
>|||I just re-created all the constraints with "Check" in place. Thanks for
pointing that out.
Unfortunately it doesn't seem to make a difference. Do I need to create the
view in a particular way? - I simply did:
create view alltmp as
select * from tmp1
union all
select * from tmp2
I also tried explicitly providing the column names.
Thanks for your help Tom.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
> The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Give this a bash:
> CREATE TABLE [dbo].[tmp1] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tmp2] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
> '20070101')
> GO
> No need to insert any data.
> Now run this:
> select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
> 00:00:00', 102)
> And look at the graphical plan. Looks good to me, as it's only looking at
> tmp2 table. This is what I was hoping was happening.
> Whereas this:
> select * from alltmp where DateCol = '2006-02-02 00:00:00'
> Looks at both the tables.
> As does this:
> declare @.Date datetime
> select @.Date = '2006.02.02'
> select * from alltmp where DateCol = @.Date
> select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
> I'm a bit lost! Any ideas are really appreciated.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
>
>|||I think it's your CHECK constraints. They overlap. See below:
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <
'20070101')
GO
These don't overlap, since I changed the <= to <.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:%23SqmciKMGHA.2744@.TK2MSFTNGP10.phx.gbl...
I just re-created all the constraints with "Check" in place. Thanks for
pointing that out.
Unfortunately it doesn't seem to make a difference. Do I need to create the
view in a particular way? - I simply did:
create view alltmp as
select * from tmp1
union all
select * from tmp2
I also tried explicitly providing the column names.
Thanks for your help Tom.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
> The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Give this a bash:
> CREATE TABLE [dbo].[tmp1] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tmp2] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
> '20070101')
> GO
> No need to insert any data.
> Now run this:
> select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
> 00:00:00', 102)
> And look at the graphical plan. Looks good to me, as it's only looking at
> tmp2 table. This is what I was hoping was happening.
> Whereas this:
> select * from alltmp where DateCol = '2006-02-02 00:00:00'
> Looks at both the tables.
> As does this:
> declare @.Date datetime
> select @.Date = '2006.02.02'
> select * from alltmp where DateCol = @.Date
> select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
> I'm a bit lost! Any ideas are really appreciated.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
>
>|||Hhmm, I didn't spot that - nice call.
I changed them as you suggested.
But, these two queries:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
select * from alltmp where DateCol = CONVERT(DATETIME,'2006.02.02')
Still produce different queryplans - the second one is what I'd call
"correct" Even in stats i/o output, the second one clearly only scans one
table whereas the top one scans both of them.
Digging around on google groups a liitle, it appears that other people have
had issues with Datetime columns on partitioning views indicating that it
"doesn't work" Maybe I've encountered a similar problem.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u1NrGqKMGHA.2624@.TK2MSFTNGP12.phx.gbl...
>I think it's your CHECK constraints. They overlap. See below:
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <
> '20070101')
> GO
> These don't overlap, since I changed the <= to <.
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23SqmciKMGHA.2744@.TK2MSFTNGP10.phx.gbl...
> I just re-created all the constraints with "Check" in place. Thanks for
> pointing that out.
> Unfortunately it doesn't seem to make a difference. Do I need to create
> the
> view in a particular way? - I simply did:
> create view alltmp as
> select * from tmp1
> union all
> select * from tmp2
> I also tried explicitly providing the column names.
> Thanks for your help Tom.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
>
>