jgale 70688 (4/13/2015)

---

Thank you for the replies.

Both solutions did work. However, the data I'm working with is slightly more complex than my simple example. My main data table is a...

Grant Fritchey (4/13/2015)

---

It made me curious, so I put this together real quick:

SELECT a.City,

a.PostalCode

FROM Person.Address AS a

WHERE ...

Eirikur Eiriksson (4/13/2015)

---

ScottPletcher (4/13/2015)

---

Here's an alternative approach, also using CROSS APPLY, that can take advantage of the (presumed) clustered index on prefix.PREFIX_VALUE; no guarantee, but it might perform better.

SELECT...

It's...

Here's an alternative approach, also using CROSS APPLY, that can take advantage of the (presumed) clustered index on prefix.PREFIX_VALUE; no guarantee, but it might perform better.

SELECT p_cross_apply.PREFIX_VALUE, SUM(d.DATA_VALUE) AS...

Go ahead and add DISTINCT to each separate query on which it's applicable:

SELECT DISTINCT omlSalesOrderID

FROM m1_dc.dbo.SalesOrderLines

WHERE omlPartID='finalmile'

UNION

SELECT ompSalesOrderID

FROM m1_dc.dbo.SalesOrders

WHERE ompShippingMethodID='JBFM'

In general, since you want to avoid grouping by varchar columns anywhere, as it's not very efficient, delay converting numeric codes until the outer-most query you can.

select DB_NAME(a.database_id) AS [Database...

Off the top of my head, I would use a temp table here, something like below. I don't have time to finish all the median values in the SELECT,...

Eric Prévost (4/9/2015)

---

jdbrown239 (4/9/2015)

---

Thanks everyone! Putting the command on one line worked but now I have another problem.

When the job runs it fails with the error: 'Procedure IndexOptimize, Line 505...

If the string is within SSMS, you could try adding a backslash to the end of each line to continue the string. For example:

declare @varchar varchar(30)

set @varchar = 'abcdef'

print...

CELKO (4/8/2015)

---

I gave you a simple LIKE predicate to assure this, as per your request. I thought ...

CROSS APPLY often works well in these types of lookups:

SELECT *

FROM @vehicle v

CROSS APPLY (

SELECT TOP (1) *

FROM @vehicleclass vc

...

I think those are also uniqueidentifier columns, so fragmentation is to be expected. Unless you retain lots of backup info in msdb, and I would not recommend that, then...

I think those are also uniqueidentifier columns, so fragmentation is to be expected. Unless you retain lots of backup info in msdb -- and I would not recommend that...

You need to review the choice of clustering key; the clustering key is critical to performance, so never be lazy enough to allow the clustering key to default to identity....