have you ever tried asking on interview what kind of results the following statement will produce:

SELECT COUNT(*)

:w00t:

there is...

I am very suspicient that you doing something wrong there....

But anyway, here is the answer:

DECLARE @ref AS CHAR(18)

SET @ref = REPLACE('8982214567.9999-Q199999999','-','.')

SELECT PARSENAME(@ref ,3) Part1,PARSENAME(@ref ,2) Part2,PARSENAME(@ref ,1) Part3

...

-- Query 4

SELECT c.*, o.*, ol.*

FROM #Customers c

LEFT JOIN #Orders o

INNER JOIN #Orderlines ol

ON ol.OrderID = o.OrderID

...

one of the manyways:

;WITH sample_data(val)

AS

(

SELECT '1.9999-Q1'

UNION SELECT '01.9999-Q11'

)

SELECT *, PARSENAME(x.namelike,3) Part1,PARSENAME(x.namelike,2) Part2,PARSENAME(x.namelike,1) Part3

FROM...

I wouldn't call the above (my) code "beautiful", it looks like a pot...

...

Now I know the obvious way to find out if this is best or not is by testing the different options. I was wondering if there was a rule...

Add this to the end of your query (you better use code="sql" tag to format your code when posting here, otherwise your query end up as single line...):

;WITH tall

AS

(

SELECT *,...

Use Windows Authentication, and forget about "sending" user/password to SQL Server...

Eirikur Eiriksson (6/17/2014)

---

Two solutions, first one for 2005 and later, the second 2012 and later. The difference is that the first one uses self-joining which makes it less efficient. For...

Yep, Gail's article conclusion tells that it doesn't matter what you type between SELECT and FROM in EXISTS query, as long it represents valid T-SQL sytax, SQL server always...

Apply your currency rate conversion before PIVOT:

....

(SELECT TrnYear, TrnMonth, CASE WHEN PostCurrency = 'GBP' THEN PostValue ELSE PostValue / PostConvRate END AS PostValue

FROM [ApInvoicePay]

Where TrnYear >= Year(...

Sorry Luis, help with what?

First of all it is a bit different topic. Second...

I know it is a very old thread, however, quite often, this exactly discussion comes up in different work places...

Is anyone have any material evidence that one syntax is better/faster...

I would say that it's very arguable statement...

😉

Create a dedicated view for this or use dynamic SQL to build relevant query, as in your case INNER JOINs in the MASTER query ensures that complete "data-tree" exists.

If...