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.
| Author |
Topic |
|
duckkiller53
Starting Member
2 Posts |
 Posted - 2010-02-16 : 20:57:07
|
| Could someone help me. I am new to actually designing databases with sql server. The following question has me confused. I have a need to store 'paragraph amounts' of data in three separate fields in a table. Currently I have the fields set as Varchar(400). Would I be better of changing them to Text ( which would then allow for a possible blob of data that I may not have prepared for ). Does the 'Text' Datatype consume a massive amount of space in a table and would it be poor design to use it for fields that may or may not have data in them. I hope I have the correct understanding of the 'Text' Datatype. Is it similar to the Access data type 'Blob'. Any help would be greatly appreciated.ThanksDave.Dave |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-17 : 00:01:15
|
| text,ntext etc are deprecated features from SQL 2005 onwards so use datatypes like varchar(max),nvarchar(max) instead. You can store upto 2 GB in a these fields.If you've very little chance of storing data in them them keep a single max field in separate attribute table and populate it rowwise with associated attribute value also stored in separate field.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-17 : 03:09:22
|
"... keep a single max field in separate attribute table and populate it rowwise with associated attribute value also stored in separate field "Or a separate 1:1 table linked to the main table on PK and just storing the VARCHAR(MAX) columns. (This may be easier to use in your queries:SELECT *FROM MyTable AS T1 JOIN MyBigTextTable AS T2 ON T2.MyPK = T1.MyPK )If you are sure that the data will fit in VARCHAR(8000) or Nvarchar(4000) set that limit - rather than VARCHAR(MAX) (it allows you to run some housekeeping routines online, rather than offline - but it is a very small point, so don't let it be the basis of a decision if you think you will need more than that) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-17 : 03:16:01
|
quote:
Originally posted by Kristen
"... keep a single max field in separate attribute table and populate it rowwise with associated attribute value also stored in separate field "Or a separate 1:1 table linked to the main table on PK and just storing the VARCHAR(MAX) columns. (This may be easier to use in your queries:SELECT *FROM MyTable AS T1 JOIN MyBigTextTable AS T2 ON T2.MyPK = T1.MyPK )If you are sure that the data will fit in VARCHAR(8000) or Nvarchar(4000) set that limit - rather than VARCHAR(MAX) (it allows you to run some housekeeping routines online, rather than offline - but it is a very small point, so don't let it be the basis of a decision if you think you will need more than that)
you mean keep separate fields as varchar(max). But will that be necessary if chances that you store value in all of them are less?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-17 : 03:29:53
|
| Horses-for-courses I think.If you have an "attribute" table the JOINs can be more problematic (depends how you want to display the data, if "rows" is OK then Attribute table is fine, if "columns" are needed then a 1:1 table is easier).A VIEW can join the MainTable and 1:1 Table for ease of querying.1:1 table also reduces the impact of any sloppy SELECT * from the MainTable (i.e. does not pull large Blobs because they are in the 1:1 table)O/P wants "three paragraph fields", so I think that will work easiest in 1:1 table (or in the main table of course)If it was "unlimited/large numbers" then I think that would favour the "attribute table" design. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-17 : 03:34:27
|
quote:
Originally posted by Kristen
Horses-for-courses I think.If you have an "attribute" table the JOINs can be more problematic (depends how you want to display the data, if "rows" is OK then Attribute table is fine, if "columns" are needed then a 1:1 table is easier).A VIEW can join the MainTable and 1:1 Table for ease of querying.1:1 table also reduces the impact of any sloppy SELECT * from the MainTable (i.e. does not pull large Blobs because they are in the 1:1 table)O/P wants "three paragraph fields", so I think that will work easiest in 1:1 table (or in the main table of course)If it was "unlimited/large numbers" then I think that would favour the "attribute table" design.
Ok that sounds sensible quite often what we do is to keep an attribute table in cases where we've lot of attributes that rarely store data and then we create a view where we cross tab and show the result as columns when required.Thanks Kristen for the explanation  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
duckkiller53
Starting Member
2 Posts |
Posted - 2010-02-17 : 08:37:28
|
| Guy's thank you very much for all the information. I am happy to hear your suggestions of storing the varchar(max) datatype info in a seperate table 1:1. That is my how my database is currently designed. I just wasn't sure what to use for the field type.ThanksDave |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-17 : 09:12:40
|
If you are stuck with SQL2000 note that TEXT datatype has very limited functions that can interact with it.Whereas functions that are used on VARCHAR can also be used on VARCHAR(MAX) [SQL2005 onwards] without any issues (AFAIK!)... quite apart from the fact that TEXT datatypes is deprecated  |
|
|
|
|
|
|
|
|
Question on Datatypes