You want to look for the filter_definition column in sys.indexes.

Try the following

SELECT t2.EntityID, t1.EntityID

FROM @NewEntities t1

CROSS JOIN @NewEntities t2

WHERE t1.ID < t2.ID

ORDER BY t2.ID DESC

Another way with just one update.

UPDATE trans

SET price = CASEWHEN relatedID IS NOT NULL

THEN

(select avg(price) from trans where id<=t.relatedid)

ELSE

(select avg(price) from trans where id<=t.id)

END

FROM trans...

It looks like you will need to perform 2 updates, one for the average and a second for the related price update.

UPDATE trans

SET price = (select avg(price) from trans where...

Your update does not appear to be doing any updating so MERGE might not be the best way to do this.

Anyway you can add an OUTPUT clause to the merge...

The following should give you what you want.

UPDATE poheaders

SET status = 'CA'

FROM poheaders h

LEFT OUTER JOIN polines l on h.poID = l.poID

AND NOT(l.status = 'CA')

WHERE h.status not in ('CA','CL')

AND...

One problem with your SQL is the @results table variable contains 6 columns yet you are attempting to insert 7 (looks like p.prod_id should not be in the select). ...

You need to use an OUTPUT parameter.

ALTER PROCEDURE [dbo].[_generateRandomString]

@length int

,@validcharacters nvarchar(50)

,@randomstring nvarchar(50) OUTPUT

AS

BEGIN

...

END

declare @newstring nvarchar(50)

EXEC _generateRandomString 10, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', @newstring OUTPUT

It looks like your table is Partitioned so the Primary Key and Unique index must contain the partitioning column 'KeyGeographicType'. Try the following.

ALTER TABLE DemographicGeo WITH NOCHECK ADD constraint...