April 30, 2010 at 7:30 am
If the LunchPeriod is not explicitly inserted it's default value will be filled in the 'inserted' row set within the trigger. Given that it easy to modify and even simplify Paul's nice solution:
CREATE TABLE dbo.Class
(
ClassID INTEGER IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(10) NOT NULL,
LunchPeriod INTEGER NULL
);
CREATE TABLE dbo.Student
(
StudentID INTEGER IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
LunchPeriod INTEGER NOT NULL DEFAULT (4),
ClassID INTEGER NOT NULL REFERENCES dbo.Class
);
INSERT dbo.Class
(
Name,
LunchPeriod
)
VALUES ('10-A', 4),
('10-B', NULL),
('9-A', 3),
('9-B', 4),
('11-A', 4),
('11-B', 5);
GO
CREATE TRIGGER [trg dbo.Student IOI]
ON dbo.Student
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM inserted) RETURN;
INSERT dbo.Student
(
FirstName,
LastName,
ClassID,
LunchPeriod
)
SELECT INS.FirstName,
INS.LastName,
INS.ClassID,
COALESCE(C.LunchPeriod, INS.LunchPeriod)
FROM inserted INS
JOIN dbo.Class C
ON C.ClassID = INS.ClassID
END;
GO
INSERT dbo.Student
(
FirstName,
LastName,
ClassID
)
VALUES ('John', 'Smith', 2),
('Paul', 'White', 3),
('Goldie', 'Graber', 6),
('Peter', 'Brinkhaus', 2)
GO
SELECT *
FROM dbo.Student
GO
DROP TRIGGER [trg dbo.Student IOI]
DROP TABLE dbo.Student;
DROP TABLE dbo.Class;
April 30, 2010 at 7:33 am
How about:
USE tempdb;
GO
CREATE TABLE dbo.Class
(
ClassID INTEGER IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(10) NOT NULL,
LunchPeriod INTEGER NULL
);
CREATE TABLE dbo.Student
(
StudentID INTEGER IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
LunchPeriod INTEGER NOT NULL,
ClassID INTEGER NOT NULL REFERENCES dbo.Class
);
INSERT dbo.Class
(
Name,
LunchPeriod
)
VALUES ('10-A', 4),
('10-B', NULL),
('9-A', 3),
('9-B', 4),
('11-A', 4),
('11-B', 5);
CREATE TRIGGER [trg dbo.Student IOI]
ON dbo.Student
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM inserted) RETURN;
INSERT dbo.Student
(
FirstName,
LastName,
ClassID,
LunchPeriod
)
SELECT INS.FirstName,
INS.LastName,
INS.ClassID,
COALESCE(INS.LunchPeriod, C.LunchPeriod, 4)
FROM inserted INS
JOIN dbo.Class C
ON C.ClassID = INS.ClassID;
END;
GO
INSERT dbo.Student
(
FirstName,
LastName,
ClassID,
LunchPeriod
)
SELECT 'John',
'Smith',
2,
NULL;
GO
SELECT *
FROM dbo.Student
GO
DROP TABLE dbo.Student;
DROP TABLE dbo.Class;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 7:36 am
Peter Brinkhaus (4/30/2010)
If the LunchPeriod is not explicitly inserted it's default value will be filled in the 'inserted' row set within the trigger. Given that it easy to modify and even simplify Paul's nice solution:
Doh! Of course! 🙁
Nice catch, Peter.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 8:00 am
Paul White NZ (4/30/2010)
Peter Brinkhaus (4/30/2010)
If the LunchPeriod is not explicitly inserted it's default value will be filled in the 'inserted' row set within the trigger. Given that it easy to modify and even simplify Paul's nice solution:Doh! Of course! 🙁
Nice catch, Peter.
No problem, Paul. It was your solution which brought me to the idea. Never realized that default values are filled in the inserted row set.
Some other thing, why are you explicitly testing if the inserted row set is empty. If it is, the insert statement is a no-op (even the two insert statements in your original solution), or am I missing something?
Peter
April 30, 2010 at 8:10 am
Peter Brinkhaus (4/30/2010)
Some other thing, why are you explicitly testing if the inserted row set is empty. If it is, the insert statement is a no-op (even the two insert statements in your original solution), or am I missing something?
Just habit: if there's no work to do, I like to get out of the trigger at the start, to avoid recompilations, version-store reads, locks being taken, statistics being updated, or any of the other possible side-effects of running the remaining statements.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 8:31 am
Paul White NZ (4/30/2010)
Peter Brinkhaus (4/30/2010)
Some other thing, why are you explicitly testing if the inserted row set is empty. If it is, the insert statement is a no-op (even the two insert statements in your original solution), or am I missing something?Just habit: if there's no work to do, I like to get out of the trigger at the start, to avoid recompilations, version-store reads, locks being taken, statistics being updated, or any of the other possible side-effects of running the remaining statements.
That's clear. Thanks.
Peter
April 30, 2010 at 10:32 pm
I'll admit that I scanned this, but I didn't see anyone mention a UDF as the default constraint on the column that does a lookup for the value in the other table. Apologies if someone did and I missed it, but wanted to also throw that out there as an option.
One note: If you are going to be using this same constraint on a bunch of tables and need to look up to a reference values table that changes infrequently, you might consider a READ UNCOMMITTED or NOLOCK transaction level on the lookup within the constraint. I've had blocking issues with these in the past when many tables used the same one.
May 1, 2010 at 12:56 am
Garadin (4/30/2010)
I'll admit that I scanned this, but I didn't see anyone mention a UDF as the default constraint on the column that does a lookup for the value in the other table. Apologies if someone did and I missed it, but wanted to also throw that out there as an option.
It is an option, but one that I would discourage 😉
A scalar UDF that does data access is just a horrible thing. Not only do you get the usual overhead of calling the UDF once per row, it results in a plan where the data access is performed once per row - in separate execution plans.
Whether these are real concerns here, I don't know - but I do know that scalar UDFs are a bad habit to acquire, and scalar UDFs that access data even more so.
If you are going to be using this same constraint on a bunch of tables and need to look up to a reference values table that changes infrequently, you might consider a READ UNCOMMITTED or NOLOCK transaction level on the lookup within the constraint. I've had blocking issues with these in the past when many tables used the same one.
You might consider such a locking hint as a last resort under extreme circumstances, but there are always better solutions. (I know you have a rather 'special' system in this respect - but my advice is general in nature).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2010 at 10:23 am
Paul White NZ (5/1/2010)
Garadin (4/30/2010)
I'll admit that I scanned this, but I didn't see anyone mention a UDF as the default constraint on the column that does a lookup for the value in the other table. Apologies if someone did and I missed it, but wanted to also throw that out there as an option.It is an option, but one that I would discourage 😉
A scalar UDF that does data access is just a horrible thing. Not only do you get the usual overhead of calling the UDF once per row, it results in a plan where the data access is performed once per row - in separate execution plans.
Whether these are real concerns here, I don't know - but I do know that scalar UDFs are a bad habit to acquire, and scalar UDFs that access data even more so.
If you are going to be using this same constraint on a bunch of tables and need to look up to a reference values table that changes infrequently, you might consider a READ UNCOMMITTED or NOLOCK transaction level on the lookup within the constraint. I've had blocking issues with these in the past when many tables used the same one.
You might consider such a locking hint as a last resort under extreme circumstances, but there are always better solutions. (I know you have a rather 'special' system in this respect - but my advice is general in nature).
I don't disagree and I think you're right for pointing out the performance concerns. Every tool has a usage and I don't know if this is a spot for this one, just wanted to throw it out as an option. Depending on the insert patterns of the table, it could end up having significantly more overhead than the trigger, as the scalar UDF would access data once per row instead of once per statement. That said, it wouldn't need to execute if you actually did supply values, so it might be ok to catch that occasional row that fell through the cracks.
The whole concept of using a scalar udf (and one that accesses data no less, which I normally hate as well) might be argued against, but I *would* say that this is probably one of the most ideal times to use a NOLOCK or READ UNCOMMITTED hint... especially if the table being referenced changed very infrequently.
Also, even though you have the overhead of a scalar UDF that accesses data, due to the pattern that I'm imagining this would take (Passing just the table name and column name to the function and getting back a value), that value would be cached in memory after the first hit and subsequent hits should be much faster (That's my thought anyways, correct me if I'm wrong on that).
May 1, 2010 at 10:27 am
Continuing with the crazy ideas...
What about putting a trigger on the table that stored the default values to modify the default constraints of the data table. That way you can avoid the performance hit on data modification and only take it when you change the default values. Depending on the usage that might be problematic though, as I believe you'd need a schema lock on the table to alter the default constraints, so that might not be a realistic option either.
May 1, 2010 at 11:08 am
Garadin (5/1/2010)
Continuing with the crazy ideas...What about putting a trigger on the table that stored the default values to modify the default constraints of the data table. That way you can avoid the performance hit on data modification and only take it when you change the default values. Depending on the usage that might be problematic though, as I believe you'd need a schema lock on the table to alter the default constraints, so that might not be a realistic option either.
Now that I've had a cup of coffee this morning, a lot more issues occurred to me with this particular concept. You should probably discard that one.
May 1, 2010 at 10:00 pm
Garadin (5/1/2010)
That said, it wouldn't need to execute if you actually did supply values, so it might be ok to catch that occasional row that fell through the cracks.
That is true - so I guess it does depend on usage patterns.
...I *would* say that this is probably one of the most ideal times to use a NOLOCK or READ UNCOMMITTED hint... especially if the table being referenced changed very infrequently.
I think we could have an very interesting discussion about this - though I would argue that if the referenced data changed very infrequently, there would be no need to perform a dirty read anyway. My broad concern here is that integrity rules don't sit well with dirty reads.
Also, even though you have the overhead of a scalar UDF that accesses data, due to the pattern that I'm imagining this would take (Passing just the table name and column name to the function and getting back a value), that value would be cached in memory after the first hit and subsequent hits should be much faster (That's my thought anyways, correct me if I'm wrong on that).
I'm not quite sure I see how this function would work, since dynamic SQL is not allowed in a function.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 9:08 am
Paul White NZ (5/1/2010)
Garadin (4/30/2010)
I'll admit that I scanned this, but I didn't see anyone mention a UDF as the default constraint on the column that does a lookup for the value in the other table. Apologies if someone did and I missed it, but wanted to also throw that out there as an option.It is an option, but one that I would discourage 😉
A scalar UDF that does data access is just a horrible thing. Not only do you get the usual overhead of calling the UDF once per row, it results in a plan where the data access is performed once per row - in separate execution plans.
Whether these are real concerns here, I don't know - but I do know that scalar UDFs are a bad habit to acquire, and scalar UDFs that access data even more so.
If you are going to be using this same constraint on a bunch of tables and need to look up to a reference values table that changes infrequently, you might consider a READ UNCOMMITTED or NOLOCK transaction level on the lookup within the constraint. I've had blocking issues with these in the past when many tables used the same one.
You might consider such a locking hint as a last resort under extreme circumstances, but there are always better solutions. (I know you have a rather 'special' system in this respect - but my advice is general in nature).
Out of curiosity, what is the difference between a UDF that accesses data and a trigger that accesses data? Won't the trigger also get called once per row?
May 3, 2010 at 9:48 am
Goldie Graber (5/3/2010)
Out of curiosity, what is the difference between a UDF that accesses data and a trigger that accesses data? Won't the trigger also get called once per row?
You're alive!!! I thought we'd lost you! Did those suggestions solve your problem?
And no, a trigger is called once per statement - so you might find one row or 100,000 rows in the inserted and/or deleted pseudo-tables.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 10:24 am
Paul White NZ (5/3/2010)
Goldie Graber (5/3/2010)
Out of curiosity, what is the difference between a UDF that accesses data and a trigger that accesses data? Won't the trigger also get called once per row?You're alive!!! I thought we'd lost you! Did those suggestions solve your problem?
And no, a trigger is called once per statement - so you might find one row or 100,000 rows in the inserted and/or deleted pseudo-tables.
Thanks for the clarification about the trigger.
I think the trigger idea is cool, but I have a number of reservations:
1. Impact. How much would insert speed slow down?
2. Complexity. As mentioned before, the trigger logic would be complex. This would probably impact speed as well.
3. Maintainability. If I start down this path I will have a numerous triggers in the database. I generally try to avoid triggers because they become hard to maintain, and are easily lost.
I also have a question about how you implemented the trigger.
Why did you hard-code the column default in the second version that you posted?
To me that seems to miss the point of not hard-coding the values in the stored procedure.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply