Viewing 15 posts - 5,191 through 5,205 (of 7,613 total)
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
Detach now (since SQL 2005, IIRC) changes the security on the files -- this is an intentional thing by MS, as a "security feature". But it's been a royal...
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:11 pm
You check for a "GLOBAL" cursor but you don't explicitly specify a GLOBAL cursor when you DECLARE it. Whether a cursor is local or global can vary based on...
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:07 pm
You should look into RCSI as well. That may solve your issue even more completely and with less effort during coding :-).
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, 2015 at 3:11 pm
CREATE FUNCTION [dbo].[CheckLoginDetails_Username] (
@SuppliedUsername nvarchar(150),
@SuppliedPassword nvarchar(150)
)
RETURNS bit
AS
BEGIN
RETURN (
SELECT 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 14, 2015 at 1:00 pm
Maybe a join to reduce repetition of comparisons:
SELECT bf.BF_ORGN_CD, bf.BF_BDOB_CD, bf.BF_TM_PERD_CD, bf.data
FROM BF_DATA bf
INNER JOIN (
VALUES('A1', 'B1', 'C1'),
...
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, 2015 at 9:56 am
There used to be both recoverability and performance reasons for the split. Now it's more about recoverability than performance.
If possible, you want the data and logs on separately "fail-able"...
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, 2015 at 9:49 am
Viewing 15 posts - 5,191 through 5,205 (of 7,613 total)