If you always have a space in the middle of your postcode, and you want to match everything to the left of the space, change your join predicate to this:

LEFT(N.[Office...

Mike

Three reasons, really:

(1) To minimise physical fragmentation. If your file grows in small chunks, you're likely to have bits of it scattered all over the disk.

(2) To avoid having...

Your business rules don't make any sense to me. Why, when you have different values in ReferenceTable2 for City and for State, do the two rows in your expected...

Find a SQL Server instance that isn't doing anything - a test instance on your desktop/laptop for example. In SSMS, go to Tools -> Options -> SQL Server Object...

Gosh, the goalposts have moved for a second time. It's not the LEFT JOIN - just put a DISTINCT after the SELECT. That will give the results you...

Benki Chendu (9/27/2016)

---

We have peer to peer replication configured in SQL 2014.

1. Could the new column be introduced in the publisher newly which isnt getting pushed to the subscriber. Creation/modification...

SELECT

s.Name

,s.City

,s.State

,r1.ReferenceName1

,r1.Address1

,r1.Address2

,r2.ReferenceName2

,r2.City AS R2_City

,r2.State AS R2_State

FROM #SourceData s

LEFT JOIN #ReferenceTable1 r1 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r1.ReferenceName1,CHARINDEX(' ',r1.ReferenceName1))

LEFT JOIN #ReferenceTable2 r2 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r2.ReferenceName2,CHARINDEX(' ',r2.ReferenceName2))

John

If you're converting to datetime in order to store in your table, you don't...

Lat

Why are you doing this row-by-row? Why not update everything in one go?

John

I've always thought that it would be good if you could shrink the file sizes as part of the restore. Unfortunately, you can't, and so you need the same...

Apologies - I misread. I answered as if the clustered index were on all columns instead of just on ID.

John

Mike

I know technically a table without...

Although if you're going down from 2008 R2 to 2008, that probably won't work. Don't know for sure, though - I've never tried it.

John

In which case, it's likely you'll need to accept some data loss to get this fixed. But we can't be sure of that until you do what it suggests...

Mike

No, because if you only have a non-clustered index, your table is a heap. Also, in the non-clustered index, the key is only ID instead of all of the...