Will the spaces of NVarchar(100) be included in the index

BenTam 1,781 Reputation points
2021-12-04T07:06:43.66+00:00

Dear All,

When I index on Name and StudentID, would the spaces of the Name field participate in the index.

  • Left(Name+Replicate('0',200),200), StudentID
    ,or just the characters of the name field, such as
  • Name, StudentID

154976-indexquest.gif

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Ronen Ariely 15,206 Reputation points
    2021-12-04T07:26:59.547+00:00

    Hi,

    Left(Name+Replicate('0',200),200), StudentID

    In your code you add the character 0 and not space

    Will the spaces of NVarchar(100) be included in the index

    Yes. Spaces are characters like any other character

    ----------

    UPDATE: ADDING FULL DEMO WHICH CREATE TABLE AND CHECK INDEX WITHOUT SPACES, WITH ZERO, and WITH SPACES

    use tempdb  
    GO  
      
    DROP TABLE IF EXISTS T  
    GO  
      
    CREATE TABLE T(  
     StudentID INT,   
     NameClean NVARCHAR(200),   
     NameWithZeo NVARCHAR(200),   
     NameWithSpace NVARCHAR(200))  
    GO  
      
    -- fill 10000 randome NameClean length between 0 to 100  
    DECLARE @StringMaxLen INT = 100  
    INSERT T(StudentID, NameClean)   
     SELECT TOP (10000)  
     ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),  
     LEFT (CAST (NEWID () AS NVARCHAR(MAX)) , ABS (CHECKSUM (NEWID ())) % @StringMaxLen + 1)  
     FROM SYS.OBJECTS A  
     CROSS JOIN SYS.OBJECTS B  
    GO  
      
    -- fill the columns with sero and with spaces  
    UPDATE T  
     SET NameWithZeo = Left(NameClean+Replicate('0',200),200),  
     NameWithSpace = Left(NameClean+Replicate(' ',200),200)  
    GO  
      
    SELECT StudentID,NameClean,NameWithZeo,NameWithSpace,DATALENGTH(NameWithSpace)   
    FROM T  
    GO  
      
    CREATE NONCLUSTERED INDEX Ix_T_NameClean ON T (studentID,NameClean);     
    GO  
    CREATE NONCLUSTERED INDEX Ix_T_NameWithZeo ON T (studentID,NameWithZeo);     
    GO  
    CREATE NONCLUSTERED INDEX Ix_T_NameWithSpace ON T (studentID,NameWithSpace);     
    GO  
    

    Let's check the size of the three indexes

    -- check the index size  
    SELECT i.[name] AS IndexName ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB  
    FROM sys.dm_db_partition_stats AS s  
    INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id]  
    where [name] in ('Ix_T_NameClean','Ix_T_NameWithZeo','Ix_T_NameWithSpace')  
    GROUP BY i.[name]  
    ORDER BY i.[name]  
    GO  
    

    154894-image.png

    Notice that the size of the index for the column with zero is exactly the same as the size of the column with spaces, since both have 200 characters of length 2 bytes, but the first column with clean name has smaller index since it has clean name in length between 0 to 200 bytes


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 122.3K Reputation points MVP Volunteer Moderator
    2021-12-04T11:17:18.093+00:00

    I'm not sure what the question is, but if the value of column is Eva, then only Eva is part of the index. There are no 197 extra spaces, not in the index, nor on the data page. If the value is Eva with three trailing spaces, the spaces are present both on the data page and on the index.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.