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 |
|
mca146
Starting Member
2 Posts |
 Posted - 2008-12-29 : 18:02:52
|
| I have some confusion understanding the varchar data type.The benefit of using varchar data type as opposed to the old char() data type is that SQL Sever is only supposed to allocate enough space to store the "actual length of the string" as opposed to the defined length of the String such as Varchar(50). So if you have Varchar(50) defined and you store the string "Hello" SQL only stores 5 characters, instead of 50. The confusion: If you run the sproc, sp_help <tableName> the stats show that trailing spaces are NOT trimmed. They are only trimmed with varchar if you specify SET ANSI_PADDING OFF;The documentation highly recommends that you always keep the padding on. QUESTION: what is the benefit of using varchar when you're not saving any space in your database. Some what confusing. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mca146
Starting Member
2 Posts |
Posted - 2008-12-29 : 18:16:01
|
Yes tkizer,I believe that is correct. but my question is: with the padding set to on,how much space is actually getting stored by SQL Server? The 50, or the 5? Do those trailing spaces count as used space? In other words, am I saving space with varchar as opposed to char datatype?Thank youMCA146 quote:
Originally posted by tkizer
Isn't that referring to "helloxxxxxxxx" (where the x's refer to spaces) rather than "hello"?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
 |
|
|
|
|
|
Varchar and Set ansi padding on/off