SQLServerCentral / Discuss Content Posted by Alex Grinberg / Article Discussions / Article Discussions by Author / The ARRAY In SQL Server 2000 / Latest Posts

RE: The ARRAY In SQL Server 2000

scott.sisson — Tue, 19 Feb 2008 11:14:30 GMT

I found this article very timely for me. I have a SQL function that does basic Proper casing of names and some matching criteria for the hundreds (eventually thousands I am guessing) of exceptions. In processing 18,197 rows with 6 calls to the function per select statement to change potential names the CURSOR ran 2x as fast as the "ARRAY". After reading the article, I am quite confused by this. Below is the code with the cursor select statement select name1, dbname.dbo.proper(name1)... to name6 ... set up and simple exlcusion stuff DECLARE properex CURSOR FAST_FORWARD FORSELECT Oldval, Cvtval FROM Proper_exceptionswhere loc = 'M' order by sort OPEN properexFETCH NEXT FROM properexINTO @old, @new-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN select @outputstring = replace(@outputstring,@old,@new) -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM properex INTO @old, @newENDCLOSE properexDEALLOCATE properex-- the table variable ... same set up and simple exclusion stuff DECLARE @tbl TABLE( RowID INT IDENTITY(1, 1), ov VARCHAR(100), nv VARCHAR(50))/*Local variables */DECLARE @old VARCHAR(100), @new VARCHAR(50), @ctr int, /*create local @@fetch_status*/ @cRow int /*row pointer (index)*//* create array simulator */INSERT @tbl (ov,nv)SELECT Oldval, Cvtval FROM Common.dbo.Proper_exceptionswhere loc = 'M' order by sort set @ctr = @@ROWCOUNTset @cRow = 1 WHILE @cRow <= @ctrBEGINselect @old = ov, @new = nv from @tbl where RowId = @cRow select @outputstring = replace (@outputstring,@old,@new) set @cRow = @cRow + 1END-- the current size of the table is only 310 rowsafter post comment - I should have read all the posts before adding mine. Sounds like cursors are more efficient in this case. I would appreciate other suggestions to my issue at hand. In try to proper case words - I already have a basic upper case first letter lower case the rest. I need an efficient way to loop through all my other criteria. For example for the name JOHN SMITH IV, CEO, CFO after the first pass would look like John Smith Iv Ceo, Cfo. I have a table with 'M' atching values of _ = blanks part of list of 300 match replacements _ceo_ = _CEO_ _cfo_ = _CFO_ _Iv_ = _IV_ After passing thru all the replacements the final word is John Smith IV CEO, CFOIs there an easier way to process all of the potential replacements. Using the like statement I have filtered out all potential replacement words that do not have a letter match for the given word. For example the like clause for the example shown is %[johnsmitvcef]%.

RE: The ARRAY In SQL Server 2000

Jeff Moden — Mon, 18 Feb 2008 18:03:04 GMT

It seems to me that the real big difference between a mere table and an array, are the array indicies themselves... You know, the stuff where you can say IF A(2,3) = X THEN... where A(2,3) would be the row two, column three of an array called "A". Would that be true in most of your eyes?

RE: The ARRAY In SQL Server 2000

Eric Wilson — Mon, 18 Feb 2008 17:53:18 GMT

[quote][b]Ed Sanford (2/18/2008)[/b][hr]Just wanted to add that my main purpose for wanting to use this Array logic is to be able to pass selection lists to a query. ... [/quote]I agree with the intent (and do the very same thing myself). But wouldn't you agree that it would be simpler AND more powerful to have more fully-implemented "table" support?We should be able to have table variables (client and server), specify table values (whole tables) as literals, do equality comparisons, etc. Just like A REAL TYPE.Then we could just pass a table as an argument.Since the industry gave us XML instead (sigh!), I use that as a work-around too.As to the "textual encoding" of it, I agree it should be somewhat terse. But that is not to be confused with the "value" of the parameter. For example, both of these "encodings" could be used to specify the same "value":[code]<searchtypes><type name="green"></type><type name="blue"></type><type name="purple"></type></searchtypes>[/code]Or...[code]searchtypes ( string name ) { green; blue; purple;}[/code]These are both the same "value" semantically. I prefer the second encoding and it's too bad we got XML instead.

RE: The ARRAY In SQL Server 2000

Ed Sanford — Mon, 18 Feb 2008 14:19:28 GMT

Just wanted to add that my main purpose for wanting to use this Array logic is to be able to pass selection lists to a query. Although my current method (when using ColdFusion anyway) is to pass list parameters ( they get turned in to long lists of bind parameters); but, that doesn't work well if I want to have the query in a stored procedure, or function call; and, besides, it would be nice to be able to logically pass a large list as one parameter, not a 100 or 1000 of them.Passing the list data as XML seems logical; although, it would be nice if there was a less bloated format to use (CSV would be nice).I suppose I could keep the path simple like root/v

RE: The ARRAY In SQL Server 2000

Ed Sanford — Mon, 18 Feb 2008 14:04:16 GMT

FYI ... I tried creating a function to return a pre-structured table from xml text, and found that sql 2005 would not allow it to function, complaining that only extended stored procs could be called from within a function. Even though sp_xml_preparedocument is listed as an extended stored proc, it apparently is seen as not extended; so, the prepare and remove calls have do be done outside of the function call.

RE: The ARRAY In SQL Server 2000

Eric Wilson — Mon, 18 Feb 2008 11:54:50 GMT

[quote][b]dmbaker (2/18/2008)[/b][hr][quote][b]Eric Wilson (2/18/2008)[/b][hr]With due respect, I'm afraid you missed [b]my[/b] point. One (major) goal of the Relational Model was to be as uncluttered as possible. To that end, relations (a.k.a. tables) provide all the necessary complexity we need. Arrays, or any other data structure, only [i]add complexity without adding additional power![/i] This leads to arbitrariness of solutions and additional confusion between the logical and physical layers. No good can come of it.[/quote]{ snip }Anyway, I really don't disagree with you. I think you nailed it when you said tables "...are more powerful (if fully implemented...)". If we were able to toss tables around in TSQL just like any other datatype, that would certainly go a long way.[/quote]Yes, seems we agree after all. My point was, as long as we (the DB developer community) are asking vendors for features, let's all please ask for [i]more fully implemented table support[/i] that is rich and robust! That would give us far more power than Arrays and has the additional bonus of being completely consistent with the existing model of what a relational DB is to be.

RE: The ARRAY In SQL Server 2000

dmbaker — Mon, 18 Feb 2008 11:02:10 GMT

[quote][b]Eric Wilson (2/18/2008)[/b][hr]With due respect, I'm afraid you missed [b]my[/b] point. One (major) goal of the Relational Model was to be as uncluttered as possible. To that end, relations (a.k.a. tables) provide all the necessary complexity we need. Arrays, or any other data structure, only [i]add complexity without adding additional power![/i] This leads to arbitrariness of solutions and additional confusion between the logical and physical layers. No good can come of it.[/quote]I'm not arguing that relations are more or less powerful than arrays, in fact I fully agree that arrays are a poor substitute for relations -- that's a no-brainer. I wasn't suggesting that they *are* a substitute for relations. I was suggesting that the array is an incredibly useful feature in modern programming languages that is wholly missing from T-SQL, and one that, as T-SQL *is now implemented*, would be quite useful to solve a number of problems that we've had to hack around for many years.As a programmer, I most certainly *do not* use a different solution simply "because I can". I look for what I believe to be the best solution to a given problem, and preferably look for a solution that someone else has already come up with. More "options" does not necessarily mean "more complexity", but more options just might give me the *option* to deliver a better solution. And delivering the "best" solution is my job (whatever the definition of "best" may be for the task at hand).Anyway, I really don't disagree with you. I think you nailed it when you said tables "...are more powerful (if fully implemented...)". If we were able to toss tables around in TSQL just like any other datatype, that would certainly go a long way.

RE: The ARRAY In SQL Server 2000

Eric Wilson — Mon, 18 Feb 2008 10:10:15 GMT

[quote][b]dmbaker (2/18/2008)[/b][hr][quote][b]Eric Wilson (2/14/2008)[/b][hr]Regarding the overall article's topic:The only possible reason to want an ARRAY type is because of assumptions of how the data will be *physically* organized. But we should have simply TABLES (including in-memory tables, table variables, etc.) and have some control over physical-level implementation concerns like whether it's -- under the hood -- sorted in a particular way. Those physical details do not impact the logical ways we interact with the table--which is in fact one of the points![/quote]I think you're missing the point. An array is a programming construct, not a relational one. I think it's pretty safe to say that all modern programming languages support arrays, it's not unreasonable to desire that T-SQL be brought into the 20th (sorry, I mean the 21st) century and offer support for this fundamental programming construct.Of course, it could be argued that we do have it now, with CLR integration in SQL Server (and also ability to pass relational structures between client and server, a.k.a. the DataSet). But that's a whole other can of worms.[/quote]With due respect, I'm afraid you missed [b]my[/b] point. One (major) goal of the Relational Model was to be as uncluttered as possible. To that end, relations (a.k.a. tables) provide all the necessary complexity we need. Arrays, or any other data structure, only [i]add complexity without adding additional power![/i] This leads to arbitrariness of solutions and additional confusion between the logical and physical layers. No good can come of it.Most programming languages [i]do not[/i] have this "minimalist" goal. And a lot of the complexity I've seen over the years can be attributed to this: different programmers use arbitrarily different solutions because--well--they can. But it doesn't mean that's a good thing. More "options" or complexity does not mean "better."Including things into an otherwise cleanly constructed system, just because it's common elsewhere is, frankly, a really dumb reason to do it. Show me the extra power (semantically) that Arrays give me over tables, and I'll be sold. But I'm certain no one can because tables are more powerful (if implemented fully in the system--which admittedly is not the case in most systems today).Before asking for new features in an RDBMS, people really owe it to themselves to understand the goals of such a system in the first place. Asking for Arrays undeniably indicates ignorance of the relational model.

RE: The ARRAY In SQL Server 2000

dmbaker — Mon, 18 Feb 2008 07:11:07 GMT

Wow, sorry for the multiple posts, not sure how that happened.

RE: The ARRAY In SQL Server 2000

dmbaker — Mon, 18 Feb 2008 07:09:31 GMT

[quote][b]Eric Wilson (2/14/2008)[/b][hr]Regarding the overall article's topic:The only possible reason to want an ARRAY type is because of assumptions of how the data will be *physically* organized. But we should have simply TABLES (including in-memory tables, table variables, etc.) and have some control over physical-level implementation concerns like whether it's -- under the hood -- sorted in a particular way. Those physical details do not impact the logical ways we interact with the table--which is in fact one of the points![/quote]I think you're missing the point. An array is a programming construct, not a relational one. I think it's pretty safe to say that all modern programming languages support arrays, it's not unreasonable to desire that T-SQL be brought into the 20th (sorry, I mean the 21st) century and offer support for this fundamental programming construct.Of course, it could be argued that we do have it now, with CLR integration in SQL Server (and also ability to pass relational structures between client and server, a.k.a. the DataSet). But that's a whole other can of worms.

RE: The ARRAY In SQL Server 2000

dmbaker — Mon, 18 Feb 2008 07:09:24 GMT

RE: The ARRAY In SQL Server 2000

dmbaker — Mon, 18 Feb 2008 07:07:43 GMT

RE: The ARRAY In SQL Server 2000

Eric Wilson — Fri, 15 Feb 2008 12:56:00 GMT

By the way, regarding an earlier comment that you cannot Index table variables...1) True, technically, and another example of the failure to separate logical and physical issues. (Tables should behave as Tables, regardless...)2) False, in practice. You can't "create index" on one. But you *can* create additional UNIQUE constraints on the table-variable at declaration time. This is implemented as an index, and queries (in my tests) will actually use them just like on a real table.And any UNIQUE constraint that *also* includes the PK, is guaranteed to be unique. Just put the "thing you want to index" first in the list, so it's more selective. (The extra PK part ends up being wasteful, but oh well.)I was able to drop a significantly complex scenario from about 10-seconds to about 3-seconds by placing two additional UQ constraints on the table-variable.

RE: The ARRAY In SQL Server 2000

John Novak-439283 — Fri, 15 Feb 2008 12:28:13 GMT

I tried the same comparisons with a real-world example, a data table with over 200,000 rows. In the first iteration the cursor method took 15 seconds, while I killed the 'array' method after 15 minutes after only a small fraction of the rows had been processed. The next time around I added a PRIMARY KEY constraint to the identity field and this did the trick. The time for the array method dropped to 8 seconds. Kind of a key omission, I'd say...

RE: The ARRAY In SQL Server 2000

Calvin Lawson — Fri, 15 Feb 2008 12:18:26 GMT

This article is over 2 1/2 years old, and it generated some controversy the first time around. So why is it a "featured article" in today's newsletter?Steve, do you guys need some writers?

RE: The ARRAY In SQL Server 2000

DaveC-635269 — Fri, 15 Feb 2008 04:28:04 GMT

The example coding in the SQL Cursor SP is incorrect as it displays the last record twice. This is really bad programming in that the fetch status should be checked immediately after it is retrieved in order to exit the loop: declare @OK bit -- Get first row open mycursor /*get row values*/ set @OK = 1; while @OK = 1 BEGIN /*get row values*/ FETCH MyCursor INTO @CompName, @ContName if @@fetch_status <> 0 set @OK=0 else begin /*perform operations with single row*/ PRINT 'T_SQL cursor row | ' + @ContName + ' | ' + @CompName end END

RE: The ARRAY In SQL Server 2000

mioux — Fri, 15 Feb 2008 03:51:52 GMT

Very nice articleNow I know how to solve my problem : "SELECT * FROM table WHERE column IN @var". I should have test a lot of thing without finding the solution :Dthanks for the idea

RE: The ARRAY In SQL Server 2000

SQLZ — Fri, 15 Feb 2008 02:56:06 GMT

I've taken the two procedure examples you supplied and the stored procedure with the cursor runs faster than your example using the table variable. I had to change the tables to HumanResources.Employee in AdventureWorks. To process just over 250 rows it takes twice as long (406 ms) using the table variable method.In fact, if you increase the number of rows to 10,000 it takes 15 seconds using the table variable method as opposed to 2 seconds using the cursor. That's a no brainer to me, stick with the cursor (in this particular case anyway).I'd carefully think twice about using table variables in this manner.

RE: The ARRAY In SQL Server 2000

Eric Wilson — Thu, 14 Feb 2008 23:42:16 GMT

Regarding the overall article's topic:We DO NOT NEED ARRAYS!To use XML and Table Variables to try to "emulate" arrays is like grabbing your neighbor's Ferrari to try and emulate a Toyota. Very WRONG thinking!(Note: I use XML or delimited lists myself in similar manners: viz. to WORK AROUND SQL Server's pathetic lack of support for true table variables.)Please go back and read *anything* about the Relational Model and why it came about. Relations (tables) are vastly superior to arrays and are meant to be a *logical* structure. (That is, independent of how they might be implemented under the hood for performance reasons.) It was meant to give the minimum complexity necessary while providing all the necessary expressive power. ARRAYs do not add any power, only complexity.The only possible reason to want an ARRAY type is because of assumptions of how the data will be *physically* organized. But we should have simply TABLES (including in-memory tables, table variables, etc.) and have some control over physical-level implementation concerns like whether it's -- under the hood -- sorted in a particular way. Those physical details do not impact the logical ways we interact with the table--which is in fact one of the points!What WE DO NEED is better support for actual relation values, and relation variables. (That is "table values" and "table variables.") We should be able to define arbitrarily complex tables, pass them between client and server, state literal table values (as whole things) inline in code, etc. etc. etc. Just like we can with an INT or a FLOAT. Then we have more POWER than arrays can give, and we have a consistent model across the board instead of introducing yet another type with its own structures--that is more LIMITED.Please explain this to any of the ignorante who keep clamoring for array types!(And please push MS and others to implement better table-variable support. Even SQL 2008's new table variable type is woefully short of the goal.)

RE: The ARRAY In SQL Server 2000

Jeff Moden — Wed, 30 Jan 2008 05:59:39 GMT

[quote][b]bonk (1/29/2008)[/b][hr]Hi Jeff!I agree with you! But there's nothing to do in this case. I explain: the table with one column for each month already exists and I can't modify its structure.I'm working in a BI (Businness Inteligence) Project and I have to import data from diferent databases to my star-schema modelling database (better for BI projects) and in this kind of project, you can find all sort of stuffs. :( I'm a system victim... :)See you,Bonk[/quote]In that case, we'd need a wee bit more information... you said you wanted to read a 12 month record from one table and put it in another... you'd need to post the CREATE statement from each table and possibly some data that we could play with to make sure we get it right. Take a look at the following URL before you start that to get the best results...[url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

RE: The ARRAY In SQL Server 2000

bonk — Tue, 29 Jan 2008 10:30:11 GMT

Hi Jeff!I agree with you! But there's nothing to do in this case. I explain: the table with one column for each month already exists and I can't modify its structure.I'm working in a BI (Businness Inteligence) Project and I have to import data from diferent databases to my star-schema modelling database (better for BI projects) and in this kind of project, you can find all sort of stuffs. :( I'm a system victim... :)See you,Bonk

RE: The ARRAY In SQL Server 2000

Matt Miller (#4) — Mon, 28 Jan 2008 14:07:28 GMT

If it helps you at all bonk - a table IS a persistent 2-dimension array. One that happens to have the ability to perform parallel operations on its members at the rate of oh - several million at a time. One you don't have to keep populating and tearing down, etc....Using a loop on a SQL table is like buying a Mercedes for its ashtray: an awful waste of resources, and isn't good for your health....

RE: The ARRAY In SQL Server 2000

Jeff Moden — Mon, 28 Jan 2008 13:59:33 GMT

[quote]I would like to know how can I read a record from a table with 12 columns (each on for each month in a year) end put the value for each mounth into an another table using this table variable?[/quote]This won't be quite the answer you expected, but typically, you WOULDN'T/SHOULDN'T make a table that has values for each month in different columns... kinda defies the whole idea of a relational database.

RE: The ARRAY In SQL Server 2000

bonk — Mon, 28 Jan 2008 10:53:36 GMT

HiI would like to know how can I read a record from a table with 12 columns (each on for each month in a year) end put the value for each mounth into an another table using this table variable?With arrays I would:select arr_value[1] = vt.jan_value, arr_value[2] = vt.feb_value, arr_value[3] = vt.mar_value, ...arr_value[12] = vt.dec_valuefrom value_table vtAnd then I would make a WHILE to put each array position in a record of that another table.I can't see a flexible solution whith Table-variables like I would have with arrays.Someone?Thanks,Bonk

RE: The ARRAY In SQL Server 2000

Ninja's_RGR'us — Wed, 02 Aug 2006 08:29:00 GMT

Repost :

Set based is faster that procedural :

IF Object_id('fnSplit_Set') > 0DROP FUNCTION dbo.fnSplit_SetGOIF Object_id('Numbers') > 0DROP TABLE dbo.NumbersGOCREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))GOINSERT INTO dbo.Numbers (dude) SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80GOALTER TABLE dbo.NumbersDROP COLUMN dudeGO--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers--1, 8000GOCREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3)) RETURNS TABLEWITH SCHEMABINDINGAS ReturnSelect dtSplitted.EachID, dtSplitted.Rank from (SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS RankFROM dbo.Numbers NWHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiterAND PkNumber < LEN(@vcDelimiter + @IDs + @vcDelimiter)) dtSplitted where len(dtSplitted.EachID) > 0GODECLARE @Ids as varchar(8000)SET @IDS = ''Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIdsSET @IDS = left(@IDS, ABS(len(@IDS) - 1))PRINT @IDS

This is what your queries will look like using the set split function:

Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.NameSelect O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.

RE: The ARRAY In SQL Server 2000

Daniel-278461 — Wed, 02 Aug 2006 08:18:00 GMT

I've been using this technique to allow multiple values to be passed to an SP for use within an IN search condition and it works really nicely.

However I'm a bit curious about the performance. I've been tuning a few queries and viewing the trace in query analyser and the step needed to get the values into a temp table seems to be taking up quite a high proportion of the query cost (nearly 20% of the whole procedure). Considering that this only involves doing a bit of string manipulation and putting up to 10 values into a temporary table, this seems a little high.

This can be recreated using the code below. Is this cost just part of the process of creating the temporary table?

I'm mainly surprised by the number of reads that are shown in the trace window. Are these disk reads or cache reads?

1. Create UDF used to convert delimited string to table

CREATE FUNCTION dbo.SplitInt( @List varchar(8000), @SplitOn nvarchar(5)) RETURNS @RtnValue table ( Id int identity(1,1), Value int) AS -- Summary: Returns table with single "Value" column, containing integer values from a delimited string of integer valuesBEGIN WHILE (Charindex(@SplitOn,@List)>0) BEGIN

Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) END

Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List))

ReturnEND

2. Run the following script:

-- Adding delimited values to another temporary table with primary keyDECLARE @tblValues TABLE( Value int NOT NULL PRIMARY KEY CLUSTERED)INSERT INTO @tblValues (Value)SELECT DISTINCT Value FROM dbo.SplitInt('1,2,3,4,5,6,7',',')

-- Selecting delimited valuesdirectly from functionSELECT Value FROM dbo.SplitInt('1,2,3,4,5,6,7',',')

RE: The ARRAY In SQL Server 2000

Jeff Moden — Thu, 02 Feb 2006 18:16:00 GMT

Here, here! I second the thought!

RE: The ARRAY In SQL Server 2000

Ninja's_RGR'us — Thu, 02 Feb 2006 14:22:00 GMT

Good point to recall. Always validate client side first. Then revalidate on the server to be 100% sure. But I'd still go with a single transaction update using a temp table. This is actually a classic case of a good use for temp tables.

RE: The ARRAY In SQL Server 2000

Calvin Lawson — Thu, 02 Feb 2006 12:54:00 GMT

Rachel,Yeah, a staging table of some sort is ideal for this. I'm assuming you want all changes in the same transaction, so that if one change fails due to constraints then all changes fail. Staging the data before updating allows you to validate it, and gracefully exit the update if you see that constraints are going to be violated. You certainly don't need a SQL array; that's what you use the staging table for, and you validate the data with SQL statements. If you wanted to use an array, you'd do your validation on the client side (which may be even better than using SQL, actually), which I'm sure would support arrays.

RE: The ARRAY In SQL Server 2000

Ninja's_RGR'us — Thu, 02 Feb 2006 09:03:00 GMT

I personally hate XML because it's too bloated so I'd go with the temp table approach. I don't think you have any other options in this case. Anyways this wouldn't hit the server a lot since this task wouldn't be ran 1000s of times/hour.

RE: The ARRAY In SQL Server 2000

Rachel Byford — Thu, 02 Feb 2006 08:55:00 GMT

I was wondering whether this is actually the solution to a problem I have, or whether it would be too inefficient. If anyone has any clever suggestions, then please let me know.

I have an application which allows the user to enter changes to a list of address records, and when they click "OK" the changes are submitted to the database by calling a stored once for every row which has been modified. The problem is that there is a description field which is unique (for the selected company), and if the user changes the description of one, then reuses the old description on another address, sometimes there is a violation of the unique constraint - depending on which order the rows happen to be updated in. I cannot delete and recreate the address records, because there are lots of other records which reference them.

One solution (I think) would be to try and send details of all the records changed to a stored procedure in one of these arrays, so that this can be parsed into a temporary table of some kind, and then the update can be made from this table. This would have the advantage of being able to make all of the updates in a single network trip, but it'd be a bit of a palaver getting the changes into the XML in the first place, and if this method is as bad as everybody says...

The only other way I can think of would be to use a temporary table, and have one stored procedure which would send the changed records to the temporary table, and another which would fire the update from this temporary table. This doesn't seem ideal either.

Does anyone have any bright ideas?

Rachel.

RE: The ARRAY In SQL Server 2000

Ian Yates — Tue, 31 Jan 2006 15:20:00 GMT

So is that saying that if I have 80MB of SQL Server memory allocated then it will use 10MB of RAM and if I have 800MB of SQL Server memory allocated then it will use 100MB of RAM? Seems a bit off to me!?

Perhaps it can use UP to 1/8th of the allocated RAM? But I may be wrong

RE: The ARRAY In SQL Server 2000

JohnMarks — Tue, 24 Jan 2006 14:46:00 GMT

Each of the methods mentioned in the article has its place in a DBA's tool box. I have used the XML approach many times with good performance but it is important to test each method to see what works best in your own environment.An important point to note when using sp_xml_preparedocument is that it supposedly uses 1/8th the total memory on the server. I would not use it in a sproc that is going to be called very frequently.From BOL:sp_xml_preparedocument returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the connection to Microsoft® SQL Server™ 2000, until the connection is reset, or until the handle is invalidated by executing sp_xml_removedocument.Note A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

RE: The ARRAY In SQL Server 2000

Ninja's_RGR'us — Mon, 23 Jan 2006 08:11:00 GMT

--create all objectsIF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U' and user_name(uid) = 'dbo') DROP TABLE NumbersGOCREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)GOINSERT INTO dbo.Numbers (dude) SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80GOALTER TABLE dbo.NumbersDROP COLUMN dudeGOCREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3)) RETURNS TABLEWITH SCHEMABINDINGAS Return Select dtSplitted.EachID, dtSplitted.Rank from ( SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter), CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID , (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank FROM dbo.Numbers N WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter AND PkNumber < LEN(@vcDelimiter + @IDs + @vcDelimiter) ) dtSplitted where len(dtSplitted.EachID) > 0GO--end creation part--start testing--generate a list of idsDECLARE @Ids as varchar(8000)SET @IDS = ''Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIdsSET @IDS = left(@IDS, ABS(len(@IDS) - 1))PRINT @IDS--use in a querySelect O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.NameSelect O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.

RE: The ARRAY In SQL Server 2000

sheepoo — Mon, 23 Jan 2006 07:19:00 GMT

I would be interested to know the set-based solutions without String parsingThanks

RE: The ARRAY In SQL Server 2000

Chris Hedgate — Mon, 23 Jan 2006 03:07:00 GMT

Using XML to pass in a 'list' of multiple values to use as a search argument is a very bad decision resource wise. There are set based solutions that can split a comma-separated list (with no procedural string parsing) much more effectively so that you do not need to a) pass way to large strings (XML is BLOATED) and more importantly b) use system resources to parse XML.

RE: The ARRAY In SQL Server 2000

Chris Hedgate — Mon, 23 Jan 2006 03:04:00 GMT

Note that decisions to use cursors, temp tables and table variables should not only be based on the duration required to run a single process using them. The real cost is in scalability, not performance. Of course the real problem is using row-by-row processing instead of a set-based solution.

RE: The ARRAY In SQL Server 2000

Jeff Moden — Sat, 21 Jan 2006 20:10:00 GMT

The author of the article wrote: "but using temporary table you are increasing disk activity since TV (Table Variable) is created in memory as opposed to Temp tables (created in TempDb)."

Now we know how rumors and myths get started. I'd like to suggest that you do just a little more research... Please refer to the Microsoft web site which states the following...

Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

The website is:http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

On top of that, if the table variables are large, the following comes into play (from the same website)...

Q3: What are some of the drawbacks of table variables?A3: These are some of the drawbacks as compared to temporary tables:

•	Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
•	Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

And, yes, I agree that cursors should be replaced but NOT that way. Show me the resource differences between a "firehose" cursor and the table variable/While loop method... I think you'll be surprised how little the difference is...

RE: The ARRAY In SQL Server 2000

Calvin Lawson — Fri, 20 Jan 2006 12:21:00 GMT

Pretty well written article, but I'm confused. I thought "Arrays" where called "Tables" in SQL Server. Why do people keep saying SQL server doesn't support arrays? Maybe it's correct to say SQL Server doesn't support multi-dimensional arrays...It seems like it's usually in reference to passing in multiple values to a stored procedure, or something along those lines. OK, that's sort of fair, if you're thinking of input parameters. However, in you think about it differently, there is another way of "passing" data into a stored procedure, and that's by storing it in a table. That's SQL programming right there.And what do cursors have to do with arrays, besides the fact that arrays are usually "looped" through? In SQL, you can access all or some members of the array without looping! It's called set based processing. Tables don't live in memory necessarily, and that does make them different than arrays. But I might add that TV and TT don't either.So maybe it's a paradigm problem.

RE: The ARRAY In SQL Server 2000

TDuffy — Fri, 20 Jan 2006 11:46:00 GMT

ONe more advantage to TVs over temp tables is the optmizers ability to use the TV in determining an execution plan, where often it can not do so with a temp table.

I'm sure we have all had code with a temp table, and displaying the execution plan returns an error because the temp table does not exist. Replacing the temp tables with TVs (where possible) solves the issue, and greatly improves performance of the query.

Terry