Best practices: GROUP BY clause

I was wondering what the best way to write a GROUP BY clause when there are many (and time consuming) operations in the fields by grouped.

Fictious example:

SELECT DeptNo, AVG(Salary) FROM Department GROUP BY DeptNo;

This will give me the average salary per department. Let's say, however that
I had 10-15 fields being returned (along with the AVG(Salary)) and some fields even had operations being performed on them. Is it better to create a temporary table to calculate the sum per department (or a VIEW) and then
perform a JOIN with the rest of the data?

Fictious example:

SELECT DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField), AVG(Salary)
GROUP BY DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField);

Am I better off writing my query this way or using a JOIN on some temporary table or view?

ThanksWrite your query this way. One way to maximize the efficiency of a process is to reduce the number of times the server has to scan through the data. By putting all your aggregate functions in a single statement, the server only needs to run through the dataset one time.

But are all those datename and datepart functions necessary? That seems kind of wastefull. You could accomplish the same thing just by sorting by date.|||Bindman...That was just an example I made up. I am not asking this for a particular case right now, but I have in the past had queries that had many fields, and many of those fields had math/string/etc functions performed on them. Most of the time This was to provide formatting for a query that would be dumped into a report or out put to the user. For example, I might format an ID by Left padding with zeroes:
RIGHT(REPLICATE(MyPadChar, MyFieldWidth) + CAST(MyID AS VARCHAR), MyFieldWidth) AS [MyFormattedID]......

so all those fields would appear in my group by clause....I was wondering if this was a good practice.

Thanks|||It's acceptable in my opinion.

Anybody else want to comment on this?|||SELECT * FROM (
SELECT DATENAME(y, StartDate) AS Col1
, DATENAME(m, StartDate) AS Col2
, DATEPART(d, StartDate) AS Col3
, SUBSTRING(DeptName, 1, 10) AS Col4
, SomeFunction(SomeField) AS Col5
, SomeFunction(SomeField) AS Col6
, AVG(Salary) AS Col7
FROM myTable99) AS XXX
GROUP BY Col1, Col2, Col3, Col4, Col5, Col6|||Yeah, I thought about suggesting that. I've used it for clarity of coding before, but can you think of any reason it might or might not be more efficient? I guess the question is, when you include a formula in the output and also specify it in the GROUP BY clause, does the server calculate the formula twice, or is it smart enough to just calculate it once?|||Kaiser. Thanks for the hint...that'll clean things up a whole lot...
As for blindman, yeah, I'd like to know if evaluation takes place twice when the quesy is run.|||I guess the question is, when you include a formula in the output and also specify it in the GROUP BY clause, does the server calculate the formula twice, or is it smart enough to just calculate it once?

It had better be once, since it's a derived table...I never checked, but a SHOWPLAN should tell you what's up.

But again, this is M$, so you never know...

But since I'm a betting kinda guy...

$US1000.00 on Once to Win....|||Kaiser. Thanks for the hint....

Your welcome....Kaiser? Try Brett, x002548, or Ski (get it...)

Where are you in the world?|||Then would you agree that your subquery example would not be more efficient than coding formulas in the WHERE clause, though it scores points for clarity?|||No...would you agree that using formulas would cause non sargable predicates there by invalidating the use of any index?|||Yes, but the use of indexes is lost anyway when you filter on the results of forumulas in the subquery. I don't see how either of these methods would make efficient use of indexes.|||In the subquery, Query Analyser still complains about the subquery you mentioned. If it sees an aggregate function with other fields, you have to GROUP BY the other fields.
Sorry, I read it too fast the first time. I thought I saw:

SELECT ClientID AS Col1, OrderID AS Col2, SUM(Price) AS Col3
GROUP BY Col1, Col2;

I did not see the subquery and thought that what you gave me was similiar to
the ORDER BY 1, 2, 3... clause (instead of using actual column names)|||scores points for clarity? are you talking about post #5?

yes, assuming all the errors were fixed up!|||Yeah, I meant post #5. (the thread is getting a little long).

Um, the subquery won't run because of the aggregate missing a GROUP BY clause.

I also couldn't get the group clause to work on a column alias...|||select col1, col2, col3, col4, col5, col6, col7
from (
select datename(y, startdate) as col1
, datename(m, startdate) as col2
, datepart(d, startdate) as col3
, substring(deptname, 1, 10) as col4
, somefunction(somefield) as col5
, somefunction(somefield) as col6
, avg(salary) as col7
from mytable99
group
by datename(y, startdate)
, datename(m, startdate)
, datepart(d, startdate)
, substring(deptname, 1, 10)
, somefunction(somefield)
, somefunction(somefield)
) as xxx

Best practice for writing own system procedures

Hello,

I'm searching for a best practice or other documentation for writing my own 'system procedure'.
I want to write procs which I can call in the context of every database without using a database name analogous to sp_who for example.
I read about the 'Resource Database'. All system procedures are stored in that readonly database and appear logically in the sys schema of every database.
But I couldn't find documentation about writing my own 'system procedure'.
I discover so far that procs with prefix sp_ stored in the master database do what I want. But is that the only way or is there a better way to do it?
In other threads I read that it is recommended not to use sp_ as prefix for procedures

Wolfgang?

It's not recommended to use the sp_ prefix because there is a slight performance hit if you use it for user stored procedures in a database other than master -- this is because SQL Server will look in master for the stored procedure, if it sees the sp_ prefix. However, if you're creating "system" stored procedures that should be callable from all databases, and which are created in master, then the sp_ prefix might make sense...

There are really no best practices I know of, that apply only to stored procedures in master. They follow the same basic rules as any other stored procedure. Note that you can't create objects in (or even access) the resource database -- it is hidden so that only the query engine can access it.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

|||Just in addition to Adam, the performance hit will be caused from the Cache Miss that is produced if the procedures takes the sp_ prefix, a schema lock on the procedure and a recompilation of the procedure.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for your answers.
As only procs with prefix 'sp_' stored in master are callable from all other databases I assume that that is the right way to write my own "system" procedures.
But I'm not very happy with that approach. I don't like to store ny own procs and tables in the master database because master is a central database for the whole server.

Wolfgang|||

You would have to define "system stored procedure" first. Very particularly, a system stored procedure has a prefix of sp_, is written by Microsoft, shipped with the product, and has special rules for name resolution.

I believe you are asking about "administrative stored procedures", these are procedures that you write that perform actions you need against one or more databases within a SQL Server. For these, I use an administrative database on the instance. I usually call it admin. In that database, I put all of my administrative procs along with any supporting tables, views, functions, etc. Any procedure can be called from any database, you just have to fully qualify the procedure.

|||OK, then talk about "administrative stored procedures".
Unfortunatly your approach doesn't fit my requirements. When I execute in a database kunktest1 'admin.dbo.kunkproc' I'm in the context of database admin and not in the kontext of database kunktest1. But I need to be in the kontext of the database from where I call the "administrative stored procedure" to select the objects of that database for example.

Wolfgang|||Normally you will be able to access the tables in the other databases using the three part names of the objects, like DatabaseName.OwnerOrSchema.Objectname. If this is not feasible for you and you really need the context of the database I would suggest generating a procedure in each database customized for each database.

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

being more specific

Hi all,

i want to write a trigger which returns a row to the application whenever a row is inserted into a table.

E.g.

When row R1 is inserted into a table T1.

the trigger should return the R1 to the application.

Wanna be coding monkey...

hi chaman,

its a bad practive for a trigger to be recordset returning.

trigger are best used for sending the result somewhere in the database

or for task such as maintaining the database consistency after an insert.

what are you going to do with it. can you specify what's your scenario so we can provide more help

if this is really what you need. you should write a stored procedure instead

regards,

joey

|||

Chaman:

I agree completely with Joey's position -- returning result sets from a trigger is in general a very bad idea. Also, there are other things that you need to consider should you continue with the result-set-from-a-trigger approach. It seems to me from your post that you have not considered impact whenever a single insert statement inserts multiple records into the table.

Are you motivated to return the row by any chance because you are using an identity column?

Dave

|||

Yes.. I also accept both Joeydj & Mugambo's comment..

If you want to read all about SQL Server Trigger visit this MSDN magazine Data Point article http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/default.aspx#S4

http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/

if you don;t have time to read these article read the summary of these article on

http://weblogs.asp.net/akinney/archive/2003/11/14/37509.aspx

But really we don't know the purpose of Chaman's need...I will give the solution but before doing this read all these article and choose your decesion.( i am not ready to say its not possible at all in sql server )

on your trigger use the following statement

select * From Inserted (note: this statement only work on Trigger)

|||

Hi all,

thanks for your help,

well as i am new in this SQL let me be clear on my requirements..

1. we have a table T1, when a new row R is added in T1,

i have a service who triggers an action (say beep) depending on the entities in R,

i want a trigger who will send the data in R of T1 to that service.

Now, question 1 - is it possible to write sucha trigger?

2. if yes, how? how can a table return a value to a service.

Regards,

chaman.

|||How is this service listening for the prompts?|||

i think you need to create a service that listens or monitor the event in the db.

any way if your into service orinted design

it is not definetely the trigger that you will have to use.

you might as well consider "sql server notification services" and/or the service broker

Beginners Question: How to write this query

Hi -

I feel stupied b/c I can't figure this out but I hope it's just a few seconds for some of the more senior posters of this forum:

I have a table that looks like this:

Table:Request
ID: bigint
... (a bunch of table specific columns)
User_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)
Provider_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)

and then there is a second table that looks like this:

Table: Status
ID: bigint
RequestID: bigint (the request ID, so I can see all status messages for the request)
Time: Timestamp
Code: byte (there are only a few status messages)

The status table holds both the status for users and providers, as they use exactly the same status code. What I'd like to get is a table/view that looks like this:

RequestID:bigint (the request that this status belongs to)
User_Code: byte (the code of th most recent user status)
Provider_Code: byte (the code of the most recent provider status)

Somehow, I am blanking how to create a query that returns both user and provider codes in one row. Any help greatly appreciated!!!!

Oliver

Why has the Status table the column RequestID?

Otherwise the SELECT you want is along the lines of

SELECT RequestID, U.Code, P.Code FROM Request, Status U, Status P WHERE Request.User_Status_Id = U.Id AND Request.Provider_Code = P.Id

|||

Thanks - that improves my life. I had used multiple views before and it was getting messy ...

BEGINNER: simple Delete trigger

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END

Unfortunatelly I receive errors which I don't understand:

Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 13
The multi-part identifier "Documents.Numer" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 16
The multi-part identifier "Documents.Number" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 18
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 19
The multi-part identifier "Dokuments.Number" could not be bound.

Please help to correct the code.
Thank you very much!
/RAM/How to forbid deleting Positions if Documents.WasDeleted bit is not
set?
Please help.
/RAM/|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:
>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
>
CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
>
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END
>
Unfortunatelly I receive errors which I don't understand:

I understand the errors, but I understand about as little of your
trigger that SQL Server does. You seem to be making things up out of
thin air. When you say:

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

What are Documenty and Documents supposed to be? Maybe you mean

IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

The same goes for

DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);

This would compile if you have a column Documents in PositionsPZZW,
and this columns is of a CLR UDT and had an attribute named Number.
What this really should be, I don't even want to guess, since I know
nothing about PositiosnPZZW.

The standarad recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statments with sample data.
o In this case: a sample DELETE statement.
o The desired result given the sample.

It also helps to give a little more detailed description of the problem.

By the way, why are there three Positions tables? Maybe there is a good
reason for this, but I have a suspicion that one should do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 6 Jul 2006 08:25:27 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>I understand the errors, but I understand about as little of your
>trigger that SQL Server does. You seem to be making things up out of
>thin air. When you say:
>
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

I meant Documents.Movement

Quote:

Originally Posted by

>
>What are Documenty and Documents supposed to be? Maybe you mean
>
IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

Exactly

Quote:

Originally Posted by

>
>
>The same goes for
>
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
>
>This would compile if you have a column Documents in PositionsPZZW,
>and this columns is of a CLR UDT and had an attribute named Number.
>What this really should be, I don't even want to guess, since I know
>nothing about PositiosnPZZW.

I need:
IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
DELETE FROM PositionsPZZW
WHERE Number IN (SELECT Number FROM deleted);

Quote:

Originally Posted by

>By the way, why are there three Positions tables? Maybe there is a good
>reason for this, but I have a suspicion that one should do.

They have different columns describing items.

Thank you, you have helped me... Problem closed
Could you help me with post "one more question"? Thank you!
/RAM/|||Sorry, too short problem description.
Anyway, I solved.
/RAM/|||R.A.M.,
What was the solution you found? Please post as others might have a
simular problem.
TIA
Rob

R.A.M. wrote:

Quote:

Originally Posted by

Sorry, too short problem description.
Anyway, I solved.
/RAM/

|||On Thu, 06 Jul 2006 10:46:50 +0200, R.A.M. <r_ahimsa_m@.poczta.onet.pl>
wrote:

Quote:

Originally Posted by

>IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
>DELETE FROM PositionsPZZW
>WHERE Number IN (SELECT Number FROM deleted);

That looks dangerous. If one row in DELETED has a 'PZ' value, all
rows in PositionsPZZW that match DELETED will be dropped, even those
that do NOT have 'PZ' or 'ZW'.

How about this alternative:

DELETE FROM PositionsPZZW
WHERE Number IN
(SELECT Number FROM deleted WHERE Movement IN ('PZ', 'ZW'));

It does not require the IF test at all, as if there are no matches it
will do nothing.

Roy Harvey
Beacon Falls, CT|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

Could you help me with post "one more question"? Thank you!

If you repost it, and clarify what you mean. I understood very little
of it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

But the code implies some design problems. What are the logical
differences among
PositionsPZZW, PositionsWZRW and PositionsMM ? This looks like
attribute splitting.

Why are you using triggers instead of DRI actions?|||On 6 Jul 2006 04:39:13 -0700, "rcamarda" <robc390@.hotmail.comwrote:

Quote:

Originally Posted by

>What was the solution you found? Please post as others might have a
>simular problem.
>TIA
>Rob

I decided not to use WasDeleted flag in Documents, so it was enough to
set Delete Rule in FK_Positions_Documents to "No Action".
/RAM/|||BEGINNER: simple Delete trigger

beginner.. :-)

Hello there

I am a very beginner..

I need to know if this is the write structure for doing this:

1. this func call for 4 other func: is it suppose to look like this?

2. with what i should replace the RETURNS int if its return date & int

thanks

ALTER FUNCTION [dbo].[StatisticsEx2_0Func]

(

@.Date_M_Y smalldatetime ,

@.FK nchar (20) ,

@.BizID int

)

RETURNS int

AS

BEGIN

DECLARE @.ResultVar int

set @.ResultVar = (SELECT

dbo.StatisticsEx_2_1_SingleMonthlySumFunc.A, dbo.Statistics_2_1_SingleAnnualAvgFunc.B, dbo.Statistics_2_1_AllGropsMonthlyTotalFunc.C, dbo.StatisticsEx_2_1_AllGroupsAnnualAvgFunc.D

FROM dbo.Statistics_2_1_AllGropsMonthlyTotalFunc, dbo.Statistics_2_1_SingleAnnualAvgFunc, dbo.StatisticsEx_2_1_AllGroupsAnnualAvgFunc, dbo.StatisticsEx_2_1_SingleMonthlySumFunc)

RETURN @.ResultVar

END

If you want to return more than one value then use Inline Table /Table Valued UDF.

If you use sql server 2005 then you can utilize the UDT (.NET Class) to return as Structure.

Create Function [dbo].[StatisticsEx2_0Func]()

Returns table

as

Return

(

Select 100 i, Cast('1/1/2007' as Datetime) d

)

go

Select * from [dbo].[StatisticsEx2_0Func]()

|||

amm..

the "mother" func suppose to get 3 parm – every child func use 2 param out of the 3

then let the table

so...?

Create Function [dbo].[StatisticsEx2_0Func]()

Returns table

as

Return

(

Select

@.Date_M_Y smalldatetime ,

@.BizID int ,

@.FK

)

go

beginner question

Hi, folks:
I am looking into using SSIS to create a OLAP database. How easy it is to write a package to re-pull some fact data from the source databae base on some flags. What happend is we are planning to roll up some minute by minute data into hourly averages using SSIS but user is allowed to modified the minute by minute data (maximum is 90 days). Say if we set up some flags, and the package comes in every hour to check for these flags and only re-ETL the changed one. Can I be done or is there better way to do it.SSIS can push data straight into an OLAP cube from the pipeline. I'm not sure about updating values that are already there though (if that is what you are asking).

-Jamie|||Yes, that's updating the OLAP values is what I am asking. We do gas-flow calculation. For each well, we insert minute-by-minute data. Out of a thousand wells, only 10 wells will have the minute by minute data changed, and all I plan to do is to flag these 10 wells and re-ETL them.

Before Insert Trigger cancels insert

Is it possible for me to write a before insert trigger which will prevent insert if the data doesn't match specific criteria? What would the syntax look like?SQL Server triggers are always AFTER triggers, but you can rollback the insert.

If some condition causes you to want to fail the insert, you can just rollback the transaction

From Books Online:

USE pubs
IF EXISTS (SELECT name FROM sysobjects
 WHERE name = 'employee_insupd' AND type = 'TR')
 DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @.min_lvl tinyint,
 @.max_lvl tinyint,
 @.emp_lvl tinyint,
 @.job_id smallint
SELECT @.min_lvl = min_lvl,
 @.max_lvl = max_lvl,
 @.emp_lvl = i.job_lvl,
 @.job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
 JOIN jobs j ON j.job_id = i.job_id
IF (@.job_id = 1) and (@.emp_lvl <> 10)
BEGIN
 RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
 ROLLBACK TRANSACTION
END
ELSE
IF NOT (@.emp_lvl BETWEEN @.min_lvl AND @.max_lvl)
BEGIN
 RAISERROR ('The level for job_id:%d should be between %d and %d.',
 16, 1, @.job_id, @.min_lvl, @.max_lvl)
 ROLLBACK TRANSACTION
END

|||I just want to add that SQL Server 2000 introduced "INSTEAD OF" triggers. These triggers fire BEFORE the action. Well, more correctly they fire instead of the updating action.

Terri|||True. The name has put me off, and so I have not really ever used them, but in effect they can be used exactly like a before trigger. Cool.

From a design standpoint I would have preferred a true BEFORE UPDATE (INSERT/DELETE) trigger to save me some work in the trigger if I want the transaction to go through, but this is certainly close.

Thanks again.|||does SQL Server not have a "Create or Replace" clause? i notice in the sample code that there is logic to do just that.

if i write an INSTEAD OF trigger, how do i insert into the same table without causing recursion? or is SQL Server smart enough to avoid that? i haven't seen any examples in the help files for what i want to do.