How many types of temporary tables are there in Sybase? How often have you encountered this question? Pretty much... Right? Yup! And here is the answer to much liked question related to Sybase ASE.
Sybase Adaptive Server Enterprise allows developers to create two different types of temporary tables namely -
The major difference that appears is that session specific temp tables represented by a # (pound symbol) in front of the table name works for the lifetime of a session, which means that once the session is closed the temp table is flushed and all the entries are removed from the system tables. In contrary a tempdb temp table specified by the long name like -
tempdb.owner.temptable
persists even after the session is closed until the Sybase server itself is restarted which will then recreate the tempdb database completely hence removing all the entries from the system catalogue. As session specific table is created and destroyed within a session hence it cannot be used by different users to share the data wherease the tempdb database can be used to share the data between users.
One more difference that exists between these two tables is that session specific table will be suffixed with a 17 digit session number to keep it unique just in case two users in two different sessions create the same session specific temp table with the same name. As the maximum limit of a variable name is upto 30 characters hence we can name a session specific temp table only upto a length of 13 characters before the name will be automatically truncated by the Sybase ASE server. So if we try to name two session specific temp tables like -
#thisis17char and #thisis17characterlong
then the Sybase server will truncate the remaining characters of #thisis17characterlong preceding 13 characters and hence we will be encountered with the error -
Table name already exists in the system catalogue
because temp table #thisis17char already exists.
Sybase Adaptive Server Enterprise allows developers to create two different types of temporary tables namely -
- Session Temporary Table
- Tempdb temporary table
The major difference that appears is that session specific temp tables represented by a # (pound symbol) in front of the table name works for the lifetime of a session, which means that once the session is closed the temp table is flushed and all the entries are removed from the system tables. In contrary a tempdb temp table specified by the long name like -
tempdb.owner.temptable
persists even after the session is closed until the Sybase server itself is restarted which will then recreate the tempdb database completely hence removing all the entries from the system catalogue. As session specific table is created and destroyed within a session hence it cannot be used by different users to share the data wherease the tempdb database can be used to share the data between users.
One more difference that exists between these two tables is that session specific table will be suffixed with a 17 digit session number to keep it unique just in case two users in two different sessions create the same session specific temp table with the same name. As the maximum limit of a variable name is upto 30 characters hence we can name a session specific temp table only upto a length of 13 characters before the name will be automatically truncated by the Sybase ASE server. So if we try to name two session specific temp tables like -
#thisis17char and #thisis17characterlong
then the Sybase server will truncate the remaining characters of #thisis17characterlong preceding 13 characters and hence we will be encountered with the error -
Table name already exists in the system catalogue
because temp table #thisis17char already exists.
No comments:
Post a Comment