Issues with GRANTing permissions to AD group

You need to use AS principal.

Ref:
https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql

WITH GRANT OPTION

The GRANT … WITH GRANT OPTION specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts. When the principal that receives the permission is a role or a Windows group, the AS clause must be used when the object permission needs to be further granted to users who are not members of the group or role. Because only a user, rather than a group or role, can execute a GRANT statement, a specific member of the group or role must use the AS clause to explicitly invoke the role or group membership when granting the permission. The following example shows how the WITH GRANT OPTION is used when granted to a role or Windows group.


Foreign keys to primary tables or nested table

I think the 2nd is better.  Because I guess you should not allow a ProductUserCommentAction if there is no ProductUserComment.


Is it possible to set a timer for autovacuum in PostgreSQL

You can set a time where autovacuum should not run.

Here's how to (assuming You're on linux):

Create a script to turn off/on autovacuum
The script should contain :

sed -i 's/autovacuum = on/autovacuum = off/g' postgresql.conf

pg_ctl -D PGDATA reload

Place the script on cron.

Let's say You have cron to turn off autovacuum at 2 PM and then have cron to turn on autovacuum at 3 PM.

Hope it helps.


Send email when certain Data is being inserted in MS SQL Server

The quickest solution I can think of would be to create on your specific table an after insert trigger where, based on your conditions (the error no..etc), you'd call the system procedure msdb.dbo.sp_send_dbmail to send a custom email.

You'd have first to configure the mail (create a profile and test it). I don't really think there's a simpler way.

I think that you could create an alert based on a custom error number. But this means that you'll have to modify your current code that inserts data in that table to throw this custom error no, to be able to see the alert.


Your use of ORDER BY is correct. However, You need to know that it has to go in order by how you specified! So your query will first order sd.Status FIRST, then it will order by distance SECOND.

For example This is how you're expecting it to look (Just my random data I gave)

 sd.Status   distance
 ---------   --------
     A         231
     A         245
     A         657
     A         987
     B         123
     B         432
     D         465
     D         576
     S         555

Note how it is ORDERED.


We have recently implemented TDE along with AlwaysON in Production running SQL Server 2014.
In our application, we noticed a slight (1-3%) increase in CPU utilization. Your environment is different, so do a thorough load testing with some realistic PROD datasize.
Takeaways from embracing TDE :

Backup compression and TDE does not go hand in hand i.e. You wont be able to leverage backup compression when you enable TDE. This means your VLDB backups will be HUGE !

Instant File Initialization is not available when you enable TDE.

When restoring the databases from PROD to other environments (e.g. UAT, etc), you need to export the certificate so that the database can be restored.

You need to properly keep safe and backup certificates that you use to enable TDE.

Enabling TDE on a VLDB takes time. You can use this script to list out the databases, encryption status along with other useful information.
Remember: TDE will encrypt data at rest (on disk) ONLY. You still have to use encryption (e.g. force encryption) to protect data inflight.

Edit:
SQL server 2016 now supports backup compression on TDE enabled databases

while backing up a TDE-enable database, the compression will kick in ONLY if MAXTRANSFERSIZE is specified in the BACKUP command. Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB).


How to connect to an Amazon PostgreSQL database using SSL

Many connection options aren't available as command line flags. That's one of them. Use a connection string, e.g.:

psql "host=XXXX.rds.XXXX.com sslmode=require sslrootcert=rds-ssl-ca-cert.pem port=XXXX user=XXXXX dbname=XXXX"

(Syntax corrected)


Need help to construct query/pl-sql block to get DB table column name and query result as key-value pairs

According to your requirement you might be looking for UNPIVOT operator(If you are using 11g or later version). It converts column-based data into separate rows.

For example:

SQL> desc tbl1
 Name                      Null?    Type
 ----------------------------------------- -------- --------------
 ID                         NUMBER
 NAME                           VARCHAR2(20)

SQL> SELECT * FROM tbl1;

    ID NAME
---------- --------------------
     1 test name

WITH all_objects_data AS(
SELECT TO_CHAR(id) id, name FROM tbl1)
SELECT column_name, column_value FROM all_objects_data UNPIVOT(column_value FOR column_name IN (id,name));

COLU COLUMN_VALUE
---- ----------------------------------------
ID   1
NAME test name

For details: Pivot and Unpivot


I think you are looking for a FULL OUTER JOIN:

SELECT COALESCE(a.id,b.id,c.id), COALESCE(a.col1,'NULL'), COALESCE(b.col2,'NULL'), COALESCE(c.col3,'NULL')
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id=b.id
  FULL OUTER JOIN table3 c ON b.id=c.id;

or, if you want blanks instead of strings:

SELECT COALESCE(a.id,b.id,c.id), a.col1, b.col2, c.col3
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id=b.id
  FULL OUTER JOIN table3 c ON b.id=c.id;


Implementing audit trail ms sql using stored procedure

Some native options:

Change Data Capture
SQL Server Audit (if you also want to audit reads)

Both are Enterprise Edition features.


Where to look for a bottleneck after elimitating CPU, memory, and disk

The execution plan and statistics IO will tell you everything you need to know about what is going on.

There are four places the SQL Server can bottleneck and you listed three of them.  Disk (logical and physical IO), RAM or CPU.  After that it's just crappy code that generates more logical IO than is actually needed.

With all those tuning hints in there you've probably got some code that needs to be cleaned up.  You need to look at the execution plan for the query (post it if you'd like) and see where the problems in your query are at.  You can see if the IO is logical (cache hits) or physical (disk hits) from looking at the output when set statistics io on is enabled.


Is there a cutoff when using datetime and date datatypes in the where clause?

Stop using BETWEEN for date range queries; it's ambiguous and brittle. WHERE COL >= '20160901' and COL < '20161001' is so much easier to construct and isn't vulnerable to unexpected changes in type / precision / rounding.

See this post and this post.


Does enabling LPIM work even if the memory pressure is coming from SQL Server itself

Does enabling LPIM help in this scenario since i see IndicatorProcess=2 in most of the cases.

No.

  I already have 128GB RAM.Is that the maximum i can have for SQL Server 2016 Standard.?

Yes.

And

  I see that plan cache gets cleared often when i have more usage
  and
  most of [the frequent queries] runs fast when there are plans available in plan cache

Suggests you should focus on your plan cache.  Are there a large number of single-use plans?  If so consider setting the optimize for ad hoc workloads configuration option.


Expecting Postgresql WAL to shrink in size but it's not

It looks at "max_wal_size" to decide if WAL freed up by the just-finished checkpoint should be recycled or deleted.  But WAL files that have already been recycled (renamed in anticipation of future re-use) in prior checkpoints while "max_wal_size" was high will not get deleted when "max_wal_size" is lowered.  They will eventually work their way out of the system as they get reused and then deleted (rather then recycled again).

If you can't wait for that to happen, you will have to delete them manually, which is a rather perilous thing to do--if you mess it up you will destroy your database.


how to interact to second database through API on mongodb

As per MongoDB Blog here and MongoDB Jira Blog here It seems to mean your node server's connection to your MongoDB instance was interrupted while it was trying to write to it.

As here  Mongoose and the MongoDB driver both do connection buffering, so you need to shut both buffering mechanisms off to make your database operations fail fast when mongoose is not connected. By default, mongoose will wait until you reconnect before actually executing an operation. For example, the below findOne() callback will execute after the 'reconnect' event is emitted.

// When the mongodb server goes down, mongoose emits a 'disconnected' event
mongoose.connection.on('disconnected', () => { console.log('-> lost connection'); });
// The driver tries to automatically reconnect by default, so when the
// server starts the driver will reconnect and emit a 'reconnect' event.
mongoose.connection.on('reconnect', () => { console.log('-> reconnected'); });

// Mongoose will also emit a 'connected' event along with 'reconnect'. These
// events are interchangeable.
mongoose.connection.on('connected', () => { console.log('-> connected'); });

const MyModel = mongoose.model('Test', new mongoose.Schema({}));

// Stopping the server will emit a 'disconnected' event
await server.stop();
console.log('stopped server');

// Using callback by design, because when the driver is disconnected
// it "buffers" operations. If the 'disconnected' event was emitted, the driver
// will hold all operations until it reconnects, so "Finished query"
// won't print until **after** "restarted server" **and** "connected"
MyModel.findOne({}, function(error) {
  console.log('Finished query', error);
});

await new Promise(resolve => setTimeout(() => resolve(), 2000));

// Restarting the server will emit the 'reconnect' and 'connected' events
await server.start();
console.log('restarted server');

For your further ref here and here


Your only hope in this scenario is to have the PROCESS privilege.
The SUPER privilege allows you to kill processes. Naturally, you don't want that. On the other hand, the PROCESS privilege allows you to see the processlist.
According to the MySQL Documentation on the PROCESS Privilege

The PROCESS privilege pertains to display of information about the
threads executing within the server (that is, information about the
statements being executed by sessions). The privilege enables use of
SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to
other accounts; you can always see your own threads.

OK, Big Deal. The PROCESS Privilege lets you see the Process List. How does that help?
You can quickly detect a mysqldump in progress when you run SHOW PROCESSLIST; and see a pattern like this in the Info field:
SELECT /*!40001 SQL_NO_CACHE */ * FROM

If you have the PROCESS Privilege and are running MySQL 5.1+, you can run this query:
select COUNT(1) mysqldumpThreads
from information_schema.processlist
where info like 'SELECT /*!40001 SQL_NO_CACHE */%';

Here is a sample output:
mysql> select COUNT(1) mysqldumpThreads
    -> from information_schema.processlist
    -> where info like 'SELECT /*!40001 SQL_NO_CACHE */%';
+------------------+
| mysqldumpThreads |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

All you need to do is query for that number. If you get a nonzero, mysqldump must be running.
Give it a Try !!!


How to Get Backups & Restores Start and Finish Times?

The backups of the database can be retrieved using the following statement:

---------------------------------------------------------------------------------
--      Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT

/* Columns for retrieving information */

   -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
   -- msdb.dbo.backupmediafamily.device_type,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupmediafamily.logical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name,
   -- msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   -- msdb.dbo.backupset.is_snapshot,

   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,

   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'

FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   INNER JOIN msdb.dbo.backupmediaset
   on msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id

/* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs
-------------------------------------------------------------------------------*/
WHERE 1 = 1

ORDER BY
          2 desc, -- backup start
          1,      -- database name
          3 desc  -- backup end

The restore history table only contains when the database was restored and not how long it took. However, if you run the following statement just after the restore has occurred, then you will have an estimate of how long it took:

SELECT
    rh.destination_database_name AS RestoredDatabaseName,
    rh.restore_date              AS RestoreDate,
    GETDATE()                    AS CurrentDateAndTime,
    RIGHT('0' + CAST(CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) / 3600 AS VARCHAR),2) + ':' +
    RIGHT('0' + CAST((CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) / 60) % 60 AS VARCHAR),2) + ':' +
    RIGHT('0' + CAST(CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) % 60 AS VARCHAR),2)                          AS [Restore HH:MM:SS]
FROM msdb.dbo.restorehistory AS rh

This statement basically takes the current date & time (GETDATE()) and the time the restore was performed, and calculates the difference in HH:MM:SS.

Additionaly you can capture the time before and after the restore using GETDATE() as per Dat Nguyen's suggestion and then calculate the difference:

USE [master]
GO
ALTER DATABASE [YOURDATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DECLARE @RestoreStarted  AS DATETIME
DECLARE @RestoreFinished AS DATETIME
SELECT @RestoreStarted=GETDATE() -- capture the date/time before the restore starts
RESTORE DATABASE [YOURDATABASE] FROM  DISK = N'C:\SQL\Backup\YOURDATABASE\FULL\YOURDATABASE_FULL_20191109_223000.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
SELECT @RestoreFinished=GETDATE() -- capture the date/time after the restore finishes

SELECT TOP 1 rh.destination_database_name AS RestoreDatabase, rh.restore_date AS RestoreDateInRestoreHistory, @RestoreStarted AS RestoreStarted, @RestoreFinished AS RestoreFinished,
RIGHT('0' + CAST(CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) % 60 AS VARCHAR),2) AS [Restore HH:MM:SS]
FROM msdb.dbo.restorehistory AS rh
WHERE rh.destination_database_name = 'YOURDATABASE'
ORDER BY 2 desc

This returns the following output:

RestoreDatabase | RestoreDateInRestoreHistory | RestoreStarted          | RestoreFinished         | Restore HH:MM:SS
----------------+-----------------------------+-------------------------+-------------------------+--------------------
YOURDATABASE    | 2019-11-29 10:20:43.577     | 2019-11-29 13:16:29.590 | 2019-11-29 13:16:34.800 | 00:00:05

Reference Reading

SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format
restorehistory (Transact-SQL)
backupmediafamily (Transact-SQL)
backupset (Transact-SQL)
backupmediaset (Transact-SQL)


Convert right side of join of many to many into array

To aggregate most rows
While querying all or most items, it is typically substantially faster to aggregate rows from the "many"-table first and join later:
SELECT id, i.title AS item_title, t.tag_array
FROM   items      i
JOIN  (  -- or LEFT JOIN ?
   SELECT it.item_id AS id, array_agg(t.title) AS tag_array
   FROM   items_tags it
   JOIN   tags       t  ON t.id = it.tag_id
   GROUP  BY it.item_id
   ) t USING (id);

Use LEFT [OUTER] JOIN in the outer query if there can be items without tags - which would be excluded with [INNER] JOIN.
Since that does not multiply rows in the join, we need no GROUP BY in the outer SELECT.
Joining before aggregation also gets out of hands with more than one 1:n table in the FROM list (not in this simple case). See:

Two SQL LEFT JOINS produce incorrect result

To aggregate few rows
For a small percentage of rows, use a LATERAL join with an ARRAY constructor:
SELECT id, title AS item_title, t.tag_array
FROM   items i, LATERAL (  -- this is an implicit CROSS JOIN
   SELECT ARRAY (
      SELECT t.title
      FROM   items_tags it
      JOIN   tags       t  ON t.id = it.tag_id
      WHERE  it.item_id = i.id
      ) AS tag_array
   ) t;

Since an ARRAY constructor always produces a row (with empty array if the subquery is empty - subtle difference in the result!), LEFT JOIN LATERAL (...) ON true is not needed here. See:

What is the difference between LATERAL and a subquery in PostgreSQL?
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
Understanding multiple table join with aggregation

Aside
You had a typo in your query. 3rd column would be t.title. I added aliases to your original (un-aggregated) query to clarify:
SELECT i.id, i.title AS item_title, t.title AS tag_title
FROM   items      i
JOIN   items_tags it ON it.item_id = i.id
JOIN   tags       t  ON t.id = it.tag_id;

"id" or "title" are typically not very distinctive and not very useful identifiers. See:

How to implement a many-to-many relationship in PostgreSQL?


PostgreSQL Change owner of all tables under a specific schema

Reassigned Owned

There is a specific privilege command that does just this, RESASSIGN OWNED. This reassigns all objects, not just ones in a specific schema.

Schema-specific

You can generate the ALTER TABLE commands with the following,

SELECT format(
  'ALTER TABLE %I.%I.%I OWNER TO %I;',
  table_catalog,
  table_schema,
  table_name,
  current_user  -- or another just put it in quotes
)
FROM information_schema.tables
WHERE table_schema = 'mySchema';

In psql, you can run them by following it immediately with \gexec


Opening MySQL connection is sometimes very slow

Slow connections could be caused by the amount of memory consumed by each DB Connection being allocated.

Back on April 24, 2012, I answered the post How costly is opening and closing of a DB connection?. In that post, I mentioned how there are many buffers connected to a single DB session.

If your Java application strictly closes DB Connections and opens new ones with out, think about deallocating multiple data buffers and communication packets connected to that old connection.

Combine that with the DB Connection entering in infamous TIME_WAIT state (See my post Too Many Connections) and you get Connections that linger in the OS for quite a while before its resources are released (even if mysqld closed the connection already).

You may need to decrease the size of your sort_buffer_size, read_buffer_size, and join_buffer_size.

If you have any queries that run multiple joins in a single statement, that connection will have multiple join buffers (See my post Fast alternative for "NOT IN"). In that event, you have two choices

Increase the join_buffer_size to make sure an entire join level fits in memory (requires more RAM in the DB Server)
Decompose multiple join query that joins more than 2 tables into individual joins between 2 tables


SQL linked server returning error suddenly upon query

This does look like a network issue, as per this question on MSDN the "Protocol error in TDS stream" indicates as such.

This seems to be a very similar question that was resolved by disabling TCP Chimney Offload (think link in that question directs to a veritas article that describes how to do it).

Which may be a quick (and possibly dirty) was of trying to resolve it. If that is not the problem, then it is almost certainly a network problem (maybe a nic problem).

--

Edit:

Might be worth making sure the native client is fully up to date (by updating SQL Server, which is no bad thing anyway).

Also, if you're still struggling, add some more information about your environment to your question e.g. OS version, SQL Server version & update level, information about your nics etc.


MariaDB 10.1.22 to use more RAM rather than CPU

I see you see your innodb_thread_concurrency is 20. That's not good. Why ??? Over the years, I have learned that infinite concurrency is best (See my old post MySQL using too much CPU). Usually, I would recommend setting this to 0.
With recent improvements to InnoDB, I learned that 0 may not be the best value in some cases. A comparison of two values (0 and 64) was done the article Percona Server 5.7 performance improvements. The article states:

For ages (Vadim discussed this ten years ago!) InnoDB has had a universal workaround for most scalability issues: the innodb_thread_concurrency system variable. It allows you to limit the number of active threads within InnoDB and reduce shared resource contention. However, it comes with a trade-off in that the maximum possible performance is also limited.

From this article, I started to realize that 0 may not improve CPU a Percona Server instance, but it can slightly improve MySQL. Setting this to 64 may or may not help. Why may or may not ??? The article compares MySQL with Percona. You are using MariaDB. Therefore, you would have to experiment with this. Since innodb_thread_concurrency is dynamic, you could run
SET GLOBAL innodb_thread_concurrency = 0;

or
SET GLOBAL innodb_thread_concurrency = 64;

an see if the CPU will level off.
CPU is not the only issue. I also noticed your innodb_buffer_pool_size is only 4G (4096M) and you have 128G of RAM. From the looks of the processlist, you seem to have a full stack running on the machine. I would suspect that the innodb_buffer_pool_size is too small and MySQL (MariaDB) is spinning its wheels removing old pages and importing new pages in the InnoDB Buffer Pool. You have the room to increase it, because you have 109G (144515380k) of RAM free.
RECOMMENDATIONS

Change innodb_thread_concurrency to 0 or 64
Change innodb_buffer_pool_size to 8G or 16G


I found the solution to this.  Below is how I achieved such:

SELECT
CAST(userid As Int) As userid
,CAST(userlogin As varchar(500)) As userlogin
,CAST(userlogindate as date) As userlogindate
,CAST(userlastlogindate as date) As lastlogindate
FROM OPENQUERY(LinkedServer,
                 'Select
                  userid As "userid"
                  ,userlogin As "userlogin"
                  ,userlogindate As "userlogindate"
                  ,userlastlogindate As "Lastlogindate"
                  From syscatalog')


What are the rules for using COLLATE in a query?

COLLATE operates per predicate or per expression, depending on context. COLLATE is mainly used to control how the string values are being compared or sorted. Hence it is most commonly used in JOIN, WHERE, and HAVING predicates, as well as GROUP BY and ORDER BY clauses (in which case it can be used per column / expression). It needs to be applied to string columns that could possibly have differing Collations. Non-string types (including XML) do not use COLLATE, and columns that are guaranteed to have the same Collation do not need it. For JOIN, WHERE, and HAVING predicates, it only needs to be specified on one side of the operator since the other side will be coerced into the specified Collation.

It generally wouldn't be used in a SELECT list unless there was a reason to change the Collation of the selected column / expression, which would mainly apply to VARCHAR data as you can change the Code Page, but I really don't see much application for this usage. The most likely reason to do this would be if the client requesting the data needs a different Code Page, but I can't think of a reason that this would ever be needed.

However, one reason to use it in the SELECT list (and possibly other places) is when doing string concatenation involving two or more columns that can potentially have mixed Collations. This scenario would apply to both VARCHAR and NVARCHAR data. You shouldn't need to worry about single columns and string literals as the literals will be coerced into the Collation of the column.

Another reason you might need COLLATE in a SELECT list is when using UNION, INTERSECT, or EXCEPT, and the Collation between columns / expressions in the same position between at least two of the queries is not the same.

So, looking at your query:

You don't need COLLATE in these places:

role_=dp.name collate Latin1_General_CI_AS,
because you are just selecting a single column, nothing is being mixed.
Obj = sys.schemas.name + '.' + so.name collate Latin1_General_CI_AS,
because both columns are database-level meta-data from the same database which is guaranteed to be the same Collation, plus a string literal that is already the database's default Collation (same as the two column's it is being concatenated with), but even if it wasn't already the same, it would be coerced into the Collation of the two columns. Only improvement here would be to prefix the literal with an upper-case N since the expression is NVARCHAR due to the schema names and object names being of type sysname which is an alias for NVARCHAR(128).
Permission = permission_name collate Latin1_General_CI_AS,
because you are just selecting a single column, nothing is being mixed.

You do need COLLATE in the string concatenation because you are mixing database-level meta-data (which uses the database's default Collation) and system-level meta-data (i.e. state_desc and permission_name) which comes from the hidden mssqlsystemresource database and usually has a Collation of Latin1_General_CI_AS_KS_WS. It should be fine to have COLLATE specified only once per expression since it should have the highest precedence and force both literals and columns into the stated Collation.

Finally, regarding which Collation to specify: DATABASE_DEFAULT, CATALOG_DEFAULT, or one of your choosing (such as you are doing here): given that you are working on NVARCHAR data (no chance of changing the Code Page / character set) and not using COLLATE for sorting or comparison (no chance of changing how the query works between different databases), it really doesn't matter; it's all the same in this particular scenario. You would use DATABASE_DEFAULT or CATALOG_DEFAULT if you needed the query (or that particular predicate or ORDER / GROUP item) to be sensitive to local database and change behavior depending on where the query is being executed.

Other notes:

I would suggested enclosing the result set column names in square brackets (i.e. [permission_order] instead of permission_order, [role_] instead of role_, etc).
You have CHAR(13) at the end which should be CHAR(10) like the rest of those newlines in that concatenation.
Even better would be to use NCHAR(10) and prefix each string literal piece of the concatenation with an upper-case N since you are concatenating sysname / NVARCHAR columns which will force the entire string into NVARCHAR, hence those CHAR() references and string literals are being implicitly converted anyway.
Also good would be to wrap the schema / object names in QUOTENAME() (and remove your explicit delimiters — [ and ] — in the string literals) as QUOTENAME has the benefit of escaping embedded delimiters.


I will be deleting approx 400 million records from this table.

Hopefully, you are doing it in chunks - to avoid bloating transaction log.

  notice that i am not see any drop in my hard drive space

You wont, as you have to explicitly shrink the database file to release space. Just deleting the records, SQL server wont release the space back to the OS.

  Since the database should not be this size. Is it still bad for me to shrink it?

It is generally a bad practice as databases should be presized properly. If you are 100% sure that your database is NOT going to be again this size, its OK for you to shrink the database (in your scenario).

  This is a production Database. If I shrink it will it cause downtime or lock the database?

Shrinking a database is a very IO intensive. Its advisable to shrink using DBCC SHRINKFILE during maintenance window or when there is minimal activity.

  In management studio you have two options, Database or Files. Given my situation what would be the best option?

You should use TSQL to do the shrink. (since you asked, shrinking FILE is the way to go instead of database). You can use shrink database in chunks - tsql script to do the shrink in chunks.

Remember that when you shrink, you are fragmenting your indexes.

  is there a rule on the percentage of free space a DB should have?

You can refer to Estimating Disk Space Requirements for Databases article. There is no fix rule, you have to take into account of how your database is expected to grow. You also have to take into account, autogrowth of your databases.

References : Why Does the Transaction Log Keep Growing or Run Out of Space?


Foreign keys - link using surrogate or natural key?

Neither SQL nor the relational model are disturbed by foreign keys that reference a natural key. In fact, referencing natural keys often dramatically improves performance. You'd be surprised how often the information you need is completely contained in a natural key; referencing that key trades a join for a wider table (and consequently reduces the number of rows you can store in one page).

By definition, the information you need is always completely contained in the natural key of every "lookup" table. (The term lookup table is informal. In the relational model, all tables are just tables. A table of US postal codes might have rows that look like this: {AK, Alaska}, {AL, Alabama}, {AZ, Arizona}, etc. Most people would call that a lookup table.)

On big systems, it's not unusual to find tables that have more than one candidate key. It's also not unusual for tables that serve one part of the enterprise to reference one candidate key, and tables that serve another part of the enterprise to reference a different candidate key.  This is one of the strengths of the relational model, and it's a part of the relational model that SQL supports pretty well.

You'll run into two problems when you reference natural keys in tables that also have a surrogate key.

First, you'll surprise people. Although I usually lobby strongly for the Principle of Least Surprise, this is one situation where I don't mind surprising people. When the problem is that developers are surprised by the logical use of foreign keys, the solution is education, not redesign.

Second, ORMs aren't generally designed around the relational model, and they sometimes embody assumptions that don't reflect best practice. (In fact, they often seem to be designed without ever having input from a database professional.) Requiring an ID number in every table is one of those assumptions.  Another one is assuming that the ORM application "owns" the database. (So it's free to create, drop, and rename tables and columns.)

I have worked on a database system that served data to hundreds of application programs written in at least two dozen languages over a period of 30 years.  That database belongs to the enterprise, not to an ORM.

A fork that introduces breaking changes should be a show-stopper.

I measured performance with both natural keys and surrogate keys at a company I used to work at. There's a tipping point at which surrogate keys begin to outperform natural keys. (Assuming no additional effort to keep natural key performance high, like partitioning, partial indexes, function-based indexes, extra tablespaces, using solid-state disks, etc.)  By my estimates for that company, they'll reach that tipping point in about 2045. In the meantime, they get better performance with natural keys.

Other relevant answers: In Database Schema Confusing


How to validate negative values in query

You could do something like this:
CASE
  LEFT
  (
    to_char (NULLIF(coalesce(0::numeric,0) -
    (coalesce(26776.94::numeric,0) -
    (coalesce(2760::numeric,0) +
   (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::VARCHAR
    ,1                -- <<<---- the '1' gets the minus sign from the string!
  )
  = '-' THEN NULL
  ELSE
    to_char (NULLIF(coalesce(0::numeric,0) -
    (coalesce(26776.94::numeric,0) -
    (coalesce(2760::numeric,0) +
    (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::VARCHAR
END AS clds

I have to say though that this has to be one of the most horrible SQL constructs that I've ever seen!
I would give serious consideration to devoting some (lots?) of effort towards an in-depth refactoring!


Extended Events replacement for Virtual file stats

Extended Events is not a replacement for DMVs; they are a replacement for SQL Trace.  Periodically querying the DMV is going to be less expensive than using an Extended Events Session.

The SQL Server storage engine always updates the file stats, whether you choose to query them or not.

Adding an Extended Event Session that captures each individual IO would introduce additional overhead for each read and write, and should only be done for debugging or troubleshooting, not performance monitoring.


Do not put that kind of data in VARCHAR or TEXT; use only VARBINARY or BLOB.  Please provide SHOW CREATE TABLE so we can discuss in more detail.

Use --hex-blob on mysqldump.

If you are "exporting" by some other means, explain.


MATCH(...) AGAINST('+tommy' IN ...)
    AND ... LIKE '%tommy -%'

That is, use FULLTEXT to do what it does best (search for words), then check the results against something more complex via LIKE or REGEXP.

Granted, it will take some intelligence in your app code to construct the suitable combination of MATCH and [R]LIKE.  But the result is fast and cover a lot more than FULLTEXT can do by itself.

Note:  The optimizer will do the MATCH first, then filter on the other stuff.


Installing command line tool with pgAdmin4

Firstly, I have no idea about pgAdmin4

Secondly, It would take a lot of time when executing a large SQL file. Please consider using pg_dump (custom format) & pg_restore to enhance your performance.

  Output a custom-format archive suitable for input into pg_restore.
  Together with the directory output format, this is the most flexible
  output format in that it allows manual selection and reordering of
  archived items during restore. This format is also compressed by
  default.

For example:

-----#### Exporting large table with custom format
pg_dump -h your_host -p 5432 -U user_name  --format custom --no-password   --encoding UTF8  --no-security-labels --no-tablespaces --table=your_schema_name.your_table_name --verbose --file your_file_name.backup --schema your_schema_name your_database_name

-----#### Importing large table with custom format
pg_restore -h your_host -p 5432 -U user_name --dbname=your_database_name --role=user_name  --format custom --no-tablespaces --schema=your_schema_name --verbose your_file_name.backup


It may seem unwieldy, but I would go with the approach of having a separate table rather than using a comma-separated string.

In addition to avoiding the parsing complexities, this allows you to do nice things like apply foreign key constraints and / or indexes to that user_id column.  It also simplifies mass updates if they are ever needed ("remove user 23 from all alerts").

I would differ from your proposed schema in that I would leave the two boolean fields on the main alerts table, as they apply whether or not there are individual sets of recipients.


Does the order for clustered index have the same constraints as non-clustered index on search?

Just think left-to-right, until you hit a gap.

For Q1, SQL server can search for ad_account_id (assuming that was in the index and you had a typo in the index definition), and then scan those with that value.

For Q2, SQL server can search for ad_account_id and group_id and then scan those.

For Q3, SQL server can seek for all columns in the WHERE clause.

So, yes, the same principals applies to a clustered index as for a non-clustered index.


Referential integrity when two FK columns need to match themselves in the same table?

You need two things:

to add an extra foreign key from Book that references Row.

to modify the foreign key from Book to Shelf so it includes RowID and becomes a composite foreign key: (RowID, ShelfID) REFERENCES Shelf (RowID, ShelfID). This is needed in order for the 2 foreign keys to be aligned. Otherwise, you may end up with a Book that references RowID = 1 and ShelfID = 33 while Shelf 33 is referencing RowID = 2.

and additionally - for the the above modification to succeed - you need to add a UNIQUE constraint on Shelf (RowID, ShelfID). Alternatively, you can modify the Shelf primary key.
All the columns will be not nullable, except Book.ShelfID. There is a way to have all columns not null but it would require an additional table.

The tables become:
        --------- dbo.Row-----------
       |                            |
     --|  RowID int   PK            |-----
    |   ----------------------------      ^
    ^                                     |
    |                                     |
    |   -------- dbo.Shelf----------      ^
    ^  |                            |--   |
  FK|  |  ShelfID int PK     UQ1    |  |  |
     --|  RowID int      FK  UQ1    |  |  ^
        ----------------------------   ^  |
                                       |  |
                                    FK1|  ^
        --------- dbo.Book----------   ^  |
       |                            |--|  |
       |  BookID int  PK            |     ^FK2
       |  RowID int      FK1 FK2    |-----|
       |  ShelfID int    FK1        |
        ----------------------------


How can I restore a single record from a MySQL backup file?

Restore is overkill.

I'd use history/change tracking within the database/application itselF: then "rollback" becomes a standard day to day business or support actions

This would be with history tables, or Slowly Changing Dimension type 2 tables, to store "old" versions of rows. Perhaps with some schedules clean-up to purge older versions


Use current_pos to switchover master. Use slave_pos for a regular replication.

current_pos - last change in GTID domain

slave_pos - last replicated change applied by SQL thread or parallel workers on the replica.

So, imagine your ordinary replica (node2) switched to a master. slave_pos not incrementing, because changes are applied directly without any replication. Then you need to reconfigure cluster and make the new master become replica again.

What you will see on node2?

SHOW global variables like '%pos%'?

you will see, that slave_pos stopped incrementing on the moment of switchover,
current_pos incrementing correctly.

In order node2 to become replica you must use current_pos, after the workload switched to a new master.

Summarizing:

slave_pos

use this option when you don't pretend to write to the replica
you don't want the transaction id part of the GTID to increment on the slave
you are adding a new slave to the cluster and would like to start replicating from the GTID.slave_pos

current_pos

use this when you do write to the slave and want to increment GTID transaction id
when a master need to become a new slave


Boolean as measure in fact table or dimension/attribute value

Here is a flexible way to store it that should support changes in requirements in the future.

Store the target in a dimension like dim_complaint_type. That way if in the future you have different types of complaints – e.g. Billing, Technical, ... – each of them can be categorized and have different targets. By applying slowly changing dimensions type 2 on the target attribute, you can also change your target from one year to another without changing the result for past complaints. You can also do "what-if" analysis by changing the target in one dimensions and see how much more/less are now on-time.
Store the response time in the fact table, as the elapsed_time (integer) or as the registration_date and closing_date as suggested in the comments. My preference goes for registration_date and closing_date with a calculated column elapsed_time which is registration_date - closing_date.

For the result, I wouldn't store it as a flag (yes/no) but as a number. If you call the column on_time and put a 1 for each complaint that meets the target, you can easily SUM on that column to have the total of on_time complaints over a period.

I see three ways of getting the result :

Not storing it, but compute it on the fly in your reporting by having the same formula everywhere to check if fact_complaint.elapsed_time is lower than or equal to dim_complaint_type.target or not. It might be dangerous if different persons use different formulas. If you have a tool with a metadata layer (e.g. OBIEE, SAP BO, ...), you can define that result measure once for all.
Create a view that joins fact_complaint and dim_complaint_type and add that result measure in the select list. It would be defined once for all. But you need to be careful with that approach as some reporting tool might not act in the same way with a table or a view. The database optimizer might also not be able to do all its magic the same way.
Actually store it in your fact table (i.e. compute it at ETL time). This is probably the best approach in term of performance. But it requires to update that column if you change the target for some past complaints.


Automatic recovery on a 3 node AlwaysOn Multisite Cluster?

The simplest answer is, no, you won't be able to facilitate this with a 3 node cluster in the manner described.

The reason is due to quorum. Assuming the 3 nodes, 2 at Primary and 1 at DR with Windows Server 2012R2. Dynamic quorum is on by default, this will automatically adjust node weights in case of a node failure. Dynamic witness is also on by default which will change the witness vote to keep the number of total votes odd.

The thing is that dynamic quorum only works if less than half of the nodes go down simultaneously. If 50% or more voting nodes go down at once there won't be enough voters left to keep quorum or for dynamic quorum to decide that this isn't a split brain scenario.

How could you potentially achieve this?

If it would be possible to put 2 nodes at the Primary site, 2 nodes at the DR site, and a witness at a 3rd site then it should do what you're looking for.

  is there any way to configure this in a way which automatic failover will occur if the primary and DR sites lose connectivity or if the primary site goes down entirely?

These look the same from the perspective of the DR site. Whether it loses connection with the servers at the primary site or whether the primary site goes down doesn't look any different. In each case they no longer can "see" the other nodes, only the local ones. This results in the race to acquire the lock on the witness. Whichever side attains the lock first, wins.

There is an additional setting in Windows Server 2012R2 called LowerQuorumPriorityNodeID which can be used to weight one side or the other when these types of situations happen.


Cannot read from secondary availability group

This sounds like you might have a long running transaction on the primary replica.  Run this query to find any transactions that have been running longer than 10 mins:

Select  B.session_id,
    A.transaction_id,
    C.transaction_begin_time,
    DATEDIFF(Second,C.transaction_begin_time,getdate()) TimeTaken_In_Seconds,
    B.HOST_NAME,
    B.program_name,
    B.login_name,b.login_time as UserLoginTime
    from sys.dm_tran_session_transactions A Join sys.dm_exec_sessions B On A.session_id=B.session_id
    Join sys.dm_tran_active_transactions C On A.transaction_id = C.transaction_id
    WHERE DATEDIFF(Second,C.transaction_begin_time,getdate())  > 600

Once you find the transaction, either complete it, or kill it.


SELECT status_list.status, COUNT(User.status)
FROM ( SELECT 0 status
       UNION ALL
       SELECT 1
       UNION ALL
       SELECT 2
       UNION ALL
       SELECT 3 ) status_list
LEFT JOIN User ON User.status = status_list.status
GROUP BY status_list.status


Why is my recursive CTE so much slower on Azure SQL?

I checked your query again and it seems you can rewrite it this way:
WITH paths AS (
    SELECT Id, ParentId, Name AS [Path] FROM Folders
    WHERE Id = @FolderId

    UNION ALL

    SELECT f.Id, f.ParentId, f.Name  + '/' + [Path] FROM Folders f
    JOIN paths on paths.ParentId = f.Id
    )
SELECT @FolderId AS Id, [Path] from paths
WHERE ParentId IS NULL


Multiple Primary-Foreign Key connections between Tables Redundant?

A foreign key can only refer to a primary or unique key of another table.  The 1-M relation you drew between Critic.Review and Review.rID is impossible.


Is it recommended to have redundant foreign key columns?

I can find no fault with adding columns to tables that meet the requirements of your system.

It may in fact make some queries much faster to have the "redundant" columns present.  Take this example, which is pretty contrived, where you want to get a list of parents, and the names of the schools their children attend.  The first example, where parents are not linked directly to schools:

SELECT p.ParentName
    , s.SchoolName
FROM dbo.Schools s
    INNER JOIN dbo.Children c on s.School_ID = c.School_ID
    INNER JOIN dbo.Parents p ON c.Parent_ID = p.Parent_ID;

Versus the query where Parents are linked to schools, even though the link is "redundant" since each parents child already has the link:

SELECT p.ParentName
    , s.SchoolName
FROM dbo.Schools s
    INNER JOIN dbo.Parents ON s.School_ID = p.School_ID

Clearly, you'd want to do the second variant because (a) it's less complicated to understand the intent, and (b) SQL Server has to do less work, which is always a good thing.


Conditions that can make a MySQL COMMIT query fail?

This depends entirely on how you setup the transaction.
If you have START TRANSACTION and COMMIT; surrounding a series of INSERT, UPDATE, and DELETE queries, there are some commands that trigger an implicit commit:

ALTER TABLE
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
BEGIN (can break another BEGIN or START TRANSACTION)
START TRANSACTION (can break another START TRANSACTION or BEGIN)

I have mentioned this before in the DBA StackExchange

Mar 06, 2018 : Can you rollback a query in state 'committing alter table to storage engine'
Aug 21, 2015 : Transactional DDL workflow for MySQL
Feb 12, 2014 : row locking within ACID transaction innodb
Mar 15, 2013 : MySQL backup InnoDB

You can see the latest list of commands that trigger implicit commits in the MySQL 5.7 Docs. Why consider implicit commits ? Data may commit out of context with other data too soon or too late.
Aside from these commands, terminating a DB Connection will implicitly rollback a transaction. You should monitor the status variables Aborted_connects and Aborted_clients to see if DB Connections are failing on entry or in session.


Define a column as MD5 sum of other columns in MySql

if you are talking of column definition as in create table, there isn't a data type for hash.

However, it's best to use binary(20) and unhex function to convert your sha-1 into a binary data type because it saves a lot of space.

create table user(
id int primary key auto_increment not null,
username varchar(50),
password binary(20)
);

insert into user(username) values('ndefontenay');

update user set
password = unhex(sha1(concat(last_insert_id(),username)))
where id = last_insert_id();

select upper(sha1(concat(1,'ndefontenay'))) as "on insert",hex(password) as "on retrieve"
from user
where id = 1;


How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?

You only need a single INSERT and a single UPDATE for the main table. The whole default detection and populating of the "variables" (columns) can be done while inserting the data:
INSERT INTO main_tags (id, tags, variablea, variableb)
select id, tags, var_a,
       case
          when var_b is null and 'valA' = any(tags)  then 'valB_default'
          when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
          else var_b
       end as var_b
from (
  SELECT id, tags,
         tags[array_position(tags, 'keyA') + 1] as var_a,
         tags[array_position(tags, 'keyB') + 1] as var_b
  FROM (
    SELECT id, string_to_array(regexp_replace(tags,'[{}"]','','g'), ',') as tags
    FROM main
  ) as b
) x
WHERE tags && array['keyA','keyB'];

The final update can also handle the "unknown" value directly, no need to run three updates:
UPDATE main e
  SET variableA = coalesce(et.variableA, 'unknown'),
      variableB = coalesce(et.variableB, 'unknown')
FROM main_tags et
WHERE e.id = et.id;

One way to speed that up, is to create an index on main_tags (id)

You can actually get rid of the temp table completely and do everything in a single statement. This might or might not be faster, but I think it's worth trying:
UPDATE main e
  SET variableA = coalesce(et.var_a, 'unknown'),
      variableB = coalesce(et.var_b, 'unknown')
FROM (
  select id, tags,
         var_a,
         case
            when var_b is null and 'valA' = any(tags)  then 'valB_default'
            when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
            else var_b
         end as var_b
  from (
    SELECT id,
           tags,
           tags[array_position(tags, 'keyA') + 1] as var_a,
           tags[array_position(tags, 'keyB') + 1] as var_b
    FROM (
      SELECT id, string_to_array(regexp_replace(tags,'[{}"]','','g'), ',') as tags
      FROM main
    ) as b
  ) x
  WHERE tags && array['keyA','keyB']
) as et
WHERE e.id = et.id;


oracle rman connecting to a database in not mount state

To connect to RMAN from the operating system command line and hide authentication information, you must first start RMAN and then perform either of the following actions:

Run the CONNECT commands at the RMAN prompt. ...
Run a command file at the RMAN prompt that contains the connection
information.


Retrieve All MySQL/MariaDB Table Definition From .frm Files

A 'partial' Answer...

The .frm contains, in an encoded format, the CREATE TABLE for the one table.

Copying a .frm into your copy of MySQL would, in theory, give you the schema definition.  But, other info is needed.  So, now the challenge is to try to re-establish a table where the data is lost, but the schema is still in the .frm.  MyISAM depends on the existence of a .MYD (.MYI can be reconstructed); InnoDB has some hooks in ibdata1, but these may be reconstructable.  (Hence, this Answer is only 'partial'.)

.frm files are probably binary compatible between OS versions, even big-endian vs little-endian.  And across large swaths of MySQL/MariaDB versions.  A table with a JSON column would not be backward compatible.  There have been only a few other incompatible changes in the past decade.

As for hacking into your system.  Which is easier to get into -- The filesystem, or MySQL?  The .frm file is somewhat protected by directory and file permissions.  MySQL is somewhat protected by its password mechanism.  Obviously, you should do whatever you can to prevent access to both.

A common entry point into MySQL is via web arguments (<form>s in HTML) where the values are not properly escaped before INSERTing text into the database.  There are different solutions to that entrypoint, depending on which API is used.  (I've seem a hacker post /etc/passwd on the Internet; he got it from a PHP program that failed to escape simple data entry fields.)  See "SQL Injection".

Is your schema the most valuable thing?  I would expect the data to be more important.  And also the root password for your server.


MySQL InnoDB or MySQL NDB Cluster which one to use

NDB is more complex to set up, but may provide a faster way to get to 13K qps sharded across multiple machines.


How to make a function from a concrete SELECT statement in Oracle SQL?

If I undferstand correctly, all you need is a bind variable.

SQL> variable sw number
SQL> exec :sw := 10;

PL/SQL procedure successfully completed.

WITH t AS (SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
   res AS (SELECT ID,
                  in_node,
                  out_node,
                  weight,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
                  CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                       ELSE 'out'
                  END direction_from_orig_node,
                  LEAST(in_node, out_node) node1,
                  GREATEST(in_node, out_node) node2,
                  row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   t
           START WITH ID = :sw
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                              OR PRIOR in_node IN (in_node, out_node))
                              AND LEVEL <= 2)
SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
FROM   res
WHERE  rn = 1
AND    ID != orig_id
GROUP BY orig_id;

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
        10          3         15          2         27

Then change the value of the variable to whatever you need:

SQL> exec :sw := 9;

PL/SQL procedure successfully completed.

SQL> /

no rows selected

SQL> exec :sw := 8;

PL/SQL procedure successfully completed.

SQL> /

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         8          1         12          1         15

SQL> exec :sw := 3;

PL/SQL procedure successfully completed.

SQL> /

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         3          1         33          3         30


SQL Cluster Resources Disappear After Removing Disk Resource

The link below has the likely answer.  Premier Support agrees that the steps to fix should fix my similar issue.  I plan on going through the steps during my next outage window.

http://blogs.technet.com/b/askcore/archive/2013/01/14/removing-a-mount-point-disk-from-a-cluster-group.aspx


Restoring SQL Server 2000 database to SQL Server 2008

No. In fact you can't restore the system databases from a SQL Server 2000 system to a SQL Server 2008 instance. If you did parts of (or the entire) database engine won't work as the SQL Server 2000 system databases are formatted very differently from SQL Server 2008.


Representing a Many-to-Many Relationship Between Students and Departments

In Sql not allowed duplicate data in Unique key.
but you can create combination of two column of unique key like
CONSTRAINT UC_Person UNIQUE (ID, StudentId)
This will solve your problem.

CREATE TABLE student
(
StudentId INT IDENTITY PRIMARY KEY,
Name VARCHAR(255),
DepartmentId VARCHAR(255),
);
INSERT INTO student VALUES ('nav',1);
INSERT INTO student VALUES ('Angra',2);
INSERT INTO student VALUES ('navjot',3);
select * from student
select * from department
drop table student
drop table department

CREATE TABLE department
(
ID INT PRIMARY KEY,
DeptName VARCHAR(255),
studentId int foreign key references Student(StudentId)
**CONSTRAINT UC_Person UNIQUE (ID,StudentId)**
);

INSERT INTO dbo.department VALUES (101, 'CSE', 1);
INSERT INTO dbo.department VALUES (102, 'Mech', 2);
INSERT INTO dbo.department VALUES (103, 'Civil', 1);


Enabling Edition Based Redefinition failed with ora-33819. Could I query why?

You can query something like this:

select o.owner, o.object_name, o.object_type, edition_name, editionable from dba_dependencies d, dba_objects o
where o.owner = d.referenced_owner
and o.object_name = d.referenced_name
and d.owner = 'USER'
and o.editionable = 'Y'
and o.edition_name is NULL;

This is will list the objects referenced by user 'USER', that are editionable but dont have any editions - thus preventing you from enabling EBR for 'USER'.


Why scan clustered index instead seek nonclustered index?

The key idea here is that your index contains (firstname,id), but not surname.  So the options for this query

SELECT firstname,
       surname
FROM   users
WHERE  firstname = 'John';

are

1) Scan the clustered index

2) Seek the non-clustered index, and then for every matching row in the index, Seek on the Clustered Index to find the surname.  It's this "bookmark lookup" that is the most expensive part of the query, and if a reasonable percentage of your users are named 'John', it may well be cheaper just to scan the clustered index.

This is why we have indexes with included columns.  You can add surname to the index to enable this query to seek on the non-clustered index, and avoid the bookmark lookup.  The index would then be a "covering index" for the query.  eg

CREATE NONCLUSTERED INDEX ix_users_firstname
  ON users (firstname)
   include (surname);


How do I get the aggregate of a window function in Postgres?

You can have window functions on the result of aggregate functions in a single query level.
This would all work nicely after a few modifications - except that it fails for the standard deviation on mathematical principal. The involved calculations are not linear, so you cannot simply combine standard deviations of sub-populations.
SELECT perm
      ,combo
      ,avg(value)                 AS perm_average_value
      ,sum(avg(value) * count(*)) OVER w_combo /
       sum(count(*)) OVER w_combo AS combo_average_value
      ,stddev_pop(value)          AS perm_stddev
      ,0                          AS combo_stddev  -- doesn't work!
      ,count(*)                   AS perm_count
      ,sum(count(*)) OVER w_combo AS combo_count
FROM   foo
GROUP  BY perm, combo
WINDOW w_combo  AS (PARTITION BY combo);

For combo_average_value you would need this expression
sum(avg(value) * count(*)) OVER w_combo / sum(count(*)) OVER w_combo

Since you need a weighted average. (The average of a group with 10 members weighs more than the average of a group with just 2 members!)
This works:
SELECT DISTINCT ON (perm, combo)
       perm
      ,combo
      ,avg(value)        OVER wpc AS perm_average_value
      ,avg(value)        OVER wc  AS combo_average_value
      ,stddev_pop(value) OVER wpc AS perm_stddev
      ,stddev_pop(value) OVER wc  AS combo_stddev
      ,count(*)          OVER wpc AS perm_count
      ,count(*)          OVER wc  AS combo_count
FROM   foo
WINDOW wc  AS (PARTITION BY combo)
      ,wpc AS (PARTITION BY perm, combo);

I am using two different windows here, and reduce the rows with DISTINCT which is applied even after window functions.
But I seriously doubt it will be faster than your original query. I am pretty sure it isn't.
Better performance with altered table layout
Arrays have an overhead of 24 bytes (slight variations depending on type). Also, you seem to have quite a few items per array and many repetitions. For a huge table like yours it would pay to normalize the schema. Example layout:
CREATE TABLE combo (
  combo_id serial PRIMARY KEY
 ,combo    int[] NOT NULL
);

CREATE TABLE perm (
  perm_id  serial PRIMARY KEY
 ,perm     int[] NOT NULL
);

CREATE TABLE value (
  perm_id  int REFERENCES perm(perm_id)
 ,combo_id int REFERENCES combo(combo_id)
 ,value numeric NOT NULL DEFAULT 0
);

If you don't need referential integrity you can omit the foreign key constraints.
The connection to combo_id could also be placed in the table perm, but in this scenario I would store it (slightly de-normalized) in value for better performance.
This would result in a row size of 32 bytes (tuple header + padding: 24 bytes, 2 x int (8 byte), no padding), plus the unknown size of your numeric column. (If you don't need extreme precision, a double precision or even a real column might do, too.)
More on physical storage in this related answer on SO or here:
Configuring PostgreSQL for read performance
Anyway, that's only a fraction of what you have now and would make your query a lot faster by size alone. Grouping and sorting on simple integers is also a lot faster.
You would first aggregate in a subquery and then join to perm and combo for best performance.


How do you make manual/one-off log-based queries?

In GCP it'll be a two step process:

You should be able to create Log Filters:

https://cloud.google.com/logging/docs/view/advanced-filters

And then use those log filters to create metrics:

https://cloud.google.com/logging/docs/logs-based-metrics/counter-metrics

This won't be as flexible or seamless as a SQL query, but the filters should provide you with a way to select the logs you're interested in, and the metrics should allow you to count them.

If you are keen on using SQL, you will need to export your log data to Big Query.  You can do this manually when you need to, or you can set up sinks to automatically send new logs that match criteria to Big Query.  Once the data is exported, you would be using the Big Query service to run SQL on the logs.

Here is a link on exporting log data to Big Query:

https://cloud.google.com/logging/docs/export/


Step by step establish a connection between SQL Server 2014 and Oracle 12c

Partial answer (select SQL Server tables from Oracle):

On the Oracle side, you can create a database link to view data in SQL Server.  Since this is a 5 step process, I've also included some debug steps that I use when diagnosing db links from SQL Server in order to track down where the failure is.

On the Oracle machine:

1) Set up an ODBC connection to SQL Server

start => control panel => Administrative tools => Data Sources (ODBC) => System DSN => Add => SQL Server)

Debug: Press the Test Connection button from the ODBC connection window

2) Create an HS file that points to the ODBC connection

In %oracle_home%\hs\admin, create a new file called init<name>.ora.  A typical HS file usually just contains:

HS_FDS_CONNECT_INFO = <name of the ODBC connection name>
HS_FDS_TRACE_LEVEL = OFF

Debug: change HS_FDS_TRACE_LEVEL = ON, then check for the appropriate trace files in %oracle_home%\hs\trace

3) Add an entry to your listener.ora

If there is already a SID_DESC present in SID_LIST_LISTENER you can add a new one just below it.

SID_LIST_LISTENER =
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=...)
      (ORACLE_HOME=...)
      (PROGRAM=...)
    )
    (SID_DESC=
      (SID_NAME=<name of the ODBC connection>)
      (ORACLE_HOME=<full path for oracleHome>)
      (PROGRAM=dg4odbc)
    )
  )

Debug: Check the listener log

c:> lsnrctl reload

4) Add a TNSEntry

<Name> =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=<Host>)(PORT=<port>))
      (CONNECT_DATA= (SID=<ODBC connection name>))
      (HS=OK)
   )

Debug c:\> tnsping <tns entry name>

5) Create the db link

create database link <name> connect to DUMMY identified by " " using '<tns entry name>';

Debug: SQL> select * from dual@<dblinkname>;

pre 10g, we used to be able to simply create database link <name> using '<tns entry>'; but the connect as identified by section became required starting in 11g even though whatever you enter isn't used (since it uses the login credentials for SQL Server in the ODBC connection).

It is a little old, but I found the following tutorial very helpful when I was learning this process: Installing and configuring Oracle Heterogeneous Services for SQLServer.

Once you have the database link working, on the Oracle side you could then create views/synonyms/materialized views (complete refresh only) over the SQL Server tables for easier access.

As for the other way around (how to view data in Oracle from SQL Server), i'm not sure since i'm exclusively an Oracle DBA...


You can install SSIS on a dedicated server. It will provide the capability of designing, executing and storing the packages. Packages would be accessible from agent service installed on other server as in your case. I see no issue in this design as per your requirement.
However this design will not use resources on dedicated SSIS server. All execution load will be on calling server with database engine.


How to `vacuumlo` an RDS PostgreSQL database?

Find here (in the GitHub mirror of the official PostgreSQL repo) sources of vacuumlo for the PostgreSQL data base version I was using at that very moment.

You can imagine the rest: I just mimic what the program is performing, also simply described here.

1. Temporary table construction.

Prepare the temporary table of object references or OIDs.

1.1. Temporary lob table as copy of full lob table.

=> SET search_path = pg_catalog;
[...]
=> CREATE TABLE vacuum_lo_removeme AS \
        SELECT oid AS lo FROM pg_largeobject_metadata;
[...]
=> ANALYZE vacuum_lo_removeme;
[...]
=> _

1.2. Limit temporary lob table.

Perform the query that returns you all your data base columns typed OID:

=> SELECT
     s.nspname, c.relname, a.attname FROM pg_class c, pg_attribute a
     , pg_namespace s, pg_type t
   WHERE
     a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = c.oid
     AND a.atttypid = t.oid AND c.relnamespace = s.oid
     AND t.typname in ('oid', 'lo') AND c.relkind in ('r','m')
     AND s.nspname !~ '^pg_';

Next you have to execute this query for all results fetched by the earlier query (note ${VARIABLE} is something you should substitute yourself according to your mileage) in order to remove from the temporary table all OIDs of objects actually in use:

=> DELETE FROM vacuum_lo_removeme WHERE \
        lo IN (SELECT ${column} FROM ${SCHEMA}.${TABLE});

In my case it was only two tables totallying five columns, and actually both tables empty, so naturally the five DELETE queries did not do a thing. If you have a bigger OID enabled subschema you might need to automate this somehow.

2. Large objects unlinking.

Finally the program declares a cursor that iterates the remaining lo cells of the temporary table, purging them with a lo_unlink function call.

2.A. Do not do it this way.

I should have automated that with a PLPGSQL stored procedure, but since I suck at that kind of tasks, I just issued this one liner:

$ echo 'SELECT COUNT(*) FROM vacuum_lo_removeme;' | \
        $MY_AUTOMATABLE_PSQL_MILEAGE
  count
---------
 1117233
(1 row)

Then this other that iterates selecting the first orphan OID in the temporary table of orphan OIDs then unlinking it and removing it from the table:

$ for i in {1..1117000}; do \
        export oid=$(echo 'SELECT * FROM vacuum_lo_removeme LIMIT 1' | \
        $MY_AUTOMATABLE_PSQL_MILEAGE | grep -v 'lo\|\-\-\-\-\|row\|^$' | \
        sed s/\ //g) && \
        echo "SELECT lo_unlink($oid); \
              DELETE FROM vacuum_lo_removeme WHERE lo = $oid" | \
              $MY_AUTOMATABLE_PSQL_MILEAGE; \
        done
 lo_unlink
-----------
         1
(1 row)

DELETE 1
 lo_unlink
-----------
         1
(1 row)

DELETE 1
 lo_unlink
-----------
         1
(1 row)

DELETE 1
 lo_unlink
-----------
         1
(1 row)

DELETE 1
ERROR:  must be owner of large object 18448
DELETE 1
ERROR:  must be owner of large object 18449
DELETE 1
ERROR:  must be owner of large object 18450
DELETE 1
ERROR:  must be owner of large object 18451
[...]
 lo_unlink
-----------
         1
(1 row)

DELETE 1
[...]

I knew it was suboptimized as hell, but I let it slowly removing those orphan records. When not being DBA at all those one liners can be easier to forge than working some meaningful idiomatic PLPGSQL.

But this was too slow to leave it like this.

2.B. Do this better than 2.A (though not a silver bullet yet).

You will be able to speed up large object unlinking with something simple such as:

=> CREATE OR REPLACE FUNCTION unlink_orphan_los() RETURNS VOID AS $$
DECLARE
  iterator integer := 0;
  largeoid OID;
  myportal CURSOR FOR SELECT lo FROM vacuum_lo_removeme;
BEGIN
  OPEN myportal;
  LOOP
    FETCH myportal INTO largeoid;
    EXIT WHEN NOT FOUND;
    PERFORM lo_unlink(largeoid);
    DELETE FROM vacuum_lo_removeme WHERE lo = largeoid;
    iterator := iterator + 1;
    RAISE NOTICE '(%) removed lo %?', iterator, largeoid;
    IF iterator = 100 THEN EXIT; END IF;
  END LOOP;
END;$$LANGUAGE plpgsql;

NOTE it is not required to unlink large objects 100 at a time, not even a particular number x of them at a time, but unlinking 100 at a time is the safest base applicable to all AWS instance sizes' default memory configuration. If you use an excessively large number for this, you risk function failure because of insufficiently assigned memory; how you can do will depend on the amount of objects to unlink and their size, on instance type and size, and on degree of manual further configuration applied.

Which is somewhat easy to forge for a non DBA person, then calling it with something like

$ for i in {0..$WHATEVER}; do echo 'SELECT unlink_orphan_los()' | \
        $YOUR_AUTOMATABLE_PSQL_MILEAGE

Where ${WHATEVER} is a constructed constant depending on the temporary large objects' table size and the number of locks per transaction your configuration is allowing (I am using RDS defaults but iterating from bash I guess I don't even have to get to know which is the largest number of lo_unlinks the RDBMS is allowing with current max_locks_per_transaction.

3. VACUUM large object tables.

Hinted by this thread in the postgres mailing list I understood I should VACUUM pg_largeobject after unlinking the objects.

I am not sure which of these are the minimum required set, or the proper time for their execution, but some of them might be of help, and none should cause any damage: I ran VACUUM ANALYZE VERBOSE pg_largeobject_metadata; VACUUM ANALYZE VERBOSE pg_largeobject; VACUUM FULL ANALYZE VERBOSE pg_largeobject_metadata; VACUUM FULL ANALYZE VERBOSE pg_largeobject; several times while the micro instance was unlinking the objects (took a really large time, alas), firstly when about 1/4 objects already got unlinked, and some little storage was given back to OS, secondly when about 1/3 objects already got unlinked, and some another little storage was given back to OS, thirdly when about 3/5 objects already got unlinked the instance underwent a massive storage giveback to OS:

That massive storage giveback was what I was looking for. After running the query for largest tables found at the official home page, with less than 3/4 total objects unlinked, the objects table got shrunk to less than 3GiB, far from the initial and even more bloated 20GiB.

NOTE automating fast VACUUM ANALYZE iteration on tables has the same long term effects (of cleaning the table from using excess disk storage) as executing a single VACUUM FULL, but without acquiring an exclusive lock.


You have an inner join with a condition there:

WITH fktable1 (id) AS (
  values (2)
)
,fktable2 (id) AS (
  values (3)
)
SELECT main.*
FROM (
  VALUES
    (1, NULL, NULL),
    (2, 2, NULL),
    (3, NULL, 3),
    (4, 2, 3),
    (5, 4, NULL),
    (6, NULL, 5)
) AS main (col1, fkcol1, fkcol2)
  join fktable1 f1 on f1.id = main.fkcol1 or main.fkcol1 is null
  join fktable2 f2 on f2.id = main.fkcol2 or main.fkcol2 is null


Normalizing data past initial db design phase

Normalization exists to remove update anomalies. If a single fact changes in the real world, a single column of a single row should change in the model. In this way one source of inconsistency is eliminated. Pragmatically, normalization can be eased if

there are no updates
all the developers understand the situation and are competent to deal with it
there are good QA processes in place
inconsistencies are of little cost and easily fixed

.. amongst other considerations.

So, should you have normalized this particular table? In best DBA tradition I'll say "it depends."

Likely having a denormalized table causes problems right now. It may be the code is more complex, or silly errors slip in, or queries run slower or .. whatever. But there is a price and you know what it is. There is a risk that future events due to having a denormalized schema also will incur some cost. Maybe the wrong coupon is applied or too many rows get updated and revenue is lost. You know your business; you know what may go wrong and its severity.

Changing the schema and application will have a cost - direct effort and opportunity cost on features not implemented, releases delayed etc.

I'd say, if the discounted future cost of the current situation is greater than the implementation costs then do it. If not, add this item to the pile labeled "technical debt", monitor the situation, and get on with other stuff.


PostgreSQL ignoring (unique) index on TEXT field

ypercubeᵀᴹ and a_horse_with_no_name were right:

I had too few rows in the table. For just 256 rows an index gives no benefit at all.

I added 100,000 extra rows and EXPLAIN started showing usage of the index.


temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

I hope I can answer this adequately because I hate temp tables that use InnoDB. Here is why

In this diagram, the data dictionary has that temp table as an entry. Once the temp table is done being used for ALTER TABLE, the data dictionary entry should disappear.
Here are your questions and my answer for them ...

1.. When alter has been successfully done then why this temp table still exist and not deleted.

Under normal circumstances, when a DB Connection terminates, all open temp tables are supposed to be dropped. The only reason a temp table will linger after an ALTER TABLE is done is if the DB Connection running the ALTER TABLE terminates before the temp table can be dropped. It is possible that the rollback segments (See Diagram) has the original info of the table before the ALTER TABLE was issued.
Since you mentioned this as happening on the Slave, the only suspect (or person of interest) for this problem would be the SQL thread for MySQL Replication. Why? It is the owner of the temp tables need to complete the SQL being executed from the relay logs. If you kill a SQL thread in a manner other than STOP SLAVE; while it is performing an ALTER TABLE, it may not drop temp tables in an orderly manner. Thus, you will have a lingering temp table regardless of the storage engine.

2.. Now how can I delete this temp table and what will be its impact.

You could go ahead and delete the .frm and .ibd. I only say this because it is temp table. The table should have disappeared. The impact ? The data dictionary will still have the temp table registered. Just a few bytes get wasted.
Don't worry about the temp table name being used again. Look at the name of the temp table
#sql-7a87_230c32.ibd

There are 10 hexadecimal digits. The chances of reusing that temp table name is 1 in 1610 or 240 which 1,099,511,627,776. I think you will be OK in this.
Don't delete them just yet. Look at the your third question ...

3.. May I read this temp file or know by any command that for which table this temp table was created.

I understand the need for this question. Evidently, you want to know what the table was that was having the ALTER TABLE done. Unfortunately, you cannot read any temp table through standard SQL.
However, you could probably rename the .frm and use a utility to read the table structure.
Here is an old post of mine to do this with a .frm in the Windows environment : How can extract the table schema from just the .frm file?
EPILOGUE
Even though the temp table no longer has a useless purpose at this point, deleting the InnoDB temp table there is that 1 in trillion chance that InnoDB be behave erratically.
Your last resort when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow
UPDATE 2014-06-10 08:36 EDT
It should be evident from the file timestamps and the commentary in your second update that a shutdown must have taken place in the middle of an ALTER TABLE. All transactional information for MVCC regarding the InnoDB table was held in a state of suspended animation in one of the following places:

Transaction Logs (a.k.a. Redo Logs, which are the files ib_logfile0 and ib_logfile1),
Rollback Segments & Undo Space (located in the system tablespace file ibdata1)
Double Write Buffer (located in the system tablespace file ibdata1)

When you started mysql back up, the MVCC for the table was replayed to bring the original table into a consistent state. That may result in either the table being rolled back to the original state before the ALTER TABLE or if everything passed through the Redo Logs, the table now existing in the new design. The fact that the .ibd file has a later timestamp show that MVCC must have been applied to it on the startup of mysql (which includes crash recovery).
Once the shutdown happened, the InnoDB table lost its identity as a temp table and became a regular table in the eyes of mysqld. Thus, you should be able to delete the temp files with impunity.


Affect of Shrinking Database before Transaction log backup

Your maintenance plan should not contain any shrink tasks anyway. These cause fragmentation by moving pages around the file and likely the database will just need to grow back to its original size shortly anyway.

But this isn't causing your error.

  BACKUP LOG cannot be performed because there is no current database
  backup.

The transaction logs would need to be restored after a compatible backup has been restored. This is telling you that there is no such backup as one has never been taken since the database has been in full or bulk logged recovery mode.

It doesn't look like step 1 was run successfully for that database, or possibly someone temporarily changed the recovery mode for that database to simple and broke the log chain so you must take another full backup.


designing database where users can have multiple positions

I would suggest that instead of having an explicit office-project relationship that you set up an employee-project relationship and allow the existing office-employee relationship to take care of this problem naturally.

From your question, it seems as though many employees can be associated with a single project and a single employee can be associated with many projects. In this case, you'll want a linking table of the form:

    employee_project
    -------------------
    employee_id | project_id

In the event that the relationship is more complex- say, that an employee participates in a project via one of their roles, you may want to move the relationship to refer to that point, for example:

    project_employee_role
    -------------------
    employee_role_id | project_id

if there is a surrogate employee_role_id

or

   project_employee_role
   -------------------
   employee_id | role_id | project_id

If not.


How to wrap long lines when SELECTing SQL text columns?

If you're using the psql command line tool, issue this command first:

\pset format wrapped

It should then wrap long lines to your terminal window like so:

test_id |              text
--------+-----------------------------
      1 | Lorem ipsum dolor sit amet,.
        |.consectetur adipiscing elit.
        |.. Mauris lorem

You can also set the number of columns to wrap to with

\pset columns 100

and you can change the dots to ellipses with

\pset linestyle unicode

More info: http://www.postgresql.org/docs/current/static/app-psql.html


I'm not sure what it is you are trying to do, the GROUP BY clause that you provided is invalid. Is the following what you meant?

SELECT coalesce (OrderID, 'GrandTotal') AS OrderID,
       SUM(OrderPrice) as SumOrderPrice
FROM ORDERS
WHERE OrderDate BETWEEN '2018-04-01' AND '2018-04-30'
GROUP BY OrderID WITH ROLLUP;

I.e. GROUP BY OrderID instead of OrderPrice?


How to create a table in access and automatically fills it with information from a query?

Sounds likes you would be best served by using another Query, not a Table.  In Access you can use a Query much like a Table, even use a saved Query as a source in another Query.  If you are careful you can create an Editable Query, in which changes you make to the Query results directly affect the Base Table.  It can be tricky to do so when you have more then one Table/Query involved, as it sounds like you do, but it is a possibility.

You can read more about Editable Queries here


Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question.

  other queries using a PK of table (let say id_foo)

That could be anything. It depends on what you have and what you query exactly. Querying a single row is not affected either way, but multiple rows might be.

Be aware that CLUSTER rewrites the table in pristine condition like VACUUM FULL does (removes dead tuples, compacts the physical size of the table, rewrites indexes) So you might see an immediate positive effect on read performance independent of the sort order. (Much like you would get with VACUUM FULL.)
After CLUSTER, you may want to run a plain VACUUM on the table to update the visibility map, too - which may allow index-only scans.

All benefits of CLUSTER shrink with the write frequency.

Also, if you have many updates to the table, CLUSTER can actually hurt write performance by removing "wiggle room" for HOT updates on the same data page. You might be able to counter that effect with a FILLFACTOR setting below 100. Again, depends on locality of updated rows, etc.

Related:

Index optimization with dates

Either way, I would probably not index and cluster on my_timestamp::date, but on my_timestamp directly. Nothing lost, something gained. The cast is very cheap, but it's still cheaper not to cast at all. And the index can support more queries.

CREATE INDEX foo_my_timestamp_idx ON foo (my_timestamp);

Even though a date occupies only 4 bytes on disk and a timestamp occupies 8 bytes, the difference is typically lost to alignment padding for your case, and both indexes have exactly the same size.

The order of multiple rows on the same day resulting from your expression index is arbitrary. There can still be two identical timestamps, but with 6 fractional digits that's normally very unlikely. Aside from that you get a deterministic order of rows, which can have various advantages.

I also dropped the DESC key word since Postgres can read indexes backwards virtually as fast a forwards. (Sort order matters for multicolumn indexes, though!) More:

Optimizing queries on a range of timestamps (two columns)

Instead of:

SELECT * FROM foo
WHERE my_timestamp::date = '2016-07-25';

You would now use:

SELECT * FROM foo
WHERE  my_timestamp >= '2016-07-25'  -- this is a timestamp literal now
WHERE  my_timestamp <  '2016-07-26';

Same performance.

If you don't need the time component of the column at all, convert the column to date ...

How to roll back CLUSTER?

CLUSTER on a single table can be rolled back with ROLLBACK like any other regular command as long as the transaction has not been committed.

However, I quote the manual:

  CLUSTER without any parameter reclusters all the previously-clustered
  tables in the current database that the calling user owns, or all such
  tables if called by a superuser. This form of CLUSTER cannot be
  executed inside a transaction block.

You can always run CLUSTER with a different index to change the physical order of rows once more.


Why does SQL developer suggest a different format for a procedure that works?

On the subject of why it would suggest a different format when the code already works, of course it's possible to have poorly formatted code that works, so in principle there is no reason why you shouldn't consider layout or refactoring improvements that might make the code more robust, efficient, or easier to maintain.
However, double-quoted names are poor practice because they hide naming errors and force you to specify upper/lowercase, and the result is less readable than the original. They are really a portability feature for use with third party applications that have weird table names. Code generators tend to slap double quotes around everything because it's easier to do that than to detect whether they are actually needed in each case. There is no way that
from "C##ELLIE"."CPP" "A1"

is any sort of improvement on
from cpp

except perhaps for the use of a table alias. It's a good idea to give the table an alias (without double-quotes!) and use it when referring to columns. But "A1" (or even a1) is the kind of table alias that only a computer would dream up. It would be far better to use an alias that is some kind of abbreviation of the table name, perhaps in this case c. Imagine if you were joining six tables - now which one is a3? Oh right, that's ORDER_DETAILS. (Though in this case the table name is so short that there is no point aliasing it.) So,
SELECT "A1"."CPP" "CPP"
FROM   "C##ELLIE"."CPP" "A1"

would be far better as just
SELECT c.cpp
FROM   cpp c

(I'd also lowercase it because this isn't 1974 and my editor highlights language keywords using colours and bold, but we'll let that go.)
Hardcoding schema names is poor practice because it's at best redundant (the object is in the schema you are already working in so it adds nothing except needless complication) or worse, it limits portability (if you ever rename the schema or move the code you'll have to go through it cleaning up the hardcoded references).
I'm sure this is a clever feature that means well, but in this instance it's not good advice.


Function quote_nullable(timestamp without time zone) is not unique

Works as is in my installation (Postgres 9.3).

You probably can fix this by casting to text.

SELECT quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy')::text);

And do not cast the result to date. It  would be pointless to call quote_nullable() if you try to cast the result back to date. It would also fail for NULL input.

Postgres allows function overloading. My installation has one instance of quote_nullable() taking text and another one taking anyelement (polymorphic type). The latter takes care of the date.

As for timestamp without time zone: Postgres first looks for an exact match (date in your case), then considers other options like types that can be cast implicitly or polymorphic types. The manual has the exact procedure for Function Type Resolution.

Not sure about EnterpriseDB. Run this to diagnose:

SELECT n.nspname AS schema_name
      ,p.proname AS function_name
      ,pg_get_function_arguments(p.oid) AS args
      ,pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  proname = 'quote_nullable';

More about function overloading in this related answer on SO:
ERROR: function addgeometrycolumn is not unique


Is there a standard way to use common data and procedures across multiple databases?

In addition to Thomas' answer, for the things like common lookup data and generic functions, you can use synonyms.

USE CentralDB;
GO

CREATE TABLE dbo.Dates(...);
GO

USE OtherDB;
GO

CREATE SYNONYM dbo.Dates
  FOR CentralDB.dbo.Dates;

Now your other databases can treat these like first-class, local objects, but you only need to maintain one copy. Note that some functionality is missing, e.g. you can't set up foreign keys.

In a previous life I managed a system where we would have ~500 databases with nearly identical schema on each instance. We managed common data using synonyms, and deployment for code that had to be local using Red-Gate SQL Compare an SQL Farms Combine (very much like Multi-Script).


Is it possible to emulate the WITH command in MySQL 5.1.73?

Yes, it is possible to return both results without repeating the subquery by rewriting your query using an outer join. The following will return same results as your version:

SELECT
    COUNT(*) AS `count`
FROM
    agreements AS ag
LEFT JOIN
    (
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) < DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
    ) AS s ON ag.driv_id = s.driv_id AND ag.veh_id = s.veh_id
WHERE
    ag.start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    ag.start_date < STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    ag.active = 1
    AND
    s.driv_id IS NULL
;

That would effectively be your not_in_sum. In order to return both counts at once, you just remove the NULL check from the where clause and use COUNT(s.driv_id) in SELECT like this:

SELECT
    COUNT(s.driv_id) AS in_sum,
    COUNT(*) - COUNT(s.driv_id) AS not_in_sum
FROM
    ...

If the subquery can return duplicates, add DISTINCT to it, otherwise in_sum may return an incorrect result.

As for the WITH clause, there is no mention of it even in the manual for the not yet officially released version 5.7, but using a temporary table to emulate it would probably make sense. The error you got while trying that method was because the right side of IN/NOT IN in your attempts was just a table name whereas it should have been a subquery:

(a.driv_id, a.veh_id) IN (SELECT driv_id, veh_id FROM tuples)


Some notes:

Why is there no PRIMARY KEY in the table?
Why date is of type int and not date or datetime or timestamp?
Why is it called date when it stores date and time?

Regarding the actual question, the efficiency of the query, an index on (tindex, tn, col, date) would help in my opinion much more than the other suggestions. And since you have no primary key and the (tindex, tn, col) is unique (you have added another unique index), my suggestion is:

(optionally) drop that unique index)
define the primary key as (tindex, tn, col):

ALTER TABLE mytable
  DROP INDEX __the_name_of_the_unique_index,  -- this is optional
  ADD PRIMARY KEY (tindex, tn, col) ;

This will take some time (that's why I suggest you do both operations in one pass.)

Then you can measure your query and all the suggested rewritings of the query (and check if the primary index is used).

Why is this a better index?

Because the tindex column is the first column in the index, all the rows with tindex='anticorrosive' will be in consecutive pages in the index (and all the rows with tindex='corrosive' will be in another part of index but still in consecutive pages.) So, reading (scanning) these 2 parts of the index will be much faster than scanning the whole table or scanning the whole index twice (which is what mysql does essentially with the (tn, col, tindex) index.)

Another benefit of making this index the primary key is that you get rid of the (hidden) 6-byte column that InnoDB added and has been using as the clustered index of the table (since you did not provide any primary or unique constraint/index) so the table is now less wide. The (tindex, tn, col) will be the clustered index of the table from now on. That also means that the date values will be available for the query - after the 2 parts of the index have been scanned.


MYSQL Social Networking website - Database Design, Data Growth and Hardware

You need to increase the amount of RAM, by a LOT.  Based on the load you are expecting I'd want at least 64 Gigs in there, if not 128 or 256.  Given that most of the database will become pretty stale pretty quick I'd say that one database should be just fine.  I've got tables with billions of rows which are OLTP.  Granted they are MSSQL, not MySQL but the ideas are the same.


Table with huge data takes long time to query

Your complaint here is rather inconsistent.  You talk about simple queries that just sort on updated_dt, but that is not the query you show.  Also, the query you did show did not take 15-30 minutes.  Is there another even simpler query that takes even longer?  If so, that would server as a better example.  Particularly if you turn on track_io_timing first.

The query you show orders by both updated_dt and id. While conceptually an index on just updated_dt could speed that up by requiring a sort on id only within ties on updated_dt, this is not implemented in any current version of PostgreSQL.  If you want an index to serve your multi-column ORDER BY, you must include both columns in the index.

If you want to make most things faster without building new indexes for all of them, you might want to get either an even larger instance, or provision faster storage.


You can GROUP BY both id and user_id and only keep records HAVNG more than 1 distinct status.

Query:

SELECT id
    , user_id
    , count(distinct status)
FROM data
GROUP BY Id, user_id
HAVING count(distinct status) > 1;

SQL Fiddle

Output:

| id | user_id | count(distinct status) |
|----|---------|------------------------|
|  a |       1 |                      3 |
|  c |       3 |                      2 |


Are mysql global variables values in bytes?

Yes, it is bytes. You also can not set anything other like

set session tmp_table_size = 8k;

This is only possible in configuration files. But you can do something like

set session tmp_table_size = 8 * 1024;


Pulling Top queries returns NULL in query plan and sql text

There are limitations when using sys.dm_exec_query_plan
5.4 When and How to Use Sys.dm_exec_text_query_plan

The query_plan column returned by sys.dm_exec_query_plan has type XML and suffers from the inherent limitation of the data type that it cannot have nest levels greater than or equal to 128. In SQL Server 2005 RTM and SP1, if the query_plan had a depth of greater than or equal to 128 levels this would prevent the query from returning. The second limitation is that it is not easy to get the query plan of a particular statement in the batch directly from the DMVs without parsing the query plan XML returned from sys.dm_exec_query_plan.
To address both these limitations, in SQL Server 2005 SP2, we introduced sys.dm_exec_text_query_plan. Sys.dm_exec_text_query_plan is a TVF that takes 3 parameters: plan_handle, statement_start_offset and statement_end_offset. It returns the showplan in text format for the batch or for a specific statement within the batch.

Instead of reinventing the wheel, best is to use Brent's - sp_BlitzCache or Glenn's Diagnostic queries. or Aaron's answer here.
From SQL Server Query Performance Analysis using DMVs :
-- Which Queries are taking the most time/cpu to execute
SELECT TOP 20
    total_worker_time, total_elapsed_time,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2
        )
        FROM sys.dm_exec_sql_text([sql_handle]) AS est) AS query_text,
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    last_worker_time, min_worker_time, max_worker_time,
    last_elapsed_time, min_elapsed_time, max_elapsed_time,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  AND (total_worker_time/execution_count) > 100
--ORDER BY avg_cpu_cost DESC;
--ORDER BY execution_count DESC;
ORDER BY total_worker_time DESC;