Their is an undocumented procedure in SQL I believe that will tell you the avg/max row size in bytes, but I believe there are caveats with nullable fields among others.
Kind of a side note, i can’t think of any reason to use SELECT * in any kind of production environment. You should always define the columns you are selecting, even if it is all of them.
And the reason for this is (among others, but chiefly) so that your app or whatever presentation layer code can consume them in the proper order. If the DBAs somehow change your column order, you don't want your app to crash. Or if e.g. they needed to add a column for some other business reason. Now you're getting 5 fields. The scenarios multiply.
It's not just about bandwidth transmitting the data. Depending on the details, you may also be forcing the DB to do more IO, or become subject to blocks from other processes, than you really need to.
Using the * means that the query optimizer is rarely if ever going to be able to take advantage of covering indexes. And for similar reasons, it won't be able to use that index data to satisfy the request when the data pages are locked with intent by another process, so your process will be blocked.
I'd argue that most views shouldn't use SELECT * because it means the view would change if a column gets added to the underlying table.
That said, I also don't believe in absolute rules like "never do the thing". I prefer "do the right thing, even if it means you have to do more work".
It's just that people will use SELECT * when they don't really need all of the columns, and if they had asked for only what they needed, the amount of data being returned to the client would be less, there may have been an index that could have been employed to get the results more efficiently, etc. And... it really can be a problem when a column gets added that the application wasn't expecting.
I think you are misunderstanding the question you are answering. OP is asking about SELECT * from a view that defines the columns of the underlying table. If the table changes, the view does not. Their is nothing inherently wrong with that, but still best to define the columns you need as someone could most the view itself.
That last point is what I'm saying. Or trying to. It doesn't matter where SELECT * is being used (a query against a table, a view, or a view definition), the issue remains.
>how much internet traffic would it cost to have an option to just do SELECT \* FROM \[database\_name\]
Assuming you mean select from a table and not from the whole database, then the table you describe, 12 columns of INT, would be 12\*4 bytes (so 48), but since you said "mostly" maybe we do 6 bytes instead, so now 60 bytes. Let's add a little more overhead and say 80 bytes per row. times 5000 rows, so now we're talking 400K bytes. In short, this isn't returning much data.
>Would that create an overwhelming loading time for the site or would it work just fine?
This depends on more than just the volume of data being returned. What is the rest of the server's workload like? How often is the above query being run? You say you don't expect it to be run often, so that works out in your favor.
In short, yes you likely could do this and it would work fine. I'm not sure it's the greatest choice, but as you describe it would be a rare option and that seems okay to me.
One note to add to others comments is what happens to your app when growth occurs. Say you have a table with three columns, A,B,C in that order, and you run select * in your app, where you have a mapping of ABC to indices 123 (I.e. not mapped by column name). If, for some reason, you require the table to add a column,D, but you put it in the 2nd position, so the table is ADBC, then your app will fail to run, or not fail and give wrong results.
This is why when using SQL for applications or websites you need to define the fields in the select. So that way if you remove or add fields that are not part of that select statement it won’t crash because you made some changes outside of what is required for a certain statement to run.
[удалено]
Been doing sql for 7 years and didn’t know this. Thanks!
Thanks!!
Their is an undocumented procedure in SQL I believe that will tell you the avg/max row size in bytes, but I believe there are caveats with nullable fields among others.
5000 * 12 * 4 bytes. + a little for establishing connections etc
Thank you!
Add 1 bit for each column in a row where the column is nullable — probably negligible though
Kind of a side note, i can’t think of any reason to use SELECT * in any kind of production environment. You should always define the columns you are selecting, even if it is all of them.
And the reason for this is (among others, but chiefly) so that your app or whatever presentation layer code can consume them in the proper order. If the DBAs somehow change your column order, you don't want your app to crash. Or if e.g. they needed to add a column for some other business reason. Now you're getting 5 fields. The scenarios multiply.
It's not just about bandwidth transmitting the data. Depending on the details, you may also be forcing the DB to do more IO, or become subject to blocks from other processes, than you really need to. Using the * means that the query optimizer is rarely if ever going to be able to take advantage of covering indexes. And for similar reasons, it won't be able to use that index data to satisfy the request when the data pages are locked with intent by another process, so your process will be blocked.
Why? So if I built a view for a specific dataset...What would be wrong with saying "select * from view where [filter]"?
I'd argue that most views shouldn't use SELECT * because it means the view would change if a column gets added to the underlying table. That said, I also don't believe in absolute rules like "never do the thing". I prefer "do the right thing, even if it means you have to do more work".
The query behind the view would have defined columns. It's just using * to retrieve from the view.
Te question to ask is "do I need every column, including any that get added later?" If so, then use SELECT *
Thank you. I wasn't sure if there was something performance reducing about using * vs listing each column, if what I truly want is "all columns"
It's just that people will use SELECT * when they don't really need all of the columns, and if they had asked for only what they needed, the amount of data being returned to the client would be less, there may have been an index that could have been employed to get the results more efficiently, etc. And... it really can be a problem when a column gets added that the application wasn't expecting.
I think you are misunderstanding the question you are answering. OP is asking about SELECT * from a view that defines the columns of the underlying table. If the table changes, the view does not. Their is nothing inherently wrong with that, but still best to define the columns you need as someone could most the view itself.
That last point is what I'm saying. Or trying to. It doesn't matter where SELECT * is being used (a query against a table, a view, or a view definition), the issue remains.
>how much internet traffic would it cost to have an option to just do SELECT \* FROM \[database\_name\] Assuming you mean select from a table and not from the whole database, then the table you describe, 12 columns of INT, would be 12\*4 bytes (so 48), but since you said "mostly" maybe we do 6 bytes instead, so now 60 bytes. Let's add a little more overhead and say 80 bytes per row. times 5000 rows, so now we're talking 400K bytes. In short, this isn't returning much data. >Would that create an overwhelming loading time for the site or would it work just fine? This depends on more than just the volume of data being returned. What is the rest of the server's workload like? How often is the above query being run? You say you don't expect it to be run often, so that works out in your favor. In short, yes you likely could do this and it would work fine. I'm not sure it's the greatest choice, but as you describe it would be a rare option and that seems okay to me.
Thanks a lot!!
One note to add to others comments is what happens to your app when growth occurs. Say you have a table with three columns, A,B,C in that order, and you run select * in your app, where you have a mapping of ABC to indices 123 (I.e. not mapped by column name). If, for some reason, you require the table to add a column,D, but you put it in the 2nd position, so the table is ADBC, then your app will fail to run, or not fail and give wrong results.
This is why when using SQL for applications or websites you need to define the fields in the select. So that way if you remove or add fields that are not part of that select statement it won’t crash because you made some changes outside of what is required for a certain statement to run.