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

Beginner T-SQL Question

I'd like to query my Tasks and TaskActivity Tables to return the following results:

Case Id
Estimated Hours
Total Hours
Remaining Hours

Because I'm an SQL novice, I've had to resort to a hack of creating three different views to get the results I want: I'm sure there's a better way (i.e. a single, well-formed query) but when I try that my numbers are all wrong.

Here's my hack to get the correct results:

First I have a view to get my Estimated Hours and CaseId from my TASKS table:
SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId
FROM dbo.Tasks
GROUP BY CaseId

Then I have a view to get my Total Hours and CaseId from my TASKACTIVITES table:
SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId

Finally I have a third view (that I actually use in my program) to put it all together:
SELECT vTaskActual.CaseId, vTaskEstimates.EstHrs, vTaskActual.TotalHrs,
vTaskActual.TotalHrs - vTaskEstimates.EstHrs AS RemHrs
FROM vTaskActual INNER JOIN
vTaskEstimates ON vTaskActual.CaseId = vTaskEstimates.CaseId

Any help would be greatly appreciated, and, if this is not the correct forum for newbie questions like this, please let me know.

TIA,
Rob

You can turn a query containing a single select statement into a derived table by surround it in parethesises and adding an alias. eg

select managers.name, managers.SSN, dept.departmentCode, dept.description

from department as dept

join (select name, SSN, deptid from employee where salary > 50.000) as managers

on dept.deptid = managers.deptid

So with the two views above, turn both into derived tables, and join:

select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,
TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs

from

(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId
) as TaskEstimates

INNER JOIN

(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId) as TaskActual

ON TaskActual.CaseId = TaskEstimates.CaseId

That should do it. As both derived table queries use the tasks table, it is likely that you can do the query in one query without using derived tables, but without having a better understanding of your schema and data I don't want to attempt that!

HTH

For more SQl tips, check out my blog:

|||Please indicate if this answered your question or not.|||

Thanks for the reply it was very helpful.

I did have to make a few changes to your response (see below) because my results are gathered from two different tables: Tasks contains the estimate, and TaskActivity contains the records of actual time. The info on derived tables will really help clean-up my DB.

Thanks again!

P.S. I use "COALESCE" to assign 0.00 to any NULL values in EstHrs. It is working so I assume that is correct)

Here's the final query that's working:

select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,

TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs

from

(SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId

FROM dbo.Tasks

GROUP BY CaseId

) as TaskEstimates

INNER JOIN

(SELECT dbo.Tasks.CaseId, SUM(dbo.TaskActivity.Minutes / 60.00) AS TotalHrs

FROM dbo.TaskActivity RIGHT OUTER JOIN

dbo.Tasks ON dbo.TaskActivity.TaskId = dbo.Tasks.TaskId

GROUP BY dbo.Tasks.CaseId) as TaskActual

ON TaskActual.CaseId = TaskEstimates.CaseId

beginner student PL/SQL to T-SQL

This summary is not available. Please click here to view the post.

Beginner prob: generating string

Help!

I'm new to T-SQL and trying to code a solve for the following problem, without a good idea of how I can make T-SQL jump through hoops. Here is the problem.

I need to populate a single string with values from a series of possible fields.

eg. a vehicle can be 4 wheel drive (4WD) and have air conditioning (Aircon).

So for each stock record I want to test each of a range of values (all or none can be true) in a column for a match. Based on that, add to the output string (that would look like "4wd ac ab abs").

So far I have :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE proc [dbo].[qry_EXPERIMENT_02-12-2003]

--provides information for the 'notes' field of the results screen.
--default value: NULL
@.notesString nvarchar (30) = NULL

as

SELECT
tblStockExtra.StockID,

(
SELECT
CASE WHEN tblExtra.ExtraHeader = '4WD' THEN '4WD ' END,
CASE WHEN tblExtra.ExtraHeader = 'Aachk' THEN 'aa ' END,
CASE WHEN tblExtra.ExtraHeader = 'Aircon' OR tblExtra.ExtraHeader = 'DualAircon' OR tblExtra.ExtraHeader = 'ClimateAirCon' THEN 'ac ' END,
CASE WHEN tblExtra.ExtraHeader = 'ABS' THEN 'abs ' END,
CASE WHEN tblExtra.ExtraHeader = 'AlloyWheel' OR tblExtra.ExtraHeader = 'FactoryAlloyWheel ' THEN 'aw ' END,
CASE WHEN tblExtra.ExtraHeader = 'SRSAirBag' OR tblExtra.ExtraHeader = 'DualSRSAirBag' THEN 'ab ' END,
CASE WHEN tblExtra.ExtraHeader = 'CD' OR tblExtra.ExtraHeader = 'CDShuttle' THEN 'cd ' END

FROM tblExtra
INNER JOIN tblStockExtra ON tblExtra.ExtraID = tblStockExtra.ExtraID
INNER JOIN tblStock ON tblStockExtra.StockID = tblStock.StockID
) AS notes

FROM tblStockExtra, tblExtra

GROUP BY tblStockExtra.StockID, tblExtra.Extraheader

ORDER BY tblStockExtra.StockID

This doesn't work, as I assume a subquery can only return 1 value. I'm hoping someone knowledgable can see what I'm attempting to do and enlighten me!

Do I need to be using Dynamic SQL?

Many thanks for your thoughts and time,

KC.You are right, this will not work. It would be easier for us if you post :

a) table descriptions of tblStock, tblExtra, tblStockExtra
b) the foreign keys between those tables (that way we can have an idea about the relationships between them)
c) some sample data
d) an example of how you would like to output to be formatted|||Originally posted by cvandemaele
You are right, this will not work. It would be easier for us if you post :

a) table descriptions of tblStock, tblExtra, tblStockExtra
b) the foreign keys between those tables (that way we can have an idea about the relationships between them)
c) some sample data
d) an example of how you would like to output to be formatted

A) Many to many realtionship between tblStock and tblExtra with tblStockExtra as a bridging table
B) Referential integrity not enforced (Don't ask! Its not mine :) )
C)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblExtra]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStock]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStockExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStockExtra]
GO

CREATE TABLE [dbo].[tblExtra] (
[ExtraID] [int] NOT NULL ,
[ExtraDesc] [nvarchar] (255) NULL ,
[ExtraAbbrv] [nvarchar] (255) NULL ,
[ExtraHeader] [nvarchar] (50) NULL ,
[Group] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStock] (
[StockID] [int] NOT NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStockExtra] (
[StockExtraID] [int] IDENTITY (1, 1) NOT NULL ,
[StockID] [int] NULL ,
[ExtraID] [int] NULL
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[tblStockExtra] ON
GO

ALTER TABLE [dbo].[tblStockExtra] WITH NOCHECK ADD
CONSTRAINT [PK_tblStockExtra] PRIMARY KEY NONCLUSTERED
(
[StockExtraID]
) ON [PRIMARY]
GO

INSERT INTO tblStock
(StockID)
VALUES (64);
INSERT
INTO tblStock(StockID)
VALUES (65);
INSERT
INTO tblStock(StockID)
VALUES (467);
INSERT
INTO tblStock(StockID)
VALUES (487);
INSERT
INTO tblStock(StockID)
VALUES (944);

--4wd
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (41,'4 wheel drive', '4WD', '4WD', 14)

--aachk
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (52, 'AA check', 'AA', 'Aachk', 17)

--aircon
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (8, 'air-conditioning', 'AC', 'AirCon', 2)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (9, 'climate air-conditioning', 'AAC', 'ClimateAirCon', 2)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (10, 'dual air condtioning', 'DAC', 'DualAirCon', 2)

--abs
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (39, 'antilock braking system', 'ABS', 'ABS', 12)

--alloys
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (27, 'alloy wheels', 'AW', 'AlloyWheel', 9)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (28, 'factory alloy wheels', 'FAW', 'FactoryAlloyWheel', 9)

--airbags
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (25, 'SRS airbag', 'AB', 'SRSAirBag', 8)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (26, 'dual SRS airbag', 'DAB', 'DualSRSAirBag', 8)

--CD
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (13, 'CD', 'CD', 'CD', 3)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (14, 'CD shuttle', 'CDS', 'CDShuttle', 3)

INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1112,64,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1113,64,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1115,64,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1116,64,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1117,64,60)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1118,64,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1118,64,59)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1119,65,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1120,65,39)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1121,65,14)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1122,65,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1123,65,26)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1124,65,16)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1125,65,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1126,65,43)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1127,65,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1128,65,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4357,467,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4358,467,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4359,467,11)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4465,487,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4466,487,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4467,487,11)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4468,487,67)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9188,944,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9189,944,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9190,944,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9191,944,16)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9192,944,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9193,944,20)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9194,944,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9195,944,28)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9196,944,64)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9197,944,41)

D) I want the output as: "Extra1 Extra2 Extra3"

Thanks for your help!

KC.|||As the list of extras linked to an item in StockId is unlimited, the list of extras is returned as a large string, seperated by a single character.

First of all, create the following function

create function dbo.fGetOptionList (@.CarId int, @.Separator char(1))
returns nvarchar(510)
as
begin

-- cursor that will browse throught the list of extra for the given car
declare cOptionList
cursor for
select
options.extraabbrv as OptionAbbrv
from tblstock as cars
inner join tblstockextra as cars_options
on cars.stockid = cars_options.stockid
inner join tblextra as options
on options.extraid = cars_options.extraid
where Cars.stockid = @.CarId

declare @.OptionAbbrv nvarchar(510)
declare @.AllOptions nvarchar(510)

open cOptionList
fetch next from cOptionList into @.OptionAbbrv
while @.@.fetch_status = 0
begin
set @.AllOptions = rtrim(isnull(@.AllOptions,'')) + rtrim(isnull(@.OptionAbbrv,'')) + @.Separator
fetch next from cOptionList into @.OptionAbbrv
end
close cOptionList
deallocate cOptionList

return @.AllOptions

end

Afterwards, just :
SELECT
stockid,
dbo.fgetoptionlist(stockid, ';') as OptionList
FROM tblstock

It will return :
stockid OptionList
---- -----
64 AC;
65 AC;ABS;CDS;DAB;
467 AC;
487 AC;
944 AC;FAW;4WD;

(5 row(s) affected)|||Wow!

Thanks for your time and energy! I truly wasn't expecting the solution on a plate! Please accept my sincerest thanks, you've saved me a great deal of effort!

I feel disappointed I can't reward you with a commendation as in other forums, but you've made my day!

:) :) :)

KC.