Want to different types of table types in sql server2000!!! - 02-16-2005 , 05:21 AM
I have a stored procedure which uses few tables. One of them is a very big
table which has more than 3 crores of records. whenever that stored proc is
executed invariably blocks would come in my database.
Now, here I want to know is there anything like in oracle, sql server has
different table types? (eg:index-oriented,temp,etc...) and I beleive oracle
has 5 types. Each are used for different logic, one is for heavy insert and
one is for hugh data select and so on... like that do we have table types
in sql server?
Pls, let me know..
I'm extremely over pressured on this issue. I really appreciate who will
put effort to resolve my problem.
Message posted via http://www.sqlmonster.com
Re: Want to different types of table types in sql server2000!!! - 02-16-2005 , 06:25 AM
The three different "types" of tables in SQL Server are:
Temporary tables (where the name starts with #).
Table variables (introduced with SQL server 2000).
Table variables (TV) are handled pretty much same as temp tables "under the hood". Data is
physically stored in tempdb (if need to spill to disk), and there is less logging to transaction log
compared to ordinary tables. But SQL Server doesn't create statistics for TVs, so for larger
datasets, temp tables are often better than TVs.
If you give us more information, we can probably give more specific recommendations.
Tibor Karaszi, SQL Server MVP
"lakshminarayan iyer via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote
Re: Want to different types of table types in sql server2000!!! - 02-16-2005 , 08:37 AM
If I'm translating this correctly, your table has 30 million records in it.
That in itslef should not cause any problems. Do you have an index on this
table? And are you using this index in the stored procedure? If you can tell
us what the stored procedure looks like and the table structure, it would be
really helpful in finding the problem(s).
"Tibor Karaszi" wrote: