can we combine these 3 statements into one single query

SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp

SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.idSELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
UNION ALL
SELECT 2 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
UNION ALL
SELECT 3 as id, (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id|||The above query doesn't work for me and infact i want to get the percentage in a single query..
Thanks|||I'm "winging" this one wildly, but could you use:SELECT
CAST(Sum(CASE WHEN name IS NOT NULL
AND name <> '' THEN 1 END) AS FLOAT) / Count(*)
FROM dbo.empThis divides the number of names with value by the total to get the percentage of usable names.

-PatP|||How about

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX|||SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
........................

I could not execute the above query, can we write the query like that?
please correct me if i am wrong?|||Don't use Name <> NULL

No value will ever match that:

MyField > NULL will always be false
MyField < NULL will always be false
MyField <> NULL will always be false
MyField = NULL will always be false
and so on for all applicable operators...

try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.|||try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.

My bad. Use this instead:

NULLIF('', TRIM(Name)) IS NOT NULL|||Just curious, but didn't my suggestion work? What results did it produce?

-PatP|||Replace:
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
By:
WHERE LTRIM(ISNULL(name,'')) <> '' ) AS XXX

yabu.

can stored procedure run more than 1 sql statements

Hi all,
I have 2 independent stored procedures(I cannot join them because of
the group condition are different however they have the same cust id)
but one for reading the summary and other showing the details of the
summary. Now I want to run these two stored procedures in order to
get the both return results for generating a .Net crytral report. Can
I get the results form both stored procedures and how can I do that.
Please help me. Thanks a lot.
Hellosixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello

Hi,

Why not use views for this. You could achieve with that.

-Manoj|||..NET should be able to handle multiple result sets from a single sp.

You can "spell out" both SQL/T-SQL code blocks in the same sp or you
can create two seperate sp's and call sp B from sp A via an EXECUTE
statement.

Good Luck

sixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello|||groups@.rlmoore.net (rm) wrote in message news:<4c61f49.0309191421.14d680a4@.posting.google.com>...
> .NET should be able to handle multiple result sets from a single sp.
> You can "spell out" both SQL/T-SQL code blocks in the same sp or you
> can create two seperate sp's and call sp B from sp A via an EXECUTE
> statement.
> Good Luck
I have try the 2nd method (spA exec spB) but it seem that nothing can
return at the .net crystal report even I do not use the subreport. is
there other methods?
Pls help me.
Thx
Hello

Can several UPDATE statements deadlock within serializable transaction

This can cause conversion deadlock:
====
set transaction isolation level serializable
begin tran
select * from authors where au_id = 'bla'
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
because shared locks in serializable transactions are held for the duration
of the transaction and exculsive locks are not compatible with shared locks
from another transaction.
Here is the question - can this deadlock as well?
====
set transaction isolation level serializable
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
If this can deadlock, how can I prevent it?
I am trying to resolve COM+ deadlocking issues...
Thanks,
-StanWhy are you using a SERIALIZABLE level?
In the update, could you get away with specifying
an UPDLOCK hint instead?
"Stan" <nospam@.yahoo.com> wrote in message
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====
> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> because shared locks in serializable transactions are held for the
duration
> of the transaction and exculsive locks are not compatible with shared
locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====
> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||1. I am not using serializable, COM+ is
2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using a SERIALIZABLE level?
> In the update, could you get away with specifying
> an UPDLOCK hint instead?
> "Stan" <nospam@.yahoo.com> wrote in message
> news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> duration
> locks
>|||2. UPDATE hint should be put on select statement
Thank you,
Alex
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
>|||Use the hint on the SELECT
ie
SELECT mycolumn
FROM mytable WITH (UPDLOCK)
WHERE ID = @.ID
I don't know anything about COM+ so I couldn't
say how to suppress the SERIALIZABLE... it
seems too drastic.
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
>|||Hi Stan,
I've been reading a lot about locking and transaction isolation level
lately, as I was troubleshooting deadlocks from dll's running under COM+ as
well. I don't think a single update statement can cause deadlocks. Is there
a select statement in the calling client code that runs within the same
transaction? In C# code - which I was reviewing - I had to look for methods
with the attribute "Autocomplete()" within classes with the attribute
"Transaction(TransactionOption.Required)". This meant that the execution of
this method will be encapsulated in 1 transaction. If no unhandled exception
occurs, the transaction is automatically committed and otherwise it is
rolled back. Which leads me to the question if the T-SQL really has a
"commit" statement, because it isn't needed and could maybe even cause an
error (I'm not sure how COM+ reacts to a "no current transaction available"
when it tries to commit after the transaction is already closed, it may or
may not check the @.@.trancount function.
This is al speculative, now some solid advice: execute the following
statement in query analyzer as sa user: "dbcc traceon(-1, 1204)". 1204
instructs Sql Server to log deadlock information to the error log file - you
can find it under Management in the enterprise manager -; -1 makes the
traceflag global instead of limited to the current session. If you search in
google with "deadlock 1204" you'll learn how to interpret the error log.
This was a great help for me when investigating the deadlock situations.
Cheers,
Henk Kok
"Stan" <nospam@.yahoo.com> schreef in bericht
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====
> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> because shared locks in serializable transactions are held for the
> duration
> of the transaction and exculsive locks are not compatible with shared
> locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====
> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==
> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||> transaction? In C# code - which I was reviewing - I had to look for
methods
> with the attribute "Autocomplete()" within classes with the attribute
> "Transaction(TransactionOption.Required)". This meant that the execution
of
> this method will be encapsulated in 1 transaction.
I understand that. However all my "get" stored procedures have
"set transaction isolation level read uncommitted" statement. This should
removed the shared locks immidiately after select statement, but I still
have deadlocks...
"Update" stored procedures do not have this statement and I was wondering if
this can be a cause of deadlocking..

Can several UPDATE statements deadlock within serializable transaction

This can cause conversion deadlock:
==== set transaction isolation level serializable
begin tran
select * from authors where au_id = 'bla'
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
because shared locks in serializable transactions are held for the duration
of the transaction and exculsive locks are not compatible with shared locks
from another transaction.
Here is the question - can this deadlock as well?
==== set transaction isolation level serializable
update authors set au_lname = au_lname where au_id = 'bla'
commit
==
If this can deadlock, how can I prevent it?
I am trying to resolve COM+ deadlocking issues...
Thanks,
-StanWhy are you using a SERIALIZABLE level?
In the update, could you get away with specifying
an UPDLOCK hint instead?
"Stan" <nospam@.yahoo.com> wrote in message
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> because shared locks in serializable transactions are held for the
duration
> of the transaction and exculsive locks are not compatible with shared
locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||1. I am not using serializable, COM+ is
2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using a SERIALIZABLE level?
> In the update, could you get away with specifying
> an UPDLOCK hint instead?
> "Stan" <nospam@.yahoo.com> wrote in message
> news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > This can cause conversion deadlock:
> >
> > ====> > set transaction isolation level serializable
> >
> > begin tran
> >
> > select * from authors where au_id = 'bla'
> >
> > update authors set au_lname = au_lname where au_id = 'bla'
> >
> > commit
> >
> > ==> >
> > because shared locks in serializable transactions are held for the
> duration
> > of the transaction and exculsive locks are not compatible with shared
> locks
> > from another transaction.
> >
> > Here is the question - can this deadlock as well?
> >
> > ====> > set transaction isolation level serializable
> >
> > update authors set au_lname = au_lname where au_id = 'bla'
> >
> > commit
> >
> > ==> >
> > If this can deadlock, how can I prevent it?
> >
> > I am trying to resolve COM+ deadlocking issues...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||2. UPDATE hint should be put on select statement
--
Thank you,
Alex
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> > Why are you using a SERIALIZABLE level?
> >
> > In the update, could you get away with specifying
> > an UPDLOCK hint instead?
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > > This can cause conversion deadlock:
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > begin tran
> > >
> > > select * from authors where au_id = 'bla'
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > because shared locks in serializable transactions are held for the
> > duration
> > > of the transaction and exculsive locks are not compatible with shared
> > locks
> > > from another transaction.
> > >
> > > Here is the question - can this deadlock as well?
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > If this can deadlock, how can I prevent it?
> > >
> > > I am trying to resolve COM+ deadlocking issues...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||Use the hint on the SELECT
ie
SELECT mycolumn
FROM mytable WITH (UPDLOCK)
WHERE ID = @.ID
I don't know anything about COM+ so I couldn't
say how to suppress the SERIALIZABLE... it
seems too drastic.
"Stan" <nospam@.yahoo.com> wrote in message
news:eNSiFqPIFHA.3760@.TK2MSFTNGP12.phx.gbl...
> 1. I am not using serializable, COM+ is
> 2. I can put UPDLOCK, but will it have an effect in UPDATE statement?
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23F$WQFPIFHA.2752@.TK2MSFTNGP12.phx.gbl...
> > Why are you using a SERIALIZABLE level?
> >
> > In the update, could you get away with specifying
> > an UPDLOCK hint instead?
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> > > This can cause conversion deadlock:
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > begin tran
> > >
> > > select * from authors where au_id = 'bla'
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > because shared locks in serializable transactions are held for the
> > duration
> > > of the transaction and exculsive locks are not compatible with shared
> > locks
> > > from another transaction.
> > >
> > > Here is the question - can this deadlock as well?
> > >
> > > ====> > > set transaction isolation level serializable
> > >
> > > update authors set au_lname = au_lname where au_id = 'bla'
> > >
> > > commit
> > >
> > > ==> > >
> > > If this can deadlock, how can I prevent it?
> > >
> > > I am trying to resolve COM+ deadlocking issues...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||Hi Stan,
I've been reading a lot about locking and transaction isolation level
lately, as I was troubleshooting deadlocks from dll's running under COM+ as
well. I don't think a single update statement can cause deadlocks. Is there
a select statement in the calling client code that runs within the same
transaction? In C# code - which I was reviewing - I had to look for methods
with the attribute "Autocomplete()" within classes with the attribute
"Transaction(TransactionOption.Required)". This meant that the execution of
this method will be encapsulated in 1 transaction. If no unhandled exception
occurs, the transaction is automatically committed and otherwise it is
rolled back. Which leads me to the question if the T-SQL really has a
"commit" statement, because it isn't needed and could maybe even cause an
error (I'm not sure how COM+ reacts to a "no current transaction available"
when it tries to commit after the transaction is already closed, it may or
may not check the @.@.trancount function.
This is al speculative, now some solid advice: execute the following
statement in query analyzer as sa user: "dbcc traceon(-1, 1204)". 1204
instructs Sql Server to log deadlock information to the error log file - you
can find it under Management in the enterprise manager -; -1 makes the
traceflag global instead of limited to the current session. If you search in
google with "deadlock 1204" you'll learn how to interpret the error log.
This was a great help for me when investigating the deadlock situations.
Cheers,
Henk Kok
"Stan" <nospam@.yahoo.com> schreef in bericht
news:OtW9NmOIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> This can cause conversion deadlock:
> ====> set transaction isolation level serializable
> begin tran
> select * from authors where au_id = 'bla'
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> because shared locks in serializable transactions are held for the
> duration
> of the transaction and exculsive locks are not compatible with shared
> locks
> from another transaction.
> Here is the question - can this deadlock as well?
> ====> set transaction isolation level serializable
> update authors set au_lname = au_lname where au_id = 'bla'
> commit
> ==> If this can deadlock, how can I prevent it?
> I am trying to resolve COM+ deadlocking issues...
> Thanks,
> -Stan
>|||> transaction? In C# code - which I was reviewing - I had to look for
methods
> with the attribute "Autocomplete()" within classes with the attribute
> "Transaction(TransactionOption.Required)". This meant that the execution
of
> this method will be encapsulated in 1 transaction.
I understand that. However all my "get" stored procedures have
"set transaction isolation level read uncommitted" statement. This should
removed the shared locks immidiately after select statement, but I still
have deadlocks...
"Update" stored procedures do not have this statement and I was wondering if
this can be a cause of deadlocking..

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. It’s a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrec
t
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:

> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>
>|||Hi John !

> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for
SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is record
ed would be each inserted
row and not the SELECT statement. One could argue that the inserted rows wil
l show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)

> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far
higher on products I
don't know that well, where I only score OK on SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in messag
e
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...[vbcol=seagreen]
> Jim
> A select into will write to the transaction log, but as Andrew says regula
r
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. It’s a bit hard to argue to
o
> strongly with someone interviewing you. I told him I thought he was incorr
ect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:
>

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?
Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>
|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. It’s a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrect
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:

> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>
>
|||Hi John !

> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is recorded would be each inserted
row and not the SELECT statement. One could argue that the inserted rows will show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)

> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far higher on products I
don't know that well, where I only score OK on SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in message
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...[vbcol=seagreen]
> Jim
> A select into will write to the transaction log, but as Andrew says regular
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. It’s a bit hard to argue too
> strongly with someone interviewing you. I told him I thought he was incorrect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
--
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. Itâ's a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrect
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:
> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> > Can select statements ever go into the transaction log?
> >
>
>|||Hi John !
> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is recorded would be each inserted
row and not the SELECT statement. One could argue that the inserted rows will show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)
> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far higher on products I
don't know that well, where I only score OK on SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in message
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...
> Jim
> A select into will write to the transaction log, but as Andrew says regular
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. Itâ's a bit hard to argue too
> strongly with someone interviewing you. I told him I thought he was incorrect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:
>> Selects do not get logged in the tran log. If you want to see what
>> statements are being executed you should use trace or profiler for that.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
>> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>> > Can select statements ever go into the transaction log?
>> >
>>