Viewing 15 posts - 6,091 through 6,105 (of 7,613 total)
Jeff Moden (5/7/2014)
On the SORT IN TEMPDB thing... It'll only affect TEMPDB by 10 or 20% of what the largest index is.
I don't understand the rationale for that statement....
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, 2014 at 2:00 pm
Sean Lange (5/9/2014)
scotsditch (5/8/2014)
I want to create a computed Column in 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 9, 2014 at 12:46 pm
You can use a GLOBAL cursor and use dynamic SQL just to declare the cursor. You can process it in static code, including deallocating the cursor at the end.
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 8, 2014 at 4:04 pm
T-SQL and SQL certainly are different. I, therefore, would never assume someone knew TSQL just because they wrote SQL on a resume.
TSQL to me would mean the person understood...
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 8, 2014 at 4:00 pm
By default, yes, they will be stored in the same page.
Someone can, however, override SQL's default behavior, table by table, via "EXEC sp_tableoption" and force SQL to store all LOBs...
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 7, 2014 at 11:34 am
rwitt 95744 (5/7/2014)
DECLARE @DELETEDFROM_<tablename> bit = 1
IF (SELECT IDENT_CURRENT('<tablename>') = 1...
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 7, 2014 at 9:54 am
Jeff Moden (5/6/2014)
KoldCoffee (5/6/2014)
OK. I'm interested.
In that case, here we go… let’s test the “Original” code, Scott’s code, and my humble submittal. With any luck at all, someone will...
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 7, 2014 at 8:30 am
I use CONTEXT_INFO() to control triggers in those cases. If you disable the trigger, of course other deletes also won't process the trigger, when you might have wanted them...
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 6, 2014 at 2:38 pm
No, don't think any other settings affect that.
Is there maybe something in the process where that table is truncating rather than deleting? Perhaps a missing foreign key constraint causing...
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 6, 2014 at 2:35 pm
No reason for the overhead of reading a table just for that. Or to calculate, say, the first Tuesday or last Friday, etc., of a month. Calendar tables...
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 6, 2014 at 8:27 am
I would never use a table variable for this -- if something goes wrong after the DELETEs, but before the output has been processed, the data is gone, with no...
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 5, 2014 at 3:47 pm
I suggest making your code as self-documenting as possible; then you need fewer additional comments. Naturally that means avoiding variable "names" like "E", "N" and so on. Example...
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 5, 2014 at 3:23 pm
The description is vague. Is "new environment" a different server? Is it a different edition of SQL? Are the db files on SAN?
If, for example, the dbs...
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 5, 2014 at 10:53 am
As others have noted, never create indexes just because dta recommends them.
More importantly, though, the single most important performance factor is first getting the best clustered index. (Barring some...
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 5, 2014 at 10:43 am
SELECT TOP (1000) --remove after testing!
t.OPENDATE, t.ASSNDATE,
CAST(minutes_diff / 60 AS varchar(3)) + ':' +
...
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 5, 2014 at 9:55 am
Viewing 15 posts - 6,091 through 6,105 (of 7,613 total)