hi all, i have a table with three columns. First Col is defined varchar(50),Second col is defined char(50),Third col is varchar(max) i inserted all colums a simple word like 'example' and i wonder how many bytes each columns occupy on my table? For eg second columns occupies 50 byte for each record. Thanx in advanced
and also depends on if how the SET ANSI_PADDING is set on that table and if allows NULL or not on that column. So it could be net data size or use more space depending on the incoming data. char data type typically use more if PADDING in ON or not trimmed on the data source.
i know it depends on the length of the data but i can not explain question clearly. I wanna to store Customers name on a table.I can select date type varchar(50) or Varchar(Max). but i wonder what is the main differences between varchar(50) and varchar(Max)? which one shoul be prefered when i store customers name? and why?
Do you really expect a customer name to be of almost 2GB??? Seems to be very unrealistic to me. You should always try to keep column data types as narrow as possible. If you allow varchar(MAX), sooner or later some smart user will put in such data, just to see what happens. So, varchar(50) or even something like a varchar(100) sounds much more reasonable. Also, since relational indexes in SQL Server can only be 900 bytes in length, you would need to consider Full-Text indexes if you want to put an index on that column if it was varchar(MAX). You can't use a relational index on such a column.
i still can not explain my question clearly I did not think to store customer name in a varchr(max) Coloum when i defined a column data type char(50) and insert frank to this column it occupies 50 byte if i defined varchar (50) and insert frank to this column how many bytes does it occupies?
Ignoring now all maybe existing internal overhead, it requires 5 bytes. You can check this via the DATALENGTH() function.
Some times before I have wrote a blog on how significance of choosing appropriate datatype, refer it here http://www.sql-server-citation.com/2009/11/common-mistakes-in-sql-server-part-1.html I hope this will clear your doubts.