Table Variables - Serial plan upon data modification statements and OPTION(RECOMPILE)

Here is something to be aware of:
When you have data modification statements

(like, INSERT,UPDATE,DELETE) against a table variable,
The SQL Server will not use a parallel plan, it will use a serial plan.
This can affect the performance, so keep it in mind.

Although, there is a workaround:
INSERT INTO @Table
EXEC('SELECT .. FROM ...')

Additionally,
Until SQL Server 2019 which has "Table variable deferred compilation" feature,
the estimated number of rows of table variables was always 1.
Why? When a query/SP/batch is compiled and optimized, the table variable is empty.

If you wanted to have proper estimations then you need to use 

OPTION(RECOMPILE) hint or use trace flag 2453, 

so the optimize will use real row count.

Note, OPTION(RECOMPILE) hint will not always work when

you join the table variable to a table and have 1 to many relationship. 

See https://www.sqlservercentral.com/blogs/should-i-use-optionrecompile for more details.

Also, the situation is different when you have some filters on your table variable.
Because there are no statistics on the table variables, 

SQL Server will use the following rules for the estimation of rows:

- When there is equal filter the estimates will be 10 % out of total rows,
- When there are inequality filters, like <, >, <> the estimates will be 30 % out of total rows
- When there is an equal filter on a bit field the estimates will be 50 % out of total rows

Source articles:
https://www.brentozar.com/archive/2017/02/using-trace-flag-2453-improve-table-variable-performance/
https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-o

Bug in SQL Server 2019: SET IDENTITY_INSERT ON a temp table in nested/inner SP/scope

Consider the following use case:

You have 2 stored procedures, one is calling another.
In the outer SP you define a temp table with an Identity field.
And in the second SP you do insert to this temp table with

SET IDENTITY_INSERT ON option.

In Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 

it throws following exception:

Cannot insert explicit value for identity column in table 

'#TempTable' when IDENTITY_INSERT is set to OFF.
Sample Code:

CREATE PROCEDURE dbo.uspTestIdenityInsert_Outer
AS
SET NOCOUNT ON

-- Create temp table
CREATE TABLE #TempTable
(
RowID INT IDENTITY(1,1) NOT NULL,
FName VARCHAR(50) NOT NULL
)

-- exec inner SP that inserts data to the temp table.
EXEC dbo.uspTestIdenityInsert_Inner
GO


CREATE PROCEDURE dbo.uspTestIdenityInsert_Inner
AS
SET NOCOUNT ON

SET IDENTITY_INSERT #TempTable ON

INSERT INTO #TempTable(RowID,FName)
SELECT 1 as RowID,'Dan' as FName
GO

--------------------------------------------------------------------------

EXEC dbo.uspTestIdenityInsert_Outer

Msg 544, Level 16, State 1, Procedure dbo.uspTestIdenityInsert_Inner, Line 8 [Batch Start Line 26]

Cannot insert explicit value for identity column in table '#TempTable' when IDENTITY_INSERT is set to OFF.

Looks like this bug exist from CTP 2.3

https://feedback.azure.com/forums/908035-sql-server/suggestions/37092100-sql-server-2019-ctp-2-3-issue-with-scope-of-set

This bug is due to the implementation of the new feature
“Reduced recompilations for workloads using temporary tables across multiple scopes” 

that was introduced in SQL Server 2019

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/04/reduced-recompilations-for-workloads-using-temporary-tables-across-multiple-scopes/
The workaround is to disable this new feature by using trace flags 11036, 11048
that can be added as SQL startup parameters

SQL Server 2019 install/setup failed

When trying to install the SQL Server 2019,
The installation/setup failed with the following errors:

Feature: Database Engine Services
Status: Failed
Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail.
Next Step: Use the following information to resolve the error, and then try the setup process again.

Feature: SQL Client Connectivity SDK
Status: Failed
Reason for failure: An error occurred during the setup process of the feature.
Next Step: Use the following information to resolve the error, and then try the setup process again.

In the summary file there was the following error:

Exit code (Decimal): -2068643839

In the detail.txt file the first error/failure mentioned was:

InstallPackage: MsiInstallProduct returned the result code 1602.
Slp: No retry-able MSI return code detected.
Slp: ExecuteActionWithRetryHelper.Failed actionToExecute is 'Install_sqlncli_Cpu64_Action',
stack trace at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.b__0()
In my case what helped is to uninstall the SQL Server Native Client via

Add/Remove Programs and reboot. 

Apparently, this is documented by Microsoft
https://techcommunity.microsoft.com/t5/SQL-Server-Support/SQL-Server-2019-Installation-Error-An-error-occurred-for-a/ba-p/998033

Here how to find all the installation-related logs of SQL Server:

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/view-and-read-sql-server-setup-log-files?view=sql-server-2017