Any sql-db-related objects updated?

Question

Any sql-db-related objects updated?

OP_HELP

SSCrazy

Points: 2618
More actions
December 2, 2008 at 11:53 am

#198426

I typically use a SQL Server Management Studio to manually open a table to perform updates rather than using the UPDATE command on the table. In doing so, I was hoping to find if there are any system-based updates being performed as a result of this.
For example, in SSMS, I open the table in the following fashion to do updates:
After I'm done, the updates are committed. But I'm wanting to know if there are any underlying system tables that are changed/ updated in the process. Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

If you're simply manipulating data, no, system tables aren't affected... well, OK, changing data can change statistics, so where the statistics are maintained can be changed, and if you have indexes on the columns that you modified or inserted or deleted, then the indexes themselves are changed. Actually, all kinds of "system" tables are affected by most of what you do.

What specifically are you worried about or having trouble with?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Grant Fritchey (12/2/2008)
, OK, changing data can change statistics, so where the statistics are maintained can be changed, and if you have indexes on the columns that you modified or inserted or deleted, then the indexes themselves are changed.

And then the PFS pages have to be changed. If new pages are allocated, the GAM or SGAM might have to be updated. The DCM will have to be modified...

But that's all deep internal stuff that people (mostly) don't need to be aware of.

What's the reason behind the question?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OP_HELP SSCrazy Points: 2618 More actions · Answer 3

What's the reason behind the question?

Grant & Gail,

wow! thanks for the quick responses. your insight is instructive.

If I'm understanding SQL 2005 DBMS correctly, the DBMS "engine" has some impact on the the underlying system tables. Maybe I'm way off base here, so I apologize in advance. (I am humble when I visit this forum. )

Its just that I've read a lot about system tables and cannot determine the extent to which they are impacted by work done as I've put in my illustration above.

UPDATE or INSERT will format a lastupdate column with the data I need. Thats fine. But because I work with a couple of related rows against the table, all I do is SELECT the rows I need to work with. Then I do the updates directly on the table with the selected rows.

Would a trigger be acceptable? But even then, I thought a trigger is only activated upon UPDATE or INSERT commands as well.

Does the Statistics Table have a column which exposes the datetime that an entry was made on that table?

Great forum. I've read your comments to others. Thanks

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

I'm still a bit confused by the question.

By and large, understanding what's occurring at lower levels within the system is useful, but shouldn't be a day-to-day worry. Rather than be concerned which system table is updated by an insert, be sure you have a good clustered index on the table and no unnecessary indexes so that you are only processing what you have to. The system tables will take care of themselves. You have to be concerned about your tables and queries and their impact on the system (and system tables) not the impact of the system tables on what you do.

As to whether or not you can use a trigger as part of your selects... if I understood the question. The answer is no. You're right in your initial assumption. Triggers are only available as part of data manipulation or, in the case of ddl triggers, structure changes. They don't help or hurt SELECT statements except in how they affect the data being selected because of any data manipulation done within the trigger during the inserts & updates.

Statistics are aggregate data, not row-by-row copies. They're only updated based on certain criteria within the system as data changes or by direct manipulation. It sounds like you're trying to do database auditing? If so, do a search in the articles and scripts section here at SSC. There are several resources on that topic.

I'm sorry if none of this answering your questions, but I'm stil a bit confused about what you're trying to achieve and where the issues lie.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

umanpowered (12/3/2008)

If I'm understanding SQL 2005 DBMS correctly, the DBMS "engine" has some impact on the the underlying system tables. Maybe I'm way off base here, so I apologize in advance. (I am humble when I visit this forum. )
Its just that I've read a lot about system tables and cannot determine the extent to which they are impacted by work done as I've put in my illustration above.

The engine does change some internal objects, but you don't need to even be aware of that.

You only need to know stuff about the internals when something breaks (which fortunately isn't often).

UPDATE or INSERT will format a lastupdate column with the data I need. Thats fine. But because I work with a couple of related rows against the table, all I do is SELECT the rows I need to work with. Then I do the updates directly on the table with the selected rows.
Would a trigger be acceptable? But even then, I thought a trigger is only activated upon UPDATE or INSERT commands as well.

Acceptable for what?

If you know what rows you want to manipulate, update them. You can use exactly the same conditions as you used with the insert

Does the Statistics Table have a column which exposes the datetime that an entry was made on that table?

Stats aren't a table. If you need to know when a entry was made to the table, you need to have some mechanism to track that. Typically a trigger on insert/update/delete works well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OP_HELP SSCrazy Points: 2618 More actions · Answer 6

GilaMonster (12/3/2008)
The engine does change some internal objects, but you don't need to even be aware of that.
You only need to know stuff about the internals when something breaks (which fortunately isn't often).

My objective was to determine if there was a way to capture such a change and present that changed data within an ASP by attaching the correspondent SQL to an ObjectDataSource, and then attach that to a GridView. I was curious to whether this was possible because of the method I was performing the updates as illustrated in my initial post.

OP_HELP SSCrazy Points: 2618 More actions · Answer 7

GilaMonster (12/3/2008)

umanpowered (12/3/2008)
UPDATE or INSERT will format a lastupdate column with the data I need. Thats fine. But because I work with a couple of related rows against the table, all I do is SELECT the rows I need to work with. Then I do the updates directly on the table with the selected rows.
Would a trigger be acceptable? But even then, I thought a trigger is only activated upon UPDATE or INSERT commands as well.
Acceptable for what?
If you know what rows you want to manipulate, update them. You can use exactly the same conditions as you used with the insert

This is what I'm setting up, in various little SQL snippets. I guess I was being lazy, expecting that some underlying system table would expose a last-update-date type of "thingy", as opposed to me explictly running an UPDATE SQL statement in order to achieve the same objective.

Because the changes I make are miniscule, I was seeking to avoid the trigger or UPDATE solutions by merely exposing changed "date" data in one of the columns in the underlying system tables. I wasn't seeking to manipulate the underlying system tables or anything, but simply use that data in an SQL when I write it out to an ASP.

OP_HELP SSCrazy Points: 2618 More actions · Answer 8

GilaMonster (12/3/2008)
Stats aren't a table. If you need to know when a entry was made to the table, you need to have some mechanism to track that. Typically a trigger on insert/update/delete works well.

The mechanism I'm using is getdate() on the column in the table at this time. I'm going to write Triggers later (I have a slew of tables I have to migrate over). I was thinking there would be a "last update" column on some type of data-table, from what I gathered from what Grant was explaining, that I could use rather than writing tiny little snippets of SQL with UPDATES with WHERE's on various columns I need to select from for editing.

But I'm content for now. I was being driven by curiousity. I'm afraid there's some type of quote about Curiousity and the Cat! :w00t:

I appreciate your observations, Gail. If that's the way the DBMS is intended, then that's fine with me.

-uman

OP_HELP SSCrazy Points: 2618 More actions · Answer 9

Grant Fritchey (12/3/2008)
I'm sorry if none of this answering your questions, but I'm stil a bit confused about what you're trying to achieve and where the issues lie.

Grant, no problem. I hope I was able to clarify my issues in response to Gail's observations. My interest in DB auditing is extreme; I hope to achieve some ground on that in the future.

If you have any input into the fundamental goal I was attempting to achieve, then please reply.

Once again, many thanks to you and this forum for your assistance.

-uman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

umanpowered (12/3/2008)
I was thinking there would be a "last update" column on some type of data-table, from what I gathered from what Grant was explaining, that I could use rather than writing tiny little snippets of SQL with UPDATES with WHERE's on various columns I need to select from for editing.

No. There are hidden internal counters that get incremented when columns get updated, but no time is associated and you can't view those anyway.

Ignore the deep internals. They're not for you to mess around with.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

umanpowered (12/3/2008)
Because the changes I make are miniscule, I was seeking to avoid the trigger or UPDATE solutions by merely exposing changed "date" data in one of the columns in the underlying system tables. I wasn't seeking to manipulate the underlying system tables or anything, but simply use that data in an SQL when I write it out to an ASP.

These are not the 'Driods you are looking for.

Use the triggers, Luke.

:laugh: (dang, I'm channeling Obi-Wan's ghost again...)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

OP_HELP SSCrazy Points: 2618 More actions · Answer 12

ha...pretty funny...

Gail, i like your signature quote...its "otherwise" for me ! :hehe:

Will follow your directions from here on out....

OP_HELP SSCrazy Points: 2618 More actions · Answer 13

rbarryyoung (12/3/2008)

[
Use the triggers, Luke.
:laugh: (dang, I'm channeling Obi-Wan's ghost again...)

My light saber apparently has a glitch!

The following trigger is not working:

USE [db]

GO

/****** Object: Trigger [dbo].[trTest] Script Date: 12/09/2008 15:17:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[trTest]

ON [dbo].[myTable]

AFTER UPDATE

AS

DECLARE @NOTE VARCHAR;

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

UPDATE [myTable] SET lastupdate = getdate() WHERE @note = note

I've tested the UPDATE statement separately, and it works as expected.

RECURSIVE TRIGGERS is set to FALSE

"Alter any database DDL trigger" and "Alter" is set to GRANT under the login that I've used for other processes

This trigger is supposed to fire when the "Note" column gets hit by a UPDATE statement

I cannot slay this data, please advise! :w00t:

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

You declare @note, but never give it a value. So - you don't get anything qualifying for your query.

Which is really fine, because that's not how you want to set up your trigger anyway. The big thing to remember is that a trigger fires once per operation, so if you add an update statement which affected 1000 rows, the kind of code you're looking to set up would blow up...

You want to look at the CREATE TRIGGER stuff in BOL, and read up on the "magic tables" or (as it calls them) "virtual tables", INSERTED and DELETED. These two tables contain the "before" and "after" of the rows being affected by a given operation (insert, update or delete) as needed.

Your trigger then looks something more like:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[trTest]

ON [dbo].[myTable]

AFTER UPDATE

AS

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

UPDATE [myTable] SET lastupdate = getdate()

from myTable

inner join inserted on mytable.note = inserted.note

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?