Viewing 15 posts - 6,571 through 6,585 (of 7,619 total)
I think this will do it; performance will depend on the indexes available on the table:
select sales.*
from (
select distinct TerritoryID
from [Sales].[SalesOrderHeader]
)...
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".
May 2, 2013 at 12:32 pm
GilaMonster (4/30/2013)
ScottPletcher (4/30/2013)
When rebuilding a nonclustered index offline, SQL has to fully scan the clustered index.
No it doesn't. That would be an inefficient way of running a rebuild. Both online...
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".
April 30, 2013 at 1:18 pm
GilaMonster (4/28/2013)
2) Up to you. Depends on your requirements...
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".
April 30, 2013 at 10:16 am
That could find the wrong entry and/or miss the entry if it was an index on a view instead of a table. Therefore, the code below is more robust:
IF...
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".
April 30, 2013 at 10:03 am
I'd use a CASE expression so you can guarantee the order of execution; you don't need a CTE for that.
SELECT OBSVALUE
FROM OBS
WHERE 1 = CASE
WHEN...
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".
April 26, 2013 at 4:29 pm
Sorry:
SELECT
[values].value,
SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,
SUM(CASE WHEN Q2 = [values].value THEN 1 ELSE 0 END) AS Q2,
SUM(CASE WHEN Q3 = [values].value THEN 1 ELSE...
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".
April 25, 2013 at 12:30 pm
SQL_Enthusiast (4/25/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".
April 25, 2013 at 11:12 am
GilaMonster (4/25/2013)
Abu Dina (4/25/2013)
I bet it's the table variables. They can't have indexes on them nor stats maintained for the data.
Table variables don't have stats, but they certainly can have...
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".
April 25, 2013 at 11:10 am
There's no inherent reason the proc should perform so badly.
Instead of table variables, use temp tables and index the temp tables appropriately.
I suspect the time can be reduced significantly 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".
April 25, 2013 at 10:38 am
You may gain some performance by avoiding counting all the rows in inserted and deleted:
ALTER TRIGGER dbo.SystemInfoTrg
ON dbo.SystemInfo
AFTER INSERT, DELETE, UPDATE
AS
SET NOCOUNT ON;
DECLARE...
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".
April 25, 2013 at 10:35 am
I think something like this should at least be close:
SELECT
[values].value,
SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,
...
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".
April 25, 2013 at 10:29 am
Just in case, IF you already have an index on ( YearValue, MonthValue ), then this should be extremely fast:
SELECT
MAX(YearValue) AS YearValue, MAX(MonthValue) AS MonthValue...
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".
April 24, 2013 at 5:01 pm
Sorry, forgot I ended up using a database-specific function in the code (was trying to avoid it). This approach should be safer overall anyway:
EXEC sp_MSforeachdb N'
IF ''?'' IN (''master'',...
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".
April 24, 2013 at 9:00 am
SQL Server does not have an equivalent to that function or functionality :-(.
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".
April 23, 2013 at 4:24 pm
Yeah, that's the official story. But I don't 100% buy it.
With that many different fragments, if I were you, I'd run contig.exe on that(those) file(s) anyway.
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".
April 23, 2013 at 4:22 pm
Viewing 15 posts - 6,571 through 6,585 (of 7,619 total)