Hi All,
I have a bunch of SQL servers that have a high volume of Bulk Inserts
happening every day (Millions of records per insert)
The most heavily used server has DBs in Simple Recovery Mode. However
both for the database and log files which are set to autogrow 10%, I am
seeing the following characteristics:
database files have about 10-15% free space every night.
log files however are about 70-90% free for all DBs
Now this is a problem since the log file size is 10gb and its using
only 2-3gb of it.
I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
the space but also reduced the log filesize to 0 and also one of my
developers complained of a table getting truncated during a stored proc
execution at that time.
1. is DBCC shrinkfile a good way to truncate free space from logs? How
could it have truncated the log to a size of 0.
2. What would be a good strategy to free up unused space from DB and
Log files every day?
Thanks all!
Hi
Good stuff to start
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
<theonlyrashid@.gmail.com> wrote in message
news:1128600308.204142.67240@.g44g2000cwa.googlegro ups.com...
> Hi All,
> I have a bunch of SQL servers that have a high volume of Bulk Inserts
> happening every day (Millions of records per insert)
> The most heavily used server has DBs in Simple Recovery Mode. However
> both for the database and log files which are set to autogrow 10%, I am
> seeing the following characteristics:
> database files have about 10-15% free space every night.
> log files however are about 70-90% free for all DBs
> Now this is a problem since the log file size is 10gb and its using
> only 2-3gb of it.
> I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
> the space but also reduced the log filesize to 0 and also one of my
> developers complained of a table getting truncated during a stored proc
> execution at that time.
> 1. is DBCC shrinkfile a good way to truncate free space from logs? How
> could it have truncated the log to a size of 0.
> 2. What would be a good strategy to free up unused space from DB and
> Log files every day?
> Thanks all!
>
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Thursday, March 22, 2012
Best Practices for Reducing Transaction Log Sizes?
Hi All,
I have a bunch of SQL servers that have a high volume of Bulk Inserts
happening every day (Millions of records per insert)
The most heavily used server has DBs in Simple Recovery Mode. However
both for the database and log files which are set to autogrow 10%, I am
seeing the following characteristics:
database files have about 10-15% free space every night.
log files however are about 70-90% free for all DBs
Now this is a problem since the log file size is 10gb and its using
only 2-3gb of it.
I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
the space but also reduced the log filesize to 0 and also one of my
developers complained of a table getting truncated during a stored proc
execution at that time.
1. is DBCC shrinkfile a good way to truncate free space from logs? How
could it have truncated the log to a size of 0.
2. What would be a good strategy to free up unused space from DB and
Log files every day?
Thanks all!Hi
Good stuff to start
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
<theonlyrashid@.gmail.com> wrote in message
news:1128600308.204142.67240@.g44g2000cwa.googlegroups.com...
> Hi All,
> I have a bunch of SQL servers that have a high volume of Bulk Inserts
> happening every day (Millions of records per insert)
> The most heavily used server has DBs in Simple Recovery Mode. However
> both for the database and log files which are set to autogrow 10%, I am
> seeing the following characteristics:
> database files have about 10-15% free space every night.
> log files however are about 70-90% free for all DBs
> Now this is a problem since the log file size is 10gb and its using
> only 2-3gb of it.
> I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
> the space but also reduced the log filesize to 0 and also one of my
> developers complained of a table getting truncated during a stored proc
> execution at that time.
> 1. is DBCC shrinkfile a good way to truncate free space from logs? How
> could it have truncated the log to a size of 0.
> 2. What would be a good strategy to free up unused space from DB and
> Log files every day?
> Thanks all!
>
I have a bunch of SQL servers that have a high volume of Bulk Inserts
happening every day (Millions of records per insert)
The most heavily used server has DBs in Simple Recovery Mode. However
both for the database and log files which are set to autogrow 10%, I am
seeing the following characteristics:
database files have about 10-15% free space every night.
log files however are about 70-90% free for all DBs
Now this is a problem since the log file size is 10gb and its using
only 2-3gb of it.
I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
the space but also reduced the log filesize to 0 and also one of my
developers complained of a table getting truncated during a stored proc
execution at that time.
1. is DBCC shrinkfile a good way to truncate free space from logs? How
could it have truncated the log to a size of 0.
2. What would be a good strategy to free up unused space from DB and
Log files every day?
Thanks all!Hi
Good stuff to start
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
<theonlyrashid@.gmail.com> wrote in message
news:1128600308.204142.67240@.g44g2000cwa.googlegroups.com...
> Hi All,
> I have a bunch of SQL servers that have a high volume of Bulk Inserts
> happening every day (Millions of records per insert)
> The most heavily used server has DBs in Simple Recovery Mode. However
> both for the database and log files which are set to autogrow 10%, I am
> seeing the following characteristics:
> database files have about 10-15% free space every night.
> log files however are about 70-90% free for all DBs
> Now this is a problem since the log file size is 10gb and its using
> only 2-3gb of it.
> I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
> the space but also reduced the log filesize to 0 and also one of my
> developers complained of a table getting truncated during a stored proc
> execution at that time.
> 1. is DBCC shrinkfile a good way to truncate free space from logs? How
> could it have truncated the log to a size of 0.
> 2. What would be a good strategy to free up unused space from DB and
> Log files every day?
> Thanks all!
>
Tuesday, March 20, 2012
Best Practices for Insert/Update/Delete
for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...
My 2 cents... SPs are a great way to interface with a database. For me, I will never access a DB in any other way. In fact, I would say it is good practice to secure the database so that only the defined stored procedures can be Executed against the DB. No direct table reads or writes. This will ensure that no one (other than an errant DBA) can do anything other than what is inteded by the interface provided through the stored procedures. It is essentially just another layer in the application model. Also, it provides a level of reusability, and design hiding... generally considered good things. An application will essentially only need to make "function calls" on the database, rather than some nasty select statement that, oh, by the way, I split this table out into two seperate ones, so now you have to go rewrite all your queries in every application that was ever written that uses my database because I didn't just write stored procedures in the first place, which, looking back would have been smart because then I would only have to fix things in two places.
// of course, you probably could create a view with the same name as the old table to fix it, but, just making a point
|||
I think you should use Stored Procedures whenever possible. But for making sure that the value in a column is unique you should always use unique constraints etc. For insert/update/delete always use SPs.
sqlBest Practice: Procedures: (Insert And Update) OR JUST (Save)
I have a Product Table.
And now I have to create its Stored Procedures.
I am asking the best practice regarding the methods Insert And Update.
There are two options.
1. Create separate 2 procedures like InsertProduct and UpdateProduct.
2. Create just 1 procedure like ModifyProduct. In which programmatically check that either the record is present or not. If present then update and if not then insert. Just like Imar has done in his articlehttp://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419
Can any one explain the better one.
Waiting for helpful replies.
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419
a
There's no "best practice" for this one. Imar presumably likes his "Save" approach because whether you are adding a new record or amending an existing one, generally software applications ask you to click the Save button - so he likes to make his programming logic analogous.
Personally, I prefer theKISS principal, and create 2 separate procedures. It's clear from the interface which one to call as a result of user action. I also see the decision as to whether to Insert or Update as being a business logic decision, and I'm uncomfortable about putting business logic in a stored procedure. The reason for this is that the business logic may not be transferable to another database platform.
I do not understand your last point regardgin Business Logic.
I understand that it should be better in your opinion to create 2 separate procedures.
But what about Business Logic Methods.
|||
zeeshanuddinkhan@.hotmail.com:
I do not understand your last point regardgin Business Logic.
Well, I suppose it depends on how you define "Business Logic". And this illustrates one of the problems with layering an application. The reason why there are so many books and theories on architecture is because there is no "right" way to do it, and definitions of what belongs in which layer are different. Some things so obviously belong in certain layers, but other things might or might not - depending on what you are used to, how you think, what you are told to do by your team leader etc. There is for example, a huge debate about whether stored procedures are a bad thing altogether, because they can be viewed as placing business logic in a database and not in the BLL.
It also depends on how atomic (how much you like to break functionality down into discrete parts - methods, classes, procedures etc) you want your application. Imar would no doubt suggest that the action of the user defines that a Save() method be called, and that while the Save() method can include two alternative actions (Insert or Update), both lead to a row being saved to the database, so it's essentially the same action. The procedure decides whether an existing row is updated or a new one created. I see the difference between Insert and Update as being too different to be combined into one method. Consequently, I break the procedures apart into separate atomic constructs. I view the difference between the 2 as a business logic thing - because I can - and something in my gut tell me it is.
That's purely my view and is neither right or wrong. Others may not agree, and they will no doubt have valid justification for their view. It's right for me but wrong for Imar. And that's why I said at the beginning that there is no Best Practice for Insert or Update v Save. It's purely down to your personal preference. Imar's solution has a certain appeal, in that it contains a certain "cleverness". Some people like that. Nothing wrong with that at all.
Quite often the difference between two alternatives is purely philosophical, and has nothing to do with performance, maintainability or re-useability, which are the three items that Best Practice should be concerned with.
[Edit]
Just re-read my first response and having rambled on above, I see I may have missed your point. If you were asking about transferable business logic, it may be that you have to move the application to a different database system which doesn't support stored procedures, but may support basic INSERT, UPDATE, SELECT and DELETE saved queries. In this case, it wouldn't be too difficult to copy and paste the SQL form each part of the proc, but if you make procs do too much in terms of massaging data, or deciding on a course of action, you will create a load more work in your migration.
You are also perfectly free to ignore this on the basis that "it will never happen". Only you know best.
Monday, March 19, 2012
best practice retrieveing current identity value
HI all,
I have a claim table. An insert trigger, in that insert trigger I want to
retrieve the current identity value of that insert, to be used elswhere
which is best scope_identity(), ident_current(), or @.@.identity
Thanks
RobertRobert,
All three will (in theory) work, and each has it's own values for a given
situation.
I tend to use @.@.identity, though, as BoL explains, this is not limited to
the current scope, and so they would suggest using SCOPE_IDENTITY which woul
d
be more exacting.
Hope this assists,
Tony
"Robert Bravery" wrote:
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
>
>|||Robert Bravery wrote:
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
Did you read about the differences between those functions in Books
Online? Either may be appropriate depending on requirements.
BUT - a big but - they are all unlikely to be useful in a trigger.
That's because well-written trigger code should always assume that more
than one row may be updated in the table that invokes the trigger.
Triggers fire once per statement, NOT once per row, so usually in a
trigger if you want to retrieve the inserted IDENTITY values you do so
by referencing the virtual table called INSERTED. That table could
contain 0,1,2 or any number of rows.
The IDENTITY functions would probably only be useful in a trigger if
your trigger contained a single row INSERT to another table regardless
of how many rows were updated by the statement that prompted the
trigger. In that case you would probably use SCOPE_IDENTITY.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Interesting point. Where would that leave us when using either
SCOPE_IDENTITY or @.@.Identity from a SProc? I was under the belief that the
correct identity was always returned from that thread used. Would you say
then that a trigger could be less precise than a SProc for retrieving the
correct Identity for a given inert transaction?
Any further insight would be useful,
Thanks,
Tony
"David Portas" wrote:
> Robert Bravery wrote:
> Did you read about the differences between those functions in Books
> Online? Either may be appropriate depending on requirements.
> BUT - a big but - they are all unlikely to be useful in a trigger.
> That's because well-written trigger code should always assume that more
> than one row may be updated in the table that invokes the trigger.
> Triggers fire once per statement, NOT once per row, so usually in a
> trigger if you want to retrieve the inserted IDENTITY values you do so
> by referencing the virtual table called INSERTED. That table could
> contain 0,1,2 or any number of rows.
> The IDENTITY functions would probably only be useful in a trigger if
> your trigger contained a single row INSERT to another table regardless
> of how many rows were updated by the statement that prompted the
> trigger. In that case you would probably use SCOPE_IDENTITY.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Tony Scott wrote:
> David,
> Interesting point. Where would that leave us when using either
> SCOPE_IDENTITY or @.@.Identity from a SProc? I was under the belief that the
> correct identity was always returned from that thread used. Would you say
> then that a trigger could be less precise than a SProc for retrieving the
> correct Identity for a given inert transaction?
> Any further insight would be useful,
> Thanks,
> Tony
>
SCOPE_IDENTITY does always have local scope but so does the INSERTED
virtual table in a trigger. In that respect a trigger is no less
precise in the way IDENTITY is retrieved - it's just that you have to
allow for multiple-row inserts. Example:
CREATE TABLE T1 (x INTEGER NOT NULL IDENTITY PRIMARY KEY, z INTEGER NOT
NULL UNIQUE)
GO
CREATE TRIGGER trg ON T1 FOR INSERT
AS
SELECT SCOPE_IDENTITY() AS [scope_identity] ;
SELECT @.@.IDENTITY AS [@.@.identity] ;
SELECT IDENT_CURRENT('T1') AS [ident_current] ;
GO
INSERT INTO T1 (z)
SELECT 1 UNION ALL
SELECT 2 ;
scope_identity
---
NULL
(1 row(s) affected)
@.@.identity
---
2
(1 row(s) affected)
ident_current
---
2
(1 row(s) affected)
The first result (NULL) is wrong because SCOPE_IDENTITY has local scope
so it doesn't see the INSERT at all.
The second is wrong because although it returns one of the IDENTITY
values there were actually two rows inserted. We can't predict the row
for which the IDENTITY will be returned - it will just be the highest
numbered IDENTITY value. This isn't good in a trigger because we can
only use this method to reference a single row and triggers should
always assume multiple rows may be updated.
The third result may be wrong if other connections also update the
table because IDENT_CURRENT is scoped to the table and not to the
session.
The reliable method uses the INSERTED table:
CREATE TRIGGER trg ON T1 FOR INSERT
AS
SELECT x FROM inserted ;
GO
If your procs insert multiple rows you also need to think about the
same issue in procs when you need the IDENTITY value. Don't assume the
values inserted will be contiguous with the value returned by
SCOPE_IDENTITY. There are at least some conditions where they may not
be.
In SQL Server 2005 you can address things slightly differently using
the OUPUT clause of the INSERT, UPDATE and DELETE statements. These
could eliminate the need for some triggers.
In SQL Server 2000 you should also be able to retrieve multiple
IDENTITY values using an alternate key of the table. There should
always be another candidate key. If you don't have such a key then you
have a significant design flaw which you should fix.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thank you for an excellent answer, very informative.
Tony
"David Portas" wrote:
> Tony Scott wrote:
> SCOPE_IDENTITY does always have local scope but so does the INSERTED
> virtual table in a trigger. In that respect a trigger is no less
> precise in the way IDENTITY is retrieved - it's just that you have to
> allow for multiple-row inserts. Example:
> CREATE TABLE T1 (x INTEGER NOT NULL IDENTITY PRIMARY KEY, z INTEGER NOT
> NULL UNIQUE)
> GO
> CREATE TRIGGER trg ON T1 FOR INSERT
> AS
> SELECT SCOPE_IDENTITY() AS [scope_identity] ;
> SELECT @.@.IDENTITY AS [@.@.identity] ;
> SELECT IDENT_CURRENT('T1') AS [ident_current] ;
> GO
> INSERT INTO T1 (z)
> SELECT 1 UNION ALL
> SELECT 2 ;
> scope_identity
> ---
> NULL
> (1 row(s) affected)
> @.@.identity
> ---
> 2
> (1 row(s) affected)
> ident_current
> ---
> 2
> (1 row(s) affected)
>
> The first result (NULL) is wrong because SCOPE_IDENTITY has local scope
> so it doesn't see the INSERT at all.
> The second is wrong because although it returns one of the IDENTITY
> values there were actually two rows inserted. We can't predict the row
> for which the IDENTITY will be returned - it will just be the highest
> numbered IDENTITY value. This isn't good in a trigger because we can
> only use this method to reference a single row and triggers should
> always assume multiple rows may be updated.
> The third result may be wrong if other connections also update the
> table because IDENT_CURRENT is scoped to the table and not to the
> session.
> The reliable method uses the INSERTED table:
> CREATE TRIGGER trg ON T1 FOR INSERT
> AS
> SELECT x FROM inserted ;
> GO
> If your procs insert multiple rows you also need to think about the
> same issue in procs when you need the IDENTITY value. Don't assume the
> values inserted will be contiguous with the value returned by
> SCOPE_IDENTITY. There are at least some conditions where they may not
> be.
> In SQL Server 2005 you can address things slightly differently using
> the OUPUT clause of the INSERT, UPDATE and DELETE statements. These
> could eliminate the need for some triggers.
> In SQL Server 2000 you should also be able to retrieve multiple
> IDENTITY values using an alternate key of the table. There should
> always be another candidate key. If you don't have such a key then you
> have a significant design flaw which you should fix.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi,
Thanks for the reply David.
> Did you read about the differences between those functions in Books
> Online? Either may be appropriate depending on requirements.
Yes I did. And I also got the idea that they all might be appropiate. But no
quite understanding the exact process of a insert trigger (as you explained)
I was
as to what was the more correct way
> BUT - a big but - they are all unlikely to be useful in a trigger.
> That's because well-written trigger code should always assume that more
> than one row may be updated in the table that invokes the trigger.
> Triggers fire once per statement, NOT once per row, so usually in a
> trigger if you want to retrieve the inserted IDENTITY values you do so
> by referencing the virtual table called INSERTED. That table could
> contain 0,1,2 or any number of rows.
At this point I am assumning a single row insert transaction. The table
involved is a claims table, needing a single input for each claim
as in:
INSERT INTO [RASRMIS].[dbo].[Claim]([divid], [dhid], [LayerID], [peril],
[cause], [resource], [fault], [DOL], [DREP])
VALUES(1812, 1237, 5, 1, 1, 1, 1, getdate()-1650, getdate())
Would this be considered as a single row insert, on a single statement,
single transaction
I then issued:
Select @.@.identity [@.@.Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()],
ident_current('claim') [ident_current()]
But got back all the same value.
But if I put the above statement into a trigger, I receive the dame values
from the @.@.identity and ident_current() functions. Scope_identity() returns
null. I would have thought that the insert trigger is within scope here
Hopefully I have explained correctly
Thankls
Robert|||Robert Bravery wrote:
> At this point I am assumning a single row insert transaction.
Why would you bother to assume that? It doesn't help you. It just means
the DBA will curse you one day when he needs to do some ad-hoc
maintenance... or integrate some external data... or the user needs an
enhancement to the application, or...
> The table
> involved is a claims table, needing a single input for each claim
> as in:
> INSERT INTO [RASRMIS].[dbo].[Claim]([divid], [dhid], [LayerID], [peril],
> [cause], [resource], [fault], [DOL], [DREP])
> VALUES(1812, 1237, 5, 1, 1, 1, 1, getdate()-1650, getdate())
> Would this be considered as a single row insert, on a single statement,
> single transaction
> I then issued:
> Select @.@.identity [@.@.Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()],
> ident_current('claim') [ident_current()]
> But got back all the same value.
> But if I put the above statement into a trigger, I receive the dame values
> from the @.@.identity and ident_current() functions. Scope_identity() return
s
> null. I would have thought that the insert trigger is within scope here
>
No. The trigger runs in its own scope.
The solution is:
SELECT id FROM inserted ;
"Inserted" is a virtual table only visible in triggers.
This isn't a good example because you don't usually want to return
results from triggers. The exact solution of course depends on what you
want to do with the IDENTITY value(s) after you retrieve them. For
example you can insert them to another table:
INSERT INTO other_table (id, ...)
SELECT id
FROM inserted ;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Unless you're using an INSTEAD OF INSERT trigger, then you shouldn't rely on
the value returned by @.@.IDENTITY; under no circumstances should you rely on
the value returned by IDENT_CURRENT()--at least not for what you're looking
for. There can be more than one FOR or AFTER INSERT trigger on a table, and
any one of them could affect @.@.IDENTITY. IDENT_CURRENT() changes whenever
any INSERT occurs on any connection, so it could change between the time
that you read it and the time that you're ready to use it, or even worse: it
could change during whatever modification you're trying to make. In a FOR
or AFTER trigger, the only reliable ways to retrieve the generated IDENTITY
values is to use the inserted pseudotable, or to use another candidate key.
An INSTEAD OF trigger fires instead of the action specified, so new IDENTITY
values haven't yet been generated at the time that the inserted pseudotable
is populated. You can use SCOPE_IDENTITY() if you use a cursor to process
the contents of the inserted pseudotable, but I would recommend against it.
Using cursors in triggers is like publishing uncomplimentary caricatures of
Mohammed: you're bound to get burned. In an INSTEAD OF INSERT trigger,
you're best bet is to use another candidate key to obtain the IDENTITY
values.
"Robert Bravery" <me@.u.com> wrote in message
news:u07T2i9KGHA.1424@.TK2MSFTNGP12.phx.gbl...
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
>
I have a claim table. An insert trigger, in that insert trigger I want to
retrieve the current identity value of that insert, to be used elswhere
which is best scope_identity(), ident_current(), or @.@.identity
Thanks
RobertRobert,
All three will (in theory) work, and each has it's own values for a given
situation.
I tend to use @.@.identity, though, as BoL explains, this is not limited to
the current scope, and so they would suggest using SCOPE_IDENTITY which woul
d
be more exacting.
Hope this assists,
Tony
"Robert Bravery" wrote:
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
>
>|||Robert Bravery wrote:
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
Did you read about the differences between those functions in Books
Online? Either may be appropriate depending on requirements.
BUT - a big but - they are all unlikely to be useful in a trigger.
That's because well-written trigger code should always assume that more
than one row may be updated in the table that invokes the trigger.
Triggers fire once per statement, NOT once per row, so usually in a
trigger if you want to retrieve the inserted IDENTITY values you do so
by referencing the virtual table called INSERTED. That table could
contain 0,1,2 or any number of rows.
The IDENTITY functions would probably only be useful in a trigger if
your trigger contained a single row INSERT to another table regardless
of how many rows were updated by the statement that prompted the
trigger. In that case you would probably use SCOPE_IDENTITY.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Interesting point. Where would that leave us when using either
SCOPE_IDENTITY or @.@.Identity from a SProc? I was under the belief that the
correct identity was always returned from that thread used. Would you say
then that a trigger could be less precise than a SProc for retrieving the
correct Identity for a given inert transaction?
Any further insight would be useful,
Thanks,
Tony
"David Portas" wrote:
> Robert Bravery wrote:
> Did you read about the differences between those functions in Books
> Online? Either may be appropriate depending on requirements.
> BUT - a big but - they are all unlikely to be useful in a trigger.
> That's because well-written trigger code should always assume that more
> than one row may be updated in the table that invokes the trigger.
> Triggers fire once per statement, NOT once per row, so usually in a
> trigger if you want to retrieve the inserted IDENTITY values you do so
> by referencing the virtual table called INSERTED. That table could
> contain 0,1,2 or any number of rows.
> The IDENTITY functions would probably only be useful in a trigger if
> your trigger contained a single row INSERT to another table regardless
> of how many rows were updated by the statement that prompted the
> trigger. In that case you would probably use SCOPE_IDENTITY.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Tony Scott wrote:
> David,
> Interesting point. Where would that leave us when using either
> SCOPE_IDENTITY or @.@.Identity from a SProc? I was under the belief that the
> correct identity was always returned from that thread used. Would you say
> then that a trigger could be less precise than a SProc for retrieving the
> correct Identity for a given inert transaction?
> Any further insight would be useful,
> Thanks,
> Tony
>
SCOPE_IDENTITY does always have local scope but so does the INSERTED
virtual table in a trigger. In that respect a trigger is no less
precise in the way IDENTITY is retrieved - it's just that you have to
allow for multiple-row inserts. Example:
CREATE TABLE T1 (x INTEGER NOT NULL IDENTITY PRIMARY KEY, z INTEGER NOT
NULL UNIQUE)
GO
CREATE TRIGGER trg ON T1 FOR INSERT
AS
SELECT SCOPE_IDENTITY() AS [scope_identity] ;
SELECT @.@.IDENTITY AS [@.@.identity] ;
SELECT IDENT_CURRENT('T1') AS [ident_current] ;
GO
INSERT INTO T1 (z)
SELECT 1 UNION ALL
SELECT 2 ;
scope_identity
---
NULL
(1 row(s) affected)
@.@.identity
---
2
(1 row(s) affected)
ident_current
---
2
(1 row(s) affected)
The first result (NULL) is wrong because SCOPE_IDENTITY has local scope
so it doesn't see the INSERT at all.
The second is wrong because although it returns one of the IDENTITY
values there were actually two rows inserted. We can't predict the row
for which the IDENTITY will be returned - it will just be the highest
numbered IDENTITY value. This isn't good in a trigger because we can
only use this method to reference a single row and triggers should
always assume multiple rows may be updated.
The third result may be wrong if other connections also update the
table because IDENT_CURRENT is scoped to the table and not to the
session.
The reliable method uses the INSERTED table:
CREATE TRIGGER trg ON T1 FOR INSERT
AS
SELECT x FROM inserted ;
GO
If your procs insert multiple rows you also need to think about the
same issue in procs when you need the IDENTITY value. Don't assume the
values inserted will be contiguous with the value returned by
SCOPE_IDENTITY. There are at least some conditions where they may not
be.
In SQL Server 2005 you can address things slightly differently using
the OUPUT clause of the INSERT, UPDATE and DELETE statements. These
could eliminate the need for some triggers.
In SQL Server 2000 you should also be able to retrieve multiple
IDENTITY values using an alternate key of the table. There should
always be another candidate key. If you don't have such a key then you
have a significant design flaw which you should fix.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thank you for an excellent answer, very informative.
Tony
"David Portas" wrote:
> Tony Scott wrote:
> SCOPE_IDENTITY does always have local scope but so does the INSERTED
> virtual table in a trigger. In that respect a trigger is no less
> precise in the way IDENTITY is retrieved - it's just that you have to
> allow for multiple-row inserts. Example:
> CREATE TABLE T1 (x INTEGER NOT NULL IDENTITY PRIMARY KEY, z INTEGER NOT
> NULL UNIQUE)
> GO
> CREATE TRIGGER trg ON T1 FOR INSERT
> AS
> SELECT SCOPE_IDENTITY() AS [scope_identity] ;
> SELECT @.@.IDENTITY AS [@.@.identity] ;
> SELECT IDENT_CURRENT('T1') AS [ident_current] ;
> GO
> INSERT INTO T1 (z)
> SELECT 1 UNION ALL
> SELECT 2 ;
> scope_identity
> ---
> NULL
> (1 row(s) affected)
> @.@.identity
> ---
> 2
> (1 row(s) affected)
> ident_current
> ---
> 2
> (1 row(s) affected)
>
> The first result (NULL) is wrong because SCOPE_IDENTITY has local scope
> so it doesn't see the INSERT at all.
> The second is wrong because although it returns one of the IDENTITY
> values there were actually two rows inserted. We can't predict the row
> for which the IDENTITY will be returned - it will just be the highest
> numbered IDENTITY value. This isn't good in a trigger because we can
> only use this method to reference a single row and triggers should
> always assume multiple rows may be updated.
> The third result may be wrong if other connections also update the
> table because IDENT_CURRENT is scoped to the table and not to the
> session.
> The reliable method uses the INSERTED table:
> CREATE TRIGGER trg ON T1 FOR INSERT
> AS
> SELECT x FROM inserted ;
> GO
> If your procs insert multiple rows you also need to think about the
> same issue in procs when you need the IDENTITY value. Don't assume the
> values inserted will be contiguous with the value returned by
> SCOPE_IDENTITY. There are at least some conditions where they may not
> be.
> In SQL Server 2005 you can address things slightly differently using
> the OUPUT clause of the INSERT, UPDATE and DELETE statements. These
> could eliminate the need for some triggers.
> In SQL Server 2000 you should also be able to retrieve multiple
> IDENTITY values using an alternate key of the table. There should
> always be another candidate key. If you don't have such a key then you
> have a significant design flaw which you should fix.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi,
Thanks for the reply David.
> Did you read about the differences between those functions in Books
> Online? Either may be appropriate depending on requirements.
Yes I did. And I also got the idea that they all might be appropiate. But no
quite understanding the exact process of a insert trigger (as you explained)
I was
as to what was the more correct way> BUT - a big but - they are all unlikely to be useful in a trigger.
> That's because well-written trigger code should always assume that more
> than one row may be updated in the table that invokes the trigger.
> Triggers fire once per statement, NOT once per row, so usually in a
> trigger if you want to retrieve the inserted IDENTITY values you do so
> by referencing the virtual table called INSERTED. That table could
> contain 0,1,2 or any number of rows.
At this point I am assumning a single row insert transaction. The table
involved is a claims table, needing a single input for each claim
as in:
INSERT INTO [RASRMIS].[dbo].[Claim]([divid], [dhid], [LayerID], [peril],
[cause], [resource], [fault], [DOL], [DREP])
VALUES(1812, 1237, 5, 1, 1, 1, 1, getdate()-1650, getdate())
Would this be considered as a single row insert, on a single statement,
single transaction
I then issued:
Select @.@.identity [@.@.Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()],
ident_current('claim') [ident_current()]
But got back all the same value.
But if I put the above statement into a trigger, I receive the dame values
from the @.@.identity and ident_current() functions. Scope_identity() returns
null. I would have thought that the insert trigger is within scope here
Hopefully I have explained correctly
Thankls
Robert|||Robert Bravery wrote:
> At this point I am assumning a single row insert transaction.
Why would you bother to assume that? It doesn't help you. It just means
the DBA will curse you one day when he needs to do some ad-hoc
maintenance... or integrate some external data... or the user needs an
enhancement to the application, or...
> The table
> involved is a claims table, needing a single input for each claim
> as in:
> INSERT INTO [RASRMIS].[dbo].[Claim]([divid], [dhid], [LayerID], [peril],
> [cause], [resource], [fault], [DOL], [DREP])
> VALUES(1812, 1237, 5, 1, 1, 1, 1, getdate()-1650, getdate())
> Would this be considered as a single row insert, on a single statement,
> single transaction
> I then issued:
> Select @.@.identity [@.@.Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()],
> ident_current('claim') [ident_current()]
> But got back all the same value.
> But if I put the above statement into a trigger, I receive the dame values
> from the @.@.identity and ident_current() functions. Scope_identity() return
s
> null. I would have thought that the insert trigger is within scope here
>
No. The trigger runs in its own scope.
The solution is:
SELECT id FROM inserted ;
"Inserted" is a virtual table only visible in triggers.
This isn't a good example because you don't usually want to return
results from triggers. The exact solution of course depends on what you
want to do with the IDENTITY value(s) after you retrieve them. For
example you can insert them to another table:
INSERT INTO other_table (id, ...)
SELECT id
FROM inserted ;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Unless you're using an INSTEAD OF INSERT trigger, then you shouldn't rely on
the value returned by @.@.IDENTITY; under no circumstances should you rely on
the value returned by IDENT_CURRENT()--at least not for what you're looking
for. There can be more than one FOR or AFTER INSERT trigger on a table, and
any one of them could affect @.@.IDENTITY. IDENT_CURRENT() changes whenever
any INSERT occurs on any connection, so it could change between the time
that you read it and the time that you're ready to use it, or even worse: it
could change during whatever modification you're trying to make. In a FOR
or AFTER trigger, the only reliable ways to retrieve the generated IDENTITY
values is to use the inserted pseudotable, or to use another candidate key.
An INSTEAD OF trigger fires instead of the action specified, so new IDENTITY
values haven't yet been generated at the time that the inserted pseudotable
is populated. You can use SCOPE_IDENTITY() if you use a cursor to process
the contents of the inserted pseudotable, but I would recommend against it.
Using cursors in triggers is like publishing uncomplimentary caricatures of
Mohammed: you're bound to get burned. In an INSTEAD OF INSERT trigger,
you're best bet is to use another candidate key to obtain the IDENTITY
values.
"Robert Bravery" <me@.u.com> wrote in message
news:u07T2i9KGHA.1424@.TK2MSFTNGP12.phx.gbl...
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
>
Sunday, March 11, 2012
Best practice for conditional insert else select?
I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.
So the table is essentially like:
CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));
CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);
I see two main approaches for how I can do my get-id-with-insert-if-needed.
(Approach 1)
DECLARE @.id INT;
SELECT @.id = id FROM MyLookup WHERE mykey = 'some key value';
IF (@.id is null)
BEGIN
INSERT MyLookup ('some key value');
SET @.id = SCOPE_IDENTITY();
END
(Approach 2)
DECLARE @.id INT;
INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');
IF (@.@.ROWCOUNT = 0)
SELECT @.id = id FROM MyLookup WHERE mykey = 'some key value';
ELSE
SET @.id = SCOPE_IDENTITY();
From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.
Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?
I went ahead and did some testing, no big surprises. Although the tests were single client, so it doesn't give me any info about locking.
I found unsurprisingly that the more sparse the table, the narrower the gap between the two options. But as the likelihood of needing an insert went down, the first approach became more effective. In my production environment I would hazard a guess that I need an insert around 5% of the time, so I'll probably go with approach 1 in general. Although I did change my approach 1 to actually incorporate approach 2 with in - first I do a select, and then if the id was null then I do a conditional insert. There is a slight increase in maintenance cost since I have to duplicate the code for the existential check, but I think it is worthwhile in my cases that are fairly high traffic.
Any other points of view on this?
Thursday, March 8, 2012
Best Practice Analyzer scan
I have a scan that is returning an exception.
The exception is on a trigger.
The trigger does an insert and refers to another object and I don't
have the object prefixed with a user/schema within the trigger. My
application is deliveried as static ddl and the customer chooses what
user (2000) or schema (2005) to install it into. How can I code the
trigger to include the user/schema name when it can be different for
each customer?
Here's the scan - One or more objects are refencing tables/views
without specifying a schema! Performance and predictability of the
application may be improved by specifying schema names.
Can you use suser_name to give the login id and go from there?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dunleav1" <jmd@.dunleavyenterprises.com> wrote in message
news:1161344508.902991.253770@.m73g2000cwd.googlegr oups.com...
>I have a scan that is returning an exception.
> The exception is on a trigger.
> The trigger does an insert and refers to another object and I don't
> have the object prefixed with a user/schema within the trigger. My
> application is deliveried as static ddl and the customer chooses what
> user (2000) or schema (2005) to install it into. How can I code the
> trigger to include the user/schema name when it can be different for
> each customer?
> Here's the scan - One or more objects are refencing tables/views
> without specifying a schema! Performance and predictability of the
> application may be improved by specifying schema names.
>
The exception is on a trigger.
The trigger does an insert and refers to another object and I don't
have the object prefixed with a user/schema within the trigger. My
application is deliveried as static ddl and the customer chooses what
user (2000) or schema (2005) to install it into. How can I code the
trigger to include the user/schema name when it can be different for
each customer?
Here's the scan - One or more objects are refencing tables/views
without specifying a schema! Performance and predictability of the
application may be improved by specifying schema names.
Can you use suser_name to give the login id and go from there?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dunleav1" <jmd@.dunleavyenterprises.com> wrote in message
news:1161344508.902991.253770@.m73g2000cwd.googlegr oups.com...
>I have a scan that is returning an exception.
> The exception is on a trigger.
> The trigger does an insert and refers to another object and I don't
> have the object prefixed with a user/schema within the trigger. My
> application is deliveried as static ddl and the customer chooses what
> user (2000) or schema (2005) to install it into. How can I code the
> trigger to include the user/schema name when it can be different for
> each customer?
> Here's the scan - One or more objects are refencing tables/views
> without specifying a schema! Performance and predictability of the
> application may be improved by specifying schema names.
>
Best Practice Analyzer scan
I have a scan that is returning an exception.
The exception is on a trigger.
The trigger does an insert and refers to another object and I don't
have the object prefixed with a user/schema within the trigger. My
application is deliveried as static ddl and the customer chooses what
user (2000) or schema (2005) to install it into. How can I code the
trigger to include the user/schema name when it can be different for
each customer?
Here's the scan - One or more objects are refencing tables/views
without specifying a schema! Performance and predictability of the
application may be improved by specifying schema names.Can you use suser_name to give the login id and go from there?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dunleav1" <jmd@.dunleavyenterprises.com> wrote in message
news:1161344508.902991.253770@.m73g2000cwd.googlegroups.com...
>I have a scan that is returning an exception.
> The exception is on a trigger.
> The trigger does an insert and refers to another object and I don't
> have the object prefixed with a user/schema within the trigger. My
> application is deliveried as static ddl and the customer chooses what
> user (2000) or schema (2005) to install it into. How can I code the
> trigger to include the user/schema name when it can be different for
> each customer?
> Here's the scan - One or more objects are refencing tables/views
> without specifying a schema! Performance and predictability of the
> application may be improved by specifying schema names.
>
The exception is on a trigger.
The trigger does an insert and refers to another object and I don't
have the object prefixed with a user/schema within the trigger. My
application is deliveried as static ddl and the customer chooses what
user (2000) or schema (2005) to install it into. How can I code the
trigger to include the user/schema name when it can be different for
each customer?
Here's the scan - One or more objects are refencing tables/views
without specifying a schema! Performance and predictability of the
application may be improved by specifying schema names.Can you use suser_name to give the login id and go from there?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dunleav1" <jmd@.dunleavyenterprises.com> wrote in message
news:1161344508.902991.253770@.m73g2000cwd.googlegroups.com...
>I have a scan that is returning an exception.
> The exception is on a trigger.
> The trigger does an insert and refers to another object and I don't
> have the object prefixed with a user/schema within the trigger. My
> application is deliveried as static ddl and the customer chooses what
> user (2000) or schema (2005) to install it into. How can I code the
> trigger to include the user/schema name when it can be different for
> each customer?
> Here's the scan - One or more objects are refencing tables/views
> without specifying a schema! Performance and predictability of the
> application may be improved by specifying schema names.
>
Best Practice Analyzer scan
I have a scan that is returning an exception.
The exception is on a trigger.
The trigger does an insert and refers to another object and I don't
have the object prefixed with a user/schema within the trigger. My
application is deliveried as static ddl and the customer chooses what
user (2000) or schema (2005) to install it into. How can I code the
trigger to include the user/schema name when it can be different for
each customer?
Here's the scan - One or more objects are refencing tables/views
without specifying a schema! Performance and predictability of the
application may be improved by specifying schema names.Can you use suser_name to give the login id and go from there?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dunleav1" <jmd@.dunleavyenterprises.com> wrote in message
news:1161344508.902991.253770@.m73g2000cwd.googlegroups.com...
>I have a scan that is returning an exception.
> The exception is on a trigger.
> The trigger does an insert and refers to another object and I don't
> have the object prefixed with a user/schema within the trigger. My
> application is deliveried as static ddl and the customer chooses what
> user (2000) or schema (2005) to install it into. How can I code the
> trigger to include the user/schema name when it can be different for
> each customer?
> Here's the scan - One or more objects are refencing tables/views
> without specifying a schema! Performance and predictability of the
> application may be improved by specifying schema names.
>
The exception is on a trigger.
The trigger does an insert and refers to another object and I don't
have the object prefixed with a user/schema within the trigger. My
application is deliveried as static ddl and the customer chooses what
user (2000) or schema (2005) to install it into. How can I code the
trigger to include the user/schema name when it can be different for
each customer?
Here's the scan - One or more objects are refencing tables/views
without specifying a schema! Performance and predictability of the
application may be improved by specifying schema names.Can you use suser_name to give the login id and go from there?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dunleav1" <jmd@.dunleavyenterprises.com> wrote in message
news:1161344508.902991.253770@.m73g2000cwd.googlegroups.com...
>I have a scan that is returning an exception.
> The exception is on a trigger.
> The trigger does an insert and refers to another object and I don't
> have the object prefixed with a user/schema within the trigger. My
> application is deliveried as static ddl and the customer chooses what
> user (2000) or schema (2005) to install it into. How can I code the
> trigger to include the user/schema name when it can be different for
> each customer?
> Here's the scan - One or more objects are refencing tables/views
> without specifying a schema! Performance and predictability of the
> application may be improved by specifying schema names.
>
Wednesday, March 7, 2012
Best Performance
I wanna know. I have 2 ways to copy data.
way I
INSERT into A
SELECT * from B
Way II
DECLARE CURSOR ... For
SELECT * from B
WHILE ... BEGIN
/* insert one by one to A
END
...
...
Which the best, way I or II? How much its speed comparison?I. is better. How much faster depends on many things; it could
be 2, 10, or 50 times as fast. Experiment and find out.
Steve Kass
Drew University
Bpk. Adi Wira Kusuma wrote:
>I wanna know. I have 2 ways to copy data.
>way I
>INSERT into A
>SELECT * from B
>Way II
>DECLARE CURSOR ... For
>SELECT * from B
>WHILE ... BEGIN
> /* insert one by one to A
>END
>...
>...
>Which the best, way I or II? How much its speed comparison?
>
>|||Hi
" I - Way" is best
rely on cursor if u dont find any other way to do things
(but there will be always another way to achieve things easily)
refer:http://www.sql-server-performance.com/cursors.asp
Regards
Magesh
"Bpk. Adi Wira Kusuma" wrote:
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
>|||Absolutely you should avoid using cursor
So What would I use a Cursor for?
Solution:
In Favour
Two reasons would be when you need to do something for each row in a
result set (usually execute a stored procedure), or when you must
process rows in a specific order.
The first case is probably most common in DBA/admin scripts, for
example to write a script which GRANTs execute permission on all stored
procs in a database
Cursors are mainly used to traverse reach row in the result of a query.
If u would like to check each row and perform a certain operation on
that, u use a cursor. For example u can check for hierarchical queries
in BOL
Most of the time you *can* conditionally perform an operation for each
row using a WHERE clause or using some combination of a WHERE clause
and CASE
expressions. Most of the time that is a better option than using a
cursor. The situations where you cannot do it with set-based DML
statements (SELECT, UPDATE, DELETE, INSERT) are usually to do with
iterative operations that cannot feasibly be defined declaratively -
for example the problem of expanding an adjacency list hierarchy to an
unknown depth. In those cases you may find that a cursor or client-side
code are the optimum solutions.
The other sensible application for a cursor is the - administrative and
management processes that need to execute non-data
manipulation code for each row in a set - for example performing
backups, loading files, sending emails. For data manipulation
operations, however, 99% of the time there are better solutions that
don't require cursors.
Cursors are a bad idea to use as they hold locks on tables for their
duration. They are also not necessary as you can use set rowcount 1 to
loop through records individually. or Use corelated Subquery
With Warm Regards
Jatinder Singh|||A cursor typically takes twice as long as a set-based select, and that's
only for reading. Inserting within the fetch loop will be several
magnitudes slower, because each separate insert forces all indexes to be
updated. A set-based insert allows SQL Server to optimize all of the index
updates.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:#23PK#LkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
way I
INSERT into A
SELECT * from B
Way II
DECLARE CURSOR ... For
SELECT * from B
WHILE ... BEGIN
/* insert one by one to A
END
...
...
Which the best, way I or II? How much its speed comparison?I. is better. How much faster depends on many things; it could
be 2, 10, or 50 times as fast. Experiment and find out.
Steve Kass
Drew University
Bpk. Adi Wira Kusuma wrote:
>I wanna know. I have 2 ways to copy data.
>way I
>INSERT into A
>SELECT * from B
>Way II
>DECLARE CURSOR ... For
>SELECT * from B
>WHILE ... BEGIN
> /* insert one by one to A
>END
>...
>...
>Which the best, way I or II? How much its speed comparison?
>
>|||Hi
" I - Way" is best
rely on cursor if u dont find any other way to do things
(but there will be always another way to achieve things easily)
refer:http://www.sql-server-performance.com/cursors.asp
Regards
Magesh
"Bpk. Adi Wira Kusuma" wrote:
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
>|||Absolutely you should avoid using cursor
So What would I use a Cursor for?
Solution:
In Favour
Two reasons would be when you need to do something for each row in a
result set (usually execute a stored procedure), or when you must
process rows in a specific order.
The first case is probably most common in DBA/admin scripts, for
example to write a script which GRANTs execute permission on all stored
procs in a database
Cursors are mainly used to traverse reach row in the result of a query.
If u would like to check each row and perform a certain operation on
that, u use a cursor. For example u can check for hierarchical queries
in BOL
Most of the time you *can* conditionally perform an operation for each
row using a WHERE clause or using some combination of a WHERE clause
and CASE
expressions. Most of the time that is a better option than using a
cursor. The situations where you cannot do it with set-based DML
statements (SELECT, UPDATE, DELETE, INSERT) are usually to do with
iterative operations that cannot feasibly be defined declaratively -
for example the problem of expanding an adjacency list hierarchy to an
unknown depth. In those cases you may find that a cursor or client-side
code are the optimum solutions.
The other sensible application for a cursor is the - administrative and
management processes that need to execute non-data
manipulation code for each row in a set - for example performing
backups, loading files, sending emails. For data manipulation
operations, however, 99% of the time there are better solutions that
don't require cursors.
Cursors are a bad idea to use as they hold locks on tables for their
duration. They are also not necessary as you can use set rowcount 1 to
loop through records individually. or Use corelated Subquery
With Warm Regards
Jatinder Singh|||A cursor typically takes twice as long as a set-based select, and that's
only for reading. Inserting within the fetch loop will be several
magnitudes slower, because each separate insert forces all indexes to be
updated. A set-based insert allows SQL Server to optimize all of the index
updates.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:#23PK#LkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
Best method for running several queries?
I have an SQL file saved from QA. It has several queries used for testing.
These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
specific statement to run in that file. This keeps everything from running
at once.
Problem is that I access the server from several computers via QA. The SQL
file with all of the above queries is usually on one computer. Should I
just store the SQL file on the SQL Server machine as an SQL file or a stored
procedure? What is best for this? This file isn't something I would ever
want an app to have access to. It's strictly for manual testing purposes
via QA.
Thanks,
BrettScript files are considered source code. So, you would want to put it in a
souce control server somewhere and just grab it when you need it. Storing
the script inside sqlserver is probably not a good idea in this case.
-oj
"Brett" <no@.spam.net> wrote in message
news:ORw4XLLLFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I have an SQL file saved from QA. It has several queries used for testing.
>These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
>specific statement to run in that file. This keeps everything from running
>at once.
> Problem is that I access the server from several computers via QA. The
> SQL file with all of the above queries is usually on one computer. Should
> I just store the SQL file on the SQL Server machine as an SQL file or a
> stored procedure? What is best for this? This file isn't something I
> would ever want an app to have access to. It's strictly for manual
> testing purposes via QA.
> Thanks,
> Brett
>
These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
specific statement to run in that file. This keeps everything from running
at once.
Problem is that I access the server from several computers via QA. The SQL
file with all of the above queries is usually on one computer. Should I
just store the SQL file on the SQL Server machine as an SQL file or a stored
procedure? What is best for this? This file isn't something I would ever
want an app to have access to. It's strictly for manual testing purposes
via QA.
Thanks,
BrettScript files are considered source code. So, you would want to put it in a
souce control server somewhere and just grab it when you need it. Storing
the script inside sqlserver is probably not a good idea in this case.
-oj
"Brett" <no@.spam.net> wrote in message
news:ORw4XLLLFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I have an SQL file saved from QA. It has several queries used for testing.
>These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
>specific statement to run in that file. This keeps everything from running
>at once.
> Problem is that I access the server from several computers via QA. The
> SQL file with all of the above queries is usually on one computer. Should
> I just store the SQL file on the SQL Server machine as an SQL file or a
> stored procedure? What is best for this? This file isn't something I
> would ever want an app to have access to. It's strictly for manual
> testing purposes via QA.
> Thanks,
> Brett
>
Saturday, February 25, 2012
Best image format to use?
I wan to insert our company logo on some of the srs reports, could someone
recommend the best format to use? I want the image to look clean when
printed as well.On May 14, 5:11 pm, Chris Patten
<ChrisPat...@.discussions.microsoft.com> wrote:
> I wan to insert our company logo on some of the srs reports, could someone
> recommend the best format to use? I want the image to look clean when
> printed as well.
Usually JPEG/JPG and GIF are the best to use due to smaller size (in
comparison to MPEG, etc).
Regards,
Enrique Martinez
Sr. Software Consultant|||I've had problems with gifs, especially on pdf exporting. jpeg seems
to work passably.
recommend the best format to use? I want the image to look clean when
printed as well.On May 14, 5:11 pm, Chris Patten
<ChrisPat...@.discussions.microsoft.com> wrote:
> I wan to insert our company logo on some of the srs reports, could someone
> recommend the best format to use? I want the image to look clean when
> printed as well.
Usually JPEG/JPG and GIF are the best to use due to smaller size (in
comparison to MPEG, etc).
Regards,
Enrique Martinez
Sr. Software Consultant|||I've had problems with gifs, especially on pdf exporting. jpeg seems
to work passably.
Friday, February 24, 2012
best bulk insert command
I have a 5 million row table that gets truncated and new values get imported
.
The new values are obtained by values that have changed in other tables.
Does anyone know the quickest way this can be acheived.
I have tried this took around 34mins
insert in attritable (attrivalue,attri_id,desc)
exec sp_insert
then
Theres a job that bcps the values out to text file in batches of 5000 ..then
inserts them into the table again in batches of 5000 and this takes around 2
8
mins.
Even though BCP is quicker it seems a waste to do it this way and more prone
to errors....Is BCP definately the quickest way to enter data this way does
anyone know'
Thanks for any help or suggestions
Sammy> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
Instead of returning a result set that you insert, consider changing
sp_insert to create the new table with SELECT ... INTO and then create
constraints and indexes.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
Bulk Insert methods like command-line BCP, Transact-SQL BULK INSERT, DTS and
bulk copy APIs are the fastest way to get external data into SQL Server.
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
This calculates to about 3000 rows per second. Not as fast as I would
expect with a narrow table on modern hardware (10,000+) but a lot depends
the size of your data and the kind of indexes you have on the table. You
may find it faster to drop indexes and recreate afterward. See Optimizing
Data Loads at
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx.[/url
]
Hope this helps.
Dan Guzman
SQL Server MVP
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:0B51C58B-7E10-4905-98CB-74CAA211622B@.microsoft.com...
>I have a 5 million row table that gets truncated and new values get
>imported.
> The new values are obtained by values that have changed in other tables.
>
> Does anyone know the quickest way this can be acheived.
> I have tried this took around 34mins
> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
> then
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
> Thanks for any help or suggestions
> Sammy
>
>
>
>
>
>
>
>
.
The new values are obtained by values that have changed in other tables.
Does anyone know the quickest way this can be acheived.
I have tried this took around 34mins
insert in attritable (attrivalue,attri_id,desc)
exec sp_insert
then
Theres a job that bcps the values out to text file in batches of 5000 ..then
inserts them into the table again in batches of 5000 and this takes around 2
8
mins.
Even though BCP is quicker it seems a waste to do it this way and more prone
to errors....Is BCP definately the quickest way to enter data this way does
anyone know'
Thanks for any help or suggestions
Sammy> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
Instead of returning a result set that you insert, consider changing
sp_insert to create the new table with SELECT ... INTO and then create
constraints and indexes.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
Bulk Insert methods like command-line BCP, Transact-SQL BULK INSERT, DTS and
bulk copy APIs are the fastest way to get external data into SQL Server.
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
This calculates to about 3000 rows per second. Not as fast as I would
expect with a narrow table on modern hardware (10,000+) but a lot depends
the size of your data and the kind of indexes you have on the table. You
may find it faster to drop indexes and recreate afterward. See Optimizing
Data Loads at
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx.[/url
]
Hope this helps.
Dan Guzman
SQL Server MVP
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:0B51C58B-7E10-4905-98CB-74CAA211622B@.microsoft.com...
>I have a 5 million row table that gets truncated and new values get
>imported.
> The new values are obtained by values that have changed in other tables.
>
> Does anyone know the quickest way this can be acheived.
> I have tried this took around 34mins
> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
> then
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
> Thanks for any help or suggestions
> Sammy
>
>
>
>
>
>
>
>
Best approach with DTS
Let me see if I can explain this.
I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.
Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.
Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.
Step 3
The returned rows here, need to be limited to key values returned from step 2
Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.
What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.
I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.
What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.
I'm open to ideas for alternatives
I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.
Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.
Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.
Step 3
The returned rows here, need to be limited to key values returned from step 2
Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.
What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.
I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.
What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.
I'm open to ideas for alternatives
Thursday, February 16, 2012
Beginning PL/SQL question
I'm writing a pl/sql program to insert the month number and number of days in that month into a table called Month_Days. I only want to use the Insert Into command once, and I also don't want to use a nested If statement. i can get the month number into month column using a loop and a counter going up to 13, but i don't know how to get the number of days into the days column. I'm assuming Jan has 31 days, Feb has 29, March has 31, Apr has 30, etc.
Here's what i have so far:
Create Table Month_Days(
Month Number(2)
Days Number(2));
Declare
LoopX Binary_Integer;
Begin
LoopX:=0;
Loop
LoopX:=LoopX+1;
If LoopX=13 Then
Exit;
End If;
Insert Into Month_Days Values (LoopX);
End Loop;
End;
Thanks in advance for any help!Hello,
the easiest way to get the lastnumber of a months is:
cDate VARCHAR2(20);
cLastDay VARCHAR2(2);
-- Build date
cDate := TO_CHAR(loopx) || '01' || '2003'
SELECT TO_CHAR(LAST_DAY(TO_DATE(cDate, 'MMDDYYYY')), 'DD')
INTO cLastDay FROM dual;
Hope that helps ?
If you want to use a PL/SQL editor try our product AlligatorSQL. It is very helpful ...
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
Here's what i have so far:
Create Table Month_Days(
Month Number(2)
Days Number(2));
Declare
LoopX Binary_Integer;
Begin
LoopX:=0;
Loop
LoopX:=LoopX+1;
If LoopX=13 Then
Exit;
End If;
Insert Into Month_Days Values (LoopX);
End Loop;
End;
Thanks in advance for any help!Hello,
the easiest way to get the lastnumber of a months is:
cDate VARCHAR2(20);
cLastDay VARCHAR2(2);
-- Build date
cDate := TO_CHAR(loopx) || '01' || '2003'
SELECT TO_CHAR(LAST_DAY(TO_DATE(cDate, 'MMDDYYYY')), 'DD')
INTO cLastDay FROM dual;
Hope that helps ?
If you want to use a PL/SQL editor try our product AlligatorSQL. It is very helpful ...
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
Sunday, February 12, 2012
Beginner @IDENTITY question
Hello,
When I execute the following code on my localhost the INSERT statement is successfull and my ID is entered. But for some reason when I uploaded to my remote host the exact same database (MS SQL) and code it keeps trying to enter a NULL value into the ID column- which causes an error? I don't understand becuase it is the exact same app and database but at a remote location.
When I execute the following code on my localhost the INSERT statement is successfull and my ID is entered. But for some reason when I uploaded to my remote host the exact same database (MS SQL) and code it keeps trying to enter a NULL value into the ID column- which causes an error? I don't understand becuase it is the exact same app and database but at a remote location.
Is @.IDENTITY somehow saved in the session and my remote servers session settings?? are off?
strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "') select @.ID = @.@.IDENTITY";
Thanks in advance for any responses.
-WileyHi Wiley,
First, you should have a ; between the statements:
strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "'); select @.ID = @.@.IDENTITY";
Second, you should probably be using SCOPE_IDENTITY instead of @.@.IDENTITY. The two are similar, but SCOPE_IDENTITY returns values inserted only within the current scope. The problem with @.@.IDENTITY is that you might get the ID value from another insert operation. Check out SQL Server Books Online for a more complete description of the issue.
But that doesn't explain your problem. My best guess is that the ID field isn't defined as an identity field in the database on the remote server. That's the first thing to check.
There is a slight chance that SET IDENTITY_INSERT is off in the database. I don't recall that you can set that as the normal setting, but something in the connection might be setting it. Definitely a longshot.
Third,DON'T USE DYNAMIC SQL THIS WAY!!!!! It opens up SQL injection attacks, particularly for fields that are obviously user input. If you don't know the issues, just ask. You are setting yourself up to have your app hacked and attacked.
Don|||Hello,
Part of my problem was that I created the database locally from a .dat file in a database called Resume. Everthing worked great. When I upoaded the tables to my remote assigned database which is called "mydatabase" not Resume I ran into permission problems becuase locally the owner was dbo and remotely the owner was "mydatabase". Also some of the ID fields were not set to IDENTITY.
I basically have my application working(sort of ) now but I have to allow NULLS on every column that allows them in every table for anything to (sort of ) work -which I DID NOT have to do locally and I can't figure out why, maybe someone can shed some light on that for me.
Also, in reference to SQL injection attacks that you mentioned above , maybe you can point me to a link on ms sql security and explain to me what is wrong with using the dynamic sql statement in the way i did. I definitely don't want to start using asp.net and start off on the wrong foot opening myself up for attacks.
thanks,
-Wiley|||there's a lot of info about sql inject that you can find on the web. some of them are:
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=533341
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
google rocks!|||wrong text on the first link. my mistake.
begin transactions
Hi I am a DBA and am having a dispute with a developer. He insists on
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
Kristina
KristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegro ups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>
|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegrou ps.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David
|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g2000cws.goo glegroups.com...
>
>
>
>
>
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedure
> or in the client code.
> David- Hide quoted text -- Show quoted text -
|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegr oups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
Kristina
KristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegro ups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>
|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegrou ps.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David
|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g2000cws.goo glegroups.com...
>
>
>
>
>
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedure
> or in the client code.
> David- Hide quoted text -- Show quoted text -
|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegr oups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David
begin transactions
Hi I am a DBA and am having a dispute with a developer. He insists on
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
KristinaKristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegroups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g20
00cws.googlegroups.com...
>
>
>
>
>
>
>
>
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedur
e
> or in the client code.
> David- Hide quoted text -- Show quoted text -|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
KristinaKristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegroups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g20
00cws.googlegroups.com...
>
>
>
>
>
>
>
>
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedur
e
> or in the client code.
> David- Hide quoted text -- Show quoted text -|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David
begin transactions
Hi I am a DBA and am having a dispute with a developer. He insists on
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
KristinaKristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> > Kristina
> >
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> > Kristina
> >
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegroups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>> KristinaDBA@.gmail.com wrote:
>> > Hi I am a DBA and am having a dispute with a developer. He insists on
>> > coding like this:
>> >
>> > Begin train
>> >
>> > 1 insert statment
>> >
>> > Check for error
>> >
>> > Commit or rollback.
>> >
>> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
>> > is to NOT put them in unless they are needed and at least 2 statements
>> > occur (insert, update, delete). I am assuming that these extra begin
>> > trans affect performance and the log some how. Can anyone help me with
>> > this. Again, this is the case when there is 1 update, delete, or
>> > insert.
>> >
>> > Thanks in advance.
>> > Kristina
>> >
>> I'd have to side with your developer on this one... You're "assuming"
>> that SQL will take care of the error handling for you. He's
>> GUARANTEEING that the errors will be handled in an expected fashion.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>
>
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> > Begin train
> > 1 insert statment
> > Check for error
> > Commit or rollback.
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.For a single DML statement, there is no need, but no real cost, to wrapping
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedure
> or in the client code.
> David- Hide quoted text -- Show quoted text -|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>>
>>
>> > Hi I am a DBA and am having a dispute with a developer. He insists on
>> > coding like this:
>> > Begin train
>> > 1 insert statment
>> > Check for error
>> > Commit or rollback.
>> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
>> > is to NOT put them in unless they are needed and at least 2 statements
>> > occur (insert, update, delete). I am assuming that these extra begin
>> > trans affect performance and the log some how. Can anyone help me with
>> > this. Again, this is the case when there is 1 update, delete, or
>> > insert.For a single DML statement, there is no need, but no real cost,
>> > to wrapping
>> it in an explicit transaction. It might generate an extra log record or
>> two, but nothing to worry about.
>> But, what if you want to enlist this procedure in a larger transaction?
>> I
>> am generally against explicit transaction handling in stored procedures.
>> While it's sometimes necessary and beneficial It's usually the wrong
>> scope
>> to knit together transactions and decide their fate. You only really
>> want
>> one level of transaction handling, either in the outermost stored
>> procedure
>> or in the client code.
>> David- Hide quoted text -- Show quoted text -
>|||Kalen,
I did read your reply. Can you give me a transact sql example of a
statement that won't roll back - like you mentioned a constraint
violation. I am not aware of this and would be happy to learn..
Thanks.
On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
> --
> HTH
> Kalen Delaney, SQL Server MVPhttp://sqlblog.com
> <Kristina...@.gmail.com> wrote in messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
>
> > Kalen,
> > I misspoke. What I mean to say is if there was an error, they would be
> > automically rolled back. - no need for begin trans. In the case of no
> > errors with a single statment, they would be automatically committed.
> > On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> > m...@.hotmail.com> wrote:
> >> <Kristina...@.gmail.com> wrote in
> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> >> > Hi I am a DBA and am having a dispute with a developer. He insists on
> >> > coding like this:
> >> > Begin train
> >> > 1 insert statment
> >> > Check for error
> >> > Commit or rollback.
> >> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> >> > is to NOT put them in unless they are needed and at least 2 statements
> >> > occur (insert, update, delete). I am assuming that these extra begin
> >> > trans affect performance and the log some how. Can anyone help me with
> >> > this. Again, this is the case when there is 1 update, delete, or
> >> > insert.For a single DML statement, there is no need, but no real cost,
> >> > to wrapping
> >> it in an explicit transaction. It might generate an extra log record or
> >> two, but nothing to worry about.
> >> But, what if you want to enlist this procedure in a larger transaction?
> >> I
> >> am generally against explicit transaction handling in stored procedures.
> >> While it's sometimes necessary and beneficial It's usually the wrong
> >> scope
> >> to knit together transactions and decide their fate. You only really
> >> want
> >> one level of transaction handling, either in the outermost stored
> >> procedure
> >> or in the client code.
> >> David- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David|||I apologize. I should really finish my coffee in the morning before become
insistent upon anything!
I was thinking of multiple statements where one failed but the rest
succeeded. But that of course would always have to have BEGIN TRAN.
One single statement will have a partial success. It will be all or nothing.
That being said, adding the BEGIN TRAN does not really add any extra
overhead, UNLESS you somehow forget to COMMIT or ROLLBACK and your
transaction ends up staying open for longer than necessary. Then there are
all kinds of ramifications.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165339226.543180.164360@.f1g2000cwa.googlegroups.com...
> Kalen,
> I did read your reply. Can you give me a transact sql example of a
> statement that won't roll back - like you mentioned a constraint
> violation. I am not aware of this and would be happy to learn..
> Thanks.
> On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> I understand that. Did you even read my reply?
>> I said that there are some errors that do NOT automatically rollback,
>> that
>> will commit even with the error. So the only way to roll them back is for
>> YOU or your code to catch them before the commit, and the only way to do
>> that is to have BEGIN TRAN.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVPhttp://sqlblog.com
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
>>
>> > Kalen,
>> > I misspoke. What I mean to say is if there was an error, they would be
>> > automically rolled back. - no need for begin trans. In the case of no
>> > errors with a single statment, they would be automatically committed.
>> > On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
>> > m...@.hotmail.com> wrote:
>> >> <Kristina...@.gmail.com> wrote in
>> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>> >> > Hi I am a DBA and am having a dispute with a developer. He insists
>> >> > on
>> >> > coding like this:
>> >> > Begin train
>> >> > 1 insert statment
>> >> > Check for error
>> >> > Commit or rollback.
>> >> > Since SQL Server 2000 an d 2005 has implicit transactions, my
>> >> > standard
>> >> > is to NOT put them in unless they are needed and at least 2
>> >> > statements
>> >> > occur (insert, update, delete). I am assuming that these extra begin
>> >> > trans affect performance and the log some how. Can anyone help me
>> >> > with
>> >> > this. Again, this is the case when there is 1 update, delete, or
>> >> > insert.For a single DML statement, there is no need, but no real
>> >> > cost,
>> >> > to wrapping
>> >> it in an explicit transaction. It might generate an extra log record
>> >> or
>> >> two, but nothing to worry about.
>> >> But, what if you want to enlist this procedure in a larger
>> >> transaction?
>> >> I
>> >> am generally against explicit transaction handling in stored
>> >> procedures.
>> >> While it's sometimes necessary and beneficial It's usually the wrong
>> >> scope
>> >> to knit together transactions and decide their fate. You only really
>> >> want
>> >> one level of transaction handling, either in the outermost stored
>> >> procedure
>> >> or in the client code.
>> >> David- Hide quoted text -- Show quoted text -- Hide quoted text --
>> >> Show quoted text -
>|||KristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
Kristina,
I did a quick benchmark:
CREATE TABLE aa(i INT)
go
CREATE PROCEDURE Test1
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
END
END
go
CREATE PROCEDURE Test2
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
BEGIN TRANSACTION
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
IF @.@.ERROR<>0 BEGIN
ROLLBACK
END ELSE BEGIN
COMMIT
END
END
END
go
Test1
go
Test1
/*
Profiler results:
CPU:406
Reads: 10720
Writes: 104
Duration: 2406
*/
go
Test2
go
Test2
/*
Profiler results:
CPU:373
Reads: 10844
Writes: 116
Duration: 2406
*/
go
DROP PROCEDURE Test1
go
DROP PROCEDURE Test2
go
DROP TABLE aa
go
I ran them 3 times and I did not notice any significant differences in
neither of 4 counters.
I do not know anything about your environment, but I would not worry
about performance penalties of BEGIN TRAN in mine.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thanks Alex,
That definitely clarifies the issue!
Also, Kalen - glad you are not *mad* any more...
This is a great group, I am going to stick with it. Today was my first
day checking it out :).
Kristina.
Alex Kuznetsov wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> Kristina,
> I did a quick benchmark:
> CREATE TABLE aa(i INT)
> go
> CREATE PROCEDURE Test1
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> END
> END
> go
> CREATE PROCEDURE Test2
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> BEGIN TRANSACTION
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> IF @.@.ERROR<>0 BEGIN
> ROLLBACK
> END ELSE BEGIN
> COMMIT
> END
> END
> END
> go
> Test1
> go
> Test1
> /*
> Profiler results:
> CPU:406
> Reads: 10720
> Writes: 104
> Duration: 2406
> */
> go
> Test2
> go
> Test2
> /*
> Profiler results:
> CPU:373
> Reads: 10844
> Writes: 116
> Duration: 2406
> */
> go
> DROP PROCEDURE Test1
> go
> DROP PROCEDURE Test2
> go
> DROP TABLE aa
> go
> I ran them 3 times and I did not notice any significant differences in
> neither of 4 counters.
> I do not know anything about your environment, but I would not worry
> about performance penalties of BEGIN TRAN in mine.
> --
> Alex Kuznetsov
> http://sqlserver-tips.blogspot.com/
> http://sqlserver-puzzles.blogspot.com/
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
KristinaKristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> > Kristina
> >
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> > Kristina
> >
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegroups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>> KristinaDBA@.gmail.com wrote:
>> > Hi I am a DBA and am having a dispute with a developer. He insists on
>> > coding like this:
>> >
>> > Begin train
>> >
>> > 1 insert statment
>> >
>> > Check for error
>> >
>> > Commit or rollback.
>> >
>> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
>> > is to NOT put them in unless they are needed and at least 2 statements
>> > occur (insert, update, delete). I am assuming that these extra begin
>> > trans affect performance and the log some how. Can anyone help me with
>> > this. Again, this is the case when there is 1 update, delete, or
>> > insert.
>> >
>> > Thanks in advance.
>> > Kristina
>> >
>> I'd have to side with your developer on this one... You're "assuming"
>> that SQL will take care of the error handling for you. He's
>> GUARANTEEING that the errors will be handled in an expected fashion.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>
>
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> > Begin train
> > 1 insert statment
> > Check for error
> > Commit or rollback.
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.For a single DML statement, there is no need, but no real cost, to wrapping
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedure
> or in the client code.
> David- Hide quoted text -- Show quoted text -|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>>
>>
>> > Hi I am a DBA and am having a dispute with a developer. He insists on
>> > coding like this:
>> > Begin train
>> > 1 insert statment
>> > Check for error
>> > Commit or rollback.
>> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
>> > is to NOT put them in unless they are needed and at least 2 statements
>> > occur (insert, update, delete). I am assuming that these extra begin
>> > trans affect performance and the log some how. Can anyone help me with
>> > this. Again, this is the case when there is 1 update, delete, or
>> > insert.For a single DML statement, there is no need, but no real cost,
>> > to wrapping
>> it in an explicit transaction. It might generate an extra log record or
>> two, but nothing to worry about.
>> But, what if you want to enlist this procedure in a larger transaction?
>> I
>> am generally against explicit transaction handling in stored procedures.
>> While it's sometimes necessary and beneficial It's usually the wrong
>> scope
>> to knit together transactions and decide their fate. You only really
>> want
>> one level of transaction handling, either in the outermost stored
>> procedure
>> or in the client code.
>> David- Hide quoted text -- Show quoted text -
>|||Kalen,
I did read your reply. Can you give me a transact sql example of a
statement that won't roll back - like you mentioned a constraint
violation. I am not aware of this and would be happy to learn..
Thanks.
On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
> --
> HTH
> Kalen Delaney, SQL Server MVPhttp://sqlblog.com
> <Kristina...@.gmail.com> wrote in messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
>
> > Kalen,
> > I misspoke. What I mean to say is if there was an error, they would be
> > automically rolled back. - no need for begin trans. In the case of no
> > errors with a single statment, they would be automatically committed.
> > On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> > m...@.hotmail.com> wrote:
> >> <Kristina...@.gmail.com> wrote in
> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> >> > Hi I am a DBA and am having a dispute with a developer. He insists on
> >> > coding like this:
> >> > Begin train
> >> > 1 insert statment
> >> > Check for error
> >> > Commit or rollback.
> >> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> >> > is to NOT put them in unless they are needed and at least 2 statements
> >> > occur (insert, update, delete). I am assuming that these extra begin
> >> > trans affect performance and the log some how. Can anyone help me with
> >> > this. Again, this is the case when there is 1 update, delete, or
> >> > insert.For a single DML statement, there is no need, but no real cost,
> >> > to wrapping
> >> it in an explicit transaction. It might generate an extra log record or
> >> two, but nothing to worry about.
> >> But, what if you want to enlist this procedure in a larger transaction?
> >> I
> >> am generally against explicit transaction handling in stored procedures.
> >> While it's sometimes necessary and beneficial It's usually the wrong
> >> scope
> >> to knit together transactions and decide their fate. You only really
> >> want
> >> one level of transaction handling, either in the outermost stored
> >> procedure
> >> or in the client code.
> >> David- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David|||I apologize. I should really finish my coffee in the morning before become
insistent upon anything!
I was thinking of multiple statements where one failed but the rest
succeeded. But that of course would always have to have BEGIN TRAN.
One single statement will have a partial success. It will be all or nothing.
That being said, adding the BEGIN TRAN does not really add any extra
overhead, UNLESS you somehow forget to COMMIT or ROLLBACK and your
transaction ends up staying open for longer than necessary. Then there are
all kinds of ramifications.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165339226.543180.164360@.f1g2000cwa.googlegroups.com...
> Kalen,
> I did read your reply. Can you give me a transact sql example of a
> statement that won't roll back - like you mentioned a constraint
> violation. I am not aware of this and would be happy to learn..
> Thanks.
> On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> I understand that. Did you even read my reply?
>> I said that there are some errors that do NOT automatically rollback,
>> that
>> will commit even with the error. So the only way to roll them back is for
>> YOU or your code to catch them before the commit, and the only way to do
>> that is to have BEGIN TRAN.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVPhttp://sqlblog.com
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
>>
>> > Kalen,
>> > I misspoke. What I mean to say is if there was an error, they would be
>> > automically rolled back. - no need for begin trans. In the case of no
>> > errors with a single statment, they would be automatically committed.
>> > On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
>> > m...@.hotmail.com> wrote:
>> >> <Kristina...@.gmail.com> wrote in
>> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>> >> > Hi I am a DBA and am having a dispute with a developer. He insists
>> >> > on
>> >> > coding like this:
>> >> > Begin train
>> >> > 1 insert statment
>> >> > Check for error
>> >> > Commit or rollback.
>> >> > Since SQL Server 2000 an d 2005 has implicit transactions, my
>> >> > standard
>> >> > is to NOT put them in unless they are needed and at least 2
>> >> > statements
>> >> > occur (insert, update, delete). I am assuming that these extra begin
>> >> > trans affect performance and the log some how. Can anyone help me
>> >> > with
>> >> > this. Again, this is the case when there is 1 update, delete, or
>> >> > insert.For a single DML statement, there is no need, but no real
>> >> > cost,
>> >> > to wrapping
>> >> it in an explicit transaction. It might generate an extra log record
>> >> or
>> >> two, but nothing to worry about.
>> >> But, what if you want to enlist this procedure in a larger
>> >> transaction?
>> >> I
>> >> am generally against explicit transaction handling in stored
>> >> procedures.
>> >> While it's sometimes necessary and beneficial It's usually the wrong
>> >> scope
>> >> to knit together transactions and decide their fate. You only really
>> >> want
>> >> one level of transaction handling, either in the outermost stored
>> >> procedure
>> >> or in the client code.
>> >> David- Hide quoted text -- Show quoted text -- Hide quoted text --
>> >> Show quoted text -
>|||KristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
Kristina,
I did a quick benchmark:
CREATE TABLE aa(i INT)
go
CREATE PROCEDURE Test1
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
END
END
go
CREATE PROCEDURE Test2
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
BEGIN TRANSACTION
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
IF @.@.ERROR<>0 BEGIN
ROLLBACK
END ELSE BEGIN
COMMIT
END
END
END
go
Test1
go
Test1
/*
Profiler results:
CPU:406
Reads: 10720
Writes: 104
Duration: 2406
*/
go
Test2
go
Test2
/*
Profiler results:
CPU:373
Reads: 10844
Writes: 116
Duration: 2406
*/
go
DROP PROCEDURE Test1
go
DROP PROCEDURE Test2
go
DROP TABLE aa
go
I ran them 3 times and I did not notice any significant differences in
neither of 4 counters.
I do not know anything about your environment, but I would not worry
about performance penalties of BEGIN TRAN in mine.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thanks Alex,
That definitely clarifies the issue!
Also, Kalen - glad you are not *mad* any more...
This is a great group, I am going to stick with it. Today was my first
day checking it out :).
Kristina.
Alex Kuznetsov wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> Kristina,
> I did a quick benchmark:
> CREATE TABLE aa(i INT)
> go
> CREATE PROCEDURE Test1
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> END
> END
> go
> CREATE PROCEDURE Test2
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> BEGIN TRANSACTION
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> IF @.@.ERROR<>0 BEGIN
> ROLLBACK
> END ELSE BEGIN
> COMMIT
> END
> END
> END
> go
> Test1
> go
> Test1
> /*
> Profiler results:
> CPU:406
> Reads: 10720
> Writes: 104
> Duration: 2406
> */
> go
> Test2
> go
> Test2
> /*
> Profiler results:
> CPU:373
> Reads: 10844
> Writes: 116
> Duration: 2406
> */
> go
> DROP PROCEDURE Test1
> go
> DROP PROCEDURE Test2
> go
> DROP TABLE aa
> go
> I ran them 3 times and I did not notice any significant differences in
> neither of 4 counters.
> I do not know anything about your environment, but I would not worry
> about performance penalties of BEGIN TRAN in mine.
> --
> Alex Kuznetsov
> http://sqlserver-tips.blogspot.com/
> http://sqlserver-puzzles.blogspot.com/
Subscribe to:
Posts (Atom)