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 |
|
luker
Starting Member
2 Posts |
 Posted - 2004-06-27 : 17:23:05
|
| I have a design question, im setting up my sql server database table and im wondering which of these 2 options are best memory and performance wise for storing around 500-1000 chars of data in a varchar feild:1) Creating one field of 500-1000 chars length 2) Creating a field of 50 chars but using multiple rows to hold the dataWhen sql references a 1000 character long field, does it allocate memory to hold 1000 characters or does it only allocate enough memory to hold the amount of data thats actually in there? |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-06-27 : 17:31:41
|
From BOL:quote:
varchar[(n)]Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
Option #1 is the best choice. I'm betting that if you go with #2 that you'll have to do some concatination on the application tier, so you probably will not gain anything overall. Even if #1 isn't a better preforming solution, it's certainly going to be easier to write code against.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-27 : 17:41:11
|
| Seehttp://www.nigelrivett.net/PageStructure.htmlfor how the data is stored==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
luker
Starting Member
2 Posts |
Posted - 2004-06-27 : 19:21:39
|
quote:
Originally posted by nr
Seehttp://www.nigelrivett.net/PageStructure.htmlfor how the data is stored==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Thanks, but that document is a bit over my head - i dont really understand what its talking about... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-27 : 19:36:42
|
| What kind of data are you wanting to hold in this field or rows? Can you give an example of say five rows you would would hold in the big field, and then how you were thinking of holding it in the smaller field with more rows? To help you out the most, we'd need to have this information.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-27 : 19:59:45
|
| It's showing how data is stored.varchars are variable length fields and so are stored at the end of the data row. They are concatenated and accessed via the column number and offset.When a varchar increases in size the data after it is moved up - and this can cause page splitting and fragmentation.For chars the full data length is stored padded with blanks so you don't get the fragmentation problem but at the expense of taking up more space.For the size of data you have use a varchar(1000) column (or 8000 - it doesn't make any diffence except you might get a rowsize warning).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
varchar length