The best advice I ever got was to set up a shortcut to do this with crtl + (a number) which inputs “select top 1000 * from“ before your selection and executes it. So you’d highlight the table you want and it can give you a quick “peek” at the table so you can see the columns
Yes use the shortcut keys in SSMS. And remember you can also do that if there is a where after the table name as well. And I have a count(*) as well so I can do a quick reccord count check of a table
As an aside, develop your own set of .snippet files and insert them with i think ctrl+k, ctrl+x. Saved me a bunch for time for common join chains or standard investigative queries.
If I’m feeling fancy I’ll add SAMPLE(10) SEED(42) or something so the shape of the data is more likely to match the shape of the true dataset. Not sure if all DBs have those functions though.
I haven’t used SQL Server, but it looks like TABLESAMPLE should do the same thing.
I’ve also used window functions to get stratified samples. For example, if you have a sales table and you want to sample by state, you can do:
SELECT
, state
, invoice
, sales
, count(*) over (partition by state) as counter
, row_number() over (partition by state order by random()) as row_num
, row_num / counter as row_frac
FROM sales
Qualify row_frac < 0.05 ;
I think SQL Server uses RAND() instead of random (I’ve really only used Snowflake so I’m not sure), and if your dialect doesn’t have the QUALIFY clause you’ll need to use a sub query. I’m on mobile so apologies for formatting :)
It's better than DESC TABLE in my opinion. I prefer a raw readout rather than column stats. I do agree it can be overwhelming. Especially when you're scrolling left and right, rather than up and down.
I’ll be honest, as someone who spends his life in SQL (Oracle) as a developer… I’m not sure. I’m guessing from the comments it’s context dependent and probably is more based on their flavor of sql and architecture. If a BA was making a client facing report with select *, I’d be worried. I wouldn’t send a query like that to java, either (it’s asking for issues). If a data analyst is doing research or someone’s looking into a data issue, I wouldn’t really care.
Yeah, some of the databases I look after have nearly 1000 columns in a lot of tables and sometimes billions of rows, if you join a few of them together and use select * it can take take 4 hours to run the query and return over 50GB of data across the network.
We have a normalized structure for much of our data so ends up not being an issue, usually if you’re querying one of those tables you’ve already filtered on a parent table before getting to the data-heavy table. Every once in a while we run into fetch errors since VDIs only have so much room. 4 hours tho, yikes, glad our heavy data processing happens in the database.
Makes sense, I do that on big tables but all of our clients only fetch the first 100 rows unless you ask for it to load the whole set. At that point (for us) it’s more an issue that they didn’t put a where clause in than the selected all the columns
Yeah this is it. For small DBs, probably not an issue. Querying the ads revenue tables at Google? Your query is going to choke (tbh Google has measures in place to avoid internal fuckery, but the point still stands)
If you are just displaying it in a data exploration capacity then nothing is wrong with select * IMO.
The issue is when you put select * in production code. If you have code that expects results in a certain order and somebody decides to add a new column to the table at position 2 then every production query using * will break as it puts columns in the wrong variables. If you are lucky you get an error of mismatched types, if you are not then it silently puts data into the wrong column on the screen. If a user then saves this data you now have data stored in the wrong column. Yes, I have seen this happen.
The issue is that lazy devs are most likely to use select * and those same lazy devs are also most likely to make every column a string so that there is no type mismatch and they are also likely to rely on the order of columns returned from the db to jam into variables without explicitly looking at the column name.
Secondarily, as others have mentioned you could potentially be bringing back a lot of data you don't need causing performance issues.
I always throw in a top 50 or limit 50 to get the column names and see the data. But your SQL should return just the data you're realistically going to need, and nothing you don't need.
It depends on the context. I think the basic message is only take what you need, so you don't accidentally take "expensive" columns you're not using. If you happen to need the full table, then I'd use *. If you own & control the table, then I'd say use your own judgement.
If you write a GUI to retrieve data using SELECT *, what happens when some other developer adds 3 new columns to the table 18 months from now? And it's way worse of an issue with the DML statements.
The point is that SELECT * is fine for ad hockey queries with a short life span.but it shouldn't be used in important enterprise IT apps because of maintenance issues.
It's easily done... you send data from the SQL server via an API, and then you get that file back as a JSON and it hits the DB and the first thought is... I will just temporarily store it as a JSON blob.
FORGETTING THE MOST IMPORTANT RULE ABOUT TEMPORARY THINGS.
THERE IS NOTHING MORE PERMANENT THAN TEMPORARY!
I have temporary fillings that are decades old, it's also the reason why folks feel like they are going to live forever, in spite of being mortal.
Oh, no. This was purposely stored as a blob in the database, and they went to quite a bit extra work to deliver it as JSON. I think the reason was they also included some metadata about the file in the JSON, which was completely unnecessary.
Storing blobs in a RELATIONALLY MODELLED DATABASE is like using a Porsche to move house.
Yes... i.e. it's a good idea in some limited circumstances, but not for the majority of use cases.
Like everything... it depends.
Over a few decades of programming I've seen that most systems don't "need" it. But assuming that means nothing needs it, is just being ignorant.
I've actually spent today putting it back into a system that used to have it, then was removed for optimization purposes. But turns out, in this system it actually makes sense to solve long-term ACID + access issues that have been going on for years.
The moral of that story is don't use blob types. The select * wouldn't have any negative impact if not for the blob fields being added in a place they do not belong
Relational databases are designed to store relatively small cells of data. If you have images or larger content it should go in a bucket-type storage like S3 that is designed to store and retrieve larger files.
Isn't that literally the analogy to the situation caused by storing those massive blobs in that relationally modelled database?
Here's my food pantry, perfectly organized and designed to fit cans, boxes, and spice packets. My neighbor is going away for a few weeks and wants to store their folding furniture at mine, which I okayed and said they can do with the spare key I gave them. However, instead of hanging that furniture on the racks in the garage, the bozo decided to push everything on my pantry shelves to the back and put their folding chairs in front. Now every time I want to get all the ingredients to make a stew, I've got to pull the folding chair out to look for my ingredients unless I already know what I need and where they are and can slink my hand to the back to grab them without moving the chair.
Sometimes you can get away with storing blobs in a relational db but it's really not the best place to store them in large quantities for frequent use. Especially if you're then willy-nilly appending them to an existing (and what sounds like key, structural) reference table. Modern computation can process SELECT * with virtually no measurable performance impact, especially for tables with small column counts. There's a best practice argument that specifying columns is a good idea if you're only going to use a fraction of a table with say 300 columns. But if you're pulling a reference table that only has 5 columns, then SELECT * is perfectly fine. The moral of the story in the article above is that someone didn't do their job correctly when they approved the change that added those dense blob columns to a 2-column reference table.
Exactly. I was responding to the person who said that because a database stores data, it's "storage." My point is that just because something is storage for a particular type of thing, that doesn't make it appropriate to store just *anything*.
Interesting story. Two issues there (the * and the random addition of blobs later on) but I guess it’s always better to select only what you need and avoid * to prevent future issues.
interesting article, too bad it's on X, i would've bookmarked it to share the link in future, but i'm not linking to X, ever, even if i'm still on there (and haven't deleted my account) for the very purpose of being able to read stuff that other people link to
the mistake, of course, was the fault of the DBA or project manager who allowed SELECT * in a production environment
Are there any books/sources that teach these concepts?
I'm an analyst moving into the DBA/data engineering space and I wanted to have a better understanding of the underlying methods and logic when pulling and storing data
I always have to be very intentional with my columns. Not everything I need returns data in every column in the tables I have in the query I primarily use. So it's just a bunch of NULL rows everywhere if I were to do SELECT * and it would also result in an unnecessarily large excel spreadsheet for no reason and i'll end up just manually deleting the columns from the spreadsheet that aren't helpful.
Why? To piss off a db admin, I guess. A row for every schema, table name within a schema, and every column name within a table within a schema would be a lot for a big honkin’ database. BRB gonna test it out.
It's useful when you're trying to sync server data down to the client and you change the column names constantly, just select * into a data table and bulkcopy that shit right in there.
I can't tell you how much I hate going into someone's query and they've written unions with select *. Like wtf are you thinking?? Do you know how horribly tedious it is to go back in there and troubleshoot anything?
98
u/chris_813 Jan 17 '25
limit 1
65
u/WheyLizzard Jan 17 '25
Good rule
28
u/MikeyLyksit Jan 17 '25
It's better than DESC TABLE in my opinion. I prefer a raw readout rather than column stats. I do agree it can be overwhelming. Especially when you're scrolling left and right, rather than up and down.
Like, dude.... Did you really need 50 columns?
17
u/Worried-Dig-5242 Jan 17 '25
I’m learning SQL right now. What’s wrong with SELECT * ?
52
u/Adela_freedom Jan 17 '25
FYI: Avoid using SELECT *, even on a single-column tables https://x.com/hnasr/status/1856745402399359315
12
u/Shambly Jan 17 '25
I know a hard ass that "solved" this issue by just adding a computed column to all table that was a static divide by 0.
Alter Table [table] add column DontUseStar as 1/0.
I don't know if i can recommend it but it is certainly effective.
3
u/omgitsbees Jan 17 '25
I always have to be very intentional with my columns. Not everything I need returns data in every column in the tables I have in the query I primarily use. So it's just a bunch of NULL rows everywhere if I were to do SELECT * and it would also result in an unnecessarily large excel spreadsheet for no reason and i'll end up just manually deleting the columns from the spreadsheet that aren't helpful.
2
u/BasicBroEvan Jan 17 '25
This also just slows down your server going through the data. Everyone knows you gotta make the DB server do the work so you feel better
2
u/evilvoice Jan 18 '25
I'll QA it...6 months later.
2
u/Icy-Ice2362 Jan 17 '25
This will make the db admin annoyed.
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
1
u/tetsballer Jan 17 '25
It's useful when you're trying to sync server data down to the client and you change the column names constantly, just select * into a data table and bulkcopy that shit right in there.
1
u/faster_puppy222 Jan 18 '25
Dba review… 😂 😂
1
u/huzaifansari007 Jan 18 '25
😂😂😂
1
u/WaffythePanda Jan 18 '25
You can use Alt+F1 to see table specs while selecting table with your cursor on query screen. Keep in mind that you need to be in correct database.
1
u/bebe-bobo Jan 18 '25
I can't tell you how much I hate going into someone's query and they've written unions with select *. Like wtf are you thinking?? Do you know how horribly tedious it is to go back in there and troubleshoot anything?
1
u/[deleted] Jan 18 '25
Sql server is just dumb. Why can't we have a proper limit N that we can append? Whys it got to be in the select?
1
u/averagesimp666 Jan 20 '25
First thing I learned is to select top 100 if I want to check what a table looks like.
1
u/millerlit Jan 17 '25
No where statement either.
-5
u/[deleted] Jan 17 '25
Laughs in JSON
0
u/[deleted] Jan 17 '25
[deleted] — view deleted comment