Viewing 15 posts - 5,176 through 5,190 (of 7,613 total)
I used CASE rather than ORs ;-).
SET NOCOUNT ON
--Temp table DROP/CREATE, in alpha order--------------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#CheckTables') IS NOT NULL DROP TABLE #CheckTables
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #CheckTables...
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, 2015 at 3:31 pm
If the code ran in just 8 minutes, and you like it and results, it's logical for you to leave it as 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".
April 21, 2015 at 9:51 am
I agree. You'd expect the specifically-chosen (?) "featured script" to be something at least moderately good/useful, but most often they are not.
As for this code, I would suggest simply:
SELECT...
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, 2015 at 8:27 am
Alan.B (4/17/2015)
What's wrong with this?
SELECT
DbName = db.name,
FileType =...
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 20, 2015 at 3:19 pm
There could also be some type of locking, and thus blocking, on the underlying system tables. I've seen this happen when, for example, people do a "SELECT ... INTO...
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 20, 2015 at 3:14 pm
Since you're trying to do a keyed lookup on the CL table, the conversion should be other way around to allow SQL to do a seek on all three key...
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 20, 2015 at 3:11 pm
I see a couple of other issues, besides formatting, with your code.
1) Step 0 is a summary for the entire job, including duration. So if you add step 0...
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 20, 2015 at 2:57 pm
there is a pre-set default fillfactor, but I've seen it set as low as 70%, it's not automatically 100%.
RonKyle (4/20/2015) What does this mean? The default is 0 (100%)....
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 20, 2015 at 1:08 pm
RonKyle (4/20/2015)
...Be careful of fillfactor, else it is possible that your table ends up taking up more space, unless that is what is expected.
If your choice of a 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".
April 20, 2015 at 9:39 am
Doesn't have to be an iTVF, you can still use dynamic SQL, just process all columns in a given table in one statement, not multiple.
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 20, 2015 at 7:34 am
dwain.c (4/19/2015)
ScottPletcher (4/17/2015)
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 20, 2015 at 7:31 am
Cursoring through the tables is fine, but you should process all columns in a single table in one pass (scan), not a separate pass for each column.
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 17, 2015 at 9:36 am
I don't understand how this code can work.
Your subqueries to tt1 / t2 don't contain any column named "Id_1" yet you reference that column when you join to t2: 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".
April 17, 2015 at 9:33 am
First, I don't see that code as turning on any setting. It sets a local variable value, that's it. You happened to name that local variable "[@]IMPLICIT_TRANSACTIONS", but...
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 17, 2015 at 7:24 am
The on-going maintenance is higher for an INSTEAD OF trigger, since every column addition or deletion, and some column changes, all requires changes the trigger, which is not the case...
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 17, 2015 at 6:48 am
Viewing 15 posts - 5,176 through 5,190 (of 7,613 total)