Temporary Indexes?

Apr 11, 2008

are there such things as "temporary indexes"?

DBA noo13 alert: i've never heard of them, but it won't surprise me to learn that they exist

on the other hand, the guy who posted this could just be dreaming...in addtiton to Oracle, Sybase and MS SQLServer employ temporary indexing when a needed index has not been implemented.
that would be a lovely trick, wouldn't it? a join query is particularly slow because todd forgot to declare indexes, and the dbms picks up on this and creates them on the fly...

View 14 Replies


ADVERTISEMENT

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

Possible To Have Temporary Rows In A Non-temporary Table? Lifespan On Rows..

Mar 14, 2004

I have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.

My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?

View 2 Replies View Related

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.

Sep 18, 2007

So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

View 4 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.

In order to do that, I have taken the records based on the fragmentation %.

But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 Replies View Related

Indexes Vs Clustered Indexes

Sep 17, 2006

What is the difference please?

View 1 Replies View Related

A Temporary Database

Feb 9, 2004

I have three aspx pages with one form in every page, these three pages allows to register a user. Now when I submit every form on every page the data is sent to the database, but somebody has recommended me to store data in a temporary database file, and when user submit last page (form) store all the data in main database.

I use Sql 2000 Server Personal Edition and VB.NET for aspx pages.
How can I use this temporary database in my aspx page?

Now my first form-page:

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>


<script language="VB" runat="server">
Dim strConnection As New SqlConnection(ConfigurationSettings.AppSettings("myConn"))

Sub Send_data(sender As Object, e As EventArgs)

Dim CmdInsert As New SqlCommand("new_user1", strConnection)
CmdInsert.CommandType = CommandType.StoredProcedure

Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = CmdInsert

CmdInsert.Parameters.Add("@Id", SqlDbType.bigint)
CmdInsert.Parameters("@Id").Direction = ParameterDirection.Output

CmdInsert.Parameters.Add(New SqlParameter("@e_mail", SqlDbType.varchar, 50, "e_mail"))
CmdInsert.Parameters("@e_mail").Value = mail.Text()
……….

strConnection.open()
CmdInsert.ExecuteNonQuery
Dim IdUser As integer = CmdInsert.Parameters("@Id").Value
strConnection.close()

End Sub




Thanks

View 10 Replies View Related

Temporary Tables

Mar 15, 2004

Can you create a temporary table using an ad-hoc query?

What I am trying to do is a type of filter search (the user has this and this or this) were i will not know how may items the user is going to select until they submit....that is why i can't use a stored procedure(i think)....any help on how to do this?

Thanks,
Trey

View 8 Replies View Related

Temporary Tables?

Sep 12, 2004

Hi could anyone give me a hint what does term "temporary table" mean regarding sql server?

View 1 Replies View Related

Temporary Fields

Sep 21, 2004

I am having a problem with a script I have written. The script adds several temporary fields, performs some updates and then drops the temp fields. The problem is that Query Analyzer attempts to validate the update statements before the fields have been added and errors out with Invalid Column Name. The script works fine if I execute the alter statements first and then the update statements. Is there a trick to tell Enterprise Manager not to validate the statement until it is ready to execute it.

I believe I recall having a similar problem in Stored Procedures a while ago.

Thanks,
Ken Nicholson

View 1 Replies View Related

Temporary Tables

Nov 7, 2005

Hi all,
how can i execute this query without errors??

create table #luca(c int)
drop table #luca
select * into #luca
from anagrafica_clienti

The error lanched is:
Server: Msg 2714, Level 16, State 1, Line 6
There is already an object named '#luca' in the database.

Why if i drop the table?How can i do?Thanks guy

View 3 Replies View Related

Temporary Tables

Jan 15, 2007

Afternoon.

I'm having trouble with a query and ASP. The query itself is easy. I need a temporary table to be filled with the contents of a select and then i need to select out of the temporary table and return the results to the ASP.

So:


Code:

DECLARE @RESULTS TABLE (
ItemID int,
ItemDescription char(50),
ItemType int,
ItemRequestedBy char(50),
ItemStatus int,
ItemQuantity int,
ItemCostPer money,
ItemOrderNumber int,
DateAdded smalldatetime,
DateLastEdited smallDateTime
)

INSERT into @results (ItemID, ItemDescription, ItemType, ItemRequestedBy, ItemStatus, ItemQuantity, ItemCostPer, ItemOrderNumber, DateAdded, DateLastEdited)
SELECT * from cr_EquipmentData

SELECT * from @results



When I run this in Query Analyser, I get exactly the results I need... But when I try and run the ASP script, I get an error about the recordset not being open. (It's not the ASP checking 'cos when I run a simple select statement it works)

I appreciate there is no use for the query as it stands, but eventually I want to be able to perform not destructive statements on the @results table before returning the data to ASP. This is more 'testing' than anything at the mo'

Has anyone got any ideas?

Thanks...

View 3 Replies View Related

Temporary Table

Feb 1, 2004

Hi

I am using SQL 2000 I have the following code. When saving it, I am getting an error:
There is already an object named '##tbl' in the database

This is although #tbl is dropped.
Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.

declare @x int
set @x=1
IF @x=0
begin
create table #tbl (
abc int
)
drop table #tbl
end

IF @x=1
begin
create table #tbl (
abc int
)
drop table #tbl
end

Than you.

View 5 Replies View Related

A Temporary Database

Feb 10, 2004

Hi !

I use Sql 2000 Server as a database and an ASP.NET application with VB.NET language.

Now I am working with three pages with one form in every page that allows to register a user. To accomplish the registration the user needs to fill all the three pages, but now I am sending the data to the database in every page, so if a user leaves the process before reaching the third page it will have an invalid user entry into the database that I don’ t want. To avoid this I was recommended to store the in a temporary database file. I have been searching information about this but I have not found it.

Somebody can help me finding the necessary documentation to achieve it please?


Thanks

View 4 Replies View Related

Temporary Tables

Apr 14, 2004

I am writing a stored procedure that outputs it's information to a temporary table while it assembles the information. Afterwards, it returns the contents of the temporary table as the results of the stored procedure.

I found that if you create the table, inside the SP, as an ordinary table, the information builds to that table considerably faster than if you use a true temporary table.

I found that if I create a user function that returns a table as it's return value, it is also as slow as if I used a true temporary table.

The database can amass over 2 million records in one table in just a few days. If I have the procedure query against this table, and output to an ordinary table it creates, and summarize the information it is adding to the table, then it takes an average of around 4 minutes to return the results from the query. If I change the output table to a temporary table (#temp), it between 12 and 15 minutes. Nothing else in the procedure changed. Just the kind of table. If I take the logic and move it to a function which returns those results in a RETURN table, it also takes over 14 minutes.

Why would it take so much longer outputing to a temporary table rather than a normal table? Is it because temporary tables are stored in a different database (tempdb)? Why would returning query results from a function be just as slow?

View 14 Replies View Related

Temporary Tables

May 25, 2004

How can I view logs of local (to a session) temporary that are created/dropped?

View 6 Replies View Related

Temporary Table

Nov 8, 2005

Hi

I have to create a temporary table for generating a report in VB.
Pls help how to check the temporary table name in database.
I want if exits than drop and create a new one.


thanks

asm

View 3 Replies View Related

Temporary Table

Apr 11, 2006

I have the following fields in table A:

GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales | -20,000.00 | 01 | 2005
===================================================
1000| Sales | -10,000.00 | 02 | 2005
===================================================
1001| Cost | 5,000.00 | 01 | 2005
===================================================
1001| Cost | 5,000.00 | 02 | 2005

the fields above have the following datatype:

Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger

The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

Description | Period 01 | Period 02 | Year to Date
=========================================================
Sales | 20,000.00 | 10,000.00 | 30,000.00
Total Sales | 20,000.00 | 10,000.00 | 30,000.00

Cost | 5,000.00 | 5,000.00 | 10,000.00
Total Cost | 5,000.00 | 5,000.00 | 10,000.00
=========================================================
Profit | 15,000.00 | 5,000.00 | 20,000.00

The above report would list 4 columns, with the last column being a calculated field as a sum of
Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called
Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).

Guys, hope someone out there can help me with the sql command for the above report?

View 2 Replies View Related

Temporary Table

Jun 20, 2007

How would someone set up a temporary table from a database in order to then run a query against it to get more accurate data??

View 11 Replies View Related

Temporary Table

Jul 29, 2007

Hi,

i have:

[first table]
user_id
user_name
user_password

[second table]
set
item
order

Since the second table is somehow only temporary table (having at the front ajax script), i need the third table to store user's ID and all the info from second table.

What would be the easiest way to do it, since the first table contains only one row per user and the second one 40 rows per user.

thank you for your thought!

View 2 Replies View Related

Using Temporary Tables

Aug 21, 2007

Hi,
I am trying to join two tables usig two temporary tables.I want the Output as table2/table1 = Output

Select temp2.Value/temp1.Value as val
from
(
(
select count(*)as Value from [Master] m
inner join [Spares]s on s.SId=m.SID
where m.Valid_From between '2007-06-01' and '2007-06-30'
)temp1
Union
(
select isnull(sum(convert(numeric(10,0),s.Unit_Price)),'0') as Value
from [Order] h
inner join [Spares] s on s.Number = s.Number
where h.Valid_Date between '2007-06-01' and '2007-06-30'
))temp2
as t


I could not find the output..
Plz help me..

Thanks..

View 3 Replies View Related

Temporary Tables

Nov 2, 2007

Hello,

Our java application is running on oracle and now we would like to port it to sql server 2000. In oracle we have a global temporary tables that has an option on commit to delete rows.
Now I am looking for the same option in sql server but as far as I can see sql server's local temporary tables are visible per connection. Since the connection are shared in the pool it seems I can not rely on local temporary tables since the connection does not get closed at the end of the user's session. I need something that is visible per transaction not per connection.

Is it a better approach just to create a regular table and basically have a trigger to delete all data on commit?

View 2 Replies View Related

Temporary Table

Nov 27, 2007

Hi,

my code looks like this

Declare @Temp Table(id_customer int, id_product varchar(10))
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103')
Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102')
Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')

select * from @Temp
Declare @results VarChar(100)
Select @results = IsNull(@results + '; ', '') + IsNull(id_product, '')
From @Temp
where id_customer = 12
Select @results as all_products


and when i run "Select @results as all_products" what shall i do to get id_customer along? if i do "select id_customer, @results as all_products" I get error in return :(

thank you

View 5 Replies View Related

Temporary Tables

Jul 20, 2005

If a stored procedure invokes another stored procedure that creates atemporary table why can't the calling procedure see the temporary table?CREATE PROCEDURE dbo.GetTempASCREATE TABLE #Test([id] int not null identity,[name] as char(4))INSERT INTO #Test ([name]) VALUES ('Test')CREATE PROCEDURE dbo.TestASEXEC dbo.GetTempSELECT * FROM #Test -- Invalid object name '#Test'.Thanks,TP

View 4 Replies View Related

Temporary Tables...

Jul 20, 2005

Hi, just a quick question regarding performance and speed.Q. Run a complicated query three times or create a TEMPORARY table andaccess it as needed?I have a page where it will be accessed 10,000+ a day.In that page I have an SQL query where it involves 3 different tables(approximate table sizes T1) 200,000 T2) 900,000 T3) 20 records)I'll be running that query 3 times in that page...One to retrieve the content and display it on the page.Second to count the number of records (using COUNT(*) function)And third to retrieve the content regions. (using DISTINCT function)What would be the best way of doing...Running the SQL query 3 timesOrCreate a temporary table and access it as many time as I needRegards,

View 4 Replies View Related

Not Able To Use Temporary Tables

May 8, 2007

Hello,



I would like to know from other members whether they are successful in using temporary tables within a stored procedure and use that stored procedure in a report.



My scenario is like this:



I have a stored procedure A which fetches the data from different tables based on the orderno passed as input parameter.



I have built another stored procedure B with a temporary table created and inserting the rows in to this temporary table based on vendor related specifc orders by calling the above procedure A.



I have used this stored procedure in the dataset created and getting this error:



Could not generate a list of fields for the query. Check the query syntax, or click the Refresh Fields on the query toolbar.



Does anybody encountered this and have a resolution.



Thanks in advance.

View 11 Replies View Related

Temporary Tables

Dec 14, 2005

Hello,

View 6 Replies View Related

Temporary Tables

Dec 12, 2007

Hi All

declare @temp table

([EVENT_TYPE_ID] [int],

[Desc] [nchar](50) NOT NULL,

[Lead_Time] [smallint] NULL)

INSERT @temp

SELECT *

FROM TBL_EVENT_DEFINiTION

The table definition for @temp is the same as TBL_EVENT_DEFINiTION. The problem is I need to add a field to the @temp table and define the values using an update statement. If I include the additional field in the @temp table declaration then I get an error saying something to the effect of the number of fields is different. Is there a way of altering the temporary table to add this field?


Many thanks in advance

Alex

View 4 Replies View Related

Temporary Function?

Aug 20, 2007

Hello,

I need to create functions in SQL Server 2000, but I do not have permission.
Is there any way to work around? It seems there is no temporary function.
Can temporary sp, or temporary tables help?

Thank very much!
-ZZ

View 3 Replies View Related

Temporary Table

Feb 12, 2007

hi!

i used some temporary table in store procedure (sqlserver 2005)

our team have report software calisto .

the calisto use crystal and reports which use

this store procedure .

because of that,

we have list of many temporary table with the same name

#dbo.sug_name ... ,#dbo.sug_name ... ,......

in the system database .

what could be the reason for that and how can we drop it ?

Msg 3701, Level 11, State 5, Line 2

Cannot drop the table '#sug_name', because it does not exist or you do not have permission."

View 10 Replies View Related

Temporary Tables

May 15, 2006

Hello!

I'm creating a temporary table in a procedure to help me with some calculations. I would like the name of the temporary table to be 'dynamic', I mean, to have the hour it was created, something like this: create table #myTempTable15May11:10:00, so if someone access the procedure while it's running, he won't have problems in creating his 'own' temporary table (create table #myTempTable15May11:10:02). Is there anyway I can do this?



Thank you!

View 5 Replies View Related

DTS With Temporary Tables

Apr 26, 2007

Hello,

I tried to make a DTS to transform data in a text file, I used a Store Procedure that use a temp table (#Resultados) but the DTS give me an error.

I read that in this case I can´t use local temp tables but I can use global temp tables, then I changed in my Store, #Resultados by ##Resultados, bu the result was the same.

My Store is likely to his. Please help me.




INSERT ##Resultados (Planta, Etapa,GrupoEquipo,Equipo,Concepto,Fecha,Guardia,

Valor,idConcepto)
EXEC CalculosDiarios @Area,@Reporte,@FechaIni,@FechaFin,0



SELECT LEFT(RP.Grupo,3) + LEFT(RP.Equipo,12) + LEFT(RP.SubGrupo,2)
+ LEFT(D.Fecha,8) + D.Valor as Dato
FROM
ReportesPlantilla RP
LEFT JOIN
##Resultados D
ON
RP.Planta = D.Planta
AND RP.Etapa = D.Etapa
AND RP.GrupoEquipo = D.GrupoEquipo
AND RP.Equipo = D.Equipo
AND RP.Concepto = D.Concepto
AND D.Fecha BETWEEN @FechaIni AND @FechaFin

View 6 Replies View Related

Temporary Tables

Jul 23, 2007

Can some one tell me how to display the fields of temporary table in the report? In the query a global temp table is created and in the end i am displaying the fields of that table. How do i do that?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved