February 2, 2012 at 8:56 am
mtassin (2/2/2012)
KWymore (2/2/2012)
mtassin (2/2/2012)
cengland0 (2/2/2012)
I like using this function for audit tables. If you have an application that allows users to add, edit, and delete, you can use this method to keep track of the changes performed by those users. Nice if you are asked to provide information such as who updated the record and what was changed.Honestly these days for auditting purposes, I prefer CDC.
I just started using OUTPUT recently versus running multiple statements to put data in one table and then remove it from another one. I haven't looked into CDC yet. OUTPUT is pretty easy to implement and understand (at least for me).
Actually, CDC is even easier, and it's based on SQL Server replication technology instead of trigger-ish based technology that OUTPUT does. The CDC system reads the Transaction log and records changes, it has a much lower impact on transactions.
That said, I use OUTPUT for things, just not auditting anymore.
I'll check that out. Thanks!
February 2, 2012 at 9:00 am
Nice and simple -- thanks, Steve!
February 2, 2012 at 9:15 am
cengland0 (2/2/2012)
I like using this function for audit tables. If you have an application that allows users to add, edit, and delete, you can use this method to keep track of the changes performed by those users. Nice if you are asked to provide information such as who updated the record and what was changed.
You can also use SQL Server Audit for this (and you can even audit SELECT statements!). CDC is primarily targeted at optimizing ETL in data warehouses.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 9:27 am
tks for the question.
February 2, 2012 at 9:32 am
SQL Kiwi (2/2/2012)
cengland0 (2/2/2012)
I like using this function for audit tables. If you have an application that allows users to add, edit, and delete, you can use this method to keep track of the changes performed by those users. Nice if you are asked to provide information such as who updated the record and what was changed.You can also use SQL Server Audit for this (and you can even audit SELECT statements!). CDC is primarily targeted at optimizing ETL in data warehouses.
Except, and correct me if I'm wrong, SQL Server Audit is only available in 2008 but the OUTPUT clause is available in 2005 and 2008.
February 2, 2012 at 9:34 am
BenWard (2/2/2012)
DATE is not a valid data type... ?
It's becoming too hard to track all the T-SQL between all versions, so I left this off. SQL 2005 is out of support, over 5 years old. At this point, you should have heard of DATE as a data type. If not, you should be learning what's new, or slightly new.
February 2, 2012 at 9:37 am
cengland0 (2/2/2012)
Except, and correct me if I'm wrong, SQL Server Audit is only available in 2008 but the OUTPUT clause is available in 2005 and 2008.
That's right, and if you're on SQL Server 2000, 7.0 or 6.5 you'll have to use triggers 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 10:04 am
SQL Kiwi (2/2/2012)
cengland0 (2/2/2012)
Except, and correct me if I'm wrong, SQL Server Audit is only available in 2008 but the OUTPUT clause is available in 2005 and 2008.That's right, and if you're on SQL Server 2000, 7.0 or 6.5 you'll have to use triggers 😛
With 2012 coming, I think it is time to leave 2000 and previous versions out of the picture.
How do you remember what was or was not available in 6.5, Paul? (I am green with envy.)
February 2, 2012 at 10:07 am
Nice and easy question. Thanks.
February 2, 2012 at 10:18 am
Revenant (2/2/2012)
With 2012 coming, I think it is time to leave 2000 and previous versions out of the picture.How do you remember what was or was not available in 6.5, Paul? (I am green with envy.)
Ha, well the simple answer is that most of the time I don't. To be honest, I find it hard enough to keep 2012, R2, 2008, and 2005 in my head - and 2005 is rapidly moving to my VM-only-when-needed personal support cycle 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 12:34 pm
If I'm not mistaken, both SQL Server Audit and Change Data Capture are Enterpise edition features. Or has that changed?
Standard edition and below, Output and/or triggers, possibly combined with a trace, are what you have available.
My favorite use of Output is in testing. Add an Output clause to a script, wrap Begin Tran and Rollback around it, and you can see the before-and-after data very easily. Very easy to confirm the rows affected are the ones you expect.
It's also better than Scope_Identity() et al, for getting ID values, since it can return multiple rows, and can get GUIDs as well as Identity values, as well as natural key values (of course).
And, yes Paul, instead-of triggers can do weird things to just about any CRUD statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2012 at 12:39 pm
CDC is available in the Enterprise, Developer, & Evaluation editions of both 2008 and 2008 R2
February 2, 2012 at 12:58 pm
GSquared (2/2/2012)
If I'm not mistaken, both SQL Server Audit and Change Data Capture are Enterpise edition features. Or has that changed?
That's right.
And, yes Paul, instead-of triggers can do weird things to just about any CRUD statement.
The weirdness I showed is specific to OUTPUT and instead of triggers. You'll have noticed the trigger doesn't do anything particularly unusual.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 5:22 pm
a nice question with no tricks, no obscure wording, no complicated code, and the correct answer is undisputably right. Can this really be a QoTD? 😛
Tom
February 3, 2012 at 10:20 am
Good question.
---------------
Mel. 😎
Viewing 15 posts - 31 through 45 (of 62 total)
You must be logged in to reply to this topic. Login to reply