|
Thank you very much.
Do i have to do them on all stored procedures?
Thanks very much.
|
|
|
|
|
No, only the SPs that are running slow or those that are accessing tables that will become very large in time.
The whole idea is to limit the amount of IO necessary for SQL Server to access the data. Everytime SQL has to hit the disks it costs time. Indexes let SQL find the appropriate rows quickly with limited IO.
Putting indexes on small tables does not improve speed access speed since the whole table can be read in 1 or 2 IO's anyway.
|
|
|
|
|
Indexing a SQL database is quite a topic in itself.
Read up on it - google it and you will find lots of useful information.
One aspect with SQL indexes that is important to understand is whether, or not, to make the indexes clustered indexes.
Creating clustered indexes can massively speed up data access and it can also create a massive slowing down of data access.
Without going into too much detail, and without being 100% accurate, clustering a table means that the last node on the index is the data and not a pointer(as is the case in non-clustered indexes).
This means that if you have a table that has very few updates on it then there is a good chance that a clustered index is what you want.
A table with a lot of updates is generally not a candidate for clustering; as when the index is written a lot of data may have to be moved around on the disk.
Also you can only have 1 clustered index per table.
The above is just to give you a bit of an idea about what indexing in SQL entails.
Understanding indexes is so important for optimising data access that you will be doing yourself a real favour to read up on it.
I hope this helps...
You always pass failure on the way to success.
|
|
|
|
|
hi there,
i'm looking for a free tool to to generate random testdata for sql server. do you have any ideas, experiences, links?
thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
HI
WE HAVE TO STORE IMAGES IN A SQLDATA BASE .
THE IMAGES ARE IN A PATICULAR SPECIFIED FOLDER(I.E. IN F - folder ).
NOW WE HAVE TO PASS THAT IMAGES FROM THAT FOLDER TO DATA BASE.
IN THIS ISSUE WE R FACING THE PROBLEM.
CAN U UR SUGGESSTIONS AND RELATED WEBSITES.
REGARDS
KISHORE
|
|
|
|
|
1 - don't shout
2 - try google, this has been covered hundreds of times, at least.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
|
In case you don't know, all capitals is considered to be shouting. Don't be rude like that, and no textspeak.
"Find it your bloody self - immediately!" - Dave Kreskowiak
|
|
|
|
|
Hi,
I have created a DataSet(dsLocal) containing rows from local database that I wish to update with the rows taken from another dataset (dsWeb).
dsWeb dataset may contain Newly added rows and/or modified rows.
dsweb is populated from the Web server Database and dsLocal is populated from Local database
Both Local and Web server database schema are same.
What dataset methods/properties should i use?
|
|
|
|
|
You will need to loop through the tables in the webservice dataset. For each table you will need to loop through each row. Then you need to check if the row already exists in the correlating local dataset/table. If so, update the row, if not add a new one. I've never found a very nice way to move a row from one table to another. The only way that works for me is to just loop through the columns and move the data column by column. Sort of like this:
For Each col As DataColumn In dtOld.Columns<br />
If dtNew.Columns.Contains(col.ColumnName) Then<br />
rowNew(col.ColumnName) = row(col.ColumnName)<br />
End If<br />
Next
Hope this helps get you started.
|
|
|
|
|
Thanks for your reply.
I will work out the solution provided by u.
And I will let u know if any queries are there.
|
|
|
|
|
Thanks,
Will you please tell me what is this rowNew and row
rowNew(col.ColumnName) = row(col.ColumnName)
Waiting for your reply
|
|
|
|
|
Oops. Sorry. I should have clarified that. The rowNew is a DataRow object. You will want to set it equal to the new datatable's NewRow in order to give it the schema. Like this:
Dim rowNew as DataRow = dtNew.NewRow
The row is also a DataRow object, but it is from the old table. You will want to loop through each row, and then for each row loop through the columns like I showed you before. Like this:
For Each row as DataRow in dtNew.Rows<br />
<br />
'The column loop goes here<br />
<br />
Next
Hope this helps.
|
|
|
|
|
Hi,
I wrote this update query. But it doesn't work.
The error message : An aggregate may not appear in the set list of an UPDATE statement.
Here is my query.
update udtStudent set score=sum(b.studentscore)
from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno
where a.score is null
group by b.empno,b.testid
i wish to update the score = sum of studentscore from udtQuestionHistory
the problem is at sum().Can anyone advice me how should i overcome it?
Thanks in advance.
thanks.
|
|
|
|
|
Eunice (VB junior) wrote: set score=sum(b.studentscore)
from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno
where a.score is null
group by b.empno,b.testid
Try to bring the sum in a select clause and assign it to an integer variable.
For instance,
Declare @intSum INT
select @intSum = sum(score) from student a inner join history b on a.testid = b.testid
Now, @intSum will hold the sum of studentscore from history table.
Later, proceed with your update statement like
update student set score = @intSum where ##your condition##
Hope that thought might help you.
|
|
|
|
|
Hi CS,
thanks for the guidance. I got what you meant.
Here is what i did according to your advice and it works.
Declare @intSum INT
declare @testID int
declare @empno int
select @intSum = sum(studentscore),@testid=testid,@empno=empno from udtquestionhistory
group by empno,testid,questype,testtype,partnumber,process
update udtstudent set score=@intSum where testid=@testid and empno=@empno
Thanks for the help.
|
|
|
|
|
You've already asked this question once. Please don't double-post.
Paul Marfleet
|
|
|
|
|
Hi pmarfleet,
Actually there are different questions. The 2nd question is about subselect. But i'm using the same table as my query testing. Anyway, thanks for the input.
|
|
|
|
|
i have a datetime column field in my table, after declaring it as datetime , i saw the following options under it
description
default value
precision
scale
identity
identity seed
similarly there is a function
how can i pass a funciton name in order to enter the date in teh sql server in the following format?
12-jan-2000
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Sonia Gupta wrote: similarly there is a function
how can i pass a funciton name in order to enter the date in teh sql server in the following format?
12-jan-2000
As Colin explained in your last post, this is presentation issue. Datetime field can't store values like what you expect, but it can be done by converting it to the format what you needed. See the following query to understand how to convert datetime value.
SELECT CONVERT(VARCHAR(20),GETDATE(),106)
Hope this helps
|
|
|
|
|
hi all,..
i want to retrive each row with the help of pointer and also the access row ll go to last one. for example
have one table name like sam. that table contain three rows like
proId proName price
------- ------- ------
P101 BOOK 600
P102 PEN 200
P103 PAPER 100
In this table i retrive first row for first request. in next req i want to access sec row as well as the first row goes to last etc..
the output like,.
in sec req my table like following format,
proId proName price
------- ------- ------
P102 PEN 200
P103 PAPER 100
P101 BOOK 600
third req my table like,.
proId proName price
------- ------- ------
P103 PEN 200
P101 PAPER 100
P102 BOOK 600
if anybody have solution reply me asap,,
regards,
syibu...
|
|
|
|
|
Hi,
I wish to update 3 data to udtstudent. These 3 datas are taken from udtQuestionHistory.
How should i write?? I tried the below query. But it doesn't work. Can anyone help me?
Thanks in advance.
my query:
update udtstudent set(score,Testdate,MarkDate)in(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid)
where TestID =1 and EmpNo=35
Thanks.
|
|
|
|
|
Eunice (VB junior) wrote: update udtstudent set(score,Testdate,MarkDate)in(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid)
where TestID =1 and EmpNo=35
Try this:
update udtstudent set a.score=b.scoreSum,a.TestDate=b.Testdate a.MarkDate = b.MarkDate from udtstudent a,(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid) b where a.TestID =1 and a.EmpNo=35
I didn't test it.
Also check your relationships and make certain that only one row is returned from the sub query!
|
|
|
|
|
Hi EvilNoodle,
Thanks for the answer. Initially it doesn't work. But i altered a bit from ur query and it works already. Thanks.
Here is the query after modified.
update udtstudent set score=b.scoreSum,TestDate=b.Testdate,MarkDate = b.MarkDate
from udtstudent a,(select sum(studentscore)as scoreSum,TestDate,MarkDate from udtQuestionHistory
where TestID =1 and EmpNo=35 group by empno,testid,testdate,markdate) b
where a.TestID =1 and a.EmpNo=35
Thanks for the help.
|
|
|
|
|
Try this:
<br />
update udtstudent <br />
set(score,Testdate,MarkDate)=<br />
(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid)<br />
where TestID =1 and EmpNo=35 <br />
You always pass failure on the way to success.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
