NOTE 336 Part I EXAM PREPARATION There is
NOTE 336 Part I EXAM PREPARATION There is also a special variable type called TABLE that can be used to store a recordset. For example, you can declare something like this: DECLARE @tmp TABLE (id int, tablename varchar(50) ) INSERT INTO @tmp SELECT id, name FROM sysobjects WHERE . type = u That creates a table similar to a temporary table that is available only within the current batch. This is faster and requires fewer resources than a temp table, but with a more limited scope. Be aware, however, that this consumes SQL Server memory, so don t put exceptionally large tables into these structures. You also cannot use a local variable of type table as the target for something like this: INSERT INTO @tmp EXEC sp_foo You cannot use a variable of type table as the target of a SELECT..into statement, either. You can populate the table using only INSERT..SELECT, INSERT..VALUES, and UPDATE. The table automatically goes away at the end of the batch, which is the end of its scope. Variable Scope All variables cease to exist at the end of their scope. To keep things simple, SQL Server has only one scope for a variable, which is the local scope. (Yes, there is a section called Global Variables. They don t count; you ll see why later in this chapter.) That means that when your script has a GO in it, any variables you have need to be redeclared and reset. So, you now know how to create variables using the DECLARE state- Picking Up the Trash Garbage ment, how to put different types of data into variables using variable Collection is the term computer sci-types, and how variables get destroyed. Now it s time to learn how to ence folks use to describe what hap- use the variables. pens to variables after they die. In many languages (C, C++, and others), the programmer has to deal with issues, such as heap fragmentation Setting and Using Variables and memory management from creat- There are four ways to put a value into a variable. If you need to put ing and disposing of variables. SQL data into a variable that comes out of a SELECT statement, you can Server handles all these mechanics do something like this: internally, so you don t have to worry about them. SELECT @id = id FROM sysobjects WHERE name = syscolumns
For high quality website hosting services please check java web hosting website.