Table variables and SQL Server

In a recent post, I mentioned temporary tables (you can find that post here). An alternative to temporary tables are table variables. Tablevariables are a lot like temporary tables, except the table variable has a few advantages over temporary tables:

• Table variables usually use fewer resources than a temporary table, which makes it a little bit faster
• Table variables will not exist after the procedure that called it exits, so there is no clean-up required with DROP statements like temporary tables

We declare table variables sort of like other variables, but the syntax looks like this:

DECLARE @<VariableName> TABLE

(

<COLUMNS>

);

Here is an example, using the Library database I created in an earlier post:

1. USE Library
3. DECLARE @SomeAuthors TABLE
4. (
5.     authorID INT
6.     , first_Name VARCHAR(50)
7. );

Here is a way we could use table variables:

Download Code

1. USE Library
3. DECLARE @SomeAuthors TABLE
4. (
5.     author_ID INT
6.     , first_Name VARCHAR(50)
7. );
9. INSERT INTO @SomeAuthors SELECT author_ID, first_Name
10. FROM Authors WHERE first_Name = ' Geoffrey';
12. UPDATE Authors SET Authors.last_Name = 'We updated this column'
13. WHERE Authors.first_Name IN (SELECT first_Name FROM @SomeAuthors);
15. SELECT * FROM Authors;

What this is doing is created a table variable called "SomeAuthors" and it is going to take the records from the Authors table in our Library database and put them into our SomeAuthors temporary variable. Then this little script is going to update all our records in the Authors table based on what is in the SomeAuthors variable. This can be expanded to do some rather cool things when we need to update records. The final line is just there to return the records of the Authors table to see what our script has done.

Temporary tables and SQL Server

When dealing with SQL Server and tables, sometimes you want to move data, make changes to a table just to see what happens and not affect your existing table structure. This might seem like a taboo thing, or part of the SQL Server Black Magic, but it is fairly simple to do this with temporary tables.

There are two types of temporary tables in Microsoft SQL Server:

• Local - these tables visible only in the current session, and they are created like a normal table but with a # in from of the table name
• Global - these temporary tables are visible to all sessions, and they are created like a normal table but with a ## in from of the table name

Here are a few rules regarding temporary tables:

• Creating temporary tables using the CREATE TABLE syntax supports constraint definitions, except for FOREIGN KEY constraints
• Temporary tables cannot be referenced in FOREIGN KEY constraints
• All local temporary temporary tables are dropped automatically at the end of the current session
• A local temporary table created in a stored procedure is dropped when the stored procedure is finished
• A local temporary table can be referenced by any nested stored procedures called by the stored procedure
• This means that if there is stored procedure "A", "B", and "C" and a temporary table is created in B, and stored procedure C is called with in B, and B is called by A, the temporary table can be referenced in both B and C, but not A.
• Local temporary table names cannot exceed 116 characters

To create a temporary table, we use the normal CREATE TABLE syntax, except we add a "#" right before the table name. Let's create a temporary table named "TempUsers"

1. CREATE TABLE #TempUsers
2. (
3.     firstName VARCHAR(30)
4.     , lastName VARCHAR(30)
5.     , nickName VARCHAR(30)
6. );

As you can see, it looks almost exactly the same as creating a normal table, except for the use of the "#". And we can pretty much do everything else with the temporary table (except using foreign key constraints, of course). For example, we can insert records into it:

1. INSERT INTO #TempUsers
2. (
3.     firstName
4.     , lastName
5.     , nickName
6. )
7. VALUES
8. (
9.     'John'
10.     , 'Doe'
11.     , 'Jimmy'
12. );

And if we did a select all on our table we can actually see the data entered with our insert statement:

1. SELECT * FROM #TempUsers;

Output:

firstName	lastName	nickName
John	Doe	Jimmy

And, if you want the overly complicated MSDN description of everything there is on temporary tables in Microsoft SQL Server, click here.

Selecting and Viewing Records

In the I Break Code’s Inserting Records post we inserted two authors into our Authors table and two books into our Books table, which reside in our Library database. After we insert this information into our database, we need some means of retrieving that information. We can do that with the SELECT statement.

The SELECT statement is fairy simple to use. We can use it to select specific columns, or all columns.

Let’s say we wanted to view all our records, or rows, in our Authors table. We would write it like this:

1. USE Library;
2. GO
3.  
4. SELECT * FROM Authors;

And SQL Server would return this output when executed:

author_ID	first_Name	last_Name
1	Geoffrey	Chaucer
2	William	Shakespeare

And if we wanted to select our books from our database we would do it like so:

1. USE Library;
2. GO
3.  
4. SELECT * FROM Books;

And SQL Server would return this output when executed:

book_ID	title	genre	description
1	The Cantebury Tales	Classic/Poetry	A great collection of poems and stories.
2	Macbeth	Classic/Play	"Fair is foul, and foul is fair". - ( Quote Act I, Scene I).

We can also specify exactly what columns we want to select from our tables too:

1. USE Library;
2. GO
3.  
4. SELECT first_Name, last_Name FROM Authors;
5. SELECT title FROM Books;

And SQL Server would return this output:

Output from line 4:

first_Name	last_Name
Geoffrey	Chaucer
William	Shakespeare

Output from line 5:

title

The Cantebury Tales

Macbeth

Try  experimenting by selecting different combinations of column names, in different orders, or the same column names multiple times to see what kind of output you can come up with.

Inserting Records

In this post, I will focus on inserting records into the tables made during the I Break Code's Creating Tables post. During we created two tables Authors and Books. The Authors table has three columns: author_ID, first_Name; last_Name. The Books table has five columns: book_ID, title, genre; description.

We are going to insert two records into our authors table:

Author 1:
First name: Geoffrey
Last name: Chaucer

Author 2:
First name: William
Last Name: Shakespeare

1. USE Library;
2. GO
4. INSERT INTO Authors
5. (
6.     first_Name
7.     , last_Name
8. )
9. VALUES
10. (
11.     ' Geoffrey'
12.     , 'Chaucer'
13. );
15. INSERT INTO Authors
16. (
17.     first_Name
18.     , last_Name
19. )
20. VALUES
21. (
22.     ' William'
23.     , 'Shakespeare'
24. );

When executed, this query will insert two authors into our "Authors" table with the provided information.

Now, let's add some books to our "Books" table.

Book 1:
Title: The Cantebury Tales
Genre: Classic/Poetry
Description: A great collection of poems and stories.

Book 2:
Title: Macbeth
Genre: Classic/Play
Description: "Fair is foul, and foul is fair". - ( Quote Act I, Scene I).

1. USE Library;
2. GO
4. INSERT INTO Books
5. (
6.     title
7.     , genre
8.     , [description]
9. )
10. VALUES
11. (
12.     'The Cantebury Tales'
13.     , 'Classic/Poetry'
14.     , 'A great collection of poems and stories.'
15. );
17. INSERT INTO Books
18. (
19.     title
20.     , genre
21.     , [description]
22. )
23. VALUES
24. (
25.     'Macbeth'
26.     , 'Classic/Play'
27.     , '"Fair is foul, and foul is fair". - ( Quote Act I, Scene I).'
28. );

When executed, this query will insert two books into our "Books" table with the provided information.

Creating Tables

This post will build off the I Break Code's Create Database post where a database was created with the name "Library".

The following SQL statements will create two tables with some columns called "Books" and "Authors".

1. USE Library;
3. CREATE TABLE Books
4. (
5.     book_ID int identity(1,1) PRIMARY KEY
6.     , title varchar(50) NOT NULL
7.     
8.     , genre varchar(25)
9.     , description varchar(100)
10. );
12. CREATE TABLE Authors
13. (
14.     author_ID int identity(1,1) PRIMARY KEY
15.     , first_Name varchar(25) NOT NULL
16.     , last_Name varchar(50) NOT NULL
17. );

The tables will be created with some columns inside of them. If you don't understand some of the syntax, just post a comment and I'll try to reply to it either directly or with a post explaining that topic.

Create Database

To create a database using Microsoft SQL Server without defining any tables in our create statement we would create it using the following syntax:

1. USE master;
2. GO
3. CREATE DATABASE Library;

This will create an empty database named "Library"

BASIC Basics, a quick look at VB.NET

People often look at the idea of programming with the attitude, or mindset, that it is too difficult and complex to learn. This may have been the case years ago when computer programming was best to left to computer engineers and by those who designed specific computer platforms. But over the years, computer's have evolved and so have the languages that run them. Similar to spoken languages. For example, choose a random page of your favorite modern book and compare it to a random page of any text written by Shakespeare or Chaucer. The differences will be almost immediately clear. The same has happened to programming languages. There are two main categories of programming languages: low-level programming languages, and high-level programming languages.

Low-level programming languages are languages designed for specific, advanced tasks, and are generally do not resemble spoken language at all. High-level programming languages aim to provide programming languages that are similar to the spoken language.

The following code shows an example application that shows the output "Hello, whats up world?" in a console window, and then waits for the user to press a key before exiting.

1. Module ExampleApplication
2.  
3.     Sub Main()
4.  
5.         ' This will write a line to the console.
6.         Console.WriteLine("Hello, what's up world?")
7.  
8.         ' This will cause the console application to pause so we can see it't output. Not the best method, but works for this example.
9.         Console.ReadLine()
10.  
11.     End Sub
12.  
13. End Module

* This source code was highlighted with Source Code Highlighter.

Looking at the code, you could probably guess what each line is, aside from maybe lines 1, 3, 11 and 13 (which will be described in a later posting). If you look at lines 6 and 9, it easy to understand what is going on.

On line 6 you could probably guess that were are going to 'write' "Hello, what's up world?" to the console. And that line 9 is going to read the next line inputed to the console. Since the program isn't using the new line for anything, it continues on to line 11 which ends that section of code, and then the program.

When the application is ran, you will see this on the screen:

The example application running.