Viewing 15 posts - 2,146 through 2,160 (of 7,613 total)
We have job which is configured to recycle the sql error log on daily basis
Are you using the standard sp_cycle_agent_errorlog proc or did you try something home grown? ...
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".
November 17, 2020 at 5:07 pm
It's not worth that risk for such a tiny gain.
In a single execution, the gain may be small, bring it up to 100K/Sec and that difference can...
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".
November 17, 2020 at 5:03 pm
Just a quick thought: Any IF statement will add an evaluation step to the execution Any AND will do the same Conditionals (IFs) introduce branching, even if there is...
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".
November 17, 2020 at 3:27 pm
I used a table for the location_id(s) to be combined, so that it would be easy to do multiple combinings. For example, if you uncomment the (1, 30), you'll see...
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".
November 17, 2020 at 3:21 pm
In this specific case, I'd recommend you add a couple of triggers to the table to maintain an accurate flag for this in the parent row.
You'd need an INSERT trigger...
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".
November 16, 2020 at 8:50 pm
I agree, the first method is unnecessary.
As to whether I put all on one line: if they are straightforward, I would code them in one line with ANDs. If one...
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".
November 16, 2020 at 6:13 pm
I really hope this isn't a dumb question. Is there a reason other then style or preference as to why you'd do this:
IF @Var1 =...
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".
November 16, 2020 at 6:10 pm
Unfortunately, we have a bit of a standard, which is that tables called "Thing" have a clustered index on ThingID, which will be an int, identity column. That's because...
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".
November 16, 2020 at 4:58 pm
The obvious difference is the number of rows read from the T_DeliveryTimetableItemResolvedCost table, which goes from 20k to 21 million (and a seek to a scan, probably unsurprisingly). 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".
November 16, 2020 at 3:42 pm
>>Use INSERT instead of MERGE in the second statement.
How would I then handle duplicates? I don't want to insert a duplicate record if there already is one that matches...
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".
November 16, 2020 at 3:35 pm
Since you only want monthly totals, I don't see any reason for the overhead to create a row for every single day; instead, you really only need a row for...
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".
November 16, 2020 at 3:24 pm
The SERIALIZABLE would cause higher levels of locks and cause them to be held longer.
Use INSERT instead of MERGE in the second statement.
Personally I'd use an UPDATE then INSERT on...
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".
November 16, 2020 at 2:44 pm
I guess the sequence must be called so many times that you're running out of values for an int.
Maybe use a bigint for the sequence?
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".
November 16, 2020 at 2:40 pm
I can't test these since you didn't provide directly usable test data:
--if you need only the acct_ids
SELECT acct_id, COUNT(*) OVER() AS total_accts
FROM my_table
GROUP BY acct_id
HAVING MIN(street +...
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".
November 13, 2020 at 2:32 pm
As noted by Brian, your table design is completely wrong. You've then had to corrupt the data to fit it into the bad table structure.
For example, the language is "English". ...
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".
November 13, 2020 at 2:11 am
Viewing 15 posts - 2,146 through 2,160 (of 7,613 total)