quote:

---

Originally posted by visakh16

how many records does tables contain? what are available indexes?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

IF OBJECT_ID('tempDb..#foo') IS NOT NULL DROP TABLE #foo
CREATE TABLE #foo (
	[ID] INT PRIMARY KEY
	, [value] VARCHAR(50)
	)

IF OBJECT_ID('tempDb..#bar') IS NOT NULL DROP TABLE #bar
CREATE TABLE #bar (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [fooID] INT
	, [value] UNIQUEIDENTIFIER
	)

CREATE INDEX IX_BAR_FOO_LINK ON #bar([fooID])


INSERT #foo ([Id], [value])
      SELECT 1, 'aaaaa'
UNION SELECT 2, 'bbbbb'
UNION SELECT 3, 'ccccc'


INSERT #bar([fooID], [value])
SELECT
	a.[ID]
	,  NEWID()
FROM
	#foo a
	CROSS JOIN #foo b
	CROSS JOIN #foo c
	CROSS JOIN #foo d
	CROSS JOIN #foo e
	CROSS JOIN #foo f
	CROSS JOIN #foo g

UPDATE #foo SET 
	[value] = 'HELLO'
FROM
	#foo f
	JOIN #bar b ON b.[fooId] = f.[ID]
WHERE
	b.[fooID] = 1

UPDATE f SET
	[value] = 'WORLD'
FROM
	#foo f
	JOIN #bar b ON b.[fooId] = f.[ID]
WHERE
	b.[fooID] = 2

SELECT * FROM #foo

UPDATE DimProjectPublicationIndicator
SET HasObligation = 'Y'
WHERE EXISTS
(
	SELECT *
	FROM DimProjectAction t
	WHERE DimProjectPublicationIndicator.PublicationId = t.PublicationId
		AND DimProjectPublicationIndicator.ProjectPublicationId = t.ProjectPublicationId
		AND EXISTS
		(
			SELECT *
			FROM DimAction a
			WHERE t.PublicationId = a.PublicationId
				AND t.ActionId = a.ActionId
				AND EXISTS
				(
					SELECT *
					FROM DimActionType c
					WHERE a.PublicationId = c.PublicationId
						AND a.ActionTypeId = c.ActionTypeId
						AND c.Code = 'O'
				)
		)
)

quote:

---

Originally posted by BarDev

If I run the following update query, the index that is created on PhoneID is not used. Also there is no way to tell the query to use the index IX_PhoneStatus_PhoneID

UPDATE PhoneStatus
SET RecordEndDate = GETDATE()
WHERE
PhoneID = 126

If I execute the following query, which includes the new FROM, I still have the same problem with the index not used

UPDATE PhoneStatus
SET RecordEndDate = GETDATE()
FROM Cust_Profile.PhoneStatus
WHERE
PhoneID = 126

But if I add the HINT to force the use of the index on the FROM it seems to work correctly.

UPDATE PhoneStatus
SET RecordEndDate = GETDATE()
FROM Cust_Profile.PhoneStatus WITH(INDEX(IX_PhoneStatus_PhoneID))
WHERE
PhoneID = 126

SQL Server should figure out what index to use, and I shouldn't have to force the index to be used. But this is how I got it to work.

BarDev

---