Susan Geller
sgeller at cce.umn.edu
Tue Dec 16 07:20:49 CST 2003
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