Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

PostgreSQL - translate encoding char to numbers and vice versa

select pg_encoding_to_char(6);
--Result: UTF8

select pg_char_to_encoding('UTF8');
--Result: 6

SQL Server - Disable Parameter Sniffing

CREATE PROCEDURE MyProcedure(@MyParam INT)
AS
SELECT ...
FROM ...
WHERE  MyParam = @MyParam
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

PostgreSQL - EXPLAIN - Show execution plan

EXPLAIN
Display the execution plan which PostgreSQL generates.
EXPLAIN ANALYZE
Display more statistics. Display actual run-time statistics.



In order to analyze INSERT, UPDATE, DELETE without affecting the data – run EXPLAIN ANALYZE in a transaction:
BEGIN;
    EXPLAIN ANALYZE sql_statement;
ROLLBACK;

CTE - SQL Server vs PostgreSQL


With myCTE AS (SELECT * FROM dbo.MyTable)
SELECT *
  FROM myCTE 
  WHERE Id = 1;


In SQL Server CTEs are processed with the main query.
In PostgreSQL CTEs are processed separately from the main query

Implications of this difference are:
  1. A query that should touch a small amount of data instead reads a whole table and possibly spills it to a tempfile.
  2. You cannot UPDATE or DELETE FROM a CTE term, because it’s more like a read-only temp table rather than a dynamic view.
  3. In PostgreSQL, in this query:
With myCTE AS (SELECT * FROM dbo.MyTable)
SELECT *
  FROM myCTE 
  WHERE Id = 1;
WHERE clauses aren’t applied until the execution of the main query, and it’s different than:
With myCTE AS (SELECT * FROM dbo.MyTable WHERE Id = 1)
SELECT *
  FROM AllPosts;

PostgreSQL - relation "pg_stat_statements" does not exist

What I did:
select * from pg_stat_statements;
-- and pg_stat_statements is enabled (if not - check here)

Error message:
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

Cause:
pg_stat_statements is not set in postgresql.conf.
It probably looks like:
#shared_preload_libraries = '' # (change requires restart)
or with other libraries.

Solution:
1. Set pg_stat_statements in postgresql.conf; adding:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

2. Restart PostgreSQL service

Check it:
SHOW shared_preload_libraries;

PostgreSQL - relation "pg_stat_statements" does not exist

What I did:
select * from pg_stat_statements;

Error message:
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: select * from pg_stat_statements
                      ^
Cause:
pg_stat_statements is not available globally but can be enabled for a specific database with CREATE EXTENSION.

Solution:
Enable pg_stat_statements:

CREATE EXTENSION pg_stat_statements;

Check it:
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';

SAP ASE (Sybase) - Select jobs list

sp_sjobhelp