Temporary Tables vs Table Variables - SQL Server Basics

hello friends welcome to sequel with Manoj and today I am going to talk about temporary tables and table variables and the difference between them so coming to the topic temporary table and table variable so these both are temporary tables actually and they are different than the normal tables because of the nature that they are created and the way they behave and the scope of these two temporary tables so we'll be discussing them one by one first of all I will go with the temporary tables and then with the table variables and finally at the end you will be able to know how you can use these two tables instead of the normal tables and where to use them right so let's go ahead with temporary tables so the syntax to create temporary table is very similar to the normal tables that is create table the temporary table name prefixed with a hash and the column list everything is same and I will show you in the demo how to create this table the second point says that a temporary table or temp table is created on disk in the temp DB database so in sequence over there is a temp TV system database where these temp tables are created and stored their whole life and why you create a temp table it is affixed with a session specific ID differentiate between the other named a tables created by other sessions so if two people have logged into a database system in the same database they in create a temp table with a similar name so how to differentiate between these 2 the sequel server database engine internally suffixes some IDs to these temp tables and the name of the temp table is limited to one at a 16 characters so let's see how these temp tables are created okay so I am just going to create a simple assembly temp table you okay so I'll just create with two columns and execute it okay so as you can see here the temp table is created and if we try to do a select star from hash temp tab okay so you can see these two two columns and let's see how these temp table is stored internally so I will go and query the system catalog view that is dot information schema dot tables okay so here as you can see the temp table is created with this particular name right so this is the name that we gave okay and rest of the name sequel server has suffix 2 here so this is because if let's say I go ahead and create a similar temp table in a different session okay so let's see what is the name sequel server has provided here so this actually got created right so let's see so sequel server has created now these temp tables with these two names so as you can see the temporary table where it's uh fixed with a six belongs to this session and temporary table subject with a seven belongs to this session now while querying both of these tables how can you make sure that you which session table you are wearing so let's try to insert a record into this temp table okay one comma one right and I'll insert some different values here 2 comma 2 okay so this session of the same name of template it will show me 2 comma 2 on both of these columns and there should be 1 comma 1 so externally if you see so we are just interacting with a temp table in in different sessions but internally sequels always taking this name and giving you results from the session specific temp table so here this particular temp table and and here this particular template will a seven okay so let's go back to the slide and the third point says that the scope of this temp table is limited to a session like a stored procedure or a set of nested procedures so let's check again this thing let's say what I will do is I will close this session okay so in this session the temp table is still there and if I try to query the information schema so the sick so the other temp table with a seven has vanished it is dropped because that belong to that particular section so the scope of that damn table belongs to a particular session so here this table is still active because the session is live I have not closed this session yeah so let's try to query this table from different session so what I will get as invalid of your think hash temp tab because this table does not belong to this session but this table belongs to this particular session okay let's go back to the slide and move to the fourth point the temp table gets dropped automatically when the session ends or the stored proc execution and or goes out of scope okay so this is the pretty much thing that we saw here so till see that again what I will do is I will create a temp f2 and let's square it from here right so you will see a temp Tab 2 table created over here temp to have to write and if I go ahead and close this session right so this table temp temp 2 gets automatically dropped so you can see here there is only one table that is that is already created here temp tab 2 is lower here right so fifth point says that one of the main benefit of using the hash table as opposed to a permanent table is the reduction of amount of locking required since the current user is the only user access of the table and also there is much less logging involved so as you can see here the temp the scope of the stamp table is limited to this session only and no other session as we saw here no other session can use the stamp tables right I'll again show you so this shows you invalid temp table so if any other section can not query this temp table so the locking mechanism is very reduced and the concurrency is also reduced a lot okay so the six point talks about the global temporary tables they are very much like temporal tables they are also great in MVP and cause less locking and logins than permanent tables but they are visible to other systems also you can create global temples by providing a double hash okay so let's create a global temple here right and let's see the existence of this temp table here all right so this global temp table is created like this so it sequel server does not create a similar name like this because it can be accessed by the other session so this name should be unique across other sessions also so unlike here I cannot query this temp table that belong to other session but I can't query the global template will that belong to other session ok so if I insert some records over here and up to the global temp table right let's say 3 3 I can query these from here also I can query from here also plus I can vary these from here also so the scope of the global tempt a busy cross the sessions so if I want people not to use this global template because right now it is a global so other sessions will also be able to use it so if I want other people not to use it or the other sessions not to use it I have to close this session then only this session will be automatically this global temperature will be logged will be automatically dropped here then nobody could use it here right let's go back so the 7 point says that one can create a desired indexes or tempura tables and these makes use of statistics thus resulting in better quality plan compared to table variables so I can create indexes on these temp tables let's create one index so with these temp tables you can create indexes on top of these to select normal tables and the temp tables can make use of statistics and can make use of all the indexes that are available in sequence over okay so now I'll move to table variables so the syntax to create table variable is very much similar to the syntax to create normal variables in sequence over like declare add the rate sign then the variable name and then a variable type that is table and the column definition we'll see it here a table variable is also created on disk and MDV system database but the name of this table variable is generated completely by the sequel engine and it also differs from the other similar Lane tables created in same or other sessions okay so I just remove all this and I will create a table variable so to create a table variable what you have to do is you have to write declare the table variable name as table and the column names called one int called two int okay so so this is the way you can create table variables let's query this table variable okay so here as we already declared this table variable but if I am trying to retrieve values from it it is not giving so these are variable so you conclude a declare statement as well so as you can see here column 1 and column 2 are the columns of this table variable okay now let's see the existence of this table variable in information schema MDV dot information schema or dot tables so if you can see here although you give it a different name like authority where it is created with an entirely different name by sequel server angel the third point says that the scope of a table variable is limited to its batch only like other variables contrary to the temporary tables they are not visible in nested rocks and in a sec statements okay so what does it mean so as I already showed you you know I declared a variable and I then selected but if I do it two different matches in the first which let's and I create this table variable and that's in the second batch if I try to use it it cannot use it it says that you must declare a table variable so I have to use it in a same batch right so this kind of the same batch or even you can use the batch separator also let's say I have a batch separator like this right if I'm going to use them in separate batches then I cannot do that so these two statements thus the Declaration of the table variables and the usage of table variables should be in same batch like this not only this when I'm even I'm inquiring about a table variable in this function schema it won't show me anything here it will it won't show me anything here in the query results because the batch ended here so I have to include this thing also in the fatty batch then only it will show me the table variable name because it exists till the back separator that is go so the life cycle of this table variability where is till here only so it is declared here used here and here and after this go it is just dead it does not exist table variable gets dropped automatically when the batch ends here after the go back separator or stored prop or function execute ends or goes on to the scope so as we saw here the batch separator like oh what if this variable is part of stored proc if this code prof ends the variable is also cleared out of memory okay the first point says that a table variable is created in memory this is a myth the they are also created as temp table and created in MDB but they perform slightly better than temp tables because there is even less locking and logging incredible variable so as you can see here the existence of this table variable is even smaller compared to temp tables so the locking and the concurrency is very less compared to table variables also 6-point is table variables are the only way you can use DMS statements on temporary data within a UDF you can create a table variable with an IDF and modify the data using one of the table statements this is not possible with temp tables okay so if you are using UDF's like user-defined functions inside those user-defined functions you can just use table variables you cannot use temporary tables or normal tables seven point is the table variable will always have a cardinality of one the statistics are not tracked for them and may result in a bad query plan so if you are using table variables for a complex query and you are expecting some performance out of it then the table variables are bad candid of it because the table variables the cardinality of records is only one so if you see in the execution plan the estimated number of rows you will always see one and irrespective of the number of rows are there already in the table variable so in that case you should use temporary tables now we came to the limitation with table variables so there are some limitations with temporary tables also but there are more limitations with table variables that we see here okay so table variables do not participate in transactions and locking so if you have a transaction explained transitive big intron and commit Ron so table variables are not part of it so let's have a small demo here begin Tron and let's have these also here okay so if I secure this and I declared a variable and I started a transaction I'm inserting a row with values four and four then I am rollback in this transaction and after that I expect that after roll back these well disconcerted value should also get rolled back but no this shows me that the table variables does not honor transactions okay you cannot use a table variable in either of the following situation insert table exe c SP some procedure starting in sequence over 2005 this elimination on removed and table variables can now be used as destination for insert exe C commands select start into add very table from some other table okay so you cannot do a select to start into a table variable from some other table but you can do with empty tables hash tables or with the normal tables third is you cannot truncate our table variable okay so let's see here so I created this table variable and let's try to truncate it yeah so it says bull correct syntax so you cannot truncate or table variable table variables cannot be altered after they have been declared they cannot be altered because they are not created with create table statement it's a variable so so any changes you have to do you have to do it as a design time you cannot add a column or you cannot change the data type of any column or you cannot remove or add a particular column or you do not exploit Li add an index to a table variable however you can create an index in line through a primary key constraints and multiple indexes one to e construct so after I created this table variable I cannot add and condense this like the way I did with temporary table I created an index on top of this I cannot do that here to create I can just create a primary key or a unique key in only in line here while I am declaring it so I what I can do is I can provide a primary key over here and I can provide a unique key over here so this is the only way I can create okay just unique so this is the only way I can create indexes also adding primary key we create a clustered index on this table variable and the unique you will create and not cluster index on this particular table you cannot create a named constraint on a table variable you cannot use a UDF in a check a string and computed column or all default and strained you cannot use a UDF in a column condition unlike a temporary table you cannot drop a table variable when it is no longer necessary you just need to let it go out of scope you cannot build the table variable inside dynamic sequel's okay so if you are using dynamic sequel so the you should not use table variables table variables are not visibly to the calling procedure in the case of nested score talks it is possible with the temp tables only you cannot insert expert values into an identity column of a table variable the table variable does not support set identity on so this is but this is possible with temp tables and the normal tables okay so now when to use either of them we saw the temp tables you can create by simple create table statement and after that also you can alter them and they support index also and thus you know they will on on the statistics on the table but on the other side of the table variables you have to just declare them just like variables although they will sit on MTBE but you cannot create exploited indexes on it and they will not honor statistics so when do you use either of them both are good features in sequel server and lot of developers use either of them so use temporary tables when you are dealing with a large volume of data set so why large well over to the side because on a large temporary table which contains a thousands or 100 thousands or millions of records you can create indexes on top of the index it will also use statistics to do an accurate cardinalate estimation thus it will go back up very plan when to use table variable when you are dealing with smaller data sets when indexes are not necessary in a table and the statistics and the cardinality does not matter a lot okay so by now I think you would have gained knowledge on both temporary tables as well as stable variables and I think you should be using both of them in a proper and good way and thank you for watching this video and please let me know your comments Thanks

Channel: SQL with Manoj

Views: 40,126

Keywords: SQL Server, SQL (Programming Language), Temporary Tables, Table Variables, Microsoft SQL Server (Software), TSQL, Transact-SQL (Programming Language)

Id: IQ8oTLYqvj4

Length: 19min 47sec (1187 seconds)

Published: Mon Jul 06 2015