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

Question

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

Answer 1

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  

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

Answer 2

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.