Viewing 15 posts - 5,686 through 5,700 (of 7,613 total)
If the controlling column(s) are all from the same table, I suggest a computed column. That leaves the defining logic in only a single place, and allows the calc'd...
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 20, 2014 at 4:23 pm
CELKO (10/20/2014)
..SEQUENCEs can "lose" numbers as well: if the transaction rolls back, the acquired sequence number(s) cannot be put back in the pool. If, by law, you can't 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".
October 20, 2014 at 10:17 am
I prefer EXISTS over NOT IN especially, since NULL values will prevent NOT IN from working. You might see if that also corrects the optimizer issue.
select *
from table_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 17, 2014 at 5:04 pm
Yes, all modifications to recoverable resources for a single transaction must either all fail or all work, never some and not others. This is fundamental to how SQL Server...
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 17, 2014 at 5:00 pm
With that many rows, you'll probably want to consider other, more efficient methods of comparing tables.
For example, change tracking would allow you to determine the rows in each table 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".
October 17, 2014 at 4:50 pm
SELECTc.cust_fullname AS Name,
c.cust_membership_id AS Account,
t.c_amount AS Amount,
'CH' + CAST(t.i_ticket_id AS varchar(12))...
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 17, 2014 at 4:30 pm
In general, it is a good idea to create reference/lookup tables instead of using case statements for gains in performance?
In general, for a limited number of values (say 10 or...
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 17, 2014 at 4:09 pm
My code to calculate max row length for a table -- and an index isn't that different -- shows 30 bytes, assuming 4 bytes for the clustering 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".
October 17, 2014 at 4:05 pm
The index you need for that DELETE would be keyed on:
( pk_Source, pk_cession )
If you only ever do this for pk_source = 2 (and not any other source numbers), 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".
October 17, 2014 at 10:48 am
Try fso. It's reasonably fast if you don't go too crazy on the number of files. I left out the error checking, you can fill that in if...
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 17, 2014 at 10:32 am
Since the table has 1.5B rows, the ~860K rows accessed is only ~0.06%, so I can see why SQL chose to do (a lot of) SEEKs rather than a scan....
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, 2014 at 3:25 pm
Probably the easiest way to do that is to create views which limit the data by date or whatever, then give them access to only the views. But 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".
October 16, 2014 at 2:35 pm
inevercheckthis2002 (10/16/2014)
So I take the same code and try to create a stored procedure by adding this:
IF OBJECT_ID('usp_DataLoadTime', 'P') IS NOT NULL
DROP PROC usp_DataLoadTime
GO
CREATE PROC usp_DataLoadTime
AS
BEGIN
... <code from...
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, 2014 at 2:32 pm
The single biggest factor for performance is to get the best clustered index on every table.
Then adjust the non-clustered indexes as required.
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, 2014 at 2:14 pm
Alexander Suprun (10/16/2014)
ScottPletcher (10/16/2014)
Alexander Suprun (10/16/2014)
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, 2014 at 2:12 pm
Viewing 15 posts - 5,686 through 5,700 (of 7,613 total)