Wouldn't it be better to have at least separate stored procedure to use in all those different places but only returning columns that they actually need or maybe replace it...

I run into this all the time. The problem with DR specifically is it's not sexy. Execs love bragging about, "Now we can <fill in the blank> right from my...

... Or you can use PatternSplitCM (Duh...) also referenced in my signature....

SELECT Item

FROM #Sample S

CROSS APPLY dbo.PatternSplitCM(S.Numbers,'%[0-9]%')

WHERE Matched = 1;

One way to approach this involves 3 functions: one that removed duplicate characters, one that replaces characters based on a pattern and Jeff Moden's Splitter. There are links to two...

Kim Crosser (9/29/2015)

---

Alan - minor issue, but the first two fields (CC, NPA) are actually Int values. Making them Varchar(5) introduces some additional overhead in the function, where...

Ed Wagner (9/29/2015)

---

Nice test, Alan. I kinda knew how it would turn out, but it was nice to see it done.

Alan.B (9/28/2015)

---

And...

Luis Cazares (9/24/2015)

---

GilaMonster (9/24/2015)

---

Since the optimiser pushes...

Luis Cazares (9/29/2015)

---

Alan.B (9/29/2015)

---

Great article Luis! Informative, to the point. I picked up a couple things.

Interesting technique using REPLICATE, I would add that I like to use ...

Great article Luis! Informative, to the point. I picked up a couple things.

Interesting technique using REPLICATE, I would add that I like to use CHAR() more often...

Above all, how long it takes for the query to complete. Reads (something you can get using extended events or SET STATISTICS IO ON). I look at the query plan...

I know and work with people who would be considered "true" data scientists as well as some aspiring data scientists. The consensus seems to be, based on what I've read...

Kim Crosser (9/28/2015)

---

Jeff Moden (9/28/2015)

---

Kim Crosser (9/28/2015)

---

On the other hand, UDFs used to do some fancy formatting of an output result value aren't likely to have any significant impact on...

Just as a side note, this won't fail:

WITH X AS

(

SELECT fld

FROM #t

WHERE cast(fld as int) > 0

)

SELECT fld

FROM X

WHERE ISNUMERIC(fld) =1

using the same sample data from above and taking Luis' solution you can also do this:

WITH T AS

(

SELECT T.*

FROM @t T

WHERE NOT...

This is just fine

DECLARE @t TABLE (memberID int);

DECLARE @m TABLE (memberID int);

INSERT @m VALUES (1),(2),(4);

INSERT @t VALUES (1),(2),(3),(4);

SELECT * FROM @t;

DELETE T

FROM @t T

LEFT JOIN @m M on T.memberID =...