Jeff Moden (7/1/2010)

---

Mike C (7/1/2010)

---

Jeff Moden (7/1/2010)

---

Mike C (7/1/2010)

---

Good catch on the week # UMG. As you mentioned, I did assume 2005 or 2008, but only because 2000 is...

UMG Developer (7/1/2010)

---

Mike C (7/1/2010)

---

One note on your update--be careful when using this syntax:

UPDATE t

SET ...

FROM table t

INNER JOIN table2 t2

ON ...

This syntax lends itself to parallelism and can result...

Goldie Graber (7/1/2010)

---

Mike C (7/1/2010)

---

Good catch on the week # UMG. As you mentioned, I did assume 2005 or 2008, but only because 2000 is out of support :)....

Jeff Moden (7/1/2010)

---

Mike C (7/1/2010)

---

Good catch on the week # UMG. As you mentioned, I did assume 2005 or 2008, but only because 2000 is out of support :)....

Good catch on the week # UMG. As you mentioned, I did assume 2005 or 2008, but only because 2000 is out of support :). If using 2000, he...

Hi mutlyp,

I took a shot at rewriting this in cursor-less form. There are some things to keep in mind:

* You'll probably need to review some of the logic (I've...

The last I heard on the topic of temp tables v table variables was that the server decides when it can use memory v serializing to disk. I only...

chris.fauvel (6/30/2010)

---

Bradley Deem (6/29/2010)

---

I recommend using a CLR for this. Microsoft has a great example here http://msdn.microsoft.com/en-us/library/ms131056.aspx. I'm curious what the performance differences are. Admittedly, I have...

Jeff Moden (6/30/2010)

---

Nice to see someone else ramp it up for a change. Nicely done Michael. 😀

The next thing we need to teach is that returning results to the...

I didn't get a chance to run any tests against the data (it was a little late when I posted that), but one thing worth considering is whether or not...

mutlyp (6/29/2010)

---

Thanks for the reply.

You have the Insert in front of the select. Is that on purpose? I guess what I am asking is, if you put an Insert statement...

How's this for data? 🙂

CREATE TABLE #Role

(

RoleName VARCHAR(100)

);

GO

INSERT INTO #Role

(

RoleName

)

VALUES ('Accounting'), ('Approver'), ('Developer'), ('International Sales Manager'), ('Marketing'), ('System Administrator'),

('Technical Customer'), ('Technical Director'), ('Training');

GO

CREATE TABLE #UserRole

(

UserID INT NOT NULL,

RoleName VARCHAR(100)...

mutlyp (6/29/2010)

---

Not realy sure I get this article.:crying: First you show us a cursor example which in my opion is beyond simple. Are there really programmers out there that use...

WITH CTE

AS

(

SELECT ROW_NUMBER() OVER

(

PARTITION BY u2.UserID

ORDER BY MIN(u2.RoleAssignedDate)

) AS RowNum,

u2.UserID,

u2.RoleName

FROM @UserRole u2

GROUP BY u2.UserID,

u2.RoleName

)

SELECT DISTINCT u1.UserID,

SUBSTRING

(

(

SELECT ', ' + c.RoleName AS '*'

FROM CTE c

WHERE u1.UserID = c.UserID

ORDER...

lehmannds (6/27/2010)

---

togaratw (6/26/2010)

---

Great article and it surely points the way for more efficient statements.

I'm eagerly anticipating the next series of this article for more complicated scenarios where cursors can...