SQL Server: set character set (not collation)

Question

How does one set the default character set for fields when creating tables in SQL Server? In MySQL one does this:

CREATE TABLE tableName (
    name VARCHAR(128) CHARACTER SET utf8
) DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Note that I set the character set twice here. It is redundant, I added both ways just to demonstrate.

I set the collation also to demonstrate that collation is something different. I am not asking about setting the collation. Most questions asking about character sets and encodings in SQL Server are answered with collation, which is not the same thing.

Answer 1

As stated in BOL

Each SQL Server collation specifies three properties:

• The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
• The sort order to use for non-Unicode character data types (char, varchar, and text).
• The code page used to store non-Unicode character data.

The quote above is from 2000 docs. See also this 2008 link. The below also demonstrates this.

DECLARE @T TABLE 
(
     code TINYINT PRIMARY KEY,
     Arabic_CS_AS CHAR(1) COLLATE Arabic_CS_AS NULL,
     Cyrillic_General_CS_AS CHAR(1) COLLATE Cyrillic_General_CS_AS NULL,
     Latin1_General_CS_AS CHAR(1) COLLATE Latin1_General_CS_AS NULL
);

INSERT INTO @T(code) VALUES (200),(201),(202),(203),(204),(205)

UPDATE @T 
  SET Arabic_CS_AS=CAST(code AS BINARY(1)),
      Cyrillic_General_CS_AS=CAST(code AS BINARY(1)),
      Latin1_General_CS_AS=CAST(code AS BINARY(1))

SELECT * 
FROM @T   

Results

code Arabic_CS_AS Cyrillic_General_CS_AS Latin1_General_CS_AS
---- ------------ ---------------------- --------------------
200  ب            И                      È
201  ة            Й                      É
202  ت            К                      Ê
203  ث            Л                      Ë
204  ج            М                      Ì
205  ح            Н                      Í