Jake Shelton (5/19/2014)

---

Grant Fritchey (5/17/2014)

---

SQLRNNR (5/16/2014)

---

Grant Fritchey (5/16/2014)

---

To address the scan, you need to either have a filter of some kind to reduce the amount of data, or fix the...

Here's another guess;

DROP TABLE #Equipment;CREATE TABLE #Equipment ([Equipment ID] VARCHAR(10), [Equipment Branch] VARCHAR(20))

INSERT INTO #Equipment ([Equipment ID], [Equipment Branch])

VALUES ('CAG001','BRISBANE'),('CAG002','TOWNSVILLE'),('CAG003','PERTH'),('CAG004','BRISBANE'),('CAG005','PERTH'),('CAG006','PERTH')

DROP TABLE #Asset;CREATE TABLE #Asset ([Equipment ID] VARCHAR(10), [Asset Branch] VARCHAR(20))

INSERT...

Kevlarmpowered (5/16/2014)

---

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns

FROM table

WHERE (column5 = '1'...

The only difference I can spot between the two UNIONed queries is this filter:

prev_stat.DW_POLICY_STATUS IN (7) or prev_stat.DW_POLICY_STATUS IN (8)

If this is the case, then combine the two queries:

SELECT...

-- set up some sample data

DROP TABLE #t1

CREATE TABLE #t1 (Table1ID INT IDENTITY(1,1) PRIMARY KEY, DenormalisedString VARCHAR(8000))

INSERT INTO #t1 (DenormalisedString) VALUES ('Red,Green,Blue'),('Red,Green'),('Red,Blue'),('Green,Blue')

DROP TABLE #t2

CREATE TABLE #t2 (Table2ID INT IDENTITY(1,1) PRIMARY...

MathewK (5/14/2014)

---

Thanks much Chris. You are awesome! 🙂 Thanks for working through my unclear specs and data. My apologies.

Your solution works well on larger sample size.

Could you mention the changes...

Implicit conversions in joins:

[Incentives].[dbo].[tblVehicleTurnoverSummary].[ModelCode]=CONVERT_IMPLICIT(nvarchar(3),[Incentives].[dbo].[tblInvoiceNotRegisteredHeader].[ModelCode],0) AND [Incentives].[dbo].[tblVehicleTurnoverSummary].[Chassis]=CONVERT_IMPLICIT(nvarchar(8),[Incentives].[dbo].[tblInvoiceNotRegisteredHeader].[Chassis],0)

?Referenced twice.

[Sales].[dbo].[tblRegHist].[PK_tblRegHist_1__17] has no clustered index and a RID lookup. Add an appropriate clustered index and a covering index.

As Sean pointed out, joining views to...

This query

SELECT COUNTRY_CODE_ALPHA2,

e.*

into #temp3

FROM SECURITY_EVENTS_STG_TEST e

INNER JOIN dbo.IP2COUNTRY_LOOKUP c

ON e.SOURCE_ADDRESS_INT BETWEEN c.[START] AND c.[END]

With a covering index on SECURITY_EVENTS_STG_TEST.SOURCE_ADDRESS_INT

Results in seeks with no (relatively expensive) residual predicate:

Seek Keys[1]:

Start: [dbo].[SECURITY_EVENTS_STG_TEST].SOURCE_ADDRESS_INT...

set statistics io, time on

PRINT 'Original query'

SELECT

(SELECT COUNTRY_CODE_ALPHA2

FROM dbo.IP2COUNTRY_LOOKUP C

WHERE START = (SELECT max(START)

FROM DBO.IP2COUNTRY_LOOKUP

WHERE START <= E.SOURCE_ADDRESS_INT)

AND "END" >= E.SOURCE_ADDRESS_INT) AS SRC_COUNTRY_CODE_ALPHA2,

E.*

INTO #Temp1

FROM SECURITY_EVENTS_STG_TEST E

print '====================================================================================='

PRINT 'Possible replacement...

bas_vdl (5/15/2014)

---

spaghettidba (5/15/2014)

---

You cannot avoid the scan: you're asking for the whole SECURITY_EVENTS_STG_TEST table (no filter predicates).

As far as the index spool is concerned, I have no idea what it's...

Can you please post the CREATE TABLE script for the source table, including any constraints/defaults/indexes? Cheers.

bas_vdl (5/15/2014)

---

Thanks for the quick reply. Your query is slightly quicker, it takes between 15 and 20 seconds on my dataset...

Are there any other query construction to lookup the country...

spaghettidba (5/15/2014)

---

You cannot avoid the scan: you're asking for the whole SECURITY_EVENTS_STG_TEST table (no filter predicates).

As far as the index spool is concerned, I have no idea what it's trying...

SQL Server will join tables in whatever order results in the lowest-cost plan. Join order doesn’t matter. The order of ON clauses matters – and can be manipulated to...

Viky123 (5/15/2014)

---

Declare @A AS NVARCHAR(16)

SET @A='1'

Select Case WHEN ISNUMERIC(@A)=1 THEN Cast(@A AS int)

ELSE @A

END

This code is working fine but,

Why below code give me error also please...