Hi;
Can MS-SQL support Arrays? I need a float that occurs 72 times in a
table and I don't want to have 72 seperate fields but can't see a way of
creating an array?
Thanks in advance.
David
*** Sent via Developersdex http://www.examnotes.net ***Hi,
There is no array concept in SQL Server. But you could use Dynamic SQL's to
provide an equalent.
Look into the great article:-
http://www.sommarskog.se/arrays-in-sql.html
Thanks
Hari
SQL Server MVP
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||No, SQL Server does not support arrays
Yo can use either dynamic sql or udf
CREATE PROCEDURE array_method_1
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC array_method_1
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
GO
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You can share them by (global) temporary tables, just follow the link Hari
presented.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DavidC" <Cervelli@.Adelphia.Net> schrieb im Newsbeitrag
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
> I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
Having just spent 20+ hours days undoing an "Array" column (i.e., column
containing a string with comma separated values), I can assure you that
having 72 columns is much better than having a CSV-string.
An "array" is a terrible hack and a huge design flaw created by a lazy
programmer.
Yeah, it's more work. Yeah, it's not fun. But, this is why they pay you the
big bucks.
Just do it.
Perhaps if you share the problem domain we may be able to help with a
cleaner design for this (aparantly) complex data.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis|||>> Can MS-SQL support Arrays?
No
Perhaps you can make your overall requirement clearer. Most likely your
logical design is flawed which forces you to come up with such complex table
structures. Are you sure these are truly 72 distinct attributes of the
entity type you are modeling? Or does the collective 72 repetitions form a
single attribute from a conceptual POV?
Anith|||Even if the number of float values will be constant at 72 per record, it
seems the logical place to store them in a relational database would be a
foreign key table. Or perhaps we are talking about a lookup table with 72
records. Loading the values into an array would be something you implement
on the presentation / GUI side. Am I understanding what it is you want to
do?
"DavidC" <Cervelli@.Adelphia.Net> wrote in message
news:OOBP485XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi;
> Can MS-SQL support Arrays? I need a float that occurs 72 times in a
> table and I don't want to have 72 seperate fields but can't see a way of
> creating an array?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***
Showing posts with label seperate. Show all posts
Showing posts with label seperate. Show all posts
Friday, February 24, 2012
Thursday, February 9, 2012
Are seperate databases betters?
Hi. I have been talking with some developers who have built a hosted application supporting multiple customers. Their database approach is to create a new, dedicated database (same schema each time) for every customer that signs-up.
This approach is contrary to typical hosted DB designs that I have delt with -- that is, a single database holding multiple customer information rather than a unique database for each customer.
Does the improved security of a dedicated database out-weigh the additional maintenance requirements?
If anyone has some objective thoughts on this topic, I'd love to hear them.
Thanks,
BillIf this is a "hosted" application for, say, a cadre of retail stores each one of which has its own block of customers, products, sales, etc to deal with, then the stores are the hosting-company's "customers."
Each store expects to see and to deal with only its own set of information, and considers that information to be highly proprietary.
The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers. I think that having separate databases, with separate login-IDs etc, is really the only scenario that would work.|||It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?|||Thanks for the replies.
[The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers...]
Even with a single database, reasonable precautions are taken. All customers have separate logins and distinct logical segregation of data. The approach is roughly analogous to a bank safe with safe boxes holding the possessions of multiple parties.
[It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?]
I think if you were to ask any given customer, their knee-jerk reaction would be to say that they want their own database. But other than the fear of co-mingling of data, there are no specific scale or performance reasons that would demand it.
My concern with separate databases is the administrative overhead if you have a thousand customers, youd have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.|||My biggest worry would be if someone from company A deletes half their data. After the restore, company B asks "Where is my data from today". Just my .02 USD.|||Originally posted by billdavidheiser
[The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers...]
Even with a single database, reasonable precautions are taken. All customers have separate logins and distinct logical segregation of data. The approach is roughly analogous to a bank safe with safe boxes holding the possessions of multiple parties.
[It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?]
I think if you were to ask any given customer, their knee-jerk reaction would be to say that they want their own database. But other than the fear of co-mingling of data, there are no specific scale or performance reasons that would demand it.
My concern with separate databases is the administrative overhead if you have a thousand customers, youd have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.
As usual, what it really comes down to is: "it depends." Multiple databases make it less likely that the wrong customers' data will be visible but have their own negative implications as well. I think that the best advice, borne out by this little discussion, is that the designer must be keenly aware of the tradeoffs that will inevitably be made with any decision. Every decision is a compromise. No matter how forcibly a particular position may be argued on a forum ;) it is neither "right" nor "wrong."|||Multiple databases have the following advantages:
If one or some customers ask for his or their data backup you can easily do it.
If one or some customers ask for some changes which may be related to the table structures, you can easily handle it.
If one or some customers leave, you can easily backup and delete (drop) his or their databases without cleaning his or their data in one database.|||Originally posted by sundialsvcs
As usual, what it really comes down to is: "it depends." Multiple databases make it less likely that the wrong customers' data will be visible but have their own negative implications as well. I think that the best advice, borne out by this little discussion, is that the designer must be keenly aware of the tradeoffs that will inevitably be made with any decision. Every decision is a compromise. No matter how forcibly a particular position may be argued on a forum ;) it is neither "right" nor "wrong."
["it depends."]
Ah, very helpful. Thank you. Are you an Economics professor by any chance? :)
Seriously though; I understand what you are saying and agree. I was hoping to hear if anyone out there has a real-world experiance with trying to maintain a boat-load of individual customer databases.|||MCrowley brings up a good point which ties into fault tolerance. What happens if the database is suspect or part of it is corrupt - all of your users would be hosed if using 1 database.
What about the amount of data being stored per customer and the frequency of updates/deletes/inserts per customer ? Is the amount of data fixed per customer and do they infrequently change data ?|||The company that I'm working for has individual customer database for each customer. All databases have the same structure except some extra fields for some databases based on the customers requirements. We had some problems which happened to some of databases and the websites related to those databases were affected. In this case, individual customer database shows an advantage.
For the maintenance of the databases, since they have almost same structure you can use a script to do a job for all databases. I think that the advantages are much more than disadvantages.
This approach is contrary to typical hosted DB designs that I have delt with -- that is, a single database holding multiple customer information rather than a unique database for each customer.
Does the improved security of a dedicated database out-weigh the additional maintenance requirements?
If anyone has some objective thoughts on this topic, I'd love to hear them.
Thanks,
BillIf this is a "hosted" application for, say, a cadre of retail stores each one of which has its own block of customers, products, sales, etc to deal with, then the stores are the hosting-company's "customers."
Each store expects to see and to deal with only its own set of information, and considers that information to be highly proprietary.
The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers. I think that having separate databases, with separate login-IDs etc, is really the only scenario that would work.|||It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?|||Thanks for the replies.
[The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers...]
Even with a single database, reasonable precautions are taken. All customers have separate logins and distinct logical segregation of data. The approach is roughly analogous to a bank safe with safe boxes holding the possessions of multiple parties.
[It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?]
I think if you were to ask any given customer, their knee-jerk reaction would be to say that they want their own database. But other than the fear of co-mingling of data, there are no specific scale or performance reasons that would demand it.
My concern with separate databases is the administrative overhead if you have a thousand customers, youd have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.|||My biggest worry would be if someone from company A deletes half their data. After the restore, company B asks "Where is my data from today". Just my .02 USD.|||Originally posted by billdavidheiser
[The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers...]
Even with a single database, reasonable precautions are taken. All customers have separate logins and distinct logical segregation of data. The approach is roughly analogous to a bank safe with safe boxes holding the possessions of multiple parties.
[It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?]
I think if you were to ask any given customer, their knee-jerk reaction would be to say that they want their own database. But other than the fear of co-mingling of data, there are no specific scale or performance reasons that would demand it.
My concern with separate databases is the administrative overhead if you have a thousand customers, youd have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.
As usual, what it really comes down to is: "it depends." Multiple databases make it less likely that the wrong customers' data will be visible but have their own negative implications as well. I think that the best advice, borne out by this little discussion, is that the designer must be keenly aware of the tradeoffs that will inevitably be made with any decision. Every decision is a compromise. No matter how forcibly a particular position may be argued on a forum ;) it is neither "right" nor "wrong."|||Multiple databases have the following advantages:
If one or some customers ask for his or their data backup you can easily do it.
If one or some customers ask for some changes which may be related to the table structures, you can easily handle it.
If one or some customers leave, you can easily backup and delete (drop) his or their databases without cleaning his or their data in one database.|||Originally posted by sundialsvcs
As usual, what it really comes down to is: "it depends." Multiple databases make it less likely that the wrong customers' data will be visible but have their own negative implications as well. I think that the best advice, borne out by this little discussion, is that the designer must be keenly aware of the tradeoffs that will inevitably be made with any decision. Every decision is a compromise. No matter how forcibly a particular position may be argued on a forum ;) it is neither "right" nor "wrong."
["it depends."]
Ah, very helpful. Thank you. Are you an Economics professor by any chance? :)
Seriously though; I understand what you are saying and agree. I was hoping to hear if anyone out there has a real-world experiance with trying to maintain a boat-load of individual customer databases.|||MCrowley brings up a good point which ties into fault tolerance. What happens if the database is suspect or part of it is corrupt - all of your users would be hosed if using 1 database.
What about the amount of data being stored per customer and the frequency of updates/deletes/inserts per customer ? Is the amount of data fixed per customer and do they infrequently change data ?|||The company that I'm working for has individual customer database for each customer. All databases have the same structure except some extra fields for some databases based on the customers requirements. We had some problems which happened to some of databases and the websites related to those databases were affected. In this case, individual customer database shows an advantage.
For the maintenance of the databases, since they have almost same structure you can use a script to do a job for all databases. I think that the advantages are much more than disadvantages.
Subscribe to:
Posts (Atom)