0

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

2
  • 1
    How are you generating this script? What choices are you making in SSMS? – Grant Fritchey May 29 '20 at 12:14
  • @GrantFritchey, I right-click on the DB name, choose 'Tasks' -> 'Generate Scripts' -> 'Select specific database object' (choose the table from above 'Students') -> in 'Advanced' tab of Select how scripts should be saved in 'Set scripting options' I placed two sreenshot in the question above what is/not selected. – query_question May 29 '20 at 12:40
1

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
2
  • Thank you for your feedback. Do not know why the extra space/data is stored after the value, should not be the case as it is a benefit of using NVARCHAR in general... I wonder why the extra placeholder is there for default. – query_question Jun 4 '20 at 20:39
  • When entered it was likely entered with the spaces, directly or indirectly. Without knowing the history it is hard to speculate. If it passed through a CHAR at some point, that would be why. If it was imported from a file using fixed width (vs delimited), that could have caused it. These are just 2 of many ways this could have came to be. – Graham Jun 4 '20 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.