Creating MS-SQL Server Temporary Tables

MS-SQL Server lets you create two types of temporary tables: global and local. Local temporary tables are available only to the session in which they are created, and the DBMS automatically erases local temporary tables when the session ends. Global temporary tables are available to multiple database sessions. The DBMS drops a global temporary table after the last user using the table terminates his or her session.

Each login to the database starts a new session. As such, when you use Query Analyzer to attach to a database, you start a session. When you log off or terminate Query Analyzer, the DBMS ends your session. If you're logged in to the database, each time you execute a stored procedure or run an application program that logs in to the same or another database, the DBMS starts a new session. If you log in to the same database a second time, the DBMS keeps your original session open, but information in temporary tables created in the first session is not available to the second session.

Temporary tables are useful when you need to do several operations on the same set of data, such as creating summary reports on a subset of the data from multiple tables. By selecting and combining the raw data you need into a single table, you avoid having the DBMS extract and combine the data multiple times. In addition to eliminating multiple select operations, using a single temporary table increases execution speed because the MS-SQL Server can retrieve data faster from a single table than it can through references to multiple base tables.

To create a local temporary table, start the table name with a single pound (#) sign. As such, executing the statement

CREATE TABLE #customer_orders
 (customer_number INTEGER,
  customer_name   VARCHAR (35),
  order_date      DATETIME,
  amount          MONEY)

will create a local temporary table. The #CUSTOMER_ORDERS table is accessible only to the person who created it. Moreover, the DBMS will automatically DROP the table when the user logs out.

If you want to create a global temporary table, start the table name with two pound (#) signs. Thus, if you want to create a temporary table that is accessible to multiple users (and sessions), use a double pound (#) sign with a CREATE TABLE statement similar to:

 CREATE TABLE ##customer_orders
  (customer_number INTEGER,
   customer_name   VARCHAR (35),
   order_date      DATE_TIME,
   amount          MONEY)

The DBMS will not DROP the global temporary table until the last user that referenced the table during his or her session logs out.

SQL Tips and Techniques

SQL Tips and Techniques - Chapter 1

• Understanding the MS-SQL Server TempDB Database


• Understanding How to Size MS-SQL Server Databases and Transaction Logs


• Using DROP DATABASE to Erase an MS-SQL Server Database and Transaction Log
  
• Using the MS-SQL Server Enterprise Manager to Create a Database and Transaction Log
  
• Using the CREATE DATABASE Statement to Create an MS-SQL Server Database and Transaction Log
  
• Using the ED Command Within ISQL to Edit SQL Statements
  
• Using the MS-SQL Server ISQL to Execute SQL Statements from the Command Line or Statements Stored in an ASCII File
  
• Using the MS-SQL Server Query Analyzer to Execute SQL Statements
  
• Understanding SQL Keywords
  
• Understanding the Structure of SQL Statements
  
• Understanding the SQL DBMS Client/Server Model
  
• Understanding Assertions
  
• Understanding the MS-SQL Server IDENTITY Property
  
• Understanding the MS-SQL Server ISNULL() Function
  
• Understanding Constants
  
• Understanding the SQL BIT Data Type
  
• Understanding Standard SQL Datetime Data Types and the DATETIME Data Type
  
• Understanding the MS-SQL Server IMAGE Data Type


• Understanding the LONG (Oracle) or TEXT (MS-SQL Server) Data Type

SQL Tips and Techniques - Chapter 2

• Using the MS-SQL Server Enterprise Manager to Create Tables


• Creating MS-SQL Server Temporary Tables


• Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default


• Using the MS-SQL Server Stored Procedure sp_bindefault to Bind a User-Created Default to a Table Column


• Using the DEFAULT Clause in a CREATE TABLE Statement to Set Default Column Values


• Using the MS-SQL Server Enterprise Manager to Create a Default for a User-Defined Data Type or Table Column


• Using the MS-SQL Server Enterprise Manager to Create a User-Defined Data Type


• Using the MS-SQL Server Enterprise Manager to Bind a Default to a Data Type or Table Column


• Using the Transact-SQL DROP DEFAULT Statement to Remove a Default from a Database


• Understanding the ALTER TABLE Statement


• Using the ALTER TABLE Statement to Add a Column to a Table


• Using the MS-SQL Server ALTER TABLE DROP COLUMN Clause to Remove a Table Column


• Using the ALTER TABLE Statement to Change to Width or Data Type of a Column


• Using the ALTER TABLE Statement to Change Primary and Foreign Keys


• Using the CREATE TABLE Statement to Assign the Primary Key


• Using the CREATE TABLE Statement to Assign Foreign Key Constraints


• Using the DROP TABLE Statement to Remove a Table from the Database


• Using the DROP VIEW Statement to Remove a View


• Using the MS-SQL Server Enterprise Manager Create View Wizard to Create a View


• Understanding the CASCADE and RESTRICT Clauses in a DROP VIEW Statement

Using the MS-SQL Server Enterprise Manager to Create Tables

In addition to typing CREATE TABLE statements into the MS-SQL Server Query Analyzer's Query pane or at the ISQL (or OSQL) Ready prompts, MS-SQL Server gives you a GUI tool you can use. To create a table using the Enterprise Manager, perform the following steps:

1. To start the Enterprise Manager, click on the Start button, move your mouse pointer to Programs on the Start menu, select Microsoft SQL Server 7.0, and click your mouse on Enterprise Manager.

2. To display the list of SQL servers, click on the plus (+) to the left of SQL Server Group.

3. To display the list of resources on the SQL Server on which you wish to create a table, click on the plus (+) to the left of the SQL Server's name. For example, if you want to work with the SQL Server, NVBizNet2, click on the plus (+) to the left of NVBIZNET2. Enterprise Manager will display a list of folders that represent the resources managed by the SQL Server NVBizNet2 (in the current example).

4. Click on the Database folders. The Enterprise Manager will display the databases on the SQL Server in its right pane.

5. Right-click your mouse on the database in which you wish to create the table. For the current example, right-click your mouse on the SQLTips database. (If you did not create the SQLTips database, right-click your mouse on the TempDB database.) The Enterprise Manager will display a pop-up menu.

6. Move your mouse pointer to New on the pop-up menu, and then select Table. Enterprise Manager will display the Choose Name dialog box.

7. Enter the table name in the Enter a Name for the Table field of the Choose Name dialog box. For the current example, enter Item_Master and then click on the OK button. Enterprise Manager will display the SQL Server Enterprise Manager-New Table window shown in Figure 47.1.

   

8. Figure 47.1: The MS-SQL Server Enterprise Manager New Table window

9. Prepare to enter the first column name by clicking your mouse on the first cell in the Column Name column.

10. Enter the name of the column. For the current example, enter item_number. Next, press the Enter key to move to the input cursors to the Datatype field.

11. Select the field's data type. Either click on the drop-down arrow to the right of the Datatype field and select the data type, or enter the data type into the Data Type field. For the current example, enter INT. Next, press the Enter key to move to the insert cursor to the Length field.

12. If you are working with a character, image, or text type, enter the length of the character. In the current example, you are working with an integer, so the Length field is not applicable. Press the Enter key to move the input cursor to the Precision field.

13. If you are working with a decimal or floating-point (nonwhole) number, enter the total number of digits in the number into the Precision field. In the current example, you are working with an integer, so the precision is set to the default precision for your implementation (which you cannot change). Press the Enter key to move the input cursor to the Scale field.

14. If you are working with a decimal or floating-point (nonwhole) number, enter the number of digits you want to carry to the right of the decimal point into the Scale field. In the current example, you are working with an integer, so the Scale field is not applicable. Press the Enter key to move to the Allow Nulls check box.

15. To allow the field to hold a NULL value, click on the Allow Nulls check box until the check mark appears. For the current example, clear the Allow Nulls check box-every item in the ITEM_MASTER table must have an ITEM_NUMBER.

16. If you want to set the column to a constant default value if you don't supply an explicit value for the column when inserting a row into the table, enter the value into the Default Value field. For the current example, leave the Default Value field blank.

17. To have the DBMS supply an incrementing value for the column if you don't supply an explicit value for the column when inserting a row into the table, click on the Identity check box until the check mark appears. For the current example, click a check mark into the Identity check box-you want the system to supply the item numbers for new items you add to the ITEM_MASTER table. Then press the Enter key to move the input cursor to the Identity Seed field.

18. Enter the first value the DBMS should supply for the column-applicable only if you've identified the column as having the IDENTITY property. For the current example, enter 1000. Then press the Enter key to move the input cursor to the Identity Increment field.

19. Enter the value by which the DBMS is to increment the previous number it supplied for the column when inserting a new table row-applicable only if you've identified the column as having the IDENTITY property. For the current example, enter 100.

20. Click on the next empty cell in the Column Name field to enter another column name.

21. Repeat Steps 9-19 until you've defined all of the columns in your table. For the current example, add a second column named Description, with data type VARCHAR of length 35, which does not allow NULL values. Enterprise Manager will display your table definition similar to that shown in Figure 47.2.

    

22. Figure 47.2: The MS-SQL Server Enterprise Manager New Table window after defining two columns for the ITEM_MASTER table

23. To identity a column as the PRIMARY KEY, right-click your mouse on any field in the column, and select Set Primary Key from the pop-up menu. For the current example, right-click your mouse on ITEM_NUMBER in the Column Name field, and then select Set Primary Key from the pop-up menu.

    Note

    If you want to use a multiple-column (composite) PRIMARY KEY, right-click your mouse on any cell in the table and select Properties from the pop-up menu. The Enterprise Manager will display the Properties dialog box. Click on the Indexes/Keys tab and select the columns you want to include in the PRIMARY KEY in the Column Name list field in the Type area of the Indexes/Keys tab. When you are finished selecting columns for the PRIMARY KEY, click on the Close button. (You will learn more about using Enterprise Manager to create indexes in Tip 162, "Understanding MS-SQL Server CREATE INDEX Statement Options.")

24. To save your table definition, click on the Save button (first button on the left with the floppy disk icon) on the New Table standard toolbar.

25. To close the MS-SQL Server Enterprise Manager New Table window, click on the close button (the X) in the upper-right corner of the application window.

You can use the CREATE TABLE statement (which you learned about in Tip 46, "Using the CREATE TABLE Statement to Create Tables") or the Enterprise Manager GUI New Table tool to create MS-SQL Server tables. Both SQL and GUI allow you to define columns, set constraints, and identify table keys. If you have MS-SQL Server Enterprise Manager installed on your computer, the method you select to create your tables is a matter of personal preference (command line vs. GUI).