Viewing 15 posts - 5,791 through 5,805 (of 7,619 total)
I recommend avoiding unnecessary string conversions/usage, and dependencies on language and/or other SQL settings.
Not sure what the h "N" means, but I still suggest this style of code instead:
WHERE DATEDIFF(DAY,...
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".
September 18, 2014 at 4:20 pm
Jeff Moden (9/18/2014)
ScottPletcher (9/18/2014)
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".
September 18, 2014 at 4:15 pm
CPU is typically not the bottleneck nowadays, I/O is. More RAM = bigger buffers = less I/O. Thus, RAM will typically be much more vital to performance that...
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".
September 18, 2014 at 1:52 pm
Or:
SELECT OBJECTPROPERTYEX ( OBJECT_ID('object_name') , 'IsView' ) AS IsViewOrNot
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".
September 18, 2014 at 1:39 pm
I believe my code above met your (implied) challenge ;-).
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".
September 18, 2014 at 10:13 am
Use a trigger on InvLoc to keep the total in PStat up to date. No one should be updating the total in PStat directly if they need to be...
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".
September 17, 2014 at 1:01 pm
Try this:
declare @t table (Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt)
set nocount on
insert @t select 10,2,6,0
insert @t select 10,2,6,1
insert @t select 40,2,8,1
insert @t select 40,2,8,0
set nocount off
SELECT
Qty, Flag, CF2 / CF1 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".
September 17, 2014 at 10:58 am
I'd change the collation first, since I think that will affect the compression. I believe the overall compression overhead, including I/O, would outweigh any advantages gained in I/O.
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".
September 17, 2014 at 10:13 am
But those conditions within the WHERE clause could convolute the query plan and thus the performance.
Much better to set variables and have a simple and consistent WHERE clause, allowing 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".
September 16, 2014 at 9:59 pm
DECLARE @start_date datetime
DECLARE @end_date datetime
IF @parameter = 'GT30'
BEGIN
SET @start_date = '19000101'
SET @end_date = GETDATE() - 30
END --IF
ELSE
IF @parameter = 'LT30'
BEGIN
...
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".
September 16, 2014 at 5:13 pm
I'm virtually certain you'll get an error if the linked server isn't there.
You could use dynamic SQL, which will only be compiled if/when it's run.
As to recompiles, SQL will 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".
September 16, 2014 at 4:49 pm
If there's always at least one decimal place:
SELECT
value AS original_value,
SUBSTRING(value, 1, CHARINDEX('.', value) + 1) + '%' AS new_value
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".
September 16, 2014 at 4:45 pm
The more disorganized the index, the more work space will be needed. Hopefully once you get the tables back in shape, the future runs won't be as bad.
Also, look...
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".
September 16, 2014 at 4:41 pm
PhilipC (9/16/2014)
ScottPletcher (9/16/2014)
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".
September 16, 2014 at 2:36 pm
ramana3327 (9/16/2014)
Hi,I can't provide the execution plan but the query is
select serialnumber from sampletable where id in (1,2,3)
CREATE NONCLUSTERED INDEX sampletable__IX_id ON sampletable ( id ) INCLUDE ( serialnumber...
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".
September 16, 2014 at 1:19 pm
Viewing 15 posts - 5,791 through 5,805 (of 7,619 total)