81–90 of 222 posts
At this point it's malpractice not to use AI to analyze your SQL statements and tables for optimizations
Are we on bizarro HN? No, you ask the DB to EXPLAIN itself to you.
Earlier quoted context omitted.
> Also, understand how your DB handles nulls. Also in regards to indexing. The DBs I've used have not indexed nulls, so a "WHERE col IS NULL" is inefficient even though "col" is indexed. If that is the case and you really need it, have a computed column with a char(1) or bit indicating if "col" is NULL or not, and index that.
NULL should generally never be used to "mean" anything. If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.
I guess you would handle it in the application and not in the query, right?
* Don't store UUIDs as strings.
* Don't use random UUID variants for your primary key (or don't use UUIDs for your primary key).
* Don't use a random column in your clustered index.
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
Set theory... There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
Set theory... There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.
Earlier quoted context omitted.
>subquery >less verbose Well… In any case, it depends. OP nicely guarded himself by writing “overusing”, so at that point his pro-tip is just a tautology and we are in agreement: not every use of DISTINCT is an immediate smell.
What do you mean? Here are your real alternatives for doing a semijoin (assuming ANSI SQL, no vendor extensions): SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE t2.x = t1.x ); SELECT * FROM t1 WHERE x IN ( SELECT x FROM t2 ); SELECT * FROM t1 JOIN ( SELECT DISTINCT x FROM t2 ) s1 USING (x); Now tell me which one of these is the less verbose semijoin? You could argue that you could fake a semijoin using SELECT…
Earlier quoted context omitted.
What do you mean? Here are your real alternatives for doing a semijoin (assuming ANSI SQL, no vendor extensions): SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE t2.x = t1.x ); SELECT * FROM t1 WHERE x IN ( SELECT x FROM t2 ); SELECT * FROM t1 JOIN ( SELECT DISTINCT x FROM t2 ) s1 USING (x); Now tell me which one of these is the less verbose semijoin? You could argue that you could fake a semijoin using SELECT…
No, sorry, you’re certainly correct, I just meant that any subqueries are generally crazy verbose. And then you usually want additional Where clauses or even Joins in there, and it starts to stop looking like a Where clause, so I’m often happy when I can push that logic into From.
SELECT * FROM t1 SEMIJOIN t2 USING (x);
although it creates some extra problems for the join optimizer.
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
The section of using functions on indexes could do with more explicit and deeper explanation. When you use the function on the index it becomes a full scan of the data instead as the query runner has to run the function on every row and column, effectively removing any benefit of the index. Unfortunately I learned this the hard way!
The given solution (create an indexed UPPER(name) column) is not the best way to solve this, at least not on MS SQL Server. Not sure if this is equally supported in other databases, but the better solution is to create a case-insensitive computed column: ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS; (season to taste)
For example, you define an index on UPPER(name_column), and in your query you can use WHERE UPPER(name_to_search_for) = UPPER(name_column), and it will use the index.
Earlier quoted context omitted.
`select *` is bad for many reasons, but the biggest is that the "contract" your code has with the remote data store isn't immutable. The database can change, for many different reasons, independent of your code. If you want to write reliable code, you need to make as few assumptions as possible. One of those assumptions is what the remote schema is.
Sure but columns can change data types too which 'select column's doesn't protect you from either
This mirrors how adding additional fields to an object type in a programming language usually isn’t considered a breaking change, but changing the type of an existing field is.