Viewing 15 posts - 6,301 through 6,315 (of 7,619 total)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category...
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".
December 18, 2013 at 3:48 pm
Rewriting the query and/or adding nonclustered indexes won't help, unless you basically rewrite the entire table by including a gazillion in the nonclus index(es) -- and constantly maintain it as...
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".
December 17, 2013 at 1:42 pm
I think you need a GROUP BY rather than a DISTINCT.
Is the code below closer/close to the final output you want?
SELECT
e.ID,e.FirstName,e.Area
,s.ID,s.Qty,s.ProductName, s.Sale
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".
December 16, 2013 at 10:52 am
Sean Lange (12/16/2013)
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".
December 16, 2013 at 10:36 am
kapil_kk (12/14/2013)
ScottPletcher (12/13/2013)
1) Capture and save the existing index missing/usage stats for that table immediately, before any hanges are made.
2) Run...
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".
December 14, 2013 at 2:44 pm
I suggest making index changes scripted below, following these steps:
1) Capture and save the existing index missing/usage stats for that table immediately, before any hanges are made.
2) Run the code...
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".
December 13, 2013 at 10:35 am
1) Maybe you can combine the three separate UPDATEs into one, as shown below, avoiding repeated joins of the same tables.
2) Depending on the row counts, you might consider creating...
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".
December 12, 2013 at 2:04 pm
You could also consider something like below, particularly since presumably you are on Enterprise Edition and thus can recreate all the non-clustered indexes ONLINE:
Before the shrink:
1) verify you have current...
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".
December 10, 2013 at 4:19 pm
Try changing:
!= ''
to
IS NOT NULL.
The variables should contain NULL, not be empty, if no row is found.
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".
December 10, 2013 at 4:11 pm
For performance reasons, it would almost certainly be better to have a trigger on the Calculations table that automatically identified and saved all variables used in a normalized table whenever...
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".
December 10, 2013 at 11:09 am
Maybe something like this?:
declare @replace1 varchar(max)
declare @replace2 varchar(max)
select @replace1 = (
select 'REPLACE('
from #CalculationVariables
for xml path('')
...
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".
December 10, 2013 at 11:00 am
FYI: the wheel's been re-invented countless times, or we'd all be riding around on wooden wheels with no rims!
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".
December 9, 2013 at 4:23 pm
SELECT RTRIM(LEFT(CustomerName,CHARINDEX('*',CustomerName + '*')-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".
December 9, 2013 at 4:16 pm
As long as you have fixed leading values, i.e. 'value1%' and not '%value1%',
an index on myColumn should handle the query fine. If it's a large % of the...
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".
December 9, 2013 at 4:09 pm
If you're using Enterprise Edition, just use Change Data Capture and let SQL do all the hard work for you.
If not, the overhead of what you want to do is...
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".
December 9, 2013 at 4:06 pm
Viewing 15 posts - 6,301 through 6,315 (of 7,619 total)