If myTable is actually a complicated view, try avoiding subqueries and use a derived table instead:

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

LEFT JOIN

(

SELECT DISTINCT T1.ReferenceNumber

FROM myTable T1

WHERE T1.ReferenceNumber NOT LIKE 'TR%'

AND...

How does this do?

SELECT UID, ReferenceNumber, Type

FROM myTable

WHERE ReferenceNumber NOT LIKE 'TR%'

AND Type = 1

UNION ALL

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

WHERE T.ReferenceNumber NOT LIKE 'TR%'

...

Try:

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

WHERE T.ReferenceNumber NOT LIKE 'TR%'

AND

(

T.Type = 1

OR NOT EXISTS

(

SELECT *

FROM myTable T1

WHERE T1.ReferenceNumber = T.ReferenceNumber

AND T1.Type = 1

)

)

ORDER BY UID

It will probably be more efficient to PIVOT tblCommon_tblSkills.

Something like:

;WITH Skills

AS

(

SELECT Common_ID, [1],[2],[3],[4],[5],[6],[7]

FROM

(

SELECT Common_ID, Skill_ID

FROM tblCommon_tblSkills

) P

PIVOT

(

COUNT(Skill_ID)

FOR Skill_ID IN ([1],[2],[3],[4],[5],[6],[7])

) A

)

SELECT C.*

,CASE WHEN S.[1] = 1 THEN 'Yes' ELSE...

There is no need for joins with deletes.

Try the standard ANSI syntax:

DELETE HouseFacility

WHERE EXISTS

(

SELECT *

FROM Facility F

WHERE F.[Id] = HouseFacility.FacilityId

AND F.FacilityGroupId = ?

)

AND EXISTS

(

SELECT *

FROM HouseFacilityGroup G

WHERE G.[Id] = HouseFacility.HouseFacilityGroupId

AND...

You need to UNPIVOT to get the attribute and then PIVOT to get the attribute values in the same row.

The only order in SQL is what is specified in the ORDER BY clause. This means that you will have to some of the work

in the front end. An UNPIVOT...

SELECT lfisno, Stockcode, quantity

,CAST(quantity * 1.0 / SUM(quantity) OVER (PARTITION BY lfisno) AS decimal(6,2))AS rate

FROM

(

SELECT lfisno, Stockcode

,SUM(quantity) AS quantity

FROM test

GROUP BY lfisno, Stockcode

) D

I would start by downloading, for free, the Express versions of SQL Server and Visual Web Developer from

http://msdn.microsoft.com

Then I would look at

http://www.asp.net

where there is information to get you started.

You could...

To get parallel processing to work, I think you will either have to combine all your queries into one with something like UNION ALL, or write an async CLR SP.

Also,...

Use the ROW_NUMBER() function to work out which row the information should be on.

Also, PIVOT tends to be more readable than lots of CASE statements.

Try something like:

SELECT SYear, SWeek

,[2] AS...

The problem may be that all 1.3 million rows are being sent to the MSSQL instance before the filter is applied.

This could take a long time on a slow link.

Try:

1....

On the limited amount of informaton provided, it is difficult to say how to improve the performance.

The main problem is the CHARINDEX(@query, a.account) > 0, or the equivalent a.account...

On looking at it again, maybe the following:

SELECT

CASE

WHEN N.N = 1

THEN 'FreeSpace'

ELSE 'UsedSpace'

END AS Measurement

,CASE

WHEN N.N = 1

THEN FreeSpaceMB

ELSE UsedSpaceMB

END AS SizeInMB

FROM

(

SELECT FreeSpaceMB, UsedSpaceMB

,ROW_NUMBER() OVER (ORDER BY EntryDateTime DESC) AS...

I am not sure what you are trying to do. If you want the latest information for each drive then try something like:

SELECT fkServerID, DriveLetter, FreeSpaceMB, UsedSpaceMB

FROM

(

SELECT fkServerID, DriveLetter, FreeSpaceMB,...