Difference between Logical and Physical database joins

Most of us know about JOINS in SQL Server and their types. But do we really know how they are interpreted in SQL Server internally. Today I found lot of informative, interesting and important sources regarding Logical and Physical joins in SQL Server (links below).

JOINS are mainly classified into 2 types:

1. Logical Joins: These joins are simple joins that we apply in our SQL queries, like INNER JOIN, RIGHT/LEFT OUTER JOIN, CROSS JOIN, OUTER APPLY, etc.

2. Physical Joins: These are the joins that users don’t use/write in their SQL queries. Instead these are implemented inside SQL Server engine as operators or algorithms to implement the Logical Joins. Their types are Nested Loop, Merge and Hash.
For a particular SQL query when you try to view an Estimated Execution Plan or execute a query by selecting Actual Execution Plan, you can clearly see these Physical Joins under the Execution Plan tab in SSMS.


References for Physical Joins:
Nested Loop Joins: https://blogs.msdn.microsoft.com/craigfr/2006/07/26/nested-loops-join
Merge Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/03/merge-join/
Hash Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/10/hash-join/

To Start SQL Server In Single User Mode

There are certain situations where user wants to start SQL Server Engine in “single user” mode from the start up.

Starting SQL Server in single user mode is very simple process shown below:

Go to SQL Server Configuration Manager and click on SQL Server 2012(Any version that you use) Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter parameter ‘-m;‘ before existing params in Startup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server service. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

Maximum number of rows that can be stored in Temp table or a Table variable.

Temp Table:

No, there is no records limit for temporary table (the limit is the disk space). But be careful, because temporary tables are physically created in tempdb database, and this database must be placed on the disk with appropriate size.

The differences between tempdb and any other database are minimal, especially when it comes to limits. If you can store it in a user table, you can store it in a temporary table as well. It does not have to fit into RAM as the tempdb is stored on disk just like any other database (only with more aggressive caching and less logging).

Table Variable:

As such the official MSDN site where the Maximum Capacity Specifications for SQL Server there is no such upper limit defined for table variables because it depends on the database size and the free memory available for the storage. You can also refer the MSDN forum discussing the same; Maximum Capicity of Table Variable

Temp table VS Table variable in SQL Server

There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp) listed below:

1. As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables.
2. Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
3. Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. 
4. Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!
5. When using a #temp table within a user transaction locks can be held longer than for table variables (potentially until the end of transaction vs end of statement dependent on the type of lock and isolation level) and also it can prevent truncation of the tempdb transaction log until the user transaction ends. So this might favour the use of table variables.
6. If you will be repeatedly adding and deleting large numbers of rows from the table then use a #temporary table. That supports TRUNCATE (which is more efficient than DELETE for large tables) and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE.
7. If the optimal plan using the table will vary dependent on data then use a #temporary table. That supports creation of statistics which allows the plan to be dynamically recompiled according to the data (though for cached temporary tables in stored procedures the recompilation behaviour needs to be understood separately).
8. If the optimal plan for the query using the table is unlikely to ever change then you may consider a table variable to skip the overhead of statistics creation and recompiles (would possibly require hints to fix the plan you want).
9. If the source for the data inserted to the table is from a potentially expensive SELECT statement then consider that using a table variable will block the possibility of this using a parallel plan.
10. If you need the data in the table to survive a rollback of an outer user transaction then use a table variable. A possible use case for this might be logging the progress of different steps in a long SQL batch.
11. Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.
12. Within stored routines, both table variables and temporary tables can be cached. The metadata maintenance for cached table variables is less than that for #temporary tables. This can cause additional contention on system tables under conditions of high concurrency. Additionally, when dealing with small quantities of data this can make a measurable difference to performance.
13. You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
14. You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).
15.  If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.
16. Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.
17. Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.