Viewing 15 posts - 2,671 through 2,685 (of 7,619 total)
case
when columnA <> 0
then columnB = 1
else columnB
end
The main thing to understand about CASE is that every result from a CASE must be a single value. The expression leading to...
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".
October 17, 2019 at 5:34 pm
Certain errors by default won't fail the entire transaction/batch. Before the transaction, use:
SET XACT_ABORT ON
to make SQL fail the entire transaction if an error like that occurs.
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".
October 15, 2019 at 9:48 pm
I think we may have interpreted the q differently. I took "statistics i/o" to mean the results from:
SET STATISTICS IO ON;
The logical i/o results are directly comparable, aren't they?
I think maybe...
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".
October 15, 2019 at 5:52 pm
Really it's best to use them in combination.
High I/O numbers tell you to look for better ways to do related part(s) of a query. The execution plan shows you what...
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".
October 15, 2019 at 2:21 pm
Something along these lines:
ALTER TRIGGER [dbo].[trig_UpdatePlacementCount]
ON [dbo].[DebtorHistory]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE DI
SET placement_count = dh.file_no_count
FROM dbo.DebtorInfo DI
INNER JOIN inserted i ON i.file_no = DI.file_no
INNER JOIN (
...
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".
October 14, 2019 at 2:28 pm
You could do comparisons after the fact using the auditing data, but that would be big overhead.
You don't need separate triggers for the original trigger to indicate which column(s) were...
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".
October 11, 2019 at 5:35 pm
The problem with the all digits string is it can be mistaken for an integer.
100% false. Date literals are strings, or delimited in some other way to distinguish them from...
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".
October 11, 2019 at 5:13 pm
This might give better performance, if the optimizer recognizes the chance:
WHERE AKey LIKE '[ABCDEGJ]%' AND (AKey LIKE 'AAA%' OR AKey LIKE 'BBB%' OR AKey LIKE 'CCC%' OR...
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".
October 10, 2019 at 5:36 pm
And if my suggestions are broken, are so bad, why do the recent DATE and DATETIME2(n) data types default to it?
Default to what exactly? We know they're not stored in...
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".
October 10, 2019 at 5:27 pm
I disagree; when at all possible, you want to make the clustered index unique yourself whenever possible. Yes, SQL will always force it to be unique anyway, but that often...
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".
October 10, 2019 at 3:21 pm
I'd say just use > CHAR(0) if you're going to go that route. Btw, wouldn't you have to use >= CHAR(9), just in case only a tab char was there?
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".
October 10, 2019 at 3:10 pm
And that's why I wrote the code the way I did. The other way is somewhat easier to code, but potentially far worse in performance.
The underlying rule is:
Never use a...
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".
October 9, 2019 at 10:17 pm
2) these display strings sort correctly in temporal order. They are not ambiguous.
As I stated, In the real world dates are sometimes given as YYYY-DD-MM for certain regions of the...
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".
October 9, 2019 at 10:07 pm
Actually, the format YYYYMMDD is perfectly acceptable under ISO. I don't know why Celko insists on repeating his false claim over and over about YYYY-MM-DD despite being told that it's...
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".
October 9, 2019 at 8:16 pm
You probably need something like this:
WHERE (AKey LIKE 'AAA%' OR AKey LIKE 'BBB%' OR AKey LIKE 'CCC%' OR AKey LIKE 'DDD%' OR
AKey LIKE...
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".
October 9, 2019 at 8:10 pm
Viewing 15 posts - 2,671 through 2,685 (of 7,619 total)