Sql-server – How to combine the input of three parameters into one

No, there is no documentation from Microsoft guaranteeing the behavior, therefore it is not guaranteed.

Additionally, assuming that the Simple Talk article is correct, and that the Concatenation physical operator always processes inputs in the order shown in the plan (very likely to be true), then without a guarantee that SQL Server will always generate plans that keep the same the order between the query text and the query plan, you're only slightly better off.

We can investigate this further though. If the query optimizer was able to reorder the Concatenation operator input, there should exist rows in the undocumented DMV, sys.dm_exec_query_transformation_stats corresponding to that optimization.

SELECT * FROM sys.dm_exec_query_transformation_stats 
    WHERE name LIKE '%CON%' OR name LIKE '%UNIA%'

On SQL Server 2012 Enterprise Edition, this produces 24 rows. Ignoring the false matches for transformations related to constants, there is one transformation related to the Concatenation Physical Operator UNIAtoCON (Union All to Concatenation). So, at the physical operator level, it appears that once a concatenation operator is selected, it will be processed in the order of the logical Union All operator it was derived from.

In fact that is not quite true. Post-optimization rewrites exist that can reorder the inputs to a physical Concatenation operator after cost-based optimization has completed. One example occurs when the Concatenation is subject to a row goal (so it may be important to read from the cheaper input first). See UNION ALL Optimization by Paul White for more details.

That late physical rewrite was functional up to and including SQL Server 2008 R2, but a regression meant it no longer applied to SQL Server 2012 and later. A fix has been issued that reinstates this rewrite for SQL Server 2014 and later (not 2012) with query optimizer hotfixes enabled (e.g. trace flag 4199).

But about the Logical Union All operator (UNIA)? There is a UNIAReorderInputs transformation, which can reorder the inputs. There are also two physical operators that can be used to implement a logical Union All, UNIAtoCON and UNIAtoMERGE (Union All to Merge Union).

Therefore it appears that the query optimizer can reorder the inputs for a UNION ALL; however, it doesn't appear to be a common transformation (zero uses of UNIAReorderInputs on the SQL Servers I have readily accessible. We don't know the circumstances that would make the optimizer use UNIAReorderInputs; though it is certainly used when a plan guide or use plan hint is used to force a plan generated using the row goal physical reordered inputs mentioned above.

Is there a way to have the engine process more than one input at a time?

The Concatenation physical operator can exist within a parallel section of a plan. With some difficulty, I was able to produce a plan with parallel concatenations using the following query:

SELECT userid, regdate  FROM (  --Users table is around 3mil rows
    SELECT  userid, RegDate FROM users WHERE userid > 1000000
    UNION 
    SELECT  userid, RegDate FROM users WHERE userid < 1000000
    UNION all
    SELECT userid, RegDate FROM users WHERE userid < 2000000
    ) d ORDER BY RegDate OPTION (RECOMPILE)

So, in the strictest sense, the physical Concatenation operator does seem to always process inputs in a consistent fashion (top one first, bottom second); however, the optimizer could switch the order of the inputs before choosing the physical operator, or use a Merge union instead of a Concatenation.

Well, you could consider a filtered index - if you're always looking for rows where IsSynchronized = 0 and this number should be relatively small, then instead of those two indexes, consider this instead:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized] 
  ON [dbo].[PackageEvents] ([PackageID])  
  INCLUDE ([EventDate], [EventDescription], [EventID], 
    [LastSyncDate], [Notes], [UserName], [Version]) 
  WHERE IsSynchronized = 0;

Of course you may want to make that even smaller and test to see the difference in impact if the query has to look up the data (should be pretty efficient if the number of rows is small), so - assuming PackageID is the clustering key:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized] 
  ON [dbo].[PackageEvents] ([PackageID])
  WHERE IsSynchronized = 0;

The overhead of maintaining this index may very well be worth the space savings compared to a full index, especially if it's only being used to optimize this query (or query pattern, at least).

Filtered indexes are not magic, though; JNK brought up some limitations below:

Caveats with filtered indexes - stats may not stay up to date without maintenance, and you need to use "standard" values for some settings like QUOTED IDENTIFIER and ANSI NULLS. These are small issues but if you have the settings wrong in a session that inserts into the index, the insert will fail.

Also you'll want to read these posts:

• How filtered indexes could be a much more powerful feature
• Optimizer Limitations with Filtered Indexes
• An Unexpected Side-Effect of Adding a Filtered Index

If you don't want to use a filtered index, you can probably test variations of these:

CREATE NONCLUSTERED INDEX [IX_Covering_try1] ON [dbo].[PackageEvents] 
  ([PackageID], IsSynchronized)  
INCLUDE ([EventDate], [EventDescription], [EventID], 
  [LastSyncDate], [Notes], [UserName], [Version]);

CREATE NONCLUSTERED INDEX [IX_Covering_try2] ON [dbo].[PackageEvents] 
  (IsSynchronized, [PackageID])  
INCLUDE ([EventDate], [EventDescription], [EventID], 
  [LastSyncDate], [Notes], [UserName], [Version]);

(For a long time I thought that including BIT columns in the key was wasteful but Martin Smith demonstrated a case where it worked quite well - worth a try. I can't find the post now.)

Without your full schema, data, query patterns etc. we can only guide you and have you test our suggestions in your environment. We can't say, "Ding! This is the one that will work for you!"