Insert into query adds records to unreadable format, how do I change that? 1

[WomanPro]

Hello everybody, I use Microsoft SQL Server Management Studio 19.1 (Microsoft SQL Server 2022)
I have created a database with one table Customers. When I run an INSERT INTO QUERY record is inserted but in unreadable format.

INSERT INTO Customers (custID,custAFM,custDOY, custName,custProfession,custHomePhone,custMobilePhone,custEmail,custAddress,custCity,custPrefecture,custPostCode,custFloor) 
VALUES('EL00000001','000000000','Αθηνών','Ασημακοπούλου Αγγελική','','','','','','','','','');

When I display all records with

SELECT * FROM Customers;

I see record with this format EL00000001 000000000 ??a??? ?s?µa??p????? ???e????
Initially I had the typeof the field nchar, I changed it to nvarchar without effect and I also changed it to text without effect too.
Any suggestions please? Why does this happen? Thank you so much in advanced.

 

[fredericofonseca]

you need to specify the strings as National strings e.g. N'X'

drop table if exists #customers

create table #Customers (custID NVARCHAR(200)
,custAFM NVARCHAR(200)
,custDOY NVARCHAR(200)
, custName NVARCHAR(200)
,custProfession NVARCHAR(200)
,custHomePhone NVARCHAR(200)
,custMobilePhone NVARCHAR(200)
,custEmail NVARCHAR(200)
,custAddress NVARCHAR(200)
,custCity NVARCHAR(200)
,custPrefecture NVARCHAR(200)
,custPostCode NVARCHAR(200)
,custFloor NVARCHAR(200)
) 

INSERT INTO #Customers (custID,custAFM,custDOY, custName,custProfession,custHomePhone,custMobilePhone,custEmail,custAddress,custCity,custPrefecture,custPostCode,custFloor) 
VALUES('EL00000001','000000000','Αθηνών','Ασημακοπούλου Αγγελική','','','','','','','','','')
, (N'EL00000001',N'000000000',N'Αθηνών',N'Ασημακοπούλου Αγγελική','','','','','','','','','')
; 

select *
from #Customers

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[WomanPro]

Thank you so much, I finally used NChar and NText wherever it need and it definetaly worked with N char in the insert into query.
Do I have to change the collation?

 

[fredericofonseca]

the collation should be according to the language you use

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

Do I have to change the collation?

Be very careful about changing the collation of anything. This is a very complex issue and should not be taken lightly.

Collations do not affect how the data is stored. It only affects how data is sorted and compared. There is a default collation on the SQL Server instance. This is used whenever you create a new database. There is a default collation for each database. This affects how strings columns are created unless you specify the collation. In fact, each and every string you create has a collation. If you don't have a consistent collation everywhere, you will likely find yourself specifying the collation in lots of unexpected places.

The biggest "gotcha" I experienced was when I sold my software to a customer in another country. The default collation was different and lots of things started failing. Specifically, I had problems with temp tables and table variables. These things are created in the tempdb, which is recreated whenever the instance is restarted. The default collation for tempdb is the default collation for the instance, which happened to be different from the default collation for my database. This is not a situation you want to get in to.

I agree with Frederico, basically, don't mess with the collation.

My database uses a case-insensitive collation (where A = a). The only time I use the collate clause in my application is when I want a case sensitive compare, which really only happens in 1 or 2 places.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chris Miller]

nchar and nvarchar (Transact-SQL)

These fields store UTF-16, which is able to support any language. So there is no thought necessary which collation supports Greek or whatever other language.

So the collation you set for your instance, database, tables or columns are only concerned with sorting and comparing aspects, not what languages are supported ot unsupported, they can be specialized on the rules of sorting for a specific language, some countries even have multiple norms about that.

So I'd conclude listen to both fredericofonseca and gmmastros. It surely is good at the start of defining your server and database to choose a collation that matches your needs. And obviously you need to be aware of the tempdb woes you could have, if you run on a server with another default collation than you need.

Chriss