Best Practice Analyzer & Custom Best Practices

Hi All,
The BPA is a great tool that can be used to audit an environment for
adherenace to proper configuration and basic T-SQL coding best practices.
Does anyone know if the BPA tool can be customized to include custom
best-practice rules that are in-addition to the standard best practices
included with the tool? For example, say for example if an orgainzation
would like to add custom rules that look for the existance of specific
UserID, specific SQLAgent Jobs, etc.
Thanks in advance for your assistance.
Dave
This is a feature request that the SQL team are well aware of but as you can
imagine with SQL2005 nearing the end of a long road (near being a relative
term!) it might be a while before a version of SQLBPA is released that
supports user defined rules.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:405E1559-6CB7-4371-822B-CE4C523A5EF3@.microsoft.com...
> Hi All,
> The BPA is a great tool that can be used to audit an environment for
> adherenace to proper configuration and basic T-SQL coding best practices.
> Does anyone know if the BPA tool can be customized to include custom
> best-practice rules that are in-addition to the standard best practices
> included with the tool? For example, say for example if an orgainzation
> would like to add custom rules that look for the existance of specific
> UserID, specific SQLAgent Jobs, etc.
> Thanks in advance for your assistance.
> Dave

Beginners question to coding

ok this is for a class assignment so if anyone doesnt want to help thats cool, but its just a small extra credit assignment and we havent gone over it in class and and the book, well is confusing to say the least so here are my questions:

What is the total weight of different colored parts?

ok so i have a parts table, with pcolor and pweight in it but i am unsure how to code that, here is kinda what i think it might be, i have no real way to check to see if it is correct though:

Select sum pweight
from parts
where distinct pcolor

looks horribly wrong so any help is appreciated. here is another one:

What colored part has total weight greater than 8 units?

select pcolor
from parts
where sum pweight > 8

?
i dunno lol.

there is a hint that says to use "GROUP BY" and "HAVING" but i dont see how that fits in... any ideas?I'll put the keywords in the proper order; you figure out the rest:

SELECT
FROM
WHERE
GROUP BY
HAVING

Notes:
1. I don't think you need the WHERE clause in this particular exercise at all.
2. You won't need HAVING in the first part

HAVING was just so totally cool when I first learned how to use it. It is enormously useful at times.

Regards,

hmscott|||This is a super intro to SQL - you will figure out your answers in no time:
http://www.w3schools.com/sql/sql_intro.asp

I'll give you another hint - no need for distinct.

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/