0

I have a dynamic store procedure and I would like to know if there are any performance issues while having an nvarchar with empty spaces.

Here is a code for demonstration of the question - in my store procedure I have many other operations that are not relevant and also the way I get the dynamic nvarchar is different.

DECLARE @Query NVARCHAR(255)
CREATE #LocationStatus
(
    LocationStatusID INT NOT NULL,
    StatusID INT
)

--First Option:
SET @Query = 'INSERT #LocationStatus
              SELECT ID AS LocationStatusID,
                     StatusID
              FROM   dbo.CalculatedStatuses'

--Second Option
SET @Query = 'INSERT #LocationStatus SELECT ID AS LocationStatusID, StatusID FROM dbo.CalculatedStatuses'

EXEC (@Query)

Is there any performance issues between option 1 to option 2? (even if it is very little).

Thanks in advance :)

Misha Zaslavsky
  • 8,414
  • 11
  • 70
  • 116
  • 1
    There is always a performance hit. The point if it's measurable or not (and even if it's measurable if it'll impact your application or you won't even notice). In your case IMO there isn't any issue but you have the last word: measure it, it's not something we/you can guess. – Adriano Repetti Mar 31 '14 at 09:30

2 Answers2

1

From performance perspective, if you use MS SQL Server of course, the disk space is not the issue, but memory will be. Double the page reads, double index size, strange LIKE and = constant behaviour etc.

Do you need to store Chinese etc script? Yes or no?

A good article with Tony Rogerson, Joe Celko and Kalen Delaney.

And from MS BLOG "Storage and Performance Effects of Unicode"

A Stack overflow question, highlighting how bad nvarchar performance can be:

SQL Server uses high CPU when searching inside nvarchar strings

Community
  • 1
  • 1
Bud Damyanov
  • 30,171
  • 6
  • 44
  • 52
  • The question is about whether spaces inside a query will affect the query's performance, not about the data the query processes –  Mar 31 '14 at 11:02
0

There will be no difference, white characters are ignored by database engine - treated only as separators. Watch query plans for both alternatives, they will be the same.

avb
  • 1,743
  • 1
  • 13
  • 23