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 server have something like dual

In oracle you can run queries against some built in virtual table called dual. Like below:

SELECT SEQ.NEXTVAL FROM DUAL

does sql server have anything similar?Do you tried 'Select SEQ.NEXTVAL' That should work!|||SQL Server does not have such virtual table for that exact purpose like oracle has, for example in Oracle you can run:

select sysdate from dual

Same would be in SQL Server:

select getdate()

e.g any virtual table is not needed, statement just consists of SELECT plus then a function or T-SQL specific stuff. With identities (same as sequences in Oracle) there are SCOPE_IDENTITY(), @.@.IDENTITY AND IDENT_CURRENT, for example to query current identity value:

SELECT IDENT_CURRENT('TABLE_NAME')

Exact difference with all three:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@.@.IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

So to reply completely to your question, there are functions etc which do not need any virtual table when they are general (table-independant, querying the date is good example) and then there are stuff which need the table to be specified in the query like previous identity query.sql

Does SQL Server creates some temporary table during query execution?

Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
PushkarYes, it does. If you show the query plan, you'll see icons such as Table
Spool/Eager Spool. That implies that a temporary table is being created by
the optimizer behind the scenes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
Hi,
I want to know when does SQL Server use temporary tables for query
processing?
Does it use for all queries or for some complex queries? Or doesn't use at
all?
Thanks
Pushkar|||Pushkar
I think SQL Server decides internally to perform some operations in tempdb
database. For sure I know that if your query has ORDER BY ,GROUP BY clauses
and it has to operate in large amount of data , so SQL Server will create a
work tables to perfom that.
I'd suggest to visit at Aaron's web site to get more explanation
www.aspfaq.com
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to know when does SQL Server use temporary tables for query
> processing?
> Does it use for all queries or for some complex queries? Or doesn't use at
> all?
> Thanks
> Pushkar
>|||Thanks !!!
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urnhSSiNGHA.2916@.tk2msftngp13.phx.gbl...
> Pushkar
> I think SQL Server decides internally to perform some operations in
> tempdb database. For sure I know that if your query has ORDER BY ,GROUP BY
> clauses and it has to operate in large amount of data , so SQL Server will
> create a work tables to perfom that.
> I'd suggest to visit at Aaron's web site to get more explanation
> www.aspfaq.com
>
>
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:ekuGHMiNGHA.2320@.TK2MSFTNGP11.phx.gbl...
>

Does it locks database/tables when Only Select query comes in SQLTransaction

Hi,

I want to make SQLTransaction as global and use it checking the State.
But then where there are Only Select queries are going to fire, it will open transaction.

So, Does it locks database/tables when Only Select query comes in SQLTransaction.

If you have another successful way of doing this, Please suggest.

Thanking you.

tatsA select typically only takes shared lock and releases the lock as soon as the select is done. If you want to an exclusive lock, you will have to use "tablockx" hint. Please see book online for detail.|||Thank you for your suggestion.|||

Just to top it....

SELECT COL1 FROM TBL2 WITH(NOLOCK) WHERE COL2 = @.VAL

|||You should avoid this technique or be sure to understand what is going on behind the scenes. Many people beginning with TSQL somewhere read that (NOLOCK) will not produce any problems, because one can read without careing about any locking, on the one side this is true, but you can′t be sure to have the most recent data, because you will do dirty ready on the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

What are you trying to accomplish? Every statement locks something, at least the schema so you can't drop a table while the user is executing a statement :)

Also, EVERY statement is in a transaction already, the difference between starting one using the client and just the implicit one that starts for every statement is that you might never reach the code to close it if the user gets bored and decides they want to go to lunch.

What locks are taken and how long locks are held is determined by the isolation level. The default is read committed (though your client defautl CAN BE DIFFERENT!) Note too that all of the locks I will talk about are shared locks for data that you read NOT updated data. Updated data requires locks to be held until the end of a transaction.

READ UNCOMMITTED - No locks issued or obeyed other than schema locks

READ COMMITTED - Locks are (generally) taken only as long as needed to protect the lowest level thing you are working with (like a row in a table.) Generally speaking it "crabs" through the objects grab a lock, release a lock. Another user can modify data you have already read

REPEATABLE READ - LIke Read Committed, only it doesn't release lock it takes. Prevents another user from modifying data you have already read, doesn't prevent user from creating new rows (referred to as Phantoms).

SERIALIZABLE - Like Repeatable Read, only it also places a lock on Ranges of data, so if you say WHERE between 0 and 100, a range lock is places on 0 and 100 to say no other user can create data in that range either.

SNAPSHOT (2005) - Readers look at only committed data, and if other users have locked portions of the data, they will only see the database as it was when they started their command. Really cool, but uses more tempdb than would otherwise.

So the only real concern about executing your select in a transaction is that you will run the risk of a user failure locking other users until cleanup occurs and the connection is noticed to be stale and not in use.

I generally would suggest that you execute your transactions in a single batch and put the BEGIN TRAN and COMMIT TRAN in the same batch if possible and using TRY...CATCH blocks around statements if you have 2005. Then you lower the risk of hanging a transaction. The connection might still exist, but the commit/rollback will certainly execute.

Does Indexes actually make queries fast?

I had a query most of the time got "timed out". Add some indexes things did
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
raj
Raj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.

> raj
>
|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>

Does Indexes actually make queries fast?

I had a query most of the time got "timed out". Add some indexes things did
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
rajRaj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.

> raj
>|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things di
d
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>

Does Indexes actually make queries fast?

I had a query most of the time got "timed out". Add some indexes things did
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
rajRaj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
--
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.
> raj
>|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>

Does High Duration and Low CPU = IO problems?

I have several queries that have a high duration (3,000 ms) and much lower
CPU values (300 ms). Is this ALWAYS an indicator of IO performance problems,
or could it be indicating other factors as well?>> On 11/27/2006 at 9:58 AM, in message
<ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com>,
Dan<Dan@.discussions.microsoft.com> wrote:
> I have several queries that have a high duration (3,000 ms) and much
> lower
> CPU values (300 ms). Is this ALWAYS an indicator of IO performance
> problems,
> or could it be indicating other factors as well?
It could be other factors. For instance, a machine that is swapping or
has too little memory would do this too.|||What would be the next steps in diagnosis?
"Joel Maslak" wrote:
> >> On 11/27/2006 at 9:58 AM, in message
> <ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com>,
> Dan<Dan@.discussions.microsoft.com> wrote:
> > I have several queries that have a high duration (3,000 ms) and much
> > lower
> > CPU values (300 ms). Is this ALWAYS an indicator of IO performance
> > problems,
> > or could it be indicating other factors as well?
>
> It could be other factors. For instance, a machine that is swapping or
> has too little memory would do this too.
>|||It is possible that the query is being blocked by another process. Also,
that query could be used by an app that does much work between retrieving
each row. The duration includes the time it takes to retrieve the last row.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com...
I have several queries that have a high duration (3,000 ms) and much lower
CPU values (300 ms). Is this ALWAYS an indicator of IO performance problems,
or could it be indicating other factors as well?|||In addition to Tom's suggestions, check out the execution plans and see if you can better them
(re-writing queries, adding indexes and all that jazz).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:D8D6F9E9-7C45-4778-A5DF-B9E911D55768@.microsoft.com...
> What would be the next steps in diagnosis?
> "Joel Maslak" wrote:
>> >> On 11/27/2006 at 9:58 AM, in message
>> <ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com>,
>> Dan<Dan@.discussions.microsoft.com> wrote:
>> > I have several queries that have a high duration (3,000 ms) and much
>> > lower
>> > CPU values (300 ms). Is this ALWAYS an indicator of IO performance
>> > problems,
>> > or could it be indicating other factors as well?
>>
>> It could be other factors. For instance, a machine that is swapping or
>> has too little memory would do this too.

Does High Duration and Low CPU = IO problems?

I have several queries that have a high duration (3,000 ms) and much lower
CPU values (300 ms). Is this ALWAYS an indicator of IO performance problems,
or could it be indicating other factors as well?
>>> On 11/27/2006 at 9:58 AM, in message
<ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com>,
Dan<Dan@.discussions.microsoft.com> wrote:
> I have several queries that have a high duration (3,000 ms) and much
> lower
> CPU values (300 ms). Is this ALWAYS an indicator of IO performance
> problems,
> or could it be indicating other factors as well?
It could be other factors. For instance, a machine that is swapping or
has too little memory would do this too.
|||What would be the next steps in diagnosis?
"Joel Maslak" wrote:

> <ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com>,
> Dan<Dan@.discussions.microsoft.com> wrote:
>
> It could be other factors. For instance, a machine that is swapping or
> has too little memory would do this too.
>
|||It is possible that the query is being blocked by another process. Also,
that query could be used by an app that does much work between retrieving
each row. The duration includes the time it takes to retrieve the last row.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com...
I have several queries that have a high duration (3,000 ms) and much lower
CPU values (300 ms). Is this ALWAYS an indicator of IO performance problems,
or could it be indicating other factors as well?

Does High Duration and Low CPU = IO problems?

I have several queries that have a high duration (3,000 ms) and much lower
CPU values (300 ms). Is this ALWAYS an indicator of IO performance problems,
or could it be indicating other factors as well?>>> On 11/27/2006 at 9:58 AM, in message
<ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com>,
Dan<Dan@.discussions.microsoft.com> wrote:
> I have several queries that have a high duration (3,000 ms) and much
> lower
> CPU values (300 ms). Is this ALWAYS an indicator of IO performance
> problems,
> or could it be indicating other factors as well?
It could be other factors. For instance, a machine that is swapping or
has too little memory would do this too.|||What would be the next steps in diagnosis?
"Joel Maslak" wrote:

> <ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com>,
> Dan<Dan@.discussions.microsoft.com> wrote:
>
> It could be other factors. For instance, a machine that is swapping or
> has too little memory would do this too.
>|||It is possible that the query is being blocked by another process. Also,
that query could be used by an app that does much work between retrieving
each row. The duration includes the time it takes to retrieve the last row.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:ABFCA63C-E4D6-44B2-AECD-B8435E5F12E2@.microsoft.com...
I have several queries that have a high duration (3,000 ms) and much lower
CPU values (300 ms). Is this ALWAYS an indicator of IO performance problems,
or could it be indicating other factors as well?|||In addition to Tom's suggestions, check out the execution plans and see if y
ou can better them
(re-writing queries, adding indexes and all that jazz).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:D8D6F9E9-7C45-4778-A5DF-B9E911D55768@.microsoft.com...[vbcol=seagreen]
> What would be the next steps in diagnosis?
> "Joel Maslak" wrote:
>

Does DTC needed to do linked server queries

I have a server B registered as a linked server on Server A and have DTC off
on both servers.
I could still run a query from Server A as
select * from serverB.db.dbo.tablename
So what is DTC used for then ?Hassan,
A select does not involve a transaction between the two servers so DTC is
not required. If you were to modify something you would need it.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23kZqbgzbDHA.616@.TK2MSFTNGP11.phx.gbl...
> I have a server B registered as a linked server on Server A and have DTC
off
> on both servers.
> I could still run a query from Server A as
> select * from serverB.db.dbo.tablename
> So what is DTC used for then ?
>
>|||Try doing something that involves both servers. DTC is for distributed
transactions. That means where the changes are distribute across both
servers but must be contained in one transaction. Take a look at MS DTC in
BooksOnLine for lots of details.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uIkA1wzbDHA.1128@.tk2msftngp13.phx.gbl...
> Well i did run an update such as
> update serverB.db.dbo.tablename
> set col2 ='XYZ'
> where col1='ABC'
> It ran successfully and had the DTCs off on both sides.Can you give me an
> example of what i need to run so that I need to have DTC running ?
> I am using SQL 2000 btw
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1NMRozbDHA.3444@.tk2msftngp13.phx.gbl...
> > Hassan,
> >
> > A select does not involve a transaction between the two servers so DTC
is
> > not required. If you were to modify something you would need it.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23kZqbgzbDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > I have a server B registered as a linked server on Server A and have
DTC
> > off
> > > on both servers.
> > > I could still run a query from Server A as
> > > select * from serverB.db.dbo.tablename
> > >
> > > So what is DTC used for then ?
> > >
> > >
> > >
> >
> >
>

Does Database Exist?

How can I run a query to see if a database exists?
I see the queries like using the sysobjects and xtype to query
different table names and stored procs...but can I use something
similar to see if a database exists?
Something along the lines of: if exists(Select * from sysobjects where
xtype = 'database' Where database name = 'Client1DB')?IF DB_ID('foo') IS NULL
PRINT 'does not exist';
ELSE
PRINT 'exists';
"INeedADip" <ineedadip@.gmail.com> wrote in message
news:1151520416.422554.20030@.j72g2000cwa.googlegroups.com...
> How can I run a query to see if a database exists?
> I see the queries like using the sysobjects and xtype to query
> different table names and stored procs...but can I use something
> similar to see if a database exists?
> Something along the lines of: if exists(Select * from sysobjects where
> xtype = 'database' Where database name = 'Client1DB')?
>|||Thanks.

Does .NET support mySQL connectivity?

If so, what category does this fall under? (ODBC, etc). I need to be able to send queries and updates through C++ .NET 2.0.You can connect to MySQL through ODBC, but there seems to be a more native driver for it, too: http://dev.mysql.com/doc/refman/5.0/en/connector-net.html

does "with (nolock)" absolutely guarantee no locks are taken?

I need to run a few short-running queries against a production system. I need to be absolutely certain that SS doesn't take out any locks on the table as a result.

Does "with (nolock)" absolutely guarantee this? I've read BOL on the topic and I understand isolation level read-uncommitted. But I want to validate that there's not any undocumented behavior in SS that might violate the documentation (which clearly states that no shared locks are issued).

Thanks!

You're talking two different things.

with (nolock) is a HINT. That means there is a possibility that SS will override it.

SET TRANSACTION ISOLATION LEVEL is a command. AFAIK, SS won't override this.

|||SQL Server honors the read uncommitted hint. It is however possible that you might get some error due to the dirty read behavior. The transaction isolation level is recommended if you do not want to specify hint on every table that you query and it is for the session. You can also set it in the tools option so it is always automatic. Note that this does affect behavior of any query since you will be reading uncommitted data i.e., perform dirty reads. In SQL Server 2005, you can use the READ COMMITTED SNAPSHOT option at the database level or the new snapshot isoaltion level which uses versioning mechanism to provide consistent view of data without taking locks.|||

The answer I am about to give is not likely to be pertinent to your question since you state short running, but just in case since you were so adament about NO LOCKS :) There is one tiny exception to the nolock/read uncommitted isolation level, in that it does take a shared schema lock so other users cannot drop/alter the table while your query is running. in 2005, run this:

create table testLocks
(
testLockId int,
bigValue char(8000) default (replicate('*',8000))
)

insert into testLocks(testLockId)
select 1
go 1000

set transaction isolation level read uncommitted
select * from testLocks
cross join testLocks as t2
-

Then on another connection run this (that query will return 1000000 16KB rows, so it will take a while :)

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,
request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then
(select object_name(object_id) from sys.partitions
where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

On my 3Ghz, 512 MB machine running Express Edition (on a Media Center PC) I got two rows, one of which was the shared schema lock, the other was a bulk operation lock, likely in Tempdb trying to build these rows. Unless you are dropping and creating rows, this is unlikely to be a concern.

|||If you perform insert/update in a transaction that has isolation level READ UNCOMMITTED it will anyhow acquire X locks. I suppose your short transaction are read-only though.|||We all kind of forgot about that one huh? Good point :)|||

thanks everyone for your very helpful information.

|||sorry I should have mentioned that I'm only running queries

does "with (nolock)" absolutely guarantee no locks are taken?

I need to run a few short-running queries against a production system. I need to be absolutely certain that SS doesn't take out any locks on the table as a result.

Does "with (nolock)" absolutely guarantee this? I've read BOL on the topic and I understand isolation level read-uncommitted. But I want to validate that there's not any undocumented behavior in SS that might violate the documentation (which clearly states that no shared locks are issued).

Thanks!

You're talking two different things.

with (nolock) is a HINT. That means there is a possibility that SS will override it.

SET TRANSACTION ISOLATION LEVEL is a command. AFAIK, SS won't override this.

|||SQL Server honors the read uncommitted hint. It is however possible that you might get some error due to the dirty read behavior. The transaction isolation level is recommended if you do not want to specify hint on every table that you query and it is for the session. You can also set it in the tools option so it is always automatic. Note that this does affect behavior of any query since you will be reading uncommitted data i.e., perform dirty reads. In SQL Server 2005, you can use the READ COMMITTED SNAPSHOT option at the database level or the new snapshot isoaltion level which uses versioning mechanism to provide consistent view of data without taking locks.|||

The answer I am about to give is not likely to be pertinent to your question since you state short running, but just in case since you were so adament about NO LOCKS :) There is one tiny exception to the nolock/read uncommitted isolation level, in that it does take a shared schema lock so other users cannot drop/alter the table while your query is running. in 2005, run this:

create table testLocks
(
testLockId int,
bigValue char(8000) default (replicate('*',8000))
)

insert into testLocks(testLockId)
select 1
go 1000

set transaction isolation level read uncommitted
select * from testLocks
cross join testLocks as t2
-

Then on another connection run this (that query will return 1000000 16KB rows, so it will take a while :)

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,
request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then
(select object_name(object_id) from sys.partitions
where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

On my 3Ghz, 512 MB machine running Express Edition (on a Media Center PC) I got two rows, one of which was the shared schema lock, the other was a bulk operation lock, likely in Tempdb trying to build these rows. Unless you are dropping and creating rows, this is unlikely to be a concern.

|||If you perform insert/update in a transaction that has isolation level READ UNCOMMITTED it will anyhow acquire X locks. I suppose your short transaction are read-only though.|||We all kind of forgot about that one huh? Good point :)|||

thanks everyone for your very helpful information.

|||sorry I should have mentioned that I'm only running queries

Documenting Tables and Queries

Hi,
I am completely new to SQL Server, just installed. I had been using
MSDE2000 on the way to re-writing a MS Access 2000 app into a Visual
Basic.Net web app.
I have learned to use SQL Server DTS to move the MS Access data to my new
SQL Server database. I am learning about SQL Server Enterprise Manager and
using it successfully.
In MS Access 2000, there is a simple menu item that allows you to document
the tables and queries, getting a hardcopy printout of the table properties.
I have not been able to find a similar capability within SQL Server. It
does not seem to have much hard copy print capability at all. I understand
that you can display everything, maybe I am just old fashion in wanting some
hard copy printout of the database table structures and properties.
Also, could you recommend a way of familiarizing myself with the functions
and capabilities (with examples) of this SQL Query Analyzer. It looks
really powerful. I believe that I just need to get initiated into the
concept and the syntax or language structure.
Thanks,
hugh
Hi Hugh,
Thanks for your post.
From your descriptions, I understood you would like to know how to document
the tables and queries in SQL Server. However, I would like to restate my
understanding of "document". You want to get the struct of the tables, the
codes of stored procedures, etc. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, SQL Server does not provide a stored procedure
directly.
If you want the code of a stored procedure, you could get the script from
SQL Server Enterprise Manager
- Click the database object
- All Task -> Generate SQL Task...
If you want to get the structure of tables, you could search the people
customized SELECT statements and some third party tools. Generally
speaking, I will use the query below for your reference
SELECT
'Table Name'=case when a.colorder=1 then d.name else '' end,
'ColName'=a.name,
'Identity'=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '
'else '' end,
'PK'=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name
in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '' else '' end,
'Type'=b.name,
'Length'=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
'Allow Null'=case when a.isnullable=1 then ''else '' end,
'Default Value'=isnull(e.text,'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and
d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:6AULFbFqFHA.2928@.TK2MSFTNGXA01.phx.gbl...
> Hi Hugh,
> Thanks for your post.
> From your descriptions, I understood you would like to know how to
> document
> the tables and queries in SQL Server. However, I would like to restate my
> understanding of "document". You want to get the struct of the tables, the
> codes of stored procedures, etc. If I have misunderstood your concern,
> please feel free to point it out.
> Based on my knowledge, SQL Server does not provide a stored procedure
> directly.
You guys should create a Data Dictionary segment for SQL Server and hide it
under Managment. DB2 has had this capapbility for a long time.
The drawing of a model in SS2000 is pretty lame.
|||Hugh:
I applaud your honesty in admitting that you are new to SQL and its
capabilities. The more I learn, the more I realize I don't know!
There are some good third-party tools for documenting databases. Check out
fmsinc.com. They developed a tool for documenting MS Access databases (which
actually do a decent job on Access Data Projects) and another higher priced
one for SQL Server.
Good luck.
TOdd