Can I have index created on temp tables (#temp) which are created dynamically in a stored procedure?
I am creating temp tables ( #temp_table ) in my stored procedure. It is a huge table with large data. Then I am creating a index in the storeed procedure as it is required for faster query to the temp table. But when I execute the stored procedure, the...
Started by Nirmal Singh Raja Reegan on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
Reference: Optimizing....
This usually happens because the access plans for the temp tables have been generated before / Indexes on Temp Tables
as the temp table, the optimizer will always be able to use these indexes.
|
|
Hi Everyone,
This is really a two prong question.
One, I'm experiencing a phenomenon where SQL server consumes a lot of tempDB log file space when using a global temp table while using a local temp table will consume data file space?
Is this normal? I...
Started by JayRu on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
Wanted to know about the tempdb database
What is the lifespan of one of these global temp tables? Are they dropped in a reasonable time? "Regular" temp tables get dropped when the user disconnects, if not manually before ....
|
|
Why does the use of temp tables with a SELECT statement improve the logical I/O count? Wouldn't it increase the amount of hits to a database instead of decreasing it. Is this because the 'problem' is broken down into sections? I'd like to know what's ...
Started by nfootit on
, 5 posts
by 5 people.
Answer Snippets (Read the full thread at stackoverflow):
The temp tableAFAIK, at least with....
I'm going to assume by temp tables you mean a sub-select hitting the base tables multiple times when only a subset of the records are needed.
Will reduce the logical IO for these queries.
|
Ask your Facebook Friends
|
Is a temp table local to the thread or global to the server?
Started by Byron Whitlock on
, 5 posts
by 5 people.
Answer Snippets (Read the full thread at stackoverflow):
They are visible in the current....
Local temp tables can be created using hash (#) sign prior to table name.
temp is session scope
##temp is server scope
MSDN:
Local temporary tables are visible only tables.
|
|
Given a table or a temp table, I'd like to run a procedure that will output a SQL script (i.e. a bunch of INSERT statements) that would populate the table. Is this possible in MS SQL Server 2000?
Thanks!
Started by RiskManager on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
DECLARE MY_CURSOR Cursor FOR Select Year, HolidayId, Date, EffBegDate, isnull(EffEndDate,'') AS EffEndDate, ChangedUser From HolidayDate Open My... .
You can get the idea from this.
I just did one yesterday.
You can create a script to do it using a cursor .
|
|
I'm trying to convert the permanent tables used in a stored procedure to
global temp tables. I've looked at the stats on these permanent tables
and some have tens of millions of rows of data and are on the order if
gigabytes in size (up to 10 GB).
So,...
Started by Dragos Toader on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
Of your TEMP tablespace or create a separate temporary tablespace just for the user(s) that will own these objects if you wanted to segregate these large temporary tables into a separate tablespace, I would be very curious about why temporary....
|
|
I have a report which on execution connects to the database with my_report_user username. There can be many end-users of the report. And in each execution a new connection to the database will be made with my_report_user (there is no connection pooling...
Started by peakit on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
I find #temp tables can be usefulNeither
If you want to cache....
Reading up on database warehousing will definitely help you.
Temp tables, bot #local and ##shared have a lifetime controlled now.
Own result set cache database.
|
|
Very quick and simple question. I am running a script to import data and have declared a temp table and applied check constraints to that table. Obviously if the script is run more than once I check whether the temp table already exists and if so, I drop...
Started by FailBoy on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
(And sometimes, creating a "permanent" temp table can.
Yes - dropping the temp table will drop any internal dependencies as well, including your it - that is typically a lot faster.
|
|
Hi there,
I'm troubleshooting a nasty stored procedure and noticed that after running it, and I have closed my session, lots of temp tables are still left in tempdb. They have names like the following:
#000E262B #002334C4 #004E1D4D #00583EEE #00783A7F...
Started by Mark Allison on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
Therefore any new.
Www.sqlservercentral.com/Forums/Topic456599-149-1.aspx
If temp tables or table variables are frequently usedTables created with the # prefix are only available to the current connection.
|
|
I have a query that looks like
SELECT P.Column1, P.Column2, P.Column3, ... ( SELECT A.ColumnX, A.ColumnY, ... FROM dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A WHERE A.Key = P.Key FOR XML AUTO, TYPE ), ( SELECT B.ColumnX, B.ColumnY, ... ...
Started by Joseph Kingry on
, 7 posts
by 7 people.
Answer Snippets (Read the full thread at stackoverflow):
Using....
What you do with the temp tables Spool or a Worktable
You can reuse the procedures without temp tables, using CTE 's, but this works well and is quite efficient.
Your procedures are being reevaluated for each row in P .
|
