SQL Server Performance Forum – Threads Archive

Difficult excution plan in partition view

Hi all,
I did a SELECT statement on a partition view "YMDWK",but there is a problem for get the best excution plan©s
One of the base table’s structure is just like this:
TABLE NAME:
YMDWK200501;
PRIMARY KEY:
YMDAY VARCHAR(10) + EMPLOYEE_ID VARCHAR(6);
CHECK CONSTRAINT:
YMDAY BETWEEN ‘2005/01/01’ AND ‘2005/01/31’; The base tables was created for every month.
Following text are the select statements:
1)SELECT *
FROM YMDWK
WHERE YMDAY BETWEEN ‘2005/08/01’ AND ‘2005/08/31’;
The excution plan shows that sql server just has scaned the base table YMDWK200508,this is what I want; 2)DECLARE
@DATE1 VARCHAR(10),
@DATE2 VARCHAR(10)
SET @DATE1=’2005/08/01′,
SET @DATE2=’2005/08/31′
SELECT *
FROM YMDWK
WHERE YMDAY BETWEEN @DATE1 AND @DATE2;
The excution plan shows sql server has scaned the tables from YMDWK200508 to YMDWK200512; In actuality,we always need to excute the code like section 2,but want to get the excution plan like section 1’s.
Thanks for any help. Jelly.

---

Which version of sql server do you have? There was a bag when you select all columns from the partitioned view… Try:
SELECT <not all columns from view>
FROM YMDWK
WHERE YMDAY BETWEEN ‘2005/08/01’ AND ‘2005/08/31’

---

Thanks for mmarovic’s help.
My SQL Server’s version is SQL Server 2000 Enterprise.
In this cause,I have to use "SELECT *" statement ,because I need to Update/Insert/Delete This partitioned view.The SQL Server ON LINE BOOK says that you must set a value for every column of a partitioned view which was been I/D/U. Jelly.

---

See if next discussion helps: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6493&SearchTerms=partition

---

]]>