May 1, 2018 at 1:16 pm
I actually found out where most of my reasoning went wrong. Not having a lot of experience yet I completely forgot that checks are performed after the insert is processed by SQL Server (meaning the table is already altered).
This resulted in max() returning the amount that needed to be stored into the table if this was higher than the current highest bid or null. So that was why comparing against null (if(@a is null)) did not work.
Yet this journey resulted in some other short questions to the more experienced users.
1. Would it be better to use a trigger instead of a check with a scalar function?
2. Why is there loss of performance when creating local variables? Logic would tell me that stored a value that I need more then once is faster then running the same select statement multiple times.
May 1, 2018 at 1:23 pm
Student031 - Tuesday, May 1, 2018 1:16 PMI actually found out where most of my reasoning went wrong. Not having a lot of experience yet I completely forgot that checks are performed after the insert is processed by SQL Server (meaning the table is already altered).This resulted in max() returning the amount that needed to be stored into the table if this was higher than the current highest bid or null. So that was why comparing against null (if(@a is null)) did not work.
Yet this journey resulted in some other short questions to the more experienced users.
1. Would it be better to use a trigger instead of a check with a scalar function?
2. Why is there loss of performance when creating local variables? Logic would tell me that stored a value that I need more then once is faster then running the same select statement multiple times.
1) The very best thing to do is to modify the system/application which performs the INSERT and to put the checking logic in there. Is this a possibility?
2) I think the argument here is that you can avoid the use of local variables completely & that this is faster than using them.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2018 at 1:41 pm
Phil Parkin - Tuesday, May 1, 2018 1:23 PM1) The very best thing to do is to modify the system/application which performs the INSERT and to put the checking logic in there. Is this a possibility?
2) I think the argument here is that you can avoid the use of local variables completely & that this is faster than using them.
1) We are being thought (student) not to do that as it goes against the single point of definition principle. Other systems or applications that use the same database might not have those same restrictions put into place.
2) Correct. I guess I am asking my question the wrong way. When I post code I try to limit the info I give to only solve one issue I am running into instead of the entire problem (has happened to often that the learning process is taken away by people giving complete answers to a question instead of helping to think in the right direction).
The question should be. When needing the result from one query multiple times in a procedure/UDF/trigger, is it best to store it in a variable or run that select multiple times?
3) This one just came up. How do I compare the old and new value in MS Server inside a trigger, does ms sql not have anything similar to new.name <> old.name?
May 1, 2018 at 1:49 pm
Student031 - Tuesday, May 1, 2018 1:41 PMPhil Parkin - Tuesday, May 1, 2018 1:23 PM1) The very best thing to do is to modify the system/application which performs the INSERT and to put the checking logic in there. Is this a possibility?
2) I think the argument here is that you can avoid the use of local variables completely & that this is faster than using them.1) We are being thought (student) not to do that as it goes against the single point of definition principle. Other systems or applications that use the same database might not have those same restrictions put into place.
2) Correct. I guess I am asking my question the wrong way. When I post code I try to limit the info I give to only solve one issue I am running into instead of the entire problem (has happened to often that the learning process is taken away by people giving complete answers to a question instead of helping to think in the right direction).
The question should be. When needing the result from one query multiple times in a procedure/UDF/trigger, is it best to store it in a variable or run that select multiple times?
3) This one just came up. How do I compare the old and new value in MS Server inside a trigger, does ms sql not have anything similar to new.name <> old.name?
Inside a trigger, SQL Server allows the use of two virtual tables, named INSERTED and DELETED. You can reference those inside a trigger in the same way you reference any other table, save that no schema or database name is going to be used. If you use a table alias in referencing either of them, then you can use that alias for each column that you touch as well.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2018 at 1:50 pm
Student031 - Tuesday, May 1, 2018 1:41 PMPhil Parkin - Tuesday, May 1, 2018 1:23 PM1) The very best thing to do is to modify the system/application which performs the INSERT and to put the checking logic in there. Is this a possibility?
2) I think the argument here is that you can avoid the use of local variables completely & that this is faster than using them.1) We are being thought (student) not to do that as it goes against the single point of definition principle. Other systems or applications that use the same database might not have those same restrictions put into place.
2) Correct. I guess I am asking my question the wrong way. When I post code I try to limit the info I give to only solve one issue I am running into instead of the entire problem (has happened to often that the learning process is taken away by people giving complete answers to a question instead of helping to think in the right direction).
The question should be. When needing the result from one query multiple times in a procedure/UDF/trigger, is it best to store it in a variable or run that select multiple times?
3) This one just came up. How do I compare the old and new value in MS Server inside a trigger, does ms sql not have anything similar to new.name <> old.name?
SQL Server has two virtual tables in triggers. They are inserted and deleted. Those contain the new and old values.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2018 at 1:52 pm
2) The question should be. When needing the result from one query multiple times in a procedure/UDF/trigger, is it best to store it in a variable or run that select multiple times?
Neither. You're right not to repeat a SELECT multiple times, but you can use a derived table instead to avoid creating a local variable. ANY local variable appearing in a function will require a separate allocation of storage every time the function is run. Below is the trigger rewritten to assign the result of the SELECT an alias name: And, you're right, that's probably a better/cleaner way to write this code anyway.
CREATE FUNCTION dbo.fn_ck_newBidIsHigher
(
@auction int,
@bid numeric(8,2)
)
RETURNS bit
AS
BEGIN
RETURN (
SELECT CASE WHEN amount IS NULL OR @bid > amount THEN 1 ELSE 0 END AS result
FROM (
SELECT MAX(amount) AS amount
FROM dbo.Bid
WHERE auctionId = @auction
) AS find_amount /*derived table; also often called a "subquery", altho perhaps that's not tech 100% true*/
)
END
GO
3) This one just came up. How do I compare the old and new value in MS Server inside a trigger, does ms sql not have anything similar to new.name <> old.name?
SQL Server has automatically-created views named "inserted" and "deleted" available in a trigger to allow you to see the before and after values of the statement being run. You can join those views to compare new and old values.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 1, 2018 at 2:01 pm
Student031 - Tuesday, May 1, 2018 1:41 PM1) We are being thought (student) not to do that as it goes against the single point of definition principle. Other systems or applications that use the same database might not have those same restrictions put into place.
In many cases, the INSERTs are done by only a single application which calls a stored proc to perform the INSERT. Or all systems which perform the INSERTs call the same stored proc. In these cases, my statement holds true.
If you have multiple applications all performing INSERTs in their own different and special ways, this is what I would call a 'wild-west situation'. I'd be looking to rationalise things so that all INSERTs are done using the same logic.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply