Viewing 15 posts - 3,976 through 3,990 (of 7,619 total)
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
I wouldn't worry about a one-time occurrence (although you should review whether or not you are on the latest SP/patch for that SQL instance).
If it happens again, then...
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 2, 2017 at 2:37 pm
Oops, sorry, I should have included that part.
SELECT *
FROM sys.indexes
WHERE index_id = 47
AND object_id = OBJECT_ID('_ACTIVITY_GEN')
Great, though, that it was index #47, 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".
February 2, 2017 at 2:23 pm
Identity the rows can be extremely difficult.
First check to see if it's a nonclus or clus index/heap. If the errors are in a nonclus index, simply drop and...
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 2, 2017 at 1:52 pm
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 2, 2017 at 8:55 am
What you would need to do is this:
SELECT Description
FROM (
SELECT Description, MIN(TransactionDate) AS TransactionDate
FROM MyTable
GROUP BY...
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 1, 2017 at 2:58 pm
Absolutely get rid of the LTRIM and RTRIM. Also, review all indexes on the tables, particularly making sure that you have the best clustered index on every (significant)...
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".
January 31, 2017 at 2:21 pm
A couple of things here:
1) The hours ranges given exclude times and allow times to overlap, which is almost certainly not what you really want.
2) Let's go...
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".
January 31, 2017 at 2:14 pm
All you need is year, since any birthday during the @service year won't count anyway:
DECLARE @Birth DATETIME = '11/30/1951'
,@Service DATETIME = '2016-08-26'
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".
January 30, 2017 at 2:14 pm
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".
January 26, 2017 at 2:27 pm
Viewing 15 posts - 3,976 through 3,990 (of 7,619 total)