• A good question, that made me think twice.

    Two minor issues though.

    1. The negation in the question is a needless extra complication. It would have been better to ask which of the listed rules are correct. (Even after realizing that the question asked for incorrect, I still found myself ticking the correct ones - human psychology works like that).

    2. The rule "Partitions can be on any column" is ambiguous. I was sure I had the rest right, but felt like taking a 50% gamble with this one - did the question author want this to be interpreted as "There are restrictions on the type of partitioning column you choose in both tables", or as "There are no restrictions no the type of partitioning column chosen, except that it has to be the same in both".

    The text written by Microsoft in the referenced Books Online article is also a bit strange. First, it says: "The tables must have the same columns with the same names and the same data type, length, collation, precision, scale, nullability". And then it says: "Nullability of the partitioning columns must match". Yeah, I know. That was already implied by the requirement that nullability of ALL columns must match:-P


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/