Viewing 15 posts - 2,581 through 2,595 (of 7,613 total)
If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.
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".
December 12, 2019 at 3:30 pm
Most likely you've hit the "tipping point" for a table(s), where instead of using an index SQL reverts to a full table scan.
Rather than the full db size, we need...
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".
December 12, 2019 at 3:06 pm
I think it's worth a second here to go through why a SQL NOT IN with a NULL value in it causes no values to match.
SQL Rule 1) A WHERE...
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".
December 11, 2019 at 8:03 pm
Look at the history in msdb. If it's lengthy, clean some of it out. You would think SQL would use master to get the dbs in SSMS, but it also...
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".
December 11, 2019 at 3:49 pm
Let me know if this code looks good, and we can convert it into more dynamic code. I didn't want to spend time doing that until I had the core...
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".
December 10, 2019 at 10:11 pm
Yeah, I am ancient. I'd still pick SQLCMD or PowerShell over bcp though.
Heh... guess I'm more ancient. I've not found much that will actually beat BCP for performance...
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".
December 10, 2019 at 9:38 pm
Could you please post directly usable data, i.e. CREATE TABLE and INSERT statements for the example table data.
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".
December 10, 2019 at 8:54 pm
You also might have a NULL value for po_num in preqitem. That would prevent the NOT IN from yielding any results, so add a NOT NULL check. Or, perhaps even...
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".
December 10, 2019 at 7:26 pm
Are you exporting the data to load into a different SQL Server instance? If so, then you should "native" format on the output. That will save a lot of time...
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".
December 10, 2019 at 3:24 pm
What we ultimately ended up doing was creating a separate db to store user tables in. A view and/or a synonym can be used in other dbs to allow 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".
December 6, 2019 at 7:59 pm
I would expect that behavior honestly. I don't see how SQL could realistically maintain a potentially almost unlimited number of intermediate row counts for different snapshot points for every table...
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".
December 6, 2019 at 5:43 pm
Expect to have to do lots of tweaking to the code to handle all the things that come up, though.
NULL / NOT NULL for each column (which should...
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".
December 6, 2019 at 3:19 pm
Expect to have to do lots of tweaking to the code to handle all the things that come up, though.
NULL / NOT NULL for each column (which should always be...
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".
December 5, 2019 at 10:58 pm
A proc is possible, but most people don't code an actual CREATE TABLE statement, they use the gui. They would have to pass in a block of SQL, which means...
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".
December 5, 2019 at 9:15 pm
Probably the easiest work-around is likely a DDL trigger that prevents them from issuing any DROP TABLE (and ALTER TABLE on a table they didn't create, if you need that,...
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".
December 5, 2019 at 6:52 pm
Viewing 15 posts - 2,581 through 2,595 (of 7,613 total)