does SQL Server take advantage of bind variables

Using prepared statements like Oracle does? This way in a high transaction
system you do not have to recompile queries every time?
Ryan wrote:

> Using prepared statements like Oracle does? This way in a high transaction
> system you do not have to recompile queries every time?

Yes it does have this capability.
Joe Weinstein at BEA|||"Ryan" <rgaffuri@.cox.net> wrote in message news:<ZVlPb.5755$_H5.281@.lakeread06>...
> Using prepared statements like Oracle does? This way in a high transaction
> system you do not have to recompile queries every time?

In general, query plans are cached (unless they're very simple), but
may be aged out of the cache if they're not used. Stored procedures
are generally the most efficient way to code, although they may be
recompiled in some situations. Profiler can show cache hits, misses
and recompilations for stored procs.

Simon|||Hi Ryan

Yes every database i know of including sql server will make use of
bind varaiables..bind varaiables are not the exclusive doamin of
oracle.

regards
Hrishy

"Ryan" <rgaffuri@.cox.net> wrote in message news:<ZVlPb.5755$_H5.281@.lakeread06>...
> Using prepared statements like Oracle does? This way in a high transaction
> system you do not have to recompile queries every time?

Does SQL permit use of a column alias directly in the CASE statement?

Our Case statements can get very long and complicated and I would like to maintain just a single "Case" and than depending on the type of set I'm processing,
use this generic "Case" statement to properly decode the value. My SQL code is listed below.

Select
'PCAR' as [vtyp]
,Case
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When [vtyp]='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as 'Engine_Decode'
From veh_owner
Where substring(veh_vin,10,1) = '3' --Selects on cars

Union All

Select
'PTRK' as [vtyp]
,Case
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When [vtyp]='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as 'Engine_Decode'
From veh_owner
Where substring(veh_vin,10,1) = '4' --Selects only Trucks

I would like to deploy the SQL code described above but get the following errors:

Msg 207, Level 16, State 1, Line 18 Invalid column name 'vtyp'

Any ideas, comments or help with this issue will be greatly appreciated.

sfmd:

SQL Server does not have this kind of alias. I am not sure that I understand what you are looking for, is this heading in the right direction?:

select veh_vin,
substring ('1 2 PCARPTRK', 4 * convert(integer, substring (veh_vin,10,1)) - 3, 4) as [vtyp],
case when substring (veh_vin,10,1) = '3' and substring (veh_vin,8,1) = 'A' then 'Cyl = V-6 Fuel = Gasoline'
when substring (veh_vin,10,1) = '4' and substring (veh_vin,8,1) = 'A' then 'Cyl = V-8 Fuel = Diesel'
when substring (veh_vin,10,1) = '3' and substring (veh_vin,8,1) = 'B' then 'Cyl = V-6 Fuel = CNG'
else '?'
end as Engine_Decode
from veh_owner
where substring (veh_vin,10,1) between '3' and '4'

-- - Sample Output: -

-- veh_vin vtyp Engine_Decode
-- -- -
-- 1233567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1243567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1253567B1373 PCAR Cyl = V-6 Fuel = CNG
-- 1263567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1334567A1473 PTRK Cyl = V-8 Fuel = Diesel
-- 1344467A1473 PTRK Cyl = V-8 Fuel = Diesel
-- 2344467B1473 PTRK ?

Dave

|||

One thing to consider is persisting these two characters of the VIN. I realize that a VIN is what it is and you cannot change these "smart coded" columns; nonetheless, these portions of the VIN have a specific meaning and it might be a good idea to give these meanings an existence of themselves rather than only existing as a "sub-column".

Dave

|||

>>One thing to consider is persisting these two characters of the VIN. I realize that a VIN is what it is and you cannot change these "smart coded" columns; nonetheless, these portions of the VIN have a specific meaning and it might be a good idea to give these meanings an existence of themselves rather than only existing as a "sub-column".<<

Absolutely. Anytime you find yourself querying on a substring, you probably ought to reconsider what you are doing :)

|||

Perhaps I was not clear enough with my example

My point with this thread is to find a way to use a generic or multi-purpose "Case" Statement to process similar "sets" of rows. The selection of each processing set, in this case either all cars or all trucks, but never a mixed group of vehicles can be handled a thousand different ways by configuring a "Where" clause.

However the vehicle group is selected, I would like to use a generic "Case" for decoding certain values. I will always know the composition of the set (vtyp=value) again either cars or trucks and need to convey/pass this informational profile to the generic "Case" statement.

Notice that a car "A" in VIN position 10 decodes differently than a truck "A" in VIN position 10, that's the crux of the problem. Sure it's easy to use and maintain 2 different "Case" Statements(1 for car and 1 for trucks), but that's not what I initially would prefer to do. It's sure to get out of hand very quickly.

I hope this helps clarify my issue

|||In answer to your question "no" you cannot do what you want without making it dynamic SQL.|||

How about having UDF on this..

Create Function dbo.VehData(@.Vtype as varchar(10), @.VinId Varchar(10))
returns Table As
Return (Select
@.Vtype as [vtyp]
,Case
When @.Vtype='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When @.Vtype='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When @.Vtype='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as Engine_Decode
From veh_owner
Where substring(veh_vin,10,1) = @.VinId )

Go

Select * From VehData('PCAR',3)
Union All
Select * From VehData('PTRK',4)
Union ALL
..etc

Does LIKE operator have major performance issue with variables?

Hi all,
Below are two similar SQL statements that give the same results:
1. SELECT * FROM InvoiceDtl WHERE IvoNum LIKE ('Ivo-0510-00001')
2. DECLARE @.IvoNum AS NVARCHAR (20)
SET @.IvoNum = 'Ivo-0510-00001'
SELECT * FROM InvoiceDtl WHERE IvoNum LIKE (@.IvoNum)
InvoiceDtl is a big table with 2.3++ million rows. IvoNum is of type
NVARCHAR (20) and has a non-clustered index.
I run both statements seperately in Query Analyzer. Statement 1 takes 1-2
seconds. But statement 2 takes 3-4 minutes (and makes my harddisk run mad)!
Cld anyone pls kindly advise why that is happening? TQ.SQL Server processes batches of SQL statements in 3 steps:
1) Parsing: check for invalid code
2) Compilation: generate an execution plan, which tables/indexes to use, and
the order to access them in etc
3) Execution: execute the execution plan generated in step 2
Now for the first statement SQL knows the value of IvoNum it has to look for
as early as step 2, because it is a literal. The Query optimizer can look up
statistics on the indexes and estimate how often the value 'Ivo-0510-00001'
appears in the column IvoNum, and generate the fastest execution plan to be
executed by step 3.
For the second statement, SQL Server does NOT know the value of IvoNum it
has to look as early as step 2. @.IvoNum is a variable, at the assignment of
a value to this variable only happens during execution in step 3. If T-SQL
had constants, you could declare @.IvoNum as a constant, and the value would
be available in step 2, but T-SQL only has variables not constants. So the
Query Optimizer does not know in step 2 to as to what the value of @.IvoNum
will be during execution. So it uses an estimate for the number of rows that
might match, and IIRC, that estimate is 30%. Remember that the value of
@.IvoNum is unknown during step 2, so it might be 'Ivo-0510-00001' , 'Ivo%'
'%0510-00001' or even '%' in step 3. This estimate leads to a very different
execution plan, which in cases will include scanning all 2.3 million rows in
the table.
Jacco Schalkwijk
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:43932C3C-D0D8-42EE-AE09-7388DBA8D6CE@.microsoft.com...
> Hi all,
> Below are two similar SQL statements that give the same results:
> 1. SELECT * FROM InvoiceDtl WHERE IvoNum LIKE ('Ivo-0510-00001')
> 2. DECLARE @.IvoNum AS NVARCHAR (20)
> SET @.IvoNum = 'Ivo-0510-00001'
> SELECT * FROM InvoiceDtl WHERE IvoNum LIKE (@.IvoNum)
> InvoiceDtl is a big table with 2.3++ million rows. IvoNum is of type
> NVARCHAR (20) and has a non-clustered index.
> I run both statements seperately in Query Analyzer. Statement 1 takes 1-2
> seconds. But statement 2 takes 3-4 minutes (and makes my harddisk run
> mad)!
> Cld anyone pls kindly advise why that is happening? TQ.|||HardKhor,
I got some questions for you here...
1) Why do you have nvarchar as datatype here? wouldnt varchar or char be
better?
2) Why 20 chars at most? If 'Ivo-0510-00001' is the longest, why not
char(14) ?
3) Why use LIKE if 'Ivo-0510-00001' is an exact match? i.e ... WHERE
Something='Ivo-0510-00001'
/Lasse
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:43932C3C-D0D8-42EE-AE09-7388DBA8D6CE@.microsoft.com...
> Hi all,
> Below are two similar SQL statements that give the same results:
> 1. SELECT * FROM InvoiceDtl WHERE IvoNum LIKE ('Ivo-0510-00001')
> 2. DECLARE @.IvoNum AS NVARCHAR (20)
> SET @.IvoNum = 'Ivo-0510-00001'
> SELECT * FROM InvoiceDtl WHERE IvoNum LIKE (@.IvoNum)
> InvoiceDtl is a big table with 2.3++ million rows. IvoNum is of type
> NVARCHAR (20) and has a non-clustered index.
> I run both statements seperately in Query Analyzer. Statement 1 takes 1-2
> seconds. But statement 2 takes 3-4 minutes (and makes my harddisk run
mad)!
> Cld anyone pls kindly advise why that is happening? TQ.

Does DBREINDEX run longer on tables with lower scan density

I am looking into a way to generate and execute DBCC DBREINDEX statements
during scheduled down time. For a particular table - I need to be able to
estimate how long it will take to REINDEX a particular table.
Let say for example I have a table that had 25000 extents and
1) the ScanDensity was 50.
2) the ScanDensity was 15.
Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
ScanDensity of 15 (as opposed to 50)?
Thanks in advance
Tom-- TJTODD wrote: --
> Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
> ScanDensity of 15 (as opposed to 50)?
--
Hi Tom,
I have not found any official documentation on this area. The only way to find out is to do your own empirical benchmarks.
Hope this helps,
-Eric Cárdenas
SQL Server support|||Tom,
The only thing I can think of is that it will obviously take longer time for SQL Server to scan the
source data if the scan density is low. I'd think that in the whole, this would count as marginal,
though.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:OzZlndSuDHA.2304@.tk2msftngp13.phx.gbl...
> I am looking into a way to generate and execute DBCC DBREINDEX statements
> during scheduled down time. For a particular table - I need to be able to
> estimate how long it will take to REINDEX a particular table.
> Let say for example I have a table that had 25000 extents and
> 1) the ScanDensity was 50.
> 2) the ScanDensity was 15.
> Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
> ScanDensity of 15 (as opposed to 50)?
> Thanks in advance
> Tom
>

Does a transaction automatically rollback on error?

When I write code for a multiple statements transaction do I need to check'if @.@.ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @.ERROR > 0 ?

If you start a transaction (with BEGIN TRANSACTION) you must end it with a COMMIT or ROLLBACK. So, yes, you should check the @.@.ERROR value after each pertinent statement and explicitly ROLLBACK the transaction if there is an error. I am not sure if there were any changes in this area for SQL Server 2005.|||In SQL 2005, you could do:

BEGIN TRY
.....
COMMIT
END TRY
BEGIN CATCH
.....
ROLLBACK
END CATCH

The good thing is you could put multiple SQL statementsin the TRY block. But there are some limitations too. check out books on line.