Question Regarding Views and Indexes

If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.

Question Regarding Views and Indexes

If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.

Question regarding Stored Procedures, Views and ASP

Please could someone explain to me the differences between a stored
procedure and a view.

The reason for this question is I have two almost identical ASP pages.
Both get the same results but one uses a stored procedure and one uses
a view. If the query returns no results the 'view page' generates
errors and therefore I have to check for BOF and EOF, whereas the
'stored procedure page' does not generate errors and instead would
appear to return a recordset with 0 entries.

Thanks in advance

Neil.Neil (neil.lloyd@.fdtsolutions.com) writes:
> Please could someone explain to me the differences between a stored
> procedure and a view.
> The reason for this question is I have two almost identical ASP pages.
> Both get the same results but one uses a stored procedure and one uses
> a view. If the query returns no results the 'view page' generates
> errors and therefore I have to check for BOF and EOF, whereas the
> 'stored procedure page' does not generate errors and instead would
> appear to return a recordset with 0 entries.

It might more have to do with who you call them. Could you post the
code, including how you set up cursor type and all? The SP code can
also be useful.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

question regarding referenced views and replication order

You can update the view definition and hope that this updates the
dependencies correctly. If it doesn't you can use sp_addscriptexec to add
all the views in the correct order. Note that in SQL Server 2005 you can
specify the article order (@.processing_order) to get round this type of
issue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Stefan,
replication of views and procs doesn't follow the usual paradigm in
replication. Changes to these programming objects aren't picked up by the
log-reader, so we have to resort to some sort of manual solution to pick up
changes to replicated objects and apply them which ever way we go about it.
The script to generate the view creation scripts could be based on the view
definition - in which case it'll always be upto date if you even need to run
the whole thing again. Ad-hoc changes can meanwhile be done using individual
views.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

question on views

Hello,
A little background...
I have a cube that automatically refreshes every 10 minutes and pulls it's
data from a view ('ViewA') in sql server 2000.
The cube takes around a minute and a half to completely refresh. During
this time, in sql server, one of my other views ('ViewC') starts to return
inconsistent results in the form of varying numbers of records in its result
set. The result set is somewhere in the neighborhood of 1,000 records, and
varies by anywhere from 40 to 50 records each time it is run during the cube
refresh.
A bit more info...
The cube pulls it's data from ViewA with these characteristics (simplified
for relevance):
ViewA selects * from ViewB (nolock) which, in turn, selects * from
MyTable (nolock)
As mentioned earlier, while the cube is refreshing, ViewC returns an
inconsistent number of records. ViewC has these characteristcs (simplified
for relevance):
ViewC selects * from MyTable (nolock)
It seems to me that the problem may have to do with record locking, but as
shown above, i am using (nolock) to prevent the issuing of any shared locks
while ViewA is running. So what is it about this cube refresh that prevents
one of my other views from gaining access to, and completely skipping over,
some of the records?!
One hundred million points for this one ;)
Thanks
Nolock doesn't guarantee consistent data so you can expect
inconsistent results...it's not unusual. You can retrieve
before and after images of rows being updated, can read
uncommitted transactions.
It doesn't look like it's record locking itself that is the
cause the issue but rather using a hint that doesn't care
about other exclusive locks, allows dirty reads. You really
have to weigh out using the nolock hint with the
inconsistent results that you can end up with.
-Sue
On Mon, 17 Oct 2005 15:18:30 -0400, "Jesse Aufiero"
<jaufiero@.moaboil.com> wrote:

>Hello,
>A little background...
>I have a cube that automatically refreshes every 10 minutes and pulls it's
>data from a view ('ViewA') in sql server 2000.
>The cube takes around a minute and a half to completely refresh. During
>this time, in sql server, one of my other views ('ViewC') starts to return
>inconsistent results in the form of varying numbers of records in its result
>set. The result set is somewhere in the neighborhood of 1,000 records, and
>varies by anywhere from 40 to 50 records each time it is run during the cube
>refresh.
>A bit more info...
>The cube pulls it's data from ViewA with these characteristics (simplified
>for relevance):
> ViewA selects * from ViewB (nolock) which, in turn, selects * from
>MyTable (nolock)
>As mentioned earlier, while the cube is refreshing, ViewC returns an
>inconsistent number of records. ViewC has these characteristcs (simplified
>for relevance):
> ViewC selects * from MyTable (nolock)
>It seems to me that the problem may have to do with record locking, but as
>shown above, i am using (nolock) to prevent the issuing of any shared locks
>while ViewA is running. So what is it about this cube refresh that prevents
>one of my other views from gaining access to, and completely skipping over,
>some of the records?!
>One hundred million points for this one ;)
>Thanks
>

question on views

Hello,
A little background...
I have a cube that automatically refreshes every 10 minutes and pulls it's
data from a view ('ViewA') in sql server 2000.
The cube takes around a minute and a half to completely refresh. During
this time, in sql server, one of my other views ('ViewC') starts to return
inconsistent results in the form of varying numbers of records in its result
set. The result set is somewhere in the neighborhood of 1,000 records, and
varies by anywhere from 40 to 50 records each time it is run during the cube
refresh.
A bit more info...
The cube pulls it's data from ViewA with these characteristics (simplified
for relevance):
ViewA selects * from ViewB (nolock) which, in turn, selects * from
MyTable (nolock)
As mentioned earlier, while the cube is refreshing, ViewC returns an
inconsistent number of records. ViewC has these characteristcs (simplified
for relevance):
ViewC selects * from MyTable (nolock)
It seems to me that the problem may have to do with record locking, but as
shown above, i am using (nolock) to prevent the issuing of any shared locks
while ViewA is running. So what is it about this cube refresh that prevents
one of my other views from gaining access to, and completely skipping over,
some of the records?!
One hundred million points for this one ;)
ThanksNolock doesn't guarantee consistent data so you can expect
inconsistent results...it's not unusual. You can retrieve
before and after images of rows being updated, can read
uncommitted transactions.
It doesn't look like it's record locking itself that is the
cause the issue but rather using a hint that doesn't care
about other exclusive locks, allows dirty reads. You really
have to weigh out using the nolock hint with the
inconsistent results that you can end up with.
-Sue
On Mon, 17 Oct 2005 15:18:30 -0400, "Jesse Aufiero"
<jaufiero@.moaboil.com> wrote:

>Hello,
>A little background...
>I have a cube that automatically refreshes every 10 minutes and pulls it's
>data from a view ('ViewA') in sql server 2000.
>The cube takes around a minute and a half to completely refresh. During
>this time, in sql server, one of my other views ('ViewC') starts to return
>inconsistent results in the form of varying numbers of records in its resul
t
>set. The result set is somewhere in the neighborhood of 1,000 records, and
>varies by anywhere from 40 to 50 records each time it is run during the cub
e
>refresh.
>A bit more info...
>The cube pulls it's data from ViewA with these characteristics (simplified
>for relevance):
> ViewA selects * from ViewB (nolock) which, in turn, selects * from
>MyTable (nolock)
>As mentioned earlier, while the cube is refreshing, ViewC returns an
>inconsistent number of records. ViewC has these characteristcs (simplified
>for relevance):
> ViewC selects * from MyTable (nolock)
>It seems to me that the problem may have to do with record locking, but as
>shown above, i am using (nolock) to prevent the issuing of any shared locks
>while ViewA is running. So what is it about this cube refresh that prevent
s
>one of my other views from gaining access to, and completely skipping over,
>some of the records?!
>One hundred million points for this one ;)
>Thanks
>

question on renaming columns

Hi,
Quick question!
Is there anyway to rename a column such that it reflects all columns on all reference tables, stored procedures,views, etc.,
Ex. table1 is with col1 (primary key)

table2 with col2, col1(FK--table1(col1)

if i try to rename col1 on table1 it has to rename col1 on table2 automatically.
Any help is greatly appreciated.
-SSFirst, buy a magic wand....

I've left typos alone because it wasn't worth the risk...or energy...

Question on generating SQL scripts using Enterprise Manager

Hi,

I was using enterprise manager to generate a script for my DB. I
scripted only my tables and views and in Options I picked all the
options EXCEPT "script Primary Keys, Foreign Keys and Constraits " (
which I was going to script seperately ). I noticed that the the
generated file still had all FKs and PKs scripted. When I additionally
unchecked the "script Full-Text indexes" option, it worked as expected.
Any idea why the full-text option causes all constraints to be
scripted. Using SQL server 2000.

Thanksdrdeadpan (vkat01-nospam@.yahoo.com) writes:
> I was using enterprise manager to generate a script for my DB. I
> scripted only my tables and views and in Options I picked all the
> options EXCEPT "script Primary Keys, Foreign Keys and Constraits " (
> which I was going to script seperately ). I noticed that the the
> generated file still had all FKs and PKs scripted. When I additionally
> unchecked the "script Full-Text indexes" option, it worked as expected.
> Any idea why the full-text option causes all constraints to be
> scripted. Using SQL server 2000.

Sounds like a bug.

It would be interesting to see a repro. That is a complete database script
with at most three tables with all these features, and when scripted in
EM displays all these problems. I doubt that the bug will ever be fixed
in Enterprise Manager, but since I'm on the SQL 2005 beta, I would like
to test if the problem is there as well.

By the way, did your tables actually have any full-text indexes?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland for your response.

No, we have NO full text indexes defined. Our tables have a rather
large number of columns so rather than pasting the script here ,I
tested it again. This time I picked 3 tables to be scripted with the
following options.

Script Database
Script database users and database roles
Script object-level permissions
Script indexes
Script full-text indexes.

The above options once again scripted all PKs and FKs even though it
was not requested.

I reran the script without the full-text scripting option and it works
fine i.e no Pks and FKs. SO, I guess it is prefectly reproducable on
Sql Server 2000. I just wanted to make sure I was'nt seeing things.
Great website BTW.

DrD|||drdeadpan (vkat01-nospam@.yahoo.com) writes:
> No, we have NO full text indexes defined. Our tables have a rather
> large number of columns so rather than pasting the script here ,I
> tested it again. This time I picked 3 tables to be scripted with the
> following options.
> Script Database
> Script database users and database roles
> Script object-level permissions
> Script indexes
> Script full-text indexes.
> The above options once again scripted all PKs and FKs even though it
> was not requested.

You don't have to post your actual tables. It's enough to post a few
tables for which the problem appears.

Anyway, I was able to reproduce the problem in SQL 2000, but when I did
a quick test in SQL 2005, no constraints were brought it.

As I mentioned earlier, the likelyhood that this will be fixed in SQL2000
is about nil.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question on generating SQL scripts using Enterprise Manager

Hi,

I was using enterprise manager to generate a script for my DB. I
scripted selected my tables and views and in Options I picked all the
options. I noticed that the
generated file does not include FKs, EXTs or PKs scripted.
Any idea why the full-text options are not scripting the constraints?
Using SQL server 2000.

Thanks(chawes40@.yahoo.com) writes:
> I was using enterprise manager to generate a script for my DB. I
> scripted selected my tables and views and in Options I picked all the
> options. I noticed that the
> generated file does not include FKs, EXTs or PKs scripted.
> Any idea why the full-text options are not scripting the constraints?
> Using SQL server 2000.

Did the database actually have any full-text indexes? I tried to reproduce
the problem according your description, and my script included PKs and
FKs. But I don't even have full-text installed on my machine.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question on constraints and partitioned views

Can you use a combination of columns for the partitioning column in a
partition view? This is what I want to do but I cannot get it to work.
I get an error that UNION ALL view 'vw_My_Data' is not updatable
because a partitioning column was not found.
Here's my DDL
--Group1 July 2003
CREATE TABLE [dbo].[Group1_07_2003] (
[Sample_ID] [uniqueidentifier] NOT NULL ,
[Group_Constraint] [int] Check(Group_Constraint = 1) NOT NULL ,
[Month_Constraint] [int] Check (Month_Constraint = 7)NOT NULL ,
[Year_Constraint] [int] Check (Year_Constraint = 2003)NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[msec] [int] NOT NULL ,
[Device_ID] [bigint] NOT NULL ,
[Topic_ID] [bigint] NOT NULL ,
[Sample_Type_ID] [bigint] NOT NULL ,
[Calculated_Value] [float] NOT NULL ,
[Original_Value] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Group1_07_2003] ADD
CONSTRAINT [PK_Group1_07_2003] PRIMARY KEY NONCLUSTERED
(
[Sample_ID],
[Group_Constraint],
[Month_Constraint],
[Year_Constraint]
) ON [PRIMARY]
GO
I would like to create other tables for Group_Constraint=2, and
Month_Constraint = 7, and Year_Constraint = 2003 and so on. But when
I add in the other group, I start getting the errors when I try to
insert into the view. Can I only use one column?Thank you for your recommendation but it did not fix the problem.
Apparently you can only have one column as your partitioning column.
However, I am ready to ditch the partitioned view design. We have
tried every other way we can to actually apply what we want to do and
either we cannot insert into the view or when we query the data it
goes to all of the tables instead of one table based on the
constraint. Even one of the examples in BOL will not work.
Here's the code from BOL:
CREATE TABLE May1998sales
(OrderID INT,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL
CHECK(DATEPART(mm, DeliveryDate) = 5)
CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID, OrderMonth)
CREATE VIEW Year1998Sales
AS
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
SELECT * FROM Apr1998Sales
UNION ALL
SELECT * FROM May1998Sales
UNION ALL
SELECT * FROM Jun1998Sales
UNION ALL
SELECT * FROM Jul1998Sales
UNION ALL
SELECT * FROM Aug1998Sales
UNION ALL
SELECT * FROM Sep1998Sales
UNION ALL
SELECT * FROM Oct1998Sales
UNION ALL
SELECT * FROM Nov1998Sales
UNION ALL
SELECT * FROM Dec1998Sales
SELECT *
FROM Year1998Sales
WHERE OrderMonth IN (5,6) AND CustomerID = 64892
--
I created just two tables and modified the select statement
SELECT *
FROM Year1998Sales
WHERE OrderMonth =5
I didn't put any data in the tables, I just ran the above select
statement. If you look at the execution plan. It does not go directly
to May1998sales.
I have only gotten one example to actually work and that is using the
customer example
-- On Server1:
CREATE TABLE Customers_33
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
-- On Server2:
CREATE TABLE Customers_66
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 33000 AND 65999),
... -- Additional column definitions)
-- On Server3:
CREATE TABLE Customers_99
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 66000 AND 99999),
... -- Additional column definitions)
However for how we want to partition the data it does not seem to
work. It has been a nightmare. Also don't try to make your
partitioning column on datetime. You can insert into the view but look
at your execution plan. If you use a datetime variable in your where
clause to specify a date, it will not work. I can elaborate more if
anyone is interested. If anyone else is looking at using this design,
BEWARE! It is not documented well in BOL. I think I have said enough.
If anyone has actually gotten this to work, feel free to comment.
"Quentin Ran" <ab@.who.com> wrote in message news:<#of9d4sVDHA.2328@.TK2MSFTNGP12.phx.gbl>...
> Loretta,
> try by putting the PK and constraint in your create table statement. Avoid
> alter table if the table participates in a partitioned view. I do not have
> the reason, but alter table tends to spoil the partitioned view / tables.
> hth
> Quentin
>|||We have similar problem. We have a large table which we
always fetch data by giving certain date.
So, we broke the table into per month bases and created a
partitioned view. The date column (datetime datatype)in
each member table has a check constraint.
When I quire to the view by giving the data condition SQL
server access all member table and it takes very long time
for fetching.
You mentioned that datetime column wouldn't work.
But it must be the once of the most typical case one want
to have a partition view scenario, isn't it?
Is there any workaround from Microsoft side?
I am looking forward to hearing from you!!!
>--Original Message--
>Thank you for your recommendation but it did not fix the
problem.
>Apparently you can only have one column as your
partitioning column.
>However, I am ready to ditch the partitioned view design.
We have
>tried every other way we can to actually apply what we
want to do and
>either we cannot insert into the view or when we query
the data it
>goes to all of the tables instead of one table based on
the
>constraint. Even one of the examples in BOL will not work.
>Here's the code from BOL:
>CREATE TABLE May1998sales
> (OrderID INT,
> CustomerID INT NOT NULL,
> OrderDate DATETIME NULL
> CHECK (DATEPART(yy, OrderDate) = 1998),
> OrderMonth INT
> CHECK (OrderMonth = 5),
> DeliveryDate DATETIME NULL
> CHECK(DATEPART(mm, DeliveryDate) = 5)
> CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID,
OrderMonth)
>CREATE VIEW Year1998Sales
>AS
>SELECT * FROM Jan1998Sales
>UNION ALL
>SELECT * FROM Feb1998Sales
>UNION ALL
>SELECT * FROM Mar1998Sales
>UNION ALL
>SELECT * FROM Apr1998Sales
>UNION ALL
>SELECT * FROM May1998Sales
>UNION ALL
>SELECT * FROM Jun1998Sales
>UNION ALL
>SELECT * FROM Jul1998Sales
>UNION ALL
>SELECT * FROM Aug1998Sales
>UNION ALL
>SELECT * FROM Sep1998Sales
>UNION ALL
>SELECT * FROM Oct1998Sales
>UNION ALL
>SELECT * FROM Nov1998Sales
>UNION ALL
>SELECT * FROM Dec1998Sales
>SELECT *
>FROM Year1998Sales
>WHERE OrderMonth IN (5,6) AND CustomerID = 64892
>--
>I created just two tables and modified the select
statement
>SELECT *
>FROM Year1998Sales
>WHERE OrderMonth =5
>I didn't put any data in the tables, I just ran the above
select
>statement. If you look at the execution plan. It does not
go directly
>to May1998sales.
>I have only gotten one example to actually work and that
is using the
>customer example
>-- On Server1:
>CREATE TABLE Customers_33
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 1 AND 32999),
> ... -- Additional column definitions)
>-- On Server2:
>CREATE TABLE Customers_66
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 33000 AND
65999),
> ... -- Additional column definitions)
>-- On Server3:
>CREATE TABLE Customers_99
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 66000 AND
99999),
> ... -- Additional column definitions)
>However for how we want to partition the data it does not
seem to
>work. It has been a nightmare. Also don't try to make your
>partitioning column on datetime. You can insert into the
view but look
>at your execution plan. If you use a datetime variable in
your where
>clause to specify a date, it will not work. I can
elaborate more if
>anyone is interested. If anyone else is looking at using
this design,
>BEWARE! It is not documented well in BOL. I think I have
said enough.
>If anyone has actually gotten this to work, feel free to
comment.
>
>
>
>"Quentin Ran" <ab@.who.com> wrote in message
news:<#of9d4sVDHA.2328@.TK2MSFTNGP12.phx.gbl>...
>> Loretta,
>> try by putting the PK and constraint in your create
table statement. Avoid
>> alter table if the table participates in a partitioned
view. I do not have
>> the reason, but alter table tends to spoil the
partitioned view / tables.
>> hth
>> Quentin
>>
>.
>|||I agree, it does seem like partitioning by date makes the most sense
but we don't know if any work around. What actually got our team
looking at partitioned views was an article called "Add Scalability
with Data Partitioning" by Jon Rauschenberger. I only have a print
out. Sorry I don't have a link. Anyways, this article seems too good
to be true. But with the design we have in mind, it just might be to
complex. We are still considering dividing our data into smaller
tables but we do not plan on using a view for inserting and querying
the data. We are considering writing our own custom apps to do this
but we are still working on other areas and that has its own
drawbacks. Not sure what the work around is for now. Sorry, no help.
"didi" <carlsdottar@.hotmail.com> wrote in message news:<1a1e01c360c9$c23f09b0$3501280a@.phx.gbl>...
> We have similar problem. We have a large table which we
> always fetch data by giving certain date.
> So, we broke the table into per month bases and created a
> partitioned view. The date column (datetime datatype)in
> each member table has a check constraint.
> When I quire to the view by giving the data condition SQL
> server access all member table and it takes very long time
> for fetching.
> You mentioned that datetime column wouldn't work.
> But it must be the once of the most typical case one want
> to have a partition view scenario, isn't it?
> Is there any workaround from Microsoft side?
> I am looking forward to hearing from you!!!
>
> >--Original Message--