Sergiy has the right answer, but the reason you have the problem is NOT so much because of the behavior of CAST, but instead is what you're asking CAST to...

Just an extra thought here, but why would you want to use a reserved word for the name of a function? It's not the least bit descriptive of...

First item: your code, as shown, fails to even run correctly, as you appear to be creating both constraints twice. Once I had that straightened out, I discovered...

It was the execution with _Test at the end of the function name.

Steve

(aka smunson)

:):):)

Jeff Moden (1/28/2009)

---

Not sure which one of those include the Tally table function that I wrote. ...

I had realized too late that I had forgotten to include the timings, and it required almost 4 minutes to run the whole thing over, as I had quickly run...

Here are the test timings I referred to:

====================================================================================

CREATE 200,000 ROW TEST TABLE

====================================================================================

Table 'spt_values'. Scan count 9, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob...

Thanks Lynn, the case sensitivity was indeed my problem. I forget all too often, but I too much prefer the case-sensitive setup, and set mine up that...

Jeff,

I have a relatively fresh installation of SQL 2005 Developer Edition, but your object in the following:

.

.

.

FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2

.

.

.

doesn't exist on my server. Is...

Jeff,

Can you supply the code necessary to generate the test data you used? I have a quad-core 2.83GHz machine with SQL 2005 DE SP2 that I'd like...

Interesting data here. Anyone know what to do with the repeating occurrences of several of the ranges because there are "locations" that may or may not be "AADC"s,...

I understand where you're coming from, but I think the gist of this thread is more related to the reason behind the desire to see order, because what Jeff has...

I'm not sure you'd have to worry about using a trigger with this structure. Since you never create a record that doesn't reference something, when you delete,...

Just wondering if you could use ISNULL instead of COALESCE, as I've seen several posts inidicating it's faster, and perhaps with 500,000 records, that might add up? ...

Oops... I find it difficult to code correctly when I have nothing to work with. Anyway, here's another shot at it. I'm not...

Ok, so you ended up with XML that isn't structured the way you need it to be to get SSIS to be able to import it in a practical fashion....