InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 15:27:25 GMT20http://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]kapil190588 (10/11/2012)[/b][hr]Dont aware of this Output clause...worng answer :(can anyone explain this well with a example?[/quote]Explanation and examples can be found here: [url=http://msdn.microsoft.com/en-us/library/ms177564.aspx]http://msdn.microsoft.com/en-us/library/ms177564.aspx[/url]If you still have questions after that, feel free to post those questions here.Thu, 11 Oct 2012 03:25:01 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxDont aware of this Output clause...worng answer :(can anyone explain this well with a example?Thu, 11 Oct 2012 01:21:32 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]Hugo Kornelis (2/8/2012)[/b][hr]So I disagree that the minimum version should be included in all QotD questions. I think we should all assume that questions apply to supported versions of SQL Server, unless stated differently.[/quote]My view on this. If it's specific to one supported version, i.e. SQL Server 2008 R2, but not SQL Server 2008, the version should be included. If it's either of those versions, then no version need be included.We may ask SQL 2005 compliant questions, but no guarantees.Thu, 09 Feb 2012 11:26:11 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxA somewhat educated guess and I got it right!Thu, 09 Feb 2012 11:08:44 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxWhile I may want to have Sql Server 2012 when it comes out, I know that it most likely will not happen and I should be content with the 2008R2 upgrade I received last spring (from 2000). I actually have to work with MSDE for some projects that code is needed for.I can see both sides of the reasoning for explaining what version was needed.However; as a learning tool, maybe we should have explanations of the reasons why people may get the wrong answers or errors, which could get extended to people learning more about the new features available and could push upgradesThat said, in Sql2000 you get a different error: "Line 11: Incorrect syntax near 'OUTPUT'."Also, I feel the QOTD should also remove any objects it creates.Thu, 09 Feb 2012 09:26:48 GMTMad Mychehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]cengland0 (2/8/2012)[/b][hr][quote][b]Hugo Kornelis (2/8/2012)[/b]You are aware that mainstream support for SQL Server 2005 has already ended, are you? I'd consider upgrading, if I were you... :hehe:[/quote]You're right that the ideal situation is to always have the most current version of the server software installed; however, some of us work with organizations and customers that have outdated hardware and software and we must use what is available.We do not always have the option of telling our clients they must use the latest server software version or we cannot work on their project. Most of my clients are set with what they have and the timeframes and budget constraints prohibit any server software upgrades.To maintain my client list, I must be fluent in several server types including MySQL, Oracle, MS SQL Server, and even Teradata. My clients cannot easily change their server types or versions without it being a major undertaking. Besides, there's a lot of debate out there as to which server software is better and I'm not going to start debating that with my clients.I make more money ($/hour wise) when I work with small companies that are using older software than I do with large enterprises that have the most up-to-date versions. The smaller companies don't have the budget dollars to keep upgrading their software nor do they have the money to keep a DBA on staff. So, when one is needed, they pay top dollar for services. Larger companies keep DBA's on staff with an annual salary and that's significantly less pay.[/quote]All true.Please note that I never said that a SQL Server professional should work hard to forget SQL Server 2005 functionality. On the contrary, do foster taht knowledge!But what I do say, is that any SQL Server professional should work hard to get knowledge of supported versions as well. With Developer Edition available for a mere 50 or so bucks and able to be installed on any reasonably modern laptop or desktop (and Express Edition available in case even Developer Edition is too expensive), there really is no excuse for not getting yourself acquainted with newer versions. You may be stuck supporting SQL Server 2005 now - but one day, your company might want to upgrade. Or you may be forced to look for another job. And having your knowledge stuck at a version that's been out for almost seven years now, that is out of mainstream support, and that has been superseded by two new versions (2008 and 2008R2 count as seperate versions), plus another one around the corner - that does not look good on your resume!So I disagree that the minimum version should be included in all QotD questions. I think we should all assume that questions apply to supported versions of SQL Server, unless stated differently.(And if you think differently, then there are lots and lots of questions where I could comment that it does not apply to SQL Server 6.5. Or 4.2. Or even 1.0 (though I would have trouble verifying the last two claims).Wed, 08 Feb 2012 04:23:02 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]monpara.sanjay (2/8/2012)[/b][hr]and This forum should want to define minimum version to use in question.[/quote]The site editor already gave his reasons for not specifying a minimum version. At least you will know next time.Wed, 08 Feb 2012 04:14:37 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]cengland0 (2/8/2012)[/b][hr][quote][b]Hugo Kornelis (2/8/2012)[/b]You are aware that mainstream support for SQL Server 2005 has already ended, are you? I'd consider upgrading, if I were you... :hehe:[/quote]You're right that the ideal situation is to always have the most current version of the server software installed; however, some of us work with organizations and customers that have outdated hardware and software and we must use what is available.[/quote]Hence the word 'consider'. The wider point is that whatever versions of SQL Server we work with, it makes sense to at least be aware that things like DATE exist.Wed, 08 Feb 2012 04:13:42 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxIts right, cengland0and This forum should want to define minimum version to use in question.Wed, 08 Feb 2012 04:08:57 GMTmonpara.sanjayhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]Hugo Kornelis (2/8/2012)[/b]You are aware that mainstream support for SQL Server 2005 has already ended, are you? I'd consider upgrading, if I were you... :hehe:[/quote]You're right that the ideal situation is to always have the most current version of the server software installed; however, some of us work with organizations and customers that have outdated hardware and software and we must use what is available.We do not always have the option of telling our clients they must use the latest server software version or we cannot work on their project. Most of my clients are set with what they have and the timeframes and budget constraints prohibit any server software upgrades.To maintain my client list, I must be fluent in several server types including MySQL, Oracle, MS SQL Server, and even Teradata. My clients cannot easily change their server types or versions without it being a major undertaking. Besides, there's a lot of debate out there as to which server software is better and I'm not going to start debating that with my clients.I make more money ($/hour wise) when I work with small companies that are using older software than I do with large enterprises that have the most up-to-date versions. The smaller companies don't have the budget dollars to keep upgrading their software nor do they have the money to keep a DBA on staff. So, when one is needed, they pay top dollar for services. Larger companies keep DBA's on staff with an annual salary and that's significantly less pay.Wed, 08 Feb 2012 03:45:12 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]monpara.sanjay (2/8/2012)[/b][hr]Thanks to clarifyI am using SQL Server 2005.[/quote]That was already clear :-PSteve said it well a few posts back...[quote][b]Steve Jones - SSC Editor (2/2/2012)[/b][hr]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.[/quote]Wed, 08 Feb 2012 03:20:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxThanks to clarifyI am using SQL Server 2005.Wed, 08 Feb 2012 02:58:24 GMTmonpara.sanjayhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]monpara.sanjay (2/8/2012)[/b][hr]Where is the [u]DATE [/u]datatype in sql server??????????[/quote]Here: [url=http://msdn.microsoft.com/en-us/library/bb630352.aspx]DATE (Transact-SQL)[/url]It's been around for four years or so now :-)Wed, 08 Feb 2012 01:34:42 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]monpara.sanjay (2/8/2012)[/b][hr][b]Where is the [u]DATE [/u]datatype in sql server??????????[/b][/quote]In all versions since SQL Server 2008.You are aware that mainstream support for SQL Server 2005 has already ended, are you? I'd consider upgrading, if I were you... :hehe:Wed, 08 Feb 2012 01:25:23 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[b]Where is the [u]DATE [/u]datatype in sql server??????????[/b][size="1"]CREATE TABLE mytesttable( MyID INT IDENTITY, mychar VARCHAR(20), mydate [b][u]DATE [/u][/b]DEFAULT GETDATE())DECLARE @mytable TABLE ( i INT, d [b][u]DATE[/u][/b]);INSERT dbo.mytesttable (mychar) OUTPUT INSERTED.myid, INSERTED.mydate INTO @mytableVALUES ( 'First Row')SELECT i, d FROM @mytable[/size]Wed, 08 Feb 2012 01:17:25 GMTmonpara.sanjayhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxLove the question. No tricks and a back-to-the-basics one.I was expecting a few BEGIN/ROLLBACK TRANSACTION commands in there to make it a little trickier but it turns out it was a very nice straightforward question. Thank you for that.Tue, 07 Feb 2012 07:44:47 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxgood question!!!!Fri, 03 Feb 2012 15:33:19 GMTrfr.ferrarihttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxGood question.Fri, 03 Feb 2012 10:20:56 GMTSqlMelhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxa nice question with no tricks, no obscure wording, no complicated code, and the correct answer is undisputably right. Can this really be a QoTD? :-PThu, 02 Feb 2012 17:22:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]GSquared (2/2/2012)[/b][hr]If I'm not mistaken, both SQL Server Audit and Change Data Capture are Enterpise edition features. Or has that changed?[/quote]That's right.[quote]And, yes Paul, instead-of triggers can do weird things to just about any CRUD statement.[/quote]The weirdness I showed is specific to OUTPUT and instead of triggers. You'll have noticed the trigger doesn't do anything particularly unusual.Thu, 02 Feb 2012 12:58:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxCDC is available in the Enterprise, Developer, & Evaluation editions of both 2008 and 2008 R2Thu, 02 Feb 2012 12:39:35 GMTVictor Kirkpatrickhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxIf 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.Thu, 02 Feb 2012 12:34:34 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]Revenant (2/2/2012)[/b][hr]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.)[/quote]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 :-)Thu, 02 Feb 2012 10:18:31 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxNice and easy question. Thanks.Thu, 02 Feb 2012 10:07:21 GMTJAZZ Masterhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]SQL Kiwi (2/2/2012)[/b][hr][quote][b]cengland0 (2/2/2012)[/b][hr]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.[/quote]That's right, and if you're on SQL Server 2000, 7.0 or 6.5 you'll have to use triggers :-P[/quote]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.)Thu, 02 Feb 2012 10:04:57 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]cengland0 (2/2/2012)[/b][hr]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.[/quote]That's right, and if you're on SQL Server 2000, 7.0 or 6.5 you'll have to use triggers :-PThu, 02 Feb 2012 09:37:35 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]BenWard (2/2/2012)[/b][hr]DATE is not a valid data type... ?[/quote]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.Thu, 02 Feb 2012 09:34:30 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]SQL Kiwi (2/2/2012)[/b][hr][quote][b]cengland0 (2/2/2012)[/b][hr]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.[/quote]You can also use [url=http://msdn.microsoft.com/en-us/library/cc280386(v=sql.105).aspx]SQL Server Audit[/url] for this (and you can even audit SELECT statements!). CDC is primarily targeted at optimizing ETL in data warehouses.[/quote]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.Thu, 02 Feb 2012 09:32:36 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxtks for the question.Thu, 02 Feb 2012 09:27:42 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]cengland0 (2/2/2012)[/b][hr]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.[/quote]You can also use [url=http://msdn.microsoft.com/en-us/library/cc280386(v=sql.105).aspx]SQL Server Audit[/url] for this (and you can even audit SELECT statements!). CDC is primarily targeted at optimizing ETL in data warehouses.Thu, 02 Feb 2012 09:15:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxNice and simple -- thanks, Steve!Thu, 02 Feb 2012 09:00:03 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]mtassin (2/2/2012)[/b][hr][quote][b]KWymore (2/2/2012)[/b][hr][quote][b]mtassin (2/2/2012)[/b][hr][quote][b]cengland0 (2/2/2012)[/b][hr]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.[/quote]Honestly these days for auditting purposes, I prefer CDC.[/quote]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).[/quote]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.[/quote]I'll check that out. Thanks!Thu, 02 Feb 2012 08:56:13 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]cengland0 (2/2/2012)[/b][hr][quote][b]mtassin (2/2/2012)[/b][hr]It can if I store it in the table. :)[/quote]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.[/quote]CDC captures the values of all the columns when you use cdc.fn_cdc_get_all_changes_table nameThu, 02 Feb 2012 08:52:24 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]Cadavre (2/2/2012)[/b][hr][quote][b]BenWard (2/2/2012)[/b][hr]DATE is not a valid data type... ?[/quote]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?"[/quote]Or simply replace DATE with Datetime...PierreThu, 02 Feb 2012 08:28:11 GMTpierre.gagnonhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]mtassin (2/2/2012)[/b][hr]It can if I store it in the table. :)[/quote]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.Thu, 02 Feb 2012 08:22:41 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxI have found the Output clause to be very handy, great question.Thu, 02 Feb 2012 08:18:20 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]cengland0 (2/2/2012)[/b][hr][quote][b]mtassin (2/2/2012)[/b][hr][quote][b]KWymore (2/2/2012)[/b][hr][quote][b]mtassin (2/2/2012)[/b][hr][quote][b]cengland0 (2/2/2012)[/b][hr]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.[/quote]Honestly these days for auditting purposes, I prefer CDC.[/quote]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).[/quote]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.[/quote]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.[/quote]It can if I store it in the table. :)Thu, 02 Feb 2012 08:17:56 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]mtassin (2/2/2012)[/b][hr][quote][b]KWymore (2/2/2012)[/b][hr][quote][b]mtassin (2/2/2012)[/b][hr][quote][b]cengland0 (2/2/2012)[/b][hr]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.[/quote]Honestly these days for auditting purposes, I prefer CDC.[/quote]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).[/quote]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.[/quote]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.Thu, 02 Feb 2012 08:15:20 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspx[quote][b]KWymore (2/2/2012)[/b][hr][quote][b]mtassin (2/2/2012)[/b][hr][quote][b]cengland0 (2/2/2012)[/b][hr]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.[/quote]Honestly these days for auditting purposes, I prefer CDC.[/quote]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).[/quote]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.Thu, 02 Feb 2012 07:59:23 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1245442-32-1.aspxGreat 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.Thu, 02 Feb 2012 07:49:13 GMTDana Medley