.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

How index will effect in update query

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
Hi All, I have one update query it has lot of joins with other table ,If we add index in the secondary table with this join condition will it improve the performance? eg: UPDATE FunctionalAcknHeaders  SET cStatus = 'E'  FROM FunctionalAcknHeaders fh   LEFT JOIN OrderHeaders oh ON cast(oh.nOrderHeaders_id as varchar(10)) = 'abc'  WHERE oh.nOrderHeaders_id is null   AND fh.cStatus = 'R'   if i create index on OrderHeaders  table nOrderHeaders_id, will it improve the performance ? Thanks, Roshan

View Complete Post

More Related Resource Links

Running a single SQL update query on multiple foxpro databases

Hi, Env : SSIS 2008 My requirement is to run a update SQL statement on multiple foxpro databases. The fox database paths are available in a SQL server table.  I've created a for each loop control and assigned the database path to a variable named dbpath. Within the loop, I added an "execute sql task" control. Now what properties are to be set to get the requirement done. Appreciate any help. Regards. BH  Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

SQL Query index

Hi, I am working on WM 6.0 MC75 device.  I am creating database using SQL compact server 3.0.  I have almost 30K records in device. When I execute simple query with Order BY it takes almost a minute to fetch data. Then I used index for where clause. It becomes fast and the time reduced to 7-8 sec.  But it creates problem in Insert/Update. As this takes ____ of time in inserting data to table. I dont know how to proceed further. As i stucked here. Please give ma a direction   Regards  Shruti

Query DB and update Check Box List on JS/jQuery Function

Hey guys.  I'm not sure if this is the right forum to post this in but I've come to a roadblock in my app and I'm not sure which way I should go.  Currently, I have a slider control and two text boxes used to set the Start and End times of a section of a video.  Let's say the video is of some Music Awards.  In my database, I've tagged the start and end time of each celebrities on-screen appearance.  When the user adjusts their start and end times on the page, I want to query the database table and pull back all celebrities within that range.  The problem is I don't know how to do this in JS.Can I use the Check Box List and bind the values to it?  Do I need to render a table of Check Boxes in JavaScript?  Do I call the Server-Side code via Ajax and then partially render the page?Any help, comments or suggestions would be great.

Select and update in same query

Hi all, I need to update the selected rows from a select statement and update it using an update query. For that the query i am using is UPDATE SERVERACCOUNTSTRAN..TRAN_JOURNALDET SET CONTRATRANSACTIONHEADCODE=T.CODE FROM ##TRAN TR INNER JOIN SERVERSSRETAIL..TRANSACTIONHEAD T ON T.SHORTNAME=TR.CREDT_ACCT WHERE T.COMPANYCODE=1 AND CONTRATRANSACTIONHEADCODE=0 Now the problem is all the values of contratransactionheadcode field is updated to 18. But the real values are different for each row. only the first row value is 18 which is updated to every other row. Thanks in advance.Sujith Kumar www.sujithkumar.tk

Check Index Fragmentation query

Hi, I have ran the following query to view the fragmentation on my large table with 1000+ pages. Now the results I got are moslty at index level 0 and 1 and most objects are fragmented 90+%. My question to the experts is that the query is displaying fragmentation mostly at Index level 0 and 1. Do I need to look or just Level = 0 is fine? The query is SELECT DB_NAME(SDDIPS.[database_id]) AS [database_name], OBJECT_NAME(SDDIPS.[object_id], DB_ID()) AS [object_name], sddips.[object_id] as [Object_ID] ,SSI.[name] AS [index_name], SDDIPS.partition_number, SDDIPS.index_level ,SDDIPS.index_type_desc, SDDIPS.alloc_unit_type_desc, SDDIPS.avg_fragmentation_in_percent], SDDIPS.[page_count] FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'detailed') SDDIPS INNER JOIN sys.sysindexes SSI ON SDDIPS.OBJECT_ID = SSI.id AND SDDIPS.index_id = SSI.indid WHERE SDDIPS.page_count > 1000 AND avg_fragmentation_in_percent > 15 AND index_type_desc <> 'HEAP' ORDER BY OBJECT_NAME(SDDIPS.[object_id], DB_ID()), index_id

Can anybody Identify whats the error in this update query ??

I am getting error as Invalid Column name 'JAVA' when i am havin a value JAVA in txtExamName text box.In my database ExamName field is having a data type nvarchar(25)."UPDATE Exam SET ExamName=' " + txtExamName.Text + " ',SubjectId=" + SubId + ",StartDate=' " + txtDate.Text + " ',StartTime=' " + txtHour.Text + ":" + txtMinutes.Text + " ',EndTime=' " + txtHour0.Text + ":" + txtMinutes0.Text + " ',ExamDescription=' " + txtDescri.Text + " ',TotalNoOfQuestion=" + txtQue.Text + ",MarkofCorrectAns=" + txtPoint.Text + ",MarkofWrongAns=" + txtExamName.Text + " WHERE ExamId=" + Convert.ToInt32(Session["ExamId"])

help with query - update from 2 tables

hi i have 2 tables     table1: num,X,Y     table2: num,X,Y i need to update X and Y in table1 where table1.num = table2.num how to do it ? i need it in Oracle query (i think that in sql server it will work too) thank's in advance 

Why does'nt an UPDATE query finish

I have been asked the following question in one of SQL interviews: "An application issues an UPDATE statement to a table. But the UPDATE query never ends i.e it is going on for ever. What might be the reason?" I gave various reasons: --> May be the table is blocked by some other query. --> May be the query is updating a huge chunk of data. --> May be appropriate indexes are not created on the table. --> May be the indexes are defragmented. --> May be the statistics on the table need to be updated. --> May be the server is too busy to process the request. But the interviewer said all the above conditions are satisfied i.e. the query is updating only 100K rows and the table has appropriate rebuilt indexes with statistics updated and the server is not very busy. There is no blocking as well. What else could be the reason?   Thanks, Chaitanya

Query doesnt use right Index

Hi experts, This is really strange issue.  I have created a table on my dev environment first. Copied the data from another table. Created Indices / Pk and then used a query which doesnt want to use right index. I have NC Index on my Lastupdate , Log , type colmns and I have CI on my Identity col called "CluIndex". And when I use a query "select * from table where lastupdate >= '09/16/2010'", it takes for ever to get the result back. And when I check the Display query exec plan, it shows its using CI which is not right. If I use query hint with the right index, the results are quick. So basically query plan is not using the right index. Weird thing is that, this same table (I created myself with same steps) uses the right Index in production. Very strange!!!

Update query help Dense_Rank .....

Hi,  I need help updating MinAmounttBilled and Dense_Rank_ fields in the table @MinNums_TestData below. MinAmountBilled - For each customer this field should be populated with the smallest value in the AmountBilled column. Dense_Rank_ - This field groups all same customer numbers, need to know how i can update this filed as well. Actuall table i will be updating has 10 million records. Here si the Sample data :- -- Create Sample Table Declare @MinNums_TestData TABLE ( CustomerNumber [varchar](20) , Dense_Rank_ [varchar](20) , AmountBilled [varchar](20) , MinAmounttBilled [varchar](20) ) -- Test data INSERT INTO @MinNums_TestData ( CustomerNumber, Dense_Rank_,AmountBilled,MinAmounttBilled) SELECT '100', '', '15243' ,''UNION ALL SELECT '100', '', '1243' ,''UNION ALL SELECT '100', '', '34243' ,''UNION ALL SELECT '100', '', '43' ,''UNION ALL SELECT '200', '', '56' ,''UNION ALL SELECT '200', '', '65' ,''UNION ALL SELECT '200', '', '34243' ,''UNION ALL SELECT '9', '', '6' ,''UNION ALL SELECT '9', '', '5' ,''UNION ALL SELECT '20', '', '34243' ,''UNION ALL SELECT '200', '', '56' ,''UNION ALL SELECT '3', '', '2' ,''UNION ALL SELECT '1', '', '3' ,'' -- Table to group all customers Declare @MinNums_TestData_2 TABLE ( CustomerNum

Re-index or update statistics after purging data

All,   I’m purging 250GB from a 1TB Multi SQL server databases in one weekend.   After running a re-indexing job do I have to run update statistics?   It takes about 5 hours to run 5% stats, so it will take a loooong time to do 100%.   I have been told that I don’t need to run update stats if I ran Re-Indexing, What do you think?

Cluster index update


Hi, I'm having a deadlock issue. I noticed that I've 2 queries one doing an update and the other doing an insert,

My table has a cluster index on the ID which is an identity field. The update statement is updating other columns which are not included on the cluster index, however on the execution plan it shows that it will need to do a cluster index update.

What's the reason it could be that the cluster index needs an update if the column itself is not being updated?

Thanks in advance.


How do I create a query that joins two tables and returns a single entry with the most recent update


Say I have the following two tables (Purchase & Customer). 

CustomerID StoreName PurchaseDate
1 Asheville 9/14/2010 15:49

update query having minimum of expresion



I have this 3 tables:

CREATE TABLE [dbo].[TC_103_M](

	[TIP_REC] [int] NULL,

	[ID_CMP] [int] NOT NULL,

	[ID_FILE] [int] NULL,

	[CUT_OFF] [datetime] NOT NULL,

	[FIN_DTST] [datetime] NULL,

	[FIN_DTVA] [datetime] NULL,

	[FIN_DTSC] [datetime] NULL,

	[FIN_DIV] [varchar](3) NULL,

	[FIN_TIPTAS] [varchar](2) NULL,

	[FIN_PREAMM] [varchar](1) NULL,

	[FIN_RIMB] [varchar](2) NULL,

	[FIN_DTIA] [datetime] NULL,

	[FIN_DTCA] [datetime] NULL,

	[FIN_FRCAP] [varchar](8) NULL,

	[FIN_AMERO] [float] NULL,

	[FIN_AMNOM] [float] NULL,

	[FIN_DEBRES] [float] NULL,

	[FIN_AMINS] [float] NULL,

	[FIN_EAD] [float] 

Update query from select statement


Cana you I need to do an update from a select statement...i'm really close here but have not quite figured out the nuance...

the full select statement provides correct results and i've joined the outer table with the inside table....can you make recommendations?

BEGIN TRAN --rollback

UPDATE geprs_price..T_ITEM_PRC_CHG 


		FROM geprs_price..T_ITEM_PRC_CHG gep
		and gep.prc_eff_dt = dm.prc_eff_dt
		LEFT JOIN construct..T_AD_USERS con
		ON dm.CONFIRM_BY  = con.UID
		LEFT JOIN construct..T_AD_USERS crt
		ON dm.CREATE_BY  = crt.UID

		AND dm.PRC_TYP_ID in (39)



Adding a new column in update query


Hi all,


Is it possible to add a new column in an update query? I am joing two tables  with [Encountered Month] from Table 1 and [Calendar Date] from table 2 and trying to add one column[Fiscal Month] into the first table where  dbo.table1.[Encountered Month]=dbo.table2.[Calendar Date]

Appreciate your help.

update query qith inner join


i have 3 tables ,and i have inserted data in table A from  tables B and C into it

"my A table has column"
Coe_Code , Coe_name , Sdo_code, Sdo_name

"B table has column"

COE_ID,  Coe_Code , Coe_name  (COE_ID is auto incremented)

C table has column

Sdo_id ,Sdo_code , Sdo_name, COE_ID  (Sdo_id is auto incremented and COE_ID coming from B table )

i have inserted data into B table from A
after that i have inserted data into C by taking column from A and B as you can see column and table above by using this query

[insert into C (Sdo_code,Sdo_name,COE_ID ) 
select A.Sdo_code,A.Sdo_name,B.COE_ID from A,B where A.Coe_Code =B.Coe_Code

but my problem arise when i need to update my C table ,i need to update only 2 column (Sdo_name,COE_ID ) in C table coming from 2 different table
i tried so many query using inner join with update  but was not able to update that..

if some one could help me to provide the query..i also tried this query but didnt work...
[update C set Sdo_code=A.Sdo_code,COE_ID=B.COE_ID from C innerjoin A on A.Sdo_code=C.Sdo_code innerjoin B on C.COE_ID=B.COE_ID]

please provide me the query or help me in what ever way you can

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend