CHARINDEX and PATINDEX are used to get starting position of a pattern. The functional difference is that the PATINDEX can use wild characters in the pattern being searched whereas CHARINDEX can’t. Another functional difference is that the pattern to be searched is limited to 8000 byte in CHARINDEX.In this blog I’ll look at CHARINDEX vs. PATINDEX performance comparison.
These functions accept two parameters
– The pattern to be searched for the starting position and
– The string to search the pattern for
Let’s run a simple query and compare the execution plan for CHARINDEX AND PATINDEX
The CHARINDEX AND PATINDEX came out to be equal in performance comparison as shown in above snapshot. The two queries search for CREATE pattern in sys.sql_modules.definition column and both are equally good as per the execution plan.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
