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 ...
Glorfindel's user avatar
  • 1,089
13 votes
Accepted

Stack Exchange User Activity Score

TOP 1 When selecting values that correspond to Unique fields referenced by constants (OwnerUserId), you're only really ...
Quill's user avatar
  • 11.9k
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 ...
this's user avatar
  • 2,029
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 ...
Mathieu Guindon's user avatar
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" ...
aghast's user avatar
  • 12.4k
6 votes

Stack Exchange User Activity Score

This is too much for a comment, adding to @Quill's answer: In your Select you use FROM Users, Comments, Posts which is an unconstraint cross join, i.e. every row ...
dnoeth's user avatar
  • 1,172
6 votes
Accepted

Coin flips and Dice rolls in a stored procedure

Overall, I think your procedure is very good. It's consistent in layout, capitalisation and style, and well commented. I had no problems reading through it and understanding what it was doing (and how)...
Stuart J Cuthbertson's user avatar
6 votes

T-SQL UDF to replace replace base URLs

Your code looping over string functions doesn't seem like something SQL Server is optimized for. Database systems are optimized for set based operations, not looping. I understand the confusion ...
Tom V's user avatar
  • 161
5 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: ...
rene's user avatar
  • 305
5 votes
Accepted

Calculating price with a sub query

You have the same column twice, I think I would start there in your quest to make your SQL "sexier" (I probably wouldn't use that term in a professional environment [i.e. interviews]). ...
Malachi's user avatar
  • 28.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 ...
rolfl's user avatar
  • 97.3k
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) ...
dfhwze's user avatar
  • 13.9k
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: ...
t3chb0t's user avatar
  • 44.3k
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, ...
Dan Oberlam's user avatar
  • 7,804
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 ...
rene's user avatar
  • 305
4 votes
Accepted

Adding dynamic column names to a table

I would consider your whole proposal for this schema to be a code smell. In SQL, if there are many data to be treated alike, they should be stored in one column, rather than in multiple columns. If ...
200_success's user avatar
4 votes

Query that shows similar columns between different tables on a database

A common-table-expression (See CTE) will help you here quite nicely.. ...
rolfl's user avatar
  • 97.3k
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 ...
Sᴀᴍ Onᴇᴌᴀ's user avatar
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 ...
rene's user avatar
  • 305
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 ...
Bogdan Mart's user avatar
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 ...
dfhwze's user avatar
  • 13.9k
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 ...
Dan Oberlam's user avatar
  • 7,804
3 votes
Accepted

Who likes that post?

Overall, very good I inspected your query closely and did not find anything standing out that could really be improved much. Here are some good points I found: Your formatting is consistent and the ...
Phrancis's user avatar
  • 20.4k
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 ...
Kris Lawton's user avatar
3 votes

Adding dynamic column names to a table

Using dynamic queries to alter the database schema is typically a pretty bad idea. If you are changing "real" tables, it would be better to pull the alter statement in to a completely separate ...
rolfl's user avatar
  • 97.3k
3 votes

Pivot script for sales numbers

A few things that I noticed: Nesting You're nesting selects two levels deep. That's usually a bad sign for most of the data mining and report generation I had to ...
Vogel612's user avatar
  • 25.2k
3 votes
Accepted

SQL procedure for Archiving and Deleting

Nitpicks You use separate statements for DECLARE and SET quite a bit. While there is nothing wrong with that, it's often just ...
Phrancis's user avatar
  • 20.4k
3 votes
Accepted

Top 10 editors partitioned by year

This can be simplified down to a single query :-) You calculate COUNTs using different WHERE-conditions and aggregation levels, but the base tables are always the same: CountByYear: Edits per user &...
dnoeth's user avatar
  • 1,172
3 votes

TSQL query to gather amounts for an employees, spouses, and children in one row

You can use T-SQL's operator PIVOT to do this. ...
Mincong Huang's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible