Very interesting article and I like to learn more about the concepts. On a practical level, how does one read the @rules table?

For example, my first lines turned out:

nsewca

000014

000114

000211

How can...

Pretty good trick, but do you really want to subject each row to the triple REPLACE function?

REPLACE is a costly function and will run for a good while when...

Hi again,

I had to take care of some work here. I did try the second CLR that Florian proposed and compared it to the his first version and mine, see...

Sorry, I am not familiar with the term "CDR's"

Jeff,

You will get no argument from me that the problems are not just the functions that we run. Grinding through 200+mln records (or 400-500GB) does take some effort. Other...

Again, thanks for all your input everyone. I have to do production work today and will not be able to work on this problem ;-(

I did talk to one of...

Florian,

thanks for submitting the CLR (C# is fine) I compared it against mine and a baseline query without the function and found the following, see below. Looks like we use...

J-F,

Although I haven't worked with table-value functions, I am not sure that they work for my scenario. The validated phone number needs to be linked up to the original data,...

First of all, thanks every one for your help so far. Its clear that I need to learn a few more things about performance of functions.

Let me post some findings...

The TSQL functions are in the select portion not the Where clause or Join conditions.

CLR is an option and I tried to convert that phone function. However, as unexpected as...

Came across this article that wants me to believe Scalar functions are not a great idea to begin with, not sure I am buying into it completely.

http://sqlblogcasts.com/blogs/simons/archive/2008/11/03/TSQL-Scalar-functions-are-evil-.aspx

Here you go:

CREATE FUNCTION [dbo].[Valid_Phone]

(@inputstring varchar(8000) )

RETURNS varchar(8000)

AS

BEGIN

/*return valid phone or no space blank*/

declare

@outputstring as varchar(8000)

set @outputstring = ''

-- Take out punctuations ()-.

set @inputstring = replace(@inputstring,'(','')

set @inputstring =...

I will not dispute the overhead, but is there a way to quantify the overhead?

we are setup for trusted connections only.

The linked server does work when you issue "select * into ... from Server.db.dbo.table"

current through is to put grant access/revoke around the SELECT into...

good one, but schema is implicit with @table

example

exec SP_Transfer 'Server', 'DB', 'dbo.table'