6 Things Should Know on SQL Server for PostgreSQL User

1. No Limit — use Top

SELECT * FROM my_table LIMIT 1000

-- To be

SELECT TOP 1000 * FROM my_table

Microsoft variation of Standard SQL is called ‘T-SQL’ and this is one of the example of ‘T-SQL’ differences.

2. There is no Boolean use 1 and 0

The make me feel back in time.

3. Timestamptz is Datetimeoffset, Timestamp is Datetime2

datetime2
datetimeoffset

4. Weird way to Rename and Describe

The normal alter is not working to rename. They have built-in tored proc to do this called sp_rename to rename Table and Column.

sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

sp_rename 'old_table_name', 'new_table_name';

If you would like to see the table structure, there is no DESCRIBE

sp_columns table_name

5. The Case and Space on String Comparison

Let’s see the following code and result

create table test_car (
 id integer identity,
 brand varchar(255)
)

insert into test_car (brand) values ('nissan'), ('toyota'), ('honda')

select * from test_car
where brand = 'NISSAN'
-- Get (1, nissan)

select * from test_car
where brand = 'NISSAN    '
-- Get (1, nissan)

select * from test_car
where brand = 'Nissan'
-- Get (1, nissan)

select * from test_car
where brand = '   Nissan'
-- No result

select * from test_car 
where brand = 'nissa n'
-- No result

select * from test_car 
where brand = 'NiSsAn'
-- Get (1, nissan) !

select * from test_car 
where brand = 'NISSan'
-- Get (1, nissan) !

The trailing Spcae and Case is ignored in SQL Server

There is ANSI_PADDING but this is not appy to nvarchar

From Microsoft, https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver16

SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF doesn't apply to the nchar or nvarchar data types.

If you really need this, use COLLATE

6. Use NVARCHAR not VARCHAR

To support UTF-8.

Use can tweak VARCHAR to support UTF by setting collate but we still recommend NVARCHAR if you build not English only application.

Mixing VARCHAR and NVARCHAR is not recommended. Use NVARCHAR for all instead.

Tips : VARCHAR(MAX) is VARCHAR(8000)

Anything added to this is truncated

create table test_table_1(
 id integer identity,
 some_long_text varchar(max)
)

insert into test_table_1
select replicate('x', 8000) as some_long_text

insert into test_table_1
select replicate('x', 8000)+'y' as some_long_text

insert into test_table_1
select replicate('x', 7999)+'y' as some_long_text

select substring(some_long_text, len(some_long_text)-3, 4) from test_table_1

Now we see the behavior of text longer than 8,000 by seeing the end of that string if it is droped.

You will see the first two row get the same result of xxxx and the last two is xxxy

Anything beyond 8,000 is ignored