Which is quicker COALESCE OR ISNULL?

by

Had a quick look into this as it’s interesting to see a number of different comparisons out there on the performance between the 2. I think this blog post by Adam Machanic is most accurate in the performance benchmarking done on this topic, where the bottom line is:

… and ISNULL appears to pretty
consistently out-perform COALESCE by
an average of 10 or 12 percent

However, I share the same view as what he then goes on to say – that the difference is pretty negligible – e.g. in his tests, a million executions showed up on average a 0.7s difference. Is it worth it? I’d suggest there are probably bigger areas to optimise. But read the article, it’s a good read.

More Related Contents:

  1. What makes a SQL statement sargable?
  2. What do Clustered and Non-Clustered index actually mean?
  3. Which are more performant, CTE or temporary tables?
  4. SQL Server: Query fast, but slow from procedure
  5. XML Server XML performance optimization
  6. Using varchar(MAX) vs TEXT on SQL Server
  7. SQL Server pivot vs. multiple join
  8. LIKE vs CONTAINS on SQL Server
  9. SQL Server Query: Fast with Literal but Slow with Variable
  10. Does the order of columns in a WHERE clause matter?
  11. cross apply xml query performs exponentially worse as xml document grows
  12. Slow query with cfqueryparam searching on indexed column containing hashes
  13. SQL Server insert if not exists best practice [closed]
  14. An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server
  15. Why do table names in SQL Server start with “dbo”?
  16. Add alternating row color to SQL Server Reporting services report
  17. How Do I Split a Delimited String in SQL Server Without Creating a Function?
  18. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  19. How to enable bulk permission in SQL Server
  20. Should every User Table have a Clustered Index?
  21. How to load an XML file into a database using an SSIS package?
  22. TSQL: Create a view that accesses multiple databases
  23. How to connect to MS SQL Server using Inno Setup?
  24. Check if a temporary table exists and delete if it exists before creating a temporary table
  25. SQL count rows in a table
  26. Executing set of SQL queries using batch file?
  27. T-SQL incrementing counter based on change in a column value
  28. The request failed or the service did not respond in a timely fashion?
  29. Generate table relationship diagram from existing schema (SQL Server) [closed]
  30. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?

Leave a Comment