14
votes
Accepted
A wounded Zombie finder, or: how to find the almost dead undead?
Not utterly wrong, but
q.PostTypeId = 1
and
AND a.PostTypeId = 2
are superfluous. The only post types with a non-null ...
- 1,113
10
votes
Accepted
SQL trigger to log when employee records are updated
First some comments.
Usage of Deprecated Features
Currently your trigger does a SELECT that returns a resultset from a trigger. That was at one time supported but ...
- 2,039
9
votes
Accepted
Build WHERE clause for search conditions
Sanitizer is a dangerous misnommer IMO. A better name could be NameQuoter, since that's what it does: it uses the RDBMS-specific ...
- 75.6k
7
votes
SQL trigger to log when employee records are updated
You are basically reformatting one group of fields into another. I'm not sure if that's a good idea - you might want to just make a straight-up copy of the fields, since that improves your "audit" ...
- 12.6k
5
votes
Accepted
The sniper: a Zombie searcher for specific tags
When using "simple" Aliases for table names in a query you should alias all the tables, not just some of them. This makes the query appear more consistent. In your query, you have ...
- 98.1k
5
votes
Accepted
SQL query to count loans issued during each week
Your query could be simplified.
use inner join to avoid ambiguity when reading the query
temporal interval join date between start and end (inclusive end)
...
- 14.2k
5
votes
Build WHERE clause for search conditions
Just one thing...
Use db-provider's sanitizer
There is already a sanitizer that the DbCommandBuilder provides. You can use it like this:
...
- 44.7k
5
votes
Safe dynamic SQL for generic search
Comments on your approach
The most obvious thing to me is that you're already inserting all of your data into the temp table; why not just join to it? You can dynamically pivot into a better, ...
- 8,049
5
votes
Accepted
Better Microsoft SQL update
style improvements
The revised code introduces a few improvements,
beyond adding another parameter.
One table column per line -- good! It helps to minimize future git diffs due to maintenance edits.
...
- 42.1k
4
votes
"How can I make this SEDE query better?" (bad title finder)
Let me address a possible DRY issue in the query in exchange for possible poorer performance.
I'm in particular talking about this bit:
...
- 295
4
votes
Data Explorer query that makes bar graphs
I'll answer your main questions:
Can I refactor it so that the list of when like then statements occurs only once? (Preferably without complicating the code too much.)
Yes, but not at the expense of ...
- 295
4
votes
Accepted
Creating SQL Indexes on Large Tables
Community wiki because the root of the answer (use an MLT to copy the data) was already in the comments. Shoutout to dnoeth and Der Kommissar for already explaining most of this in the comments.
...
4
votes
SQL trigger to log when employee records are updated
More Comments
While the others have offered great feedback and suggestions, I also noticed a couple things with the current code. The derived table syntax suggested by Austin Hastings will (and ...
- 29.6k
4
votes
Find how much reputation a user had on a given date
This is a partial answer as these were the bits I could verify easily.
In your column query for ReputationFromSuggestedEdits you can move all of the projection to ...
- 295
4
votes
Accepted
Inserting data into SQL-Server table dynamically
The main problem with this code is that it's not screening column values, unfortuantely buit in mechanisms in Invoke-SQLCMD aren't viable. It allows to use ...
- 359
4
votes
Function to get current value or default from a string of two values
Potential Issues
a naive string replacement could change more than just the placeholders; for instance, when another part of the script uses the same string as a literal
the replacement can introduce ...
- 14.2k
4
votes
Accepted
Function to get current value or default from a string of two values
Answering your question
Overall, I think the solution is sound, but there are a few things you could make cleaner. I agree with everything in dfhwze's answer, but assuming this is the route you keep ...
- 8,049
3
votes
Accepted
Small DSL: Converting JSON to MSSQL query
Escaping and SQL injections
One of the major problems in the presented code is that the code is vulnerable to SQL injection attacks and does not properly sanitize, validate and escape the field values....
- 17.5k
3
votes
Accepted
3
votes
Accepted
Stored procedure to insert a new person
Per billinkc in the comments, rollback before throw would do what you intend.
Per Michael Green, avoid using ...
- 150
3
votes
T-SQL Hmac (Rfc2104 SHA2_256) Implementation
Looks OK to me, without knowing Transact SQL anyway. Some things to consider:
obviously this implements just HMAC-SHA256, so the function should be named that way;
the key parameters is 8000 ...
- 6,739
3
votes
Accepted
Stored procedure to describe the reason that a particular employee is unsuitable for a particular task
A handful of critiques:
As mentioned in the comments, this is undefined behavior: SELECT @val = @val + ColumnName FROM AnyTable. As such, you should avoid it. If ...
- 8,049
3
votes
Displaying a single record based on DocID
Starting with SQL 2008 there is a window function that does this nicely.
900,000 records even with lack of index that seems like it should be faster.
I doubt moving the condition into the join will ...
- 6,136
3
votes
Accepted
SQL Server - Iterate, aggregate, and insert
There's no need for a cursor or recursion, both are not really performant for larger amounts of data.
Simply adjust the CaptureTime to the start of each interval.
...
- 1,222
3
votes
Accepted
Retrieving a product's sold quantity
I would test using the OVER clause with aggregate functions. This may speed up your results and make it a bit more readable. You may also want to check out the ranking functions.
Here is an example ...
- 478
3
votes
SQL SP creating certificates for shipping
The following are a few suggestions on how I'd write the stored procedure.
Source Control
If you don't already have a database project, create one in Visual Studio. Then check it in to source ...
- 478
3
votes
Query with multiple foreign keys to the same table
I've updated the Fiddle with your initial query and 2 alternatives.
This is the initial query plan:
Using OUTER APPLY instead of the nested ...
- 14.2k
3
votes
Query with multiple foreign keys to the same table
However, I'm concerned that using subqueries like this may be a bit of a hack and affect performance (especially if I end up adding additional columns that need this information).
Whenever you find ...
- 8,049
3
votes
all appointment based on interval distance
You can greatly simplify things as well as wildly improve performance by using something like the following...
...
- 206
Only top scored, non community-wiki answers of a minimum length are eligible