I don't think it's going to make a lot of difference to system
performance, especially if the majority of the text entries are brief,
since:
1) Unless you have overridden the default behavior, SQL Server uses
only enough space to hold the actual values - as opposed, say, to
reserving 2000 bytes in each physical row for each nvarchar(1000)
column.
2) As you have stated, the text columns will be relevant to most rows -
so for the applications that use those columns, the text will be
retrieved/stored in most cases, anyway.
3) It's quite unlikely (and even less advisable) that your applications
are issuing SELECT * queries, so only those that need the text columns
will be retrieving them.
But separating them does have a certain aesthetic appeal, and opens up
data administration opportunities, such as putting them on separate
physical devices, and I tend to favor this approach. If you are meeting
resistance to this, offer your customers a view that combines the
tables. If the view can't be made updateable, offer a stored procedure
to handle this.
On the advisability of storing text in the database at all: what's
wrong with that? In my book, it's an excellent application of
relational technology. And it's a lot easier to handle than storing
pointers to external files in the database, and having to access and
manage those files separately.
===========================================
James D. (Jim) Gawn
Consultant
Information Services Group, Inc.
200 Airport Road, Suite 100
Capital City Airport
New Cumberland, PA 17070
USA
717-774-0709 x23
717-201-7698 (cell)
717-774-3637 (fax)
[Email address protected]
[Email address protected] (home)
===========================================
-----Original Message-----
From: Carol Green
[mailto:mssqldba-ezmlmshield-x70369394.[Email address protected]
Sent: Tuesday, September 26, 2006 11:57 am
To: LazyDBA Discussion
Subject: Design question
Hello all,
a design question for you:
We currently have a table which is accessed by several different
applications. However, we have been asked to add several huge varchar
columns to hold a bunch of text (a crap idea if you ask me). The varchar
fields will be completed for most but not all of the records. These
fields are only relevant to two of the applications.
Would it be better to have one main table or to have two tables, one for
the basic data and one for all the guff?
I'm thinking two tables although I wasn't sure how much of a performance
issue it would be to have the varchar fields in the same table as the
frequently accessed fields.
Carol.
This email and any files transmitted within it are private and
confidential.
If you are not the intended recipient, this email and any attachments
within it have been transmitted to you in error.
You are prohibited from using, copying, distributing or otherwise using
the information contained within this email.
Nothing in this email message amounts to a contractual or legal
commitment on the part of Optilan unless confirmed by a communication
signed on behalf of the company.
Any email cannot be guaranteed to be secure, error free or free from
viruses. Although every possible care is taken by Optilan, Optilan does
not accept any liability whatsoever for any loss or damage which may be
caused as a result of the transmission of this message by email.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page