I haven't got around to read the 2nd part of the article completely. I did have a glance. I just want to thank you again as what i learned here...

Thanks Grant. Will do.

- arjun

Grant, I had done some reading on this and I read some articles again. Looks like no one really advises the use of nolock. It will be great if you...

Thanks Grant and Kshitij.

@Grant: What do you mean by missing rows? Rows that are there in the dataset but not present in database as a result of an update...

Reduced execution time from ~ 2 min to 2-4 secs by changing

'where scalarvaluedfunction(table.column) = @id or @id = 0 or @id is null '

to

'where @id = 0 or @id...

hey sorry folks, i was just messed up in the head after seeing my query misbehaving so badly. I ran the query simultaneously in the DBs and they fetch the...

I would like to add that I am more worried about performance than best practice and maintenance.

- arjun

Paul White NZ (4/12/2010)

---

mohd.nizamuddin (4/12/2010)

---

Nice Article Paul. It is very well expalined with example.

I have a doubt, since the UDTF would get executed as many times as the row count...

Is this what you need? Otherwise, please post the desired output.

declare @var xml

set @var = '<AttributeCollection>

<Attribute>

<value>Personal P and C</value>

<attributeid>100055</attributeid>

...

I was wondering why you chose sys.columns. Thanks Paul. It makes sense now.

- arjun

Paul, can you explain this? Especially, the ROW_NUMBER() OVER (ORDER BY (SELECT 0)) part. I'm sorry if i am bugging you.

SELECT TOP (10000)

...

Wow!!! Beautifully explained.

"Using HAVING (or a GROUP BY, DISTINCT or any aggregate function) means that any rows returned refer to groups not records. In this case, there is one...

vaibhav.tiwari (4/1/2010)

---

Yes, with reference to reply of BigM that if schemaname is concatenate with the table name then sql server identify that it is the table name then it will...

I suggest that you do some performance testing with this query.

- arjun

@vaibhav: the query runs fine. It does not cause an error.

@swati: count(*) returns the count in a group. In your query you haven't mentioned a group....