When optimizer parses query it cannot tell how many records will be returned.

So it treats SELECT as an array, no matter what you have in mind.

Table function desined to return...

SET ROWCOUNT @IDCount

select agent_id

INTO #Foo

from t_1'

SET RWCOUNT 0

select ID from t_Test where ID not in (select agent_id from #Foo)

Or just:

Set @AgeAtWildParty = DateDiff(dd, @BirthDate, @DateOfWildParty) / 365.25

You are trying to supply a table as a parameter.

Does not matter that this table has only 1 row, it's a table anyway.

And it's not allowed because SQL Server does...

There are only 19 of them:

sysobjects

sysindexes

syscolumns

systypes

syscomments

sysfiles1

syspermissions

sysusers

sysproperties

sysdepends

sysreferences

sysfulltextcatalogs

sysindexkeys

sysforeignkeys

sysmembers

sysprotects

sysfulltextnotify

sysfiles

sysfilegroups

Topic "System tables" in BOL.

It's not about performance.

The logic of your queries is completely wrong.

You are receiving wrong results no matter how fast. Unless you have only 2 different ContractId's.

INNER JOIN

Would you mind to read at least BOL?

Article "Using EXPLICIT Mode" for example?

Or some basic books about XML in SQL 2000?

 CONVERT(DECIMAL(10,2), ROUND(tp.parhrs*2, 0)/2 )  AS dhrs

See BOL for xp_cmdshell.

The value in that column must be not null but empty string.

'' is not NULL.

Try this:

 where len(ResourceEnterpriseRMV21ID) > 0

Create Table Address 

(Id int, Street Varchar(40), City Varchar(20), State Char(3), Country Varchar(30)

)

Create Table Customer

(CCode char(3) Not Null  Primary key, CName Varchar(50) Not Null, CAddressId int,

 CONSTRAINT FK_Customer_Address FOREIGN KEY (CAddressId) REFERENCES Address (Id)

)

select Job_Id, MIN(End_Job_Time - Start_Job_Time) as MinDuration,

MAX(End_Job_Time - Start_Job_Time) as MAXDuration,

AVG(End_Job_Time - Start_Job_Time) as AVGDuration,

YEAR(Start_Job_Time) as StartYear, MONTH(Start_Job_Time) as StartMonth

FROM JobRecords

GROUP BY Job_id, YEAR(Start_Job_Time), MONTH(Start_Job_Time)

Use...

What exactly does not work?

What are trying to do?

Any exaple of your code having problem?