T-SQL question in SQL 2005
-
I am curious to know if we have built-in support for ARRAYS in SQL 2005. any help or info is appreciated.
-
Yes those are called tables and table functions
. -
Not in the same way other RDBMS' implement them (PROGRESS comes to mind... ouch). Usually people create normalized child tables to represent what you might otherwise use an array for.
-
Care to link an article here... I'd like to see that!
-
I'm not sure what you're asking here... PROGRESS supports array datatypes. So you could say you have a column called month and give it a field extent of 12, so you can store 12 distinct values in that particular field (all must have the same datatype). It works like a table within a table. Most of the time if we need that type of functionality in any normal RDBMS you'd follow standard normalization rules and create a one-to-many relationship with another table.
-
I C said the blind man.
-
I'm going to be spat on for even suggesting this, but is it possible you could create a CLR data type with a list-like interface that you could use for this purpose? Yep, it's grubby I know, and I'm not even suggesting it's a good idea. All I'm saying is it might work.
Cheers,
Bart
-
In short, the answer is no ... but it's not difficult to emulate an array.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I already ported an application from FoxPro (not even sure of the name but it used lists too). I had to take a few hours to create a lookup table for each field that contained a list (25 tables). It was a pain to do but it took only 5% of the total work. I don't see why this solution can't be applied here!
-
declare @MyArray table(item1 int unique, item2 varchar(10), item3 datetime)
insert into @MyArray values(1,'aaa',getdate())
insert into @MyArray values(2,'bbb',getdate())
insert into @MyArray values(3,'ccc',getdate())
insert into @MyArray values(4,'ddd',getdate())
insert into @MyArray values(5,'eee',getdate())
select * from @MyArray where item1 = 3
-
OK, so still SQL 2005 is lacking with ARRAY datatype that will be my conclusion. As my Web Application is going to send list of string arrays so if i have SQL ARRAY Datatype then my job will become easier rather than going thru TEMP TABLE or Table Datatype & not forget but will like to spell curser(CURSOR). any update will be appreciated & thanking in advance.
-
Just use a set based split function (which returns a table). That way you won't have to use all those "evil" tools
. -
Yes, it does not have an array data type. It also does not come with cupholders, cigarette lighter, or satellite radio, but it blows away SQL 2000 in the quarter mile and can tow any boat you hook up to it.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Yup that sums it up pretty good
.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply