Does the column really have to be (nvarchar (max))?

Created date is unicode?

Assuming this is all hypothetical and no one is really trying to create a unicode character date field or...

I might have over done the ISNULLs.

SELECT SUM(isnull(Pay_Records.hours,0)) AS Hours, isnull(Pay_Records.rate_type,'NONE') as rate_type,

...

Varchar (5) that's why.

The below does not throw an error.

dECLARE @date DATE,

@string VARCHAR(10)= '2015-01-01'

BEGIN TRAN

PRINT XACT_STATE()

BEGIN TRY

-- see if the data I've been given can be cast to something else

SET...

You are being generous and tactful when you say that updating MSSQL is not the only solution.

I seriously doubt that it is ever the solution unless SQL is broken.

In my...

Hope this is useful for people hunting through old but well named topics.

This works

declare @var varchar(max) = ' '

select len(@var)

This doesn't

declare @var varchar(max)...

You might get better CPU usage if you upgrade to 2014 due to the enhanced Statistics estimator.

But a patch most of the time won't improve performance.

High CPU occurs mostly when...

You havent defined columns in the result.

This example seems to not have syntax errors.

CREATE FUNCTION clrDynamicPivot

(

-- Add the parameters for the function here

@query nvarchar(4000),

@pivotColumn nvarchar(4000),

@selectCols nvarchar(4000),

@aggCols nvarchar(4000),

@orderBy nvarchar(4000)

)

RETURNS TABLE

(column1...

Has this Sproc ever executed in live?

What isolation level is running in the sproc?

An option is to look at isolation levels. If you can change the isolation level of the...

Either dynamic sql to get rid of all the the scan inducing OR's or I think you can use with recompile if it is in a proc.

consider partitioning in a way that inserts or updates happen to the latest partition only and doing page compression to the partitions that do not get any inserts or updates....

Is the arithabort setting on the db disabled? ssms might enable it and give youu data fast.

check whether the the proc is being executed with arithabort set to off in ssis. I am guessing ssms has it enabled.

apologies, had Arithabort on the brain. Should have been xactabort.

Don't know how to change topic or kill this post so will resubmit with proper description.

Unless I am mistaken, Trust is not an index property. It is a Foreign Key property.

It looks like a scan count > 0 occurs when access occurs against a non unique index or a table scan.

Probably because a seek against a non-unique non-clustered index is...