InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 03:30:14 GMT20http://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxnice discussion :-)Thu, 01 Nov 2012 05:13:16 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxTrue, UK postal codes are a nightmare to handle, thats why most people will try and push it up into the UI layer and rely on specialised Reg-ex validation procedure before they get to the Db layer, or use a Postal code tool that uses the Royal Mail PAF files to validate the addresses. Though the later is often out of date especially for new developments as its only updated every quarter. Wed, 31 Oct 2012 09:00:12 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspx[quote] Then you have UK postal codes which are 5-8 characters long, depending on whether you include a space or not. [/quote]Oh, the UK is even worse than that! Try to write a regular expression to validate it! It is an alphanumeric nightmare. They finally introduced a MailSort code for commercial bulk mailers, but not the general public. Korea is replacing the Japanese street address system with the US 9-1-1 system, but the Buddhist are protesting it. The old system is based on "neighborhoods" (chome) and numbering the building within the boundaries in the order they were built! But some of the chome names are Buddhist saints and they are afraid they will be lost. Kinda like small town post offices in the US :-DWed, 31 Oct 2012 08:19:46 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxFor Zip codes (which are actually a USPS trademark), yeah, 5-digits is fine. Well, unless you need Zip+4. Of course, you can put the +4 in a separate, Char(4), numeric-only, nullable column, to enforce the rules on that.For international postal codes, you need to get a bit more flexible. Canadian, for example, have numbers and letters, in a particular pattern. Same for UK if I'm not mistaken. As Gail mentioned, SA uses a 4-digit code (all numbers if I'm not mistaken). Other countries, other rules.Nullable columns, one for each format, can be done, with enforcement of format in each column. ZipCode char(5) numeric-only, ZipPlus4 char(4), numeric-only, PostalCanadian char(6) with formatting enforced, and so on. But that's a little complex to manage.Could do a table per format (some formats are multinational), with limits on the ISO3 country code in each table to map that to relevant national laws, then Union with forced-null columns for columns that some countries have and others don't (like State/Province/Region) into a single view for querying.Could create a CLR datatype "MailableAddress" that could format, enforce, etc., based on rules built into the DLL.The right XML rules could also be built to enforce a full set of international address rules.Or you could use nvarchar(10) for a column called PostalCode, and manage the input/update rules in a higher software layer. Preferably the UI, where an error in Zip code format can be brought to the user's attention and they have a choice of fixing it or overriding the rule.Lots of ways to solve these things.(I've been working with address list data for over 10 years. It's a bit more complex than char(5) and a Like '[0-9][0-9][0-9][0-9][0-9]' constraint, unless you're going to completely disallow non-US addresses.)But Joe is right that you need to know what you're planning on storing and not just blanket varchar(50) or char(25) everything.Wed, 31 Oct 2012 08:04:54 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspx[quote][b]GilaMonster (10/31/2012)[/b][hr][quote][b]CELKO (10/31/2012)[/b][hr]or better: zip_code CHAR(5) NOT NULL CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')[/quote]As long as whatever that is is intended for US only, now and always. I just love finding web sites and forms that insist I put a 5-digit post code (mine is 4 digits).[/quote]Then you have UK post codes which are 5-8 characters long, depending on whether you include a space or not.Wed, 31 Oct 2012 07:53:25 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspx[quote][b]CELKO (10/31/2012)[/b][hr]or better: zip_code CHAR(5) NOT NULL CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')[/quote]As long as whatever that is is intended for US only, now and always. I just love finding web sites and forms that insist I put a 5-digit post code (mine is 4 digits).Wed, 31 Oct 2012 07:41:52 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspx[quote] I have a database with over 200 tables. They all use char(xx) instead of varchar(xx). I heard that varchar is better because it saves space. [/quote] Yes, it matters very much. The length is a constraint on the column and ensures part of the data integrity. Each column needs to be designed separately, based on what it models. This question is like asking "should all numbers be integers less than 100 in my database?"Look on SQL forums and you will see morons who write crud like: zip_code VARCHAR(5) or worse zip_code NVARCHAR(255) The guy that does that last one deserves the Unicode Chinese sutras I love to stick in those columns. If he cannot learn SQL, then he learn Zen :-P The right way is at lest zip_code CHAR(5) NOT NULLor better: zip_code CHAR(5) NOT NULL CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')or best, tho this is anal even for me: zip_code CHAR(5) NOT NULL CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]' AND zip_code BETWEEN '01001' -- Agawam, Massachusetts AND '99950') --Ketchikan, AlaskaAnother favorite is VARCHAR(1); think about it. I found over-use of VARCHAR(n) comes from C family programmers who think of nul terminated strings and over-use of CHAR(n) comes from COBOL, Fortran, Pascal and BASIC programmers who think of fixed length stringsAnd do not blindly assign VARCHAR(50) like ACCESS or VARCHAR(255) default lengths. Read your standards! For example, U.S. postal address lines are based on a VARCHAR(35) length; we used to use 10-pitch type on 3.5 inch labels with five lines per label. Wed, 31 Oct 2012 05:37:30 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspx[quote][b]mike 57299 (10/29/2012)[/b][hr]I tried to change a table from char(xx) to varchar(xx). It seems that it doubled the amount of space used. What gives? [/quote]Rebuild the clustered index, the table should drop back down (providing those char columns you changed weren't something like char(2))Tue, 30 Oct 2012 11:01:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxThere are a couple of factors that can make varchar take more space than char of the same length. First is very short strings in a small column. For example, storing "AA" in char(2) takes 2 bytes, but storing it in varchar(2) takes 4 bytes (because of the length data added to variable-length columns).The other is ANSI padding. It can do weird things to trailing spaces in varchar() columns. Check out this for details: [url]http://msdn.microsoft.com/en-us/library/ms187403.aspx[/url]For fixed-length data, or for very small columns (up to about 10 characters), I use char() instead of varchar(). Unless the data will much more often be very short than very long. Partially, this is due to fixed-length columns not resulting in page splits when updated to longer values. Page splits often result in extra disk allocation being needed, even if only a small percentage of the pages are actually "full", plus they can result in performance issues in busy databases.Tue, 30 Oct 2012 10:54:58 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxImportant to remember: When someone says to use a certain technique because it is better/faster/more economical, make it a point to understand [i]WHY[/i]. Blind faith doesn't take you very far in SQLTue, 30 Oct 2012 10:44:07 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxOr you could construct a query against your table as follows to check total actual string lengths for both scenarios.Assume the column (Your_Column) in your table (YourTable) is VARCHAR(100):[code="sql"]SELECT VARCHAR=SUM(2 + CAST(DATALENGTH(ISNULL(Your_Column,'')) AS BIGINT)) ,CHAR=SUM(CAST(100 AS BIGINT))FROM YourTable[/code]There's probably also information on this in the sys tables but I don't recall the details.[b]Edit:[/b] Note that this is only an estimate as I'm not 100% sure that NULL values take up the 2 byte length specifier.Tue, 30 Oct 2012 02:57:29 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxpls check below code...declare @var_chr char(50)='abc'declare @var_varchr varchar(50)='abc'select DATALENGTH(@var_chr) chr,DATALENGTH(@var_varchr) varO/P:chr var 50 3Mon, 29 Oct 2012 22:20:41 GMTsubbareddy542http://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxChar:1.Fixed length memory storage2.CHAR takes up 1 byte per character3.Use Char when the data entries in a column are expected to be the same size5.Ex:Declare test Char(100);test="Test" -Then "test" occupies 100 bytes first four bytes with values and rest with blank data.VarChar:1.Variable length memory storage(Changeable)2.VARCHAR takes up 1 byte per character, + 2 bytes to hold length information3.varchar when the data entries in a column are expected to vary considerably in size.4.Ex:Declare test VarChar(100);test="Test" -Then "test" occupies only 4+2=6 bytes. first four bytes for value and other two bytes for variable length information.Conclusion:1.When Using the fixed length data's in column like phone number, use Char2.When using the variable length data's in column like address use VarCharMon, 29 Oct 2012 22:02:11 GMTprettsonshttp://www.sqlservercentral.com/Forums/Topic1378547-392-1.aspxHi all. I have a database with over 200 tables. They all use char(xx) instead of varchar(xx). I heard that varchar is better because it saves space. I tried to change a table from char(xx) to varchar(xx). It seems that it doubled the amount of space used. What gives? Does it really matter?Thanks,MikeMon, 29 Oct 2012 21:00:25 GMTmike 57299