Arrays in SQL SERVER 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Arrays in SQL SERVER 2000

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-09-29 : 14:55:20

I am using this code to build an array in SQL SERVER 2000. My question is that when I access any element from the array like element[300] will it take one access to reach that element or will it go through 0-299 and and find the 300th element.

CREATE TABLE #array(k1 int identity, arraycol varchar(8000))

INSERT #array(arraycol) VALUES('LES PAUL '+
'BUDDY GUY '+
'JEFF BECK ')

INSERT #array(arraycol) VALUES('STEVE MILLER '+
'TOM SCHOLZE '+
'EDDIE MURPHY ')
INSERT #array(arraycol) VALUES('STEVE MILLER '+
'TOM SCHOLZE '+
'EDDIE MURPHY ')
INSERT #array(arraycol) VALUES('STEVE MILLER '+
'TOM SCHOLZE '+
'EDDIE MURPHY ')
INSERT #array(arraycol) VALUES('STEVE MILLER '+
'TOM SCHOLZE '+
'EDDIE MURPHY ')

-- Gets the first element from the Array
SELECT Element1 = SUBSTRING(arraycol,(0*15)+1, 15)

FROM #array a

Mohammad Azam
www.azamsharp.net

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-29 : 15:05:34

that's a bad way to do things.
normalize your data. you'll be better off in the long run.

Go with the flow & have fun! Else fight the flow

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-09-29 : 15:13:37

Hi,

Thanks for the reply. I know its bad I just wanted to knonw if it will tranverse the whole array or only extract the data specified at the position.

Thanking you,
AzamSharp

Mohammad Azam
www.azamsharp.net

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-29 : 15:20:32

well since it's substring function and since sql server is written in c++ i'd say that
it goes through the whole string with a pointer.
shortly.... yes.

Go with the flow & have fun! Else fight the flow

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-09-29 : 15:29:19

Thanks again. Is there any way I can access location[300] in just one shot without going through all the rows

Mohammad Azam
www.azamsharp.net

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-29 : 15:33:00

you'll have to define something for me.
what is location[300]?
is that 300th row in your temp table or the 300th string in one row?

Go with the flow & have fun! Else fight the flow

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-09-29 : 18:13:44

Its 300th Row in the temp Table.
I thought we can do this with arrays since arrays can access particular location using the index.

THanks,

Mohammad Azam
www.azamsharp.net

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-29 : 18:33:15

if you do:
CREATE TABLE #array(k1 int identity primary key, arraycol varchar(8000))
then insert 300 rows
there will be an index on the k1 column.
this translates into a b-tree structure programming-wise.

so when you access "row #23"
(order has no meaning in databse - how's that?)

select arraycol from #arrays where k1 = 23

it will look up "23rd row" using the b-tree and give you the result of whatever was stored in the arraycol.

-------------------------------------
was that an answer to your question ?

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-09-29 : 21:56:25

Rockmoose, thanks for the explanation. When it will look up 23rd row using the B trees it will parse some other branches as well right. Like it will reach 21 and decides that 23 is to the left of 21 something like this. Am I right?

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-30 : 01:50:58

Will this help you?
http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-30 : 02:27:30

I am not sure about the implementation in sql-server, but yes
it will make < n-1 lookups into the b-tree where n is the depth of the tree.
n-1 is a worst case scenario.
A tree of depth n can store 2^n + 1 values.

If someone knows better, feel free to correct me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources