Does SQL Server put a shared lock on all tables within a transaction?

Would table1, table2 and table3 in code below, be locked with a shared lock from start of transaction to the end of transaction Or they would only be locked for the duration of their update, or insert statements and not for the entire transaction? Default isolation level is in effect in SQL Server.

begin tran
update table1 set column1 = 100
if @.ERROR = 0
begin
declare @.stat int
set @.stat = (select stat from table2 where employeeid = 10)
insert into table3 (col1, col2) values (@.stat , 325)
if @.@.ERROR = 0
commit tran
else
rollback tran
end
else
roll back tran

The answer to your first question is no. The answer to your second question is no.|||

Here's the long answer:

To keep this long answer from becoming a book, I'm going to classify IS/S locks as "shared", IU/I locks as "Update" and IX/X locks as "Exclusive". The I-versions are basically the same, but has a few differences, and usually used prior to obtaining the non-I version to eliminate lock starvation.

I'm going to assume table1 only has 1 row in it for this.

Update table1 ... will initially request an update lock as it scans the table for the rows to update, once it's located the correct rows, it will acquire an exclusive lock on those pages and rows then release the update locks.

set @.stat=(SELECT .. will request shared locks on the table, retrieve it's results, then release it's shared locks.

insert into table3 will acquire an exlusive lock on either an existing page, or a new page if either none are available, or the available ones are already exclusively locked. (There is an additional exclusive lock placed on the particular row as well).

The exclusive locks will be held until the transaction is either commited or rolled back. This stored procedure will not block any other session from either reading table2 at any time, or inserting a new record into table3. It will however block any other session from updating it's only row until the transaction is complete.

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 'Group By' affect the query speed?

i have a table such sa below:

Name1, Name2, Name3, Nam4, C1, C2,.., C100

and in this table, i have found index for Name1-Nam4,

i don't why sql below is very slow?

select
Name1, sum(C1), ...., Sum(C100)
from
(
select
Name1, Name2, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, Name4, C1, ...., C100
from
My_Table
group by Name1, Name2, Name3, Name4
) as T
group by Name1, Name2, Nam3
) as T
group by Name1, Name2
) as T
group by Name1

Does 'Group By' affect the speed of query?

Yes... It depends with your number of data...

I found your query is strange...

Why not, can you try the following query..

select
Name1, sum(C1), ...., Sum(C100)
from
My_Table
group by Name1

Bcs.. finally you are going to get only the Name1 data...

If you need kind of Rolling up data... use ROLLUP instead of multiple Subqueries...

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 ***

Docked Headers

Can I set my page header to stay visible on the screen while scrolling thru
the detail lines below it? Thanks.Exporting to Excel provides this facility, but not in other rendering formats.
So how you are planning to implement ?
Amarnath
"Larry" wrote:
> Can I set my page header to stay visible on the screen while scrolling thru
> the detail lines below it? Thanks.|||Hello Amarnath,
Thanks for the reply. I didn't think there was a way to keep the headers on
the screen within Reporting Services. I will tell my users they can do it in
Excel. Thanks.
Larry
"Amarnath" wrote:
> Exporting to Excel provides this facility, but not in other rendering formats.
> So how you are planning to implement ?
> Amarnath
> "Larry" wrote:
> > Can I set my page header to stay visible on the screen while scrolling thru
> > the detail lines below it? Thanks.|||You can also do this with the HTML and Winforms renderers in RS 2005. Set
the Table property "Header should remain visible when scrolling".
Best,
-Chris
SQL Server Reporting Services
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:2A1AB41D-D78E-4EA4-B6BF-89236035E530@.microsoft.com...
> Hello Amarnath,
> Thanks for the reply. I didn't think there was a way to keep the headers
> on
> the screen within Reporting Services. I will tell my users they can do it
> in
> Excel. Thanks.
> Larry
>
> "Amarnath" wrote:
>> Exporting to Excel provides this facility, but not in other rendering
>> formats.
>> So how you are planning to implement ?
>> Amarnath
>> "Larry" wrote:
>> > Can I set my page header to stay visible on the screen while scrolling
>> > thru
>> > the detail lines below it? Thanks.