What is the difference between a temporary table and a table variable in SQL Server

When we use tables in a database, we often encounter two ways to use a table, which is to use temporary tables and table variables. How can we use them flexibly in a stored procedure when we are actually using them, although they are basically the same functionality, how do you sometimes use a temporary table instead of a table variable in a stored procedure, or do you use a table variable instead of a temporary table?

Temporary tables

A temporary table is similar to a permanent table, except that it is created in tempdb, which disappears only after a database connection has been completed or dropped by the SQL command, otherwise it will always exist. Temporary tables generate SQL Server's system logs when they are created, although they are embodied in tempdb, are allocated in memory, and they support physical disks, but the user cannot see the files on the specified disk.

Temporary tables are both local and global, and the local temporary table names are prefixed with "#" and are only visible in the local current user connection and are deleted when the user disconnects from the instance. The name of the global temporary table is prefixed with "# #", which is visible to any user after it is created, and is deleted when all users referencing the table are disconnected.

Let's look at an example of creating a temporary table:

CREATE TABLE dbo. #News  
(  
news_id int not NULL,  
newstitle varchar (MB),  
newscontent varchar (2000), c5/> newsdatetime datetime  
)

Temporary tables can create indexes or define statistics, so you can use Data Definition language (DDL) declarations to prevent temporary tables from adding restrictions, constraints, and referential integrity, such as primary and foreign key constraints. For example, we now add a default GetData () Current date value for #news table field Newsdatetime, and add a primary key for news_id, and we can use the following statement:

ALTER TABLE dbo. #News ADD CONSTRAINT [Df_newsdatetime] DEFAULT (GETDATE ()) For[newsdatetime], PRIMARY KEY clust Ered ([news_id]) on [PRIMARY] go IF EXISTS (SELECT * fromtempdb ...). sysobjects WHERE ID =object_id (' tempdb. # #wzg_test ') and type= ' U ') begin DROP TABLE[ADDON_SCM].
      
                      [# #wzg_test] Select ' droptable # #wzg_test ' End ELSE begin CREATE TABLE [ADDON_SCM].  
      
                      [# #wzg_test] (id int, name varchar (100 ) SELECT ' CreateTable # #wzg_test ' End I F EXISTS (SELECT * fromtempdb. sysobjects WHERE ID =object_id (' tempdb. # #wzg_test ') and type= ' U ') Begin insert INTO [ADDON_SCM].  
      
[# #wzg_test] VALUES (1, ' Wzh ')              SELECT * FROM [ADDON_SCM].　　 [# #wzg_test] End

Temporary tables can modify many of the defined options after they are created, including:

1 Add, modify, delete columns. For example, the name, length, data type, precision, scale, and nullability of a column can be modified, except for a few limitations.

2 You can add or remove primary and foreign key constraints.

3 You can add or remove unique and CHECK constraints and default definitions (objects).

4 You can add or remove an identifier column using the identity or ROWGUIDCOL property. Although the ROWGUIDCOL property can also be added to an existing column or deleted from an existing column, there can be only one column in the table that can have that property at any time.

5 The selected columns in the table and table are registered as Full-text indexes.