andrew gothard (3/29/2014)

---

Simple arithmetic, if you have 8k pages, one row per page (and we know that it's going to be bigger than that), 12500 rows. Table Lock (if...

Ignoring any potential performance issues, a CROSS APPLY can do what you want fairly easily. Simply indexing the table properly can often deal with that. Or you can...

SELECT

COALESCE(a.ID, b.ID) AS ID,

COALESCE(a.NAME, b.NAME) AS NAME,

CASE WHEN a.UpdateDate > b.UpdateDate THEN a.UpdateDate ELSE b.UpdateDate END...

andrew gothard (3/27/2014)

---

ScottPletcher (3/27/2014)

---

andrew gothard (3/27/2014)

---

ScottPletcher (3/27/2014)

---

yuvipoy (3/27/2014)

---

Sean Lange (3/27/2014)

---

True. I have worked with this OP on several threads and the problem is performance because they have several related tables...

I think you can simplify the query something like below, although some tweaking might need done:

SELECT

s.state_name,

derived.NumOfDupPermits, derived.AmendedDups, derived.DistinctPermits

FROM (

...

andrew gothard (3/27/2014)

---

ScottPletcher (3/27/2014)

---

yuvipoy (3/27/2014)

---

Sean Lange (3/27/2014)

---

True. I have worked with this OP on several threads and the problem is performance because they have several related tables and the primary...

Hmm, I think the log file should...

yuvipoy (3/27/2014)

---

Sean Lange (3/27/2014)

---

True. I have worked with this OP on several threads and the problem is performance because they have several related tables and the primary key (and clustered...

I think you're looking for LEFT OUTER JOIN, something like this:

SELECT

c.CaseID, c.Resolution,

COALESCE(CAST(s.CaseID AS varchar(10)), 'DUMMY-' + c.Resolution) AS SubCaseID

...

I don't see any need for a pre-built proc for:

Get All of Foo

Just SELECT the columns you need from the table instead.

For the rest, if you're willing to use dynamic...

Based on the query plan, I think two these things 100% need done and should help:

1)

Table: SubOrderTrans

Index: idx_subordertransday

INCLUDE column SubOrderTransDeleted in that index

2)

Table: SUBORDER_ETA_CORRECTIONS

Index: IDX_NC_SubOrderID

Change it to a clustered index,...

Current query is not SARGable and is somewhat convoluted as well.

SELECT DISTINCT TOP 7

Convert(DateTime, Datediff(Day, 0, DateCreated)) AS DateCreated,

Datepart(dw,DateCreated) AS WeekNum...

Grant Fritchey (3/25/2014)

---

ScottPletcher (3/25/2014)

---

Grant Fritchey (3/25/2014)

---

ScottPletcher (3/25/2014)

---

Grant Fritchey (3/25/2014)

---

I can't see the query since it exceeds the size of what's stored in the plan, so suggestions from the plan alone...

Roughly like below. You may have to adjust the "SINGLE_NCLOB" to "SINGLE_CLOB", depending on the format of the input file.

DECLARE @file_text varchar(max)

SELECT @file_text = file_text

FROM OPENROWSET(BULK 'd:\full\path\to\file\filename.txt', SINGLE_NCLOB) AS...

Gotta' admit, I prefer working in SQL/TSQL, so even for (moderately) large text files I'll often just do an:

OPENROWSET(BULK '<path_and_file_name>', SINGLE_CLOB) --or NCLOB, if req'd

to load the whole file at...