I can't test these since you didn't provide directly usable test data:

--if you need only the acct_ids
SELECT acct_id, COUNT(*) OVER() AS total_accts
FROM my_table
GROUP BY acct_id
HAVING MIN(street +...

As noted by Brian, your table design is completely wrong.  You've then had to corrupt the data to fit it into the bad table structure.

For example, the language is "English". ...

I think there is one way you can try the upgrade path.

IF your san/disk/software allows you to snapshot a given point-in-time on disk, and accurately restore everything on disk (including...

OP = "original poster", i.e., the person that originally asked the q (or, rarely used and only by some people, "original post")

The actual database and logfiles should preferably be kept on same drives as they are now.

The most simple approach I can come up with is:

• Detach the database in...

I think you're correct, you should do this for all tables.  No sense wasting resources scanning a 900-row table either.  Of course that 900 rows could be far...

I think you're correct, you should do this for all tables.  No sense wasting resources scanning a 900-row table either.  Of course that 900 rows could be far larger than...

Hmm, I wouldn't think there'd be a direct money charge just from having to reread something into RAM.  Then again, that could change by vendor I guess (we use MS).

But...

/*INSERT INTO dbo.input_Main_data ( ... )*/
SELECT new_rows.*
FROM dbo.input_Main_data iMd1 
CROSS APPLY (
    SELECT TOP (1) *
    FROM dbo.input_Main_data iMd2
  ...

Yes, I...

I see the big issue as one of less RAM.  I suspect the max ram you receive will be far less than what you'd get in an on-premises db.

When fragmentation...

Here's a method I often use.  "Ideal", don't know.

Use full backups and differentials.  Generally a differential restores very quickly (you can do the "base" restore of the full backup ahead...

If hyperthreading is present (most CPUs use this nowadays), set MAXDOP to no more than half the cores, in your case 4.

If NUMA is being used, set it to no...

SQL row/page compression is typically extremely helpful to reduce disk space.  Before SQL 2016, I believe you must have Enterprise Edition for it to be available.

In the main table storage area, a row will be on one and only one page.  Any other data for that row must go to overflow pages.

-1- There's not an...