Viewing 15 posts - 4,336 through 4,350 (of 7,619 total)
The simplest rules is:
Never use ISNULL in a WHERE or JOIN clause.
Whether the column is nullable or not is irrelevant.
The proper way to code this:
where isnull(SalesOrderID,-1) = 43665 --NEVER!!
is this:
where...
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 15, 2016 at 10:40 am
Triggers should always be written for best efficiency first, even if it makes them harder to understand, especially so in your situation, with such heavy usage.
To keep each trigger 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".
April 14, 2016 at 10:29 am
If you just want the static result of a function call to be inserted to the table, use INSERT INTO ... SELECT rather than INSET INTO ... VALUES:
INSERT INTO tblDashboard...
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 13, 2016 at 3:58 pm
No, I don't know what would have added those indexes. But I also don't see the relevance of that. Wouldn't normal index analysis -- of missing index stats,...
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 12, 2016 at 1:02 pm
I think it's a bad idea to assume the existing physical design is correct (unless you know that it was modeled very carefully logically first and then the physical design...
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 11, 2016 at 9:10 am
Or, since we're dealing with JOINs:
What is the difference between INNER JOIN and LEFT OUTER JOIN?
What is the difference between LEFT OUTER JOIN and RIGHT OUTER JOIN?
What is a FULL...
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 8, 2016 at 3:33 pm
Yep, exactly. It sounds like some type of certification q too, where because MS mentions "restricted" in BOL or some other help, you're supposed to remember it exactly 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".
April 8, 2016 at 2:37 pm
GonnaCatchIT (4/8/2016)
We run re-indexing and update statistics on daily basis
Is that really necessary? Do you re-index all tables or just those that really need it? Hopefully you don't...
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 8, 2016 at 8:26 am
If the incoming data is sorted in clustering key order, you can add an:
ORDER ( { column [ ASC | DESC ] } [ ,... n ] )
clause to 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".
April 7, 2016 at 5:20 pm
Add another temp table to hold the Project & Packages you want to list. Then run the SELECT below.
Btw, you should add a clustering key to the #DateHr table...
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 7, 2016 at 2:24 pm
Not sure which ClientId column is in Temp_Client, so change if needed:
SELECT [AssigneeID]
FROM [dbo].Assignee
LEFT OUTER JOIN Temp_Client ON Clientid = Client_ID
WHERE @clientid = 'ALL' OR Temp_Client.Clientid IS 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".
April 7, 2016 at 10:01 am
CREATE TRIGGER %%
ON ALL SERVER
WITH EXECUTE AS 'sa'
AFTER
CREATE_TABLE,ALTER_TABLE,DROP_TABLE,CREATE_VIEW, ALTER_VIEW, DROP_VIEW
, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON;
SET NUMERIC_ROUNDABORT...
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 7, 2016 at 9:57 am
DesNorton (4/6/2016)
Hi Scott
This is certainly fast, with similar I/O to the other solutions, but very low CPU numbers. However, I am unable to wrap my head around the math...
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 6, 2016 at 12:25 pm
Rather than using a gui, use a SQL RESTORE command to do the restore. Then the new files can be specified using the "WITH" clause.
And here's a function 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".
April 6, 2016 at 9:21 am
I think you can use the (old) view "sysusers", "WHERE isaliased = 1" to find any aliases.
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 5, 2016 at 4:07 pm
Viewing 15 posts - 4,336 through 4,350 (of 7,619 total)