6
In the Sqlserver there are temporary and variable table type resources.
What would be the most advantageous or the best option to put in a Procedure which inserts the query that was returned from a SELECT, can store on average 2 thousand records?
1 answer
6
A temporary table is a real table and can do everything a normal table can, SQL Server will do all possible optimizations on it, especially if it has secondary indexes, can be in transactions, respects your database settings anyway, the only difference from the normal table is that it will be discarded at the end of the user session or of all users who use it.
Variables with tables can be easier to use in some scenarios like stored procedures and functions. It can even help their performance itself (not the execution of the query). But you hardly use these things, right? Or you are DBA? : P But there are things that are harder to do with this feature. The variable is a programming resource in the database, not usually used in the normal manipulation of the database, it is a code instrument. Don’t use it for tables with more than hundreds of lines. Think of it more as a array a little more sophisticated than a table itself.
As each has a function they offer more advantages in the appropriate scenario. Interestingly a feature made for programmers is usually used by Dbas who like to play code within the database, and the feature most made for the database itself is preferred by programmers since they program outside the database.
- Documentation of table type.
- One much more complete response in the DBA.SE.
- Blog da Microsoft.
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.