Viewing 15 posts - 3,961 through 3,975 (of 7,613 total)
How is the table clustered? If it's clustered on the date, you'll be fine, just run one DELETE at a time for a decent-sized chunk of rows, 20000...
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".
February 16, 2017 at 4:38 pm
Yes, inner joins like that are still valid because they are still unambiguous and thus 100% logically valid. Equi-joins function exactly the same using WHERE joins...
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".
February 16, 2017 at 10:08 am
You could get an initial page split. But after that, SQL detects that incoming values are sequential and shouldn't do another page split unless you overlap other existing rows.
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".
February 15, 2017 at 3:44 pm
I don't think AM/PM is an issue really. Here's the code to calc the diff in minutes; I'll leave converting those minutes to hours:mins or hours.hours as I'm pressed for...
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".
February 14, 2017 at 10:12 am
I think the problem may be deeper than that. Remember, once you alias a table in a query, you cannot refer to that table by its original name, but only...
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".
February 13, 2017 at 10:07 am
You need to add ending quotes to the strings, and not specify a third parameter for the the CHARINDEX, like so:
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".
SELECT SUBSTRING('APP4005673452-45580', CHARINDEX('-','APP4005673452-45580') + 4, 4)
February 10, 2017 at 11:28 am
Since you're already using dynamic SQL, you can directly pass the value to the SQL command:
ALTER PROCEDURE spGetList
@ID INT
AS
--DECLARE @ID INT
DECLARE @Query...
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".
February 9, 2017 at 8:01 am
Would need to see the query plan to know for sure what is going on, but, based solely on your description, there's a limited amount you can do.
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".
February 8, 2017 at 12:56 pm
Be sure to cluster the temp table as well, on ( TransactionGroupGUID, b.TransactionID )
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".
February 8, 2017 at 12:15 pm
I agree, we need more details and a temp table would be much better. But you should always UPDATE an alias when doing JOIN(s) in an UPDATE. And, if possible,...
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".
February 7, 2017 at 4:37 pm
select top (1) id
from @test T
where VendorID = @VendorID and (CompanyId = @CompanyId Or CompanyId IS NULL)
order by companyid desc
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".
February 7, 2017 at 3:08 pm
Perhaps this?:
;
WITH my_junk AS (
SELECT CAST(my_column as varchar(7)) as my_column FROM (VALUES
('-1.23456'),('-1.2345'),('1.2345000'),('12.3456'),('-1.234'),('1.234'),('-12.345'),('12.345'),('-12.34'),('12.34'),('-1.23'),
('ab'),('x'),(''),('0'),
('1.23'),('-1.2'),('1.2'),('-12.3'),('12.3'),('-1'),('1'),('-12'),('12')
) d (my_column)
)
SELECT my_column, final_value
FROM my_junk
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".
February 7, 2017 at 11:21 am
SQL Server provides system views that greatly assist you here. You need to use those views, rather than just look at SQL code, to determine index(es) usage and missing index(es). 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".
February 6, 2017 at 2:47 pm
How about something like below? Since it involves renaming the table, naturally you may need to adjust the implementation details to avoid/lessen glitches in your specific environment:
1) Create...
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".
February 6, 2017 at 12:06 pm
The biggest performance factor overall is properly clustering the tables. Presumably you always query by client (if you have clients that see multiple clients, you might need another...
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".
February 3, 2017 at 3:17 pm
Viewing 15 posts - 3,961 through 3,975 (of 7,613 total)