SQL: Star vs All?
-
Re: SQL: sequel or ess-kew-ehl?
I'll see your sequel vs ess-kew-ehl, and raise you this:Do you say "Select star from table" or "Select all from table"?
-
@AgentDenton "Select Stern from Tabelle". Which sounds especially retarded.
-
-
select shift+8from table
-
@AgentDenton Neither.
Select Asterisk.
-
And then you get some instances where people select Asterix
-
@Zecc said in SQL: Star vs All?:
Select Asterisk.
Let someone else do that for you, if at all possible.
Filed under: The scars, they will never heal
-
SELECT field, field, field, field FROM places...
Sure, you might save a few bytes of inter-process (or network) bandwidth in the sending, and you might get a few more hits in the query cache but IME this means you save a lot of memory coming back with results and you get it faster too since MySQL (yes I'm stuck with that) has to move less data around.
-
@ChrisH said in SQL: Star vs All?:
Select Stern from Tabelle
-
@AgentDenton said in SQL: Star vs All?:
select Asterix
-
@Arantor the only time I ever use
select *
in a script destined for production, it's a situation like:WITH CTE AS ( --select some stuff ) SELECT * FROM CTE
which can usually be replaced with just the
select some stuff
part, but not always
-
@Jaloopa CTEs aren't a thing in MySQL last I checked and even if they are, they're not in the versions I get to use.
-
@Luhmann said in SQL: Star vs All?:
DERAIL!
What the hell is up with
1up
anyway? Whoever called player 1 "1up", ever?
-
@Onyx I saw this on videogames and never had any idea of why
-
@AgentDenton Everyone I have everyheard has said "select * from", I say it internally when I am writing queries.
-
@ChrisH said in SQL: Star vs All?:
@AgentDenton "Select Stern from Tabelle". Which sounds especially retarded.
Sure does, but that hasn't managed to stop me, either.
-
@AgentDenton
Obviously, you write your queries properly, so that you only select the columns you need.
-
@izzion said in SQL: Star vs All?:
@AgentDenton
Obviously, you write your queries properly, so that you only select the columns you need.this. exactly this.
select * is fine if you're in REPL spelunking through the database for answers, but if i ever find a developer of mine using it in production code Mr Half Brick will be vigorously introduced to their head.
-
@Onyx said in SQL: Star vs All?:
1up
Since it's a reference to an arcade game that swallowed my coins ...
It meant "insert coin"
-
@Luhmann well, that kinda makes sense... Though I saw that shit outside of arcade games IIRC.
Also, I mostly played the SNES one. I remember it being hard, but that just might've been me sucking.
-
-
select chocolate starfish from ...
-
-
@lordofduct said in SQL: Star vs All?:
select chocolate starfish from ...
https://www.youtube.com/watch?v=Vdd4rBlsj2o
Filed under: squirrel
-
@accalia Does it really matter? The database will pull back those columns anyway, unless it is involving joins. I think to be explicit on what you want to pull back is a good idea, but performance wise it doesn't matter I don't think.
-
@AgentDenton If it's production code, I always list the columns I want.
-
@lucas1 where it starts to matter is 6 months down the line when someone adds a column, or re orders them, and your query is suddenly not doing what you wanted.
-
@lucas1 said in SQL: Star vs All?:
but performance wise it doesn't matter I don't think.
and you would be wrong there.
you want to write your query to have the minimum information required to do your job without needing subsequent queries, because when you put that into production and the database server is on a separate machine from the application, all of a sudden you have the problem of network latency and bandwidth to deal with. losses for excessively large data sets start adding up fast.
-
@Luhmann I prefer EXTEND, as seen in Japanese shooting games.
-
@aliceif
I didn't care ... it was one less coin ...
-
@Luhmann said in SQL: Star vs All?:
@ChrisH said in SQL: Star vs All?:
sounds especially retarded
Duh, it's German!
NO, THAT'S NOT German. GERMAN SOUNDS LIKE this.
-
@Rhywden But German in an English Accent sounds really gay.
-
@Jaloopa said in SQL: Star vs All?:
@lucas1 where it starts to matter is 6 months down the line when someone adds a column, or re orders them, and your query is suddenly not doing what you wanted.
Or someone adds a
varbinary(max)
column to one of the tables.
-
@lucas1 said in SQL: Star vs All?:
@Rhywden But German in an English Accent sounds really gay.
Your mom is gay.
-
@Rhywden I wouldn't exist if she was.
-
@Luhmann said in SQL: Star vs All?:
have you tried one of those blue pills?
Actually, I've been thinking it ever since I got here: Why oh why didn't I take the blue pill?
-
-
@Arantor said in SQL: Star vs All?:
SELECT field, field, field, field FROM places...
Sure, you might save a few bytes of inter-process (or network) bandwidth in the sending, and you might get a few more hits in the query cache but IME this means you save a lot of memory coming back with results and you get it faster too since MySQL (yes I'm stuck with that) has to move less data around.
Also, the fields you get back don't change after you write the query.
Edit: 'd by @Jaloopa
-
@Luhmann said in SQL: Star vs All?:
@Onyx said in SQL: Star vs All?:
1up
It meant "insert coin"
This is completely and totally wrong. 1-up can refer to (1) an extra life (2) doing just better than your peers or I guess (3) "re-upping"/refreshing your play time limit for an arcade game, but in the given context (Player 1 making a selection), it means neither of them.
It simply means that Player 1's turn to select has come up. "
Player1isup."
-
@Luhmann said in SQL: Star vs All?:
@ChrisH said in SQL: Star vs All?:
sounds especially retarded
Duh, it's German!
German doesn't sound retarded, but everything said in that language sounds irrationally angry.
-
@Polygeekery said in SQL: Star vs All?:
@Luhmann said in SQL: Star vs All?:
@ChrisH said in SQL: Star vs All?:
sounds especially retarded
Duh, it's German!
German doesn't sound retarded, but everything said in that language sounds irrationally angry.
You can SPEAK ENGLISH with similar INFLECTION PATTERNS and it sounds JUST AS ANGRY.
-
@Polygeekery said in SQL: Star vs All?:
German doesn't sound retarded, but everything said in that language sounds irrationally angry.
A friend of mine told me about a time in high school when there was a German exchange student at the school. He said he was dating her for a while, and she was really good-looking, but he ended up breaking up with her because she kept trying to say romantic things at him in German, and no matter how much she may have been trying to sweet-talk him, it invariably ended up all sounding harsh and angry and being a major turnoff for him.
-
@Groaner said in SQL: Star vs All?:
@Jaloopa said in SQL: Star vs All?:
@lucas1 where it starts to matter is 6 months down the line when someone adds a column, or re orders them, and your query is suddenly not doing what you wanted.
Or someone adds a
varbinary(max)
column to one of the tables.Or it's not a table, but a view, and joins lot of tables (or worse, executes UDFs) to deliver columns you don't even need in the first place.
-
@ChrisH said in SQL: Star vs All?:
Or it's not a table, but a view, and joins lot of tables (or worse,
executes UDFsother views, which call other views, down to 12 levels of nesting because the guy who does the SQL hates SQL and tries to write it like C#) to deliver columns you don't even need in the first place.I really don't miss that job. The views tended to use
select *
as well
-
One of my favourite "features" of SQL Server:
Running the following:
create table tbl (Col1 int) go insert into tbl(Col1) values(1) go create view vw as select * from tbl go select * from vw go alter table tbl add Col2 int go insert into tbl(Col1, Col2) values(2, 3) go select * from vw go drop table tbl go drop view vw go
will not return Col2 for the second "select * from vw". That's because the view's metadata only gets updated once the view gets recompiled. Try finding that bug when someone writes a "select * from table" view.
-
@Vault_Dweller if you insist on using
SELECT *
for views, you should includeWITH SCHEMABINDING
so you get an error when trying to modify the base table.
-
@Jaloopa WITH SCHEMABINDING only works if you state the columns explicitly.
select *
will give you
Syntax '*' is not allowed in schema-bound objects.
Edit: I probably misunderstood you. Still, you should always use schemabinding for views.
-
@ChrisH I didn't realise that. Makes a lot of sense though.
So, I modify my statement to:
You should always use schemabinding, because it forces you to deal with any views that will be broken by schema changes and stops you using
select *
instead ofselect [all columns]
-
You shouldn't be typing SQL at all. Use ORM or an SQL generator.
If you actually do need to type SQL, it should be for some very specialized procedure call or a report query. Not for a "just dump a bunch db rows into RAM" kind of thing.
-
It occurs to me now that I probably should have been more specific
This wasn't about writing SQL so much as how you verbally portray the * in SQL statements (like the linked topic was about sequel vs ess-kew-ehl)
When writing code, yes, absolutely, we shouldn't be using *. But we've all had to do some troubleshooting in data, and then there's no harm in writing a quick few select *'s to look at data.
My bad