Viewing 15 posts - 5,191 through 5,205 (of 7,619 total)
Doesn't have to be an iTVF, you can still use dynamic SQL, just process all columns in a given table in one statement, not multiple.
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 20, 2015 at 7:34 am
dwain.c (4/19/2015)
ScottPletcher (4/17/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".
April 20, 2015 at 7:31 am
Cursoring through the tables is fine, but you should process all columns in a single table in one pass (scan), not a separate pass for each column.
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 17, 2015 at 9:36 am
I don't understand how this code can work.
Your subqueries to tt1 / t2 don't contain any column named "Id_1" yet you reference that column when you join to t2: 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 17, 2015 at 9:33 am
First, I don't see that code as turning on any setting. It sets a local variable value, that's it. You happened to name that local variable "[@]IMPLICIT_TRANSACTIONS", but...
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 17, 2015 at 7:24 am
The on-going maintenance is higher for an INSTEAD OF trigger, since every column addition or deletion, and some column changes, all requires changes the trigger, which is not the case...
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 17, 2015 at 6:48 am
CREATE TRIGGER MYTABLE_AUDIT__TRG_INSERT_UPDATE
ON dbo.MYTABLE_AUDIT
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE ma
SET ma.DateModified = GETDATE()
FROM MYTABLE_AUDIT ma
INNER JOIN inserted i ON
i.AID = ma.AID
WHERE
i.DateModified IS NULL;
GO...
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 16, 2015 at 2:36 pm
Use an AFTER trigger(s) for both INSERT and UPDATE.
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 16, 2015 at 1:47 pm
For example:
--USE [<your_db_name_here>] --naturally make sure you are in the right db
SET DEADLOCK_PRIORITY LOW --probably irrelevant, but just in case
DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname
--NOTE: showing missing indexes can take some...
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 16, 2015 at 1:08 pm
You will need to look at least at the stats SQL provides for indexes:
1) missing index stats and
2) index usage stats.
Between those and you knowledge of the table, we...
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 16, 2015 at 1:07 pm
Lowell (4/16/2015)
Talib123 (4/16/2015)
They believe it will improve...
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 16, 2015 at 11:04 am
Someone's apparently discovered an obscure bug that's being repeated here. Software bugs are just bugs, we can't "explain" them.
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 16, 2015 at 10:05 am
There are many, many times when the PK should not be the clustered index. Why do you think it should be changed??
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 16, 2015 at 9:10 am
You're welcome. The really great thing is that that code still works perfectly if/when you convert the column itself to datetime (or date) ... nice!
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 16, 2015 at 8:18 am
You want to use the current index where possible, so do this:
select MRN, Name, AppointmentDate
from DATA
where datetime >= '2015-04-01 00:00:00.000'
and datetime < '2015-05-01 00:00:00.000'
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, 2015 at 2:14 pm
Viewing 15 posts - 5,191 through 5,205 (of 7,619 total)