Using column size much larger than necessaryComputed column with less size than the “referring column”SQL...
Purpose of のは in this sentence?
Can hackers enable the camera after the user disabled it?
How do LIGO and VIRGO know that a gravitational wave has its origin in a neutron star or a black hole?
Manager is threatening to grade me poorly if I don't complete the project
Why isn't nylon as strong as kevlar?
Does a card have a keyword if it has the same effect as said keyword?
Would Hubble Space Telescope improve black hole image observed by EHT if it joined array of telesopes?
How can I support myself financially as a 17 year old with a loan?
Upside-Down Pyramid Addition...REVERSED!
How do I overfit?
Why doesn't WotC use established keywords on all new cards?
How to model the curly cable part of the phone
What is the most remote airport from the center of the city it supposedly serves?
Why was the battle set up *outside* Winterfell?
How can I close a gap between my fence and my neighbor's that's on his side of the property line?
How did Shepard's and Grissom's speeds compare with orbital velocity?
What are the differences between credential stuffing and password spraying?
Should I replace my bicycle tires if they have not been inflated in multiple years
What are the advantages of luxury car brands like Acura/Lexus over their sibling non-luxury brands Honda/Toyota?
Building a list of products from the elements in another list
If your medical expenses exceed your income does the IRS pay you?
I drew a randomly colored grid of points with tikz, how do I force it to remember the first grid from then on?
Multi-channel audio upsampling interpolation
Expressing 'our' for objects belonging to our apartment
Using column size much larger than necessary
Computed column with less size than the “referring column”SQL Server Database Size much higher than the backup size after restore“Variant” values structure and their use for multiple columnsTransaction log backup file larger than expected?Why the backup file size is much larger than the restored databaseInteger 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 columnTransaction Log Files Larger Than Data Files
.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 units 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
edited 2 hours ago
MDCCL
6,89331845
asked 12 hours ago
elbillafelbillaf
1434
New contributor
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a 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 units 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
edited 2 hours ago
MDCCL
6,89331845
asked 12 hours ago
elbillafelbillaf
1434
New contributor
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
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
10 hours ago
1
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
9 hours ago
add a 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 units 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
edited 2 hours ago
MDCCL
6,89331845
asked 12 hours ago
elbillafelbillaf
1434
New contributor
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 units 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
edited 2 hours ago
MDCCL
6,89331845
asked 12 hours ago
elbillafelbillaf
1434
New contributor
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 hours ago
MDCCL
6,89331845
asked 12 hours ago
elbillafelbillaf
1434
New contributor
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 hours ago
MDCCL
6,89331845
edited 2 hours ago
MDCCL
6,89331845
edited 2 hours ago
MDCCL
6,89331845
6,89331845
asked 12 hours ago
elbillafelbillaf
1434
New contributor
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 12 hours ago
elbillafelbillaf
1434
asked 12 hours ago
elbillafelbillaf
1434
1434
New contributor
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
elbillaf is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
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
10 hours ago
1
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
9 hours ago
add a comment |
2
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
10 hours ago
1
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
9 hours ago
2
2
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
10 hours ago
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
10 hours ago
1
1
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
9 hours ago
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
9 hours ago
add a 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 12 hours ago
Max VernonMax Vernon
53k13115233
add a 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 12 hours ago
Aaron Bertrand♦Aaron Bertrand
155k18301498
add a comment |
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
});
}
});
elbillaf is a new contributor. Be nice, and check out our Code of Conduct.
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 12 hours ago
Max VernonMax Vernon
53k13115233
add a 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 12 hours ago
Max VernonMax Vernon
53k13115233
add a 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 12 hours ago
Max VernonMax Vernon
53k13115233
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 12 hours ago
Max VernonMax Vernon
53k13115233
answered 12 hours ago
Max VernonMax Vernon
53k13115233
answered 12 hours ago
Max VernonMax Vernon
53k13115233
answered 12 hours ago
Max VernonMax Vernon
53k13115233
53k13115233
add a comment |
add a 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 12 hours ago
Aaron Bertrand♦Aaron Bertrand
155k18301498
add a 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 12 hours ago
Aaron Bertrand♦Aaron Bertrand
155k18301498
add a 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 12 hours ago
Aaron Bertrand♦Aaron Bertrand
155k18301498
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 12 hours ago
Aaron Bertrand♦Aaron Bertrand
155k18301498
answered 12 hours ago
Aaron Bertrand♦Aaron Bertrand
155k18301498
answered 12 hours ago
Aaron Bertrand♦Aaron Bertrand
155k18301498
answered 12 hours ago
Aaron Bertrand♦Aaron Bertrand
155k18301498
155k18301498
add a comment |
add a comment |
elbillaf is a new contributor. Be nice, and check out our Code of Conduct.
elbillaf is a new contributor. Be nice, and check out our Code of Conduct.
elbillaf is a new contributor. Be nice, and check out our Code of Conduct.
elbillaf is a new contributor. Be nice, and check out our Code of Conduct.
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
2
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
10 hours ago
1
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
9 hours ago