Erik Darling
  • Member for 7 years, 11 months
  • Last seen this week
1 answers
-2 votes
3k views
A RETURN statement with a return value cannot be used in this context
Accepted answer
7 votes

The problems you have in your function: Take the double quotes off your parameter declarations You're not declaring @fechafinal anywhere All of the stuff you have between RETURNS INT and BEGIN isn't ...

View answer
2 answers
0 votes
302 views
Should I believe Query Store runtime stats?
7 votes

Pathology! This could happen to queries in ways that wouldn't impact the plans: Heaps: Forwarded Fetches Indexes: Internal fragmentation Additionally some selects that cause writes may trigger ...

View answer
5 answers
6 votes
11k views
Creating temporary table dynamically using condition
7 votes

Supported? I know this is tagged as 2008R2. Since that's officially out of support, perhaps an upgrade is in your future. If you end up on a version of SQL Server > 2012, you can use code like this: ...

View answer
1 answers
5 votes
100 views
Okay to merge included columns on otherwise identical indexes?
Accepted answer
7 votes

Yes, you should merge these indexes and only keep one, as long as you verify that: Neither of them are marked as UNIQUE (in which case, preserve that one) Neither of them have a filter condition ...

View answer
1 answers
5 votes
315 views
Queries running parallel with default maxdop
Accepted answer
7 votes

The simple answer is that 64 is the DOP limit when zero is specified: To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum ...

View answer
1 answers
4 votes
227 views
Find records with same string with extra character
Accepted answer
7 votes

The SOUNDEX function can be applied to a column as well. But since there's thousands like that I wouldn't suggest just writing a query to join on a function to do that. This will likely not ...

View answer
3 answers
5 votes
481 views
"SELECT *" why is it an antipattern
7 votes

The two reasons that I find the most compelling not to use SELECT * in SQL Server are Memory Grants Index usage Memory Grants When queries need to Sort, Hash, or go Parallel, they ask for memory ...

View answer
1 answers
2 votes
5k views
Create index on 106 million row table
Accepted answer
7 votes

I'm leaving this as an answer, even though it's mostly an Extended Comment®. You don't mention the index definitions, or the types of columns you're trying to index. You say the table is 200 columns, ...

View answer
1 answers
3 votes
616 views
How to find Stored Procedures With Recompile?
Accepted answer
7 votes

Per database, you can do this: SELECT DISTINCT DB_NAME() AS DBName, SO.name AS SPName, SM.is_recompiled, ISR....

View answer
3 answers
4 votes
1k views
How Selective Should Index Be
Accepted answer
7 votes

Considering column selectivity only when deciding which columns to index ignores quite a bit of what indexes can do, and what they're generally good for. For instance, you may have an identity or ...

View answer
6 answers
11 votes
5k views
Best situation to use READ UNCOMMITTED isolation level
7 votes

I think it's fine in some circumstances, as long as you accept the consequences, and don't have other options. For other options, I'd push people towards using Read Committed Snapshot Isolation (RCSI)...

View answer
1 answers
3 votes
570 views
Best practices on dealing with devs that want to use XP_CMDSHELL to BCP OUT
Accepted answer
7 votes

There are two things at play here Data security Server health For 1, management has to be okay with any developer walking out the door with whatever data they export to a text file. This might be ...

View answer
3 answers
7 votes
454 views
REORGANIZE seems to work as well as REBUILD all the time
7 votes

The big difference, in my environment, is that Reorganize is single threaded, and Rebuild can go as parallel as you have processors. Rebuild also has the added bonus of updating your statistics, so ...

View answer
3 answers
1 votes
5k views
SQL Server: Improve CTE performance with case statements
7 votes

I think you might be writing code under a faulty assumption: CTE results are persisted They're not, and every time you reference them, the syntax is re-executed. Here's a quick example: CREATE ...

View answer
2 answers
5 votes
40k views
Parent Child Relation in same Table
7 votes

A common way to do this in SQL Server is to use a Recursive CTE: Here's your test data as an insert: CREATE TABLE #yourmom ( custid INT, custname VARCHAR(10), deptid VARCHAR(3), ...

View answer
1 answers
2 votes
476 views
Database Mirroring - Mixed SQL Server 2012 to SQL Server 2016
Accepted answer
7 votes

The short answer is that yes, you can Mirror up to a higher version, but you should really only do this if you're going to upgrade. Once you fail over, you can't fail back down to a lower version.

View answer
1 answers
5 votes
1k views
How can I tell how many Cores SQL Server, at peak, it is using?
7 votes

However, I want to know how many of those cores are, at peak, REALLY being used This isn't really the way you'd want to figure out hardware size for a new server. You'd want to look at your server's ...

View answer
4 answers
2 votes
6k views
why computed columns slowing down performance all the sudden?
7 votes

I'm going to throw this out there, since no one else has mentioned it. Having a scalar valued function in a computed column is an awful idea. Forces ALL queries against the table to run serially (...

View answer
4 answers
23 votes
117k views
How to get a group where the count is zero?
7 votes

Cleaning up code to work on a case sensitive instance... CREATE TABLE Streets ( ID INT IDENTITY PRIMARY KEY, Name VARCHAR(100) ); CREATE TABLE users ( ID INT IDENTITY PRIMARY KEY, ...

View answer
1 answers
7 votes
5k views
Performance issue with sp_executesql and VARCHAR parameter
Accepted answer
7 votes

What you're running into is the curse of local variables. The short of it is that when you declare a variable and then use it in a query, SQL can't sniff the value. It sometimes uses magic numbers ...

View answer
1 answers
1 votes
61 views
What happens to queries that are in progress when we change maxdop in live system and while they are executing?
6 votes

plan cache Changing MAXDOP will clear out the plan cache, which means queries will have to come up with new execution plans. If your server was tanked because of an issue with one or more poor query ...

View answer
1 answers
2 votes
232 views
SQL Server using index scan instead of seek, all of a sudden
Accepted answer
6 votes

El Filter The reason your plan changed is because the cardinality estimate for how many rows will pass through the filter operator changed pretty drastically. This is sort of a tough thing to ...

View answer
1 answers
3 votes
98 views
sp_HumanEvents @event_type = N'blocking' and logging job help needed
Accepted answer
6 votes

First The blocked process report seconds is how many seconds blocking has to go on for before it makes it into the report. I don't have a specific recommendation here, but 10 or higher is usually a ...

View answer
2 answers
6 votes
151 views
Reset Column Header Index
Accepted answer
6 votes

Probably the least complicated option would be to select all your data into a new table with the correct column names, filtering out the row of values containing the header values. From there, you can ...

View answer
2 answers
6 votes
289 views
How to control position of startup predicate in the execution plan
6 votes

Is there any whitepaper or documentation on how SQL Server is placing startup predicates? No, not that I've ever seen. They don't get written about all that much. The one time I blogged about them, I ...

View answer
2 answers
3 votes
642 views
Cross join yields execution plan that reads one of the tables multiple times
Accepted answer
6 votes

My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed ...

View answer
2 answers
2 votes
13k views
Sum up counts in subquery
Accepted answer
6 votes

Does this do what you want? SELECT SUM(x.records) FROM ( SELECT COUNT(thecol) AS records FROM thetable WHERE thecol IS NOT NULL GROUP BY thecol HAVING COUNT(*) > 1 ) AS x

View answer
2 answers
2 votes
3k views
Create stored procedure that contains newlines with a oneliner sql statement
6 votes

Yes, you can do something like this with dynamic SQL: DECLARE @SQL NVARCHAR(MAX) = N''; DECLARE @NewLine NCHAR(1) = NCHAR(10); SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' ...

View answer
1 answers
1 votes
72 views
Confused to use join or sub-query SQL?
Accepted answer
6 votes

but which is more accurate / relevant for more number of records ? Neither and both. The optimizer is free to rewrite subqueries as joins. For clarity, since you don't need any columns from Table2, ...

View answer
1 answers
1 votes
424 views
Add values from two existing column into new columns for a total
Accepted answer
6 votes

Just change your table create statement to use a computed column. CREATE TABLE [dbo].[Incomes]( [Id] [int] IDENTITY(1,1) NOT NULL, [ClientIncome] [float] NOT NULL, [PartnerIncome] [float] ...

View answer
1
3 4
5
6 7
16