InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 06:44:14 GMT20http://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspx[quote][b]scott mcnitt (1/27/2010)[/b][hr]MAV query is a very useful techique. I am sure it is something I am doing wrong (or is there a database setting I am missing) but I only get one column in my output when I should get 117.[/quote]That's another problem with this technique: it is possible for a plan to be produced where the desired 'concatenation' doesn't work, and just one of the candidate values ends up in the variable. The query you posted happened to work 'correctly' on the instance I tested it with - but it is not guaranteed.Also, note that the ORDER BY clause in your query only applies to the final output of rows - it does not enforce an order for the multiple assignment operation.Wed, 27 Jan 2010 20:18:50 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspx[quote][b]pbarbin (1/27/2010)[/b][hr]Thanks for all the good information you post, but I have to chime in on this one. Personally, I use system views almost exclusively, but there is a good reason to use INFORMATION_SCHEMA views and that is for portability of code. They are the SQL-92 standard. And when have you ever been afraid to pile on too much? :-P[/quote]Ah...code portability. Pretty rare requirement in my experience, and there will likely be bigger challenges in porting to another database product than use of system views...but ok, it's a consideration I guess.When have I ever been afraid of piling on too much? Just the once it seems :laugh:Wed, 27 Jan 2010 20:07:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspx[quote]@PaulWhite said: Absoutely - and I was going to include a quote from BOL to make the point that system views are preferred over INFORMATION_SCHEMA...but I didn't want to pile on too much. I agree that it is rarely better to use anything other than the system views like sys.columns in this case. [/quote]Thanks for all the good information you post, but I have to chime in on this one. Personally, I use system views almost exclusively, but there is a good reason to use INFORMATION_SCHEMA views and that is for portability of code. They are the SQL-92 standard. And when have you ever been afraid to pile on too much? :-PWed, 27 Jan 2010 08:01:03 GMTpbarbinhttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxMAV query is a very useful techique. I am sure it is something I am doing wrong (or is there a database setting I am missing) but I only get one column in my output when I should get 117.declare @col_list varchar(4000), @tbl_name sysnameselect @tbl_name = 'Master', @col_list = ''--list all the columns firstselect '[' + column_name + ']' as col from information_schema.columns where table_name = @tbl_name--MAV queryselect @col_list = @col_list + col + ', ' from ( select '[' + column_name + ']' as col from information_schema.columns where table_name = @tbl_name) d order by colselect @col_listprint @col_list_________________________(117 row(s) affected)(1 row(s) affected)[WorkPaidByAnother],Wed, 27 Jan 2010 07:07:59 GMTscott mcnitthttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxCool stuff Steve - thanks for taking the time to write such a comprehensive answer - makes sense :-)Tue, 26 Jan 2010 23:12:15 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxPaul asks, @Steve McNamee: Are you saying that the technique used in the article is faster than FOR XML PATH for fewer than 500 elements? Not nit-picking; I just want to be clear on it. For me, the greater problem is that using variables in this way lacks an ORDER guarantee. Incorrect results, even when produced fractionally more quickly, are rarely to be desired No I'm not saying that; forgive my offhand remark. To clarify, about 6 months ago (before reading the great threads here about shredding), I was helping test one of my colleage's CSV-shredding CLR functions. I used a variant of this topic's technique to generate test data. When I lazily included the CSV generating code within the timing of the CLR, the results caused me to doubt the effectiveness of the CLR. I finally figured out that this technique, while handy and cool, can take a looong time to generate large CSV strings. The number 500 sticks in my head, but I don't have any evidence to back that up. For sure, when I got up to about 50,000 elements, the technique would not even return results before I got tired of waiting. That's when I found the XML technique to generate CSV strings, which did not seem to suffer from the same performance limitation. From an email I sent at the time:Last week, I thought I had found a performance limit in the CLR parsing function when parsing strings larger than about 30k elements. I was wrong about that. The offending code was the TSQL code I used the create the csv list that I then parsed using the function. With a @CSVList list of 515847 members, a select count(*) from the CLR function takes about a second.Tue, 26 Jan 2010 22:46:13 GMTstevemchttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspx@viacoboni: a construction using COALESCE is normally used, but in any case I can see no reason to prefer any variation of the method over the FOR XML solution (documented, supported, and faster...)@Bradley Deem: True - and I am a [i]great[/i] fan of appropriate CLR usage; however, FOR XML still performs faster than even a CLR UDA - so a call-out to the hosted CLR seems unnecessary...unless you have special requirements of course!@yaadman: Absoutely - and I was going to include a quote from BOL to make the point that system views are preferred over INFORMATION_SCHEMA...but I didn't want to pile on too much ;-) I agree that it is rarely better to use anything other than the system views like sys.columns in this case.@pbarbin: Yes - there are many better approaches to this sort of problem - and carl.anderson mentions another worthwhile optimization in a later post (checking row counts). HashBytes, CHECKSUM, the TableDiff utility...it is a long list.@Steve McNamee: Are you saying that the technique used in the article is faster than FOR XML PATH for fewer than 500 elements? Not nit-picking; I just want to be clear on it. For me, the greater problem is that using variables in this way lacks an ORDER guarantee. Incorrect results, even when produced fractionally more quickly, are rarely to be desired :-PGood discussion :w00t:Tue, 26 Jan 2010 21:20:15 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxIf the purpose of this script is to detect whether two tables with identical structure have different data, it might be nice to check the row counts before jumping into a potentially expensive comparison.Just a thought.[url=http://edw.northwestern.edu]Carl AndersonData Architect, Northwestern University[/url]Tue, 26 Jan 2010 12:17:21 GMTcarl.anderson-1037280http://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspx[quote]Now, in a previous post of mine discussing comparing tables using EXCEPT &lt;reference&gt;, I noted that it is best practice to explicitly name your columns in any query using EXCEPT.[/quote]I saw this in your previous article, but didn't respond to it there...I think you're wrong, and that this is one of the rare instances where it's best NOT to explicitly name your columns. If the column order is different between the two tables, then there may be other differences as well. Of course there can be other differences without the column order being different or the column order could be the only difference, but since it's simpler to use * and the failure provides you with useful information (that the structure of the two tables has branched at some point), that's what I'd use. Of course you may need to do the comparision in any case, in which case todays script can come in handy.Speaking of todays script, using @col_list = IsNull(@col_list +',' , '') + col prevents having to delete a trailing comma.Tue, 26 Jan 2010 11:50:57 GMTjohn.morenohttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxNice article. A minor nit-pick. You need to initialize the @col variable to an empty string befory running the MAV query. Otherwise, your result will be NULL.I've also used this for quick and dirty CSV list generation. For long lists, though, say over 500 elements, a FOR XML query, like Paul White's above, is much faster.Tue, 26 Jan 2010 10:12:23 GMTstevemchttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxThanks for the article, I use this "feature" quite a bit. One of mine is similar to yours, I have a script that creates insert statements using column names in order to reduce the size of our production database for testing purposes. We copy over only the records we want from the production db into an empty db, the process is much faster than deleting and resizing the db. I realize that your article is just for explanation purposes, but when I compare the contents of two tables that have the same structure, I use the CheckSum_Agg() and CheckSum() functions. You can start by comparing the entire tables with just one checksum value. If the values don't match, you can show the rows that differ by comparing each row's checksum with the checksum of the corresponding tables row (using primary keys to match rows). Syntax is:SELECT CheckSum_Agg(CheckSum(*)) FROM TableNameSELECT Checksum(*) FROM TableName WHERE PrimaryKey=1PaulTue, 26 Jan 2010 09:23:28 GMTpbarbinhttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxHi Stephen, I was wondering if there's a reason you use information_schema rather than the new sys.columns? Backwards compatibility? I believe in 2005 onwards information_schema is just a wrapper for the new object views (sys.columns, sys.objects, etc.)...Tue, 26 Jan 2010 08:56:44 GMTyaaadmanhttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxI can see where this could come in handly during ETL processes.In regards to creating a delimited list see the example here on MSDN [url]http://msdn2.microsoft.com/en-us/library/ms131056.aspx[/url]. Using a CLR it will create a comma delimited list. Since it's an Aggregate Function you can use it just like any other aggregate which makes it really handy.Tue, 26 Jan 2010 08:06:37 GMTBradley Deemhttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxA quick note on that trailing comma. Instead of [code]select @col_list = @col_list + col + ', '[/code]which leaves the trailing comma that must be taken off, you might try[code]set @col_list = null -- though defaults to null when defined...select @col_list = nullif(@col_list + ',', '') + col[/code]Tue, 26 Jan 2010 08:01:39 GMTviacobonihttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxNot only is it important to name columns (as opposed to using '*') - it is important to name them and return them in the same order!Nothing in the 'MAV' query [i]guarantees[/i] the order of column names returned. Use FOR XML PATH with an explicit ORDER BY clause instead (it's faster and entirely documented):[code]SELECT STUFF( ( SELECT N',' + sc.name FROM sys.columns sc WHERE sc.[object_id] = OBJECT_ID(N'dbo.Widget', N'U') ORDER BY sc.column_id ASC FOR XML PATH(''), TYPE ).value(N'.[1]', 'NVARCHAR(MAX)'), 1, 1, '');[/code]Tue, 26 Jan 2010 02:58:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic853414-2596-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL/69047/">Using &quot;Multiple Assignment Variables&quot; for a Generic Compare Data Script</A>[/B]Mon, 25 Jan 2010 21:47:22 GMTzintp