Do you have the 'Lock Pages in Memory' permission granted to the SQL service account ?

That can be a known performance problem on 64 bit SQL2005, if the OS is...

I'll take a break from gnashing my teeth & swearing at the original coder to add my common mistake:

Frequent use of DISTINCT to hide a fundamental design flaw and/or hide...

Yep, both definitely better than my solution, avoiding the Replace() and any performance penalty.

Because the requirement is:

>>I need one space between each value when it exists.

If a value is NULL and you replace with a space, you then have 3 spaces between...

Instead of replacing the NULL values with an empty string, replace with some character string that does not occur in the data, then use REPLACE() to remove that character string...

[EDIT] Nevermind, I see I need to drink a gallon of coffee to keep up with this thread. Removed redundant repost of solutions posted while I as typing.

The || operator is string concat, so replace with + in SQL.

Function nvl() is replaced with IsNull() (or Coalesce() )

To_Char is replaced with convert() and 1 of the MSSQL...

Check BOL on Rank()

Select Num, Rank() Over( Order By num ) As NumberSequence

From #temp

Order By num

It's amazing how much procedural code you can write when you're trying to avoid thinking in sets:

CREATE TABLE #tbl_registers (uid UNIQUEIDENTIFIER)

...

It's easy enough to test with a couple of temp tables. Results are equivalent between the Coalesce & Union. So check the query plan on each and implement whichever costs...

It's not a UNION. It's a UNION ALL.

Different query plan, no need for an expensive sort to eliminate dupes.

Why not just have 2 statements and a UNION ALL ? The INNER JOIN ensures the sets are mutually exclusive (assuming your rule that a record can't be in both...

The primary concern is performance, versus maintainability & prevention of SQL injection attacks, right ?

Any SQL like this:

WHERE (col_a = @a OR @a IS NULL) AND...

Typically never a non-clustered on only 1 column - in a many-to-many intersection table, an index on just 1 of the columns would never be selective enough to be considered.

Clustered...

As always, the answer is "it depends".

Depends on volume of Insert/Update/Delete activity on the table versus Select.

Depends on how the data is typically accessed ie, always looking at the possible...