 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / C# / September 2007char or varchar?
My understanding is that char stores a fixed number of characters even if the string stored in the char has fewer characters, whereas varchar stores the string with the number of characters that the string contains. So, varchar sounds more efficient, but are there any disadvanages of using varchar over char (eg speed and size)? If I wish to store ISBNs (which can be 10 or 13 characters, using characters 0-9 and X) would char(13) be a good choice? I don't need to store them as unicode, so am ignoring nchar and nvarchar, but let me know if there are any problems with this. Jay, Actually, when it comes to database operations, char fields are more efficient, as the the character fields can be stored contiguously in memory, and it is easy to get the data. With varchar fields, you have a redirection in order to find the data, as the data can not be stored contiguously in memory due to the changing size of the data. Generally though, I wouldn't worry about this unless you have already created your application and you know that this is a bottleneck. As for what your ISBN numbers should be, if you are exposing the results from the database directly without any processing, then I would say that the varchar is better, as it return the data trimmed appropriately. If there is a layer of indirection, then I would say to use char(13), and then have the layer processing the data trim it before it is returned to the user.  Signature- Nicholas Paldino [.NET/C# MVP] - mvp@spam.guard.caspershouse.com > My understanding is that char stores a fixed number of characters even if > the string stored in the char has fewer characters, whereas varchar stores [quoted text clipped - 9 lines] > any > problems with this. Can you expand on "redirection"? My understanding is that varchar is still stored "in row"... but with a variable number of records per page; the downside here being that this can necessitate page-shuffling if the contents change and would overflow other records on the page (exhausting the padding). Marc Sorry, I should elaborate. In the case of SQL Server 2005, if you have a varchar type, then the data is stored in the field with a two byte prefix indicating the length of the varchar string. However, if the size of the row is greater than 8060 bytes, then SQL Server might store variable length data off-row: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/23/644607.aspx varchar(max) is always stored off-row. I'm not sure how it works in SQL Server 2000 and before though. Signature- Nicholas Paldino [.NET/C# MVP] - mvp@spam.guard.caspershouse.com > Can you expand on "redirection"? My understanding is that varchar is > still stored "in row"... but with a variable number of records per [quoted text clipped - 3 lines] > > Marc > varchar(max) is always stored off-row. > ... > I'm not sure how it works in SQL Server 2000 and before though. Well, [n]varchar(max) didn't exist, so you had either [n]varchar(len<=8000) or [n]text. The former is in-row, the latter is off-row by default, but short values can be brought in-row via TEXT_IN_ROW, essentially the same as "row-overflow" in your cited doc; with TEXT_IN_ROW specified, short values (your choice of length within limits) can be brought in-row without any coding changes. I've a suspicion (without any real grounds) that [n]varchar(max) can work similar to TEXT_IN_ROW on [n]text. Thanks for the info, Marc Although this is a bit old, it still should be helpful: http://www.sql-server-performance.com/articles/per/saving_space_p1.aspx -- Peter Recursion: see Recursion site: http://www.eggheadcafe.com unBlog: http://petesbloggerama.blogspot.com BlogMetaFinder: http://www.blogmetafinder.com > Can you expand on "redirection"? My understanding is that varchar is > still stored "in row"... but with a variable number of records per [quoted text clipped - 3 lines] > > Marc Thanks everyone for your answers - that was very helpful, as was the discussion that followed. Sorry about my very late reply... My understanding is that char stores a fixed number of characters even if the string stored in the char has fewer characters, whereas varchar stores the string with the number of characters that the string contains. So, varchar sounds more efficient, but are there any disadvanages of using varchar over char (eg speed and size)? If I wish to store ISBNs (which can be 10 or 13 characters, using characters 0-9 and X) would char(13) be a good choice? I don't need to store them as unicode, so am ignoring nchar and nvarchar, but let me know if there are any problems with this. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.