Examples for these.

Have come across the following guidelines in a few sites:
-> Avoid using data conversion functions in the WHERE clause of a query.
-> Use of a derived query in place of a cursor.
Although the reasoning is allright, can someone please provide examples
that can be used to illustrate these?
Thank you
sqlcatz
sql
SELECT * FROM Orders WHERE OrderDate <'20040101' AND OrderDate>='19970704'
SELECT * FROM
(
SELECT OrderID,CONVERT(CHAR(8),OrderDate,112) AS OrderDate
FROM Orders
) AS Deriv
WHERE OrderDate <'20040101' AND OrderDate>='19970704'
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:01B15B2B-322A-4EB4-9429-F58EE5B797AE@.microsoft.com...
> Have come across the following guidelines in a few sites:
> -> Avoid using data conversion functions in the WHERE clause of a query.
> -> Use of a derived query in place of a cursor.
> Although the reasoning is allright, can someone please provide examples
> that can be used to illustrate these?
> Thank you
> sqlcatz
>
|||Uri,
Thank you for the quick response!
What I was looking out for was something along these lines:
Write a Query that uses a data conversion function in the where clause - it
gives a particluar resultset.
How can I get the same resultset without using a data conversion function in
the where clause?
I want to illustrate the fact that you can achieve the same result without
having a data conversion function in the where clause.
Same holds true for the Cursor - derived table...
Cheers!
|||SQL
>How can I get the same resultset >without using a data conversion >function
in
>the where clause?
SELECT * FROM
(
SELECT OrderID,CONVERT(CHAR(8),OrderDate,112) AS OrderDate
FROM Orders
) AS Deriv
WHERE OrderDate <'20040101' AND OrderDate>='19970704'
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:AF1ED9E1-6C47-4789-B589-B2D9460DC80E@.microsoft.com...
> Uri,
> Thank you for the quick response!
> What I was looking out for was something along these lines:
> Write a Query that uses a data conversion function in the where clause -
it
> gives a particluar resultset.
> How can I get the same resultset without using a data conversion function
in
> the where clause?
> I want to illustrate the fact that you can achieve the same result without
> having a data conversion function in the where clause.
> Same holds true for the Cursor - derived table...
> Cheers!
>
|||> Write a Query that uses a data conversion function in the where clause -
it
> gives a particluar resultset.
> How can I get the same resultset without using a data conversion function
in
> the where clause?
What "same resultset"?
http://www.aspfaq.com/
(Reverse address to reply.)
|||One example you can find in my article on datetime: http://www.karaszi.com/SQLServer/info_datetime.asp
Search for below query:
SELECT c1, dt FROM #dts WHERE CONVERT(char(8), dt, 112) = '20040305'
And you'll find reasoning behind it and finally my suggested query, which doesn't do a CONVERT on the column
side.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:AF1ED9E1-6C47-4789-B589-B2D9460DC80E@.microsoft.com...
> Uri,
> Thank you for the quick response!
> What I was looking out for was something along these lines:
> Write a Query that uses a data conversion function in the where clause - it
> gives a particluar resultset.
> How can I get the same resultset without using a data conversion function in
> the where clause?
> I want to illustrate the fact that you can achieve the same result without
> having a data conversion function in the where clause.
> Same holds true for the Cursor - derived table...
> Cheers!
>