August 14, 2009 at 12:01 pm
Matt Miller (8/14/2009)
David Portas (8/14/2009)
Matt, you missed the point of what I was saying. My UPDATE should not violate the constraint, that's true. But what I wanted to demonstrate was that the constraint will generate an error sometimes even when the constraint is not violated.and my point is - your script does NOT show that. In the original state - there is no error. Nor should there be.
Huh?
David's script shows an example where the constraint is tripped (an error is raised) when it shouldn't be. Didn't you run it?
August 14, 2009 at 12:02 pm
cs_troyk (8/14/2009)
Matt Miller (8/14/2009)
David Portas (8/14/2009)
Matt, you missed the point of what I was saying. My UPDATE should not violate the constraint, that's true. But what I wanted to demonstrate was that the constraint will generate an error sometimes even when the constraint is not violated.and my point is - your script does NOT show that. In the original state - there is no error. Nor should there be.
Huh?
David's script shows an example where the constraint is tripped (an error is raised) when it shouldn't be. Didn't you run it?
I copied it right out of his post, ran it, and got no error - what am I missing here?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 14, 2009 at 12:13 pm
Matt Miller (8/14/2009)I copied it right out of his post, ran it, and got no error - what am I missing here?
Whether you get an error or not is dependent on the execution plan, so it may not be 100% reliable.
What MSSQL version are you using? On 10.50.1092 I always seem to get an error.
August 14, 2009 at 12:13 pm
copied it right out of his post, ran it, and got no error - what am I missing here?
David is right, it works SOMETIMES. In this case it depends on the sequence that SQL Server updates the rows.
Try this slightly modified script, and it should fail. All I've done is to add a clustered index to the column X, prompting SQL Server to update the rows in the order of this index.
CREATE TABLE tbl (id INT NOT NULL PRIMARY KEY nonclustered, x INT NOT NULL);
GO
CREATE FUNCTION dbo.fnRowCount(@x INT)
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*) cnt FROM tbl WHERE x = @x)
END
GO
ALTER TABLE tbl ADD CONSTRAINT chk1 CHECK (dbo.fnRowCount(x)<=3 /* No more than 3 rows for x */)
GO
create clustered index x on tbl(x)
GO
INSERT INTO tbl (id,x) VALUES (1,1);
INSERT INTO tbl (id,x) VALUES (2,1);
INSERT INTO tbl (id,x) VALUES (3,1);
INSERT INTO tbl (id,x) VALUES (4,2);
GO
UPDATE tbl SET x = x + 1;
August 14, 2009 at 12:25 pm
Awesome! Unpredictable behavior based on builds (or better yet, Editions). I ran David's script on SS2K5 9.00.4035.00 and SS2K8 10.0.1600.22 and got the error on both (both Developer Editions).
Imagine how much easier life would be if you could declare constraints (both column constraints and inclusion dependencies) that reference views... http://connect.microsoft.com/SQLServer
TroyK
August 14, 2009 at 12:26 pm
David Portas (8/14/2009)
Matt Miller (8/14/2009)I copied it right out of his post, ran it, and got no error - what am I missing here?
Whether you get an error or not is dependent on the execution plan, so it may not be 100% reliable.
What MSSQL version are you using? On 10.50.1092 I always seem to get an error.
I was in 2005 express.
Ian's does seem to cause the error.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 14, 2009 at 12:52 pm
cs_troyk (8/14/2009)Imagine how much easier life would be if you could declare constraints (both column constraints and inclusion dependencies) that reference views... http://connect.microsoft.com/SQLServer
That would be useful indeed. But constraint support will never be complete until it is possible to do multiple assignments (update more than one table simultaneously). Without multiple assignment any kind of multiple relation constraint is a blunt tool.
August 14, 2009 at 1:11 pm
David Portas (8/14/2009)
cs_troyk (8/14/2009)Imagine how much easier life would be if you could declare constraints (both column constraints and inclusion dependencies) that reference views... http://connect.microsoft.com/SQLServer
That would be useful indeed. But constraint support will never be complete until it is possible to do multiple assignments (update more than one table simultaneously). Without multiple assignment any kind of multiple relation constraint is a blunt tool.
Agree totally. Did you see this workaround for multiple inserts? (Limited to 2 tables and SS2K8, but interesting nonetheless) http://weblogs.sqlteam.com/peterl/archive/2009/07/29/How-to-insert-into-two-tables-in-one-statement.aspx
August 14, 2009 at 1:15 pm
cs_troyk (8/14/2009)Did you see this workaround for multiple inserts? (Limited to 2 tables and SS2K8, but interesting nonetheless) http://weblogs.sqlteam.com/peterl/archive/2009/07/29/How-to-insert-into-two-tables-in-one-statement.aspx
Yes. You can use OUTPUT to insert to another table in 2005 as well. Unfortunately it won't work if the two tables affected are on either side of a foreign key constraint.
August 14, 2009 at 1:17 pm
--David beat me to it--
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 17, 2009 at 11:50 am
David Portas (8/14/2009)
cs_troyk (8/14/2009)Did you see this workaround for multiple inserts? (Limited to 2 tables and SS2K8, but interesting nonetheless) http://weblogs.sqlteam.com/peterl/archive/2009/07/29/How-to-insert-into-two-tables-in-one-statement.aspx
Yes. You can use OUTPUT to insert to another table in 2005 as well. Unfortunately it won't work if the two tables affected are on either side of a foreign key constraint.
The OUTPUT is only part of the trick. The other part is the way the "MERGE" operator (SS2K8+ only) is being used to populate the 2 tables where the attributes of interest for the 2nd table are not in the set of attributes available in the "inserted" or "deleted" virtual tables of the 'classic' OUTPUT clause.
You are correct, though, that the FK restriction on the target of the output clause severely limits the real-world usefulness of this trick.
August 17, 2009 at 8:16 pm
Hi All,
I am really surprise on how the discussion went through but I am glad that I have personally witness how Top Caliber Database Personnel thinks even on simple cases.
Have read on functions and constraint and have tried simple ones also. I just have this query, does implementing all of this adds the overhead in terms of data insertion? I am thinking what if my table grows and I do think adding this constraints will add the overhead in terms of processing. I am not sure but is my thinking wrong?
Also, I did learn a few tricks from the discussions that you have. Particularly,
'never ever left the validation to your application' and 'put the implementation of your business rules as close to the database as possible'.
I personally learn so much from this point of view. 🙂
I think I would post more in this site. Kudos to all of you! 😀
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply