Hi All,
     I am trying to insert rows from two tables which are having same
number of rows with similar schema (except constraints) so I mean two
tables One having constraints and another not having any constraints
(but they are having same rows).

Now I am trying this

insert into SomeTable select * from Table1 where Condition
-- Table1 is having Constraints (and it retreives correct rows)

insert into SomeTable select * from Table2 where Condition
-- Table2 is having no Constraints (and it retreives incorrect rows)

Is this possible ?

I could not found any explaintion for this behaviour. The platform I am
trying this is on Windows 2003/ SQL Server 2000 with no Service Packs
..Can someone guide me on this

With Warm regards
Jatinder Singh

Jatinder,

    The information you posted is inadequate to comment on the issue.

    Can you post a repro?

    Either way, the constraints on a table has no effects when you are
retrieving data from that table.

    No Service packs? Numerous problems/bugs were fixed  in the service
packs.
    So apply them first.

Show quoteHide quote

Hi Roji,

Either way, the constraints on a table has no effects when you are
retrieving data from that table.

Yeah that is the point which stumped me.
I will be posting DDL,DML etc soon as it is very large I have to trim
it down to bare minimum .

With Warm regards
Jatinder Singh

Can you post the code?  This is still too vague.

CHECK() constraints are checked when you do an insertion.  A SELECT
statement can use them by putting the predicates into the optimizer.  I
am not sure if SQL Server's is smart enough for that.

Yes, it certainly does use them in the plan as long as they are trusted:

create table testConstraint
(
testConstraintId int identity(1,1) PRIMARY KEY,
value int,
padRow  char(1000) default (replicate(':)',500))
)

--put 10000 rows into the table
insert into testConstraint(value)
select (number) + 200
from  sequence --table of integers from 0 to 10000

select * from testConstraint where value = 100

Scan(OBJECT:([tempdb].[dbo].[testConstraint].[PK__testConstraint__0DAF0CB0]),
WHERE:([testConstraint].[value]=Convert([@1])))

ALTER TABLE testConstraint
   ADD CONSTRAINT CKtestConstraint CHECK (value >= 200)

select objectproperty(object_id('CKtestConstraint'),'CnstIsNotTrusted')
--returns 0 means it is trusted, so SQL Server knows that ALL data matches

Now checking the plan again includes a filter to check the parameter to see
if it passes the check constraint

Scan(OBJECT:([tempdb].[dbo].[testConstraint].[PK__testConstraint__0DAF0CB0]),
WHERE:([testConstraint].[value]=Convert([@1])))

Show quoteHide quote

Grid display
Like vs =
T-SQL Question
Query Speed
Stored procedures in an n-tiered environment.