Tuesday, August 17, 2010

4-week Beginners Course Tuesdays, 6:00-7:30 p.m., starting on August 31, 2010A once-a-week course for those new to Shinshin Toitsu Aikido (Aikido with Mind and Body Coordination). This course introduces the Four Basic Principles of Mind and Body Coordination and the basic movements used in aikido techniques, along with breathing meditation. Learn how to use mind and body coordination to improve learning, athletic performance, and awareness of your surroundings. ** The FIRST class is mandatory. ** Intermediate class Tuesdays, 6:30-8:00 p.m. (July through August 24) This class is for those who have completed the Beginners Course. Joining Ki Society by paying the one-time Ki Society initiation fee is required. General Aikido class Thursdays, 6:30-8:30 p.m.This class is for those who have completed 8 weeks of Intermediate class.

* Intermediate class participants can attend the Beginners Course at no additional cost. Once you complete 8 weeks of Intermediate class, students can attend any classes.

Temporary Tables

Written by Bill Graziano on 17 January 2001

Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!" This article covers temporary tables and tables variables and is updated for SQL Server 2005.

I love questions like this. This question is just a perfect lead in to discuss temporary tables. Here I am struggling to find a topic to write about and I get this wonderful question. Thank you very much Sophie.

Temporary Tables

The simple answer is yes you can. Let look at a simple CREATE TABLE statement:

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )

You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table. The others are covered in Books Online.

except that I took about fifty underscores out to make it readable. SQL Server stores the object with a some type of unique number appended on the end of the name. It does all this for you automatically. You just have to refer to #Yaks in your code.

If two different users both create a #Yaks table each will have their own copy of it. The exact same code will run properly on both connections. Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure A creates a temporary table and calls stored procedure B, then B will be able to use the temporary table that A created. It's generally considered good coding practice to explicitly drop every temporary table you create. If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.

Now let's get back to your question. The best way to use a temporary table is to create it and then fill it with data. This goes something like this:

Obviously, this DBA knows their yaks as they're selecting the famed Tibetan yaks, the Cadillac of yaks. Temporary tables are usually pretty quick. Since you are creating and deleting them on the fly, they are usually only cached in memory.

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:

Which to Use

If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.

If you need to create indexes on it then you must use a temporary table.

When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

Answering the Question

And all this brings us back to your question. The final answer to your question might look something like this:

Global Temporary Tables

You can also create global temporary tables. These are named with two pound signs. For example, ##YakHerders is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it. These are rarely used in SQL Server.

Summary

That shows you an example of creating a temporary table, modifying it, and returning the values to the calling program. I hope this gives you what you were looking for.