|
Just curious to know, what makes you think that SQL Server will not use indexes for this query?
SELECT TOP 100 * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBith IS NULL OR Contacts.DateOfBith = @DateOfBith)
AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)
|
|
|
|
|
I am trying to stay database agnostic on this, I might be wrong on some databases.
When you write the query below in a stored procedure, the database engine will typically build an execution plan for it.
SELECT TOP 100 * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBith IS NULL OR Contacts.DateOfBith = @DateOfBith)
AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)
At that time it will still have to decide which index to use.
Whatever choice it makes at compile time is doomed to be wrong.
The optimizer can't choose the right index, because there is not right index.
It would have to choose the index that is right with a set of given parameters.
Even if it were to delay the choice till the first use of the procedure, then the second use might be done with different parameters and another index should be used.
From what I can see in MS SQL2008, the execution plan is invariably a Clustered Index Scan.
Index Scan meaning that it will read the entire index, from A to Z. This can be terribly slow on a very large table.
If the query is dynamic however then the query becomes a lot simpler.
SELECT TOP 100 * FROM Contacts WHERE
(Reference = @Reference)
AND (DateOfBith = @DateOfBith)
The 'OR' disappear. The execution plan then is a lot better. Typically it will use an Index Seek which is only a few disk access and not a full read.
If you use SQL Server Management Studio you can check that using the 'Include Actual Execution Plan' button.
|
|
|
|
|
Pascal Ganaye wrote: I am trying to stay database agnostic
Then don't use stored procedures; not all databases support them.
(@ContactType IS NULL OR Contacts.ContactType = @ContactType)
How about
Contacts.ContactType = COALESCE(@ContactType,Contacts.ContactType) 
(Not that that is necessarily agnostic either.  )
|
|
|
|
|
What is the best precise method of converting bit(0 or 1) from sql(row array text cell) to boolean value.
example: bool? b = (bool?)(row.cells[0].Text)
Thanks. If possible one line of code please
I only read newbie introductory dummy books.
modified on Tuesday, August 16, 2011 7:25 AM
|
|
|
|
|
SELECT FORUM[^] FROM CODEPROJECT WHERE FORUM = "DATABASE"
Panic, Chaos, Destruction. My work here is done.
Drink. Get drunk. Fall over - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer
Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett
|
|
|
|
|
Your database structure is not normalized 
|
|
|
|
|
1. create table test(name varchar(20),status bit not null)
assume status has value 0 or 1. then fill to a dataset.
Bind a gridview directly to dataset.
Column status shows true or false on gridview which is ok.
To retrieve the status value from gridviewrow.cells[1].text return empty string.
Confirm.
I only read newbie introductory dummy books.
|
|
|
|
|
5fingers wrote: What is the best precise method of converting bit(0 or 1) from sql(row array text cell) to boolean value.
Probably by asking it in the correct forum and by reading the message at the top of the page that says...
Please do not post programming questions here.
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC Link[ ^]
Trolls[ ^]
|
|
|
|
|
1. create table test(name varchar(20),status bit not null)
assume status has value 0 or 1. then fill to a dataset.
Bind a gridview directly to dataset.
Column status shows true or false on gridview which is ok.
To retrieve the status value from gridviewrow.cells[1].text return empty string.
Confirm.
I only read newbie introductory dummy books.
|
|
|
|
|
See here[^]
Every man can tell how many goats or sheep he possesses, but not how many friends.
|
|
|
|
|
See here[^].
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
My middle one, to be exact.
|
|
|
|
|
|
1. create table test(name varchar(20),status bit not null)
assume status has value 0 or 1. then fill to a dataset.
Bind a gridview directly to dataset.
Column status shows true or false on gridview which is ok.
To retrieve the status value from gridviewrow.cells[1].text return empty string.
Confirm.
I only read newbie introductory dummy books.
|
|
|
|
|
You need to spend some time with your debugger to figure out exactly where this is going wrong and why. Check all the possible values in your objects at each stage, in particular check whether gridviewrow.cells[1] actually has a text value that can be returned.
|
|
|
|
|
Wow, you are long enough here on CP to know the rules, but no, you have to misbehave again and again, and then you complain why you got constantly downvoted? 
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
1. create table test(name varchar(20),status bit not null)
assume status has value 0 or 1. then fill to a dataset.
Bind a gridview directly to dataset.
Column status shows true or false on gridview which is ok.
To retrieve the status value from gridviewrow.cells[1].text return empty string.
Confirm.
I only read newbie introductory dummy books.
|
|
|
|
|
iam having ntext column in my database
that store uniqueidentifier value seprated with ,
so i create function to split this ntext value in local table which each row have 1 uniqueidentifer value
but i have error when i try to create local variable of type ntext
The text, ntext, and image data types are invalid for local variables
i need any help to solve my problem
md_refay
|
|
|
|
|
|
Dear all,
Please suggest me to write shortest and effective query in Storeprocedure.
To assign value to @Color, there will be different select queries with different conditions, My query sample will be as the following,
Select @result=count(*) from table where condition1
IF @result = 20
Begin
@Color='Red Color'
End
Else
Begin
select @result=count(*) from table where condition2
IF @result=20
Begin
@Color='Blue Color'
End
Else
Begin
select @result=count(*) from table where condition3
IF @result>0
Begin
@Color='Blue Color'
End
Else
Begin
Select @result=count(*) from table where Condition4
IF @result>0
Begin
@Color='Blue Color'
End
Else
Begin
Select @result=count(*) from where Condition5
IF @result=20
Begin
@Color='Green Color'
End
Else
Begin
Select @result=count(*) from where Condition6
IF @result>0
Begin
@Color='Yellow Color'
End
Else
Begin
@Color='Orange Color'
End
End
End
End
End
End
Thanks and best regards
|
|
|
|
|
You should let us know what database you are using.
Look into CASE if you are using SQL Server
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you for your reply.
So far what I've found CASE are use like below
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)
But, I have no idea how to use in my query.
Could you please give me some samples by using my scenarios?
Thanks and best regards
|
|
|
|
|
Using your second format you basically take your where clauses and place them between WHEN and THEN
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN whereclause1 THEN VacationHours + 40
WHEN whereclause2 THEN VacationHours + 10
WHEN whereclause3 THEN VacationHours + 19
WHEN whereclause4 THEN VacationHours + 11
END
)
The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible. Good luck
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible
This is very true if the where clauses are even a bit complex...
|
|
|
|
|
Absolutely right 
I'm at that situation now
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
