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