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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2009-07-01 : 09:44:38
|
| In SQL 2005, I need to create a transaction table...CREATE TABLE dbo.Transaction ( Command: VARCHAR(100) NOT NULL, Name: VARCHAR(100) NOT NULL, Value: VARCHAR(64000) NOT NULL)First, I don't know if SQL 2005 did anything about the length limit of VARCHAR in a table. Is there a declaration that supports strings up to 64000 long?Second, less than half the transactions will have a Value that is more than 10 bytes long. Will all these 10 byte value fields gobble up 64000 bytes of disk space? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-01 : 09:51:06
|
| All varchars will use space dynamically.varchar (max) is pretty big (2^31)-1 bytesOh, and it's almost certainly a crap design if you need this. What do you need in that column? |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2009-07-01 : 09:59:04
|
| It's a column that records varchar from an external standard. There's one value that can be 64,000 bytes.Everything else is less than 10 or 15 bytes.So show me a method that isn't crap design. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-01 : 10:08:53
|
| If you can't reduce the 64K value to something more usable then you don't have much choice. Typically though the 64K represents something you want to use/interpret/analyze/count/group or something. If this isn't the case then maybe a big lump o' stuff is the way to go.So what is in it? |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2009-07-01 : 11:53:25
|
| I'm heading out, so this is my last response for a while.The last field is variable string that the standard gives for the application program to store "anything it wants".Most applications store 1 or 2K bytes. I don't want to break anything, so I'll have to allow for the max of 64KB. |
|
|
|
|
|
|
Transaction Table Design Question