Tag Archives: Installation

Articles on Microsoft SQL Server Installation

#0352 – SQL Server – Working with Temporary Tables (L100) – Scope


In this post, I provide a Level 100 primer on the scope related considerations for local temporary tables.

While I have written about temporary tables a lot in the past (and the same has also been done by other writers), there are some questions that I keep encountering from the team or in various forums on the Internet which is why I am writing this post.

Temporary tables use the tempdb and come in two variants:

  1. Local Temporary Tables
    • As the name implies, they are local to the connection that created them
    • Therefore, if multiple connections request creation of a temporary table with the same name, each connection gets it’s own unique copy of the temporary table
    • The local temporary tables are implicitly deleted when the connection is closed
    • To create a local temporary table, prefix the desired table name with a single hash (#) mark, e.g. A local temporary table for employees might be named #Employee or #HumanResources
  2. Global Temporary Tables
    • These are global to the entire SQL Server instance and are therefore shared by all connections to the server
    • They are implicitly deleted when the last connection referencing them is closed
    • To create a global temporary table, prefix the desired table name with a double hash (##) mark, e.g. A global temporary table for employees might be named ##Employee or ##HumanResources

A demo on local temporary tables

The most important point that is missed out by most novice developers is that temporary tables are session specific. To briefly demonstrate this, allow me to follow the following steps:

  1. Open SQL Server Management Studio and launch two (2) query editor windows
    • In each window, connect to the same database and same SQL Server instance
  2. Run the following in window #1. This snippet creates a local temporary table and populates some records into it
--Window 01
USE AdventureWorks2012;
GO

--Create the local temporary table if it does not exist
IF OBJECT_ID('tempdb..#BasicEmployeeList','U') IS NULL
BEGIN
CREATE TABLE #BasicEmployeeList 
    ( BusinessEntityID INT          NOT NULL,
      JobTitle         NVARCHAR(50) NOT NULL,
      Title            NVARCHAR(8)      NULL,
      FirstName        NVARCHAR(50) NOT NULL,
      MiddleName       NVARCHAR(50)     NULL,
      LastName         NVARCHAR(50) NOT NULL
    );
END;
GO

INSERT INTO #BasicEmployeeList
    (BusinessEntityID,
     JobTitle,
     Title,
     FirstName,
     MiddleName,
     LastName
    )
SELECT p.BusinessEntityID,
       e.JobTitle,
       p.Title,
       p.FirstName,
       p.MiddleName,
       p.LastName
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID;
GO
  1. Without closing window #1, Switch over to window #2
  2. Attempt to run the statement shown below
    • The system will encounter an error
USE AdventureWorks2012;
GO
SELECT bel.BusinessEntityID,
       bel.JobTitle,
       bel.Title,
       bel.FirstName,
       bel.MiddleName,
       bel.LastName
FROM #BasicEmployeeList AS bel;
GO

/* ERROR
Msg 208, Level 16, State 0, Line 1
Invalid object name '#BasicEmployeeList'.
*/
  1. The error is received because the temporary table was created as a local temporary table and is therefore inaccessible to other connections

Important points to note

  • Because temporary tables are persisted in the tempdb, the tempdb can see significantly large I/O operations if your application makes use of a lot of temporary storage
  • SQL Server gives Local Temporary tables an auto-generated name which is transparent to the caller to allow multiple connections to the use local temporary tables with the same name [Link]
  • Local temporary tables can be shared to referencing stored procedures as long as they use the same connection
    • This is documented in my post [Link]
  • Creating named constraints on temporary tables prevent them from being used concurrently
  • Temporary tables do not support:
    • Foreign key constraints [Links]
    • Triggers

Until we meet next time,

Be courteous. Drive responsibly.

#0344 – SQL Server – Missing Configuration Manager on Windows 8


Microsoft SQL Server comes with a very powerful  configuration manager which can be used to manage the SQL Server services and control network visibility and footprint. Working without the SQL Server Configuration Manager is a nightmare for a DBA.

SQL Server Configuration Manager is not available in Windows 8

Many development environments have SQL Server installed on the developer workstations running a Windows 8 operating system. I have been consulted on a couple of instances where the SQL Server Configuration Manager fails to come up in the “Start” menu application list (even using the Search charm does not yield any results).

The workaround

The SQL Server Configuration Manager is in reality a Microsoft Management Console (MMC) application. Hence, the workaround is to do the following:

  1. Launch the Search charm
  2. Search for the SQL Server Configuration Manager MMC snap-in:
    • SQL Server 2012 – search for “SQLServerManager11.msc
    • SQL Server 2008R2 – search for “SQLServerManager10.msc
  3. Press “Enter” to launch (or alternatively, right-click on the result to pin to the Start Menu or create a shortcut on the desktop)

Further Reading on the SQL Server Configuration Manager

  • SQL Server Configuration Manager – Hide your SQL Server Instance [Link]
  • #0151 – SQL Server– Which TCP/IP port is my SQL Server listening on? [Link]
  • Changing SQL Server Service Account or Password – Avoid restarting SQL Server [Link]

[EDIT, 09/08/2014, 10:55AM IST]: Rectified a typo for the MSC file name related to Configuration Manager for SQL Server 2008 R2.

Until we meet next time,

Be courteous. Drive responsibly.