Viewing 15 posts - 5,386 through 5,400 (of 7,619 total)
Luis Cazares (2/6/2015)
Scott,I mainly agree with you, but the OP stated that there's always one Payroll_Year at a time. Based on that, I wouldn't include it in the index.
True, I...
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, 2015 at 12:38 pm
jdbrown239 (2/6/2015)
I have 15 GB allocated space and 98% free
In the log file?
Yeah, that should be plenty, so the statement should run as fast it can.
Are there triggers, replication or...
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, 2015 at 10:28 am
I'd cluster the table by:
Payroll_Year, PayPeriod, EmployeeID and, optionally, CompanyID
That way the clustering key is generally increasing, which will reduce fragmentation. That's not the main criterion for a clus...
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, 2015 at 9:32 am
That statement is going to have the scan the table regardless of what you do.
The critical thing for performance is to pre-allocate (and thus pre-format) enough log space for 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".
February 6, 2015 at 9:23 am
Yes. Why risk not recompiling it?
Get the query plan id and remove all plans related to this proc from the cache using:
DBCC FREEPROCCACHE { plan_handle }
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 5, 2015 at 2:23 pm
JJR333 (2/5/2015)
The first NOT clause makes all the difference.
Is this T-SQL logic different from say Excel VBA logic?
SUM the turnover of all stores NOT...
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 5, 2015 at 1:07 pm
You can put the SELECTs directly in the ISNULL, you don't have to use separate queries:
SELECT ISNULL((select H from table_1 where a = 'X' and b = 'Y'),
...
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 5, 2015 at 1:02 pm
Move the specific nbr_ky to the inner query to limit the rows that have to be read there:
CROSS APPLY (
SELECT TOP (1)
stock_nbr_ky as priorRecord_stock_nbr_ky,
stock_nbr,
item_desc,
unit_price,
ui_conversion_factor,
lin_tamcn_ky,
reportable_commodity_type_ky,
security_commodity_type_ky,
stocking_unit_of_issue_cd,
stores_account_cd,
stock_item_cd,
federal_supply_class_cd,
history_record_generated_dt_tm,
FROM his.cat_stock_nbr priorRecord
WHERE priorRecord.stock_nbr_ky = stockNew.stock_nbr_ky
AND priorRecord.cat_stock_nbr_his_ky...
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 5, 2015 at 12:56 pm
JJR333 (2/5/2015)
ScottPletcher (2/5/2015)
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 5, 2015 at 12:37 pm
JJR333 (2/5/2015)
I can't get my head around it though. 🙂
For one location (@IDHuis = 'WH')
I need to exclude the Hrs from the sum of one department (D.AfdelingZPT...
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 5, 2015 at 11:59 am
For efficiency, you might want to specify RECOMPILE on the proc and only test the D.AfdelingZPT column if you have to:
AND (@IDHuis, '' <> 'WH' OR...
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 5, 2015 at 11:25 am
There are steps you need, in order:
1) drop the nonclustered index
2) drop the existing clustered index
3) create the new clustered index
4) add the new nonclustered index, if any.
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 5, 2015 at 11:19 am
If you are having performance issues, you need to review the indexes themselves too, in particular verifying that you have the best clustered index on each table (hint: it's usually...
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 5, 2015 at 11:17 am
Jeff Moden (2/5/2015)
ScottPletcher (2/5/2015)
Jeff Moden (2/4/2015)
RamSteve (2/4/2015)
That's one solution but it relies on @@DATEFIRST which is subject to change based on selected language and other...
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 5, 2015 at 10:10 am
Jeff Moden (2/4/2015)
RamSteve (2/4/2015)
That's one solution but it relies on @@DATEFIRST which is subject to change based on selected language and other things. You...
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 5, 2015 at 9:04 am
Viewing 15 posts - 5,386 through 5,400 (of 7,619 total)