[Table Partitioning] Does WAIT_AT_LOW_PRIORITY work with Standard Edition?

(TL;DR - yes.  Yes, it does.)

As you hopefully know, back with SQL Server 2016 SP1, Microsoft opened the floodgates of formerly-only-enterprise-features (https://sqlperformance.com/2016/11/sql-server-2016/big-deal-sp1). 

But with caveats.

For instance, IMOLTP (in-memory online transactional processing, aka "in-memory") tables are limited in the amount of RAM used. 

But how about WAIT_AT_LOW_PRIORITY?  That was introduced in 2014 to make table partitioning deal better with That-Dude-From-Accounting-Who-Kicks-Off-A-Massive-Query-On-Friday-at-5pm, which causes partitioning to hang on Saturday when you're trying to add and remove partitions.

Specifically, the notes don't say anything about it.  The closest they get is:
"WAIT_AT_LOW_PRIORITY used with ONLINE=ON only."

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-2017

And ONLINE is an Enterprise-only feature.

So, does it or doesn't it?

For a simple example I actually used Aaron Bertrand's answer to a StackOverflow question:
https://dba.stackexchange.com/questions/155495/table-partitioning-in-sp1-for-sql-server-2016



So, what do we get?
Well, as pointed out by Joe Sack back in 2013(!), that feature will log to the Errorlog when it works. 
https://sqlperformance.com/2013/09/sql-indexes/lock-priority-sql2014

Let's try it out.  In the first window create the objects.  Once created, start a second window and open the transaction.  Then back to the first and run the SWITCHes and see what happens!

...Yup! It works in Standard!

Here are the messages I got:

Date 5/7/2019 3:41:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message
An ALTER TABLE SWITCH statement was executed on database 'eif_workspace', table 'partitionexample' by hostname 'CH004547', host process ID 30812 with target table 'staging_FactInternetSales' using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 1 and ABORT_AFTER_WAIT = BLOCKERS. Blocking user sessions will be killed after the max duration of waiting time.

, followed a minute later by: 

Date 5/7/2019 3:42:03 PM

Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message

An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 7, object_id = 1182627256. All blocking user sessions will be killed.

and 

Date 5/7/2019 3:42:03 PM
Log SQL Server (Current - 3/23/2019 1:06:00 AM)

Source spid61

Message
Process ID 63 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 7, object_id = 1182627256.

I was figuring/hoping it'd work, but that example using ONLINE = ON gave me pause.
Proof, and a blog post!

[Partitioned Tables] Does the clustered index take up space if referenced?

Setting up a new partitioned table with associated indexes. I was curious whether the adding the partitioning key to any index would cause the index to grow - I expected not, but you never know.

Our clustered index:

create unique clustered index clustind_pk on ourtable (id, partitionedkey)

Our test indexes:

create nonclustered index A on ourtable (partitionedkey, fielda)
on ps_daily (partitionedkey)

create nonclustered index B on ourtable (fielda)
on ps_daily (partitionedkey)

create nonclustered index C on ourtable (fielda) include (partitionedkey)
on ps_daily (partitionedkey)

Then used this query to check a particular partition for all 3 indexes (thanks to Simon Sabin). Each was within 1 page of the others.

select OBJECT_NAME(p.object_id ), i.name,p.*
from sys.dm_db_partition_stats p
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE p.object_id = 2071234567
AND partition_number = 28