If you are inside a stored procedure, function or trigger

object_name(@@procid)

Avoiding the parent axis as mentioned here may improve performance

http://msdn.microsoft.com/en-us/library/ms345118(SQL.90).aspx

SELECT Doc.value('NAME[1]', 'varchar(16)') AS [Name],

Doc.value('VALUE[1]', 'varchar(16)') AS [Value],

...

Another way

WITH CTE AS (

SELECT ID, NR, Txt, Val1,

ROW_NUMBER() OVER(PARTITION BY NR,Val1 ORDER BY ID) AS rn,

...

SELECT ename + CASE WHEN COUNT(*) OVER(PARTITION BY ename) > 1 THEN ' ' + ecode ELSE '' END

FROM emp

Jeff Moden (7/24/2009)

---

Mark (7/13/2009)

---

I think both functions can be simplified

create function dbo.ConvertIp2Num(@ip nvarchar(15))

returns bigint

as

begin

return ((cast(parsename(@ip,4) as bigint)*256+

cast(parsename(@ip,3)...

Ken McKelvey (7/23/2009)

---

Nice idea Mark.

This may be a slightly simpler way to catch the gap in RangeId

;WITH CTE AS (

SELECT RangeId,

CountryCode,

...

There is one issue, but we only encountered it after further testing and in fairness my examples didn't highlight this potential scenario.

Mark's solution assumes that the RangeId values are in...

Thanks...

WITH CTE AS (

SELECT RangeId,

CountryCode,

CurrencyCode,

ROW_NUMBER() OVER(PARTITION BY CountryCode ORDER...

Ryan Keast (7/20/2009)

---

AGE =

-- Find difference in years and subtract 1 if date is before this years birthday

...

WITH CTE AS (

SELECT mobilenumber,activationdate,status,

ROW_NUMBER() OVER(PARTITION BY mobilenumber ORDER BY activationdate DESC) as rn)

SELECT mobilenumber,activationdate,statu

FROM CTE

WHERE rn=1

Michael Valentine Jones (7/20/2009)

---

The logic for Age in this select statment shows a simple way to do it.

declare @date_of_birth datetime

set @date_of_birth = '19770801'

select

...

Maybe this

select xData_1.value('

sum(for $x in /items/item

where $x/@year=2009 and $x/@month=6

return $x/@amount)','int')

from MyTable

This should get you started

SELECT District,School,

SUM(CASE WHEN Gender='M' THEN Count ELSE 0 END) AS MALES,

SUM(CASE WHEN...

Jonathan Kehayias (2/3/2009)

---

doobya (2/3/2009)

---

SQL 2005 has xml deadlock report in profilerwhich will track deadlock down to tables and stored procedure statementsI think your solution does not solve the deadlock -...