Viewing 15 posts - 5,386 through 5,400 (of 7,613 total)
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
New Born DBA (2/4/2015)
ScottPletcher (2/4/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 4, 2015 at 1:16 pm
As a DBA, I dislike doing thousands of logical I/Os -- such as a calendar table -- when instead a simple mathematical calcs can yield the same results:
DECLARE @num_years int
SET...
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 4, 2015 at 1:04 pm
The key thing for performance is the clustered index. Yes, you can instead create gazillions of nonclustered covering indexes, but that actually doesn't help overall performance much, and could...
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 4, 2015 at 12:49 pm
New Born DBA (2/4/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 4, 2015 at 11:53 am
The row length excluding the Branch column is ~64 bytes:
8 for bigint
40 for nchar(20)
4 for var length ptr area
1 for...
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 4, 2015 at 10:00 am
The separate table is really the only acceptable method for that type of data, since it could see many modifications in the future. For example, you could add types,...
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 3, 2015 at 3:53 pm
Viewing 15 posts - 5,386 through 5,400 (of 7,613 total)