It's just silly mistake:

cast(od.Orderid as char(8))

or

convert(char(8), od.Orderid)

Use noeld suggestion or make a view from you UNION statement and select from this view using any kind of WHERE clause you like.

Varchar values use typically 36 different combinations per each byte instead of 256 possible. So you use only 14% out of each page in your index. Your index (and column...

If your sysadmin role registered as login for SQL Server?

I'm not sure about @@trancount. It will count all transaction started within the connection - in procedure called this SP and in another procedure is called by this SP (e.g....

Clause in your UDF appian.dbo.udf_A

"WHERE ... datediff(day, [login_log].[dt_login], Convert([@date]))<=[@last_x_day"

eliminates all indexes and makes server scan whole table and return all rows, because it cannot predict result of the calculation.

Change it...

Everything is cool, but who did tell you that collation for this patricular column is the same as default collation for the database?

Yes, typically nobody bothers to change collations after...

Yes, if you know TableA_Collation and sure it will never be different.

Create one @sql for both operations:

SET @sql='UPDATE @tbl SET fieldname=@fieldname, lookupvalue=@lookupvalue, lookuporder=@lookuporder WHERE lvid=@lvid'

INSERT INTO @tbl (fieldname, lookupvalue, lookuporder,...

For big tables better to use joins:

delete M

from Milestones M

   left join GeneralInfo on GeneralInfo.YNumber = M.YNumber

   left join Imports on GeneralInfo.XNumber = Imports.XNumber

where Imports.XNumber IS NULL

And if you do SELECT much more often than INSERT,DELETE, UPDATE, create computed column and make it = dbo.Function2ComputeSeqNumber(OrderNum).

Function must be :

SELECT COUNT(*) ...

where OderNum = @OrderNum and ... <=...

No, that was a query to get COUNT without actually performing count.

Server does COUNT everytime table is updated and stores value in sysindexes. So why do it again and again?

And...

Select rowcnt from sysindexes

where id = object_id (@TableName) and indid in (0,1)

0 exists for all tables without PK, 1 is PK index. Any particular table has only one of those indexes.

Almost right.

But this must be better:

select * from TableA  A  join TableB B on A.KeyFld  collate  SQL_Latin1_General_CP1_CI_AS = B.KeyFld   collate  SQL_Latin1_General_CP1_CI_AS

There is no index on QuestionId in table vts_tbAnswer.

That's why SQL Server creates hash table with millions rows to get all possible joins with table vts_tbQuestion. Because therwe is no...