adonetok (9/13/2012)

---

I use one store procedure to select data as below code:

select * from ORDER where city='London'

In asp.net app, one dataset will be created based on this store procedure.

How to...

You don't need to use UDF, there is in-build format for T-SQL CONVERT function:

select convert(varchar, GETDATE(), 107)

Man, you do really need to read the previous posts.

It's only going to work if you use dynamic sql.

Please note, if you run the following type of query...

Sorry, I completely forgot this one. Could you please remind me...

It takes some time to update statistics....

What are you doing exactly in your stored proc?

It can be well justified if it does modify big chunk of a table.

I like this one, as it's very different approach, but yeah it's still much slower.

I...

Nowadays, it's not considered as the best practice. However, there are cases where the use...

ChrisM@Work (9/12/2012)

---

A completely different method, courtesy of Dwain C:

;WITH SourceNums AS (

SELECT strcol FROM (VALUES ('1'),('2'),('5'),('7'),('8'),('9')) a (strcol)

),

nTuples (n, Tuples) AS (

SELECT 1, CAST(strcol...

...

The only way to make it more efficient is to shortcut the math. That's why some are hard-coding 3 as the final digit. (It only saves one step...

Have a look my example. The problem is here:

LEFT JOIN dbo.TBL_VALUATIONS v ON a.pr_id = v.pr_id

WHERE v.date_asset IN

(SELECT MAX(a.date_asset)

FROM TBL_VALUATIONS a LEFT JOIN TBL_PROPERTY b

ON a.pr_id = b.pr_id

GROUP BY a.pr_id)

You...

Greg Snidow (9/12/2012)

---

Jeff Moden (9/12/2012)

---

It's even more annoying when you need it and it's not there....

And the winner is the one you didn't include in your test:

DECLARE @ddatetime2

SET @d = SYSDATETIME()

;with a (num)

as

( select * from (values...

I've tried all three methods, INNER JOINs, CROSS APPLY and CROSS JOIN with WHERE: all produce very similar query plans with use of Nested Loops (Inner Join).

Cannot see much...

Cheating? No, it's a common...

Your query for MAX(Date) should be linked somehow to entities you are querying above.

I'm not 100%, may be that way:

ELECT

a.id,

a.new_id,

a.add_id,

b.add_1,

b.add_2,

b.add_3,

c.town,

d.county,

e.val,

e.date

FROM TBL a

LEFT JOIN TBL_ADD b ON a.addir = b.addid

LEFT JOIN...