Sql-server – MS SQL Management Studio – Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR

bulk-insertdatatypesinsertsql servert-sql

I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data type. As you can see on the below screenshot, the student names are up to 5 characters which should most equal 10 bytes. However, MS Management Studio has allocated the maximum size to the field as lots of scroll to the right is needed to see the whole insert statement…?

Please advise – is there a way to prevent this?

edit: Please find the advanced scripting options selection from MS Server Management Studio on the below screenshots:

enter image description here
enter image description here

Thanks much in advance!

CREATE TABLE [dbo].[Students](
    [StudentID] [int] NOT NULL,
    [StudentName] [nvarchar](100) NOT NULL,
    [StudentSurname] [nvarchar](100) NOT NULL,
    [Biology] [decimal](3, 2) NOT NULL,
    [Maths] [decimal](3, 2) NOT NULL,
    [Geography] [decimal](3, 2) NOT NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
    [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (1, N'Mike                                                                                                ', N'Manson                                                                                              ', CAST(2.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(1.00 AS Decimal(3, 2)))
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (2, N'Timo                                                                                                ', N'Torn                                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (3, N'Jeffrey                                                                                             ', N'Jones                                                                                               ', CAST(1.00 AS Decimal(3, 2)), CAST(3.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO

Best Answer

I believe that the underlying data in your table has additional spaces at the end. You can verify the actual data length with a query like:

SELECT LEN(StudentName) FROM dbo.Students WHERE StudentID = 1

If it's not 4, then it's storing more than just 'Mike' and SSMS is simply preserving your data.

If you don't want the extra spaces, you can remove it from your data thusly:

UPDATE dbo.Students
SET 
    StudentName = LTRIM(RTRIM(StudentName)), -- older db compatibility levels
    StudentSurname = TRIM([StudentSurname]) -- new db compatibility levels
Related Question