Viewing 15 posts - 4,666 through 4,680 (of 7,613 total)
Good point about partial matches. Presumably you'd want to match on the longest match (?!):
insert into #import (importcity, title)
select 'new_city_for_testing', 'rancho'
insert into #city (city)
select 'rancho'
update i
set importcity...
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".
November 9, 2015 at 2:22 pm
Yep. NULLs can invalidate NOT IN logic. Add a check to eliminate NULLs from the subquery:
SELECT COUNT(1)
FROM dbo.Token TD WITH ( NOLOCK )
WHERE ...
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".
November 9, 2015 at 11:38 am
This may give you some ideas. I used a placeholder of <your_tally_table> for the tally table. Replace that with your own tally table name, either a physical tally...
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".
November 9, 2015 at 11:35 am
In SQL Server, you'd need brackets around the name "Add":
Select l.[add], l.phne
from e_lkup l
where mins is null
GROUP BY phne, [add]
HAVING count(*) >1
EXCEPT
select l.[add], l.phne
from s_status ss
left outer...
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".
November 9, 2015 at 11:04 am
I don't think so. I use that style all the time to rename files (yes, I should use Windows directly, but I'm used to working from within SQL :)...
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".
November 6, 2015 at 9:54 am
Yeah, that drives you crazy until you figure it out, since SQL doesn't always clear out the job activity queue. You have to join to another control table to...
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".
November 6, 2015 at 9:41 am
SET @CMD='ren "\\pashare\shared\dhsr data\Adolescent Health\IMG\PHI_Refresh\export_file_IMG.xlsx" "export_File_IMG_' +@date+'.xlsx"'
(Assuming date is a char data type and thus doesn't need CAST to char).
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".
November 6, 2015 at 9:35 am
Can you give us the DDL for dbo.Cache and/or the query plan for:
UPDATEdbo.Cache
SETAvailability = Availability - 1,
LastUpdated = GETUTCDATE()
WHEREUnitId = @UnitId
ANDStartDate < DATEADD(DAY, @Duration, @StartDate)
AND@StartDate < DATEADD(DAY, Duration, StartDate)
ANDAvailability >...
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".
November 6, 2015 at 9:30 am
To help the performance of your procs, you need to make sure no implicit conversions occur on table columns that are being compared against (WHERE column_name ...) or JOINed (INNER...
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".
November 5, 2015 at 2:42 pm
Just as an FYI, you can use a CROSS APPLY in these types of situations to assign meaningful alias names to intermediate values, which can sometimes make the SELECT logic...
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".
November 5, 2015 at 2:37 pm
IF that's a highly selective condition -- i.e., there are not many rows where that column has between 4 and 10 bytes -- then you might gain performance by adding...
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".
November 5, 2015 at 2:10 pm
I would certainly rebuild that table, if it's used a lot or you have some other cause of concern over it. As always for best performance, first determine 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".
November 5, 2015 at 10:14 am
To tune for performance, you first need to review missing index stats and index usage stats (and index operational stats, if you can). The main improvement is to determine...
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".
November 5, 2015 at 10:12 am
What you're doing is very dangerous code inside a trigger, as it's likely to run quite a while.
That said, you can only reference the "inserted" from within the trigger itself,...
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".
November 4, 2015 at 5:46 pm
Is this homework or similar? If so, we can only give you general guidelines, not fully completed SQL.
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".
November 4, 2015 at 5:34 pm
Viewing 15 posts - 4,666 through 4,680 (of 7,613 total)