Viewing 15 posts - 2,731 through 2,745 (of 7,619 total)
Again, very bad idea trying to combine all three actions, even for one table, let alone for all tables.
It's easy enough to generate individual procs. If you wanted to, you...
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".
August 26, 2019 at 10:36 pm
Typically one would use a bit value setting to reduce space usage. For example:
languages int NOT NULL
You'd want a separate table with the language code values:
CREATE TABLE #language_codes ( language_bit...
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".
August 26, 2019 at 6:22 pm
I suppose you could force that to work, but it's a bad idea. Individual tables have vastly different columns and requirements.
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".
August 26, 2019 at 6:13 pm
The major problem with your first query is that it doesn't satisfy the original conditions to: select all rows if the value is NULL. Other than dynamic SQL, the scan...
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".
August 22, 2019 at 6:41 pm
Of course bad triggers can be written. Bad stored procedures can also be written, but that doesn't mean we should quit writing stored procedures (agreed?). Yes, take great care 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".
August 22, 2019 at 3:27 pm
Don't use functions in a WHERE clause if you can avoid it, as they destroy "sargability".
In this case, just directly check each variable for NULL or a match, as below. ...
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".
August 22, 2019 at 3:18 pm
An AFTER UPDATE trigger on the table would capture who, at least as well as you can within SQL Server. You can use "UPDATE(column_name)" to limit it to only processing...
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".
August 21, 2019 at 7:33 pm
If the AccountNumber must be changed, it will have to change whether it's the clustering key, and/or a PK, or not.
In that situation, you'd temporarily disable FK checking as needed...
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".
August 20, 2019 at 4:37 pm
You'd need to post the actual code that processes the cursor for us to really accurately help.
But, likely an extra FETCH is being issued. People insist on coding multiple FETCH...
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".
August 19, 2019 at 8:17 pm
No, ID is absolutely not needed. It's likely to hinder processing since people tend to inevitably cluster on it.
Use the AccountNumber as the clustering key and the primary key. You...
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".
August 19, 2019 at 8:14 pm
To avoid any IF / CASE logic, I usually use this technique instead:
SIGN(Jan) + SIGN(Feb) + ...
In situations where the numbers could ever be negative, you'd need to include ABS()...
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".
August 19, 2019 at 5:21 pm
I would think so, since SQL doesn't (re)order / sort rows going into a columnstore, it loads them in the same order in which they arrive.
Thus, presumably the first 1,048,576...
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".
August 16, 2019 at 8:09 pm
Yeah, I was incorrectly thinking of column3 also matching.
I don't like having to have duplicative indexes. I'd probably go with just:
( column2, datetime ) INCLUDE ( column3 )
Sure, that index...
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".
August 16, 2019 at 7:18 pm
I would of that the best index would be:
(Column3, Column2, DateTime)
given the WHERE condition.
Particularly if you later use the same query with:
Where tab1.Column3= 9
to query a different column.
And I'd move...
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".
August 16, 2019 at 5:38 pm
Since an IN gets converted to ... OR ... OR ..., I suppose it might make a difference, in which case you'd want to put the most common values first.
You...
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".
August 15, 2019 at 8:22 pm
Viewing 15 posts - 2,731 through 2,745 (of 7,619 total)