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

Edited July 15, 2005 by Puiu

[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 haven't implemented the proc in VB yet...it's in the query analizer that i get no results!

[Joe Mamma]

the @Value is varchar(100)

But i haven't implemented the proc in VB yet...it's 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 won't 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 didn't 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...