Implicit Conversion and SQL Server
Today, I spent most of my worktime analyzing a bug, where for a larger than normal number of input rows an SQL query would take longer than 30 seconds (default timeout duration) and the request would fail. After understanding the data flow and validating that the data itself shouldn’t cause any problems, I executed the procedure manually and checked the query plan.
It didn’t take long to spot the yellow bang on the query plan, but as a friend once told me, you can’t really trust what SQL Server thinks to know is wrong. So I scrolled a bit further to the right and the bold arrow essentially confirmed that the warning on the plan wasn’t justified.
Turns out, if your column type of table A is VARCHAR, but your JOIN or MERGE on table B with a column type of NVARCHAR, SQL Server will helpfully, implicitly cast from Unicode to non-Unicode. It however will also very “helpfully” suddenly fail to use your nicely crafted index and just check every single row you have in that table. So instead of doing an Index Seek with Look Up, you end up with an Index Scan, which for my example meant, that instead of one row the query ended up checking 6.3mil rows instead.
I ran into this issue before, but not with JOINs in a handcrafted stored procedure, but with Entity Framework translating any string as Unicode by default. So if your database tables use VARCHAR and you use EF, make sure you set IsUnicode(false) on the string property configuration.
Clang the Final Chapter
As playing around with Clang on Windows has come to an end (for me), I briefly want to summarize all the different ways to use Clang on Windows, as a sort of reference for my future self (oh hi there 👋).
MSVC + Clang
- Install Clang via the Visual Studio Installer
- Run CMake with
cmake .. -G"Visual Studio 16 2019" -T ClangCL -A x64
Clang + Clang-cl
- Install Clang via LLVM package
- Make sure you’re not inside an MSVC environment
- Make sure Clang is in your PATH or provide them to CMake as parameter
- Run CMake with
cmake .. -G"Ninja"
Clang + MSYS2 MinGW-w64
- Install MSYS2 from their website or Chocolatey (
choco install msys2) - Install the necessary packages (see yesterday’s TIL)
- Run CMake with
cmake .. -G"MinGW Makefiles" -DCMAKE_C_COMPILER=clang -DCMAKE_CXX_COMPILER=clang++