Using a MEMORY table as an array

,

I liked the script of using a table as an array. But Didn't liked the cursor in it so I rewrote the script so that it uses a memory table.

I tested it in the Query analyzer and this one is indeed faster. So all the people out there yelling that a memory table is faster than a cursor could be right.....

I say could be because the memory table variant has less statements in it.......

SET NOCOUNT ON
DECLARE @ARRAY TABLE (K1 INT IDENTITY, ARRAYCOL VARCHAR(8000))

INSERT @ARRAY (ARRAYCOL) VALUES ('LES PAUL       '+
    			      	 'BUDDY GUY      '+
			      	 'JEFF BECK      '+
				 'JOE SATRIANI   ')
INSERT @ARRAY (ARRAYCOL) VALUES ('STEVE MILLER   '+
			      	 'EDDIE VAN HALEN'+
				 'TOM SCHOLZ     ')
INSERT @ARRAY (ARRAYCOL) VALUES ('STEVE VAI      '+
				 'ERIC CLAPTON   '+
			      	 'SLASH          '+
				 'JIMI HENDRIX   '+
				 'JASON BECKER   '+
				 'MICHAEL HARTMAN')

DECLARE @I INT, @L INT 
DECLARE @CURREC INT, @MAXREC INT
DECLARE @ARRAYVAR VARCHAR(8000)

SET @CURREC = 0
SELECT @MAXREC = MAX(K1) FROM @ARRAY
WHILE @CURREC  < @MAXREC
 BEGIN
  SET @CURREC = @CURREC + 1
  SELECT @ARRAYVAR= ARRAYCOL FROM @ARRAY WHERE K1 = @CURREC
  SET @I = 0
  SET @L=DATALENGTH(@ARRAYVAR)/15
  WHILE (@I<@L) BEGIN
    SELECT 'GUITARIST'=SUBSTRING(@ARRAYVAR,(@I*15)+1,15)
    SET @I=@I+1
  END
END

Rate

Share

Share

Rate