Declare the variable as varchar.

DECLARE @v-2 VARCHAR(8000)

SELECT @v-2 = SUBSTRING(Code,@StartPos, @Length) FROM tmp_AuditTriggerCustomised

You'll get an error if you give a length over 8000 (since the variable can't hold more than 8000 characters)

The...

select ID from TBL where COL1= @a UNION select ID from TBL where COL2= @a

That will result in one index seek (first query) and one table scan (second) plus a distinct...

In that case I will stick with my earlier advice. If this is a once-off query and in the future you will only search by ID then don't put an...

Huh?

Statistics is a measure of the distribution of values within a column. It has nothing to do with collation.

If you want to change the collation of existing columns in existing...

So both ID and Col1 are already indexed. If this is going to be a once off query of Col2 whrn don't bother indexing it.

What's the data type of...

With an INNER JOIN I've never seen it make a difference and hence I prefer to keep the filter in the where clause. (join in the from, filter in the...

Not unless you want to risk a corrupt database.

See my first post in this thread. I noted exactly what changing the db collation changes and how to change the...

Do you ever search on ID? Is it the PK of the table (and hence uniquely indexed)

How many rows are in the table? How often is it queried? How often is...

From books online:

sp_dboption is supported for backward compatibility. Use ALTER DATABASE to set database options.

ALTER DATABASE MyDatabase COLLATE French_CI_AS

Note that this won't change the collation of existing tables. it will affect the following (from Books online)

• The default collation for the database. This new...

I want to know what is happening when you make a composite index on the three columns.

What exactly do you want to know?

select ID from TBL where COL1= @a or...

DBCC DBREINDEX

Rebuilds one or more indexes for a table in the specified database. DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table....

Also index defrag only reorders the leaf-nodes of an index (as opposed to index rebuild which completely recreates it.)

Hence, on your dev server the higher-level nodes could have a different...

The reason why is that the optimizer is smart enough to lock different extents in the systems tables for each temp table (or real table if you want) so although...

That's not going to work very well for a multiple row update.

Perhaps something more like

Insert into <some tbl> (<field list> )

SELECT <fields>

FROM inserted inner join deleted ON inserted.<primary...