Tuesday, 16 November 2010
Differences between SQL Server temporary tables and table variables
Temporary Tables
Temporary tables are created in tempdb. The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a "#" sign. This is the identifier for SQL Server that it is dealing with a temporary table.
The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#):
CREATE TABLE #TempTable
(
ID INT NOT NULL,
Name VARCHAR(10),
DOB DATETIME
)
Table Variables
The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:
DECLARE @TableVariable TABLE (
ID INT NOT NULL,
Name VARCHAR(10),
DOB DATETIME
)
As you can see the syntax bridges local variable declaration (DECLARE @variable_name variable_data_type) and table creation (column_name, data_type, nullability). As with any other local variable in T-SQL, the table variable must be prefixed with an "@" sign. Unlike temporary or regular table objects, table variables have certain clear limitations.
* Table variables can not have Non-Clustered Indexes
* You can not create constraints in table variables
* You can not create default values on table variable columns
* Statistics can not be created against table variables
Similarities with temporary tables include:
* Instantiated in tempdb
* Clustered indexes can be created on table variables and temporary tables
* Both are logged in the transaction log
* Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.
Differences between SQL Server temporary tables and table variables
There are three major theoretical differences between temporary tables And table variables
* The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism
After declaring our temporary table #T and our table-variable @T, we assign each one with the same "old value" string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same "new value" string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.
* The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
* Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.
Temporary tables are created in tempdb. The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a "#" sign. This is the identifier for SQL Server that it is dealing with a temporary table.
The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#):
CREATE TABLE #TempTable
(
ID INT NOT NULL,
Name VARCHAR(10),
DOB DATETIME
)
Table Variables
The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:
DECLARE @TableVariable TABLE (
ID INT NOT NULL,
Name VARCHAR(10),
DOB DATETIME
)
As you can see the syntax bridges local variable declaration (DECLARE @variable_name variable_data_type) and table creation (column_name, data_type, nullability). As with any other local variable in T-SQL, the table variable must be prefixed with an "@" sign. Unlike temporary or regular table objects, table variables have certain clear limitations.
* Table variables can not have Non-Clustered Indexes
* You can not create constraints in table variables
* You can not create default values on table variable columns
* Statistics can not be created against table variables
Similarities with temporary tables include:
* Instantiated in tempdb
* Clustered indexes can be created on table variables and temporary tables
* Both are logged in the transaction log
* Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.
Differences between SQL Server temporary tables and table variables
There are three major theoretical differences between temporary tables And table variables
* The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism
After declaring our temporary table #T and our table-variable @T, we assign each one with the same "old value" string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same "new value" string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.
* The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
* Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.
Difference between a "where" clause and a "having" clause.
Having clause is used only with group functions whereas Where is not used with.
SQL Server consists of fourdatabases by default.
Master It contains system catalogs that keep information about disk space, file allocations, usage, system wide configuration settings, login accounts, the existence of other database, and the existence of other SQL Servers (for distributed operations).
Model It is a simply a template database. Every time you create a new database, SQL Server makes a copy of model to form the basis of the new database.
Tempdb Temporary database, tempdb, is a workspace. SQL Server’s tempdb database is unique among all other databases because it is recreated not recovered every time SQL Server is started.
Msdb This database is used by the SQL Server Agent Service, which performs scheduled activities such as backups and replication tasks.
Database Files: A database file is nothing more than an operating system file. SQL Server 2000 allows the following three types of database files:
·Primary data files (.mdf)
·Secondary data files (.ndf)
·Log files (.ldf)
When we create a new user database, SQL Server copies the model database (includes 19 system tables and 2 system views (for backward compatibility)). A new user database must be 1MB or greater in size.
We can create a new database using the following command: CREATE DATABASE newdb
Types of Backups:
* Full Backup
* Differential Backup
* Log Backup
Model It is a simply a template database. Every time you create a new database, SQL Server makes a copy of model to form the basis of the new database.
Tempdb Temporary database, tempdb, is a workspace. SQL Server’s tempdb database is unique among all other databases because it is recreated not recovered every time SQL Server is started.
Msdb This database is used by the SQL Server Agent Service, which performs scheduled activities such as backups and replication tasks.
Database Files: A database file is nothing more than an operating system file. SQL Server 2000 allows the following three types of database files:
·Primary data files (.mdf)
·Secondary data files (.ndf)
·Log files (.ldf)
When we create a new user database, SQL Server copies the model database (includes 19 system tables and 2 system views (for backward compatibility)). A new user database must be 1MB or greater in size.
We can create a new database using the following command: CREATE DATABASE newdb
Types of Backups:
* Full Backup
* Differential Backup
* Log Backup
Types of User Defined Functions
User defined Functions in SQL
User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. Data transformation and reference value retrieval are common uses for functions. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.
Advantages of User Defined Functions
Before SQL 2000, User Defined Functions (UDFs), were not available. Stored Procedures were often used in their place.
One of the advantages of User Defined Functions over Stored Procedures, is the fact that a UDF can be used in a Select, Where, or Case statement. They also can be used to create joins. In addition, User Defined Functions are simpler to invoke than Stored Procedures from inside another SQL statement.
Disadvantages of User Defined Functions
User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables.
Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions.
GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.
Types of User Defined Functions
There are three different types of User Defined Functions. Each type refers to the data being returned by the function.
1. Scalar functions return a single value.
2. In Line Table functions return a single table variable that was created by a select statement.
3. The final UDF is a Multi-statement Table Function. This function returns a table variable whose structure was created by hand, similar to a Create Table statement. It is useful when complex data manipulation inside the function is required.
Example for Scalar-valued Function
CREATE FUNCTION dbo.DateOnly
(
@DateTime datetime
)
RETURNS varchar(10)
AS
BEGIN
DECLARE @Output varchar(10)
SET @Output = CONVERT(varchar(10),@DateTime,101)
RETURN @Output
END
To call the function, execute : SELECT dbo.DateOnly(GETDATE())
Example for Inline Table-valued Function
CREATE FUNCTION dbo.FindNamesBy
(
@Name varchar(10)
)
RETURNS TABLE
AS
BEGIN
RETURN SELECT * FROM Employee
WHERE EmpName LIKE '%' + @Name
END
To use the above function, execute : SELECT * FROM dbo.FindNamesBy('Cherukuri')
Example for Multi statement Table-valued Function
CREATE FUNCTION dbo.MultiLineFunction
(
@Name varchar(10)
)
RETURNS @Result TABLE
(
Empname varchar(20),
HireDate datetime,
OnProbation char(1)
)
AS
BEGIN
INSERT INTO @Result (EmpName, HireDate)
SELECT Empname, HireDate FROM Employee
WHERE EmpName LIKE '%' + @Name
UPDATE @Result SET OnProbation = 'N'
UPDATE @Result SET OnProbation = 'Y'
WHERE HireDate < '11/16/2002'
RETURN
END
To use the above function, execute :
SELECT EmpName,HireDate,OnProbation FROM dbo.MultiLineFunction('Ch')
User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. Data transformation and reference value retrieval are common uses for functions. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.
Advantages of User Defined Functions
Before SQL 2000, User Defined Functions (UDFs), were not available. Stored Procedures were often used in their place.
One of the advantages of User Defined Functions over Stored Procedures, is the fact that a UDF can be used in a Select, Where, or Case statement. They also can be used to create joins. In addition, User Defined Functions are simpler to invoke than Stored Procedures from inside another SQL statement.
Disadvantages of User Defined Functions
User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables.
Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions.
GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.
Types of User Defined Functions
There are three different types of User Defined Functions. Each type refers to the data being returned by the function.
1. Scalar functions return a single value.
2. In Line Table functions return a single table variable that was created by a select statement.
3. The final UDF is a Multi-statement Table Function. This function returns a table variable whose structure was created by hand, similar to a Create Table statement. It is useful when complex data manipulation inside the function is required.
Example for Scalar-valued Function
CREATE FUNCTION dbo.DateOnly
(
@DateTime datetime
)
RETURNS varchar(10)
AS
BEGIN
DECLARE @Output varchar(10)
SET @Output = CONVERT(varchar(10),@DateTime,101)
RETURN @Output
END
To call the function, execute : SELECT dbo.DateOnly(GETDATE())
Example for Inline Table-valued Function
CREATE FUNCTION dbo.FindNamesBy
(
@Name varchar(10)
)
RETURNS TABLE
AS
BEGIN
RETURN SELECT * FROM Employee
WHERE EmpName LIKE '%' + @Name
END
To use the above function, execute : SELECT * FROM dbo.FindNamesBy('Cherukuri')
Example for Multi statement Table-valued Function
CREATE FUNCTION dbo.MultiLineFunction
(
@Name varchar(10)
)
RETURNS @Result TABLE
(
Empname varchar(20),
HireDate datetime,
OnProbation char(1)
)
AS
BEGIN
INSERT INTO @Result (EmpName, HireDate)
SELECT Empname, HireDate FROM Employee
WHERE EmpName LIKE '%' + @Name
UPDATE @Result SET OnProbation = 'N'
UPDATE @Result SET OnProbation = 'Y'
WHERE HireDate < '11/16/2002'
RETURN
END
To use the above function, execute :
SELECT EmpName,HireDate,OnProbation FROM dbo.MultiLineFunction('Ch')
Subscribe to:
Posts (Atom)