Implement Database Objects: Create and alter tables

Microsoft’s Measured Skill description: This objective may include but is not limited to: develop an optimal strategy for using temporary objects (table variables and temporary tables); manage a table without using triggers; data version control and management; create tables without using the built-in tools; understand the difference between @Table and #table

What I see:

·         table variables

·         temp tables

·         table variables vs. temp tables

·         basic table creation

Table Variables

                As defined in BOL, a table variable is a data type that stores a result set (as opposed to our typical data types storing scalar data).  They can be used in functions, stored procedures, and batches.

Example

use MeasureMySkills

go

declare @TableVariable1 table

(

       id int identity(1, 1) not null,

       SomeString nvarchar(100) not null

              default replicate('a', 100)

)

insert into @TableVariable1

values(default)

select *

from @TableVariable1

Notes

·         explicit indexes cannot be created on table variables

·         the optimizer cannot grant a cost with table variables

·         CHECK constraints, DEFAULT values, and computed columns can’t make UDF calls

Temporary Tables

                Temporary tables are tables that are stored in tempdb for the duration of a session.  There are two types of temp tables:  local (defined with a single hash [#]), and global (defined with two hashes [##]).  A local temp table has the scope of the creating connection, whereas a global temp table can be accessed by all sessions (but it will still be dropped when the creating session is ended).

Example

use MeasureMySkills

go

create table #TemporaryTable1

(

       id int identity(1, 1) not null,

       SomeString nvarchar(100) not null

              default replicate('a', 100)

)

go

create clustered index IX_TempTbl1

on #TemporaryTable1 (id)

go

insert into #TemporaryTable1

values(default)

go 101

select *

from #TemporaryTable1

select *

from tempdb.sys.tables

where name like '#TemporaryTable1%'

Notes

·         temp tables are dropped when the creating session ends (or when explicitly dropped), or when locally created in a stored procedure

·         temp tables can have indexes created on them

Temporary Tables vs. Table Variables

                I am going to reference a DBA Stack Exchange answer by Martin Smith on the differences between a temp table and a table variable.  The post can be found here.  It is a very extensive and all-inclusive post, so I recommend reading his answer from top to bottom.  Great points and well laid out.  A few take-aways from his information:

·         both are stored in tempdb

·         lifetime (table variables are for a batch, and temp tables for a session [or stored procedure])

·         scope is a single session for table variable and local temp table (owning session), but a global temp table is instance wide

·         both are logged in the tempdb transaction log

·         table vars can’t have statistics created, but temp tables can

·         table variables cannot leverage parallelism, whereas temp tables can

Basic Table Creation

            Creating tables can be one of the simplest tasks to do with T-SQL, but there are a lot of caveats and options with table creation.  I recommend skimming the BOL reference on CREATE TABLE to get a feel for what you may not be well-versed with.  For basic table creation code (in its simplest form), reference the temporary table CREATE TABLE command.

References

·         BOL table data type reference

·         BOL CREATE TABLE reference

·         DBA.SA post on Temp Table vs. Table Var

Version-Controlled Code (GitHub)

·         Database_Objects_CreateTableVar.sql

·         Database_Objects_CreateTempTable.sql

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.