SQL Server 2008 - is "NT AUTHORITY\ANONYMOUS LOGON" login "mandatory"?

This account seems to allow login for every people that are in my domain

That is actually incorrect. It allows access to anyone that cannot be authenticated. Members of your domains will successfully authenticate and they will be rejected.

On a serious note: there is absolutely ZERO reasons to have this login enabled. It was added intentionally to your system by somebody, this is not a default configuration. If I'd venture a guess, it would be that somebody tried to configure integrated authentication with impersonation on a web application and did not understand the requirement for 'double hop' constrained delegation. Needless to say, the 'solution' is a huge security hole that requires immediate action. You need to identify the application that uses this login and fix the authentication as appropriate (the link contains the primer on how to set up constrained delegation).


in PostgreSQL you could use use something called "windowing and analytics". this does not exist in MySQL however.
an alternative way would be to use GROUP BY and do ..
SELECT max(id), person, address, ... FROM tab GROUP BY person, address, ...
this should work somewhat efficiently if you need it for all. if you need it for jsut one folk you should go for ypercube's answer.


Increasing tempdb data files on 2 node Availability Group

The steps you provide are correct except for the "disable synchronization" and "enable synchronization," which are not needed.  If you pause synchronization prior to failing over, you may have some data loss.

The stopping/starting of synchronization occurs automatically when failover is performed, regardless of whether it is automatic or manual.  So the process would be:

Assuming node B is the secondary, make the tempdb modifications on node B
Fail over to node B
Make the tempdb modifications on node A
Fail back to node A

If you are concerned that the tempdb changes might cause issues with replication to the secondary, and thereby affect commit times on the primary, you could change the sync mode to asynchronous to accommodate:

Assuming node B is the secondary, configure node B as an asynchronous replica
Make the tempdb modifications on node B
Configure node B as a synchronous replica and ensure that replication is caught up
Fail over to node B
Configure node A as an asynchronous replica
Make the tempdb modifications on node A
Configure node A as an synchronous replica and ensure that replication is caught up
Fail back to node A

I think this is overkill as I don't believe tempdb is used in the replication process, but I can't find an authoritative answer that states that explicitly.


CLOB / VARCHAR(MAX) is causing slow select query

If you can adjust the datatype of the column to XML and your table has a clustered primary key, you can potentially improve performance, but there are a few fundamental changes you'll need to ensure occur.

Ensure your table is Clustered on the Primary Key.  Your table must be clustered and the cluster key must also be your Primary Key.
Strongly type your column as XML.  Storing XML as character data eliminates any possibility to create an XML Index(es) on the column.

If you can satisfy these requirements, you can then take advantage of XML Indexes.  I'm not going to go into the details of how these work or how you set them up, as you can easily find articles online that discuss this in more detail (ref1, ref2, ref3), but used properly these can dramatically improve performance of queries that have to interact with XML data.


How to Concatenate DateTime to nvarchar field

Datetime without punctuation and up to 7 second decimals are 21 digits so there would be 1 character (32 - 10 - 21) still missing.

You can use the following to format the way you want.

Update Bil_ReturnsRepository Set
    PaymentReference = CONVERT(NVARCHAR(32),
        CONCAT(
            LEFT(PolicyNumber, 10),
            FORMAT(SYSDATETIME(), 'yyyyMMddHHmmssfffffff0')))
Where
    PolicyNumber = '1234567890'

If PolicyNumber is already NVARCHAR then there is no need to do an explicit CONVERT. Notice that I hard-coded the last zero.


Consistency of data between two databases on different server

If SQL server is the master,
LEt him insert first and get an id, this id you use to insert into mysql. As the sql server control ist. Mysql has an index on the id but is not a primary key., so you can insert what ever you need.
If both can generate the id, you must switch to uuids, these would always guarantee, that you are unique, and so it doesn't matter who generated the id first. as teh application generates the uuid, no autoincrrement is needed any more.
It will always be a good idea, to switch to other unique identifiers when mnore than one server is invooved.
The price is that the queries get slower, even with an index on the ids


MySQL Triggers not working from PHP page but work for manual DB updates via phpMyAdmin

Type SHOW CREATE TRIGGER trigger_name into the query window. This will give you the SQL code that creates trigger.

You can then add it to your question so we can help you with it.

Also, when modifying your trigger, instead of using GUI, use the query window. Just put DROP TRIGGER trigger_name in the top of your trigger script.


distrb.exe & Logread.exe using huge SQL.txt file in C:\Users\sqlsrvacct\AppData\Local\Temp folder

the tracing was enabled for ODBC and it was reponsile for this file.
I do not know why after Stoppin tracing from below screenshot all instances and agents was using this file and we have to stop them one by one until deleting was possible.


ORA-04031: unable to allocate bytes of shared memory ("","","","")

Your application is probably not using bind variables, which has caused the SGA to become fragmented (it fills up with multiple copies of SQL that use literals instead of binds).

You can temporarily fix the problem by flushing the shared pool with:

alter system flush shared_pool;

There's a workaround you can try to stop it happening in the future.

Turn on cursor sharing, then bounce the database:

ALTER SYSTEM SET cursor_sharing='SIMILAR' SCOPE=BOTH;

The only real way of fixing this is to rewrite your application so that it uses bind variables.


How do I find the leafs using PostgreSQL's ltree?

Using ltree2text sounds like a horrible idea, and cstring is an internal thing -- you should never be using that. If an ltree has no descendants it's a leaf, so let's test for that using @>

CREATE TABLE foo(ltree)
AS
  VALUES
    ( 'Top.Science.Astronomy'::ltree ),
    ( 'Top.Science.Astronomy.Astrophysics' ),
    ( 'Top.Science.Astronomy.Cosmology' ),
    ( 'Top.Collections.Pictures.Astronomy' ),
    ( 'Top.Collections.Pictures.Astronomy.Stars' ),
    ( 'Top.Collections.Pictures.Astronomy.Galaxies' ),
    ( 'Top.Collections.Pictures.Astronomy.Astronauts' );

CREATE INDEX ON foo USING gist(ltree);

SELECT *
FROM foo AS f1
WHERE NOT EXISTS (
  SELECT 1
  FROM foo AS f2
  WHERE f1.ltree <> f2.ltree
    AND f1.ltree @> f2.ltree
);

This uses the GIST index on the ltree. See the docs for more information on using @> with ltree.

I suggest not storing this on the table. First, it's maintenance nightmare. After every insert and update, you would have to potentially rewrite the parent nodes to update the leaf column. Moreover, if simply querying for this isn't fast enough, I would instead use a MATERIALIZED VIEW. At least then you know when the view is stale. Keeping this in sync with your data sounds like no-fun to me.


MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index

There are good reasons ALGORITHM=INPLACE may not be used and switches to ALGORITHM=COPY.
REASON #1
In the link you gave in the question, there is a chart that shows the following:

Adding a FULLTEXT index trigger a table copy is not supported for online operation
Many operations can revert to copy because it says in the notes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.

Some online DDL may still lock and copy because of an index's interaction. Don't forget that each BTREE entry in a secondary index has a hook back to the primary key. Adding a new secondary index may induce row level locks and index locks.
REASON #2
According the limitations page : InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY.
I am sure there are other reasons that can be researched an discussed along these lines.


I would suggest using Postgres as the database for a beginner. This is the most ANSI compliant one of the open source databases. Unlike MySQL, it wont teach you any bad habits.

Pretty much all languages support querying Postgres, MySQL and SQL Server - just pick the languages you want to learn.


Approach to take regarding a field that is frequently re-computed

I would add another table for versioning of data. It would have composite key of data and timestamp or version number and in table I would hold actual data.
When new data arrives in table data is updated and in versioning table is inserted.

Regarding vacuuming you set auto vacuum for that table.
If table gets bloated despite auto vacuum setting you can run vacuum full,
but it would fully lock your database.
If you are running on linux you can use pg_repack extension.


Oracle HIPAA/FIPS-Compliant Encrypted Connections

SSLFIPS_140 is used for SSL-based encryption. That is not related to native network encryption.

  Is Oracle native network encryption [1,2] HIPAA/FIPS compliant?

  If so, is any configuration required or is it compliant by default?

https://docs.oracle.com/database/121/DBSEG/asoappe.htm#DBSEG1061

  For native encryption, this behavior of cryptographic operations
  landing in RSA/Micro Edition Suite (MES) and being accelerated is
  similar to the above, except that it is determined by the FIPS_140
  setting in sqlnet.ora (instead of the SSL_FIPS140 setting in
  fips.ora).

This parameter is available since version 12.1.

  Are there certain encryption algorithms that are/are not compliant?

https://docs.oracle.com/database/121/DBSEG/asoappe.htm#DBSEG9828

  Only the following cipher suites are approved for FIPS validation:

  SSL_DH_anon_WITH_3DES_EDE_CBC_SHA
  SSL_RSA_WITH_AES_256_CBC_SHA
  SSL_RSA_WITH_AES_128_CBC_SHA
  SSL_RSA_WITH_AES_256_GCM_SHA384
  SSL_RSA_WITH_3DES_EDE_CBC_SHA

Could not find a list specifically for native encryption, but you can set the algorithms based on the above. After setting the above parameter, if you choose an algorithm that is not compliant, you can not connect to the database and will receive the below error:

ORA-12630: Native service operation not supported


I believe that if you run native SQL Server backups (using T-SQL BACKUP) instead of DPM protection agent-based SQL backups, the SQL Server VSS Writer service must not be used on your SQL Server database folders.  (The same problem is true for other third-party backup tools.)

This DPM/VSS service is not used by native SQL backups. If you prevent the DPM protection agent backups from triggering SQL Server VSS snapshots, this will protect your chain of log backups.

If DPM/VSS does continue to backup the SQL Server files, then the log chain is broken, because there are two tools (T-SQL BACKUP and DPM/VSS) backing up the log files.


Postgres one-liner import & export SQL without login & without asked for password

psql doesn't provide a command line option for the password to avoid disclosing it to other local users.

However it accepts the PGPASSWORD environment variable, so your equivalent of mysql's -p password is, in shell syntax:

PGPASSWORD=password psql -h localhost -d dbname -U postgres -f /var/www/db.sql

Alternatively, a client-side password file may be used.


optimize table in stored produce using phpMyAdmin causes out of sync error

For INNODB I was able to do the same thing and it didn't cause an error in php.

Here is what I did:

 ALTER TABLE phppos_items FORCE;


Multiple volumes vs single volumes for SQL Server CHECKDB performance

Is this what you're looking for?
https://techcommunity.microsoft.com/t5/sql-server-support/a-faster-checkdb-8211-part-ii/ba-p/316882
At the end of this blog Bob Ward says:
There are also some factors that can affect whether these changes actually help make your execution of CHECKDB faster:
...
Spreading files across disks – As with the general performance of server queries, if we are spreading out our reads across separate physical disks for files, we can achieve better I/O performance.


Build a Hyper-V template w/ SQL Server 2016 pre-installed

From https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server :

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, 'local';
GO

Restart the instance of SQL Server.

For a named instance:

sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, 'local';
GO

Restart the instance of SQL Server.

Note you cannot rename the instance part of the name via this method.


In Oracle (and other RDBMSs), when an SQL statement is run for the first time, it is cached and if somebody else wants to run a similar statement, the plan is taken from the cache - or shared pool. This is called soft parsing and is less costly than going to the trouble of redoing it entirely.

So, the term "construct" in this context means an in memory structure (like a "C" struct) which (ideally) allows the database to manage user requests better, like a plan for an already executed query.

Obviously, having a plan (construct) in memory will improve the server's response time - no need to read disks and recheck indexes &c. which take time. This process is an entire science in its own right.

From here, "The shared pool is like a buffer for SQL statements.  Oracle's parsing algorithm ensures that identical SQL statements do not have to be parsed each time they're executed.  The shared pool  is used to store SQL statements, and it includes the following components"...


You can execute below query.

DROP TABLESPACE xxx_undo_01 INCLUDING CONTENTS AND DATAFILES;


Expdp with metadata_only gives way too large dump. Why?

What version are you running? I'm not able to reproduce your results on 11.2.0.4. Here's my schema:

16:36:09 system@DB> select owner, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'USER' group by owner;

OWNER                                  GB
------------------------------ ----------
USER                           1156.41901

Here's the export statement: expdp system/x schemas=user directory=DATA_PUMP_DIR content=metadata_only logfile=test_meta.log

Here's the sizing on the dump file:

oracle@db01:(db1) ~]$ ls -ltrh /u01/exports/db/expdat.dmp
-rw-r----- 1 oracle oinstall 25M Nov 20 16:40 /u01/exports/db/expdat.dmp

Make sure you're using SYSTEM to do exports, as sysdba privs are not recommended for running data pump (see Invoking Data Pump Export. I wonder if that might be the problem.


Are postgres connection able to manage multiple requests at the same time?

No, you can only have a single statement executing at the same time on a PostgreSQL connections.
PostgreSQL v14 introduces pipelining of SQL statements, but that still means that one statement will execute after the other. Besides, I doubt that Npgsql already has support for that.
Use several database connections.


Using only vanilla PostgreSQL, how do I predict a future number based on existing data?

Besides the point that there is literally a whole branch of mathematics which is only focusing on answering your question, which should give you a hint that there is no "silver bullte" easy formula to use in any case.

WARNING I only have a basic understanding of statistics but I know that you cannot use the mean as a reliable value to predict future values. In almost all, if not all, cases a prediction depends very much on the distribution type of your events. This mean value will only get you a rough estimate which might be better than selecting a random number.

Anyway here is my implementation of a mean timestamp between two events:

Create table eventsTable(
  id int,
  TS timestamp);

Insert into eventsTable VALUES (1, '2020-05-07 08:00');
Insert into eventsTable VALUES (1, '2020-05-07 08:05');
Insert into eventsTable VALUES (1, '2020-05-07 08:10');
Insert into eventsTable VALUES (1, '2020-05-07 08:15');
Insert into eventsTable VALUES (1, '2020-05-07 08:20');
Insert into eventsTable VALUES (1, '2020-05-07 08:25');

SELECT
  EXTRACT(EPOCH FROM (Max(TS) - Min(TS))) / ( COUNT(*)  -1) as Mean_Number_Of_Seconds_Untill_Next_Event
FROM eventsTable
WHERE TS > '2020-05-07 08:10'

Fiddle link

It just takes the maximum and minimum value and divides it by the number of new events in that time interval. Use the where clause to find a good time interval for performance and prediction correctness.


Risks involved in upgrading to SQL Server 2008 R2

That's a really big question so let's break it up a bit.

What can I do in advance?

Start with some required reading.

2008 R2 Backwards Compatibility
2008 Backwards Compatibility

These links have links to further information such as

Deprecated SQL Server Features
Discontinued SQL Server Features
Breaking Changes
Behavior Changes to SQL Server Features

Read each of them to see what major stuff is changing. Pay particular attention to features you are using.

In addition you should use the Upgrade Advisor.  It checks for installed components and identifies those you will need to fix either before or after the install.

In-Place vs Side-by-side

Lots of pro's and con's here on both sides.

In-Place

Pros

MUCH easier. All of your configurations stay the same for example. Also the connection strings for your applications probably won't need to be changed.
Cheaper. No second set of hardware required.

Cons

Backout is difficult to impossible.  If something goes wrong you are going to have to power through and finish because backout involves creating a whole new server and reinstalling SQL then restoring backups of your tables.

Side-by-side

Basically the pros & cons are the opposite of In place.

Pros

Safer - If something goes wrong you kill the new version and just continue on with the old one.  Then you can try again later.

Cons

It's more expensive because you have to create a new set of instances probably on new servers.
It's more difficult because you have to change connection strings, make sure all of your configurations are the same, etc.

Now you can mitigate the expense of the side-by-side by creating a new instance on the same server, moving everything to it, then uninstalling the old instance. It works and depending on your situation might be the best idea.

General Risk

Honestly the move from 2005 - 2008 R2 isn't that bad.  It's nothing compared to 2000 - 2005 or 2008 R2 - 2012 (mostly SSIS changes).  I'd say with careful planning and reading you should be in good shape.


Technically, you could create a Java stored procedure or a dbms_scheduler external job that you invoke from your APEX process.  Doing so, however, would almost certainly be a mistake.

First, you'd have the issue of concurrency.  If you did invoke your Python script from within your APEX process, for example, that process would start immediately and would not wait for your transaction to complete.  Assuming you made a synchronous call, when your script ran and accessed the database, the row you inserted into the request table would not be visible because your transaction would not have committed yet.  If you made an asynchronous call, the row might or might not be visible because the transaction may or may not have been committed yet.  Additionally, your APEX process might encounter an error later on in some other page process that would cause the transaction to be rolled back which would also cause the script not to be able to see the row.  Breaking up the transaction so that you commit before calling your script would ensure that the row was visible but would probably create a number of other data consistency and data quality issues in your application.

It would almost always make more sense to implement some sort of queuing mechanism instead.  Whether you use Oracle AQ or whether you just write into the request table and have separate job that reads from the table and runs your Python code is less important than the existence of some sort of queue.  That decouples the two pieces of code-- the code that writes requests can have proper transactional boundaries while the code that reads requests doesn't have to worry about trying to process requests that haven't been committed yet or that may have been rolled back.  That is not convoluted and fragile-- that is a much more robust approach.  Yes, you potentially need to monitor the job that is processing requests (you may be able to rely on existing monitoring of the Oracle alert log or of the log of whatever scheduling tool you already use).  But that's generally much easier than trying to debug why some requests didn't get processed or why the external script keeps failing to find rows.


The size reported by psql -l includes the size of all indexes.

The dump only contains the definition of the indexes, but no data for them, that's why the dump file is (substantially) smaller then the reported size on disk.

In addition to the size of the indexes, the dump only contains the raw data, while the size on disk might include table bloat (free space inside the table's data files)


Not able to start mysqld as windows service as well as from command line in windows 8

Connecting from the localhost shouldn't be an issue, but edit your "C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" file and change the bind-adress from 127.0.0.1 to the external IP address. Also, set mysql skip-networking to off. Finally, set a rule on windows firewall to allow connections on port 3306.


Advice to set minimum preferable value for MEMORY_TARGET

This depends on many factors. The OEM advisor is not the smartest. Your minimum requirements will be somewhere between the minimum size that allows your database to start and what you have now.
Smart would be to check the size of the shared_pool components after the instance has been running a few days. Less memory will cause problems because allocations from the shared pool will fail and it causes a lot of additional parsing.
Next to that, add memory meant for caching database blocks. More blocks means more caching and most of the time this means a better performance. There is a point where adding more memory no longer improves the performance.
Most of the times this is below what the OEM memory advisor tries to make you believe. Don't forget that Oracle is also a hardware selling company ....
If performance is no issue, the minimum requirement is the size calculated for the shared pool plus a few megabytes. If performance is important add a few GB to that and monitor your applications performance.


Should i use subquery to limit table before a join?

The answer to your question depends on the specific database and version you are using. In any case most current databases will optimize the query and end-up having the same execution plan for all cases.

I'd go for the simplest syntax that clearly states your purpose. It is probably the one that the database will be able to best optimize. And, specially, it is the one that will be easier to interpret by yourself, or anyone modifying any application later on. If you find out that some query seems to underperform, then you should check execution plans, alternatives, and find out if it can actually be improved.

You can check it with most DBs with a statement similar to EXPLAIN SELECT * FROM Customers NATURAL JOIN Orders WHERE shipped=1 and check what the execution plan is.

This is how you would do it when using PostgreSQL:

Creation and population of (mockup) tables:

 CREATE TABLE Customers
 (
     customer_id integer primary key,
     customer_name text not null
 ) ;

 CREATE TABLE Orders
 (
     order_id integer primary key,
     customer_id integer NOR NULL REFERENCES Customers(customer_id),
     whatever text,
     shipped boolean
 ) ;

 -- We invent 1_000 customers
 INSERT INTO
     Customers (customer_id, customer_name)
 SELECT
     i, 'Name ' || i
 FROM
     generate_series (1, 1000) as s(i) ;

 -- and 25_000 orders
 INSERT INTO
     Orders (order_id, customer_id, whatever, shipped)
 SELECT
     i AS order_id,
     1 + 999*random() AS customer_id,
     'a text' AS whatever,
     (random() < 0.85) AS shipped
 FROM
     generate_series(1, 25000) AS s(i) ;

Make sure the database has proper statistics:

ANALYZE Orders;
ANALYZE Customers;

Check for the exeuction plan of the simplest query:

EXPLAIN ANALYZE
SELECT * FROM Customers NATURAL JOIN Orders WHERE shipped

 | QUERY PLAN                                                                                |
 | :-------------------------------------------------------------------------------------------------------------------- |
 | Hash Join  (cost=28.50..705.05 rows=21131 width=24) (actual time=0.946..29.305 rows=21131 loops=1)                    |
 |   Hash Cond: (orders.customer_id = customers.customer_id)                                                             |
 |   ->  Seq Scan on orders  (cost=0.00..386.00 rows=21131 width=16) (actual time=0.008..10.089 rows=21131 loops=1)      |
 |         Filter: shipped                                                                                |
 |         Rows Removed by Filter: 3869                                                                                |
 |   ->  Hash  (cost=16.00..16.00 rows=1000 width=12) (actual time=0.906..0.906 rows=1000 loops=1)                       |
 |         Buckets: 1024  Batches: 1  Memory Usage: 52kB                                                                 |
 |         ->  Seq Scan on customers  (cost=0.00..16.00 rows=1000 width=12) (actual time=0.005..0.445 rows=1000 loops=1) |
 | Planning time: 0.419 ms                                                                                |
 | Execution time: 34.613 ms                                                                                |

Check for the execution plan of the second version of the query:

 EXPLAIN ANALYZE
 SELECT * FROM Customers NATURAL JOIN (SELECT * FROM Orders WHERE shipped) AS O

 | QUERY PLAN                                                                                |
 | :-------------------------------------------------------------------------------------------------------------------- |
 | Hash Join  (cost=28.50..705.05 rows=21131 width=24) (actual time=0.693..24.537 rows=21131 loops=1)                    |
 |   Hash Cond: (orders.customer_id = customers.customer_id)                                                             |
 |   ->  Seq Scan on orders  (cost=0.00..386.00 rows=21131 width=16) (actual time=0.007..8.534 rows=21131 loops=1)       |
 |         Filter: shipped                                                                                |
 |         Rows Removed by Filter: 3869                                                                                |
 |   ->  Hash  (cost=16.00..16.00 rows=1000 width=12) (actual time=0.676..0.676 rows=1000 loops=1)                       |
 |         Buckets: 1024  Batches: 1  Memory Usage: 52kB                                                                 |
 |         ->  Seq Scan on customers  (cost=0.00..16.00 rows=1000 width=12) (actual time=0.003..0.312 rows=1000 loops=1) |
 | Planning time: 0.263 ms                                                                                |
 | Execution time: 29.083 ms                                                                                |

You can see that, in this case, PostgreSQL (9.6.2) uses exactly the same execution plan. This is what will happen most often.

dbfiddle here

A slightly different version, where one index is defined (and where we decided that shipped orders were minority, not majority):

CREATE INDEX idx_shipped_orders ON Orders(shipped, order_id) ;

... works with a different plan, but again, the same execution plan for both the simple and the shipped filtered first queries:

 | QUERY PLAN                                                                                |
 | :--------------------------------------------------------------------------------------------------------------------------------------- |
 | Hash Join  (cost=28.78..214.66 rows=3822 width=24) (actual time=0.750..5.985 rows=3822 loops=1)                                          |
 |   Hash Cond: (orders.customer_id = customers.customer_id)                                                                                |
 |   ->  Index Scan using shipped_orders_idx on orders  (cost=0.28..133.61 rows=3822 width=16) (actual time=0.008..1.873 rows=3822 loops=1) |
 |   ->  Hash  (cost=16.00..16.00 rows=1000 width=12) (actual time=0.730..0.730 rows=1000 loops=1)                                          |
 |         Buckets: 1024  Batches: 1  Memory Usage: 52kB                                                                                |
 |         ->  Seq Scan on customers  (cost=0.00..16.00 rows=1000 width=12) (actual time=0.008..0.345 rows=1000 loops=1)                    |
 | Planning time: 0.209 ms                                                                                |
 | Execution time: 6.863 ms                                                                                |

dbfiddle here

NOTE: The small differences in execution time can be due to any external factors, such as other processes using the database at the same time or not. They should only be taken into consideration if you perform the queries a statistically significant number of times, and perform the adequate statistical test for significance.

NOTE 2: I've used the NATURAL JOIN as you did, to not introduce more elements to the analysis. In practice, I'd rather use either ON t1.col = t2.col (compatible with mostly all databases) or USING (col). The meaning of both remains constant. NATURAL JOIN can change meanings if you add columns to your tables. For instance, you might decide to add columns for last_modified_at, last_modified_by, to track the "age" of your data ... and your NATURAL JOINs stop working. In short: don't use them. NATURAL JOIN does not refer to joining using the columns participating in a foreign key constraint, as you might have thought. They just refer to columns having the same name in both tables. And that's risky. I have plenty of tables with columns called created_at and last_modified_at, for instance, and it doesn't make any sense to use them to JOIN.


why there is no performance differences if I use where clause inside of inner join?

BigQuery reduces how much data is scanned in a couple scenarios:

You have a partitioned table, and you filter on the partitioning column.
You have a clustered table, and you filter on one of the columns used for clustering.

For other types of filters, however, such as if you don't partition or cluster a table, you will incur the cost of scanning the entire table. See the query pricing documentation for more information.


Postgres SELECT query produces exact same explain analyze results but different execution times in the results

You can create an index that is specifically designed for the query:
CREATE INDEX ON product (product_id, item_created_on)
   INCLUDE (item_cash_staked, item_cash_won)
   WHERE item_rejection_code_id IS NULL;

VACUUM product;

That should get you and index-only scan.
With old PostgreSQL versions, you can add the columns to the index instead:
CREATE INDEX ON product (product_id, item_created_on, item_cash_staked, item_cash_won)
   WHERE item_rejection_code_id IS NULL;

VACUUM product;


You want to do something like this (see fiddle here):
CREATE TABLE test
(
  t_id INTEGER PRIMARY KEY,
  x VARCHAR (50),
  seq_num INTEGER NOT NULL
);

INSERT INTO test VALUES (1, 'INS. Company Cancelled - 127', 127);
INSERT INTO test VALUES (2, 'INS. Company Cancelled - 128', 128);
INSERT INTO test VALUES (3, 'INS. Company Cancelled - 126_1', 126);
INSERT INTO test VALUES (4, 'INS. Company Cancelled - 126_2', 126);

The record below has 126 as the seq_num and the fragment INS.  isn't at the beginning of the target string:
-- Note: 126 as seq_num!
--
-- BUT, also note that the `INS.` isn't at the beginning of the string!
--

INSERT INTO test
VALUES
(5, 'stuff... ___INS. Company Cancelled - 126', 126);

Then:
SELECT REPLACE(x, 'INS. ', 'INSEXP. ') AS "Check:" FROM test;

Result:
Check:
INSEXP. Company Cancelled - 127
INSEXP. Company Cancelled - 128
INSEXP. Company Cancelled - 126_1
INSEXP. Company Cancelled - 126_2
stuff... ___INSEXP. Company Cancelled - 126

The problem here is that the substring INS.  is being replaced everywhere seq_num is 126 whereas we only want it done where INS.  occurs at the beginning of the target string!
So, now we try REGEXP_REPLACE:
SELECT
  REGEXP_REPLACE(x, '(^INS)(\. )', '\1EXP\2') AS "Check:"
FROM test
WHERE seq_num = 126;

Result:
Check:
INSEXP. Company Cancelled - 126_1
INSEXP. Company Cancelled - 126_2
stuff... ___INS. Company Cancelled - 126

So, we can see that REGEXP_REPLACE changes the first two INS.  substrings, but not the last one - where it doesn't occur at the beginning of the string!
So, now for the UPDATE:
UPDATE test t SET t.x = REGEXP_REPLACE(t.x, '(^INS)(\. )', '\1EXP\2')
WHERE t.seq_num = 126;

And then (to check - always check!):
SELECT t.* FROM test t ORDER BY t.t_id;

Then:
T_ID                                   X       SEQ_NUM
   1    INS. Company Cancelled - 127               127
   2    INS. Company Cancelled - 128               128
   3    INSEXP. Company Cancelled - 126_1          126
   4    INSEXP. Company Cancelled - 126_2          126
   5    stuff... ___INS. Company Cancelled - 126   126

Et voilà! We have replaced the correct substring from the correct part of the target string - all due to the power of regular expressions!
You should also look into the REGEXP_REPLACE() functionality (the oracle-base site is excellent for all things Oracle!). I've only recently started mastering them and it's amazing what they can let you do in a one-liner that would take tons of code otherwise.
Explanation of the regex:
Now, here we specify the beginning of the string using the ^ anchor, so the replacement only takes place at the start of the string x - this specificity can be very helpful (indeed critical, as we can see in this case) when substituting text.
The ( and ) brackets are for "capturing groups" of text - so we can refer to <start_of_string> followed directly by INS followed by . (dot is also a special character - it's a single character wildcard, so it's escaped by \).
So, the group (^INS) is represented by the place-holder \1 - remember the backslash is a special character in regexps! And the (\. ) group represents a . (full stop or period) followed by a space - because . without the backslash is a wildcard for one character in regexps!
Caveat: There will be a performance penalty to pay here - naturally enough - more complex functionality requires more processing, but I would strongly urge any database programmer/DBA &c. to master regular expressions - there are many tutorials out there.
As an aside (and this is more for me really), in exploring this issue, I came across this page (again, from Tim Hall's fabulous oracle-base site) - it explains 3 different ways of updating from queries!
The one I liked the best is the MERGE (3rd method) functionality (fiddle):
MERGE INTO test tt
  USING test st
  ON (tt.t_id = st.t_id AND tt.seq_num = 126)
WHEN MATCHED THEN
  UPDATE SET tt.x = REGEXP_REPLACE(st.x, '(^INS)(\. )', '\1EXP\2');

The end result is the same!
In this snippet, tt = target_table & st = source_table - of course, in this case, they're both the same and the simpler code above works - but if you're going to be UPDATEing from table_1 to table_2, you'll need mechanisms like this! A fiddle for this code is to be found here.
My second favourite was what Oracle calls an Inline View (but most of us normal folks call a sub-query) - it looks like this (fiddle here):
UPDATE
  (SELECT target_table.t_id, target_table.x, target_table.seq_num,
          REGEXP_REPLACE(source_table.x, '(^INS)(\. )', '\1EXP\2') y
          FROM test target_table, test source_table
          WHERE target_table.t_id = source_table.t_id
          AND target_table.seq_num = 126) ilv

  SET ilv.x = ilv.y;

And finally, there is what the page (and Oracle?) calls the sub-query method - it  uses the EXISTS predicate thus (fiddle):
UPDATE test t1
  SET (t1.x) =
  (
    SELECT REGEXP_REPLACE(t2.x, '(^INS)(\. )', '\1EXP\2')
    FROM test t2
    WHERE t1.seq_num = 126 AND t1.t_id = t2.t_id
  ) WHERE EXISTS (SELECT t.t_id, t.seq_num FROM test t WHERE t.t_id = t1.t_id
                                                       AND t.seq_num = 126);

Interestingly, there is also a performance analysis after each one, which suggest to me that none is the best in all cases and therefore we should test (with realistic datasets) our queries for performance before implementing them in production (don't we always? :-) )!
I'm giving a +1 to the question because I learned a lot from answering it - I hope you did too!


How to prevent/detect fetching data from another database on same server

To find objects in the database (views, procedures etc) that use 3 (or 4) part naming, you can use the scripts in this article to search the SQL Dependencies in the database.
SELECT OBJECT_SCHEMA_NAME(objects.object_id)
                            AS referencing_schema_name,
       objects.name AS referencing_object_name,
      objects.type_desc referencing_object_type,
      CASE WHEN referenced_database_name = DB_NAME()
                  AND referenced_server_name IS NULL
            THEN 'Internal' ELSE 'External' END
                        AS referenced_database_location,
COALESCE(sql_expression_dependencies.referenced_server_name,
         '<localserver>') AS referenced_server_name,
       sql_expression_dependencies.referenced_database_name,
       sql_expression_dependencies.referenced_schema_name,
       sql_expression_dependencies.referenced_entity_name
                                    AS referenced_object_name
FROM sys.sql_expression_dependencies
        JOIN sys.objects
            ON objects.object_id =
                   sql_expression_dependencies.referencing_id
WHERE referenced_database_name IS NOT NULL
ORDER BY referencing_schema_name,referencing_object_name

There is also a script on that article for finding dynamic SQL in objects in the database. This doesn't mean they're using 3-part naming, but at least gives you a starting point for your searching.
As for the code in C#, that is going to be more difficult to find. You might be better placed to take a copy of your production DB and restore it to a non-production server that doesn't have the Test DB on it, then run your application against that database (which is now technically non-production). With the Test DB missing, you should get errors wherever you have references to the Test DB in application code.
You can then iteratively resolve each error and progress further with the application until you've got it working without errors. You could also setup up an Extended Events session filtered by user and database (Test DB) to record sqlserver.sql_statement_completed events. Periodically check this events session to see any queries executing against the Test DB by your production user to further identify problem queries and resolve.
Lastly, as a general best practice - take Test DB off your production server and put it somewhere else. This will guarantee that cross database queries to Test DB will fail (because it won't exist on production) and help prevent these kind of cross-contamination issues between environments. It also means you can treat your production environment as a true production, so the next time you have an external contractor come in to write reports/applications, they can develop in the test environment, and any issues with hard-coded DB or server names will become immediately apparent during deployment.


Domain Admin on AWS lacking user rights to edit the top 200 using the Management Studio interface

OK, I think the Work Around mentioned at serverfault.com/questions/696804/... is a very valid answer so I am listing it directly here.  I have the exact same issue in Microsoft Azure (editing a table SQL Management Studio).

I fixed it by setting DWORD regkey ProtectionPolicy to 1 for the sub-key HKEY_LOCAL_MACHINE\Software\Microsoft\Cryptography\Protect\Providers\df9d8cd0-1501-11d1-8c7a-00c04fc297eb


Mysql Update columns from same table columns


Convert SQL Server results to a text file that PowerShell can access and use

This answer assumes multiple comma's are in a single row of the ServerName column, and these need to be removed & Line feed + Carriage returns added instead of each comma.

There could be better ways to get your endresult, but to write to a text file without the comma's, you could use the REPLACE() function.

CHARINDEX() will only give you the location of the first value found.

CREATE TABLE [TempPSWmiObjectQuery](QueryText nvarchar(max));
INSERT INTO [TempPSWmiObjectQuery]
SELECT  REPLACE(ServerName,',',+ ' '+char(13)+char(10) )  -- Replace comma with space, line feed and carriage return.
FROM MasterList;

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

EXEC xp_cmdshell 'bcp SQLEnv.dbo.TempPSWmiObjectQuery out "\\ServerName\Data\temp\testlist.txt" -c -T -S "ServerName\Instance"';

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

DROP TABLE TempPSWmiObjectQuery;

Test

INSERT INTO MasterList(ServerName)
VALUES('Server1,Server2,Server3')

Result

Change SELECT REPLACE(ServerName,',',+ ' '+char(13)+char(10) ) to
SELECT REPLACE(ServerName,',',+ ' ' ) if you don't want new lines.


I would convert that value to an integer array, then sort on that array:
SELECT number
FROM orders
ORDER BY string_to_array(number, '-')::int[]
LIMIT 10;

Online example


"Deploy Database to SQL Azure" Task missing

Microsoft has changed the procedure. What you need to do is Export/Import a Data-tier Application via a BACPAC file. Microsoft explains it here (in the section Backup Package (.bacpac):
http://msdn.microsoft.com/en-us/library/ee210546.aspx

Here it is in a nutshell:

Use SQL Server Management Studio 2014
Right-click your local database name, then Tasks / Export Data-tier Application
Follow the wizard to create a BACPAC file on your local disk. This will contain your
complete DB schema as well as your data.
In SSMS, connect to your existing Azure SQL account (tcp:xxxxxxxxxx.database.windows.net,1433)
Under the Azure account, right-click on the word Databases and
select Import Data-tier Application
Using the wizard, import your BACPAC file "from local disk" When it is finished, you will have an Azure-based replica of your original database, schema, data and all.


Adding a sequence number to existing rows

You can accomplish this using ROW_NUMBER() syntax, as mentioned by @Kin :

USE Tempdb;
CREATE TABLE dbo.testRowNum
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Name VARCHAR(255)
    , SequenceNum INT NULL
);
INSERT INTO testRowNum (Name) VALUES ('A'), ('B'), ('C');

SELECT *
FROM dbo.testRowNum;

UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum
    INNER JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM dbo.testRowNum) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;

SELECT *
FROM dbo.testRowNum;

Output:

If you want only update certain rows, you can use a Table Variable (or a #temp table if you are updating a LOT of rows), as such:

DECLARE @IDsToUpdate TABLE
(
    ID INT
);

INSERT INTO @IDsToUpdate VALUES (1),(3);

UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum
    INNER JOIN (
        SELECT t.ID
            , ROW_NUMBER() OVER (ORDER BY t.ID) AS RowNum
        FROM dbo.testRowNum t
            INNER JOIN @IDsToUpdate i ON t.ID = i.ID
        ) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;

The example above results in the following output:

An improvement (based on a suggestion by Daniel Hutmacher) avoids the join and results in a more efficient query plan (no join or Halloween protection):

WITH Numbered AS
(
    SELECT
        SequenceNum,
        RowNum =
            ROW_NUMBER() OVER (
                ORDER BY ID)
    FROM dbo.testRowNum
)
UPDATE Numbered
SET SequenceNum = RowNum;

Or, when using the @IDsToUpdate table:

WITH Numbered AS
(
    SELECT
        SequenceNum,
        RowNum =
            ROW_NUMBER() OVER (
                ORDER BY ID)
    FROM dbo.testRowNum
    WHERE EXISTS
    (
        SELECT *
        FROM @IDsToUpdate AS I
        WHERE I.ID = dbo.testRowNum.ID
    )
)
UPDATE Numbered
SET SequenceNum = RowNum;


What's the best way to track Updated By, Updated On

1) Your trigger idea is good: we use it sometimes.

It is foolproof mostly except:

issues you mentioned
you don't always have SUSER_SNAME if folk connect via a web server or middle tier

You can mitigate this with SET CONTEXT_INFO to control admin behaviour, or more carefully to pass in some end user details.

2) All writes via stored procedures. We use this method mostly.

We also use defaults on UpdatedOn so...

UPDATE
   ...
   UpdatedBy = whoever, UpdatedOn = DEFAULT

3) Let your client do it. I won't take this any further...

Finally, we implemented "AffectedBy", and "AffectedOn" fields to track admin/indirect updates.


Track database name in anemometer queries

(Until I get answers to my comment, here is all I can suggest...)

1. LEFT and the two tests on wp_postmeta have no impact on the outcome; get rid of them.

Get rid of wp_postmeta all together.  And get rid of the GROUP BY, which was probably a byproduct of the JOIN.
INDEX(post_type, post_status, post_date)

That leaves you with just

select  *
    from  wp_posts
    where  post_type = ?
      and  post_status = ?
    order by  post_date desc

Does that give you the right answer?


What is the best database design for this situation?

A rule of thumb I use, is that it's bad database design to have to alter a table to accomodate a code change or a new feature if it can be planned for in the future.

That said, I would use a variant of Option 1

Approvals
    Id PK
    ApprovalTypeID FK
    Status

ApprovalComments
    ID PK
    ApprovalId FK
    Comment

ApprovalTypes
    ID PK
    Name

Now when adding new types of objects that need approval, you only need to insert a row into ApprovalTypes instead of altering a table to add a column.


Sync database table schema and stored procedures

SQL Server Data Tools (SSDT) has the ability to do just this. It can compare a source and target database, determine the differences between them for many classes of object including tables and executables. It can produce a script to make the target look like the source. You get to choose which changes to include and which to ignore.

It is not magic, however. If you had new column and declare it as NOT NULL in the source, then try and apply that to a target table that contains data it will fail because no default is specified. Be sure to properly account for this and similar circumstances.


How to install MySQL server on Mac (OS X 10.6.8) from command line?

If you have homebrew installed, then :

brew install mysql

Else install homebrew it might be useful also to install other projects


Howto (async?) preview intermediate resultset

What you describe is the default behaviour of SQL Server's query execution and client connection protocols. Rows are delivered to the server's network buffer as they are produced from an executing query plan. The network delivers them to the client, which makes them available to the application.

I think the pause you see is caused by how the application consumes the dataset returned. It's been a while, but I think SqlDataReader.Read() allows stream-like behaviour.

The risk is that the application falls behind SQL Server and the connection and server resources remain locked up until the application explicitly releases them.


Exception when starting Snapshot Agent when setting up database replication

I finally got it resolved. The issue was missing OLE DB driver.
I just downloaded latest driver. Then I got msoledbsqlr.rll driver in directory C:\Windows\SysWOW64\1033. Then I copied to C:\Program Files\Microsoft SQL Server\150\COM directory, so it can be found by snapshot.exe, which is executable run by Snapshot Agent.
Alternatvely, one can define environmental variable to point to C:\Windows\SysWOW64\1033 direcotry, so it will be found there.
Another issue might be that 64-bit SQL tries to use 32-bit drivers (msoledbsql.dll, msoledbsqlr.rll), but it wasn't issue in my case.


Reduce SQL Server Memory used by a Database

Your best option is going to be using Resource Governor. Just create a resource for that vendor app and throttle the memory down for it.

You just have to come up with what you are going to identify that workload with, you are allowed:

The ability to classify incoming connections and route their
workloads to a specific group.
The ability to monitor resource usage for each workload in a group.
The ability to pool resources and set pool-specific limits on CPU
usage and memory allocation. This prevents or minimizes the
probability of run-away queries.
The ability to associate grouped workloads with a specific pool of
resources.
The ability to identify and set priorities for workloads.


Before launch the VACUUM FULL, is there a another method for the
  resolution of this problem?

VACUUM FULL does not solve that problem, in the sense that if the files you found inside the database directory do not corresponding to anything in the catalog, they will be ignored by VACUUM FULL and their space will not be reclaimed.

Such files are left out when a backend is stopped abrubtly (fast shutdown or crash) during the transaction that creates the objects, and the crash recovery sequence is not able to clean up.
In that case, only manual deletion will reclaim that space.

Before removing the files you want to be absolutely sure of your query. Right now, it's based on relkind='r', and altough it gets back to indexes and toast tables indirectly, it seems to miss materialized views or sequences.

See How to reclaim space taken by an index that partially built and was terminated by a power outage
for an approach that worked. That question was about an index, but it applies to other kind of objects.


Aggregating results of two database views in one database view

You want to use the UNION ALL operator, rather than trying to JOIN on Username.

A UNION ALL returns a combination of all results from Query A and Query B.

Alternatively, if you only wanted DISTINCT records from the set, you could omit the ALL keyword and just do a UNION.

SELECT
    UserID,
    Username,
    OperationFaName,
    OperationEnName,
    ComponentName,
    AccessString,
    ApplicationName,
    ApplicationID
FROM UserInfo

UNION ALL

SELECT
    UserID,
    Username,
    OperationFaName,
    OperationEnName,
    ComponentName,
    AccessString,
    ApplicationName,
    ApplicationID
FROM UserRoleInfo


Postgres: find all FK constraints associated with a given PK

Does the following query work for you?
SELECT nr.nspname AS table_schema,
        r.relname AS table_name,
        c.conname AS constraint_name,
        split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 2 ), ')', 1 ) AS column_names,
        nr2.nspname AS r_table_schema,
        r2.relname AS r_table_name,
        split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 3 ), ')', 1 ) AS r_column_names,
        CASE c.confmatchtype
            WHEN 'f' THEN 'MATCH FULL '
            WHEN 'p' THEN 'MATCH PARTIAL '
            WHEN 'u' THEN 'MATCH NONE '
            ELSE ''
            END
            || CASE c.confupdtype
                WHEN 'c' THEN 'ON UPDATE CASCADE '
                WHEN 'n' THEN 'ON UPDATE SET NULL '
                WHEN 'd' THEN 'ON UPDATE SET DEFAULT '
                WHEN 'r' THEN 'ON UPDATE RESTRICT '
                WHEN 'a' THEN 'ON UPDATE NO ACTION '
                ELSE ''
                END
            || CASE c.confdeltype
                WHEN 'c' THEN 'ON DELETE CASCADE'
                WHEN 'n' THEN 'ON DELETE SET NULL'
                WHEN 'd' THEN 'ON DELETE SET DEFAULT'
                WHEN 'r' THEN 'ON DELETE RESTRICT'
                WHEN 'a' THEN 'ON DELETE NO ACTION'
                ELSE ''
                END AS constraint_rule,
        d.description AS comments
    FROM pg_class r
    INNER JOIN pg_namespace nr
        ON ( nr.oid = r.relnamespace )
    INNER JOIN pg_constraint c
        ON ( c.conrelid = r.oid )
    LEFT OUTER JOIN pg_description d
        ON ( d.objoid = c.oid )
    INNER JOIN pg_catalog.pg_class r2
        ON ( r2.oid = c.confrelid )
    INNER JOIN pg_namespace nr2
        ON ( nr2.oid = r2.relnamespace )
    WHERE r.relkind = 'r'
        AND c.contype = 'f'
        --AND nr.nspname = ?
        --AND r.relname = ?
        AND nr2.nspname = ?
        AND r2.relname = ?


If your big tables are innodb, you can use --single-transaction to not lock the table.

Other possibility is Percona XtraBackup

Or pt-archiver (again Percona)


The export utility will use the NLS_LANG environment variable specified for the client session.  If all your data can be represented in the Windows-1252 character set, that shouldn't be an issue.  If you want to do the export using the AL32UTF8 character set, you'd need to set the NLS_LANG.  In Windows, that would be something like

c:\> set nls_lang = american_america.al32utf8


Move (Restore) SQL Server 2005 database which does not exist before

No, for a restore you don't have to have the database existing already (be mindful though to not have the with replace option set in your restore database command).

The below statement would create a new database from the backup:

RESTORE DATABASE [YourNewDbName]
FROM  DISK = N'C:\YourBackupFile.bak'
WITH  FILE = 1,
    MOVE N'YourDataFile' TO N'C:\YourNewDbName.mdf',
    MOVE N'YourLogFile' TO N'C:\YourNewDbName_1.ldf',
    NOUNLOAD,
    STATS = 10
GO

Obviously, modify the parameters accordingly.  But this doesn't require YourNewDbName to exist prior to the restore database.


Escape a quote in SSIS Flat File Conn Manager

First, I'll assume you can't simply ask for a correctly formatted csv file. If the last name should really be Harkin", then the entry in the csv file should have escaped the double quote character by doubling it (i.e., "Harkin"""). If this is a recurring process, then the best solution will definitely be to require a correctly formatted file.

I don't know of a good way to force SSIS to read in an incorrectly formatted file and figure out how it should be formatted.

If I were in your shoes, I'd try one of two approaches:

Repair the file manually: Open the file up with a text editor, preferably one that can handle regular expressions. Replace the string "," in the file with a unique token (say, --COLBREAK-- - anything that won't be in your data will work [check first if in doubt]). Then search for double quotes between two characters (i.e., any remaining double quotes not at the start and end of the line). Check these, if appropriate replacing " with "". you might also want to confirm that all lines have the expected number of column breaks, to make sure that you don't have the ","embedded in an existing string, or rows that are missing columns altogether. When done, change your token back to "," and try importing the file again.
Load the data in two phases: If there too much data to manipulate in an editor, rewrite your initial load process to import the entire row as one column. You can process this data in SSIS or load it to a SQL table. Next, check for valid rows (start with ", have "," twice, end with "; and, does not have seven or more instances of " in it), and write those rows to a "valid" file. Write the rest of the rows to an "invalid" file. Process the "valid" file as you originally planned; again, manually resolve the rows in the "invalid" file so they are valid, then process them as well.


Clustered Index vs Primary Key Performance

A clustered index will dictate the order of the data in the pages of your table. When your temp table is initially populated, the inserted data will be sorted or, if you're doing multiple inserts to populate, it's possible you will end up with some page fragmentation as the pages must split to accommodate additional data being written.

Assuming your temp table is populated in a single insert, and you have no page fragmentation, the next issue that may or may not affect your performance is the presence of a 4 byte "uniqueifier." Because the clustered key is used as a pointer to each row in the table, it must be unique for that purpose. So if you're using a non-unique clustered index, every nonclustered index (and your clustered index) is going to be 4 bytes wider than you've defined which will affect page density and IO required to perform the query. This will likely only be of very marginal impact as the uniquifier will only be added to duplicates.

The other factor that you need to consider is, how are you joining to this temp table. SQL Server supports 3 physical join operators; Merge, Loop, and Hash. Merge can yield the best performance in many situations.

  Unlike the nested loops join where the total cost may be proportional
  to the product of the number of rows in the input tables, with a merge
  join each table is read at most once and the total cost is
  proportional to the sum of the number of rows in the inputs.  Thus,
  merge join is often a better choice for larger inputs.

It is unlikely SQL will chose a merge operator unless both datasets being joined are sorted by the join criteria. There are other factors as well, of course, but if you can optimize your table for specific, common usage patters, all the better.

The second thing to consider is any other indexes you are going to place on that table since each nonclustered index will contain the keys specified (in the order specified) and the pointer to the clustered index to facilitate the lookup and retrieval of any fields not covered by the index. Again, a narrow clustered key will yield better density in your non-clustered indexes and thus better i/o performance for index scans and seeks. If your nonclustered indexes require many lookups to the clustered index either consider including those columns in your index (sacrificing page density for reduced lookups) or consider clustering on that criteria (if this is your primary usage pattern)

The biggest factor that would steer me towards using a primary key over a unique (or non-unique clustered index) would be the ability to create the index inline in the create table statement. Historically, I have found that the optimizer is less apt to use indexes that I created inside my stored procedure after the create table statement. It's easier to create a primary key inline with the create table statement. I'm not sure if it's possible to do this with a non-constraining index but you can create a unique clustered index inline.


Is there a PPA for MySQL 5.5 for ubuntu 10.10 and 11.10?

Funny thing. Someone in another post suggested going to this URL:

https://launchpad.net/~nathan-renniewaldock/+archive/ppa

I got that link from this DBA StackExchange post by @galador:

https://dba.stackexchange.com/a/9476/877

Give it a Try !!!


SQL regex to identify alphanumeric values

If you're attempting to exclude rows in a table, then a LIKE comparison would work.

Take this minimally complete verifiable example:

IF OBJECT_ID(N'tempdb..#LikeTest', N'U') IS NOT NULL
DROP TABLE #LikeTest;

CREATE TABLE #LikeTest
(
    d varchar(50) NOT NULL
);

INSERT INTO #LikeTest (d)
VALUES ('this is a 3708.dk test')
    , ('this is another test');

Here's the pertinent bit:

SELECT lt.d
    ,  Match = CASE WHEN lt.d LIKE '%[0-9][0-9][0-9][0-9]\.[a-z][a-z]%'
                ESCAPE '\'
            THEN 1 ELSE 0 END
FROM #LikeTest lt;

Results:

╔════════════════════════╦═══════╗
║           d            ║ Match ║
╠════════════════════════╬═══════╣
║ this is a 3708.dk test ║     1 ║
║ this is another test   ║     0 ║
╚════════════════════════╩═══════╝

The LIKE comparison above says "match where the data contains 4 consecutive digits, followed by a period, followed by two alpha characters in the range of [a-z].  Be aware the LIKE statement is collation-sensitive; if you have a server or database collation that is case sensitive, my example LIKE won't match upper case alpha characters.  You can force a case-insensitive search by adding a COLLATE clause to the LIKE comparison, like this:

SELECT lt.d
    ,  Match = CASE WHEN lt.d LIKE '%[0-9][0-9][0-9][0-9]\.[a-z][a-z]%'
                ESCAPE '\'
                COLLATE SQL_Latin1_General_CP1_CI_AS
            THEN 1 ELSE 0 END
FROM #LikeTest lt;


Can I use an index to optimize select clause, versus where clause?

Query 1:

INDEX(person_type, person_id) -- in that order
INDEX(person_type, person_id, full_name) -- to be "covering"

Query 2 ("covering" is not practical because of *):

INDEX(person_type, full_name) -- in that order

Query 3:  Before making suggestions here, please explain why you have a LIMIT without an ORDER BY.

See also my cookbook on making indexes.


Using MATCH, AGAINST multiple times in MySQL fulltext search

The main performance problem comes from OR between multiple tables, then from the need to check for rows from each table.

Plan A:

One common improvement is to turn OR into UNION, but it may be only a partial solution.  See how far you can get with this query (which will become a subquery in the real query):

SELECT p.id, MATCH(b...) AS Brv
      FROM brands b        JOIN products p ON b.id = p.brand_id
      WHERE MATCH(b...)
UNION ALL
SELECT p.id, MATCH(bm...) AS BMrv
      FROM brand_models bm JOIN products p ON bm.id = p.brand_model_id
      WHERE MATCH(bm...)
...

That query should be reasonably fast because each subquery can efficiently use the FULLTEXT index that (I assume) exists in brands, etc.  Also, you must have INDEX(brand_id), INDEX(brand_model_id), etc.

Once that looks good (and fast), the rest will be even messier, but fast.

Plan B:

This is possibly better in the long run, but it requires setup.

Build a single table with all the text columns into 5 columns (with a single FULLTEXT index on all 5).  Or, you could build a single text column with all the text combined from the other columns.  By having a single column (with FULLTEXT), the test will be faster, and avoid the need for OR or Coalesce.

That table would have a copy of p.id, thereby allowing you to quickly reach into the 5 tables to get the 5 individual MATCHes (if not already gotten) and do the ORDER BY computation.

Plan C:

This might be best.  Noticing the format of

((Prv*100) + Brv + BMrv + Orv + OVrv)

Do like Plan B, but with 2 text columns -- one for p.name, one for the combination of the other 4 tables.  You get Prv from the MATCH on one column, and you get (approximately) Brv + BMrv + Orv + OVrv from the other text column.

Plan D:

Hmmm... This may be even simpler?  Have an extra table with p.id, name, source (products, brands, etc) and a "weight" (100 or 1).  Then a simple query against this table gets virtually all the data needed (except for p.name, p.price, p.created_at).  Then JOIN to products to get those 3 columns.


How to get the Total disk space in ms sql 2008 or below

Since you mentioned you are already using xp_fixeddrives,following code will give you total size. Many shops will not allow this for security reason. I copied the code from here.

There are few more version of this code and example of psinfo.

SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB Numeric ;
SET @MB = 1048576
CREATE TABLE #drives
    (drive char(1) PRIMARY KEY,
     FreeSpace int NULL,
     TotalSize int NULL)

INSERT #drives(drive,FreeSpace)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject', @fso OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive

OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize',
@TotalSize OUT IF @hr <> 0

EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives SET TotalSize=@TotalSize/@MB
WHERE  drive=@drive
FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT
 drive,
 TotalSize as 'Total(MB)',
 FreeSpace as 'Free(MB)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
GO


Import binary database file into postgres

Assuming you took a copy of the whole data directory including pg_xlog, pg_clog, global, base, etc, then you can simply ensure that PostgreSQL 9.3 is installed and:

postgres -D /path/to/data/directory -c "port=5433"

then in another terminal use pg_dump to dump the database(s) from the database running on port 5433 with (eg) pg_dump "dbname=mydb port=5433".

This process is well-covered elsewhere; it's just starting a new PostgreSQL server and dumping the database. So I won't go into detail and cover all the possible errors, etc.

However, if you only copied the base directory, you're pretty much stuffed. The data is sort-of there, but it's missing all the vital information that tells the system what databases there are, handles crash-safe updates, keeps track of rolled back vs committed transactions, and lots more.

If you only have base, recovery might be possible but would probably involve paying an expert for a fair bit of time to try to extract the data. Even then, it'd likely be significantly corrupted - duplicates in primary key columns, deleted data reappearing, old versions of updated rows reappearing, recently committed transactions only half-complete, etc.

So I hope you kept backups.


Change gap datatype from interval to text and enclose it between single quotes in your dynamic query.
create function test (gap text)
returns text
as $ftest$
declare
    cmd text;
begin
    cmd := 'select ''' ||  gap || '''::interval';
    return cmd;
end
$ftest$ language plpgsql

select test('5 minute');

test
:--------------------------
select '5 minute'::interval

select '5 minute'::interval

| interval |
| :------- |
| 00:05:00 |

db<>fiddle here


begin
  execute immediate 'create table t1 as select * from dba_users where username  =''BP''';
end;
/

SQL> select username from t1;

USERNAME
----------
BP

Double quotes (") have a different meaning than having mutliple single quotes after each other (''). Use this:

BEGIN EXECUTE IMMEDIATE
'Create newTable as select * from oldTable where col1=''XXX'' and col2=''Y''';
END;


Copy SQL Server replication configuration from production to test environment

From the Microsoft Docs on Replication:

If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.
In the event of server failure or the need to set up a test environment, you can modify the script by changing server name references, and it can be used to help recreate your replication settings. In addition to scripting your current replication settings, you should script the enabling and disabling of replication. For information about scripting replication objects, see Scripting Replication.

You can script the replication objects, then modify the server names to suit the test/dev environment via SQL Server Management Studio:

Connect to the Distributor, Publisher, or Subscriber in Management Studio, and then expand the server node.

Right-click the Replication folder, and then click Generate Scripts.

Specify options in the Generate SQL Script dialog box.

Click Script to File.

Enter a file name in the Script File Location dialog box, and then click Save. A status message is displayed.

Click OK, and then click Close.

The resulting script can then be ran against the test/dev environment to automatically setup replication, just ensure you modify all server names as needed.  You really don't want the test/dev environment to interfere with the production environment.


How to DROP multiple tables from databases?

Since you only have to do it once, forget the fancy batch job and just get on with each task.  Assuming you know the table names, you just do this sort of thing.

For each table with foreign keys, drop the foreign key constraint on that field.  Then depending on your requirement, you can add records to the table that should have the data and add a new foreign key, or, simply delete those records.  Then you can drop the table.

Much of this work can be done with SSMS so you might not have to write that many queries.


What happens if I interrupt or cancel `pg_upgradecluster`?

By default, pg_upgradecluster doesn't use pg_upgrade. It's creating a logical dump with pg_dump and restoring it (with pg_restore) on the new cluster.

To avoid this and speed up the process, you need to add the -m upgrade option.
If you need to speed up the process once more, you can add the --link so that the upgarde will be inplace. Be carefull, you can't cancel the process if you did it or you will loose your cluster.

If you didn't use the -m upgrade option and the --link option, you can cancel the process, drop the new cluster and create it again:

pg_dropcluster 9.6 <cluster_name>
pg_createcluster 9.6 <cluster_name>


SQL Server 2017 crashes when backing up because filepath is wrong

I was able to reproduce this.

On 2016, if I put an invalid path like that, I get this message:

  Cannot open backup device 'D:mapbenefits_LogBackup_2019-02-21_13-58-24.bak'. Operating system error 3(The system cannot find the path specified.)

On 2017 CU 13 (14.0.3048.4), it results in the service crashing.  You've already mentioned that in the latest hotfix (14.0.3049.1), the service doesn't crash, but the session is killed.

I've confirmed the same exact behavior applies to RESTORE DATABASE as well - passing a path like "D:Backups" (with a missing backslash) or "D::\Backups" (extra colon) crashes the SQL Server instance (thanks Michael K Campbell for bringing this up).

If I put a "valid" path that doesn't exist, I get the correct behavior ("cannot find the path specified") in 2017.

This is a bug - in both CU 13 and the hotfix build.  Passing incorrect parameters to the BACKUP or RESTORE commands shouldn't crash the service or kill your session.  You can report it on the feedback site.

Note: the service-crashing version of this bug can be reproduced in a public preview version of SQL Server 2019 (CTP2.2 - thanks to Denis Rubashkin for pointing this out)

Looking at this in a debugger, it seems that path validation code is simply broken. It ends up causing a stack overflow by recursively calling into sqlmin!CheckFileStreamReserved after the normal (and quite extensive) checks on the supplied path fail to make sense of it. The stack overflow brings down the service on build 3048. The same thing happens on 3049 except a sequence of access violations while attempting to handle the stack overflow instead breaks the connection rather than stopping the whole server.

A fix for this bug was released in SQL Server 2017 CU 15:

FIX: SQL Server 2017 crashes because of stack overflow when you try to back up database master to disk

The issue is also resolved in SQL Server 2019 CTP 3.0.


Restore PostgreSQL database (or db names) to new version, from files?

Looks like you've figured out question 1 for yourself already (short answer: yes, use the latest 9.1.x release, and make sure the compile-time options are the same between the version on the old and new machine to be sure the data directory, and the machines should ideally be as similar as possible in order to be binary-compatible, e.g. both x86-64, similar glibc versions, etc.).

But about question 2:

  Is there a way to extract some basic information (such as database names, maybe even sizes or timestamps)

You can look under the "base" subdirectory of the data directory, and you should see something like this:

/datadir/base $ du -sh *
6.5M    1
6.1M    12292
6.5M    12297
 39M    3237152
6.3M    371336
 10M    4049006
 41M    4481732
6.7M    4691247
 10M    4927721
7.2M    4927722
7.4M    58068
  0B    pgsql_tmp

Each of those directories with an integer as the directory name represents a database inside my PostgreSQL cluster. The integers (OIDs) in the directory name match the oid you would see from a query like:

 SELECT oid, datname FROM pg_database;

if you had the server running. I don't know of a trivial way to determine the database name from those OIDs without having the server running, but at least you know how many databases there are and how big they should be.  And you should be able to figure out creation time too from checking stat. In my case, "1" was for "template1", "12292" was "template0", and the rest were various other databases.


Here is a run down to accomplish what you are trying to do. I added screen shots to help you along with backing up and restoring your database. The file paths won't exactly be the same.

BACKUP DATABASE

Backup the database Registered

Click on OK

The database has been backed up

RESTORE DATABASE

To restore the database registered, right click on the Registered Database in SSMS and choose Restore Database

In the window that appears, select From Device and click on the button with three ... to search for your backup

From here, click on Add and locate your backup file

Now click on OK

Click on OK

You are now back in the Restore Database Window, select Options

Make sure to select Overwrite the existing database (WITH REPLACE) and Leave the database ready to use option in the Recovery State options.

Then Click on General in the Select a page zone

Click on the checkbox "Restore" and then click on Script. I prefer sending the restore command to a new windows as a Script instead of directly clicking on OK. This way, I can drop any active connections to the database before restore which could result in a database in use error.

A new query window will be opened with the code necessary to restore the Registered database.

Add the following code just above the restore database script

-- drop all connections and put database in restricted user mode
ALTER DATABASE [Registered] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

-- restore database
RESTORE DATABASE [Registered]
FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Registered.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 1
GO

You will notice that I modified the STATS option from STATS=10 STATS=1. This can be useful when restoring large databases. In the Messages tab, you can view the progression of the restore by %. With STATS=1, the restore can be monitored 1% at a time instead of every 10%

Now hit F5, to run the script.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

13 percent processed.
23 percent processed.
32 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
73 percent processed.
83 percent processed.
92 percent processed.
100 percent processed.
Processed 168 pages for database 'Registered', file 'Registered' on file 1.
Processed 5 pages for database 'Registered', file 'Registered_log' on file 1.
RESTORE DATABASE successfully processed 173 pages in 0.076 seconds (17.783 MB/sec).


What can cause delay in SELECT statement execution and high ASYNC_NETWORK_IO even with "disregard results after execution"

Results are still returned to the client with the discard results option. The large ASYNC_NETWORK_IO wait time with discard results suggests network latency returning 221MB of data. I suggest you focus your troubleshooting on network rather than query plans.
Try a simple test by copying an 200MB file from the SQL machine to the client to see if you get similar times.


Extract Archived Log Files from RMAN Backupset

Here is the solution.

RMAN > run
{
    ALLOCATE CHANNEL d1 DEVICE TYPE disk;
    restore archivelog all;
}


Changing the use of GETDATE() in the entire database

Use the SQL Server tool to export the database objects definition to a SQL file which should include: tables, views, triggers, SPs, functions, and so on
Edit the SQL file (make a backup first) using any text editor that allows you to find the text "GETDATE()" and replace it with "[dbo].[getlocaldate]()"
Run the edited SQL file in Azure SQL to create your database objects...
Execute the migration of data.

Here you have a reference from azure documentation: Generating Scripts for SQL Azure


Implementing User Privacy Settings with MySQL Database

There is a spectrum of options open to you, depending on how much normalisation you desire, how much denormalisation you're prepared to live with, the use cases you have currently and what future functionality you reasonably expect to support.

The most trivial implementation would be to treat your JSON as a blob and store it accordingly, in a VARCHAR column. The application takes care of constructing the JSON and parsing it.  MySQL acts as a glorified key-value store. This will be easy to implement. It is likely to scale well. There will be no flexibility of usage, no built-in data integrity and ad hoc queries will be awful.

Next from this is using a column of type JSON. The DBMS has insight into the content of the JSON and can optimise with indexes, more compact storage and validation etc. It is not what a relational database is optimised for, however, so is unlikely to be your best bet.

I include these two for completeness.  Neither of them use the relational nature of MySQL. If you were to go this route you would be better adopting one of the many fine open source NoSQL solutions available.

To use the relational nature of your chosen product you will need to normalise your data.  The given examples and business rules are sparse so I'll infer and extrapolate somewhat, but I believe my model will cover the essential aspects.

Obviously there will be an entity type called "User", with some large integer type as the primary key.  I'll call this UserID.

All the different things you want to control visibility of will likely have their own representation.  I'd suggest you use the supertype/ subtype pattern. Amongst other benefits this will abstract the security from other concerns and allow addition of new types of interaction without changing this model.  I'll call the root type for all these types of things a Posting and give it a surrogate key PostingID.

Your example image has five categories of visibility, one of which is Custom. I'd suggest an entity type to cover this list. Let's call it Visibility. There is a question of how the system is to reflect changing friendships.  If the Posting is visible only to Friends, and a new friendship is created, should that new friend see the old posting or should it remain shown only to those who were friends at the point of posting? Similarly when unfriended does the miscreant loose rights to see items? I'm assuming the dynamic option is what is preferred.

It is tempting to form an intersection of these three to hold rights to specific postings. This would get very large very quickly, however, and would have substantial write activity reflecting changing friendships.

Better would be to hold the Visibility as an attribute in Posting (a foreign key column) and the corresponding UserFriend list for each User. It may be possible to combine the "Friends" and "Friends except Acquaintances" lists with a flag to distinguish the latter, depending on your specific rules. I'm assuming that friendship is defined as mutual so if A is a friend of B then B is by definition a friend of A. This will double the number of rows in UserFriend but will simplify the semantics.

A separate entity type will be reqired when Visibility has a value of "Custom". This will list the specific UserID values for that PostingID.

From this the tables will be

User
  UserID

UserFriend
  UserID    -- FK to UserID; the owner of this list
  FriendID  -- FK to UserID; the friend's UserID

Visibility
  VisibilityID

Posting
  PostingID
  PostedBy      -- FK to UserID
  VisibilityID

PostingUser
  PostingID
  VisibleTo    -- FK to UserID; the friend who can see this posting

To construct a list of a user's friends' recent activitis one would read the given user's UserFriend rows, join to Posting on the friends' UserIDs, where the posting visibility was "public", "friends" or "custom". For those that have a custom list, join to that on PostingID and the given UserID.

Assuming the symantics of "deny" are "all my friends except this one" I'd implement this by adding a flag to PostingUser to differentiate allow and deny.

You can take a step back along the spectrum of options by denormalising the above.  For example, PostingUser could be stored as a comma list in Posting, as UserFriend could be in Friend.  This would reduce the IO and, likely, the elapsed time. The trade-off is that more of the work has to be done in the application instead of the DBMS.

Although Facebook famously uses MySQL the above is assuredly not how they do this.  They most certainly have deployed many man-years of custom coding, caching, sharding and other tricks to make this work at their scale.

As a final point I would mention solving these sorts of problems are exactly what graph databases are for. Dispense with MySQL and implement this in, say, Neo4J.


How to force feed offset keyword a number calculated from table

Plan A:  Write a stored procedure to construct (via CONCAT()) the query you need, then use prepare and executed.

Plan B:  Create a temp table with an AUTO_INCREMENT column in order to number the rows.  Then look in that table for the answer.

Plan C:  Use @variables to count through the table.

Plan D:  http://www.artfulsoftware.com/infotree/queries.php#444


Why should the most restrictive condition be listed last in the where clause?

That was the case many years ago (back when I started working with databases).  These days however the optimizers are smart enough that it doesn't matter what order you put the restrictions in the WHERE clause.

What @PieterGeerkens mentioned was something called caching where the optimizer is smart enough to take the information it needed for the query you ran and put it into memory.  This of course provides much quicker access than disk IO.  Then when you run the second query all (or most depending on the size of your data and RAM) of your data will already be in memory.  This means that your second query will go faster.  And again as Pieter said if you clear your cache then run the queries in reverse order the second query will again go faster.

I'm hoping your book is just really old but either way I would find a different one.  There are a number of good free books out there along with other free training materials.  Here is a page with some good links.  http://sqlstudies.com/study-and-reference-tools/


Postgresql nested row_to_json not working

What about:

with tweets(type, id) as (
    select 'add' as type, tweets.id from tweets
) select row_to_json(row(array_agg(tweets))) from tweets;

This is actually one of the really cool things about PostgreSQL, arrays, and complex types.  One can aggregate them and then convert.  You should get a structure which is effectively:

tweets[] (i.e. an array of tweet tuples) which then get should converted to JSON properly).

Now, if you need a more complex structure than this, you probably want to define types separately.  Something like:

CREATE TYPE tweet_set AS (
    set_id int,
    tweets tweet[],
    generated_at timestamp,
    ....
);

And then come up with a query to generate that, and then convert it to JSON.

One of the basic issues is that since row_to_json expects a record type, you have to pass in a record, not an anyarray.  You could also do:

with tweets(type, id) as (
    select 'add' as type, tweets.id from tweets
) select array_to_json(array_agg(tweets)) from tweets;


Error during Oracle database 12c Release 1 installation

I tried creating /tmp which was not existing as a separate file system and kept 4gb of space. But the issue did not get resolved.

I try to keep my temporary file in a different place where the space available around 20gb. Still issue did not get resolved.

Finally I had to run the below command which resolved the issue. I couldn't find the solution so easily in google in first few pages as none of the people had raised this issue for Linux OS. Everyone was asking about Windows OS issue.

I had run the below command to provide execute permission for the folder CVU_12.1.0.2.0_**** under my temp directory.

chmod -R 777 /tmp/CVU_12.1.0.1.0_oracle/*

This resolved my issue. Thanks for the website which provided this solution.

I am posting here just to let others know, so that if anyone facing the same issue in future like me, can see this and get benefit.

Thanks for everyone who tried to answer my question. I really appreciate it.


Informix - Getting all descendants of a parent

Using the CONNECT BY syntax (similar to Oracle's) which allows recursive queries:

select connect_by_root parent as ancestor,
       child
from x                            -- table name
connect by prior child = parent
order siblings by parent ;

Tested in rextester.com


I believe you have not installed express edition because as far as I recall I had that error there sometime back.

Also please check if you have got SSIS component installed. I do not use maintainece plans but per my knowledge since those use part of SSIS I guess that component is needed to run the maintenance plan


MySQL how to migrate to new server without data lost?

Take the slave offline and clone it.

Replication will be backed up, and will catch up after bring brought back online.


ERROR: could not read block 84 in file "base/16386/670007": Input/output error

Your storage subsystem is failing. This is not a PostgreSQL error, as such, it's an error from the underlying file system or disks.

You might learn more from the output of the dmesg command and/or the kernel logs.


MongoDB specifying shard key and sharding strategy

As per MongoDB documentation here Ranged-based sharding involves dividing data into contiguous ranges determined by the shard key values. In this model, documents with “close” shard key values are likely to be in the same chunk or shard. This allows for efficient queries where reads target documents within a contiguous range. However, both read and write performance may decrease with poor shard key selection.

Ranged sharding is most efficient when the shard key displays the following traits:

Large Shard Key Cardinality
Low Shard Key Frequency
Non-Monotonically Changing Shard Keys

For Example

A sharded cluster using the field X as the shard key. If the values for X have a large range, low frequency, and change at a non-monotonic rate, the distribution of inserts may look similar to the following:

Use the sh.shardCollection() method, specifying the full namespace of the collection and the target index or compound index to use as the shard key.

sh.shardCollection( "database.collection", { <shard key> } )

For further your ref here and here


MYSQL - Operand should contain 1 column(s) - Nested Case statement

CASE in MySQL can either be a statement, or an expression.

Since your CASE statement is in a SELECT list, it's being treated as an expression. As an expression, it should return a single value. You're trying to return multiple columns (and, presumably multiple rows).

Presumably, you want to use CASE as a statement, without the SELECT in front, so that just one of your three SELECT statements is executed:

CASE WHEN current_time() BETWEEN CAST('6:00:00' AS TIME) AND CAST('16:30:00' AS TIME)
     THEN
          SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
            FROM workers_totals
           WHERE time_stamp >= curdate()
     WHEN (current_time() BETWEEN CAST('16:30:00' AS TIME) AND CAST('23:59:59' AS TIME)
        THEN
             SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
               FROM workers_totals
              WHERE time_stamp BETWEEN CAST(CONCAT(curdate(),' 16:30:00') AS DATETIME) and CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME)
     ELSE
             SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
               FROM workers_totals
              WHERE (time_stamp BETWEEN CAST(CONCAT(curdate(),' 00:00:00') AS DATETIME) and CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME))
                 OR (time_stamp BETWEEN CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME) and CAST(CONCAT(curdate()-1,' 23:59:59') AS DATETIME))
END CASE

Note that the CASE statement is terminated with END CASE instead of just END.

Also, I've condensed your code to use one CASE statement instead of two nested ones (you can have multiple WHEN branches with CASE), and changed your timestamp comparisons to check if the value is BETWEEN the two dates you supply, instead of timestamp >= dateX AND dateY.

You should double-check your time_stamp boundaries - for times between midnight and 6AM, you're cutting off anything with a timestamp later than today at 5AM.

Finally, note that this can only be used in a stored program.

I believe you could do this as a single SELECT statement, without needing a CASE at all:

SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
  FROM workers_totals
 WHERE (    current_time() < CAST('6:00:00' AS TIME)
        AND time_stamp >= CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME)
        AND time_stamp <=CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME)
       )
    OR (    current_time() >= CAST('6:00:00' AS TIME)
        AND current_time() < CAST('16:30:00' AS TIME)
        AND time_stamp >= curdate()
       )
    OR (    current_time() >= CAST('16:30:00' AS TIME)
        AND time_stamp >= CAST(CONCAT(curdate(),' 16:30:00')
        AND time_stamp <= CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME)
       )
;

I've tweaked your current_time() checks, to try to make sure that all times are covered, and fall uniquely into one range or another. You'll want to validate that this works as intended.

NOTE: All code is untested.


pg_restore warning column X has type unknown

CREATE VIEW or CREATE TABLE... AS SELECT have to infer the column types for the relation to create. Sometimes the context is not sufficient to guess a datatype, for example when it's just a string literal. In this case it's created as unknown.

Example :

test=> CREATE VIEW testview AS SELECT  'bla' AS foobar;
WARNING:  column "foobar" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW

Result:

test=> \d testview
    View "public.testview"
 Column |  Type   | Modifiers
--------+---------+-----------
 foobar | unknown |

If this view was pg_dumped and pg_restored, the column would be created as unknown with again the warning mentioned:

  pg_restore: WARNING:  column "foobar" has type "unknown"
  DETAIL: Proceeding with relation creation anyway.

The table/view should be created to begin with with explicit casts to columns when needed, as in

CREATE VIEW testview AS SELECT  'bla'::text AS foobar;

which we can assume is the point of that warning in the first place.


Can PostgreSQL tell me what configuration files it's using?

Certainly:

SHOW hba_file;
SHOW ident_file;

or just

SHOW ALL;

:-)


MySQL, import SQL dump with another database

By reading the official documentation, I'll say that your guess is right, the dump script will override the DB1 database.

MySQL Use db syntax

  The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued.

The solution is the one proposed by Stuart, use a grep-like tool to find and replace "use db1" with "use db2" inside the dump file. Maybe even other references to db1 inside the dump script (procedures that use complete table names..). There are solutions for both Linux or Windows variants to edit that big text file.


Importing plain (raw) files into a postgres table (bytea and large objects)

Server-side files

Assuming  the account has the pg_read_server_files role (or is superuser), if the files are accessible on a filesystem mounted on the server, and their paths have been collected in the table, this will grab the contents efficiently:

UPDATE emails SET mail_data = pg_read_binary_file(emails.fullpath);

It is more efficient than using large objects as an intermediate storage area.

Client-side files

When the files are not accessible server-side or the account does not have elevated permissions, with psql a more generic solution may be, for each file:

\set clientpath '/path/to/file'
-- assume clean paths (without any character that would be special to the shell)
\set contents `base64 :clientpath`

insert into email_data([other columns...], mail_data)
 values ( [other columns values...], decode(:'contents','base64'));

A base64 intermediate representation is used because psql doesn't support parameters in binary form. The :'contents' syntax instructs psql to inject the variable in text form into the query.


Slow CREATE table from subquery using SELECT .. INNER JOIN

Looking on Your pictures, I can guess - for run subquery You also use MySQLWorkbench. Most of software - not return to You back all rows from dataset, by default it filter up to 1000 records (some less)

So select without ORDER BY start work and immediately stop after first XXX rows.

In case of create table:

The subquery return all rows
Insert operation always longer than SELECT ... much, much longer

Possible or not increase speed of insert? it depends from Your current server settings and configuration - may yes, may be not

Also it depend from other server loading:

Select it non-locking operations, it start work immediately
INSERT FROM SELECT (other form of CREATE from SELECT) - lock all data, so if other transactions running on this time, it spend some time for lock tables

Also You can test query with combined indexes on both tables:
(cityCode,tvlDate) and (cityCode,inventoryDate)


Is a “‘Junction’ Table” the same as a Weak Entity?

A junction table is a weak entity, but a weak entity often is not a junction table.  A junction table is not the same as a weak entity.  A junction table is a type of weak entity.

Tables used to resolve repeating groups are also weak entities.  These are only junction tables if the repeating group contains a repeated foreign key relationship.


Using a filtered index when setting a variable

The optimization that allows you to use the filtered index with a RECOMPILE hint is called the "parameter embedding optimization."  That's a process where the query parser replaces the variable reference with the literal value inside the variable.
See this post from Paul White for the reason why it doesn't work in your second case: Parameter Sniffing, Embedding, and the RECOMPILE Options

There is one scenario where using OPTION (RECOMPILE) will not result in the parameter embedding optimization being applied. If the statement assigns to a variable, parameter values are not embedded:


cause of / how to fix a table with excessive reserved pages

Please examine the level of fragmentation of those indexes with below query. Index fragmentation could be the culprit.

SELECT
 DB_NAME() AS DBName
 ,OBJECT_NAME(ps.object_id) AS TableName
 ,i.name AS IndexName
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 FROM sys.dm_db_partition_stats ps
 INNER JOIN sys.indexes i
 ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
 CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
 ORDER BY ps.object_id, ps.index_id

If you rebuild your indexes, you will reclaim that extra space. You will reduce the size of those indexes and the size of the overall database. Perform index maintenance with the following script:

DECLARE @TableName varchar(255)

 DECLARE TableCursor CURSOR FOR
 (
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )

 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
 WHILE @@FETCH_STATUS = 0

 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
 Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
 End Try
 Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
 FETCH NEXT FROM TableCursor INTO @TableName
 END

 CLOSE TableCursor
 DEALLOCATE TableCursor

Create an Azure Runbook to defragment indexes regularly and avoid using more storage space.


How to split url in sql server like this

Here's one which is heavily based on Jeff Moden's DelimitedSplit8k:

declare @url VARCHAR(8000), @delimiter char(1)

set @url = 'www.abc.com/aaa/bb/c/ddd/ee/ff/g/h/iii'
set @delimiter = '/'

;WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (
                 SELECT TOP (ISNULL(DATALENGTH(@url),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
ctePos(N1) AS (
                 SELECT t.N-1 FROM cteTally t WHERE SUBSTRING(@url,t.N,1) = @delimiter
                 union all
                 select datalength(@url)
                )
select left(@url, N1) + case when ROW_NUMBER() OVER(ORDER BY N1) = 1 then '/' else '' end as URL
from ctePos order by N1 desc

This should outperform any loop / recursive code if used as a function.

DelimitedSplit8k source: http://www.sqlservercentral.com/articles/Tally+Table/72993/