To check whether retrieval works efficiently, you can insert values directly into the partitions:
INSERT INTO T1 VALUES(10)
INSERT INTO T2 VALUES(3000000000)
INSERT INTO T3 VALUES(5000000000)
then try retrieving data that's supposed to be in only one partition:
SET STATISTICS IO ON
SELECT * FROM V1 WHERE col1 = 10
The results of the graphical execution plan and of STATISTICS IO show that SQL Server accessed all partitions:
Table 'T3'. Scan count 1, logical reads 2, ...
Table 'T2'. Scan count 1, logical reads 2, ...
Table 'T1'. Scan count 1, logical reads 2, ...
SQL Server has a certain way it interprets the data type of constants that you specify. For example, SQL Server considers 10 an integer. However, it considers a whole number outside the range of regular integers (e.g., 4,000,000,000) as numeric with a scale of zero, not bigint. Now, let's consider the following expression from one of the CHECK constraints:
col1 > 4000000000
Col1 is a bigint column, but 4000000000 is numeric, so SQL Server implicitly converts the value that has the lower precedence data type to the one with the higher precedence. Checking BOL under "Precedence, Data Type Precedence" reveals that numeric (decimal) has a higher precedence than bigint. So your partitioning expression effectively becomes
CONVERT(numeric, col1) > 4000000000
One rule about the partitioning column is that you can't specify it as an argument for a function in the CHECK constraint's expression. In this case, the column implicitly became the CONVERT() function's argument, so you can't use it for partitioning. To fix the problem, explicitly convert the constants to bigint. Drop the old CHECK constraints and recreate the correct ones by running the code that Listing 5 shows. Make sure that STATISTICS IO is turned on, then try to retrieve and modify the view—it works as expected:
INSERT INTO V1 VALUES(11)
SELECT * FROM V1 WHERE col1 = 10
Imparted Wisdom
I hope I've convinced you to use T-SQL when performing schema changes or at least to be cautious when using Enterprise Manager. Be aware of what happens behind the scenes, and make sure you use T-SQL when you find that Enterprise Manager doesn't perform efficiently.
In addition, when faced with a limitation that might compromise an important part of your application, such as backward compatibility, don't give up. Always look for a way to get what you want. If you look hard enough, you'll probably find a solution.