Microsoft SQL Server Management Studio query window INSERT statement results issue

• Question

• 

  

  0

  Sign in to vote

  Environment:
  Workstation: 16GB RAM. 500MB SSD
  Windows 10 Pro (64-bit)
  SQLServer 2019 (Express Edition) Just installed 2 weeks ago.
  
  Not available on workstation (though some supporting libraries may be present, e.g. .NET framework 3.5 && 4.8 for Windows 10):
  Visual Studio
  C# compiler
  etc.
  
  Task: Select entered VARCHAR data.
  
  Error/roadblock (steps to reproduce):
  
  Within Microsoft SQL Server Management Studio, 
  
  I have a simple database "test2020" with 1 table "eligibleperson" with 6 columns (2 int, 4 nvarchar), and 1 primary/identify key (personid).
  
  I hand-type (copied from an Excel spreadsheet) an INSERT statement:
  
  USE [test2020]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  INSERT INTO dbo.eligibleperson
  Values
  ( 820682 , N' Michael ',N' K ',N' Alley ',N' Active ', 10003 ),
  ( 820686 , N' David ',N' J ',N' Anderson ',N' Active ', 10008 ),
  ( 820687 , N' Lynn ',N' C ',N' Anderson ',N' Active ', 10009 ),
  ( 820688 , N' Bob ',N' A ',N' Anderson ',N' Active ', 10010 ),
  ( 820689 , N' Will ',N' H ',N' Banker ',N' Active ', 10011 ),
  ( 820690 , N' Ranesse ',N' T ',N' Barsanti ',N' Active ', 10012 ),
  ( 820696 , N' Carol ',N' Reid ',N' Belew ',N' Active ', 10013 ),
  ( 820698 , N' Cathy ',N' L ',N' Bertucci ',N' Active ', 10015 ),
  ( 820699 , N' Dennis ',N' K ',N' Bertucci ',N' Active ', 10020 ),
  ( 820701 , N' Bruce ',N' W ',N' Bilgers ',N' Active ', 10030 ),
  ( 820709 , N' Irene ',N' M ',N' Boden ',N' Active ', 10036 ),
  ( 820717 , N' Carl ',N' E ',N' Brookes ',N' Active ', 10040 ),
  ( 820718 , N' Tom ',N' Scott ',N' Streamer ',N' Active ', 10050 )
  GO
  
  The records were successfully inserted:
  
  USE [test2020]
  GO
  select * from eligibleperson
  GO

  results:
  personid firstname middlename lastname countrystatus countryid
  
  820682   Michael K Alley                             Active            10003
  820686   David J Anderson                          Active            10008
  820687   Lynn C Anderson                           Active            10009
  820688   Bob A Anderson                            Active            10010
  820689   Will H Banker                                 Active            10011
  820690   Ranesse T Barsanti                         Active            10012
  820696   Carol Reid Belew                            Active            10013
  820698   Cathy L Bertucci                             Active            10015
  820699   Dennis K Bertucci                           Active            10020
  820701   Bruce W Bilgers                              Active            10030
  820709   Irene M Boden                               Active             10036
  820717   Carl E Brookes                                Active             10040
  820718   Tom Scott Streamer                       Active             10050
  
  Next I query:
  
  SELECT TOP (10) [personid]
        ,[firstname]
        ,[countrystatus]
    FROM [test2020].[dbo].[eligibleperson] where countrystatus='Active'
    GO
  
  No results are returned:  (0 rows affected)
  
  I also tried 
  
  SELECT TOP (10) [personid]
        ,[firstname]
        ,[countrystatus]
    FROM [test2020].[dbo].[eligibleperson] where countrystatus=' Active'
    GO
  
  No results are returned:  (0 rows affected)

  NOTE the leading whitespace in ' Active'
  
  
  
  I did notice this query
  
  SELECT TOP (6) [personid]
  
        ,[firstname]
  
        ,[countrystatus]
  
        ,LEN(personid) personidlength
  
        ,LEN(firstname) firstnamelength
  
        ,LEN(countrystatus) countrystatuslength
  
    FROM [test2020].[dbo].[eligibleperson]
  
    GO
  
  Returns unexpected results:
  
  personid firstname countrystatus personidlength firstnamelength countrystatuslength
  
  820682  Michael     Active             6                       9                         8
  
  820686  David        Active             6                       7                         8
  
  820687 Lynn          Active              6                       6                         8
  
  820688 Bob           Active              6                       5                         8
  
  820689 Will           Active              6                       6                          8
  
  820690 Ranesse    Active              6                       9                          8
  
  Notice
  
  personidlength is what I expect, 6
  
  firstnamelength is always longer than I would expect by 2
  
  countrystatuslength is always longer than I would expect by 2
  
  Is that a clue?
  
  TIA

  
  

  Wednesday, August 5, 2020 4:41 PM