Using column size much larger than necessarySQL Server Database Size much higher than the backup size after restore“Variant” values structure and their use for multiple columnsIndex not being used in SELECT queryWhy the backup file size is much larger than the restored databaseSpeeding up insert in SQL ServerInteger ID vs varchar ID with JOINbackup log larger than log fileSQL Server 2008 R2 differential backups much larger than expectedStoring NULL versus storing '' in a varchar columnTSQL - create a function that takes a multi-row query result as an argument
tikz: not so precise graphic
Why in a Ethernet LAN, a packet sniffer can obtain all packets sent over the LAN?
How to use structured binding in an array passed as arg to some function?
How does Howard Stark know this?
Can the sorting of a list be verified without comparing neighbors?
Does kinetic energy warp spacetime?
Does Lawful Interception of 4G / the proposed 5G provide a back door for hackers as well?
What stroke width Instagram is using for its icons and how to get same results?
How can this triangle figure be modeled/drawn with TikZ?
Is it a bad idea to replace pull-up resistors with hard pull-ups?
As programers say: Strive to be lazy
List software from restricted, multiverse separately
What is the best way for a skeleton to impersonate human without using magic?
How to make a language evolve quickly?
Reaction of borax with NaOH
Make all the squares explode
How to cope with regret and shame about not fully utilizing opportunities during PhD?
Create a list of all possible Boolean configurations of three constraints
Anatomically Correct Carnivorous Tree
Who was this character from the Tomb of Annihilation adventure before they became a monster?
Why was the Ancient One so hesitant to teach Dr. Strange the art of sorcery?
Two researchers want to work on the same extension to my paper. Who to help?
What does i386 mean on macOS Mojave?
Why does the Earth follow an elliptical trajectory rather than a parabolic one?
Using column size much larger than necessary
SQL Server Database Size much higher than the backup size after restore“Variant” values structure and their use for multiple columnsIndex not being used in SELECT queryWhy the backup file size is much larger than the restored databaseSpeeding up insert in SQL ServerInteger ID vs varchar ID with JOINbackup log larger than log fileSQL Server 2008 R2 differential backups much larger than expectedStoring NULL versus storing '' in a varchar columnTSQL - create a function that takes a multi-row query result as an argument
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I'm creating an SQL Server database with someone else. One of the tables is small (6 rows) with data that will probably remain constant. There is a remote possibility that a new row will be added. The table looks something like this:
CREATE TABLE someTable (
id int primary key identity(1,1) not null,
name varchar(128) not null unique
);
INSERT INTO someTable values ('alice', 'bob something', 'charles can dance', 'dugan was here');
I'm looking at the char length of that name column, and I think that its values are probably never going to be larger than, say, 32 characters, maybe not even larger than 24. Is there any advantage to my changing this column to, for example, varchar(32)?
Also, is there any advantage to keeping default column sizes to multiples of 4, 8, 32, etc?
sql-server database-design varchar
asked May 1 at 14:05
elbillafelbillaf
1817
|
show 1 more comment
I'm creating an SQL Server database with someone else. One of the tables is small (6 rows) with data that will probably remain constant. There is a remote possibility that a new row will be added. The table looks something like this:
CREATE TABLE someTable (
id int primary key identity(1,1) not null,
name varchar(128) not null unique
);
INSERT INTO someTable values ('alice', 'bob something', 'charles can dance', 'dugan was here');
I'm looking at the char length of that name column, and I think that its values are probably never going to be larger than, say, 32 characters, maybe not even larger than 24. Is there any advantage to my changing this column to, for example, varchar(32)?
Also, is there any advantage to keeping default column sizes to multiples of 4, 8, 32, etc?
sql-server database-design varchar
asked May 1 at 14:05
elbillafelbillaf
1817
3
Yes make fields with reasonable size, but that’s more of a problem for 2000 vs. 100 and not 128 vs. 32. Having said that there are naming standards which recommend (for person names) 35+35 or even 100 chars. 128 does not look far off.
– eckes
May 1 at 16:09
3
Might be worth noting that 128 is kind of a special number in SQL Server because that's the max length of certain object names. So if you're storing object names in this table, it would make sense to use exactly 128. Don't ask me how I thought of this >.>
– Jacob H
May 1 at 17:03
@JacobH storing object names is whatsysnameis for.
– AakashM
May 2 at 13:55
2
Please ignore the downvote. You've asked a good question, I think someone is just unhappy about the result and is taking it out on everyone.
– Aaron Bertrand♦
May 2 at 15:32
2
@Joe The defined size (and not the actual populated size) dictates memory grants, which can definitely become a performance/concurrency issue.
– Aaron Bertrand♦
May 2 at 15:51
|
show 1 more comment
I'm creating an SQL Server database with someone else. One of the tables is small (6 rows) with data that will probably remain constant. There is a remote possibility that a new row will be added. The table looks something like this:
CREATE TABLE someTable (
id int primary key identity(1,1) not null,
name varchar(128) not null unique
);
INSERT INTO someTable values ('alice', 'bob something', 'charles can dance', 'dugan was here');
I'm looking at the char length of that name column, and I think that its values are probably never going to be larger than, say, 32 characters, maybe not even larger than 24. Is there any advantage to my changing this column to, for example, varchar(32)?
Also, is there any advantage to keeping default column sizes to multiples of 4, 8, 32, etc?
sql-server database-design varchar
asked May 1 at 14:05
elbillafelbillaf
1817
I'm creating an SQL Server database with someone else. One of the tables is small (6 rows) with data that will probably remain constant. There is a remote possibility that a new row will be added. The table looks something like this:
CREATE TABLE someTable (
id int primary key identity(1,1) not null,
name varchar(128) not null unique
);
INSERT INTO someTable values ('alice', 'bob something', 'charles can dance', 'dugan was here');
I'm looking at the char length of that name column, and I think that its values are probably never going to be larger than, say, 32 characters, maybe not even larger than 24. Is there any advantage to my changing this column to, for example, varchar(32)?
Also, is there any advantage to keeping default column sizes to multiples of 4, 8, 32, etc?
sql-server database-design varchar
sql-server database-design varchar
asked May 1 at 14:05
elbillafelbillaf
1817
asked May 1 at 14:05
elbillafelbillaf
1817
edited May 3 at 14:36
elbillaf
asked May 1 at 14:05
elbillafelbillaf
1817
asked May 1 at 14:05
elbillafelbillaf
1817
asked May 1 at 14:05
elbillafelbillaf
1817
1817
3
Yes make fields with reasonable size, but that’s more of a problem for 2000 vs. 100 and not 128 vs. 32. Having said that there are naming standards which recommend (for person names) 35+35 or even 100 chars. 128 does not look far off.
– eckes
May 1 at 16:09
3
Might be worth noting that 128 is kind of a special number in SQL Server because that's the max length of certain object names. So if you're storing object names in this table, it would make sense to use exactly 128. Don't ask me how I thought of this >.>
– Jacob H
May 1 at 17:03
@JacobH storing object names is whatsysnameis for.
– AakashM
May 2 at 13:55
2
Please ignore the downvote. You've asked a good question, I think someone is just unhappy about the result and is taking it out on everyone.
– Aaron Bertrand♦
May 2 at 15:32
2
@Joe The defined size (and not the actual populated size) dictates memory grants, which can definitely become a performance/concurrency issue.
– Aaron Bertrand♦
May 2 at 15:51
|
show 1 more comment
3
Yes make fields with reasonable size, but that’s more of a problem for 2000 vs. 100 and not 128 vs. 32. Having said that there are naming standards which recommend (for person names) 35+35 or even 100 chars. 128 does not look far off.
– eckes
May 1 at 16:09
3
Might be worth noting that 128 is kind of a special number in SQL Server because that's the max length of certain object names. So if you're storing object names in this table, it would make sense to use exactly 128. Don't ask me how I thought of this >.>
– Jacob H
May 1 at 17:03
@JacobH storing object names is whatsysnameis for.
– AakashM
May 2 at 13:55
2
Please ignore the downvote. You've asked a good question, I think someone is just unhappy about the result and is taking it out on everyone.
– Aaron Bertrand♦
May 2 at 15:32
2
@Joe The defined size (and not the actual populated size) dictates memory grants, which can definitely become a performance/concurrency issue.
– Aaron Bertrand♦
May 2 at 15:51
3
3
Yes make fields with reasonable size, but that’s more of a problem for 2000 vs. 100 and not 128 vs. 32. Having said that there are naming standards which recommend (for person names) 35+35 or even 100 chars. 128 does not look far off.
– eckes
May 1 at 16:09
Yes make fields with reasonable size, but that’s more of a problem for 2000 vs. 100 and not 128 vs. 32. Having said that there are naming standards which recommend (for person names) 35+35 or even 100 chars. 128 does not look far off.
– eckes
May 1 at 16:09
3
3
Might be worth noting that 128 is kind of a special number in SQL Server because that's the max length of certain object names. So if you're storing object names in this table, it would make sense to use exactly 128. Don't ask me how I thought of this >.>
– Jacob H
May 1 at 17:03
Might be worth noting that 128 is kind of a special number in SQL Server because that's the max length of certain object names. So if you're storing object names in this table, it would make sense to use exactly 128. Don't ask me how I thought of this >.>
– Jacob H
May 1 at 17:03
@JacobH storing object names is what
sysname is for.– AakashM
May 2 at 13:55
@JacobH storing object names is what
sysname is for.– AakashM
May 2 at 13:55
2
2
Please ignore the downvote. You've asked a good question, I think someone is just unhappy about the result and is taking it out on everyone.
– Aaron Bertrand♦
May 2 at 15:32
Please ignore the downvote. You've asked a good question, I think someone is just unhappy about the result and is taking it out on everyone.
– Aaron Bertrand♦
May 2 at 15:32
2
2
@Joe The defined size (and not the actual populated size) dictates memory grants, which can definitely become a performance/concurrency issue.
– Aaron Bertrand♦
May 2 at 15:51
@Joe The defined size (and not the actual populated size) dictates memory grants, which can definitely become a performance/concurrency issue.
– Aaron Bertrand♦
May 2 at 15:51
|
show 1 more comment
2 Answers
2
active
oldest
votes
SQL Server uses column lengths when allocating memory for query processing. So, yes, in short, you should always size columns appropriately for the data.
Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column.
Having said that, in this case where you've got 6 rows you probably don't want to over optimize prematurely. Unless you JOIN this table to another with millions of rows, there won't be a massive difference between a varchar(24) and a varchar(32), or even a varchar(128).
Your second question asks about aligning column lengths on binary multiples. That's not required at all since SQL Server stores all data in 8KB pages, regardless of the length of each column.
answered May 1 at 14:21
Max VernonMax Vernon
53.2k13116234
1
I have found no performance difference when using Varchar(8000) vs. Varchar(20). This is on joins to multiple tables with rowcounts on the largest tables being 500 million and 100 million. Total size of the two tables is 300GB. Of course my join columns are kept short and almost always int or bigint.
– Joe
May 2 at 15:46
2
@Joe Just because you haven't observed performance differences doesn't mean they can't exist. This sounds like the Bear Patrol.
– Aaron Bertrand♦
May 2 at 15:54
1
@joe - fantastic. You'll keep people like me employed with lots of performance tuning work.
– Max Vernon
May 2 at 15:56
1
@AaronBertrand, thanks I learned something new today. In future scenarios I might need to reconsider. Either the hardware I work on or the specific use cases haven't shown an impact to date but I see from this article that it might be a factor in the future. sqlperformance.com/2017/06/sql-plan/…
– Joe
May 2 at 16:27
3
@Joe nothing is ever a performance issue until it is. I find it is very hard to find cases where testing an app has fully reflected the same scale and concurrency the app eventually hits in production ("worked on my machine" / "worked in dev"), and it's at those levels of scale and concurrency where this specific issue becomes a problem. Your scenario, even though you've tested it, will have a breaking point. You may be lucky enough to never hit it, but again, that doesn't mean it can't be hit. Tell me where your app is and I'll give it a shot. :-)
– Aaron Bertrand♦
May 2 at 16:41
|
show 1 more comment
With 6 rows, no, there will be no observable benefit. That entire table will fit on a single page so lowering the maximum potential space you’ll use on that page while still occupying that entire page is really no different in all practical sense.
On larger tables, though, right-sizing is crucial. The reason is that memory estimates will be based on the assumption that every value will be 50% populated. So if you have varchar(128), every value will expect to occupy 64 bytes, regardless of the actual data, therefore memory grants will be 64b * number of rows. If all the values will be 32 characters or less, making it a varchar(64) or even varchar(32) is probably a better choice. If a large percentage of values are close to or at the cap, you could even argue for char to take volatility out of it.
As for benefits of having string lengths capped at powers of 2, I don’t think on today’s hardware anyone could demonstrate any obvious advantages.
answered May 1 at 14:24
Aaron Bertrand♦Aaron Bertrand
156k18303508
2
@jpmc26 -1 all you like. Sometimes char is a valid choice, and sometimes what a developer has to learn how to handle (or that you have to obscure from them) isn't top priority. Anyway if you read closely my answer wasn't "use char" it was mentioned as an aside with a very specific use case as a disclaimer.
– Aaron Bertrand♦
May 2 at 13:39
4
@jpmc26 When was the last time you had the freedom to just add RAM to a production server that was wasting memory? I would -1 anywhere where you said the best approach is always to just throw hardware at something.
– Aaron Bertrand♦
May 2 at 13:41
2
@jpmc26 Agree to disagree. Adding hardware might sound like the simpler thing in some cases, but adding memory isn't always possible. It's not always your hardware. There aren't always free RAM slots. Sometimes you're already using the highest capacity chips available. I think in theory that argument works but not always in practice. Just like fragmentation, wasting memory has downstream effects too (think readable secondaries that are less equipped). And just to re-state one more time: I am not advocating char here, just mentioning it as a possibility in very specific cases.
– Aaron Bertrand♦
May 2 at 13:55
2
@jpmc26 I also don't believe that changing a varchar column to char(32) because it always or almost always contains all 32 characters is either illogical or requires extra work. Tell me all about the extra work a developer has to perform, or bugs that might be created, if they are dealing with char instead of varchar. Maybe we should petition Microsoft to remove the char type if it is so useless and dangerous?
– Aaron Bertrand♦
May 2 at 15:23
2
@jpmc26 an app that is writing data doesn't have to trim anything. An app that is displaying data also doesn't have to trim anything. An app that is comparing data shouldn't have to worry about trailing spaces unless it is building literals inside of ad hoc queries. All of those cases are moot anyway if we're talking about character data that uses the entire column width. I repeated it twice already but, once more, this is not my first string pick, it is a use case that may be appropriate sometimes. Stop talking to me like I told everyone they all have to abandon varchar tomorrow.
– Aaron Bertrand♦
May 2 at 20:33
|
show 7 more comments
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237128%2fusing-column-size-much-larger-than-necessary%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
SQL Server uses column lengths when allocating memory for query processing. So, yes, in short, you should always size columns appropriately for the data.
Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column.
Having said that, in this case where you've got 6 rows you probably don't want to over optimize prematurely. Unless you JOIN this table to another with millions of rows, there won't be a massive difference between a varchar(24) and a varchar(32), or even a varchar(128).
Your second question asks about aligning column lengths on binary multiples. That's not required at all since SQL Server stores all data in 8KB pages, regardless of the length of each column.
answered May 1 at 14:21
Max VernonMax Vernon
53.2k13116234
1
I have found no performance difference when using Varchar(8000) vs. Varchar(20). This is on joins to multiple tables with rowcounts on the largest tables being 500 million and 100 million. Total size of the two tables is 300GB. Of course my join columns are kept short and almost always int or bigint.
– Joe
May 2 at 15:46
2
@Joe Just because you haven't observed performance differences doesn't mean they can't exist. This sounds like the Bear Patrol.
– Aaron Bertrand♦
May 2 at 15:54
1
@joe - fantastic. You'll keep people like me employed with lots of performance tuning work.
– Max Vernon
May 2 at 15:56
1
@AaronBertrand, thanks I learned something new today. In future scenarios I might need to reconsider. Either the hardware I work on or the specific use cases haven't shown an impact to date but I see from this article that it might be a factor in the future. sqlperformance.com/2017/06/sql-plan/…
– Joe
May 2 at 16:27
3
@Joe nothing is ever a performance issue until it is. I find it is very hard to find cases where testing an app has fully reflected the same scale and concurrency the app eventually hits in production ("worked on my machine" / "worked in dev"), and it's at those levels of scale and concurrency where this specific issue becomes a problem. Your scenario, even though you've tested it, will have a breaking point. You may be lucky enough to never hit it, but again, that doesn't mean it can't be hit. Tell me where your app is and I'll give it a shot. :-)
– Aaron Bertrand♦
May 2 at 16:41
|
show 1 more comment
SQL Server uses column lengths when allocating memory for query processing. So, yes, in short, you should always size columns appropriately for the data.
Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column.
Having said that, in this case where you've got 6 rows you probably don't want to over optimize prematurely. Unless you JOIN this table to another with millions of rows, there won't be a massive difference between a varchar(24) and a varchar(32), or even a varchar(128).
Your second question asks about aligning column lengths on binary multiples. That's not required at all since SQL Server stores all data in 8KB pages, regardless of the length of each column.
answered May 1 at 14:21
Max VernonMax Vernon
53.2k13116234
1
I have found no performance difference when using Varchar(8000) vs. Varchar(20). This is on joins to multiple tables with rowcounts on the largest tables being 500 million and 100 million. Total size of the two tables is 300GB. Of course my join columns are kept short and almost always int or bigint.
– Joe
May 2 at 15:46
2
@Joe Just because you haven't observed performance differences doesn't mean they can't exist. This sounds like the Bear Patrol.
– Aaron Bertrand♦
May 2 at 15:54
1
@joe - fantastic. You'll keep people like me employed with lots of performance tuning work.
– Max Vernon
May 2 at 15:56
1
@AaronBertrand, thanks I learned something new today. In future scenarios I might need to reconsider. Either the hardware I work on or the specific use cases haven't shown an impact to date but I see from this article that it might be a factor in the future. sqlperformance.com/2017/06/sql-plan/…
– Joe
May 2 at 16:27
3
@Joe nothing is ever a performance issue until it is. I find it is very hard to find cases where testing an app has fully reflected the same scale and concurrency the app eventually hits in production ("worked on my machine" / "worked in dev"), and it's at those levels of scale and concurrency where this specific issue becomes a problem. Your scenario, even though you've tested it, will have a breaking point. You may be lucky enough to never hit it, but again, that doesn't mean it can't be hit. Tell me where your app is and I'll give it a shot. :-)
– Aaron Bertrand♦
May 2 at 16:41
|
show 1 more comment
SQL Server uses column lengths when allocating memory for query processing. So, yes, in short, you should always size columns appropriately for the data.
Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column.
Having said that, in this case where you've got 6 rows you probably don't want to over optimize prematurely. Unless you JOIN this table to another with millions of rows, there won't be a massive difference between a varchar(24) and a varchar(32), or even a varchar(128).
Your second question asks about aligning column lengths on binary multiples. That's not required at all since SQL Server stores all data in 8KB pages, regardless of the length of each column.
answered May 1 at 14:21
Max VernonMax Vernon
53.2k13116234
SQL Server uses column lengths when allocating memory for query processing. So, yes, in short, you should always size columns appropriately for the data.
Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column.
Having said that, in this case where you've got 6 rows you probably don't want to over optimize prematurely. Unless you JOIN this table to another with millions of rows, there won't be a massive difference between a varchar(24) and a varchar(32), or even a varchar(128).
Your second question asks about aligning column lengths on binary multiples. That's not required at all since SQL Server stores all data in 8KB pages, regardless of the length of each column.
answered May 1 at 14:21
Max VernonMax Vernon
53.2k13116234
answered May 1 at 14:21
Max VernonMax Vernon
53.2k13116234
answered May 1 at 14:21
Max VernonMax Vernon
53.2k13116234
answered May 1 at 14:21
Max VernonMax Vernon
53.2k13116234
53.2k13116234
1
I have found no performance difference when using Varchar(8000) vs. Varchar(20). This is on joins to multiple tables with rowcounts on the largest tables being 500 million and 100 million. Total size of the two tables is 300GB. Of course my join columns are kept short and almost always int or bigint.
– Joe
May 2 at 15:46
2
@Joe Just because you haven't observed performance differences doesn't mean they can't exist. This sounds like the Bear Patrol.
– Aaron Bertrand♦
May 2 at 15:54
1
@joe - fantastic. You'll keep people like me employed with lots of performance tuning work.
– Max Vernon
May 2 at 15:56
1
@AaronBertrand, thanks I learned something new today. In future scenarios I might need to reconsider. Either the hardware I work on or the specific use cases haven't shown an impact to date but I see from this article that it might be a factor in the future. sqlperformance.com/2017/06/sql-plan/…
– Joe
May 2 at 16:27
3
@Joe nothing is ever a performance issue until it is. I find it is very hard to find cases where testing an app has fully reflected the same scale and concurrency the app eventually hits in production ("worked on my machine" / "worked in dev"), and it's at those levels of scale and concurrency where this specific issue becomes a problem. Your scenario, even though you've tested it, will have a breaking point. You may be lucky enough to never hit it, but again, that doesn't mean it can't be hit. Tell me where your app is and I'll give it a shot. :-)
– Aaron Bertrand♦
May 2 at 16:41
|
show 1 more comment
1
I have found no performance difference when using Varchar(8000) vs. Varchar(20). This is on joins to multiple tables with rowcounts on the largest tables being 500 million and 100 million. Total size of the two tables is 300GB. Of course my join columns are kept short and almost always int or bigint.
– Joe
May 2 at 15:46
2
@Joe Just because you haven't observed performance differences doesn't mean they can't exist. This sounds like the Bear Patrol.
– Aaron Bertrand♦
May 2 at 15:54
1
@joe - fantastic. You'll keep people like me employed with lots of performance tuning work.
– Max Vernon
May 2 at 15:56
1
@AaronBertrand, thanks I learned something new today. In future scenarios I might need to reconsider. Either the hardware I work on or the specific use cases haven't shown an impact to date but I see from this article that it might be a factor in the future. sqlperformance.com/2017/06/sql-plan/…
– Joe
May 2 at 16:27
3
@Joe nothing is ever a performance issue until it is. I find it is very hard to find cases where testing an app has fully reflected the same scale and concurrency the app eventually hits in production ("worked on my machine" / "worked in dev"), and it's at those levels of scale and concurrency where this specific issue becomes a problem. Your scenario, even though you've tested it, will have a breaking point. You may be lucky enough to never hit it, but again, that doesn't mean it can't be hit. Tell me where your app is and I'll give it a shot. :-)
– Aaron Bertrand♦
May 2 at 16:41
1
1
I have found no performance difference when using Varchar(8000) vs. Varchar(20). This is on joins to multiple tables with rowcounts on the largest tables being 500 million and 100 million. Total size of the two tables is 300GB. Of course my join columns are kept short and almost always int or bigint.
– Joe
May 2 at 15:46
I have found no performance difference when using Varchar(8000) vs. Varchar(20). This is on joins to multiple tables with rowcounts on the largest tables being 500 million and 100 million. Total size of the two tables is 300GB. Of course my join columns are kept short and almost always int or bigint.
– Joe
May 2 at 15:46
2
2
@Joe Just because you haven't observed performance differences doesn't mean they can't exist. This sounds like the Bear Patrol.
– Aaron Bertrand♦
May 2 at 15:54
@Joe Just because you haven't observed performance differences doesn't mean they can't exist. This sounds like the Bear Patrol.
– Aaron Bertrand♦
May 2 at 15:54
1
1
@joe - fantastic. You'll keep people like me employed with lots of performance tuning work.
– Max Vernon
May 2 at 15:56
@joe - fantastic. You'll keep people like me employed with lots of performance tuning work.
– Max Vernon
May 2 at 15:56
1
1
@AaronBertrand, thanks I learned something new today. In future scenarios I might need to reconsider. Either the hardware I work on or the specific use cases haven't shown an impact to date but I see from this article that it might be a factor in the future. sqlperformance.com/2017/06/sql-plan/…
– Joe
May 2 at 16:27
@AaronBertrand, thanks I learned something new today. In future scenarios I might need to reconsider. Either the hardware I work on or the specific use cases haven't shown an impact to date but I see from this article that it might be a factor in the future. sqlperformance.com/2017/06/sql-plan/…
– Joe
May 2 at 16:27
3
3
@Joe nothing is ever a performance issue until it is. I find it is very hard to find cases where testing an app has fully reflected the same scale and concurrency the app eventually hits in production ("worked on my machine" / "worked in dev"), and it's at those levels of scale and concurrency where this specific issue becomes a problem. Your scenario, even though you've tested it, will have a breaking point. You may be lucky enough to never hit it, but again, that doesn't mean it can't be hit. Tell me where your app is and I'll give it a shot. :-)
– Aaron Bertrand♦
May 2 at 16:41
@Joe nothing is ever a performance issue until it is. I find it is very hard to find cases where testing an app has fully reflected the same scale and concurrency the app eventually hits in production ("worked on my machine" / "worked in dev"), and it's at those levels of scale and concurrency where this specific issue becomes a problem. Your scenario, even though you've tested it, will have a breaking point. You may be lucky enough to never hit it, but again, that doesn't mean it can't be hit. Tell me where your app is and I'll give it a shot. :-)
– Aaron Bertrand♦
May 2 at 16:41
|
show 1 more comment
With 6 rows, no, there will be no observable benefit. That entire table will fit on a single page so lowering the maximum potential space you’ll use on that page while still occupying that entire page is really no different in all practical sense.
On larger tables, though, right-sizing is crucial. The reason is that memory estimates will be based on the assumption that every value will be 50% populated. So if you have varchar(128), every value will expect to occupy 64 bytes, regardless of the actual data, therefore memory grants will be 64b * number of rows. If all the values will be 32 characters or less, making it a varchar(64) or even varchar(32) is probably a better choice. If a large percentage of values are close to or at the cap, you could even argue for char to take volatility out of it.
As for benefits of having string lengths capped at powers of 2, I don’t think on today’s hardware anyone could demonstrate any obvious advantages.
answered May 1 at 14:24
Aaron Bertrand♦Aaron Bertrand
156k18303508
2
@jpmc26 -1 all you like. Sometimes char is a valid choice, and sometimes what a developer has to learn how to handle (or that you have to obscure from them) isn't top priority. Anyway if you read closely my answer wasn't "use char" it was mentioned as an aside with a very specific use case as a disclaimer.
– Aaron Bertrand♦
May 2 at 13:39
4
@jpmc26 When was the last time you had the freedom to just add RAM to a production server that was wasting memory? I would -1 anywhere where you said the best approach is always to just throw hardware at something.
– Aaron Bertrand♦
May 2 at 13:41
2
@jpmc26 Agree to disagree. Adding hardware might sound like the simpler thing in some cases, but adding memory isn't always possible. It's not always your hardware. There aren't always free RAM slots. Sometimes you're already using the highest capacity chips available. I think in theory that argument works but not always in practice. Just like fragmentation, wasting memory has downstream effects too (think readable secondaries that are less equipped). And just to re-state one more time: I am not advocating char here, just mentioning it as a possibility in very specific cases.
– Aaron Bertrand♦
May 2 at 13:55
2
@jpmc26 I also don't believe that changing a varchar column to char(32) because it always or almost always contains all 32 characters is either illogical or requires extra work. Tell me all about the extra work a developer has to perform, or bugs that might be created, if they are dealing with char instead of varchar. Maybe we should petition Microsoft to remove the char type if it is so useless and dangerous?
– Aaron Bertrand♦
May 2 at 15:23
2
@jpmc26 an app that is writing data doesn't have to trim anything. An app that is displaying data also doesn't have to trim anything. An app that is comparing data shouldn't have to worry about trailing spaces unless it is building literals inside of ad hoc queries. All of those cases are moot anyway if we're talking about character data that uses the entire column width. I repeated it twice already but, once more, this is not my first string pick, it is a use case that may be appropriate sometimes. Stop talking to me like I told everyone they all have to abandon varchar tomorrow.
– Aaron Bertrand♦
May 2 at 20:33
|
show 7 more comments
With 6 rows, no, there will be no observable benefit. That entire table will fit on a single page so lowering the maximum potential space you’ll use on that page while still occupying that entire page is really no different in all practical sense.
On larger tables, though, right-sizing is crucial. The reason is that memory estimates will be based on the assumption that every value will be 50% populated. So if you have varchar(128), every value will expect to occupy 64 bytes, regardless of the actual data, therefore memory grants will be 64b * number of rows. If all the values will be 32 characters or less, making it a varchar(64) or even varchar(32) is probably a better choice. If a large percentage of values are close to or at the cap, you could even argue for char to take volatility out of it.
As for benefits of having string lengths capped at powers of 2, I don’t think on today’s hardware anyone could demonstrate any obvious advantages.
answered May 1 at 14:24
Aaron Bertrand♦Aaron Bertrand
156k18303508
2
@jpmc26 -1 all you like. Sometimes char is a valid choice, and sometimes what a developer has to learn how to handle (or that you have to obscure from them) isn't top priority. Anyway if you read closely my answer wasn't "use char" it was mentioned as an aside with a very specific use case as a disclaimer.
– Aaron Bertrand♦
May 2 at 13:39
4
@jpmc26 When was the last time you had the freedom to just add RAM to a production server that was wasting memory? I would -1 anywhere where you said the best approach is always to just throw hardware at something.
– Aaron Bertrand♦
May 2 at 13:41
2
@jpmc26 Agree to disagree. Adding hardware might sound like the simpler thing in some cases, but adding memory isn't always possible. It's not always your hardware. There aren't always free RAM slots. Sometimes you're already using the highest capacity chips available. I think in theory that argument works but not always in practice. Just like fragmentation, wasting memory has downstream effects too (think readable secondaries that are less equipped). And just to re-state one more time: I am not advocating char here, just mentioning it as a possibility in very specific cases.
– Aaron Bertrand♦
May 2 at 13:55
2
@jpmc26 I also don't believe that changing a varchar column to char(32) because it always or almost always contains all 32 characters is either illogical or requires extra work. Tell me all about the extra work a developer has to perform, or bugs that might be created, if they are dealing with char instead of varchar. Maybe we should petition Microsoft to remove the char type if it is so useless and dangerous?
– Aaron Bertrand♦
May 2 at 15:23
2
@jpmc26 an app that is writing data doesn't have to trim anything. An app that is displaying data also doesn't have to trim anything. An app that is comparing data shouldn't have to worry about trailing spaces unless it is building literals inside of ad hoc queries. All of those cases are moot anyway if we're talking about character data that uses the entire column width. I repeated it twice already but, once more, this is not my first string pick, it is a use case that may be appropriate sometimes. Stop talking to me like I told everyone they all have to abandon varchar tomorrow.
– Aaron Bertrand♦
May 2 at 20:33
|
show 7 more comments
With 6 rows, no, there will be no observable benefit. That entire table will fit on a single page so lowering the maximum potential space you’ll use on that page while still occupying that entire page is really no different in all practical sense.
On larger tables, though, right-sizing is crucial. The reason is that memory estimates will be based on the assumption that every value will be 50% populated. So if you have varchar(128), every value will expect to occupy 64 bytes, regardless of the actual data, therefore memory grants will be 64b * number of rows. If all the values will be 32 characters or less, making it a varchar(64) or even varchar(32) is probably a better choice. If a large percentage of values are close to or at the cap, you could even argue for char to take volatility out of it.
As for benefits of having string lengths capped at powers of 2, I don’t think on today’s hardware anyone could demonstrate any obvious advantages.
answered May 1 at 14:24
Aaron Bertrand♦Aaron Bertrand
156k18303508
With 6 rows, no, there will be no observable benefit. That entire table will fit on a single page so lowering the maximum potential space you’ll use on that page while still occupying that entire page is really no different in all practical sense.
On larger tables, though, right-sizing is crucial. The reason is that memory estimates will be based on the assumption that every value will be 50% populated. So if you have varchar(128), every value will expect to occupy 64 bytes, regardless of the actual data, therefore memory grants will be 64b * number of rows. If all the values will be 32 characters or less, making it a varchar(64) or even varchar(32) is probably a better choice. If a large percentage of values are close to or at the cap, you could even argue for char to take volatility out of it.
As for benefits of having string lengths capped at powers of 2, I don’t think on today’s hardware anyone could demonstrate any obvious advantages.
answered May 1 at 14:24
Aaron Bertrand♦Aaron Bertrand
156k18303508
answered May 1 at 14:24
Aaron Bertrand♦Aaron Bertrand
156k18303508
answered May 1 at 14:24
Aaron Bertrand♦Aaron Bertrand
156k18303508
answered May 1 at 14:24
Aaron Bertrand♦Aaron Bertrand
156k18303508
156k18303508
2
@jpmc26 -1 all you like. Sometimes char is a valid choice, and sometimes what a developer has to learn how to handle (or that you have to obscure from them) isn't top priority. Anyway if you read closely my answer wasn't "use char" it was mentioned as an aside with a very specific use case as a disclaimer.
– Aaron Bertrand♦
May 2 at 13:39
4
@jpmc26 When was the last time you had the freedom to just add RAM to a production server that was wasting memory? I would -1 anywhere where you said the best approach is always to just throw hardware at something.
– Aaron Bertrand♦
May 2 at 13:41
2
@jpmc26 Agree to disagree. Adding hardware might sound like the simpler thing in some cases, but adding memory isn't always possible. It's not always your hardware. There aren't always free RAM slots. Sometimes you're already using the highest capacity chips available. I think in theory that argument works but not always in practice. Just like fragmentation, wasting memory has downstream effects too (think readable secondaries that are less equipped). And just to re-state one more time: I am not advocating char here, just mentioning it as a possibility in very specific cases.
– Aaron Bertrand♦
May 2 at 13:55
2
@jpmc26 I also don't believe that changing a varchar column to char(32) because it always or almost always contains all 32 characters is either illogical or requires extra work. Tell me all about the extra work a developer has to perform, or bugs that might be created, if they are dealing with char instead of varchar. Maybe we should petition Microsoft to remove the char type if it is so useless and dangerous?
– Aaron Bertrand♦
May 2 at 15:23
2
@jpmc26 an app that is writing data doesn't have to trim anything. An app that is displaying data also doesn't have to trim anything. An app that is comparing data shouldn't have to worry about trailing spaces unless it is building literals inside of ad hoc queries. All of those cases are moot anyway if we're talking about character data that uses the entire column width. I repeated it twice already but, once more, this is not my first string pick, it is a use case that may be appropriate sometimes. Stop talking to me like I told everyone they all have to abandon varchar tomorrow.
– Aaron Bertrand♦
May 2 at 20:33
|
show 7 more comments
2
@jpmc26 -1 all you like. Sometimes char is a valid choice, and sometimes what a developer has to learn how to handle (or that you have to obscure from them) isn't top priority. Anyway if you read closely my answer wasn't "use char" it was mentioned as an aside with a very specific use case as a disclaimer.
– Aaron Bertrand♦
May 2 at 13:39
4
@jpmc26 When was the last time you had the freedom to just add RAM to a production server that was wasting memory? I would -1 anywhere where you said the best approach is always to just throw hardware at something.
– Aaron Bertrand♦
May 2 at 13:41
2
@jpmc26 Agree to disagree. Adding hardware might sound like the simpler thing in some cases, but adding memory isn't always possible. It's not always your hardware. There aren't always free RAM slots. Sometimes you're already using the highest capacity chips available. I think in theory that argument works but not always in practice. Just like fragmentation, wasting memory has downstream effects too (think readable secondaries that are less equipped). And just to re-state one more time: I am not advocating char here, just mentioning it as a possibility in very specific cases.
– Aaron Bertrand♦
May 2 at 13:55
2
@jpmc26 I also don't believe that changing a varchar column to char(32) because it always or almost always contains all 32 characters is either illogical or requires extra work. Tell me all about the extra work a developer has to perform, or bugs that might be created, if they are dealing with char instead of varchar. Maybe we should petition Microsoft to remove the char type if it is so useless and dangerous?
– Aaron Bertrand♦
May 2 at 15:23
2
@jpmc26 an app that is writing data doesn't have to trim anything. An app that is displaying data also doesn't have to trim anything. An app that is comparing data shouldn't have to worry about trailing spaces unless it is building literals inside of ad hoc queries. All of those cases are moot anyway if we're talking about character data that uses the entire column width. I repeated it twice already but, once more, this is not my first string pick, it is a use case that may be appropriate sometimes. Stop talking to me like I told everyone they all have to abandon varchar tomorrow.
– Aaron Bertrand♦
May 2 at 20:33
2
2
@jpmc26 -1 all you like. Sometimes char is a valid choice, and sometimes what a developer has to learn how to handle (or that you have to obscure from them) isn't top priority. Anyway if you read closely my answer wasn't "use char" it was mentioned as an aside with a very specific use case as a disclaimer.
– Aaron Bertrand♦
May 2 at 13:39
@jpmc26 -1 all you like. Sometimes char is a valid choice, and sometimes what a developer has to learn how to handle (or that you have to obscure from them) isn't top priority. Anyway if you read closely my answer wasn't "use char" it was mentioned as an aside with a very specific use case as a disclaimer.
– Aaron Bertrand♦
May 2 at 13:39
4
4
@jpmc26 When was the last time you had the freedom to just add RAM to a production server that was wasting memory? I would -1 anywhere where you said the best approach is always to just throw hardware at something.
– Aaron Bertrand♦
May 2 at 13:41
@jpmc26 When was the last time you had the freedom to just add RAM to a production server that was wasting memory? I would -1 anywhere where you said the best approach is always to just throw hardware at something.
– Aaron Bertrand♦
May 2 at 13:41
2
2
@jpmc26 Agree to disagree. Adding hardware might sound like the simpler thing in some cases, but adding memory isn't always possible. It's not always your hardware. There aren't always free RAM slots. Sometimes you're already using the highest capacity chips available. I think in theory that argument works but not always in practice. Just like fragmentation, wasting memory has downstream effects too (think readable secondaries that are less equipped). And just to re-state one more time: I am not advocating char here, just mentioning it as a possibility in very specific cases.
– Aaron Bertrand♦
May 2 at 13:55
@jpmc26 Agree to disagree. Adding hardware might sound like the simpler thing in some cases, but adding memory isn't always possible. It's not always your hardware. There aren't always free RAM slots. Sometimes you're already using the highest capacity chips available. I think in theory that argument works but not always in practice. Just like fragmentation, wasting memory has downstream effects too (think readable secondaries that are less equipped). And just to re-state one more time: I am not advocating char here, just mentioning it as a possibility in very specific cases.
– Aaron Bertrand♦
May 2 at 13:55
2
2
@jpmc26 I also don't believe that changing a varchar column to char(32) because it always or almost always contains all 32 characters is either illogical or requires extra work. Tell me all about the extra work a developer has to perform, or bugs that might be created, if they are dealing with char instead of varchar. Maybe we should petition Microsoft to remove the char type if it is so useless and dangerous?
– Aaron Bertrand♦
May 2 at 15:23
@jpmc26 I also don't believe that changing a varchar column to char(32) because it always or almost always contains all 32 characters is either illogical or requires extra work. Tell me all about the extra work a developer has to perform, or bugs that might be created, if they are dealing with char instead of varchar. Maybe we should petition Microsoft to remove the char type if it is so useless and dangerous?
– Aaron Bertrand♦
May 2 at 15:23
2
2
@jpmc26 an app that is writing data doesn't have to trim anything. An app that is displaying data also doesn't have to trim anything. An app that is comparing data shouldn't have to worry about trailing spaces unless it is building literals inside of ad hoc queries. All of those cases are moot anyway if we're talking about character data that uses the entire column width. I repeated it twice already but, once more, this is not my first string pick, it is a use case that may be appropriate sometimes. Stop talking to me like I told everyone they all have to abandon varchar tomorrow.
– Aaron Bertrand♦
May 2 at 20:33
@jpmc26 an app that is writing data doesn't have to trim anything. An app that is displaying data also doesn't have to trim anything. An app that is comparing data shouldn't have to worry about trailing spaces unless it is building literals inside of ad hoc queries. All of those cases are moot anyway if we're talking about character data that uses the entire column width. I repeated it twice already but, once more, this is not my first string pick, it is a use case that may be appropriate sometimes. Stop talking to me like I told everyone they all have to abandon varchar tomorrow.
– Aaron Bertrand♦
May 2 at 20:33
|
show 7 more comments
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237128%2fusing-column-size-much-larger-than-necessary%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Yes make fields with reasonable size, but that’s more of a problem for 2000 vs. 100 and not 128 vs. 32. Having said that there are naming standards which recommend (for person names) 35+35 or even 100 chars. 128 does not look far off.
– eckes
May 1 at 16:09
3
Might be worth noting that 128 is kind of a special number in SQL Server because that's the max length of certain object names. So if you're storing object names in this table, it would make sense to use exactly 128. Don't ask me how I thought of this >.>
– Jacob H
May 1 at 17:03
@JacobH storing object names is what
sysnameis for.– AakashM
May 2 at 13:55
2
Please ignore the downvote. You've asked a good question, I think someone is just unhappy about the result and is taking it out on everyone.
– Aaron Bertrand♦
May 2 at 15:32
2
@Joe The defined size (and not the actual populated size) dictates memory grants, which can definitely become a performance/concurrency issue.
– Aaron Bertrand♦
May 2 at 15:51