Hot answers tagged

29

The shorter query I can think of is SELECT COALESCE(MAX(Data), 'Default Value') FROM Configuration WHERE Name = 'NameOfConfiguration'; If the configuration is present it'll take it. If the configuration is not present the MAX will generate a NULL value (the MAX of nothing is NULL) that will be coalesced to the default value There is a more generic ...


22

Technically, the full outer join is insufficient. If during a certain week there was not a single user who posted their first question or answer, the plot for that week needs to be 0. It would be misleading to plot a direct line from the previous week to the following week, skipping the quiet week. So, strictly speaking, you need to generate a series of ...


16

You could use the SET NOEXEC statement, which controls whether the subsequent statements are executed (OFF) or not (ON). Use it in conjunction with your EXISTS test like this: IF NOT EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SQL_CLR_Functions') SET NOEXEC ON GO CREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N',') ...


14

I find this pretty nicely done and really hard to pick on. But here's a little something. Instead of writing this condition this way: WHERE Posts.CreationDate <= @today AND Posts.CreationDate > @target_week This is shorter and simpler: WHERE Posts.CreationDate BETWEEN @target_week AND @today Keep in mind though that's not exactly the same, ...


14

'2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. And then '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. If that wasn't Copy+Paste'd, you've given your fingers a lot of work here. And if it was... well then, you've given yourself more work than you should ...


14

The use of the Tags field on the Post is a poor choice for the query. It relies on a table scan, which is slow, because it has to check each question. Note, you can use the < and > characters to identify tag start and end values in the Tags column, but, as I say, don't use that column. Instead, you should do a join with the PostTags and Tags tables. ...


13

The query feels tortured because SQL is designed to work with sets of data. You're using SQL like an ordinary programming language, trying to turn tables into scalars, and embedding special values in the query rather than storing it as data. I recommend that you create and populate a ConfigurationDefaults table that is analogous to your Configuration table....


13

Your query is well structured, and consistent, but there is one issue you have failed to incorporate.... not all bad questions have a user. When questions are migrated, or there's a user deleted, the question may have no link back to the Users table. This requires an Outer Join to Users. Additionally, I have found that CTE expressions are fantastic for ...


13

TOP 1 When selecting values that correspond to Unique fields referenced by constants (OwnerUserId), you're only really selecting one field, meaning this is redundant. UVN Unexplained Variable Names Don't use names like Q and A, they're confusing at best and unreadable at worst. DECLARE @Q DECLARE @A DECLARE @Questions DECLARE @Answers Yo dawg, heard ...


13

Not utterly wrong, but q.PostTypeId = 1 and AND a.PostTypeId = 2 are superfluous. The only post types with a non-null value in ParentId are answers (see the Database schema documentation), and their values are the ids of questions, so the join already takes care of this. I'd slightly misunderstood the objective of the query at first, because questions ...


12

-- Number of weeks must not go into the future, hence the following: SET @weeks_ago = (CASE WHEN @weeks_ago >0 THEN -@weeks_ago ELSE @weeks_ago END); This boils down to taking the absolute value of the given parameter, so it could be simplified to: SET @weeks_ago = abs(@weeks_ago) I would expect the query to return 0 rows for invalid parameter values ...


11

I have looked at this query/report, and from the beginning I figured it must be missing something. I looked through the SQL, and can't identify it off-hand, so I figured I would build my own query, and see how they compare. The results I got are very different... :( Edit: Found the problem You cannot chain two outer joins.... Consider the query for the ...


11

Comments -- NumberWeeks: Number of weeks to go back -- DATETIME VARIABLES DECLARE @today DATETIME; SET @today = CURRENT_TIMESTAMP; DECLARE @weeks_ago INT; SET @weeks_ago = ##NumberWeeks:int?4##; The comment about the number of weeks should be much closer to the declaration. A better variable name would eliminate the need for this variable entirely. ...


10

Going through this code again, it appears there are a few things that could be improved. Use #Temp Table for User Selection Instead of doing a cheap-shot UNION select to get the set of users to process, the right way would be to create a temp table, and then conditionally populate it: create table #TopUsers ( UserId int not null, Reputation int ...


10

This doesn't do anything at all. ClosedDate IS NULL OR ClosedDate IS NOT NULL Saying "where is or not" results in the same thing as not including the statement at all. Either you're not getting the right results, or this could be removed.


10

Before we begin with the code... I just want to address one thing regarding test cases with sample data. To get the best out of a performance review of your queries, try to provide a sample that's as close as possible to your real data. You stated: Difference between the Demo code and real life: Items are in tables and not UNION ALL CTEs The list ...


10

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 has been since deprecated since at least 2012. I would remove them. Audit requirement I very much agree with @Austin Hasting that it's generally better to not transform the data into a ...


9

First off, please heed the advice from @Zak about Version Control System (VCS). That should be the first take-away from this. Slashy comment block You have 31 lines, 4462 characters of slashy documentation template. It seems like an eye-sore, at least to me, and distracts from the code and even the documentation itself because it is so dense. With some ...


9

Sanitizer is a dangerous misnommer IMO. A better name could be NameQuoter, since that's what it does: it uses the RDBMS-specific syntax for quoting identifiers - SQL Server using square brackets, MySQL using backticks, Oracle using backslashes: this has nothing to do with sanitizing, which from what I can tell is essentially impossible to achieve with this ...


8

Common Table Expressions are for all, intents and purposes, a Table and should be named accordingly, WITH cte_downvoted_posts AS Should be WITH DownvotedPosts AS Drop the Hungarian Naming style and go with Pascal for big name items like Tables and Columns. All objects in the Database should be Pascal (In my opinion)


8

Good things You use good local variables, and you are consistent with your naming. The typical naming for T-SQL is using PascalCase, however there are no standards and snake_case or camelCase work just as good, as long as you are consistent (which you are). You validate your values, although I am not quite sure why you chose 500 and 3 as arbitrary minimums (...


8

You can do this more succinctly by first grouping by the User and the Post Type to get a set of every first question and answer. Then group that set by the type of post and the week it occurred in. Finally use the PIVOT operator to pivot the rows into two columns so you can display it on the chart. DECLARE @FirstDate DATE = '2011-01-01'; --There was very ...


8

You started out with all your keywords being capitalized and then you abandoned your capitalization haphazardly throughout the script, this makes it a little hard to read because your brain says that keywords are capitalized and then all of a sudden you see a begin and your brain says, "hold up a second, no that's a keyword, never mind" make sure that ...


8

Misspellings There are several misspelled words you should correct (see line comments): -- current weak -- should be 'week' declare @CurrentWeakStart date = dateadd(day, 1-datepart(dw, @Today), CONVERT(date, @Today)) -- should be '@CurrentWeekStart' declare @CurrentWeakEnd date = dateadd(day, 7-datepart(dw, @Today), CONVERT(date, @Today)) --should be ...


7

You don't need the op join on Users since you can compare the user IDs in the question directly. Remove that join and in the where clause replace and op.Id != u.Id with and q.OwnerUserId != bo.UserId or move this to the first join on Votes.


7

You have OPTION (OPTIMIZE FOR (@DateStart UNKNOWN, @DateEnd UNKNOWN)); in the second query. Add it to the first one and see what happens, but I am guessing that it might speed that one up as well. This is not needed. WHERE [LeadsGeneration_ViewAllModelRequests].[Request Date Time] BETWEEN @DateStart AND @DateEnd These values will never be outside of ...


7

I don't know how you are using this information, but I think that you could create a very small table variable or temp table with like one column and then sort and select the information in order. DECLARE @Table1 TABLE ( Dimension VARCHAR(10), Size DECIMAL(4,2) ) INSERT INTO @Table1 ( Dimension , Size ) VALUES ('Length', @lLength),('...


7

Nitpicks Why is this indented? ALTER PROCEDURE [dbo].[Product_GetID] This indentation is inconsistent: UPDATE Product SET CategoryID = @CategoryID, Description = @Description, FullDescription = @FullDescription, Price = @Price, MarkupPer = @MarkupPer, LabourHours = @LabourHours, LabourRate = @LabourRate, Stock = @Stock WHERE ProductID = @...


7

I think your problem is do with the amount of cartesian products you are forming. I would approach something like this, as I alluded to in chat: with salesYTD as ( SELECT SalesPerson, InvoiceDate, Sum(Amount) As AmountOnDay FROM sales WHERE InvoiceDate > DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) GROUP By SalesPerson, InvoiceDate ), ...


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