Can SQL Server automatically read from more than one table if data spans more than one table without modifying the query?

I have a table called 'custom开发者_JAVA百科er' which has too many rows & the decision was made to split the table into multiple tables by a date column called InsertedDate. The data span years 2009-2011.

I would like to put each year in its own table. So customers for 2009 go into one table called 'customer2009' and so on.

Say I have I have a query like:

SELECT LastName
  FROM Customer 
 WHERE InsertedDate BETWEEN '12/20/2009' 
                        AND '01/15/2010'

Is there a feature in SQL Server (in Enterperise edition ?) where if it gets such a query, it intelligently knows to get the data from different tables. The reason I ask is that I don't want to modify the query into: (there are hundreds of queries)

SELECT LastName 
  FROM Customer2009
 WHERE InsertedDate >= '12/20/2009' 
UNION
SELECT LastName 
  FROM Customer2010 
 WHERE InsertedDate <= '01/15/2010'

I would like to read a white paper and best practices and architecture to do this type of thing if a good resource exists.

Addition:

The gist of my question is I wanted to know if there's a built-in Enterprisy feature in SQL Server. Not to hack a solution manually which needs to be modified and maintained by people.

Look into table partitioning in SQL Server 2005+.

You could create a view:

create view vCustomer
as
SELECT LastName 
FROM Customer2009
WHERE InsertedDate >= '12/20/2009' 
UNION
SELECT LastName 
FROM Customer2010 
WHERE InsertedDate <= '01/15/2010'

Of course you would still need to update your stored procs, but it would be a little easier.

SELECT LastName
FROM vCustomer 
WHERE InsertedDate BETWEEN '12/20/2009' AND '01/15/2010'

May be view - is solution? why not if so?

I think your best option (provided your sticking with this somewhat strange architecture) is bulding a view that looks at all those tables.

CREATE OR REPLACE VIEW vw_all_customers AS
SELECT * FROM Customer2009
UNION
SELECT * FROM Customer2010
...
;

This view gives you the power to do stuff like this:

SELECT LastName
FROM vw_all_customers
WHERE InsertedDate >= '12/20/2009' 
AND InsertedDate <= '01/15/2010'

You can consider two options : built-in partition functions (as proposed by Joe Stefanelli) and manual partitioning which involves creating partitioned view. Partitioned views have some restrictions, but they also give you a possibility to use additional hardware resources, for example splitting data between different instances of SQL Server. You can find some useful info here