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 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 dynamic SQL allow table variables?

Hello!
Please see below the test code that is using dynamic sql and table
variable. It is not working. I am not sure if the dynamic SQL allows
using table variables?
Thanks!
declare @.Tblvar TABLE(a int, b varchar(10))
declare @.s varchar(200)
create table #Dept(a int, b varchar(10))
insert into #Dept values(1, 'abcd')
insert into #Dept values(2, 'xyz')
set @.s = 'insert into ' + @.Tblvar
' select * from #Dept'
exec (@.s)
select * from @.Tblvar
*** Sent via Developersdex http://www.examnotes.net ***The problem is that you didn't create the #Dept table in the same scope.
Inside the EXEC() there is no #Dept table.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23A2JudbLGHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello!
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
>
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||As Aaron says..this is a scope problem...table variables must be used
in the same batch...
exec('declare @.Tblvar TABLE(a int, b varchar(10))
declare @.s varchar(200)
create table #Dept(a int, b varchar(10))
insert into #Dept values(1, ''abcd'')
insert into #Dept values(2, ''xyz'')
insert into @.Tblvar
select * from #Dept
select * from @.Tblvar')
MJKulangara
http://sqladventures.blogspot.com|||I see two problems here. First, you are trying to build your query string,
@.s, by concatenating a string with a table, which won't work. You could
instead to
set @.s = 'insert into @.Tblvar select * from #Dept'
but then you encounter another problem: neither the variable @.Tblvar or the
temporary table #Dept is defined in the scope that the query is executed
under with EXEC.
"Test Test" wrote:

> Hello!
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
>
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes it does, but due to the fact that exec is opening another session
)verything you execute) it has to be put within the context of the (in
your case) INSERT INTO statement. Otherwise you can use a global
temporary data to share data with if you want to.
HTH, Jens Suessmeyer..|||Test Test (farooqhs_2000@.yahoo.com) writes:
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
The dynamic SQL constitutes a scope on its own, and variables are
only visible in the direct scope that created it. This is in difference
to temp tables which are visible for inner scopes. (No less than two
posters gave incorrect information on this.)
A scope is a stored procedure, function, trigger - or a batch of dynamic
SQL.
A good demonstration of this is:
CREATE PROCEDURE nestlevel_sp AS
SELECT @.@.nestlevel
EXEC('SELECT @.@.nestlevel')
EXEC sp_executesql N'SELECT @.@.nestlevel'
go
EXEC nestlevel_sp
This prints 1, 2 3 in that order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to everyone!!!
*** Sent via Developersdex http://www.examnotes.net ***

Does Debug > Build in Script Task actually do anything?

I'm looking for some way to verify the syntax, references, variables, etc. in my Script Tasks without having to run the package or the task.

There's a Build command in the Debug menu, but it doesn't seem to do anything -- certainly, not like the "Build Solution" in a Visual Studio project.

Am I missing something? Does Debug > Build in the VSA editor actually do anything?

Thanks!

- Mike

mike.groh wrote:

I'm looking for some way to verify the syntax, references, variables, etc. in my Script Tasks without having to run the package or the task.

There's a Build command in the Debug menu, but it doesn't seem to do anything -- certainly, not like the "Build Solution" in a Visual Studio project.

Am I missing something? Does Debug > Build in the VSA editor actually do anything?

Thanks!

- Mike

Mike,

I don't think it does for SSIS, no. This is a legacy from using someone else's IDE in order to facilitate scripting.

-Jamie

Does a checkpoint file record a package's state

The state of a package at any one point in time is determined by the values in all of its user variables as these are the only properties in the package that can be changed and persisted throughout the runtime of the package.

Is the package's state stored in a checkpoint file in the event that the package fails? In other words are the user variable values stored in the checkpoint file?

Thanks

Jamie

Can task A update an enivornment variable i.e. persist the state outside the package.

Out of interest viw does package B need to update a variable in package A.

|||

SimonSa wrote:

Can task A update an enivornment variable i.e. persist the state outside the package.

Not in this scenario!

SimonSa wrote:

Out of interest viw does package B need to update a variable in package A.

[I presume that word was "why" :)]

That's a question that needs to be answered at length - preferably over a few beers!

-Jamie

|||

Jamie,

Can you ellaborate on what this variable (PkgA) represents (I.E., some sort of count, threshold, clock/time, etc...)?

We've had scenerios where a variable needed to be updated and couldn't be done using a GLOBAL_ENV_VAR or some other global, it needed to be updated per client(job), and client(job) specific function. So what we did is created and modified text-based files to update the variables values.

Say for instance, you had to update variableA (which was a boolean true/false), you could create/modify a file specifc to that instance/job run, and change the "variableA=" to true of false. Then you could always refer back to that specific file to lookup the values - like creating your own checkpoint files.

|||

Jason,

The particular one one in question was a variable called PackageStack which maintains a comma seperated list of packages that have been called, in the order that they have been called.

Each package has an OnPreExecute event that "pushes" the name of the package into the PackageStack variable and an OnPostExecute event that "pops" it off.

-Jamie

|||

Similarly, you could log this using a log provider, however, you will run into the same problem I am facing - with that logging bug and only logging the immediately inherited pkg!!!

Maybe, whom ever answers this post can solve both our problems...

|||

Yes, the values of the variables are stored in the checkpoint file.

K

|||

I disagree, I believe this is only true for simple variables. I have stored a recordset in a variable and this wasn't stored in the checkpoint file.

Either object types aren't supported or there is something about what is saved.

What happens if a variable is changed in a loop. What is stored in the checkpoint, the variable value at the start of the loop or the value at the point of failure.

|||

Correct, object types are not stored.

ForEach loops start at the beginning of the collection.

For loops continue where they left off.

They all key off of the variables.

K

|||

KirkHaselden wrote:

Correct, object types are not stored.

Why not?

Will this change?

-Jamie

|||

I've just come across a differrent "problem" with this.

I have 2 packages pkgA and pkgB. pkgA calls pkgB

pkgB has 2 tasks taskA and taskB. taskA is a script task that updates a variable (varA) in pkgA. taskB is a data-flow that is failing.

pkgB uses checkpoints

Now, I can correct the error that causes taskB to fail but the problem I have is that when I rerun it using the checkpoint file taskA will not execute and therefore varA will have the wrong value in it.

There isn't a way around this problem currently but is there a way that this could be catered for in the future using checkpoint files? i.e. The checkpoint file for pkgB would have to store the value of varA even though varA is in pkgA.

-Jamie