You came to the right place !!! Hang onto something because you are going to roll on the floor laughing

I answered a similar question just like this back on April 22, 2014 involving apples, oranges, pears, and figs: MySQL group concat not showing distinct values when joined to another table

In your case, you need to assemble distinct fruits by the id it belongs to

Ready for a convoluted solution tailor made for you ?

PROPOSED QUERY

SELECT CONCAT('SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),'','','', '') tag FROM (',
CONCAT('SELECT ',GROUP_CONCAT(fruitstr SEPARATOR ' UNION SELECT ')),') A GROUP BY id')
INTO @TagSQL FROM
(
    SELECT REPLACE(CONCAT(REPLACE(QUOTE(tag),', ',CONCAT(''',',
    id,' id UNION SELECT ''')),',',id,' id'),',',' frt,') fruitstr FROM fruit
) A;
SELECT @TagSQL\G
PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;

YOUR SAMPLE DATA

mysql> drop database if exists dinesh;
Query OK, 2 rows affected (0.38 sec)

mysql> create database dinesh;
Query OK, 1 row affected (0.01 sec)

mysql> use dinesh
Database changed
mysql> create table fruit
    -> (
    ->     id int not null auto_increment primary key,
    ->     tag varchar(100) not null
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> insert into fruit (tag) values
    -> ('apples, oranges, apples'),
    -> ('apples, pears, pears'),
    -> ('figs, oranges, figs, oranges');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from fruit;
+----+------------------------------+
| id | tag                          |
+----+------------------------------+
|  1 | apples, oranges, apples      |
|  2 | apples, pears, pears         |
|  3 | figs, oranges, figs, oranges |
+----+------------------------------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT CONCAT('SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),'','','', '') tag FROM (',
    -> CONCAT('SELECT ',GROUP_CONCAT(fruitstr SEPARATOR ' UNION SELECT ')),') A GROUP BY id')
    -> INTO @TagSQL FROM
    -> (
    ->     SELECT REPLACE(CONCAT(REPLACE(QUOTE(tag),', ',CONCAT(''',',
    ->     id,' id UNION SELECT ''')),',',id,' id'),',',' frt,') fruitstr FROM fruit
    -> ) A;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT @TagSQL\G
*************************** 1. row ***************************
@TagSQL: SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),',',', ') tag FROM (SELECT 'apples' frt,1 id UNION SELECT 'oranges' frt,1 id UNION SELECT 'apples' frt,1 id UNION SELECT 'apples' frt,2 id UNION SELECT 'pears' frt,2 id UNION SELECT 'pears' frt,2 id UNION SELECT 'figs' frt,3 id UNION SELECT 'oranges' frt,3 id UNION SELECT 'figs' frt,3 id UNION SELECT 'oranges' frt,3 id) A GROUP BY id
1 row in set (0.00 sec)

mysql> PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----+-----------------+
| id | tag             |
+----+-----------------+
|  1 | apples, oranges |
|  2 | apples, pears   |
|  3 | figs, oranges   |
+----+-----------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

P.S. Like I said in my earlier post,

I TOLD YOU IT WAS CONVOLUTED !!!


Why a query is not covered when using a range operator on documents?

db.doc.explain(true).find({doc:{$gt:d1.doc}},{_id:0,doc:1})

The above query doesn't use db.doc.createIndex({"doc":1}) index, because the index is created at embedded document level. To use this index, you should specify all fields in the document. For example

db.doc.explain(true).find({"doc" : { "k1" : "v_0", "k2" : 0 }},{_id:0,doc:1})

or else you need to create an index at field level for embedded documents as shown below

db.doc.createIndex({"doc.k1":1});
db.doc.createIndex({"doc.k2":1});

For more details refer

Create an Index on an Embedded Field

Create an Index on Embedded Document


You will need to schedule this using Oracle DBMS scheduler.

In order to run the 8 procedures, there are couple of ways, you can create a small procedure that calls the 8 procedures respectively.

After that, you will create a DBMS scheduler job that will execute the procedure which calls the main procedure.

The script below creates the main procedure to call the other procedures

CREATE OR REPLACE PROCEDURE RUN_8PROCEDURES AS
BEGIN
  pkg_cd.PRC_CD_CASEDATAENTRY;
  pkg_cd.PRC_CD_DEDUP;
  pkg_cd.PRC_CD_CIBIL;
  pkg_cd.PRC_CD_TVR;
  pkg_cd.PRC_CD_SANCTION;
  pkg_cd.PRC_CD_DOWNPAY;
  pkg_cd.PRC_CD_DOC_CPA;
  pkg_cd.PRC_CD_DOC_QA;
  pkg_cd.PRC_CD_PDN_REQ;
  pkg_cd.PRC_CD_PDN_AUTHORIZE;
END;
/

This script creates a job that runs every minute and runs the main procedure.

BEGIN
   DBMS_SCHEDULER.Create_job (
      job_name          => 'MY_JOB_THAT_RUNS_EVERY_MINUTE',
      repeat_interval   => 'FREQ=minutely;interval=1',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'RUN_8PROCEDURES',
      enabled           => TRUE
   );
END;
/


I can give you a few things to watch out for, but I'm not sure what level of detail that you're looking for.

Merging and splitting partitions can be an expensive operation in Oracle, except for a few special cases which are optimized, such as splitting off an empty partition. For example, if you have a single partition of 12 months and you split off a month then Oracle will need to rewrite the entire 12 months worth of data to complete the operation. If you want to split off another month then Oracle will need to rewrite 11 months worth of data to complete the operation, and so on. In Oracle 12c you have the option to split and merge N partitions in a single operation but I do not believe this is available in Oracle 11g. Because of this it can often be faster in 11g to just create a new table with the partitioning structure that you want and to load all data into that new table. Once the operation is done you can drop the old table and rename the new table.

You said that you have specifications for the partitioning. Do those specifications include the tablespaces for the partitions? Are there any existing indexes on the tables to be partitioned? If so, should those be defined as global or local indexes? Do you need to apply any kind of compression to the partitions? Perhaps you are using advanced compression for all compressions or basic compression for old partitions which will no longer have data modified in them. If all of that is documented, great. If not, you might need to do some analysis and make some decisions.

Depending on your requirements you may be able to use interval partitioning. With interval partitioning you can define your starting partitions upfront and also the size of the partitions that you want going forward as new data is loaded into the table. So if you want your table partitioned by a date column into single day partitions you don't need to write code to get a new partition for each day or to script it out. That can be done for you automatically by Oracle.

Here is an example of interval partitioning from one of the links below:

CREATE TABLE interval_sales
   ( prod_id        NUMBER(6)
   , cust_id        NUMBER
   , time_id        DATE
   , channel_id     CHAR(1)
   , promo_id       NUMBER(6)
   , quantity_sold  NUMBER(3)
   , amount_sold    NUMBER(10,2)
   )
 PARTITION BY RANGE (time_id)
 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
   ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
     PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
     PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
     PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

In that example query Oracle will automatically create monthly partitions for any data after TO_DATE('1-1-2010', 'DD-MM-YYYY'). Data before TO_DATE('1-1-2010', 'DD-MM-YYYY') will be loaded into one of the non-monthly partitions p0, p1, p2, or p3.

If you want examples of how to apply range partitioning or interval partitioning you could start here:

https://oracle-base.com/articles/11g/partitioning-enhancements-11gr1
https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#BAJHFFBE
https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#insertedID1

I know that you aren't supposed to just provide links without summarizing information from them, but there's a lot of content in those links and I'm not quite sure what you're looking for.


Could we recover Mongo records from the data files only without namespace files?

Hmm.. You have MMAPv1 engine in use... If your mongodb version is BEFORE 3.0, you can use mongodump and --dbpath parameter to read data directly from data files.


Pros and cons of a particular DB design approach for single vs multiple tables

Note, that I assume C# (since this language is present in tags) and Entity Framework.

Both approaches assumes, that entity types are inherited from abstract, non-persistent base entity type, which accumulates 75% of fields, so the code base discussion is not relevant.

Pros of Table-Per-Hierarchy (all descendants are stored in single table):

database structure is simpler (just one table)
queries are simpler (with Table-Per-Type you need joins to select descendant's row)
auditing is simpler

Cons of Table-Per-Hierarchy:

the table has low-density data (a number of nullable fields, filled dependent on row type). This isn't a problem, when there are 5-6 such fields, but what if there are 20-25 of them?
table relations and indexes structure are not obvious. Since your entities will have relations with other entities, this will lead you to foreign key madness. Just imagine foreign keys for you table.
database structure isn't obvious. Who can guess, that news are stored beside CMS pages?
usually, 90% of entity types have an Id and Name-like fields (Title, Header, etc). Are you going to store all of them, using single table?
table size will grow very fast. With the lapse of time some operations (e.g., deletion) will be slower.

IMHO, every inheritance scheme, mapped to a relational database, should be used very carefully. Definitely you shouldn't map all you entities to a single table.


Updating column based on conditions from another table

For the first two statements, try:

set    a.status = COALESCE( (select case
                        ...
                                and   a.cvg_type_cd not in (...)), a.status);

This way, if the select case statement generates a NULL value, you'll set the status back to what it already was.

For the third, try this:

set    status = case
                  ...
                  when cvg_cstat in ('H') then 'NB_Rider Claims'
                  else status
                end

Similarly, rather than allowing the case statement to return a NULL value, you force it to return the original status if none of the updated values match.

NOTE: You might get better performance in the first two queries if you modified the query to eliminate rows from a (that is, i2017_mar_ye) that should not be updated. For instance, in the second query, add a WHERE clause on the outer query:

WHERE a.cvg_cstat NOT IN ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR','B','I','K','N','D','E','S','F','H')
  AND a.cvg_iss_dt <= to_date('31-March-2016')
  AND a.cvg_cstat not in ('1','2','3','4')
  AND a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9');

You can remove the last three of those lines from the subquery at that point, as well.

Depending on your DBMS, you may be able to use a join between i2017_mar_ye and i2016_mar within the UPDATE statement, but only update i2017_mar_ye. That might allow for better performance as well.


SchemaSpy is a free piece of software that enables you to output html documentation of your databases, one of the many vendors it supports is PostgreSQL.


Cassandra nodetool repair parallelism: "parallel" versus "dc_parallel"

The problem here is the poor choice of name for the --dc-parallel option, IMO.

Currently there are 3 "parallelism degrees" that is possible to specify for a repair operation: sequential, parallel and datacenter-aware (dc-parallel).

Parallel repair is the oldest option and it simply allows all nodes to run repair tasks at once.  This can be pretty CPU-intensive.
Sequential repair was introduced in order to reduce the CPU impact of repair on the whole cluster.  It is implemented by taking snapshots on all affected nodes then performing Merkle tree calculations using snapshots, one node at a time.  It puts less pressure on the CPU at a cost of longer run time and additional space taken by the snapshots.
Finally, "dc-parallel" repair is actually sequential repair within every DC, while all DCs are allowed to run these repairs at the same time.  So with this option only one node per DC is actively working on repair at a time.  This is not different from sequential if you only have a single DC.


Passing Arguments from INSERT / UPDATE to Trigger

Use NEW or OLD depending if you need the inserted or updated record.
Postgres documentation

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:
NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.
OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.

CREATE TRIGGER gerarconf
AFTER INSERT OR UPDATE
ON asterisk.fila
FOR EACH ROW
EXECUTE PROCEDURE asterisk.registra_gerar_conf_fila('FILA',NEW.column);


Postgresql table size growth after adding column

I suppose now tuples did not fit any free space on pages, so each row had to relocate. Assuming most if not all new rows only grown in size, around half rows needed new location, and so table is taking 50% more on disk. Why half of rows?.. Because space released by two old tuples is enough for one new tuple only. I think tuple tetris did not help here, because rows were moved not two closest together, but randomly, so new tuples could not effectively use space released...

If this assumption is correct, the space your table takes is half empty. Alas it is reserved for future operations. You need vacuum full table_name to basically rebuild it to return space... Or just work with current size, day after day (or even year after year ) the size will drop a bit if you keep on updating, deleting...


Help me to map Column types betwenn SQL and TopSpeed Clarion Tables

Personally, I would buy the odbc driver and import all the files with the SQL wizard
http://www.softvelocity.com/drivers/tsodbc.htm


Postgres on Engineyard space at 100%, pg_xlog filled up with files

You can revise the checkpoint_segments setting in your postgresql.conf and issue SELECT pg_reload_conf(); as the postgres (super)user (in any database) to make that change live. What the change to that setting does is reduce the number of WAL segments that remain resident in your pg_xlog directory. If you have a lot of data churn in your db you will want to keep the checkpoint_segments setting at 32 minimum (see http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS for more details).

Some general tips:

Ensure your autovacuum settings are pretty aggressive.
Move the pg_xlog directory to a different volume than the $PGDATA directory. I have measured performance increases from 10% - 30% from that change alone.
Set up a monitoring tool to keep an eye on table/index/database/server size increases. Eg. Nagios, New Relic,


PostgreSQL surrogate keys: use sequence/bigint or OID?

There are a couple of misunderstandings floating around here.

OIDs were included with every row by default in very old versions of Postgres. The default was soon changed to not include OID columns in user-defined tables.

Quoting the Postgres 8.1 documentation:

  default_with_oids (boolean)

  This controls whether CREATE TABLE and CREATE TABLE AS include an OID
  column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS
  is specified. It also determines whether OIDs will be included in
  tables created by SELECT INTO. In PostgreSQL 8.1 default_with_oids is
  disabled by default; in prior versions of PostgreSQL, it was on by default.

Bold emphasis mine. What @Shire quoted is related, but OIDs are not even considered for a PK in modern Postgres.

The obvious reason why @a_horse (correctly) suggested bigserial (and not just serial) is in the question:

  Is there a downside to using them instead of creating a bigint field and a sequence?

Bold emphasis mine again. Typically, a serial column (implemented with a 4-byte integer) is good enough for most tables - unless you expect more than 2 billion (2^31 - 1 = 2.147.483.647) rows over the lifetime of the table.


It looks like your ACCEPT prompt includes quotation marks. But quotation marks are also used to delimit the prompt string, so if you want to include those characters into the prompt, you need to double them:

… prompt 'Fecha de inicio (''DD/MM/YYYY-HH24:MI:SS''): '
… prompt 'Fecha de fin    (''DD/MM/YYYY-HH24:MI:SS''):'

Although I think you could simply omit them, since this is just a prompt:

… prompt 'Fecha de inicio (DD/MM/YYYY-HH24:MI:SS): '
… prompt 'Fecha de fin    (DD/MM/YYYY-HH24:MI:SS): '


How to restore mdf file in sql server 2005?

You can use sp_attach_single_file_db or the newer version CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG.  In both cases it will create a new log file for you.

Unfortunately because you have no log you will not be able to recover anything that has not been committed.  It is also a good idea to run a DBCC CHECKDB afterwards to make sure there is no other damage.


#1066 - Not unique table/alias: 'clients'

You joined clients twice in the same FROM clause, that's throwing the error.

The two incarnations of the table need to have different aliases (chosen: cl_to and cl_from):

SELECT request.*,
       cl_to  .ClientName AS ToClient,
       cl_from.ClientName AS FromClient,
       drivers.DriName, requesttype.ReqTName
FROM request
    INNER JOIN clients AS cl_to    -- alias 1
        ON cl_to.ClientID = request.ReqToClient
    INNER JOIN clients AS cl_from   -- alias 2
        ON cl_from.ClientID = request.ReqFromClient
    INNER JOIN drivers
        ON drivers.DriID = request.DriID
    INNER JOIN requesttype
        ON requesttype.ReqTID = request.ReqTID ;

I'd prefer to use (short) aliases for all tables:

SELECT rq.*,
       cl_to  .ClientName AS ToClient,
       cl_from.ClientName AS FromClient,
       dr.DriName, rt.ReqTName
FROM request AS rq
    INNER JOIN clients AS cl_to   ON cl_to.ClientID = rq.ReqToClient
    INNER JOIN clients AS cl_from ON cl_from.ClientID = rq.ReqFromClient
    INNER JOIN drivers AS dr      ON dr.DriID = rq.DriID
    INNER JOIN requesttype AS rt  ON rt.ReqTID = rq.ReqTID ;


Is backing a website by a SQL Server 2012 OLAP cube considered reasonable?

You could do this with an OLAP system - some of the benefits of SSAS for this type of application include:

SSAS can readily scale out - especially as this is a read-only application with no requirements for cube writeback.
Aggregations can be tuned to minimise the I/O allowing the cubes to be tuned for efficiency.
OLAP client software and third party controls (web and rich client) are readily available from a number of vendors.
SQL Server 2012 Business Intelligence edition has pretty much all of the scalability features for SSAS, so it can be used as a cost-effective platform to front cubes for a SQL Server enterprise edition (or third party) database.  Note that licensing may be an issue for this as B.I. edition is CAL-only.
SSAS has a data mining function that could be used to do a shopping basket analysis on the data and feed a 'suggested purchases' feature on the website.

On the other hand, the requirement is to show a relatively constrained data set, so the ad-hoc slice-and-dice capability of an OLAP server may be overkill, both in cost of software and cost of the hardware infrastructure to run it (SSAS is quite resource hungry).  You could probably achieve your immediate requirement with a periodically refreshed summary database, and do it with less hardware and licensing costs.

From a first glance, I would suggest OLAP is probably not necessary to fulfil your existing requirement.  However, it could certainly be done this way and you might get some mileage from the data mining features to provide a 'suggested purchases' feature.


As part of a different issue I had to give my login the sysadmin role. I haven't seen the problem I described above since then.

Yes, it may work now, but I am not happy with this solution, partially because I don't quite understand what was going on.

Later edit: As pointed out by Thomas in a comment, here's what's going on:

  If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).


SSDT project with working linked server references gets SQL71562 warning

According to the message shown in the Question, this is actually a warning ("warnung") and not an error. An error will stop the build process and prevent the project from being published to the server. Warnings merely show up in the "Error List" window, but don't prevent the build or publish processes from completing.

I created a Database project in Visual Studio 2013 and added a stored procedure that selected from a Linked Server. If the Linked Server name did not match a valid Linked Server on SQL Server, then there was a publication error as reported by SQL Server (not Visual Studio). Otherwise, everything worked as expected. But in both cases, my Linked Server reference in that Stored Procedure in Visual Studio was underlined in blue AND the following message appeared in the "Error List" window after the build finished:

Warning 2   SQL71562: Procedure: [dbo].[GetExpressDBs] has an unresolved reference to
            object [LStoSqlExpress2014].[master].[sys].[databases].

If you don't want to see these warnings, there are two things you can do:

You can simply not show them in the "Error List" window by clicking the button at the top of the "Error List" window that says "X of Y Warning(s)". If you do this, you won't see ANY warnings here (even non-Linked Server-related warnings), but your code will still show the blue underlines that will display the warning if you hover over them.
You can tell Visual Studio / SSDT to ignore just these specific warnings (but still show the others) by doing the following:

Go to Project Properties
Go to the "Build" tab
Enter the following into the "Suppress Transact-SQL warnings:" text field:
71562

Doing the steps noted above will prevent just this one specific warning from showing up in the "Error List" window as well as the blue underlines in the code using the Linked Servers.


What should it look like when your SQL servers are properly licensed?

Aha, those will always be disabled and null, according to the SERVERPROPERTY (Transact-SQL) manual page:

LicenseType:

Unused. License information is not preserved or maintained by the SQL Server product. Always returns DISABLED.

NumLicenses:

Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL.

The query is not correct.
Instead, I need to download Microsoft's Assessment and Planning Toolkit (MAP).


PostgreSQL SELECTs not returning correct result following recovery?

The index data order on disk for text columns depends on the locales provided by the underlying operating system.

The same locales (that is, with the same name) may differ between operating systems on the order rules, even on simple things. As an example this question:
PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly
shows how "0102"and 0102sort differently on Ubuntu and FreeBSD.

The result in the physical index structure is that one value should come before the other in one OS and vice-versa in the other.

The problem with a recovered base backup is the same as with replication: the on-disk structure of the index is used as-is, resulting in a corrupted index in the target system if the column's locale doesn't sort exactly the same between systems.

It's mentioned in the comments that the origin system is CentOS and destination Mac OS X. Given that CentOS uses GNU libc and Mac OS X certainly a BSD-like libc, I'd think that there is no chance at all for on-disk postgres portability between these systems.


How to troubleshoot poor trigram (gin) index performance?

Substantial improvements were made to pg_trgm in PostgreSQL v9.6, pg_trgm v1.3.

But if you upgraded your server from before 9.6 to 9.6 or later using "pg_upgrade", you will not benefit from these improvements until you do alter extension pg_trgm update TO "1.3".


Infix search on large table when majority of rows can be filtered out

Thank you all for your input. My colleague just solved the problem by using Trigram index on the VALUE_N columns. Now the queries run under 10ms.


PostgreSQL won't allow remote connections

Okay ... Upon installation of postgres another service was using the port 5432 so postgres took 5434. I didn't notice. Works now


How do I query this 20 million record view faster?

A view is macro that expands. So if your view is a JOIN of 2 tables, the execution plan will show the 2 tables. The view is transparent.

This doesn't apply if the view is indexed/materialised. However then you wouldn't be asking this question.

So, what does the execution plan say? The DTA? Missing indexes dmv query? Most expensive dmv query?


How do I join information schema to the table's data?

That depends upon the type of DB you actually use.

In NOSQL like MongoDB support Dynamic schemas to join the table using column name which results in embedded documents.


How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?

So it looks like to me what you are missing is the "Return only the top Created record for each instance".  So you are getting all rows, and then watever its top Created value is for the same SomeInfo record.  Unfortunately you can't just add the MAX(Created) = Created into the base WHERE clause.

If you just wrap the whole thing in a CTE you can then just add a MAX(Created) = Created into the WHERE and get what you are looking for (not that i think CTE's are the anwer for everything).

WITH CTE (ID, SomeInfo, GroupID, ParentID, Created, MaxCreated, RootCount, IsMulti)
AS
(
    SELECT
        Id,
        SomeInfo,
        GroupId,
        ParentId,
        Created,
        MAX(Created) OVER(PARTITION BY SomeInfo) AS MaxCreated,
        COUNT(Id) OVER(PARTITION BY SomeInfo) AS RootCount,
        CASE WHEN COUNT(Id) OVER(PARTITION BY SomeInfo) > 1 THEN 1 ELSE 0 END AS IsMulti
    FROM
        Temp
)
SELECT ID, SomeInfo, GroupID, ParentID,  MaxCreated AS Created, RootCount, IsMulti
FROM CTE
WHERE
(
    GroupId = 1
    AND ParentId IS NULL
    AND Created = MaxCreated
)
ORDER BY MaxCreated ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

In my quick test it has the same execution plan and does not take any additional execution time (See execution plan added below). (now it is a small result set so it is probably something you will still need to test with.)

Hopefully that is more of what you are looking for.


SQL Server Self Signed Cert - no private key?

This is too big for a comment on Sean's answer so let me add to it.

  q1) can I check if the cert use for SSL encryption is the
  MS_SQLAuthenticatorCertifcate ?

According to the Principals (Database Engine) doc
the ##MS_SQLAuthenticatorCertificate## cert is used for an internal certificate-based login.

The self-signed certificate generated during instance start-up is not exposed by a DMV, at least as far as I know. EDIT: Also, Sean mentioned in his answer this certificate is resident only in the SQL Server process memory space and is removed upon SQL Server shutdown for security reasons.

  q2) How could a SSL encryption / CERT work with "NO PRIVATE KEY" ? In
  all my understanding, a cert/asymmetric encryption will involved a
  PUBLIC key (provided in the cert) for encryption, and the owner of the
  cert will have its PRIVATE key for decryption ?

The actual SQL Server self-signed certificate is an X509 certificate with a public/private key pair. However, note that TLS uses a symmetric key (same key used for both encryption and decryption) to encrypt/decrypt messages over the wire for performance reasons. This session-specific key is generated during the TLS handshake and the self-signed cert (or provisioned one) is used to generate and protect the secrets exchanged, not to encrypt the subsequent messages.
This Microsoft doc (https://technet.microsoft.com/en-us/library/dn786441(v=ws.11).aspx) describes how the Transport Layer Security (TLS) protocol works and provides links to the IETF RFCs for TLS 1.0, TLS 1.1, and TLS 1.2.

You can find more details of the actual negotiated TLS protocol and cypher from this Extended Event trace but this requires SQL Server 2016 SP1 or later:

CREATE EVENT SESSION [tls] ON SERVER
ADD EVENT sqlsni.trace(
    WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%')))
ADD TARGET package0.event_file(SET filename=N'tls_trace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

Below is an example of the event text field using a self-signed certificate from the trace against SQL Server 2017

SNISecurity Handshake Handshake succeeded. Protocol: TLS1.2 (1024), Cipher: AES 256 (26128), Cipher Strength: 256, Hash: SHA 384 (32781), Hash Strength: 0, PeerAddr: ::1


How to get a SQL value literal for arbitrary type in PostgreSQL?

With quote_literal you can get a text representation of any type. Or if your input can be NULL quote_nullable might be more appropriate depending on what you wanna do with it.

select quote_literal(now());
          quote_literal
---------------------------------
'2018-08-01 16:27:42.696751+02'
(1 row)


May be this can help --> How to recover deleted records


You will find the chapter Read Committed Isolation Level of the Postgres 9.5 manual instructive:

  Read Committed is the default isolation level in PostgreSQL. When a
  transaction uses this isolation level, a SELECT query (without a
  FOR UPDATE/SHARE clause) sees only data committed before the query began;
  it never sees either uncommitted data or changes committed during
  query execution by concurrent transactions.

Bold emphasis mine.

Until your transaction is committed, changes are not visible to other transactions. VACUUM has nothing to do with this, it only cleans up dead tuples after all transactions are finished with them.


how to generate a sequence of values with differents formats in PostgreSQL

You can use regexp_replace() to cleanup the numbers so that you can cast it to an integer value.
with cte as (
  select left(documentno,12) lpart,
         regexp_replace(split_part(documentno, '/', 3), '[^0-9]', '', 'g')::int as num
  from documents
), minmax as (
  select lpart, min(num) minpart, max(num) maxpart
  from cte
  group by lpart
)
select m.lpart, t.missing_document_no
from minmax m
  cross join generate_series(m.minpart, m.maxpart) as t(missing_document_no)
order by t.missing_document_no

I have split up the "cleaning" of the data and the group into two CTEs to make the steps a bit more readable. Given your sample data, this returns:
lpart        | missing_document_no
-------------+--------------------
PROFS/2021/0 |                2050
PROFS/2021/0 |                2051
PROFS/2021/0 |                2052
PROFS/2021/0 |                2053
PROFS/2021/0 |                2054
PROFS/2021/0 |                2055
PROFS/2021/0 |                2056
PROFS/2021/0 |                2057
PROFS/2021/0 |                2058
PROFS/2021/0 |                2059
PROFS/2021/0 |                2060

To find the missing values, you can extend the final select to:
with (
...
), minmax as (
...
)
select lpart||t.doc_no as missing_doc_no
from minmax m
  cross join generate_series(minpart, maxpart) as t(doc_no)
where not exists (select *
                  from documents d
                  where regexp_replace(split_part(d.documentno, '/', 3), '[^0-9]', '', 'g')::int = t.doc_no)


Slow running Oracle query caused by unnecessary full table scan

Answer left as comments by the question author

I think I may have found the issue, and it definitely makes sense. The type of the data column on the temp table was nvarchar2(100) while the type of the scheduleid column was char(22).

So apparently with the different types, the index cannot be used. When I added a to_char to the data column, it was able to finally use the index.

This temp table is also used in some other joins, where the data is of type varchar(100). In hindsight, maybe I could have had multiple columns on the temp table for different types, but what I have now works.


Not sure if this is a solution or a work-around. Either way, it solved the problem.
I disabled the SQL Task and created a Script Task. The connection happens within a try/catch. If it fails, the flow goes to a "do-nothing" catch block. The package sees the completion as success and progress continues. A screen image of the new Script Task and its code are included.

#region Namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_4ddaab95fb4d46ef9521d651f5227575
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain :
Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
    public void Main()
    {
        Package pkg = new Package();
        Variables vars = null;
        VariableDispenser vd = pkg.VariableDispenser;
        SqlConnection LoggingConn;
        SqlCommand LoggingCommand;
        vd.GetVariables(ref vars);
        try
        {
            LoggingConn = (SqlConnection)Dts.Connections["Logging ADO NET"].AcquireConnection(null);
            LoggingCommand = new SqlCommand("[etl].[usp_LogPackageStatus]", LoggingConn);
            LoggingCommand.CommandType = CommandType.StoredProcedure;
            LoggingCommand.Parameters.AddWithValue("@PackageID", vars["System::PackageID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@PackageName", vars["System::PackageName"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@MasterExecutionID", vars["$Package::MasterExecutionID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@ExecutionID", vars["System::ExecutionInstanceGUID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@Status", vars["User::Status"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@StartTime", vars["User::StartTime"].Value.ToString());
            LoggingCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //Do nothing

            //For Debugging
            //MessageBox.Show(ex.Message);
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

    #region ScriptResults declaration
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion
}

}


Determine existence in recursive working table

I'm not sure I fully understand what you expect to happen with the SQL you pasted .. (add up the numbers from 1-99 ignoring all even values?)  But this is a SQL to get around the syntax error you have:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n+1 not in (t.n) and n < 100
)
SELECT sum(n) FROM t;

I would like to point out that the result of my SQL above returns 5050 because the "n+1" is never actually in the result set...  (You'd need to compare for n-1 in the result set if you wanted to ignore even numbers..)


Generate insert statements for table AND related data

The SSMS Tools Pack allows you to generate insert scripts for a particular table.  On the Features page, it's the "Generate Insert statements from resultsets, tables or databases."  That won't get all child tables automatically, however.

In an older Stack Overflow post, they mention a few methods for generating inserts.  The first method (using SSMS's Generate Scripts functionality) will work if you have SQL 2005 or better.  The second method, a downloaded stored procedure, could do the job if you're using 2000.

Assuming you have 2005 or later, right-click on the database and go to Tasks --> Generate Scripts.  From there, you can select specific database objects.  You'll select all of the necessary tables (so this is the manual process).

To get the list of tables, you are correct about walking the foreign key list.  That's at sys.foreign_keys.  The following script will get a list of all foreign key dependencies from a central point.  Note that this does not get the child records, but only the "parent" records.  For example, suppose T3 has a foreign key (T2ID) to T2, and T2 has a foreign key (T1ID) to T1.  If we run this script on T3, it will return three tables:  T3, T2, and T1.  If we run it on T2, it will return two tables:  T2 and T1.  If we run it on T1, it will return only T1.

declare @SchemaName sysname = 'dbo';
declare @TableName sysname = 't2';

with foreignkeys as
(
    select
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName,
        convert(varchar(8000), tbls.name + '.' + tbl.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
    where
        tbls.name = @SchemaName
        and tbl.name = @TableName

    UNION ALL

    select
        ref.object_id as ObjectID,
        refs.name as SchemaName,
        ref.name as TableName,
        convert(varchar(8000), fks.[Path] + '/' + refs.name + '.' + ref.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables ref on fk.referenced_object_id = ref.object_id
        inner join sys.schemas refs on ref.schema_id = refs.schema_id
        inner join foreignkeys fks on fks.objectid = fk.parent_object_id
    where
        fks.objectid <> ref.object_id
        AND fks.[Path] NOT LIKE '%' + refs.name + '.' + ref.name + '%'
)
select
    SchemaName + '.' + TableName
from
    foreignkeys

UNION

select @SchemaName + '.' + @TableName;

If you want to get the child tables as well (i.e., getting the entire foreign key dependency chain), another CTE will do it:

declare @SchemaName sysname = 'dbo';
declare @TableName sysname = 't2';

with foreignkeys as
(
    select
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName,
        convert(varchar(8000), tbls.name + '.' + tbl.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
    where
        tbls.name = @SchemaName
        and tbl.name = @TableName

    UNION ALL

    select
        ref.object_id as ObjectID,
        refs.name as SchemaName,
        ref.name as TableName,
        convert(varchar(8000), fks.[Path] + '/' + refs.name + '.' + ref.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables ref on fk.referenced_object_id = ref.object_id
        inner join sys.schemas refs on ref.schema_id = refs.schema_id
        inner join foreignkeys fks on fks.objectid = fk.parent_object_id
    where
        fks.objectid <> ref.object_id
        AND fks.[Path] NOT LIKE '%' + refs.name + '.' + ref.name + '%'
),
parentsandchildren as
(
    select
        ObjectID,
        SchemaName,
        TableName
    from
        foreignkeys

    UNION ALL

    select
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName
    from
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
        inner join parentsandchildren fks on fks.objectid = fk.referenced_object_id
    where
        not exists (select * from foreignkeys fk where fk.objectid = tbl.object_id)

)
select
    SchemaName + '.' + TableName
from
    parentsandchildren

UNION

select @SchemaName + '.' + @TableName;

If you run this script on T1, T2, or T3, it will return the list of all three tables (because it traverses the chain in both directions).  Both of these scripts are also smart enough to not get caught up in self-joins and loops, should your table structure have either of those.

Running one of these two scripts would get you the list of tables that you could then select in the Generate Scripts menu.  Just make sure to set the Type of data to script option to Schema and Data, or else you'll only get the table structure.

EDIT

I don't know of any tool which will script out individual records the way you're asking for them.  Even writing this would be difficult at first glance, because you would need to know which records you need to insert, all records which depend upon that, all records which depend upon those, etc.  That's a fairly straightforward recursion problem by itself.  But then you'd need to maintain a structure telling when each row gets inserted so you don't double-insert records.  For an arbitrary table structure (especially one with loops), this becomes a challenge.

But based strictly on your example, I'm not sure if there's actually any value to doing what you want regarding specialized insertion.  As far as SQL Server is concerned, there's no difference between the two scenarios:

Scenario A:

Insert Bob's Person record
Insert Animals related to Bob
Insert Fred's Person record
Insert Animals related to Fred

Scenario B:

Insert Bob and Fred's Person records
Insert all Animal records related to Bob and Fred

After the insertions are complete, all select statements will return the same results regardless of which path you choose.  Even if you have a trigger or some other complex logic that depends upon inserting in a particular order, I'd have a hard time seeing a case in which A works but B doesn't.  I could see this making sense if we were discussing a 1980s RDBMS and you used interleaving parent and child records in a file to improve performance, but that's not really applicable here.

What you might look at instead is getting the order of insertions correct:  insert into reference data tables first and go on down the chain.  In your example, that's inserting into Person and then into Animal.  That way, you won't have any foreign key constraint violations and all records would be up to date.  Or, using a tool like Atlantis Interactive's Data Inspector, you can have it disable foreign keys during the load and then re-enable them afterward.


pg_upgrade fails with lc_ctype cluster values do not match

You'll need to re-initdb the new database with LC_CTYPE set to de_DE (i.e. use a non-Unicode encoding); check the old DB's encoding and use the same one when you initdb the new database.

Alternately, use pg_dump and pg_restore to do a dump and reload of the old DB's contents into the new DB. This requires that you have the old DB running.


Can you use dynamic sql in a select statement?

You can never use dynamic SQL in a SELECT or in a function.  The Query Optimizer for the outer query wouldn't know what your dynamic batch does, and it might try changing the database.  In which case there's no way to build a correct query plan.


Migration from Sybase to a different platform

How feasible it is move the Sybase database to a different more popular platform such SQL Server or any other.
Is it feasible in the first place?

Very feasible and supported by the vendors (e.g. Microsoft in-case of SQL Server). I have done plenty of SybaseASE to SQL Server migrations.
Best is to use SQL Server Migration Assistant (SSMA) for migration.
Refer to :

Migrating from Sybase ASE to SQL Server – How to use SSMA for Sybase
SQL Server Migration Assistant (SSMA) Team's Blog

Which one is preferred?

Its up to your business what RDBMS they want to use. We are a Microsoft shop running majority of our software on Microsoft stack and so we move our Sybase Clients to SQL Server.
If you have any specific step that you find hard to proceed further, let me know and I will help you out, but above links will give you  a good start.


Space not reclaimed after dropping MyISAM table

If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql is on the same mount and /root.

Here is what you can do to start investigating: Go to the OS and do this:

cd /var/lib/mysql
df -h .

If the root partition is 100% despite dropping 4GB,

your datadir is not var/lib/mysql
another table consumed up the space quickly
something else clogged the root partition

Who are the usual suspects ?

Binary Logs : You cannot run PURGE BINARY LOGS with a 100% full disk. You will have to locate the folder with the binary logs, delete the oldest ones, and mysqld will get moving.
Error Log : Just a text file. Suppose your error log is /var/log/mysqld.log. You run echo -n > /var/log/mysqld.log and the space from that file is reclaimed. I don't use rm since mysqld has an open file handle to the error log.
Slow Log : Locate the Slow Log and do the same thing as you did with the Error Log
Temp Tables : These are usually created in /tmp. If you see .MYD file in /tmp with old timestamps, you can manually delete them.

Whatever it is, find the offending files and delete/truncate them because mysqld will not crash on Error 28 (Out of Disk Space). The mysqld will pause and wait patiently for diskspace to be available (See my post "Site Offline" MySQL server failing to start and stop).


Cost of Increasing max pool size in SQL Server

What may be the negative effects of the increase of the max
connections pool?

If connections are indeed properly closed and disposed when not actively used (i.e. immediately returned to the pool) and you have capacity headroom on both the app server and database server, I would not expect negative effects with a larger pool size. However, increasing the max pool size to avoid errors at peak load might not be the best approach depending on the root cause. Consider the implications on both application and database server resources.
Are the queries mostly in runable status on the database server? This may indicate query/index tuning is needed or the database server could use more cores to handle peak demand within SLAs.
Are the queries on the server side mostly suspended due to async network waits? This may be a symptom the web server is slow consuming query results under load, indicating the app server might need more cores, instances, or network bandwidth. You mentioned "a dedicated server" so perhaps additional app servers are in order. Note that 100 active queries also requires 100 threads on both app server and database server.
Are the queries on the server side mostly suspended due to other resource waits, such as IO, locks, or latches? This can be mitigated with query/index tuning or db server configuration (e.g. more tempdb files in the case of tempdb page latch contention).


Reorganize Objects in OEM - Host Credentials do not have enough privileges

You have different types of credentials in OEM !
First you need to create those credentials and test them.
See here an article that walks you throw how to create and test your named credentials


You can find information about connection in listener.log
Location is under $ORACLE_HOME/diag/ or $ORACLE_HOME/network/log


How can I sync databases to a secondary server for read-only usage?

If you have SQL Server 2012 Enterprise Edition you could implement Always On Availability groups.

This is an extension of mirroring (built on-top of windows clustering) which allows the mirrored database to be a readable secondary. The benefit of this is that the secondary is updated as the primary database is updated. However it is expensive as the secondary server does need a full licence.

You can implement log shipping and have the secondary databases in STANDBY mode, which would make them readable. You can then control when the logs are applied. I would recommend a short interval between log backups, this should prevent your logs from expanding so that you do not have to shrink them.


Check only prerequisites when running runInstaller in silent mode

I found the answer:

To check the prequisites in silent mode run the following:

./runInstaller -silent -executePrereqs -responseFile "/0/grid/response/crs_install_mk.rsp"


Omit max entry from each group in postgres?

My first thought would be to use a window function like ROW_NUMBER(), almost identical to your solution.

Here are a few more ways to write this query:

WITH mytable AS
  ( --- the query --- )
SELECT a_key
FROM mytable AS t
WHERE EXISTS
      ( SELECT *
        FROM mytable AS n
        WHERE t.a_value = n.a_value
          AND t.a_timestamp < n.a_timestamp
      ) ;

Using another window function, LEAD():

SELECT a_key
FROM
  ( SELECT a_key,
           (LEAD(a_timestamp) OVER (PARTITION BY a_value
                                    ORDER BY a_timestamp)
            IS NOT NULL) AS ok
    FROM mytable
  ) AS pointless
WHERE ok ;

A variation on the above, using a different condition to check which rows to keep:

           (LEAD(a_value) OVER (ORDER BY a_value, a_timestamp)
            = a_value) AS ok

And a rather weird solution:

SELECT a_key
FROM mytable

EXCEPT

( SELECT DISTINCT ON (a_value) a_key
  FROM mytable
  ORDER BY a_value, a_timestamp DESC
) ;

Modified NOT IN to NOT EXISTS:

SELECT a_key
FROM mytable AS t
WHERE NOT EXISTS
      ( SELECT 1
        FROM mytable AS m
        WHERE m.a_value = t.a_value
        HAVING MAX(m.a_timestamp) = t.a_timestamp
      ) ;

And modified again to a JOIN:

SELECT t.a_key
FROM mytable AS t
  JOIN
    ( SELECT a_value, max(a_timestamp) AS a_timestamp
      FROM mytable
      GROUP BY a_value
    ) AS m
  ON  t.a_value = m.a_value
  AND t.a_timestamp < m.a_timestamp ;

Regarding performance, I did a test with a small 200K rows table (not a subquery), with and without indexes, of the various methods.

Since the query needs to return a large majority of rows (more than 50% and could be close to 100% depending on the distribution), I wouldn't expect indexes to be particularly helpful.

The window function solutions (ROW_NUMBER(), RANK(), LEAD()) performed quite well and similarly to each other (less than 2 sec).

The EXISTS method came a bit slower and last was the DISTINCT ON method (around 3 sec).

The NOT IN method by @joanolo shows a materialized subplan and was really slow (but it may be more efficient if the mytable subquery returns fewer rows). Modified to a similar NOT EXISTS lowered the response time to about 3 seconds. The JOIN modification was somewhat better, around 2 - 2.5 sec.

The plans showed sequential scans of course and most improved with indexes, doing index scans instead (and lowering response time to about 1-1.5 sec for the window functions and the join methods).

(I used (a_value, a_timestamp) and (a_value, a_timestamp, a_key) indexes and variations changing to timestamp DESC but the actual indexes are more or less irrelevant to the specific example, since we have no idea how complex the sybquery is.)


When installing mysql in linux, why do we create a mysql group and user?

Every process under linux runs under specific user privileges. Services (like MySQL) usually need to open ports and access various system resources during startup, so they are required to be started as root user. However, it is not safe to have all the processes run under root as it is not required for continuous operation of services, thus it is recommended to create a special user, which will be used to run MySQL service. MySQL will only be able to access what special user can, and this is going to be limited to MySQL files on the system.

This is usual practice in linux. If you, however, use you distributions built-in package manager to install MySQL, this will be done for you automatically (in most distributions at least).


Set Field To Have Default Value Of Another

For a one-off update to make saledate the same as saledate1:

UPDATE dbo.test1
SET saledate = saledate1;

You could also do this with a computed column. This would ensure saledate always matches saledate1 (even when the data changes):

-- Drop the existing column
ALTER TABLE dbo.test1
DROP COLUMN saledate;

-- Add it back as a computed column
ALTER TABLE dbo.test1
ADD saledate AS saledate1;

A third option would be to drop saledate, then rename the saledate1 column:

-- Drop the existing column
ALTER TABLE dbo.test1
DROP COLUMN saledate;

-- Rename saledate1 to saledate
EXECUTE sys.sp_rename
    @objname = N'dbo.test1.saledate1',
    @newname = N'saledate',
    @objtype = 'COLUMN';

This last approach is more complicated there are dependencies on the column. You would need to remove or update those before performing the rename.


To simplify, say we have

R(A,B,C,D)

AB -> C
A  -> D

Relation R is NOT in 2NF -- the KEY is {AB}, but {D} is dependent only on part of the key.

Now decomposition

Step 1.   R1 {ABC}  R2 {AD}
Done.

So, both R1 and R2 are in 6NF and therefore in 5,4,3,2,1. There is no in-between -- it may happen that in some specific example you may see "progress" 1, 2, 3, BCNF; but that is an exception, not the rule.

  ... looking at a systematic approach to move from 1 NF to 2 NF to 3
  NF to BCNF ...

is a common misunderstanding.


Improve the insertion rate of mongo sharded cluster

The problem (from what I can see)
From what I can see from your output, you have a monotonically increasing shard key like ObjectId or a Date or something similar.
MongoDB sharding is done with key ranges over the selected shard key. Put simply, it works like "shard0000 is supposed to hold the rang from -infinity to $i, shard0001 the keys from $h to $p and shard0002 from $q to infinity. With a monotonically increasing shard key, every new value will be bigger than the last one inserted, heading to infinity, so the shard which will be used is always shard0002, instead of the writes being balanced to all shards.
So all of the write operations seem to hit shard0002 with individual chunks being migrated to the other shards. The necessary chunk migrations together with added network latency and a lot of metadata updates going on, it may well be that you have a poor write performance.
The solution
Really bad news: you can't change the shard key once it is set. You basically have to create  new sharded collection with a better shard key. For choosing a proper shard key, please read Considerations for Selecting Shard Keys in the MongoDB docs.
P.S. I wasn't sure wether to answer this question, since it is slightly off topic on Stackoverflow, which is aimed at programming questions. But since usually the developers are supposed to choose the shard key, my intention was to clarify things for them. Additionally, the data modeling might be influenced by sharding considerations, so – by a small margin – I thought it was ok to answer here. However, dear reviewer, feel free to flag this answer for deletion if you think differently. I am really not sure.


Try this

I believe the first part already evaluates to 00:00:00. So I just added the seconds in.

WHERE m_dt BETWEEN  (CURDATE() - INTERVAL 60 DAY) AND
  (DATE_ADD((CURDATE() - INTERVAL 60 DAY),INTERVAL 86399 SECOND))


I think it should be;

    DECLARE
      _port int;
    BEGIN
      select alter_port into _port from a_table where ....;
      select data into data_list from a_table where PortNumber = _port;


SQL Server 2016 compress function details - external decompression

According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are
just a blackbox

I don't know what documentation page you are referring  to but the  COMPRESS reference states:

This function compresses the input expression, using the GZIP
algorithm. The function returns a byte array of type varbinary(max).

You can then decompress using a GZip library in application code. PowerShell example, using .NET objects:
$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=tempdb")
$connection.Open()
$command = New-Object System.Data.SqlClient.SqlCommand("SELECT COMPRESS('this is gzip compressed data') AS GzipData;", $connection)
$gzipBytes = $command.ExecuteScalar()
$connection.Close()

$memoryStream = New-Object IO.MemoryStream
$memoryStream.Write($gzipBytes, 0, $gzipBytes.Length)
[void]$memoryStream.Seek(0,0)

$gzipStream = New-Object IO.Compression.GZipStream($memoryStream, [IO.Compression.CompressionMode]::Decompress)
$streamReader = New-Object IO.StreamReader($gzipStream)
$decompressedData = $streamReader.ReadToEnd()
Write-Host $decompressedData


Can't run rs.initiate() when do mongodb replication set

Answer is simple! You don't have "node1" defined in your /etc/hosts file. Your machine don't know IP address of "node1" (or "node2").

echo "127.0.0.1  node1" >> /etc/hosts

And then other thing, you have defined (in config file) that your mongod is listening only localhost. So it means that your whole replica set must be in that one machine and if it is so, all those three members cannot use same port 27017, every member must have different port.


Which Column is better to be used here VARCHAR or DATETIME?

As stated by manual DATE type internally represented by 3 bytes that is smallest possible for the required type of data. TIMESTAMP is represented internally as BIGINT seconds since epoch (4 bytes) but TIMESTAMP is always forced to be converted to/from the UTC if server's TZ is not UTC. That can cause significant overhead. DATE is compatible with date-related functions like week() or monthname() so it's look like the best choice.

DATE is far more efficient for search/sort/join/group/between than VARCHAR(18) so VARCHAR() isn't even an option.


Postgresql responses are slow without internet

something is doing dns lookups on your connections, check your logging settings are not logging domain names, and ensure that pg_hba.conf contains no host names (only ip addresses)

perhaps use wireshark or tshark to check for DNS traffic on the database server and see what the lookups are.


Force create foreign key even if reference does not exist

As a quick fix. you can add a semaphore that prevents the creation of bar before foo exists. Pseudocode:

WHILE true:
    n = (select count(1)
         from information_schema.tables
         where table_name = 'foo')
    if n == 1:
        break
    sleep 1

create table bar ...

In the long run, consider how you should handle dependencies between different parts of the system, that are created independently of each other


Why is the Group by and Having clause very slow with this query?

Get rid out the outer layer:

SELECT  SQL_CALC_FOUND_ROWS  ...
    FROM (SELECT ...
            ORDER  BY chitentrygroup.chitentrygroupid DESC
         ) AS t
   LIMIT  0, 10

-->

SELECT  SQL_CALC_FOUND_ROWS  ...
   ORDER  BY chitentrygroup.chitentrygroupid DESC
   LIMIT  0, 10

This will also fix a bug you may not have noticed.  A derived table returns an unordered set of rows.  That is, the optimizer is permitted to ignore the ORDER BY in the formulation you have.  This gives you not the first 10 rows, but some arbitrary 10 rows.

FOUND_ROWS is passe.  Have you noticed that search engines first moved to "about 1,000,000 hits", then got rid of the count?  Do you really need the count?

What indexes do you have on chitentrygroup?  In particular, this one may be beneficial:

INDEX(industryid, status)

Are you using InnoDB?  What is the value of innodb_buffer_pool_size.  For a tiny 2GB machine (or VM), it should be no more than 400M.

What version of MySQL are your running?  Old versions defaulted that to 8M or 128M.  These are too small.

What are the mappings?  Is there one employee per chitentrygroupid?  That is "1:many".  What about account and chitentry?  If these are each 1:many, you don't need to GROUP BY -- which is the real villain.

That is, if you can SELECT the chitentrygroupids, get through the ORDER BY and LIMIT without needing the ORDER BY, you avoid the "inflate-deflate" that happens with JOIN + GROUP BY.


Return unique records based on shared column in 2 tables

Assuming Account is on CustList, the following example should get you going:

--Set up demo data
SET NOCOUNT ON

DECLARE @Visits TABLE (Id INT,Account INT,DateOfVisit DATE,TimeOfVisit TIME)
DECLARE @CustList TABLE (Id INT,Account INT,EmailAddress VARCHAR(100))

INSERT INTO @Visits (Id,Account,DateOfVisit,TimeOfVisit)
VALUES (1,1,'2017-05-25','08:00:00')
INSERT INTO @Visits (Id,Account,DateOfVisit,TimeOfVisit)
VALUES (1,1,'2017-05-25','10:00:00')

INSERT INTO @Custlist (Id,Account,EmailAddress)
VALUES (1,1,'MyEmail@Test.com');

--Common Table Expression for distince account numbers
WITH DistinctAccountsVisits
AS (
    SELECT DISTINCT Account
    FROM @visits
    WHERE TimeOfVisit IS NOT NULL
        AND DateOfVisit = '2017-05-25'
    )
--Join Visits with CustList on Account
SELECT v.Account
    ,c.EmailAddress
FROM DistinctAccountsvisits v
JOIN @CustList c ON c.Account = v.Account


How do I log into the Dedicated Admin Console (DAC) on Linux?

Clients

There are two Linux clients from Microsoft that can connect to SQL Server's Dedicated Administrator Connection (DAC),

mssql-cli. Versions newer than 0.90 have the -A option to connect to the Dedicated Administrator Connection (DAC). (QUOTED_IDENTIFIERS is on by default).

mssql-cli -S localhost -U sa -A

sqlcmd. This is the older client that ships with the mssql-tools package. It should never be used. If you insist on using it, you'll have to get accustomed to GO. Don't foget to always use -I with sqlcmd to turn on QUOTED_IDENTIFIERS. No idea why Microsoft chose the admin: prefix for sqlcmd. On most other clients, they use -A flag.

sqlcmd -I -S admin:localhost -U sa

Server Settings

If connecting from outside localhost you may have to enable remote DAC first. To do that simply run

sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO


JOINS - optimize cost of LIKE/OR criteria

There are several factors that are contributing to your slow performance.
Using the like with the wild card on both sides of the text is going to cause the query to scan all of the rows in both tables multiple times to match your join predicate because the expression is not SARGable.  The LEFT operator in your where statement is also going to force your query to run in a single thread because again it's going to have to scan all of the rows in the master table to determine which rows will have the first three characters equal to 123.

There are a couple things you can try to get a little better performance out of the query.

The first would be to change your where statement from LEFT(Master.AcctNbr,3) = '123' to Master.AcctNbr like '123%'.  This will make the expression SARGable and should give you a seek on the master table assuming that you have the column AcctNbr indexed.

The second would be, building off the first change, to break the query out into three parts and union the results together.  This may not make a huge difference however it could help the query processor by technically providing it three independent queries and the query processor might decide to run the query in parallel.  (Going parallel might only happen if the query plan meets a certain threshold of cost based on statistics and the complexity of the statement along with the settings of SQL Server.)

   SELECT
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr1 + '%')
WHERE #Master.AcctNbr like '123%'
UNION
SELECT
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr2 + '%')
WHERE #Master.AcctNbr like '123%'
UNION
SELECT
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr3 + '%')
WHERE #Master.AcctNbr like '123%'

You might also have some hardware constraints depending on the # of CPU cores, amount of memory available and the speed of the disks your temp database is on.


Is there a DBMS that allows the "= NULL" syntax?

Some implementations of SQL do recognise x = NULL as equality, the ISO/ANSI standard on the other hand does not. In SQL Server for instance, SET ANSI_NULLS OFF results in (NULL = NULL) = true.

SET ANSI_NULLS OFF
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END

SET ANSI_NULLS ON
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END


How to structure renting and sale as type of purchase?

As long as you don't have any additional data to store with the "rental" or "sell" property, you don't need a table for it. Just use a COLUMN manage ENUM('rent', 'sale'). Then you have to think about prices. The "BuyMe" price seems simple. Renting Models may have a real interesting live, I mean there are a lot of ideas which come in mind, and much more ideas which already live out there, not to speak about the infinite and strange wishes of some Marketing Stuff. So you would be wise enough to point the product to a category of price models. When you have rental, I assume that some products are more or less equal.

This Price Category can then be resolved to a Price Model, so you can combine the time of rental together with the Price Model to the real price.

In the beginning start with your original idea of just a price per day (or price per hour), the Price Model contains just this single one value and you multiply number of days (or of hours) with that single number. When it gets more complicated, just extend the table (and the code, of course).

So you have a table:

CREATE TABLE products (
      id int not null auto_increment primary key
    /* , name, praising, weight, properties... */
    , manage ENUM('rent', 'sale')
    , pricecategory_id int DEFAULT NULL
    , sale_price decimal(12,2) DEFAULT NULL
    /* indices */
);

CREATE TABLE pricecategory (
      id int not null auto_increment primary key
    , name varchar(255)
);

CREATE TABLE pricemodel (
      id int not null auto_increment primary key
    , name varchar(255)
    , pricecategory_id int not null
    , price_per_day decimal(18,5)
    , index(pricecategory_id)
);

You refer from pricemodel to pricecategory, since sooner or later you might get different types of customers, which get different prices (Gold Card Members or something like that). So you just add the customertype to pricemodel and are ready for the beginning.

Think about Special Offers, Easter Bundles etc. products.sale_price is only set for the sale case, and pricecategory_id only for rent.

Have fun with the application and grow! I know of Price Models which tend to need nice Graph Theory Optimization to get calculated, since the different paths have to be compared and of cause the customer gets 'best price' between hour, day, and a lot more.


Is it possible to restore sql-server bak and shrink the log at the same time?

There is a do-not-ever-do-this-in-a-live-environment hack you can use where space is limited, by restoring the log file to a compressed folder. Attempt this by compressing an existing folder and restoring to it will result in an error, so you have to cheat with a symbolic link.

Create a compressed folder D:\LogCompressed\
Create a symbolic link to the compressed folder mklink /D /J D:\Log\ D:\LogCompressed\
Restore your database with the ldf file pointing at D:\Log\
Shrink the log file to an appropriate size
Detach the database, move the log file to an uncompressed folder, attach

It's dirty, it's cheating, DO NOT EVER DO IT IN LIVE, but it works. Quick test of a newly created database with a 32MB log file shows it as occupying 330kb on disk when compressed, decompress the folder and on disk size is back to 32MB.


Delete an object from within an array using JSONB in PostgreSQL

To remove key (warning - json/jsonb arrays index starts from 0 unlike pure SQL arrays):

UPDATE site_content
   SET content #- '{playersContainer,players,0}'::text[];

To remove array element with specific id:

UPDATE site_content
   SET content = content #- coalesce(('{playersContainer,players,' || (
            SELECT i
              FROM generate_series(0, jsonb_array_length(content->'playersContainer'->'players') - 1) AS i
             WHERE (content->'playersContainer'->'players'->i->'id' = '"2"')
         ) || '}')::text[], '{}');


No there's no way of configuring SQL Server to do what you want to do.

Under snapshot isolation the call to sp_tables gets blocked waiting for a shared key lock on one of the system base tables (sysschobjs) when doing a SELECT from sys.all_objects

The Using Row Versioning-based Isolation Levels topic in BOL does say:

  SQL Server does not keep multiple versions of system metadata. Data
  definition language (DDL) statements on tables and other database
  objects (indexes, views, data types, stored procedures, and common
  language runtime functions) change metadata.

Even under TRANSACTION ISOLATION LEVEL READ UNCOMMITTED the call to sp_tables ends up blocking however, whilst the straight forward SELECT ... FROM sys.all_objects is no longer blocked the same query references the HAS_PERMS_BY_NAME function in the WHERE clause. This appears to start a system transaction (CMetadataAccessor::CMetadataAcce) at a higher isolation level and ends up getting blocked waiting for a shared key lock on sysschobjs again.


Can sp_Blitz, sp_BlitzCache, or sp_BlitzFirst identify what queries caused a major growth operation to occur on a particular log file?

Sure, start with:

SELECT TOP 100 *
  FROM dbo.BlitzCache
  WHERE CheckDate BETWEEN '2019-02-18' AND '2019-02-19'
  ORDER BY TotalWrites DESC;

The output of sp_BlitzCache includes the most resource-intensive queries in the plan cache. Starting with that query, you can narrow down what queries wrote the most data during your problem time span.

I like this approach a lot more than the Extended Events one since the XE session would only catch the query that happened to trigger the log growth - which may not be the one that did the most writes. (It might have just been the straw that broke the camel's back.)

Note that these are the queries that did the most writes altogether, though, and those numbers aren't reset each time sp_BlitzCache runs. You may need to do a little more digging to find out which ones ran the most often (or generated the most writes) during a particular time span by joining to the prior runs of that query in dbo.BlitzCache.


Using an index consisting of the columns display_name, id, active and is_company would enable the query to be run using the index alone.  Ordering the columns in the order provided would remove the sort requirement.  This comes at a slightly update cost, and could replace the ix_displayname index.

If active were rare, it would be faster to order the columns active, display_name, id, 'is_company.  However, updating the active column would have higher update costs.

You may need to select the display_name in an inner query, or use an index hint.


CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table

I don't think this has anything to do with the spatial extensions. I think this is this problem here,

From that post, all these work without generating CONVERT_IMPLICIT

SELECT * FROM [Table] WHERE IsSomething = 'TRUE';

SELECT * FROM [Table] WHERE IsSomething = Cast(1 as bit);

DECLARE @TRUE bit;
SELECT @TRUE = 1;
SELECT * FROM [Table] WHERE IsSomething = @TRUE;

You can read about the reasons for this problem here.

If it is the spatial type you may try a CAST to VARBINARY(max).. CAST( geography::STPolyFromText('') AS VARBINARY(max))

If you can't afford to upgrade to a newer version of SQL Server, PostgreSQL with PostGIS is perhaps an option as it is free and open source and has the best GIS implementation.

CREATE TEMP TABLE xSpatialTable (
     id         int           PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     GeogCol1   geography
);

INSERT INTO xSpatialTable (GeogCol1)
VALUES ( 'POLYGON((53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670))' );


Get datetime between start and end datetime

Full overlap:

SELECT *
FROM source
WHERE Start_Date >= '2019-09-11 12:00.000'
  AND End_Date <= '2019-09-11 13:15.000'

Partial overlap (not adjacent only, for adjacents replace strong unequiations > and > with safe ones >= and <=):

SELECT *
FROM source
WHERE End_Date > '2019-09-11 12:00.000'
  AND Start_Date < '2019-09-11 13:15.000'

fiddle


Locking issues under transaction on Postgres

It sounds like the code might be using SAVEPOINTs to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks.

RELEASE SAVEPOINT after you're done with a step.

You might also want to consider batching the work into smaller chunks, as the:

SAVEPOINT
Try it
ROLLBACK TO SAVEPOINT if it fails, RELEASE SAVEPOINT if it succeeds

pattern works, but has some performance costs that scale with number of savepoints in a transaction.

This applies to PL/PgSQL BEGIN ... EXCEPTION blocks too.


Database design: where do I put this data?

What you are doing here is called specialization.

You can look at this answer to a similar question at stackoverflow which will point you in the right direction. I've implemented a slightly modified version with success.

   *An other option (that I do not advise!!) is the use of EAV. *

Edit: I've created an example for your situation. (Don't mind the sample data ;) )

CREATE TABLE EventType (
EventTypeID INT NOT NULL PRIMARY KEY,
EventType VARCHAR(200) NOT NULL
);

INSERT INTO EventType (EventTypeID, EventType) VALUES (1, 'Play Rockband'),(2, 'Play Call of Duty');

CREATE TABLE Event (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Event VARCHAR(200) NOT NULL,
    CONSTRAINT UQ_Event UNIQUE (EventID, EventTypeID),
CONSTRAINT FK_Event_EventType FOREIGN KEY (EventTypeID) REFERENCES EventType(EventTypeID)
);

INSERT INTO Event (EventID, EventTypeID, Event) VALUES (1, 1, 'Play the bass'),(2, 2, 'Sniper!');

CREATE TABLE SpecialEvent1 (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Extrafield VARCHAR(200) NOT NULL,
/* Restrict to only insert SpecialEvent1 */
CONSTRAINT CHK_EventTypeID_1 CHECK (EventTypeID = 1),
/* Check if event exists */
CONSTRAINT FK_SpecialEvent1_Event FOREIGN KEY (EventID, EventTypeID) REFERENCES Event(EventID, EventTypeID)
);

INSERT INTO SpecialEvent1 (EventID, EventTypeID, Extrafield) VALUES (1, 1, 'Expert');

CREATE TABLE SpecialEvent2 (
    EventID INT NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL,
    Extrafield2 VARCHAR(200) NOT NULL,
Extrafield3 VARCHAR(200) NOT NULL,
/* Restrict to only insert SpecialEvent2 */
CONSTRAINT CHK_EventTypeID_2 CHECK (EventTypeID = 2),
/* Check if event exists */
CONSTRAINT FK_SpecialEvent2_Event FOREIGN KEY (EventID, EventTypeID) REFERENCES Event(EventID, EventTypeID)
);

INSERT INTO SpecialEvent2 (EventID, EventTypeID, Extrafield2, Extrafield3) VALUES (2, 2, 'Character X', 'Team Z');

You can query the events like this:

SELECT EventType.Eventtype AS [Type of event], Event.Event, SpecialEvent1.Extrafield AS Difficulty
FROM Event
INNER JOIN EventType ON Event.EventTypeID = EventType.EventTypeID
INNER JOIN SpecialEvent1 ON Event.EventID =  SpecialEvent1.EventID AND Event.EventTypeID = SpecialEvent1.EventTypeID;

SELECT EventType.Eventtype AS [Type of event], Event.Event [Play As], SpecialEvent2.Extrafield2 AS Character, SpecialEvent2.Extrafield3 AS Team
FROM Event
INNER JOIN EventType ON Event.EventTypeID = EventType.EventTypeID
INNER JOIN SpecialEvent2 ON Event.EventID =  SpecialEvent2.EventID AND Event.EventTypeID = SpecialEvent2.EventTypeID;

SQL Fiddle: http://sqlfiddle.com/#!6/d1911/1/0


Deleting data from a table containing LOBs did not reduce the amount of data reported INTERNALLY by the table or database

@edze asked a great question. I've known that LOBs are stored separately from the table data (with a pointer referencing the storage location), but I didn't know specifically where they were stored.

While searching for more information on where LOB data is stored, I found the following links:

http://itknowledgeexchange.techtarget.com/sql-server/deleting-lob-data-and-shrinking-the-database/

The key take away here (focused on a database shrink) is the following:

Do the database deletion as normal.
Then backup and restore the database.
Then do the shrink, followed by rebuilding the clustered indexes in order to fix the fragmentation issue which the shrink will introduce.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f80ad8c7-e6c9-4538-a89b-8d6727050b9c/release-space-used-after-sql-server-delete-in-rows-with-blob-varbinarymax-using-textimage-on?forum=sqldatabaseengine

Here the key is:

Rebuild the clustered indexes of the table.
Execute DBCC UPDATEUSAGE (0)

With this being Standard edition, a rebuild will require a LONG lock - but it looks like the only way to recover the space.


Error: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table

This error means you already have an BEFORE INSERT trigger on companies table.

If it is the same trigger (meaning trig_companies_table_insert) that you created earlier and now you want to replace it then you need to drop it first:

DROP TRIGGER trig_companies_table_insert;
DELIMITER $$
CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
FOR EACH ROW SET NEW.createdAt = NOW();
END$$
DELIMITER ;

Now if you have some other trigger you have to merge code from both triggers into one, then drop existing trigger, and then create a new one.

To show the list of existing triggers use SHOW TRIGGERS.

SHOW TRIGGERS WHERE `table` = 'companies';


Which field type is better to store specific play time in video for postgres?

Time is for representing time of day

Interval is for representing durations

Interval is probably the best fit for locating instants inside a video recording,  but an integer or bigint count of frame number is another candidate.


How make my architecture scalable to handle linear process

Since you suspect the disk is the limiting factor, upgrading to SSD sounds like your best option if you are focused on hardware.  More RAM might also work, if it would allow to cache most of the slow table/indices.  (How big is map_rto and its most-used index)?

If you would rather try tuning the hardware you already have, or making sure that that is really the issue before buy hardware, then you should identify the slow queries from your stored procedures, and follow this advice for them.

Particularly useful would be turn on track_io_timing and post the EXPLAIN (ANALYZE, BUFFERS) for the slow queries.

Finally, is the code that calls these two functions single-threaded?  If so, you could try making it parallel, with different database connections for each thread.


Are you doing a backup every 12 hours?  MyISAM is blocked when dump, ALTER, OPTIMIZE, and several other operations are done.

Switch to InnoDB to eliminate most of such locks.

You can probably see the offending SQL by getting a full SHOW PROCESSLIST;.


I want to exclude rows from an SSRS report based upon a field value and display a message if, as a result of filtering, there are no results

I'm a little confused on your logic, because the visibility expression =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,False,True) will  hide the row if the count is 0 which is opposite the logic it sounds like you're trying to achieve with =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,"No Commitment Warnings", nothing) and based on what you said. If so, then all you may need to do is flip False and True around in your visibility expression like this =IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,True,False).
For reference, SSRS's functions are derived from VBA, so you can find the docs on the IIF Function here.


Combine tsvector and timestamp in one index?

You could create a RUM index like this:
CREATE INDEX ON bar USING rum (
   to_tsvector('english', the_text) rum_tsvector_hash_ops,
   timestamp
);

And query like
SELECT * FROM foo
WHERE to_tsvector('english', the_text) @@ to_tsquery('english', ?)
  AND timestamp < '2021-01-01 00:00:00'
ORDER BY timestamp DESC
LIMIT 1000;

You need a WHERE condition with timestamp for the RUM index.


Difference between count(*) and count(1) in mysql?

They are the same. This has often been asked in Stackoverflow and here

count(*), count(0), count(1), count(-1)

all return the count.

In fact, in SQL Server, the expression isn't even evaluated.

Edit In fact, in SQL Server, a COUNT(ALL ...) CONSTANT expression doesn't appear to be evaluated at all, however, a COUNT(DISTINCT ...) is*.

e.g.

select count(ALL 1/0) from xyz; -- Succeeds

but

select count(DISTINCT 1/0) from xyz;  -- Divide by Zero

and at least one exception is NULL

select count(1) from xyz; -- Operand data type void type is invalid for count operator.

FWR in MySQL count(1/0) returns 0 irrespective of the number of rows.


How to get number of begin transactions in PostgreSQL?

A_horse_with_no_name's comment had me curious, and it seems like PostgreSQL doesn't use transactions in the same way that you're used to in Microsoft SQL Server. Rather they use Save Points. This StackOverflow answer gives a little context about Save Points and how to track them (see the comments on the answer for additional details).
There typically can only be one transaction open at a time, but you can achieve nested transactions utilizing save points.


Generate and Insert 1 million rows into simple table

Itzik Ben-Gan uses following approach
This is probably the fastest way he found and he's quite smart :-)

WITH
  L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),       -- 2^16
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),       -- 2^32
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)

select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1
from nums
where k <= 1000000


Breaking down SSIS keystring values for connection manager between Server2000 and Server20008

the provider is the driver name. A connection can be opened against variuos sources and destination (SQL server, Oracle etc..). You have to specify the proper driver.
SQLNCLI10.1 is the sql server native client version 10.1
OLE DB Services=-13; is the default option for additional features like pooling and recursion etc...
Application Name is only a string that can be usefull to distinguish the running workload from other applications. Most of times you can find ".NET application" or the ssis dtsx name or id like in this case.


Is there a way to see how SQL Server 2012 defines new identities?

SELECT INTO has two phases (not visible in execution plans).

First, it creates a table that matches the metadata of the query used to create it. This happens in a system transaction, so the (empty) created table will continue to exist even if the SELECT INTO is wrapped in a user transaction that is rolled back. At the end of the first phase, we have an empty table that includes a column named A_ID with the identity property.

The second phase performs an insert into the table the first phase created. This insert is done with identity_insert semantics, so the identity values from the source table end up in the destination, unchanged. The highest value actually inserted is set as the last value used. You can use IDENT_CURRENT or sys.indentity_columns to see it.

Unrelated, but semi-interesting fact: There is a small window between creating the table and starting to perform the insert, where the source table in the query is not protected by a schema stability lock. If a concurrent process happens to change the schema of the source table (after the target has been created, but before the insert starts), error 539 is raised:

  Schema changed after the target table was created. Rerun the Select Into query.


How to force Windows to support not supported LC_COLLATE and LC_TYPE in PostgreSQL?

On Windows, it's likely that the needed locale is already installed, it's just named differently than on Unix.

As an example, the following database creation appears to works fine for me with PG9.1 on my  Windows XP, with its default locale French_France.1252 and no additional language pack installed that I'd recall.

CREATE DATABASE pldb
       ENCODING = 'UTF8'
       LC_COLLATE = 'Polish'
       LC_CTYPE = 'Polish'
       TEMPLATE=template0;

Also, a quick test of the sort order by submitting this query in UTF8 in this newly created database:

select * from (values ('z'), ('ź'), ('ż'), ('s'), ('ś'), ('x'), ('y')) as letters order by 1;

seems to output correct results:

  s
  ś
  x
  y
  z
  ź
  ż


How do you encrypt data while performing a mysqldump?

You can use ccrypt. Example:

Creating the key file:

echo 'mySecretKey123' > ~/.backup.key
chmod 600 ~/.backup.key

Encrypting the backup:

mysqldump databasename | ccrypt -k ~/.backup.key | bzip -c > ~/backup-mysql.sql.bz2.cpt

To decrypt the backup:

cat ~/backup-mysql.sql.bz2.cpt | ccat -k ~/.backup.key | bunzip2 -c | less


Is it possible to get a status from a concluded operation?

Yes and no. Depends on operation what was done and if you have opLog (your system is replica set; one node replica set is enough).

local.oplog.rs records all changes (insert, delete, update) but not queries.

But, you don't get status of that operation nor any performance data.


It's realistically a bug in whatever version of 10g you're using that would appear to be fixed in whatever version of 11g you're seeing an error on both statements.

I don't have a 10g database in front of me to test this.  I would wager, through, that you'd get an error in both cases if you used the old-style join syntax rather than the SQL 99 syntax.  That would imply that the problem is that Oracle wasn't translating from SQL 99 syntax to old-style syntax correctly and was missing the fact that the column was ambiguously defined.


Setting MAX memory for SQL servers depending upon the model of CPU

The max memory setting is how much RAM SQL will use. It will usually not use more then the max memory set. (Following Shanky's comment, see below for the exception to that rule): Quoting from  Memory Management Architecture Guide

  Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that
  are loaded into the SQL Server process. Examples of such memory
  allocation requests include allocations from extended stored procedure
  DLLs, objects that are created by using Automation procedures (sp_OA
  calls), and allocations from linked server providers.

Now if your hardware has 1 TB but only 768Gb are recognize in the OS, setting up the max memory to higher then 768Gb will allow SQL to use all available RAM (all the RAM detected by the OS) and this could cause potential issue with the OS as it will have to do memory pressure on SQL to claim it back.

Set the SQL MaxMemory setting depending on how much RAM is seen by the OS and it should be all good. (Leaving at least 4 Gb for the OS)

P.s. Do not set min memory setting the the same value as the max memory. Set it lower as SQL will not release RAM under the min value when there is memory pressure and this could lead to an OS crash.


[can I] be 100% sure that the DB server has no memory of successfully executed queries and therefore no one else can get access to this information?

No, you can never be 100% sure of that.

PostgreSQL maintains an internal plan cache. Currently this cache is only used for prepared statements; one-shot statements do not go into the cache. If you use a prepared statement though, the plan will be cached, and when no longer required the cached plan is invalidated but not necessarily deleted until the PostgreSQL backend that cached it exits.

PostgreSQL stores the query string in an internal debug variable. When a new query replaces the old one the debug variable is overwritten. If the new query is shorter than the old one, some of the old query won't be overwritten.

A query could be running a backend that has pages of memory written to disk in swap space because of memory pressure. That memory might contain the query string and/or parameters. This swap space isn't necessary overwritten even after the query has finished and the backend has exited. If the server is powered off abruptly the hard drive could be forensically analyzed and might discover query text.

When PostgreSQL frees internal query planner/executor objects, it does not zero them. It just marks the memory as free for re-use. This memory remains mapped until the PostgreSQL backend exits, and may contain query text, parameter values, etc.

When a PostgreSQL backend exits, the kernel doesn't generally zero its memory, just marks it as freed. An attacker who can exploit the kernel to gain raw memory read access could then see some past (and, of course, all current) queries.

So if:

You have encrypted swap space with a password supplied interactively on boot;
You accept that anyone with physical access to the machine will probably be able to exploit it to retrieve recent query history;
You understand that anyone with root access, or access as the postgres system user, can trace PostgreSQL's execution and capture query data (just not retroactively); and
You only care about query text recoverability after the individual PostgreSQL backend that ran that query has exited

then turning off logging is probably good enough. You also need to avoid using extensions like pg_stat_plans and pg_stat_statements that might capture query text and record it in the database.

Note that if PL/Python, PL/Perl, etc are installed, or some contrib modules are present, then it might be fairly easy to escalate access from the postgres database user (or other superuser) to the postgres shell user. From there you can reconfigure the database to enable logging, install extensions, inject C code, etc. There is no guarantee you would detect such an exploit, so they might be logging activity for some time.

An attacker who gets access even as the table owner might install a FOR EACH STATEMENT trigger on tables of interest that logs queries affecting those tables. That won't work for SELECT, but will for everything else. They don't even need to be database superuser for that.

Generally your queries shouldn't be security sensitive so much as your query parameter data. You can greatly reduce the chances of it being captured or logged by using proper bind parameters, e.g. libpq's PQexecParams, PgJDBC's PreparedStatement class, etc.

Most importantly, though ... if you want to maintain database security, don't lose privileged access credentials. Don't use a superuser account except from the DB server and set pg_hba.conf up so the superuser can't log in remotely. Don't have applications connect to the database as the same account that owns the tables they use, use a different account and GRANT only the rights they require. etc.

IMO PostgreSQL needs an extension in the protocol that lets you mark query parameters as "sensitive". Such query parameters should never be emitted in log files, shown in pg_stat_activity or otherwise recorded. This feature does not currently exist, though, and I'm not aware of anybody working on it


Consideration for a little bit heavy database

This table should have a clustered index on UserId. If there is no more than one thumbnail per user, then the clustered index can be the primary key.

Since you say you have a currently-running system on SQLServer 2012 standard edition, online index builds are not possible. You could wait for a low-activity period to create the index, or create a new table, migrate the data into it, then switch to using the new table.

The table should ideally look like this:

CREATE TABLE RegisteredThumbnail
(
    UserId       INT            NOT NULL,
    ThumbData    VARBINARY(MAX) NOT NULL,
    Created      DATETIME2(7)   NOT NULL,
    Updated      DATETIME2(7)   NULL,

    CONSTRAINT PK_RegisteredThumbnail PRIMARY KEY CLUSTERED (UserId ASC)
)

Since you cannot recreate the table, this is the index you need to create:

CREATE INDEX IX_RegisteredThumbnail_UserId
ON RegisteredThumbnail(UserID ASC)

As you're on 2012 Standard, this will be an "offline" operation - it'll block activity on this table while the index is being built. Given it's only a million rows, it should not take too long to build, however it does have to scan the table in order to do it, so you'll be limited by how fast it can read that data.


Alias the columns in the select. The values in the In (...) part of the Pivot need to be the exact field values observed in the data.

Select CompanyName, [1] As Mon, [2] As Tues, [3] As Wed...
  From Pivot234
  Pivot (Avg(Freight) For DayNumber In ([1], [2], [3], [4], [5], [6], [7])) As p;

  Can you explain why it is necessary to alias the spreading columns? Why can I not just simply state the columns as I did in the PIVOT table operator and in the SELECT statement?

The Pivot(Sum([ValueField]) For [CategoryField] In ('Category A', 'Category B' ... )) syntax is looking for the literal values that you supply in the In () to be in the [CategoryField]. It isn't mapping number 1 to position 1, it is searching the field for those exact values.


Expanding a dataset based on a weight field

An efficient way to perform this task uses an auxiliary table of numbers. This is simply a table with integers from 1 to n, where 'n' is perhaps a million or so. A numbers table comes in handy for all sorts of regular tasks.

CREATE TABLE dbo.Demo
(
    SampleNumber    integer IDENTITY NOT NULL,
    SampleWeight    integer NOT NULL,
    Attribute1      integer NOT NULL,
    Attribute2      integer NOT NULL,
    Attribute3      integer NOT NULL,

    CONSTRAINT [PK dbo.Demo SampleNumber]
        PRIMARY KEY (SampleNumber),

    CONSTRAINT [CK dbo.Demo SampleWeight 1-50]
        CHECK (SampleWeight BETWEEN 1 AND 50)
);

INSERT INTO dbo.Demo
    (
    SampleWeight,
    Attribute1,
    Attribute2,
    Attribute3
    )
VALUES
    (2, 23, 32, 65),
    (1, 32, 56, 75),
    (3, 54, 25, 87);

SELECT
    D.SampleNumber,
    D.Attribute1,
    D.Attribute2,
    D.Attribute3
FROM dbo.Demo AS D
JOIN dbo.Numbers AS N
    ON N.n BETWEEN 1 AND D.SampleWeight;

DROP TABLE dbo.Demo;

Execution plan:

Output:

╔══════════════╦════════════╦════════════╦════════════╗
║ SampleNumber ║ Attribute1 ║ Attribute2 ║ Attribute3 ║
╠══════════════╬════════════╬════════════╬════════════╣
║            1 ║         23 ║         32 ║         65 ║
║            1 ║         23 ║         32 ║         65 ║
║            2 ║         32 ║         56 ║         75 ║
║            3 ║         54 ║         25 ║         87 ║
║            3 ║         54 ║         25 ║         87 ║
║            3 ║         54 ║         25 ║         87 ║
╚══════════════╩════════════╩════════════╩════════════╝

SQLfiddle here.


How to dump a postgreSQL/postgis sample database filtered by a spatial extent?

Might not be the most elegant solution, but you can use the pg_dump utility to copy the DB and then overwrite the data in the spatial tables of the new, subset DB using the ogr2ogr utility. Run pg_restore and then ogr2ogr with your clipping method of choice. To overwrite the spatial data so that it is constrained to the extent of a shapefile, for example, run something like:

ogr2ogr -overwrite -update -clipsrc clip_shape.shp PG:"host=subsethost user=subsetloginname dbname=subsetdb password=subsetpassword" PG:"host=originalhost user=originalloginname dbname=originaldb password=originalpassword"

Check pg_dump and ogr2ogr documentation (linked) for details on the optional switches. Combining the two utilities should get you squared away.


Can't stop replication sp_dropdistpublisher responds "there are subscribers defined"

Have you tried using this to enumerate the list of subscribers to find if it thinks there are some defined? Notice that I'm just starting in sp_dropdistpublisher and reading the code to see where it checks before deleting. Just an idea.

--
-- Name:
-- sp_MSrepl_enumsubscriptions
--
-- Description:
--  Enumerate subscriptions on a database
--
-- Returns:
--  0 == Failed
--  1 == Succeed
--
-- Security:
--  public, db_owner check
--  Requires Certificate signature for catalog access
--
-- Notes:
--  Used by the UI to generate a list of subscriptions
--


To update a column using a sum of values from other tables that may have nulls, do something like this:

UPDATE Holiday_Reservation R SET Subtotal =
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.In_Flight_ID), 0) +
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.Out_Flight_ID), 0) +
   NVL((SELECT AC.Acc_PricePn FROM Accommodation AC WHERE AC.Acc_ID = R.Acc_ID), 0);

In a trigger this would look something like this:

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
   SELECT
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.In_Flight_ID), 0) +
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.Out_Flight_ID), 0) +
      NVL((SELECT AC.Acc_PricePn FROM Accomodation AC WHERE AC.Acc_ID = :new.Acc_ID), 0)
      INTO :new.Subtotal
   FROM dual;
END;
/