Djabarov, Robert
Robert.Djabarov at usaa.com
Tue Dec 16 09:13:01 CST 2003
VARCHAR datatype does not take the maximum allowed number of characters unless they are actually used. This means that if I have a field declared as VARCHAR(8000) and store a 10-character value there are 7,992 characters that are avalable on the page for the rest of the fields in the table. -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan Geller Sent: Tuesday, December 16, 2003 7:21 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]Large varchar fields -- performance implications? I believe Billy is correct. I read a bit about this in Delaney's book on SQL Server and it is all about how you can use pages. The fewer the pages, the more efficient your I/O. A page can hold 8K. Thanks, Billy. --Susan -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy Pang Sent: Monday, December 15, 2003 5:54 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]Large varchar fields -- performance implications? Somebody correct me if I am wrong but if you are concerned about performance, keep your tables thin. Basic unit of storage in sql server is a page. For a table, the more rows you can fit into an 8k page, the better the performance. This is because when you query that table, sql server goes through less pages when gathering the results. For example, you have two phone books, each with equal number of entries. The first phone book is your standard phone book with 400 names per page. The second phone book has 1 name per page (and thus is 400 times thicker than first phone book). It would take you less time to find a name in the first phone book than second phone book. HTH Billy >From: "Susan Geller" <sgeller at cce.umn.edu> >Reply-To: dba-sqlserver at databaseadvisors.com >To: <dba-SQLServer at databaseadvisors.com> >Subject: [dba-SQLServer]Large varchar fields -- performance >implications? >Date: Mon, 15 Dec 2003 14:52:52 -0600 > >I am working on a data model and have some questions about large (that >is over 255) sized varchar fields. > >1. Does it matter what size the varchar is once it is over 255? That >is, do you take the same performance hit for a varchar 260 as for a >varchar 8000? > >2. If I have a field in a table that needs to be over varchar(255), >but will only be populated about 20% of the time, should I put that >field in its own table with an id value and just store the id value in >the table? In other words, do I take a performance hit even when my >field is empty? > > >--Susan > > >Susan B. Geller >Office of Information Systems >College of Continuing Education >University of Minnesota >306 Wesbrook Hall >77 Pleasant Street SE >Minneapolis, MN 55455 >Phone: 612-626-4785 >Fax: 612-625-2568 > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin .msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com