Non-Deterministic function in DPV

Hi! Using the new SQL 2005 functionality where the optimizer determines
where the data is based on the check constraints defined on the tables, can
you use a non-deterministic function such as getdate() in the check
constraints as long as you have another mechanism to make certain the data
gets moved when the date is no longer in the valid range.
For example: Table 1 has data that is 12 months old or less. Table 2 has
data that is 13 months old or more.
Thanks.alw (alw@.discussions.microsoft.com) writes:
> Hi! Using the new SQL 2005 functionality where the optimizer determines
> where the data is based on the check constraints defined on the tables,
> can you use a non-deterministic function such as getdate() in the check
> constraints as long as you have another mechanism to make certain the
> data gets moved when the date is no longer in the valid range.
> For example: Table 1 has data that is 12 months old or less. Table 2 has
> data that is 13 months old or more.
No. It simply does not make any sense. SQL Server cannot trust you to
actually use that mechanism. If you want to move data daily, then you
have to redefine the constraints daily. Not to talk about if your datetime
column also has minutes and milliseconds...
Note that in SQL 2005 you also have partitioned tables.
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