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

nocheck when creating a constraint

i was looking at a database creation script and i have a bunch of
constraints being created with a nocheck on them. i think i know what
the nocheck does (don't check for data issues). i'm not sure why the
original coder would define them like this since there is no data to
begin with when the tables are built. am i missing something? does
this hurt the database table?
ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]That seems to be a stupid thing to do. You won't gain anything, and possibly just have a non-trusted
constraint which can lead to worse performance (non-trusted will limit some of the optimizations
that can be done).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1178199834.825140.114810@.y5g2000hsa.googlegroups.com...
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]
>|||Derek,
See this blog from SQL Server MVP Hugo Kornelis.
Can you trust your constraints?
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx
AMB
"Derek" wrote:
> i was looking at a database creation script and i have a bunch of
> constraints being created with a nocheck on them. i think i know what
> the nocheck does (don't check for data issues). i'm not sure why the
> original coder would define them like this since there is no data to
> begin with when the tables are built. am i missing something? does
> this hurt the database table?
>
> ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD
> CONSTRAINT [PK_mytable] PRIMARY KEY ([mytable_id]) ON [PRIMARY]
>

No triggers, constraints

I have several sql server databases that were recently moved to a new
server. In the process of migrating the databases, any triggers and
constraints attached to tables were removed on accident. I need to add
these objects back into the databases, however I'm worried that I will
have problems with referential integrity since there is a chance
someone may have deleted or updated a record that should have cascaded
changes to other tables but did not since the triggers and constraints
were missing. Is there an easy way to check for problems of this
nature in the databases?

Also - I am planning on generating a script from the database on the
old server that scripts the triggers and constraints, and then use
Query Analyser to run this script against the database on the new
server. Is this the best way to add these objects back into databases
that are already populated with data and running on a live server?

In in a pretty bad position, but at least these are databases that are
not accessed too frequently, so I'm hoping any referential integrity
problems will be minor.
Any help or advice would be greatly appreciated.<jason_s_ford@.hotmail.com> wrote in message
news:1097705795.080513.155750@.c13g2000cwb.googlegr oups.com...
>I have several sql server databases that were recently moved to a new
> server. In the process of migrating the databases, any triggers and
> constraints attached to tables were removed on accident. I need to add
> these objects back into the databases, however I'm worried that I will
> have problems with referential integrity since there is a chance
> someone may have deleted or updated a record that should have cascaded
> changes to other tables but did not since the triggers and constraints
> were missing. Is there an easy way to check for problems of this
> nature in the databases?

Not really, since in order to check this you would need to know the
primary/foreign keys that should be there anyway. So you might as well just
add them back again, and see if you get any errors. Triggers are a bit more
of a problem, but if you're only using them for RI, then hopefully adding
the foreign keys will pick up any problems. If you have more complex
business rules in triggers, then there are no real shortcuts except to work
through your logic and see if any data is incorrect - hopefully you don't
have too many...

> Also - I am planning on generating a script from the database on the
> old server that scripts the triggers and constraints, and then use
> Query Analyser to run this script against the database on the new
> server. Is this the best way to add these objects back into databases
> that are already populated with data and running on a live server?

That's fine. If the script is large, make sure you run it in chunks to avoid
getting too many errors back in one go. And of course try it out on a test
copy of the database first.

> In in a pretty bad position, but at least these are databases that are
> not accessed too frequently, so I'm hoping any referential integrity
> problems will be minor.
> Any help or advice would be greatly appreciated.

For the future, you might want to review how you move databases to new
servers (backup/restore is usually an easy option, and you can't lose
anything that way), and perhaps look at some schema comparison tools, which
can help you find and fix schema issues. This one is quite good, but there
are plenty of others:

http://red-gate.com/SQL_Compare.htm

Simon