Inserted Rows

Does anyone have any SP or script that can help me easily
determine the activity within my tables? Specifically, I
am looking for any kind of SPs, scripts, or tools that
can help me easily determine how heavily hit my various
tables are. For example is table A with 1,000,000 rows
in it not the heavily used while table B with 5,000 rows
in it is constantly being inserted to, deleted from, and
updated. I am trying to track down my heavy hitter
tables to do some P&T on them or move them to their own
files, etc.Z,
You can use SQL Profiler to track activity against a database. One data
column it can report is the ObjectName being referenced. Examine the
discussion in the BOL on SQL Profiler and SQL Trace.
Ideally, you would run the trace and spool its results to a file. Afterward
you can load the file into a table and do some queries to aggregate the
activity you are experiencing.
Running a trace will take some CPU from your server, but if you are
judicious in the events and data columns it should not be oppressive to the
server unless you are running at very high CPU levels already.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Z" <anonymous@.discussions.microsoft.com> wrote in message
news:07a601c3af8f$d7d328f0$a001280a@.phx.gbl...
> Does anyone have any SP or script that can help me easily
> determine the activity within my tables? Specifically, I
> am looking for any kind of SPs, scripts, or tools that
> can help me easily determine how heavily hit my various
> tables are. For example is table A with 1,000,000 rows
> in it not the heavily used while table B with 5,000 rows
> in it is constantly being inserted to, deleted from, and
> updated. I am trying to track down my heavy hitter
> tables to do some P&T on them or move them to their own
> files, etc.
>

Insert trigger to populate other columns in same row

I'm looking for an efficient way to populate derived columns when I
insert data into a table in SQL Server. In Informix and PostgreSQL
this is easily done using the "for each row..." syntax, but all I've
been able to come up with for SQL Server is the following:

create table testtrigger(id integer unique, b integer, c integer)
go

create trigger testtrigger_ins on testtrigger
for insert as
update testtrigger set c = (select ...some_function_of_b... from
testtrigger t1,inserted t2
where t1.id = t2.id)
where id in (select id from inserted);
go

where id is testrigger's unique id field, and c is a field derived from
b.

This seems terribly inefficient since each insert results in an extra
select and update. And if the table is large and unindexed (which it
could be if we are bulk loading) then I would imagine this would be
very slow.

Are there any better ways of doing this?

Many thanks,...
Mike Dunham-WilkieCREATE table ><>>Look at computered columns is your answer . BTW no
data is stored for this type of column.

e.g. CREATE TABLE mytable
(
low int,
high int,
myavg AS (low + high)/2

Duncan|||On 22 Sep 2006 13:11:12 -0700, mike@.barrodale.com wrote:

Quote:

Originally Posted by

>I'm looking for an efficient way to populate derived columns when I
>insert data into a table in SQL Server. In Informix and PostgreSQL
>this is easily done using the "for each row..." syntax, but all I've
>been able to come up with for SQL Server is the following:

(snip)

Quote:

Originally Posted by

>This seems terribly inefficient since each insert results in an extra
>select and update. And if the table is large and unindexed (which it
>could be if we are bulk loading) then I would imagine this would be
>very slow.
>
>Are there any better ways of doing this?

Hi Mike,

Since SQL Server is optimized for set-based operations, you'll probably
find the speed of these operations to be quite adequate in most cases.

Here's an alternate syntax of the UPDATE statement that might result in
even faster operation. Note, though, that this syntax has some quirks,
especially if there's not a guaranteed one to one mapping of rows in the
update target and the source of the data.

UPDATE t
SET c = some_function_of_b
FROM testtrigger AS t
INNER JOIN inserted AS i
ON i.id = t.id

But only use this if you have a solid reason for being unable to use a
computed column, as Duncan ("undercups") demonstrates in his reply!

--
Hugo Kornelis, SQL Server MVP