You're join to Number_List is as follows:

on n.number < t.Person_count

Less than ?

Based on the number of rows in Eng_Wales and the values of Person_Count, your intention is to...

What does this return:

SELECT COUNT(*), SUM(Person_Count)

FROM Eng_Wales

Couple of questions & points:

Use DBCC DropCleanBuffers before each test so that you're running with a 'cold' SQL data cache each time. You want perf differences to be related to...

>>The execution plan shows that 88% of the time is spent in the index seek.

Are you sure it says "seek" ? I would expect a clustered index scan

Create Table #Test...

The problem is that the order of columns in the index does not support that query, and I would assume a scan is happening.

If f1 is the 1st column in...

If you have a linked server, the properties of the link are in system table master..sysservers.

SELECT datasource

FROM master..sysservers

WHERE srvname = 'YourLinkAlias'

What does fn_Splt return ? A table variable ? If so, just use it like a table and join to it:

SELECT name

FROM EMPLOYEE_INFORMATION As E 

INNER JOIN dbo.fn_Split(@sText, ',')  As S

 ...

Just join with an OR ...

SELECT [Tracking_Code]

  ,tracking.[Order_Code]

  ,Sales_Amount

FROM Tbl_Tracking Tracking

INNER JOIN Tbl_Sales Sales

ON (Sales.Your_reference = Tracking.Order_Code OR

     Sales.Order_Code = Tracking.Order_Code ) 

Follow the dependencies.

That error is something you've explicitly coded. You've coded it to occur, based on this condition:

IF @Logon IN (SELECT CWOPALogin FROM...

Why use an ORDER BY with a SELECT INTO ? This does not gurantee anything about the physical ordering of the rows in the destination table.

The datatype of the result of an expression is dependant on the data types used in it. Either explicitly CAST() them, or tack on a decimal portion:

SELECT     SUM(weight * points)...

Use CASE ... WHEN for conditionals

For example, if you only want to include B.Billing_Time in the SUM() if B.Event_Type_ID <> 1810, then use:

SUM(

  CASE B.Event_Type_ID

    WHEN 1810 THEN 0  --...

Try this article:

http://www.sql-server-performance.com/nonclustered_indexes.asp

Check the section on selectivity, and order of columns in a composite index.

The optimizer makes decisions based on data distribution in the index and whether the index is 'selective' enough. It can't perform this reliably if statistics are not up to date...

If this is for an INSERT as stated, then it's obviously an attempt to generate an ascending sequence number, and Max() + 1 will only be safe if you keep the...