Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The ARRAY In SQL Server 2000 Expand / Collapse
Author
Message
Posted Friday, May 6, 2005 11:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:19 PM
Points: 27, Visits: 311
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp
Post #180627
Posted Monday, May 16, 2005 8:14 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:34 AM
Points: 6,259, Visits: 2,031

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
Post #182662
Posted Monday, May 16, 2005 9:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2008 7:53 AM
Points: 4, Visits: 10

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

Post #182694
Posted Monday, May 16, 2005 2:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 17, 2010 4:18 PM
Points: 360, 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

 

Post #182745
Posted Monday, May 16, 2005 2:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
because @tbl must be a declared table variable.
Post #182748
Posted Monday, May 16, 2005 3:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 3, 2012 8:34 AM
Points: 2, 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

Post #182754
Posted Monday, May 16, 2005 3:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 17, 2010 4:18 PM
Points: 360, 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

Post #182755
Posted Monday, May 16, 2005 3:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623

Select * from @tbl T INNER JOIN Products p ON T.ItemName = Products.ProductName
Post #182756
Posted Thursday, May 19, 2005 11:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 21, 2009 10:33 AM
Points: 50, 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")

 




Post #183803
Posted Thursday, May 19, 2005 5:29 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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!




Post #183957
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse