Viewing 15 posts - 1,201 through 1,215 (of 7,619 total)
Your code also has zeros just disappear completely from your results. It's never explicitly stated that zeros can't appear in these numbers. Just in case they do, I...
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 25, 2021 at 5:16 pm
ScottPletcher wrote:Is that code fundamentally different than the first query I posted using the same method?
No temp table
No Cartesian product without row goal, i.e. SELECT TOP(n)
No LEFT JOIN
No ISNULL
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 25, 2021 at 4:01 pm
Try different clustering for the table. If that doesn't help enough by itself, then you can also look into partitioning the table.
The key thing for determining the best clustered 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".
October 25, 2021 at 3:25 pm
maybe something like this
select ic.NumericValue,
stuff((select '' + v.repl
...
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 25, 2021 at 3:09 pm
IF OBJECT_ID('tempdb.dbo.#translations') IS NOT NULL
DROP TABLE #translations
CREATE TABLE #translations (
from_char char(1) NOT NULL PRIMARY KEY,
to_char...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 22, 2021 at 3:27 pm
Yep, doing this yourself will be very complex.
I would let SQL itself determine the type(s) in your situation. This will involve an extra full load of the data. Make every...
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 21, 2021 at 7:04 pm
I second the "nonwork_days" table, but I would make sure to design the reason code table well.
You would want to make sure that the reason code table has 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 21, 2021 at 4:26 pm
I very strongly prefer a separate "nonwork_days" table. The table needs nothing only the date and a tinyint reason code for why that date is a nonwork day.
The big problem...
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 21, 2021 at 3:29 pm
Partitioning often serendipitously helps with performance. That's because people will create a different clustered index to help with the partitioning process and that new clustered index actually does help 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 19, 2021 at 4:26 pm
Again, how do you search the table? That is, what are the common WHERE clauses that you use on the table? That is what should control the clustering.
If you (almost)...
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 18, 2021 at 9:08 pm
How do you search the table? It's likely you would get better performance using partitioning based on how you search the table, esp. if you can partition / cluster first...
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 18, 2021 at 8:53 pm
My recommendation would be to read the documentation for BULK INSERT, which is located at the following URL:
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15
The two keywords to search for in that article are ERRORFILE...
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 16, 2021 at 7:42 am
A trigger is the perfect approach for this. Some developers just irrationally avoid triggers no matter what.
Be sure to specify option FIRE_TRIGGERS in your BULK INSERT...
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 16, 2021 at 7:38 am
A trigger is the perfect approach for this. Some developers just irrationally avoid triggers no matter what.
Be sure to specify option FIRE_TRIGGERS in your BULK INSERT command.
The duplicate...
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 16, 2021 at 12:48 am
The SET applies to all SELECTs.
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, 2021 at 7:43 pm
Viewing 15 posts - 1,201 through 1,215 (of 7,619 total)