December 30, 2008 at 4:50 pm
I create a table: create table xyz (field1 varchar(8000))
Insert a record: insert into xyz values('a')
How much storage space have I used? I have read the description of a varchar, but still am a bit confused. I would think I just ate at least 8000 bytes + misc stuff. But 'Help' seems to indicate it may only store the actual 'a' + misc stuff. There's a bit difference after a million of records or so.
My second question is how does SQL keep track of where records are? I don't need to know, but I'm the curious sort.
Thanks for your time, everyone!
December 30, 2008 at 6:26 pm
Varchars only store that space that you are actually using.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 6:36 pm
RBarryYoung (12/30/2008)
Varchars only store that space that you are actually using.
+ 2 bytes to store the length of data;)
P.
...and your only reply is slàinte mhath
December 30, 2008 at 7:06 pm
Thanks.
I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.
I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?
December 30, 2008 at 7:43 pm
klini (12/30/2008)
Thanks.I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.
I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?
So, you won't mind when you get an error that you can't save data to the table because it exceeds the maximum length allowed.
And yes, I would get upset with other table-creators that do similar. If you don't need 8000 characters to store the data, don't size the column that large. Remember, whatever size you allow is the size that is going to be used - whether or not it actually makes sense.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 8:02 pm
Piotr Rodak (12/30/2008)
RBarryYoung (12/30/2008)
Varchars only store that space that you are actually using.+ 2 bytes to store the length of data;)
P.
Also don't forget that if you have other columns or multiple varchars and you have varchars that exceed the 8KB page limit, you'll start storing the varchar in row overflow pages.
Interestingly, you can't store more than 8KB of fixed char columns, but you can store more than 8KB variable columns.
December 30, 2008 at 8:03 pm
klini (12/30/2008)
Thanks.I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.
I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?
If that's what you are trying to achieve, have a look at VARCHAR(MAX). It might be what you are looking for.
December 30, 2008 at 9:03 pm
What the heck... no table design needed... just store it all in one big ol' nasty EAV and call it a day... just think how easy indexing will be. :P;):hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 9:26 pm
RBarry says "Varchars only store that space that you are actually using"
but
Jeffrey says "whatever size you allow is the size that is going to be used"
Could I have some clarification?
December 30, 2008 at 9:41 pm
Jeffrey meant that if you give room for a mistake to happen, it will happen. You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated. And, no, you shouldn't use VARCHAR(MAX) if you don't have to because it has some "off page" overhead.
Neither Barry nor Jeffrey contradicted each other.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2008 at 11:28 pm
Jeff Moden (12/30/2008)
Jeffrey meant that if you give room for a mistake to happen, it will happen. You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated. And, no, you shouldn't use VARCHAR(MAX) if you don't have to because it has some "off page" overhead.Neither Barry nor Jeffrey contradicted each other.
I just want to state for the record that I'm not recommending varchar(MAX) if you don't need it. However, varchar(MAX) has a number of excellent features that make it good for storing large amounts of text data.
However, I'm not sure what is meant by "You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated." Jeff, can you clarify? If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages. Of course if you try define more than approx. 8KB of fixed chars in a table you'll get that error...
Note that again, for the record, I don't recommend storing greater than what you need. Having to read more than one page at a time could hurt performance.
As they say, store only the amount that you need to store. If you can store less than 8KB of data per row, it would be better.
December 31, 2008 at 6:01 am
ta.bu.shi.da.yu (12/30/2008)
If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages.
Not true... run this and see.
CREATE TABLE #TooBig
(
ColA VARCHAR(5000),
ColB VARCHAR(6000)
)
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2008 at 6:04 am
Jeff Moden (12/31/2008)
ta.bu.shi.da.yu (12/30/2008)
If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages.Not true... run this and see.
CREATE TABLE #TooBig
(
ColA VARCHAR(5000),
ColB VARCHAR(6000)
)
Ack... crud... my bad... I was running this in SQL Server 2000... not 2005. Sorry. You don't get the same error in 2005. (Man, I gotta get out of the habit ;))
Still, like VARCHAR(MAX), there is a bit of overhead involved with "overflow" pages. It is best to stay within the page limits of 8060 bytes.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2008 at 6:17 am
Even though SQL Server 2005/2008 will store excess values past the 8060 limit when dealing with varchar,etc., you will get performance overhead to go and do that extra retrieve. I'd be sure I needed it rather than toss the idea of database design out the window & store everything in flipping huge, unformed, strings.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 31, 2008 at 6:58 am
Thanks everyone!
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply