The system table cross join method is really slick (and it is a lot smaller!). The only downside is it adds I/O overhead to the query (~52,000 reads in SQL2k)...

If I understand your comment correctly, could you not just append an:

ORDER BY 1

to the query?

Good Points Gift Peddie and Antares686. For some reason, I was thinking in join operators. Sorry guys!

you can't return results from EXEC() into a varchar. (unless you take the results out of the table variable)

I may be missing something here, but why are you using...

To elaborate, @Results is a table variable with a schema to match the output of your query.

Try this:

INSERT INTO @Results

EXEC (@FileExist)

That is a very subjective question.

It depends on how the tables and columns are indexed, what join operater is used, etc.

Examples:

are both columns in the join predeicate indexed at all,...

I would be surprised if there was a faster way to do it than this... (let me know if you want a different type of random number) Oh, and for...

Your example will give you the same results nomatter how your db is configured.

Len will always implicitly trim trailing spaces, datalength will not. Be aware, though, that datalength and len...

SQL Server does sometimes treat a space as the same as an empty string.

This occurs when the database compatibility is set to 65 or lower.

To quote MSDN:

Whether SQL Server...

What is the max width of your comment column? Could you please provide the schema for your two tables?

Thanks

/*************************************

*** Set up example table structure ***

*************************************/

CREATE TABLE #order

(

    Order_id    INT    PRIMARY KEY CLUSTERED

    -- ... 

)

CREATE TABLE #OrderComment

(

    CommentID   INT     PRIMARY KEY NONCLUSTERED,

    Order_ID    INT     REFERENCES #order(Order_Id),

    Comment     VARCHAR(500)

    -- ...

)

CREATE CLUSTERED INDEX IXC__OrderComment__Order_Id