How many rows would SQL Server estimate if you use table variables?

 In SQL Server, when you use table variables, the query optimizer estimates the number of rows based on the available statistics and the structure of the table variable. However, unlike regular tables, table variables do not maintain statistics (like index or row count statistics), which can lead to the optimizer making rough or inaccurate estimates.

Here are a few points to understand about how SQL Server estimates row counts for table variables:

1. Initial Estimates: SQL Server initially assumes that a table variable contains no rows, and therefore may choose execution plans that are suboptimal. This is because table variables do not have statistics by default, and SQL Server does not update the row count based on the actual data that gets inserted into them during the execution of a query.

2. Row Count Behavior: The optimizer does not have the benefit of up-to-date statistics for table variables, so if you insert a significant number of rows into the table variable, the optimizer may still use the initial assumption of 1 row or no rows (depending on the context). This can lead to suboptimal execution plans.

3. Queries Involving Table Variables: When executing queries that reference table variables, SQL Server may not adjust the plan well to account for actual row counts. For example, a join or a query with a table variable may perform poorly if the optimizer assumes there are fewer rows than in reality.

4. Estimating Row Counts: If you explicitly insert data into a table variable and then query it, SQL Server will not update its row estimate unless the execution plan is explicitly recompiled or the table variable is used in a context where SQL Server reevaluates its estimate.

To summarize, SQL Server's optimizer often defaults to assuming that table variables have a small number of rows (or sometimes zero rows) because table variables do not have statistics like permanent tables. Therefore, the row estimate can be inaccurate, potentially leading to poor execution plans. To mitigate this, you might need to use other techniques like adding hints, explicitly recompiling queries, or using temporary tables instead of table variables when row estimates are important.