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 CREATE TABLE reference
Version-Controlled Code (GitHub)
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s