Category Archives: T-SQL

Temp Table vs. Table Variable

Posted by on 2012/09/06 1 comment

This is a comparison summary that I’ve shared to describe the differences between Temp Tables vs. Table Variables.  A few people have asked that I post it so here you go:

Comparison Summary

This is a summary of the functionality and behavior of the different object types. Note that global temporary tables aren’t included, because typically you use those for different purposes than the other types of temporary objects. You might find this table handy as a reference when you need to choose the appropriate temporary object type for a given task.

Functionality/Object Type Local Temp Table Table Variable Table Expression
Scope/Visibility Current and inner levels Local Batch Derived Table/CTE: Current statement View/Inline UDF: Global
Physical representation in tempdb Yes Yes No
Part of outer transaction/Affected by outer transaction rollback Yes No Yes
Logging To support transaction rollback To support statement rollback Yes
Locking Yes No Yes
Statistics/recompilations/efficient plans Yes No Yes
Table size Any Typically recommended for small tables Any

Temp tables can be altered with DDL statements but table variables can’t (so you cannot create a non-clustered index on a table variable for example). That makes every table variable a heap, or at best a table with a single, clustered index and every table variable access a table scan (or clustered index scan, which is essentially the same thing).

SQL Server creates statistics for temp tables, so the query optimizer can choose different plans for data involving temp tables. However, SQL Server does not create statistics on columns in table variables (So, the only way to access data in a table variable is through a table scan).

Now go read Paul White’s (b | t) post on Temporary Tables in Stored Procedures

Table Partitioning vs. Partitioned Views

Posted by on 2012/08/30 1 comment

Recently I was doing some work trying to figure out whether to use Table Partitioning or Partitioned views in a data warehouse to solve a problem.  In this particular data warehouse the data cannot be partitioned by date, it’s actually partitioned by a value in a table.  I was leaning towards Table Partitioning and talking with Brent (b | t) we put together this quick comparison up on a whiteboard:

Table Partitioning Partitioned Views
Parallel Loads Y Y
Query Partition Elimination Y Y
Divide Filegroups Y Y
Index Tuning EASY (single table means single set of indexes) POWERFUL (different indexes on different tables)
Schema changes doable N (single table means adding a column is time consuming) Y (different tables can be different schemas and then managed through views)
Enforced Relational Integrity Y N
Freeze Data 10% R/O, Perfect Stat/Indexes Y Y

As I said I was leaning towards table partitioning while Brent was leaning towards partitioned views.  My argument was for referential integrity while Brent’s was ability of schema changes.  Well turns out that Brent won (big surprise huh?) however not for any of the reasons listed.  The reason we went Partitioned Views is that once that is in place we can then implement table partitioning on the individual tables.  Combining these two techniques can be very powerful (if done correctly that is).  If we went straight to table partitioning, we would be limited in our future potential in this regards.

So lesson of the day is when looking and planning architecture – think, whiteboard and then think some more – do not just open up SSMS and start typing code.

PASS Summit PreCon 2

Posted by on 2011/10/12 No comments

Day 2 of the SQLPass Summit I attended Advanced T-SQL for SQL Server 2008 and Denali presented by Itzik Ben-Gan (twitter | site), this was rated a 499 Session, so not for the faint of heart.  For those of you that don’t know who Itzik is just take a look at this site and what books he authors. In short he’s one of the premier T-SQL experts.

Here was the Agenda:

  • APPLY Magic
  • Grouping Sets
  • TOP / OFFSET-FETCH
  • Sequences
  • Windows Functions
  • Intervals
  • Other T-SQL Improvements in Denali

First a truism – SQL is a set based language, the concepts and logic that exist are designed for set based activities.  He also went over how the Query is written:

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

vs. how it’s processed:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

The session was, as you can imagine, pretty intense.  Itzik obviously loves what he was talking about and is very passionate.  He talked about how to improve standard SQL as well for migrations between other SQL implementations.

As far as some of the new features of T-SQL in Denali that was mentioned:

  • OFFSET / FETCH (think paging)
  • Sequences (independent identity objects that can be referenced and keep proper seeding)

Image

  • Windows Functions (this set functions) – these have been expanded tremendously to include things like LAG, LEAD, FIRST_VALUE, LAST_VALUE, distribution functions (PERCENT_RANK, etc.), ROWS and RANGE 
  • Conversion – PARSE, TRY, TRY_CONVERT (returns NULL if can’t convert), TRY_PARSE
  • Date and Time – EOMONTH (end of month), DATEFROMPARTS and similar functions to build date, time and datetime data from different parts
  • Logical – CHOOSE, IIF – these were added to improve Access migrations
  • String – CONCAT this is used for string concatenation, similar to the + operator, however it will automatically convert NULL to to strings; FORMAT – this brings in .NET format functionality, however it’s slower than native SQL formatting (probably because it utilized CLR behind the scene)
  • Mathematical – LOG now supports the ability to indicate the base
  • Improved Error Handling – THROW, this finally allows us re-throw the original error to be bubbled up.  Until now we’ve had to utilize user errors
  • EXECUTE WITH RESULT SETS – This allows us to call an SP and guarantee the shape of result set.  We submit an expected result set shape which if the return set doesn’t match SQL will try to implicitly convert to, however it could fail if it can’t return what is specified.
  • Metadata discovery – removes the SET FMTONLY – this allows us to interrogate and return the metadata
  • FORCESCAN and FORCESEEK – this allows us to force an index seek or index scan for a particular query

Another great session and it’s cool to see the advancements being made within T-SQL.  More to come later.