Write Differences Between NVARCHAR and VARCHAR

Write Differences Between NVARCHAR and VARCHAR

use SandBox

go

CREATE TABLE dbo.t(c NVARCHAR(32));

INSERT dbo.t(c) SELECT 'រៀន';

INSERT dbo.t(c) SELECT 'នរៀ';

INSERT dbo.t(c) SELECT N'រៀន';

SELECT c FROM dbo.t;

SELECT c FROM dbo.t WHERE c = 'រៀន';

SELECT c FROM dbo.t WHERE c = N'រៀន';

List of SQL Server databases

SELECT name FROM master.sys.databases

Current DateTime as a string

DECLARE @CurrentDateTime as varchar(50)

SELECT @CurrentDateTime = CAST(Year(GetDate()) as nvarchar(4)) 
+ (CASE 
WHEN Month(GetDate()) < 10 THEN '0' + CAST(Month(GetDate()) as nvarchar(2))
ELSE CAST(Month(GetDate()) as nvarchar(2))
END)
+ (CASE 
WHEN Day(GetDate()) < 10 THEN '0' + CAST(Day(GetDate()) as nvarchar(2))
ELSE CAST(Day(GetDate()) as nvarchar(2))
END)
+ '_'
+ (CASE 
WHEN DatePart(hour, GetDate()) < 10 THEN '0' + CAST(DatePart(hour, GetDate()) as nvarchar(2)) ELSE CAST(DatePart(hour, GetDate()) as nvarchar(2))
END)
+ (CASE 
WHEN DatePart(minute, GetDate()) < 10 THEN '0' + CAST(DatePart(minute, GetDate()) as nvarchar(2))
ELSE CAST(DatePart(minute, GetDate()) as nvarchar(2))
END)
+ (CASE 
WHEN DatePart(second, GetDate()) < 10 THEN '0' + CAST(DatePart(second, GetDate()) as nvarchar(2))
ELSE CAST(DatePart(second, GetDate()) as nvarchar(2))
END)

SELECT @CurrentDateTime

T-SQL to find IP Address

SELECT CONNECTIONPROPERTY('net_transport') AS net_transport, CONNECTIONPROPERTY('protocol_type') AS protocol_type, CONNECTIONPROPERTY('auth_scheme') AS auth_scheme, CONNECTIONPROPERTY('local_net_address') AS local_net_address, CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port, CONNECTIONPROPERTY('client_net_address') AS client_net_address

PostgreSQL CamelCase problem

Given an OO language in which the usual naming convention for object properties is camelCased, and an example object like this:

{
    id: 667,
    firstName: "Vladimir",
    lastName: "Horowitz",
    canPlayPiano: true
}

How should I model this structure in a PostgreSQL table?

There are three main options:

1. unquoted camelCase column names
2. quoted camelCase column names
3. unquoted (lowercase) names with underscores

They each have their drawbacks:

1. Unquoted identifiers automatically fold to lowercase. This means that you can create a table with a canPlayPiano column, but the mixed case never reaches the database. When you inspect the table, the column will always show up as canplaypiano - in psql, pgadmin, explain results, error messages, everything.
2. Quoted identifiers keep their case, but once you create them like that, you will always have to quote them. IOW, if you create a table with a "canPlayPiano" column, a SELECT canPlayPiano ... will fail. This adds a lot of unnecessary noise to all SQL statements.
3. Lowercase names with underscores are unambiguous, but they don't map well to the names that the application language is using. You will have to remember to use different names for storage (can_play_piano) and for code (canPlayPiano). It also prevents certain types of code automation, where properties and DB columns need to be named the same.

So I'm caught between a rock and a hard place (and a large stone; there are three options). Whatever I do, some part is going to feel awkward. For the last 10 years or so, I've been using option 3, but I keep hoping there would be a better solution.

T-SQL to JSON

you need to use a varchar(max) else SSMS will limit it to 2033 chars

---------

declare @FSLI_JSON as varchar(max) = (

    SELECT [col1]
, [col2]
, [col3]...
    FROM [dbo].[myTable]
    FOR JSON auto
)

SELECT @myJSON