If you can, give this task to DBA.

DBA can switch server into Bulk-logged or Simple Recovery model (if it's currently set to a Full Recovery ) and then use SELECT...

Thank You for the table & data setup. For the future please use ISO date format as we are not all Americans here 😉

Now the query for you:

;with LastInFlows

as

(

select nf.ClientCode,...

Try this:

DECLARE @ProductId NVARCHAR(30)

SET @ProductId = 'AZXXX423AA';

WITH ProductComponents AS

(

SELECT

s.Product,

s.Component,

s.Quantity

FROM Structures AS s

INNER JOIN Products AS p...

It is very easy to write query which returns requied number of records, just add TOP #OfRequiredRecords straight after SELECT keyword 😀

Seriously, it's not clear from your table structure what...

Let say you have table like:

CREATE TABLE [dbo].[MyTestTable]

(

[RecId] [int] NOT NULL,

[Val] [nchar](10) NULL,

CONSTRAINT [PK_MyTestTable] PRIMARY KEY CLUSTERED

(

[RecId]...

This version of recursive CTE is based on assumption that the Structures.Component is a parent of other parts (products).

WITH ProductComponents AS

(

SELECT

p.Product,

p.ProductId AS ParentProductId,

...

Your script is one huge single line of text!,

Could you please try to make it a bit more usable.

Thanks.

Another version (without sub-select & join):

select id, location

from tblTest

order by

(case location when 'LIVERPOOL' then 1

...

Try this:

select id, location

from tblTest t

left join (select 1 oid,'LIVERPOOL' o union all select 2, 'SEFTON' union all select 3, 'KNOWLSEY') ot

on ot.o = t.Location

order by ISNULL(ot.oid,10),...

Using INTs (with same index structure) would reduce number of reads anyway.

However I agree, the changing legacy system is not a viable option in your case.

In SQL2008 you can do this:

',ORGANISATION = ' + convert(varchar, cast(@FIELD18 as varbinary ),1)

In SQL2005 you need to use system function:

',ORGANISATION = ' + master.dbo.fn_varbintohexstr(cast(@FIELD18 as varbinary ))

See, you will...

I prefer "Three Men in a Boat (To Say Nothing of the Dog)" by Jerome K. Jerome.

Oops, it is nothign to do with T-SQL, sorry.

😀

john.delahunt (5/26/2010)

---

...I can see how the change of data types would make the comparisons quicker, but I think the real problem is the number of reads being done by the...

It would be long time...

Just to make sure...

I am not advising to store date in YYYYMMDD format (or other date format) as INT (that would be stupid).

Try SELECT CAST(GetDate() AS INT)

You will see...