Viewing 15 posts - 4,666 through 4,680 (of 7,619 total)
Hmm. Typically one of the main things to address deadlocking is adjusting indexes. And the first priority in tuning is general is always to get the best clustered...
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 11, 2015 at 9:20 am
There are certain changes that could make recreating the table more efficient than just the ALTERs. Would need details of the specific change(s) to know for sure. It's...
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 11, 2015 at 8:52 am
How many unique UnitIds are there? It looks as if the clustering key on the table should be ( UnitId, StartDate, Id ) [Id is optional, just to make...
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 11, 2015 at 8:49 am
Sergiy (11/10/2015)
David Rich (11/9/2015)
Type conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255)....
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 10, 2015 at 1:43 pm
David Rich (11/9/2015)
Type conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255). 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".
November 10, 2015 at 11:42 am
Yet another version, as an in-line table-valued function, for easy use within a query:
CREATE FUNCTION dbo.fn_first_day_and_last_day_of_week
(
@year smallint,
@week tinyint
)
RETURNS...
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 10, 2015 at 9:31 am
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
Viewing 15 posts - 4,666 through 4,680 (of 7,619 total)