Option FAST

[Puiu]

1.Could anyone tell me if I were to use the OPTION FAST(x) in an SQL query (where x is a number smaller then the total number of rows returned by the query) would it be of any help if i use that query in ASP.NET to fill a dataset ?

For as much as i know a dataset is completly filled with the data and then it is displayed

2. I have a query that sounds like this:

select * from Media where Description like @Value and convert(char(10),ShootingDate,110) =@BeginDate

The problem is that if leave the @Value blank () and @beginDate is a date found in the table there are no results returned.
If I use "or" instead of "and" and I give @Value a value and @Begindate a date it returns all the rows that match the date without looking at the @Value !

The question is: Can I create a query that checks for both @Value and @BeginDate and if one is missing to check only for the one given ? And how ?
10x

 

[Joe Mamma]

Are you setting the parameter type for the @value parameter?


I know in VB6, using ado, if you didnt specify adVarChar as the type, empty strings were pushed accross as nulls and that kept the query from returning matches.

 

[Puiu]

the @Value is varchar(100)
But i havent implemented the proc in VB yet...its in the query analizer that i get no results!

 

[Joe Mamma]

the @Value is varchar(100)
But i havent implemented the proc in VB yet...its in the query analizer that i get no results!

oops. . . didnt look closely at your query.

If you are using the Like operator, and want to select on a wild card, you need to use % in sql server.

Like % returns all (except nulls)
Like A% returns all starting with A
Like %Z returns all ending with Z (can be slow as indecies are not used)
Like %CONTENT% returns all containing the string CONTENT (can also be slow)

Note, depending on your database collation settings, results are typically case-insensitive. That being said. . .

try:

select *
from Media
where Description like @Value+ %
and
convert(char(10),ShootingDate,110) = @BeginDate

but. . . Do you want null descriptions to be treated as empty strings????
if so -

select *
from Media
where IsNull(Description, ) like @Value+ %
and
convert(char(10),ShootingDate,110) = @BeginDate

bear in mind . . . because this uses left side function results as where criteria, it wont utilize any indecies on Description or ShootingDate - you may want to change Description so nulls are not allowed, and the default value is . This may require running this one opf these update queries before changing the schema -

(If the media table doesnt not have many rows and Description is indexed)

update media
set Description =
where Description is null

(If the media table has alot of rows or Description is not indexed. . . drop any indexes on description and recreate them after running this)


update media
set Description = isNull(Description, )


Finally if shootingdate is defined as a datetime, and the above mentioned schema change is made to Description, put the convert around the BeginDate parameter and use this for optimal execution-

select *
from Media
where Description like @Value+ %
and
ShootingDate = cast(@BeginDate as DateTime)

 

[Puiu]

Thnk you for your answer joe mama!
Actualy my query was a little more complicated than the one posted and it a simple matter of parantheses

My bad i didnt ask corectly, but anyway your answer helped me afterwards

I would still have another question:

Is there a way to compare the eficiency of a query (relative to another) in Sql 2000 ? I saw there is an Estimated Execution Plan and a Show Execution Plan...
I was wondering if the Estimated Execution Plan compares the eficiency of 2 or more queries...