Viewing 15 posts - 5,011 through 5,025 (of 7,613 total)
I prefer this approach [Edit: Because of its clarity, and to avoid using functions in the WHERE clause, which should be avoided whenever (reasonably) possible]:
WHERE
(requirementGradYear...
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".
June 25, 2015 at 12:24 pm
GilaMonster (6/25/2015)
Nolock hints, do you know what they do? (hint, they don't make queries faster)
Why does everyone keep repeating that? Of course NOLOCK hints make queries run faster, even...
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".
June 25, 2015 at 12:21 pm
Select [C1] ,
[C2] ,
DATEADD(S, C3, '1970-01-01 00:00:00') AS [C3] ,
[C4] ,
[C5] ,
[C6] ,
[C7] ,
[C8] ,
[C112],
[C675001003] ,
[C812000101] ,
[C875000000] ,
[C875000001],
[C875000002] ,
[C875000003] ,
[C875000004] ,
[C875156000] ,
[C875156001] ,
[C875156002] ,
[C875156005] ,
[C875156006] ,
[C875156007] ,
[C875156003] ,
[C875156004]...
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".
June 23, 2015 at 12:30 pm
drew.allen (6/19/2015)
By default, SQL Server joins tables in left-to-right order,
I'm not sure it's safe to make that blanket statement. I think SQL will join in whatever order it deems...
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".
June 19, 2015 at 2:54 pm
Is the owning account a single user account? Or did it get changed into a group account or role?
When you use "EXECUTE AS OWNER" the owner cannot be a...
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".
June 19, 2015 at 2:51 pm
UPDATE Sales
SET Sales.price = P.price
FROM Sales
CROSS APPLY (
SELECT TOP (1) price
FROM Price
WHERE Sales.itemId = Price.itemId AND
...
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".
June 18, 2015 at 3:42 pm
That trigger is way too much overhead. The trigger itself cannot be dynamic. Instead, dynamic code should be used to generate a static trigger, which will need regenerated...
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".
June 18, 2015 at 3:07 pm
I created minimum sample data, which I've included after the main code. I assumed you have a table that has a list of IDs -- if not, just uncomment...
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".
June 18, 2015 at 3:03 pm
Please try this and see if it's right, or at least very close :-D. Btw, please add appropriate table aliases to all columns (scope_id, firstName (presumably CUS but 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".
June 18, 2015 at 1:39 pm
With this method, the web login/user can use only procs that you're explicitly given the web app authority to execute. I don't know how to lessen the risk. ...
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".
June 18, 2015 at 9:26 am
You could try this:
1) create a separate "power user" that has ddladmin authority in that db
2) create a stored proc that runs under the power user account (EXEC AS 'power_user')...
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".
June 17, 2015 at 2:31 pm
For best performance, get rid of all unnecessary variables in functions.
Edit: Changed COUNT to COUNT_BIG based on return data type.
CREATE FUNCTION dbo.spGet_Rec_Count
(
@source_tbl varchar(100)
)
RETURNS bigint
AS
BEGIN
RETURN (
...
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".
June 17, 2015 at 12:01 pm
I'd put it all in the JOIN clause, since that's what those conditions effectively are:
DELETE x
FROM TableX x
INNER JOIN TableY y ON (x.Id = y.Id) AND
...
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".
June 17, 2015 at 11:57 am
Sure. 8 works for 7 days, and 1 should work for 2 days. The weekend adjustment is the same.
SELECT *
FROM Test
WHERE DATEDIFF(DAY, DischargeDate, ApptDate) <= ( 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".
June 17, 2015 at 9:38 am
Original code, for easy reference:
SELECT *
FROM Test
WHERE DATEDIFF(DAY, DischargeDate, ApptDate) <= ( 8 +
CASE DATEDIFF(DAY, 0, DischargeDate) % 7
...
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".
June 17, 2015 at 9:23 am
Viewing 15 posts - 5,011 through 5,025 (of 7,613 total)