Maximum selectivity for bookmark lookups depends on row size.

Consider a range query on a column that has a non clusterd index. For narrow ranges the optimizer chooses to access the table via bookmark lookups. For wider ranges it just scans the clustered index, as it is cheaper than bookmark lookups. There is no hardcoded threshold - it depeneds on row size and bookmark size. A simple example - in all three cases the optimizer chooses bookmark lookups, but if you increase the range just a little bit, it scans the whole clustered index.

-- Assuming Numbers table with 10000 rows

CREATE TABLE WideRows(i INT NOT NULL PRIMARY KEY, j INT NOT NULL, c
CHAR(1000))
go

INSERT WideRows SELECT Numbers.Number, Numbers.Number + 1, 'asdf' FROM
dbo.Numbers
go

CREATE INDEX WideRows_j ON WideRows(j)

---- break even point close to 4%
SELECT i, j, c FROM WideRows WHERE j BETWEEN 1000 AND 1400
go

DROP TABLE WideRows
go

CREATE TABLE HugeRows(i INT NOT NULL PRIMARY KEY, j INT NOT NULL, c
CHAR(3500))
go

INSERT HugeRows SELECT Numbers.Number, Numbers.Number + 1, 'asdf' FROM
dbo.Numbers
go

CREATE INDEX HugeRows_j ON HugeRows(j)

---- break even point almost 20%
SELECT i, j, c FROM HugeRows WHERE j BETWEEN 1000 AND 2900
go

DROP TABLE HugeRows
go

CREATE TABLE NarrowRows(i INT NOT NULL PRIMARY KEY, j INT NOT NULL, c
CHAR(10))
go

INSERT NarrowRows SELECT Numbers.Number, Numbers.Number + 1, 'asdf'
FROM dbo.Numbers
go

CREATE INDEX NarrowRows_j ON NarrowRows(j)

---- break even point less than 1%
SELECT i, j, c FROM NarrowRows WHERE j BETWEEN 1000 AND 1040
go

DROP TABLE NarrowRows
go

The price of BEGIN TRANSACTION

A question came up in a newsgroup:
Is there any performance penalty for explicitly wrapping a single DML statement in BEGIN TRANSACTION / COMMIT brackets?

I did a quick benchmark:

CREATE TABLE aa(i INT)
go
CREATE PROCEDURE Test1
AS
BEGIN
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE(@i < 10000) BEGIN
INSERT aa(i) VALUES(@i)
SET @i = @i + 1
END
END
go
CREATE PROCEDURE Test2
AS
BEGIN
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE(@i < 10000) BEGIN
BEGIN TRANSACTION
INSERT aa(i) VALUES(@i)
SET @i = @i + 1
IF @@ERROR<>0 BEGIN
ROLLBACK
END ELSE BEGIN
COMMIT
END
END
END
go
Test1
go
Test1
/*
Profiler results:
CPU:406
Reads: 10720
Writes: 104
Duration: 2406
*/
go
Test2
go
Test2
/*
Profiler results:
CPU:373
Reads: 10844
Writes: 116
Duration: 2406
*/
go
DROP PROCEDURE Test1
go
DROP PROCEDURE Test2
go
DROP TABLE aa
go

I ran the tests 3 times and I did not notice any significant differences in
neither of 4 counters. I would not worry about performance penalties of BEGIN TRAN in my environment.

Bypassing A Trigger

Several ways to bypass a trigger can be found in an excellent book "Inside MQ SQL Server 2005: T_SQl Querying". Here is yet another one.

Suppose you have a trigger that records all the changes against a table, as follows:

CREATE TABLE Party(PartyID INT NOT NULL PRIMARY KEY, LastName VARCHAR(20),
FirstName VARCHAR(20), Salutation VARCHAR(20))
go

CREATE TABLE PartyHistory(PartyID INT NOT NULL, LastName VARCHAR(20), FirstName
VARCHAR(20), Salutation VARCHAR(20),
ModifiedBy VARCHAR(100), ModifiedAt DATETIME)
go

CREATE TRIGGER PartyUpd ON Party
FOR UPDATE
AS
PRINT 'Entering Trigger PartyUpd'
INSERT INTO [dbo].[PartyHistory]([PartyID], [LastName], [FirstName],
[Salutation], [ModifiedBy], [ModifiedAt])
SELECT [PartyID], [LastName], [FirstName], [Salutation], SYSTEM_USER,
CURRENT_TIMESTAMP
FROM Deleted
go

INSERT dbo.[Party]([PartyID], [LastName], [FirstName], [Salutation])
VALUES(1, 'Doe', 'John', 'Junior')
INSERT dbo.[Party]([PartyID], [LastName], [FirstName], [Salutation])
VALUES(2, 'Doe', 'John', 'Junior')
go
UPDATE dbo.[Party] SET [LastName] = 'Cooper', [FirstName] = 'Stanley'
WHERE partyID = 1
go
SELECT * FROM PartyHistory
/*
PartyID LastName FirstName Salutation
ModifiedBy
ModifiedAt
----------- -------------------- -------------------- --------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------------
1 Doe John Junior
US\akuznetsov
2006-12-04 16:42:12.480

(1 row(s) affected)
*/

Suppose you want to modify Salutation column, as follows

UPDATE Party SET Salutation = 'Jr.' WHERE Salutation = 'Junior'

And you do not want the trigger to fire. Make sure that RECURSIVE_TRIGGERS is disabled (which is the default):

SELECT DATABASEPROPERTYEX('SANDBOX','IsRecursiveTriggersEnabled')
----
0

Retrieve your spid:

SELECT @@spid
/*
------
58

(1 row(s) affected)

*/

And wrap your update in a trigger:

CREATE TRIGGER Bypass_PartyUpd ON Party
FOR UPDATE
AS
PRINT 'Entering Trigger Bypass_PartyUpd'
PRINT @@SPID
IF @@spid = 58
UPDATE Party SET Salutation = 'Jr.' WHERE Salutation = 'Junior'
go

Now all you need to do is run an update which does not modify any rows

UPDATE Party Set LastName ='asfdsdf' WHERE PartyID<0
go

The new trigger will fire and update Salutation. Because RESURSIVE_TRIGGERS is turned off, the old trigger will not fire:

SELECT * FROM Party
/*
PartyID LastName FirstName Salutation
----------- -------------------- -------------------- --------------------
1 Cooper Stanley Jr.
2 Doe John Jr.
*/
go
SELECT * FROM PartyHistory
/*
--- still only one row in PartyHistory
PartyID LastName FirstName Salutation
ModifiedBy
ModifiedAt
----------- -------------------- -------------------- --------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------------
1 Doe John Junior
US\akuznetsov
2006-12-04 16:51:49.257
*/
go

If you try an update which clearly will not update any rows no matter what:

UPDATE Party Set LastName ='asfdsdf' WHERE (1=0)

The optimizer will be smart enough to not execute the update, so the trigger will not run at all.

The advantage of this approach is that you do not need to touch the original trigger at all. The disadvantage is that you must not forget to drop the new trigger.