[dba-SQLServer]Large varchar fields -- performance implications?

Susan Geller sgeller at cce.umn.edu
Tue Dec 16 12:22:41 CST 2003


This is true, but as Delaney points out, there can be performance issues
when you increase how much data is actually in the varchar, especially
if you have a clustered index. In order to accommodate the growth and
maintain the index, a new page would be created thus decreasing
performance.  

--Susan


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Djabarov, Robert
Sent: Tuesday, December 16, 2003 9:15 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Large varchar fields -- performance
implications?


Sorry, I was thinking about an 8-character value, thus - my math is
really bad this morning :)

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Djabarov, Robert
Sent: Tuesday, December 16, 2003 9:13 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Large varchar fields -- performance
implications?


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

_______________________________________________
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



More information about the dba-SQLServer mailing list