It seems the dual TOP/ORDER BY was the most efficient approach by the time.

I tested the new Denali OFFSET/FETCH and I got the exact same number of reads for @StartRow...

SELECTSTUFF(CONVERT(CHAR(10), GETDATE(), 101) + ' ' + SUBSTRING(CONVERT(CHAR(19), GETDATE(), 100), 13, 7), 15, 2, SUBSTRING(CONVERT(CHAR(8), GETDATE(), 8), 4, 5))

why? WHY?

A date is a datetime value, nothing else. What you want is a textual representation of a datetime value and you can accomplish that with CONVERT function. Look in...

In SQL Server the only three valid values for BIT is 1, 0 and NULL.

Only 0 will be interpreted as 0 and all other values will be treated as 1.

It's...

SELECTinv.Reference AS 'Invoice Reference',

inv.InvoiceDate AS 'Invoice Date',

DATEPART(YEAR, inv.InvoiceDate) AS InvoiceYear,

DATEPART(MONTH, inv.InvoiceDate) AS InvoiceMonth,

DATEPART(WEEK, inv.InvoiceDate) AS InvoiceCalWeek,

MAX(serv.CreatedDate)

FROMdbo.SalesInvoices AS inv

INNER JOINdbo.SalesOrders AS sales ON sales.[UID] = inv.DocumentID

INNER JOINdbo.ServiceOrders AS serv ON serv.DocumentID...

CREATE FUNCTION dbo.fnGetMilitaryDate

(

@Sample DATETIME

)

RETURNSCHAR(5)

AS

BEGIN

RETURN(

SELECTSUBSTRING(DATENAME(YEAR, @Sample) + DATENAME(DAYOFYEAR, @Sample), 3, 5)

)

END

How does the distribution of ColY values look like?

If the distribution is somewhat even, consider making the partitioning key on ColY value.

Maybe 10 partitions, ranging 0-(10, 10-(20, 20-(30, 30-(40, 40-(50,...

I have another thought... In what range does ColY varies within?

0-100?

If you make the ColY clustered, inserting a new record into the clustered index will make the index reorganize and while doing so, the index is locked and since clustered...

Alter column ColX to be a bigint identity NONCLUSTERED primary key.

Alter column Coly to be a REAL clustered column.

Then your selects will be really speedy! 😀

But then again, inserting new...

SELECTo.*

FROM@Overlap AS o

CROSS APPLY(

SELECT*

FROM@Overlap AS x

WHEREx.infoname = o.infoname

AND x.id <> o.id

AND x.effective <= o.termination

and x.termination >= o.effective

) AS f

How many records are we talking about? Maybe partitioning could help you.

How does the SELECT look like? Proper indexing will help you here.

You don't need a tvf here.

declare @pol_cd char(18), @val_date datetime

set @pol_cd = 'xx000000'

set @val_date = '2010/06/30'

...

Tom, that would be three simple steps

1) Replace 0 with '17530101'

2) Replace 6 with '17530107'

3) Replace 1900 with 1753

Done!

SELECTDATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - d.Delta, 0) AS StartOfYear,

DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) - 22790 - 12 * d.Delta, 1) AS EndOfYear

FROM(

SELECT0 UNION ALL

SELECT1 UNION ALL

SELECT2 UNION ALL

SELECT3

) AS d(delta)

ORDER...