SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The ARRAY In SQL Server 2000


The ARRAY In SQL Server 2000

Author
Message
Alex Grinberg-230981
Alex Grinberg-230981
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 414
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp
noeld
noeld
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12364 Visits: 2048

Nice Article by the way I would like to correct some comments in it though

>> A similar process is required for TV, but all operations are conducted 100% in memory <<

That statement is not completly correct.

FYI :http://support.microsoft.com/default.aspx?scid=kb;en-us;305977

hth




* Noel
Tom Andersen
Tom Andersen
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 10

Interesting reading. Thank you for taking the time to share the ideas.


Sql Junkie-204042
Sql Junkie-204042
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 168

Good article.

Why do I get an variable declaration error with this statement -

Select * from @tbl INNER JOIN Products p ON @tbl.ItemName = Products.ProductName


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37151 Visits: 9671
because @tbl must be a declared table variable.
Rick Schulte
Rick Schulte
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 102

Good article, here is an variation that I use ...


Declare
@SQL Varchar(200),
@Array Varchar(200),
@Tic Varchar(8),
@Field Varchar(25)
Set @Array = ''
--Set @Field = 'SupplierId'
--Set @Tic = '' -- for nbrs
Set @Field = 'ContactName '
Set @Tic = '''' -- for chars
Select @Array = @Array + @Tic+ fldName +@Tic+','
From (Select cast(ContactName as Varchar(50)) as fldName -- enter @field name
FROM Suppliers WHERE ContactName LIKE 'c%') as s
Group by fldName
--Print @Array
If len(@Array) > 0
Begin
Set @Array = left(@Array,len(@Array)-1)
Set @SQL = 'Select CompanyName from Suppliers where '+@Field+' In ('+@Array+')'
--print @SQL
Exec(@SQL)
End


Sql Junkie-204042
Sql Junkie-204042
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 168

In reply to Remi, here is the full code. I still get an error.

Declare @tbl table (RowId int identity(1,1), ItemName varchar(100))


Insert @tbl
Select ProductName
FROM Products
WHERE ProductName like 'M%'

Select * from @tbl INNER JOIN Products p ON @tbl.ItemName = Products.ProductName


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37151 Visits: 9671
Select * from @tbl T INNER JOIN Products p ON T.ItemName = Products.ProductName
jgoodwin
jgoodwin
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 10

I'd be intereseted to see some actual performance results. In my mind, it doesn't make sense that a table variable used in this fasion is faster then a cursor. A table variable adds the over head of having to create the table with an additional column and then select into it before you can even get started.

I do see the value of a table variable in being able to pass it to a store procedure, and the more limited scope and clean up, but I still use temp tables (mainly because it is easier to use the "insert into #mytemp exec my_sp")





Ian Yates
Ian Yates
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 445

From memory, many cursors (depending on how they are declared) use temp tables to provide the forward/backward functionality anyway.
Also, table variables are not always entirely in memory - they too use tempDB, but have a much more tightly defined scope and so can be managed by SQL more efficiently and may be able to stay in memory - as you've said

I sometimes still use temp tables in a stored proc when it is a stored proc that returns Iseveral result sets, or performs many calculations, all relying on the one temp table and accessing / joining on several of its columns - it is more efficient to be able to create a clustered index and other indices on the temp table and suffer its recompilation overhead than it is to have slow performing queries using table scans. I've only made use of it for this reason a couple of times - I usually stick to a table variable and then ALWAYS check out the query plan for anything that is more than a couple of lines (have cut running times from several mins to several seconds by making the switch from variable to temp table)


As an aside, if I need to pass many values (eg an array of ints) to a stored proc from my application, I pass them in a comma-separated varchar(8000) - seems to work a treat. I have a UDF to parse the string and return a table. I suppose that XML would also work, but seems a bit too much overhead for such a simple task!

My 2c - cheers! Ian

PS - nice article - I like the ones that generate plenty of discussion!





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search