February 2, 2012 at 5:15 am
Nice simple basis question - Thanks! I did have to double check looking for the trick though!!!
February 2, 2012 at 6:06 am
Thanks Steve
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 2, 2012 at 6:22 am
Good question about a very useful feature that a lot of people do not appear to know about. You can also use the OUTPUT clause with the MERGE command too. It's especially useful when you need to know a range of values that were inserted so that you have the newly generated keys in order to also insert data into detail tables below the insert.
February 2, 2012 at 6:36 am
Hand up, I did not know about it. I can definitely see uses for this OUTPUT thingy though - cheers.
February 2, 2012 at 6:59 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.
Honestly these days for auditting purposes, I prefer CDC.
February 2, 2012 at 7:35 am
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).
February 2, 2012 at 7:49 am
Great question. Just what I needed this morning to get my brain working. Like many others with the QotD I was looking for the obvious (or sometimes not so obvious) trick, but this was simple and straight forward.
February 2, 2012 at 7:59 am
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.
February 2, 2012 at 8:15 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.
So can CDC take a value from a web page (such as the user that's logged into the site) and then log that into your audit table? I can do that with the OUTPUT clause easily with .Net by specifying a parameter. If you're relying on transaction logs for your auditing, you can only get information from the actual tables and not anything additional.
February 2, 2012 at 8:17 am
cengland0 (2/2/2012)
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.
So can CDC take a value from a web page (such as the user that's logged into the site) and then log that into your audit table? I can do that with the OUTPUT clause easily with .Net by specifying a parameter. If you're relying on transaction logs for your auditing, you can only get information from the actual tables and not anything additional.
It can if I store it in the table. 🙂
February 2, 2012 at 8:18 am
I have found the Output clause to be very handy, great question.
February 2, 2012 at 8:22 am
mtassin (2/2/2012)
It can if I store it in the table. 🙂
Exactly. You don't need that information in the main table -- just the audit table.
Also, what would CDC do if you did have userid in the main table and the same person that edited it before edited it the second time? It would not log that column (since it was not updated) so you'll lose the username in your audit table.
February 2, 2012 at 8:28 am
Cadavre (2/2/2012)
BenWard (2/2/2012)
DATE is not a valid data type... ?It was introduced for SQL Server 2008, so I guess the question should read "On SQL Server 2008 and above, what is the output of this code?"
Or simply replace DATE with Datetime...
Pierre
February 2, 2012 at 8:52 am
cengland0 (2/2/2012)
mtassin (2/2/2012)
It can if I store it in the table. 🙂Exactly. You don't need that information in the main table -- just the audit table.
Also, what would CDC do if you did have userid in the main table and the same person that edited it before edited it the second time? It would not log that column (since it was not updated) so you'll lose the username in your audit table.
CDC captures the values of all the columns when you use cdc.fn_cdc_get_all_changes_table name
Viewing 15 posts - 16 through 30 (of 62 total)
You must be logged in to reply to this topic. Login to reply