SQL Tip for the Day

Tip 1 - Difference between Table Variables Vs Temporary Tables Vs Global Temporary Tables

Global Temporary Tables
1. Global temporary tables are available to all SQL Server connections.
2. All Sessions can access it
3. Same As Temp Tables Add ## before table name while declaring it

Table Variables
1. No Statistics associated with them
2. Do not participate in locking/transactions
3. Operations are not logged
4. Indexs cannot be created for table variables
5. They have scope associated only with current stored procedure it would be visible.
6. The only place where you can define columns to a table variable is in the declaration. ALTER TABLE not supported for Table Variable

Temp Tables
1. Statistics associated with them
2. Participate in locking/transactions
3. Operations are Logged
4. Add a single # before table name while declaring it
5. ALTER TABLE supported for Temp Tables
Reference - Link1, Link2, Link3, Link4, Good One - SangeethaShekar Blog
TempDB:: Table variable vs local temporary table


Tip 2 - When Clustered Index Columns are modified are Non-Clustured Indexes Re-built
Yes They are rebuilt. When Clustered Index column is re-ordered/arranged based on newly added/modified columns. Supporting no-clustered indexes would be as well rebuilt
Reference - Link1

Tip 3 - What resides in tempdb

• Inserted and deleted tables actually stored in tempdb
• User Created temp tables
• Online Index operation uses tempdb
• Worktables group by, order by (Ex- ORDER BY clause references columns not covered by any indexes, the relational engine may need to generate a worktable)
• MARS (Multiple Active Result Sets)
• In SQL 2005 Onwards When you use Row versioning data (Readcommitted, Snapshot Isolation level) is stored in tempdb

Happy Learning!!!