Viewing 15 posts - 196 through 210 (of 7,619 total)
If you are putting NOLOCK on an INSERT, UPDATE, or DELETE query, that's not going to do anything. SQL doesn't allow NOLOCK on data change operations as it doesn't...
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 21, 2024 at 9:58 pm
... but is using surrogate keys really that bad? ...
Surrogate keys are not necessarily bad, if chosen after proper analysis, but just automatically defaulting to using a surrogate...
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 21, 2024 at 1:41 pm
IF the most critical lookup on the table is the one you've posted, and since you're deleting rows frequently, I'd suggest you consider re-clustering the table to allow clus 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".
May 21, 2024 at 12:33 am
The ERD is nice, thanks for that.
But I think you need to step back and do the logical design first (designing using Entities and Attributes rather than tables and columns),...
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 20, 2024 at 6:35 pm
SELECT SUBSTRING(ID, ID_start_byte_2, 50) AS ID_you_wanted, ID AS original_ID
FROM dbo.table_name tn
CROSS APPLY ( SELECT CASE WHEN LEFT(ID, 1) = 'D' THEN 2 ELSE 1 END AS ID_start_btye_1 ) AS ca1
CROSS...
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 16, 2024 at 7:37 pm
n/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".
May 14, 2024 at 1:13 am
You don't actually have to drop both indexes. You would, for example, often drop one index and modify the other one.
I've started on code to do this process...
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 13, 2024 at 6:12 pm
For all indexes, or the 1 you tell it to generate (either by index number OR by index name).
As I said, I've been designing code to do my index checking...
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 13, 2024 at 2:54 pm
You don't actually have to drop both indexes. You would, for example, often drop one index and modify the other one.
I've started on code to do this process but have...
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 13, 2024 at 2:44 pm
Actually, partitioning came sometimes accidentally (or coincidentally) help performance. For example, the data was originally clustered by id, but to partition you cluster it first by a date. IF 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".
May 10, 2024 at 2:57 pm
Remove the second column from the first IN subquery:
SELECT minutes FROM dbo.timeaccountmovement WHERE ownertype = 1 AND timeaccountid = 8 AND ownerid IN
(
SELECT ownerid FROM dbo.timeaccountmovement WHERE timeaccountid = 73...
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 9, 2024 at 7:41 pm
Yes, I've used CDC. I ended up creating my own proc to view change data (basically pre-prepping the params for the MS proc) because the MS proc itself is so...
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".
April 25, 2024 at 5:17 pm
I see "emp." being referenced, but I don't see "emp" anywhere in the supplied query.
You would have to check each of the joined tables to see if there is an...
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".
April 24, 2024 at 8:01 pm
You don't really need to partition, assuming you re-cluster, although you could. What will happen is that you will partition on date, so you will have to add date 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".
April 22, 2024 at 1:26 pm
If avol is actually numeric. If it's varchar, could be an issue.
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".
April 21, 2024 at 5:49 pm
Viewing 15 posts - 196 through 210 (of 7,619 total)