SQL Server Performance Forum – Threads Archive
Database Design article
Hi allI read now the article of "Tips on Optimizing
SQL Server Database Design "
and i missing something ..
How can Iconvert from length of varchar to the bytes that the sql stored ?
I refferd to "only 8060 bytes are used to store a row."
Best regards
Tomer
I think what the article meant is that you can only store up to 8060 bytes in a row on a table.
In a varchar column the length used to store your data by sql server is the length of the data that you insert or update it with and not neccessarily the specified width of the column.. Man, I’m really bad at explaining things, I hope it helped anyway. Cheers
Shaun World Domination Through Superior Software
The focus of this database design performance tip was to suggest that you make the most of the available space in a database. Each page in SQL Server 7.0 and 2000 is 8192 bytes. A page is the smallest unit that SQL Server will read. Of this page size, 8060 bytes can be used for storing rows of data. In order to reduce disk I/O, and to maximize SQL Server performance, it better to place as many rows in a page as possible. You can’t do this directly, as SQL Server does this housekeeping for you. But what you can do is to try to efficiently design your rows so that as many as possible will fit into a single data page. For example, If you have a row that was 4031 bytes long, then only one row could fit into a data page, and the remaining 4029 bytes left in the page would be empty (this is true of both varchar and char). (Keep in mind that a row can be up to 8060 bytes, and half of this is 4030 bytes.) This is a great waste of space which can affect the I/O performance of the server. If you run into a situation like this, try to redesign the row, if possible, so that the row is 4030 bytes or less. This way you would get two rows in each page and I/O performance would be greatly enhanced. This not only applies to this particular example. It can also apply to cases where you are trying to fit three, four, or more records efficiently into a single data page. Another factor that affects how many rows that can fit onto a page is the Fill Factor. This determines how much free space SQL Server reserves on a page when indexes are built or rebuilt. The greater the Fill Factor, the more empty space there in on each page. Some free space is often useful, as it reduced page splits, but too much free space contributes to excessive empty space, which can hurt I/O performance, as already described. I hope some of this has helped. If not, please rephrase your question so I can fully understand exactly what you would like to know.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thanks Shaun
I try to Explain my self again .
When I use varchar(1000) for example that mean the variable length is 1000 but I dont know
how many bytes it is ?
How Can I calculate This ?
For varchar(1000), the exact size of that field, for a given row, depends on the length of the data inserted into it. It will allow up to 1000 characters, but if a smaller string is insetred into that field, it will only require the smaller number of bytes to store on disk. This differs for a char(1000) where the short data would be padded to 1000 bytes as it is inserted, and would therefore always consume 1000 bytes on disk. For nvarchar(1000), which is stored as unicode, this would take twice as many bytes to store on disk as an equivalent varchar(1000), because each character actually consumes 2 bytes. To figure out how long (in characters, not bytes) a (n)varchar in a specific row is, use LEN(fieldname) Hope this answers your question
If you have a copy of Inside SQL Server 2000 by Kalen Delaney, check out the section starting on page 255 for a very detailed explanation of how bytes are calculated for char and vchar. To extremely summarize this section, she says that calculating the exact number of bytes that a varchar column takes up varies with a number of factors, and that it is somewhat unpredictable. But when it really comes down to it, the easiest way to estimate the number of bytes a varchar column takes up is to use one byte for every character stored in the Varchar column. It will be very close to the actual amount. So, if you have a varchar(100) column, and there are only 20 characters used in the column of a single row, then estimating 20 bytes for this column will be a very close estimate, close enough for most purposes. If you really want to get detailed information on how data is stored in data pages, check out the DBCC PAGE command. It produces complex results, but if you really want to know what is going it with data on a page, this is the way to find out.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Thanks a lot guys
If 1 character is estimate to 1 byte
its anser my question .
]]>