September 21, 2017 at 5:47 am
Dear All,
Question regarding the length of a column . As its understandable it has to be in accordance with the data that occupies the location ... does it have any impact on performance ? Since a table has to be read into the memory, does it carry the free space of the cell value with it ? I mean can the reduction / change of column length / column type would make much sense for performance tuning . Thank you.
September 21, 2017 at 5:55 am
The wider your column, the fewer rows you#ll fit on a single data page and hence the more pages are likely to need to be read into memory to satisfy a query. This uses up more IO, as well as evicting older pages from memory. So yes, there is likely to be an impact on performance.
John
September 21, 2017 at 7:22 am
John Mitchell-245523 - Thursday, September 21, 2017 5:55 AMThe wider your column, the fewer rows you#ll fit on a single data page and hence the more pages are likely to need to be read into memory to satisfy a query. This uses up more IO, as well as evicting older pages from memory. So yes, there is likely to be an impact on performance.John
Thanks John . So does it mean that , a column with max length , say 10 , causes empty spaces in the page because a row in it fills up only 30% of it (is the remaining 70% loaded into memory) ?
September 21, 2017 at 7:32 am
Not necessarily, no. It depends whether your column is variable width (varchar(n), for example), and if so what values are in the column (whether they take up the whole maximum width of the column). It also depends on what other columns are in the table, the fill factors on your indexes and what INSERT, UPDATE and DELETE activity occurs on the table. The wider your table as a whole is (adding up all of the columns), the fewer rows you can fit on an 8KB page and the more likely you are to have free space on a page.
John
September 21, 2017 at 7:46 am
John Mitchell-245523 - Thursday, September 21, 2017 7:32 AMNot necessarily, no. It depends whether your column is variable width (varchar(n), for example), and if so what values are in the column (whether they take up the whole maximum width of the column). It also depends on what other columns are in the table, the fill factors on your indexes and what INSERT, UPDATE and DELETE activity occurs on the table. The wider your table as a whole is (adding up all of the columns), the fewer rows you can fit on an 8KB page and the more likely you are to have free space on a page.John
Thanks John. So CHAR/VARCHAR need to be avoided as SQL would keep them fixed and load the memory with empty spaces ?
September 21, 2017 at 7:58 am
char pads with empty space, yes. That's why you should only use it for short (up to 8 to 10 characters, say) or fixed-length data. But no, it would be undesirable and impractical to avoid these data types altogether. Use the right one for the right purpose. Type "char vs varchar" into your favourite search engine for further reading.
John
September 21, 2017 at 8:02 am
Arsh - Thursday, September 21, 2017 7:46 AMJohn Mitchell-245523 - Thursday, September 21, 2017 7:32 AMNot necessarily, no. It depends whether your column is variable width (varchar(n), for example), and if so what values are in the column (whether they take up the whole maximum width of the column). It also depends on what other columns are in the table, the fill factors on your indexes and what INSERT, UPDATE and DELETE activity occurs on the table. The wider your table as a whole is (adding up all of the columns), the fewer rows you can fit on an 8KB page and the more likely you are to have free space on a page.John
Thanks John. So CHAR/VARCHAR need to be avoided as SQL would keep them fixed and load the memory with empty spaces ?
Not exactly. CHAR or NCHAR fields will always be fixed length and all of the field gets loaded everywhere it goes. VARCHAR or NVARCHAR fields have some overhead bytes, but also mean that what is stored in the row is the overhead plus only the string value, and not any empty spaces to fill out the length of the field. For example, if a field is defined as VARCHAR(255), that means that you have the overhead bytes plus only the characters in the field, which will likely be less than 255 characters. The number of characters can vary from 0 to 255, or you can have a NULL value. CHAR(255) on the other hand, will always have 255 characters stored in the row. That's why you rarely see CHAR fields of any sizable length.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 21, 2017 at 8:19 am
sgmunson - Thursday, September 21, 2017 8:02 AMArsh - Thursday, September 21, 2017 7:46 AMJohn Mitchell-245523 - Thursday, September 21, 2017 7:32 AMNot necessarily, no. It depends whether your column is variable width (varchar(n), for example), and if so what values are in the column (whether they take up the whole maximum width of the column). It also depends on what other columns are in the table, the fill factors on your indexes and what INSERT, UPDATE and DELETE activity occurs on the table. The wider your table as a whole is (adding up all of the columns), the fewer rows you can fit on an 8KB page and the more likely you are to have free space on a page.John
Thanks John. So CHAR/VARCHAR need to be avoided as SQL would keep them fixed and load the memory with empty spaces ?
Not exactly. CHAR or NCHAR fields will always be fixed length and all of the field gets loaded everywhere it goes. VARCHAR or NVARCHAR fields have some overhead bytes, but also mean that what is stored in the row is the overhead plus only the string value, and not any empty spaces to fill out the length of the field. For example, if a field is defined as VARCHAR(255), that means that you have the overhead bytes plus only the characters in the field, which will likely be less than 255 characters. The number of characters can vary from 0 to 255, or you can have a NULL value. CHAR(255) on the other hand, will always have 255 characters stored in the row. That's why you rarely see CHAR fields of any sizable length.
It's super fun when a developer gives you an alter table statement for a large table and you don't realize they used a char(some large value) instead of varchar until after you run it in production :angry:
September 21, 2017 at 8:42 am
September 21, 2017 at 8:47 am
Arsh - Thursday, September 21, 2017 8:42 AMThank you ZZartin and John . I think I didn't keep my question clear . Narrowing it down ..... Will SQL load the memory with empty spaces/memory-holes when it finds that the column max length is at 8 but the current content in question in that column is of length 4. The 4 memory locations have no data . Does SQL carry them to memory too ?
If the column is CHAR/NCHAR Yes
If the column is VARCHAR/NVARCHAR No
September 21, 2017 at 8:48 am
Yes. The database engine doesn't read in units smaller than a page. So if you want to see something on a data page, the whole page is read into memory. That's why smallest is best for performance - so that you can fit more rows on a single page.
John
September 21, 2017 at 10:04 am
Thank you John,Anthony and all others for clarification on the question.Thanks.
September 22, 2017 at 9:02 am
Columns lengths have another effect on memory allocation as well. SQL reserves memory prior to processing a table. It determines the amount of memory to reserve partly based on column lengths, as would typically make sense. Thus, though, if you have a lot of columns defined as, say, varchar(500) or varchar(1000) even they only ever used 10 bytes, you would cause SQL to pre-allocate a lot more memory than it would really need.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 23, 2017 at 2:34 am
Thanks so much Scott.Got it.
September 25, 2017 at 9:55 am
Arsh - Thursday, September 21, 2017 5:47 AMDear All,
Question regarding the length of a column . As its understandable it has to be in accordance with the data that occupies the location ... does it have any impact on performance ? Since a table has to be read into the memory, does it carry the free space of the cell value with it ? I mean can the reduction / change of column length / column type would make much sense for performance tuning . Thank you.
If we're talking about how the definition of a column impacts storage or performance, a column like LastName VARCHAR(300) does not consume more space than LastName VARCHAR(30), and neither does it impact performance internally within SQL Server. The maximum length defined for a variable width character column is essentially just a meta-data constraint.
However, it does matter to SSIS. Columns within dataflow buffers are fixed width, and the preallocated width of a buffer in SSIS is based on the maximum width of a column. The same applies to other ETL tools as well.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply