Erik Darling
  • Member for 8 years
  • Last seen this week
Using HASHBYTES() yields different results for nvarchar and a variable
Accepted answer
15 votes

If you want a quoted string to be NVARCHAR (treated as Unicode), you need to prefix it with N. DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6' SELECT HASHBYTES('SHA1', N'...

View answer
GETUTCDATE()-2 vs DATEADD(d,-2,GETUTCDATE())
14 votes

There's no real difference there, but when you start using DATETIME2 values, or functions that return DATETIME2 values, you'll get errors. SELECT SYSDATETIME() - 1 AS [Incompatible] Msg 206, Level ...

View answer
SQL Server - How to achieve READCOMMITED and NOLOCK at the same time?
Accepted answer
14 votes

What you're looking for is an optimistic isolation level, like Snapshot Isolation, or Read Committed Snapshot Isolation. Code example: USE Crap; CREATE TABLE dbo.users (id INT, username NVARCHAR(40)...

View answer
Force SQL Server to use fragmented indexes?
Accepted answer
14 votes

Index Hint You can use an index hint to do that: SELECT * FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index) WHERE u.Reputation = 2; The downsides are: Potentially changing a lot of code If ...

View answer
Finding the default isolation level in SQL Server 2017 and Azure SQL DB
Accepted answer
14 votes

It's referring to Azure SQL Database which uses RCSI by default. Isolation Level SQL Database default database wide setting is to enable read committed snapshot isolation (RCSI) by having both the ...

View answer
CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?
14 votes

Hold on hold on hold on While the performance and licensing aspects are interesting, they're not the only aspect of a workload to consider. One thing that can have an impact on processor choice is ...

View answer
What function quotes an identifier in dynamic-sql with SQL Server?
Accepted answer
14 votes

The function that you're looking for is QUOTENAME! Through the practical use of square bracket technology, you can safely encapsulate strings to aid in the prevention of hot SQL injection attacks. ...

View answer
how to simulate a low-memory state when running a query
Accepted answer
14 votes

If you're on SQL Server 2012 SP3 or better, you can use the MAX_GRANT_PERCENT hint to cap a single query's memory grant. However, you'd have to mess with the number to figure out which percent gives ...

View answer
sp_msforeachdb in a stored procedure does not work well
Accepted answer
13 votes

sp_MSforeachdb has lots of known issues. If you follow the link, you'll get to a post by Aaron Bertrand that details them, and also provides an alternate solution.

View answer
Dynamic SQL query - how do I add an int to the code?
Accepted answer
13 votes

George's answer does solve your problem, but it leaves you wide open to SQL injection attacks. While converting an INT to a VARCHAR 11 is likely not going to cause any issues, sysname is the ...

View answer
Physical only checkdb is failing, but full one is completed successfully
Accepted answer
13 votes

Yes, this a defect in SQL Server 2017 up to CU14. There was a similar issue with nonclustered columnstore indexes in SQL Server 2016. It's detailed in this video: CHECKDB Bug With SQL Server 2017 And ...

View answer
MAXDOP Settings for SQL Server 2014
Accepted answer
13 votes

Bogus Here's why that wait stats report stinks: It doesn't tell you how long the server has been up. I can see it in your screenshot of CPU time: 55 days! Alright, so let's do some math. Math ...

View answer
How to multiply rows for a column that contains negative and zero values?
Accepted answer
13 votes

The magic of NULLIF seems to do the trick for the test case in your question. Since you used a different example than in your SQL Fiddle, I don't know if that's what you want there too. CREATE TABLE ...

View answer
FillFactor: IndexOptimize script by Ola Hallengren
13 votes

Ola's script will only change Fill Factor if you assign a value to the @FillFactor variable. IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @...

View answer
Offline Index Rebuild on a Partitioned Table
Accepted answer
13 votes

I had some time to look at this, and since I already had some of the demo scripts written, it was pretty easy to check on the rest. Let's do the setup, then look at the results. This will create the ...

View answer
Best way to shrink a DB after nulling data from varbinary(max)?
Accepted answer
13 votes

It looks to me like just updating the columns to NULL will release pages for reuse. Here's a Very Scottish® demo, to celebrate it being almost 5PM, EST. USE tempdb; DROP TABLE IF EXISTS dbo....

View answer
Huge "Network I/O" type resource waits
Accepted answer
13 votes

Stop using Activity Monitor Use sp_WhoIsActive to figure out what's actually going on It could be backups, in which case there's not a whole heck of a lot you can do that doesn't involve hardware ...

View answer
SQL Server 2017: How do Batch Mode Adaptive Joins work?
Accepted answer
12 votes

Batch Mode Adaptive Joins For Batch Mode Adaptive Joins, the goal is to not pin join choice to a specific type at compile time. When available, Adaptive Joins allow the optimizer to choose between ...

View answer
How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?
Accepted answer
12 votes

How does SQL Server know which rows to delete? To understand how it's processed, it's probably more helpful to look at the execution plan for the query. Setup scripts are at the end. First, let's ...

View answer
how to get the CPU speed in SQL Server?
Accepted answer
12 votes

You could use xp_cmdshell, or you could use xp_regread. Since xp_regread is 'safer' (I mean, you don't have to change any configuration options to use it), you could do something like this: DECLARE @...

View answer
Aggressive Under-Indexing and no data for missing index
Accepted answer
12 votes

Free Unit Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further. No Missing Indexes? Why doesn't SQL Server have any missing ...

View answer
Should I avoid adding a clustered key to a table with high throughput
Accepted answer
12 votes

The main reason I would want a clustered index in this scenario is this line: The process for deleting process.Audit records will execute every hour and delete an hours worth of audits from x days ...

View answer
Batch Requests/sec reported by DMV millions of times larger than Activity Monitor
Accepted answer
12 votes

From the documentation: Note: For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any ...

View answer
Parameterized query creating many plans
11 votes

The length of @P1 is different in all of those. You're getting different plans because you're not explicitly setting parameter lengths in your code. Any difference in strings, no matter how minor, ...

View answer
AppDomain 2 SSISDB is Marked for Unload due to Memory Pressure
Accepted answer
11 votes

About your server This query spills badly in two places, despite a 29 GB memory grant. That's about the high end of what a query can ask for on your system with max server memory set to 115 GB. The ...

View answer
Why would a SELECT query cause writes?
Accepted answer
11 votes

Clumsy I couldn't remember if I included these in my original answer, so here's another couple. Spools! SQL Server has lots of different spools, which are temporary data structures stored off in ...

View answer
Why is using a table variable more than twice as fast as a #temp table in this specific case?
11 votes

Disco Inferno Since this is an older question, I decided to revisit the issue on newer versions of SQL Server to see if the same performance profile still exists, or if the characteristics have ...

View answer
Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode
11 votes

Forrest is mostly right, but the finer details are: SQL Server can't parallelize modifications to table variables, which your function uses. Prior to SQL Server 2017's Interleaved Execution, row ...

View answer
In SQL Server, How Does Parallelism Change Memory Grants?
Accepted answer
11 votes

Sup? For SQL Server queries that require additional memory, grants are derived for serial plans. If a parallel plan is explored and chosen, memory will be divided evenly among threads. Memory grant ...

View answer
Fragmentation Level for Heaps
Accepted answer
11 votes

Heaps have a few special challenges that you can't experience with clustered indexes: Forwarded Records Captive Pages I'd suggest running sp_BlitzIndex against your database to find out if either of ...

View answer
1
2
3 4 5
16