Duplicate indexes

sqlserver 7 indexes

Anonymous — Fri, 11 Jul 2008 02:49:17 GMT

PingBack from http://evanwebsite.madoo.com/sqlserver7indexes.html

re: Duplicate indexes

Paul White — Tue, 31 Mar 2009 22:10:25 GMT

Hi Greg,

Here's a (contrived) example of a useful duplicate index, which might just be of use on a read-heavy table where the concurrency benefit outweighs the modification overhead...

-- Set up demo
CREATE TABLE dbo.Temp (A INT NOT NULL, B INT NOT NULL);
DECLARE @i INT;
SET NOCOUNT ON;
SET @i = 0;
WHILE @i < 100000
BEGIN
INSERT dbo.Temp(A, B) VALUES (@i, @i);
SET @i = @i + 1;
END;

-- Duplicate indexes
-- Note that row locks are disabled
-- (for a good operational reason!)
CREATE UNIQUE NONCLUSTERED INDEX i1 ON dbo.Temp (B ASC) WITH (ALLOW_ROW_LOCKS = OFF);
CREATE UNIQUE NONCLUSTERED INDEX i2 ON dbo.Temp (B ASC) WITH (ALLOW_ROW_LOCKS = OFF);

-- In query window 1
BEGIN TRAN;
UPDATE T SET A = -1 FROM dbo.Temp AS T WITH (HOLDLOCK, INDEX(i1)) WHERE B = 50000;

-- In query window 2
BEGIN TRAN;
UPDATE T SET A = -1 FROM dbo.Temp AS T WITH (INDEX(i1)) WHERE B = 50001;

-- The above blocks trying to grab a page-level update lock on the index.

-- In query window 3
UPDATE T SET A = -1 FROM dbo.Temp AS T WITH (HOLDLOCK, INDEX(i2)) WHERE B = 50001;

-- This succeeds!

I know it's a fairly out-there example, but I bet someone is using it...!

Cheers,

Paul

Recent Faves Tagged With "tlogs" : MyNetFaves

Anonymous — Sun, 17 May 2009 13:20:25 GMT

PingBack from http://www.mynetfaves.com/tags/tlogs

How earn $ 1000 a week?

AleVaserman — Fri, 05 Mar 2010 16:25:59 GMT

You can earn from 3000$-5000$ a Month with this program!
You will make money from from multiple streams: people search engine, ClickBank, HD Publishing, Google AdSense, HostGator, My Life and GDI (Global Domain International).
Visit site: [url=http://bit.ly/aLNyID]acme-people-search.com[/url]