A "heap" is a table without a clustered index. It may or may not have nonclustered indexes.

You should review heaps carefully. Typically it's far better in SQL Server...

Here's my suggested code for this trigger.

CREATE TRIGGER [dbo].[MAYTABLE_A_IUD_WD]

ON [dbo].[MYTABLE]

AFTER DELETE, INSERT, UPDATE

AS

SET NOCOUNT ON;

DECLARE @action char(6)

...

N/A.

Please try code below. I didn't have any data to test it first, of course.

SELECT

a.ValetNO AS PlanNo,

cross_apply_1.Status1,

...

Perhaps....

I'd still use a table. You just need to add some type of csv list id and a sequence #.

If the function just removes an optional trailing '-' followed by other chars, you can avoid a function completely using CROSS APPLY:

UPDATE ts

SET

Col1 = CASE WHEN...

amy26 (2/11/2014)

---

Removing the transaction name does not rectify the situation.

The only thing that I could think of was we do have an if statement that calls another stored procedure...

The single index with the included columns can handle queries for both ProductKey alone and for ProductKey and one/both of the included columns. That means you only need one...

CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]

AFTER UPDATE

AS

SET NOCOUNT ON

BEGIN TRY

IF UPDATE(STATUS)

BEGIN

INSERT INTO dbo.B

(

col0,

col1,

...

Look at Change Tracking; it can identity changes vastly more efficiently than what you're doing now.

If you're on Enterprise Edition, also look at Change Data Capture, which gives you "point-in-time"...

You most likely do have nested transactions, either implicitly or explicitly.

Just remove the transaction name from the ROLLBACK, which is not meaningful for SQL Server anyway:

ROLLBACK TRANSACTION /*UPDT_DATA*/

You need to run these commands (or the equivalent). Run them online if possible, and using tempdb for sort if possible:

DROP INDEX [IX_PurchaseOrderDetail_ProductKey] ON [dbo].[PurchaseOrderDetail]

CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_ProductKey]...

george sibbald (2/7/2014)

---

thats a bit over the top surely?

Nowhere does it say using maintenance plans per se is not...

And, if the query uses more than one table, use an alias and prefix all columns with the correct alias name. Remember, we have absolutely no idea what columns...