Asked  7 Months ago    Answers:  5   Viewed   39 times

In SQL Server 2005, we can create temp tables one of two ways:

declare @tmp table (Col1 int, Col2 int);

or

create table #tmp (Col1 int, Col2 int);

What are the differences between these two? I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory.

In which scenarios does one out-perform the other?

 Answers

56

There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn't one of them, as spelt out in the MSDN link below.

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

Some points to consider when choosing between them:

  • Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.

  • Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.

  • Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!

  • Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.

  • You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.

  • You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).

  • Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.

  • Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.

  • Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

Some further reading:

  • Martin Smith's great answer on dba.stackexchange.com

  • MSDN FAQ on difference between the two: https://support.microsoft.com/en-gb/kb/305977

  • MDSN blog article: https://docs.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table

  • Article: https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

  • Unexpected behaviors and performance implications of temp tables and temp variables: Paul White on SQLblog.com

Tuesday, June 1, 2021
 
Asnexplore
answered 7 Months ago
95

%variable% are environment variables. They are set with set and can be accessed with %foo% or !foo! (with delayed expansion if enabled). %%a are special variables created by the for command to represent the current loop item or a token of a current line.

for is probably about the most complicated and powerful part of batch files. If you need loop, then in most cases for has you covered. help for has a summary.

You can

  • iterate over files: for %x in (*.txt) do ...
  • repeat something n times: for /l %x in (1, 1, 15) do... (the arguments are start, step and end)
  • iterate over a set of values: for %x in (a, b, c) do ...
  • iterate over the lines of a file: for /f %x in (foo.txt) do ...
  • tokenize lines of a file: for /f "tokens=2,3* delims=," %x in (foo.txt) do ...
  • iterate over the output of a command: for /f %x in ('somecommand.exe') do ...

That's just a short overview. It gets more complex but please read the help for that.

Variables of the form %%a (or %a if for is used outside of batch files) are very similar to arguments to batch files and subroutines (%1, %2, ...). Some kinds of expansions can be applied to them, for example to get just the file name and extension if the variable represents a file name with path you can use %%~nxa. A complete overview of those is given in help for.

On the other hand, environment variables have other kinds of special things. You can perform replacements in them via %foo:a=b% would result in %foo% except that every a is replaced by a b. Also you can take substrings: %foo:~4,2%. Descriptions of those things can be found in help set.

As to why %variables% and %%a are different things that's a bit hard to answer and probably just a historical oddity. As outlined above there is a third kind of variable, %1, etc. which are very similar to those used in for and have existed for longer, I guess. Since environment variables are a bit unwieldy to use in for due to blocks and thus heavy reliance on delayed expansion the decision probably was made to use the same mechanisms as for arguments instead of environment variables.

Also environment variables could be more expensive, given that the process has a special “environment” block of memory where they are stored in variable=value? pairs, so updating environment variables involves potentially copying around a bit of memory while the other kind of variables could be more lightweight. This is speculation, though.


As for your problem, you don't really need for here:

find /v ":" "%appdata%gamelauncheroptions.txt" | find "menu=a" && set usemenu=a

This will only run the set if the preceding command was successful, i.e. menu=a was found. This should be considerably easier than for. From what I read you're trying to look whether menu=a exists in a line that does not contain a colon and in that case usemenu should be set to a, right? (And likewise for b and c. You could try coaxing for into doing that by looping over the lines of the file or output and tokenizing appropriately to figure out the value of menu but depending on the format of the lines this can be tricky. If what you have there works in theory then you should simply stick to that. You can however use a loop around it to avoid having to repeat the same line three times for a, b and c:

for %%X in (a b c) do (
  find /v ":" "%appdata%gamelauncheroptions.txt" | find "menu=%%X" && set usemenu=%%X
)

If the file you are parsing is simple, however, with just name=value pairs in each line where : foo would be a comment, then you could use for as well:

for /f "tokens=1,* eol=: delims==" %%A in (%appdata%gamelauncheroptions.txt) do (
  if "%%A"=="menu" set usemenu=%%B
)

But that depends a little on the exact format of the file. Above snippet would now read the file line by line and for each line would discard everything after a colon (the eol=: option), use the equals sign as a token delimiter and capture two tokens: The part before the first = and everything after it. The tokens are named starting with %%A so the second one is implicitly %%B (again, this is explained in help for). Now, for each line we examine the first token and look whether it's menu and if so, assign its value to the usemenu variable. If you have a lot of possible options to support this is certainly easier to maintain :-).

Wednesday, July 21, 2021
 
lechup
answered 5 Months ago
27

Running the followign to scripts, it would seem that the Table Variable is the better option

CREATE TABLE #Temp(
        ID INT
)

DECLARE @Int INT,
        @InnerInt INT
SELECT  @Int = 1,
        @InnerInt = 1

WHILE @Int < 50000
BEGIN
    WHILE @InnerInt < 10
    BEGIN
        INSERT INTO #Temp SELECT @InnerInt
        SET @InnerInt = @InnerInt + 1
    END
    SELECT @Int = @Int + 1,
            @InnerInt = 1
    TRUNCATE TABLE #Temp
END

DROP TABLE #TEMP

GO

DECLARE @Temp TABLE(
        ID INT
)

DECLARE @Int INT,
        @InnerInt INT
SELECT  @Int = 1,
        @InnerInt = 1

WHILE @Int < 50000
BEGIN
    WHILE @InnerInt < 10
    BEGIN
        INSERT INTO @Temp SELECT @InnerInt
        SET @InnerInt = @InnerInt + 1
    END
    SELECT @Int = @Int + 1,
            @InnerInt = 1
    DELETE FROM @Temp
END

From Sql Profiler

CPU     Reads   Writes  Duration
36375     2799937   0       39319

vs

CPU     Reads   Writes  Duration
14750   1700031 2       17376   
Thursday, August 5, 2021
 
Ramy Al Zuhouri
answered 4 Months ago
25

As mentioned in a comment to the question, you would not bother creating a class for the association table if it only contains the foreign keys linking the two tables in the many-to-many relationship. In that case your first example – an association table – would be sufficient.

However, if you want to store additional information about the nature of the link between the two tables then you will want to create an association object so you can manipulate those additional attributes:

class StudentIdent(db.Model):
    __tablename__ = "student_identifier"
    course_id = db.Column(
        db.Integer, 
        primary_key=True, 
        autoincrement=False,
        db.ForeignKey('courses.course_id')
    )
    user_id = db.Column(
        db.Integer, 
        primary_key=True, 
        autoincrement=False,
        db.ForeignKey('students.user_id')
    )
    enrolment_type = db.Column(db.String(20))
    # reason for student taking this course
    #   e.g., "core course", "elective", "audit"

and then you could create the link between a given student and a particular course by creating a new instance of the association object:

thing = StudentIdent(course_id=3, user_id=6, enrolment_type="elective")

Note: This is just a basic linkage. You can get more sophisticated by explicitly declaring a relationship between the ORM objects.

Monday, August 30, 2021
 
CAMason
answered 3 Months ago
55

If ever possible avoid cursors like the plague. SQL Server is set-based - anything you need to do in an RBAR (row-by-agonizing-row) fashion will be slow, sluggish and goes against the basic principles of how SQL works.

Your question is very vague - based on that information, we cannot really tell what you're trying to do. But the main recommendation remains: whenever possible (and it's possible in the vast majority of cases), use set-based operations - SELECT, UPDATE, INSERT and joins - don't force your procedural thinking onto SQL Server - that's not the best way to go.

So if you can use set-based operations to fill and use your temporary tables, I would prefer that method over cursors every time.

Tuesday, September 14, 2021
 
Owen
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share