|
really gives us novices a great place to post our beginner questions:
SELECT COUNT(BookTitle) FROM Book WHERE IsFiction = 1;
I am Indexing the IsFiction field, but not sure if I should be Indexing the BookTitle field too? Any expert thoughts?
Thanks again!
|
|
|
|
|
Not based on that query. If the booktitle field is in a join or a where clause then it may need an index. Take a look at the execution plan and see where the main cost is, if it says table scan then an index will help.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am not joining on the BookTitle field...I just wasn't sure if Count needed BookTitle to be Indexed when it aggregates the count command. I will leave BookTitle un-indexed, thanks!
|
|
|
|
|
You could in theory get a faster query if you make a composite index on (IsFiction, BookTitle) and booktitle are having many duplicates.
But this is improbable in this case as booktitles are fairly unique, and you will have extra overhead on the inserts and updates.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
It is normally good practice to have indexes on fields used in where clauses. However, if IsFiction is a bit field, it will have low selectivity (a measurement of how unique each value is) which will mean that it is highly unlikely that the query optimizer would consider using the index. You are much more likely to find that the optimizer is using a clustered index scan or a table scan.
|
|
|
|
|
maybe in need 2 go back to the basics...can anyone give me a link or two of a basic tutorial that talks about optimization...i always put indexes on Where or Order By fields if I am doing joins on the Where, and if I think the tables are going to have a huge number of records.
also, a simple tutorial link about the Query Optimizer and how to use it would be great too.
Thanks!
|
|
|
|
|
|
I am using VB 2008 & MS Access database
I have a table WebRateList with 2 columns ResFrom and ResTo defined as DateTime, another column WebID defined as Text (allow Null)
When I search:
strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "#"
Then it showed "It is in this range!"
But if I scan the same row with column WebID must be emptied, it didn't get in (infact the WebID is empty)
strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "# AND WebID = NULL"
da = New OleDbDataAdapter(strSQL, dbConn)
dt = New DataTable
da.Fill(dt)
If (dt.Rows.Count() > 0) Then
MsgBox("It is in this range!")
End If
Even I defined the following the result is the same
strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "# AND WebID = '' "
Anyone can help? 
|
|
|
|
|
You can't use = NULL, you must use IS NULL.
|
|
|
|
|
It worked!
Thanks for a very quick help 
|
|
|
|
|
This one should be easy  ...
I cant get a simple parametrized update query to function at all...
This is a simplified version of what I am trying to do...
UPDATE TheDatabase
SET ([Vehicle #] = @NewVehNo)
WHERE ([Vehicle #] = @OldVehNO)
I dont enderstand why this is wrong... I suck at SQL lol...
from MSDN i get the format:
UPDATE ShoppingCart
SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)
|
|
|
|
|
UPDATE targets a table, not a database.
suggestion: stop using those field names full of special characters, and get rid of those square brackets then.
|
|
|
|
|
Get rid of the parens. Such as:
UPDATE Thetable
SET [Vehicle #] = @NewVehNo
WHERE [Vehicle #] = @OldVehNO
and I assume you are updating a table - Yes?
|
|
|
|
|
wait yes... i replaced that for some reason... i am updating a table
UPDATE [dbo].[Pickups & Cars]
SET ([Vehicle #] = @Vehicle_NO)
WHERE ([Vehicle #] = @Vehicle_NO)
this doesnt work...
|
|
|
|
|
Get rid of the parens:
UPDATE [dbo].[Pickups & Cars] SET [Vehicle #] = @Vehicle_NO
WHERE [Vehicle #] = @Vehicle_NO
Have tried the SP in Query Analyzer?
|
|
|
|
|
I guess the parens were messing it up... thanks!
|
|
|
|
|
Are you getting an error when you run the update?
Have you tried running the update against a vehicle that you know exists in the database rather than a variable? eg UPDATE .... WHERE [Vehicle #] = 1;
|
|
|
|
|
I would emphasize what Luc said, you need to go through your entire database and remove the spaces and special characters from the table and field names NOW. If you do not do this now it will drive you nuts and may cause compatibility problems in the future. Some tools will not accept [Vehicle #].
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok, Ill restrict my database to letters only... underscores shouldnt cause problems right?
|
|
|
|
|
No underscores are fine they will just irritate you, most developers try and avoid them. Take a look through most sample code in the articles and things like northwind, very few underscores.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
iam usin sql server 2000, the table are as follows
tbluser
========
username nvarchar(50) pk
usertype nvarchar(50)
companyid nvarchar(50) fk
tblcompany
==========
companyid nvarchar(50) pk
company name nvarchar(50)
tblbranch
========
branchid nvarchar(50) pk
branchname nvarchar(50)
companyid nvarchar(50)
location nvarchar(50)
how to write the querry for user belongs to a company all related branches
please give the example which helps me.
|
|
|
|
|
Now you are pushing it, what have you tried?
Use the inner join example and extend it to include the user table.
Don't just ask for the code, put up something you have tried and we may point out whee you are going wrong. Asking for examples all the time is like asking us to write your code for you and is  discouraged.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
iam using this query but not getting the proper result.
select companyname,branchname,location from users,company,branch
on user.companyid=company.companyid innerjoin company.companyid=branch.branchid
i want the user blongs to the company and all related branches can you suggest.
|
|
|
|
|
hi, iam using sql server 2000 , the table fields are
brabchid nvarchar(50),
branchname nvarchar(50),
companyid nvarchar(50),
startdate datetime,
enddate datetime
1)how to querry for list of branches for each company
please give me example which helps me.
|
|
|
|
|
Get a book and work through some examples.
Fire up query analyser and try something yourself
SELECT
C.ComapnyName,
B.BranchName
FROM Branch B
INNER JOIN Company C ON C.CompanyID = B.ComapnID
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
