IN MySQL 8 (>= 8.0.16) You can use a CHECK CONSTRAINT
In older Versions you will need an INSERT/UPDATE TRIGGER
That said, you should also normalize your table and use a lookup (or reference/helper) table for an Origin id and a Type id.

CREATE TABLE Product
(
    P INT
  , PType varchar(19)
  , PName varchar(20)
  , Price DECIMAL(8,2)
  , Origin varchar(50)
  , CONSTRAINT CHK_type CHECK ((Ptype = 'meat' AND Origin ='Europe')
                           OR Ptype <> 'meat' )
);

INSERT INTO Product VALUES (1,'meat','Kobe Beef',10000.00,'Japan')

Check constraint 'CHK_type' is violated.

INSERT INTO Product VALUES (1,'apple','Breaburn',10.00,'New Zealand')

✓

db<>fiddle here


The "best" solution which we found was:
1. migrate the Access tables to an SQL tables
2. Migrate view & stored procedures to the SQL server
3. make the MS-Access run from a network location. This way there are two Ms-Access: one for the development work, the other for everyday use.
This way ms-access can scale.
As the developer who has done this I can tell you this:
Migrating the tables is pretty strait-forward (there's even an option for that in Access). Then you need to check that the data types have stayed the way the are supposed to. SQL might use a data size which is bigger then it should be (currency shouldn't be money, it should be decimal (18,2); avoid floats if you don't need to; use the smallest data type possible; strings have migrated correctly; etc).  Also: when Access uses SQL as it's back end it can only have one column as a primary key, otherwise it doesn't link properly. So if you had a primary key which was composed of two columns, you need to add to that table an identity column.
Once that is established you have a split Access application which means better availability. Yet, you need to migrate the views and stored proc: basically copy & paste the code form Access to SQL. Since the syntax is different you need to check for the following pitfalls:
(1) for example, now there's IIF in SQL so that sort that part out.
(2) parameters which are incorporated in the stored proc are used in a different syntax in SQL so that's also something to be aware of.
(3) Avoid ORDER BY in views. (don't use the SQL option, just don't use it and do the sorting in Access)
(4) Last but not least: in Access the "true" is -1, in SQL it is 1. It is very important to check that the WHERE clause says WHERE flag <> 0, and not flag = -1.
Good luck!


Need to sum a field in a row with same it and result needs to be cumulative in a field

With SQL Server 2012 or newer, you can use a WINDOW function with boundaries:

SELECT *
    , SUM([Amount]) OVER(PARTITION BY [ID] ORDER BY [date_time] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM @data;

With previous version, a self JOIN can be used:

SELECT d1.ID, d1.[date_time], d1.Amount
    , SUM(d2.Amount) as running_amount
FROM @data d1
INNER JOIN @data d2 ON d1.ID = d2.ID AND d2.[date_time] <= d1.[date_time]
GROUP BY d1.ID, d1.[date_time], d1.Amount;

Output:

ID  | date_time                 | Amount    | running_amount
1   | 2015-01-01 02:01:00.000   | 9         | 9
1   | 2015-01-02 02:02:00.000   | 20        | 29
1   | 2015-01-02 02:03:00.000   | 20        | 49
2   | 2015-01-01 02:01:00.000   | 20        | 20
2   | 2015-01-01 02:02:00.000   | 20        | 40
3   | 2015-01-02 01:00:00.000   | 20        | 20
3   | 2015-01-03 01:00:00.000   | 10        | 30
3   | 2015-01-04 01:00:00.000   | 5         | 35


BI Tools for SSDT is a standalone install meant for development and testing.

You can preview (render) SSRS reports within SSDT without having SSRS installed on your dev machine. This is for development only and will only be visible in SSDT. On a side note SSIS packages will function in the same manner. SSAS has no preview option - it requires a functional/full SSAS service available for deployment.

To deploy the reports you will obviously need a functional SSRS service running on the target server.


Enable GTID on one node in Percona/Galera

The scenario you have described is all OK.

Nevertheless, we recommend that you enable GTID on all nodes, and the recommendation in that reference you provided is also to enable GTID on all nodes. As a general principle, Percona recommends having the same configuration on all nodes of the cluster. If you don't do it at the same time and then later try to enable it then you risk having GTID inconsistent as per that reference you posted.

As long as that recommendation is understood...

Running transactions:

If the node that is being shutdown is replicating trx .. then existing trx will not have any affect
If the node that is being shutdown is processing an active workload then you should stop this workload. If the trx is already in commit state that will processed but no new trx will allow to progress.

There is a discussion on the Percona blog that describes some different scenarios using 5.6 and this might provide some further insight for you. For example, it observes that even if you are using 'mixed' replication "we can have different replication modes on different servers, but only if all servers have gtid_mode set to ON."

However, the post also mentions that the author can't think of a good reason to do this (maybe you now have one). As well as blog posts (do a search on that site) you might also find some useful - free - recorded webinars on PXC that might be good resources for you and provide more information. Here is the link, as always please read the details carefully and test! :)
https://www.percona.com/blog/2013/05/21/replication-in-mysql-5-6-gtids-benefits-and-limitations-part-1/

Here is a webinar for 5.6 https://www.percona.com/resources/mysql-webinars/whats-new-percona-xtradb-cluster-56 ... if you add webinar to your site search you'll turn up some more useful recorded webinars on Percona XtraDB Cluster. These are for techs and presented by techs, normally support or development engineers.

Disclosure: I work for Percona (obviously, but I guess I should say it anyhow).


Multiple MongoDB databases (1 per client) and sharding - are they compatible?

Yes, they are compatible: if you have multiple shards, then different databases will be automatically allocated to different shards in a balanced way.

The sharding system does distribute large collections across shards by using a shard key, but it also distributes separate databases across different shards. The shards documentation explains:

  Each database in a sharded cluster has a primary shard that holds all the un-sharded collections for that database. Each database has its own primary shard. The primary shard has no relation to the primary in a replica set.
  The mongos selects the primary shard when creating a new database by picking the shard in the cluster that has the least amount of data.

You can use that to let each client's database be allocated to different shards, as follows:

Set up multiple shards (perhaps 3 for now)
When you add a new database for a new client, mongos will allocate the database to whichever shard has least data
This has the effect of 'balancing' the database among the available shards

A few warnings about this approach:

When you first add new shards, mongos does not automatically move databases from one shard to another to balance the system. If you need to do that, you will have to do so deliberately using the movePrimary command
If your databases grow, again you might need databases moved from one shard to another to give them enough space; again, you will have to do this yourself using the movePrimary command
If a single collection in one of your databases is getting too large for a single shard, you will have to manually enable sharding for that collection; then mongos will do the allocation of chunks to different shards, automatically.


I would suggest that they are equally important on physical or VMs.

suggest you to create all critical alerts as highlighted by Glenn Berry.


How to store thousands of properties related to a record in PostgreSQL?

You could create a table to hold the key/value pairs like such:

CREATE TABLE KEY_VALUE (
  ID BIGINT, -- THIS COULD BE A FKEY TO YOUR '300K' RECORD TABLE
  KEY VARCHAR,
  VALUE VARCHAR
);

As long as this table is indexed properly (an index on id/key, I imagine), getting any key/value pairs you are interested in should still be very quick, even if it's millions of rows large. Granted - this solution as a viable option really depends on what scope of data you expect to store with your key/value pairs (is it all text? or numbers? etc..). Perhaps adding a 3rd column to say what type of data the value is would help.

EDIT

https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway

Just to be clear, btw - I figured this might work given the sheer number of different key->value pairs you said you would have..  If it were a small number, I would probably just have a "details" type of table where each value was stored in a column.


I figured it out. While /etc/mysql/my.cnf didn't have a password keypair stored, there was a password stored in /root/.my.cnf.

As soon as I commented out the password in /root/.my.cnf, I was not allowed to log in without a password (which is what I expected).


What does reslen and other means in mongodb log?

reslen is the query result length in bytes.  From the profiler docs here:

http://docs.mongodb.org/manual/reference/database-profiler/


Can mysqldump dump triggers and procedures?

I normally do not separate triggers from the tables they were meant for. I dump like this:

mysqldump -u... -p... --no-data --routines --triggers dbname > DBSchema.sql

Check for presence of routines and triggers like this:

SELECT COUNT(1) FROM mysql.proc;

SELECT COUNT(1) FROM information_schema.triggers;

SELECT * FROM information_schema.triggers\G

If you want to get this done to all DBs in the MySQL Instance, do this:

mysql -u... -p... -A -N -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > /tmp/dblist.txt
for DB in `cat /tmp/dblist.txt`
do
    mysqldump -u... -p... --no-data --no-create-info --routines dbname > ${DB}-routines.sql
    mysqldump -u... -p... --no-data --triggers dbname > ${DB}-schema-triggers.sql
done

That way, stored procedures go in a routines dump for the DB, while the schema and triggers go in another dump.


SQL Cluster instance does not see all drives

I believe that is the whole purpose of FCI. You will only see the disk part of shared storage in that cluster.
I am not sure what you are trying to exactly do when you say local backup. Backup of database ? If yes then I guess that should be simple enough to backup in the drive part of shared storage so that after failover to other node backup continues to work.
There are few drives which we might not care local installation directories to be part of same shared storage or software drives or dba script drives what we have in our env.
So totally depends what drives are you talking about here


Optimization: insert only not existing rows with additional conditions

Besides adding indexes (which you say is not allowed), the query is unnecessarily complex. The 2 of the 3 table references in the correlated subquery are not needed as they join to the same tables in the main query and on primary keys. You can simplify it to:

INSERT INTO target.child (value, parent, vtype)
  SELECT
    p1.value, p.erow_id, 100
  FROM
    patch.source AS p1
    INNER JOIN target.parent AS p
      ON  p.uid_p1 = p1.uid_p1
      AND p.uid_p2 = p1.uid_p2
  WHERE NOT EXISTS (
    SELECT
      1
    FROM
      target.child AS c
    WHERE
      c.vtype = 100 AND
      c.parent = p.erow_id
  );

An index on child (vtype, parent) would help I think for performance. If that index is UNIQUE, the query can be simplified further using OR IGNORE clause:

INSERT OR IGNORE
INTO target.child (value, parent, vtype)
  SELECT
    p1.value, p.erow_id, 100
  FROM
    patch.source AS p1
    INNER JOIN target.parent AS p
      ON  p.uid_p1 = p1.uid_p1
      AND p.uid_p2 = p1.uid_p2 ;

What OR IGNORE does:

  IGNORE

  When an applicable constraint violation occurs, the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is used.


Table Design for user-specific and user-agnostic criteria

Track your achievements in parts.  Right now you have some indicative (i.e. descriptive) information and one "business rule" column (Metric).

You actually need more than one business rule column.  Depending on what you foresee being your needs and how much work you want to put into now, this might be either one or two more business rule columns, or possibly even an extra table or two.

In addition to "how many are needed?" (i.e. Metric) you also want a column that describes how many of what?  You have two descriptive columns for this now, but these are human readable. You also want a machine readable column.  In terms of nomenclature, I would actually use Metric for the what, and something else, say Scalar for the how many.

With a Metric column and a Scalar column you can then write program logic that knows how to measure each type of achievement.  This way you don't need custom logic for different levels.

You could take this a step further and normalize this somewhat so that the descriptive information is part of a separate table.  This would allow you to have groups of achievements that share certain types of information and logic.  This distinction would help you with handling user-specific and user generic logic in a nice, managed, organized way.


Risk in evicting node from SQL Server cluster without prior uninstalling of SQL Server binaries

Decided to take a risk and test it in production.
Took out 2 nodes from 4 node cluster without uninstalling SQL from those and I can confirm for the two weeks of being in this situation I haven't receive any complaint from users.

Hope this helps others potentially dealing with same question


SQL stored procedure to produce custom XML text

This seems to at least approach a solution for you.  You might need to add in some data type detection and conversions to make it work for you though.  It also makes an assumption that the first column for the table is the ID column...

DECLARE @TableName NVARCHAR(100) = 'dbo.MyTableName'
DECLARE @SQLCommand NVARCHAR(4000)

;WITH CTE_Initial AS
    (
    SELECT CONCAT('<', CASE WHEN C.column_id = 1 THEN 'TOP (' + C.name + ')' ELSE 'FIELD fieldname="(' + C.name + ')"' END, ' value="', '''', ' + ', '[T].', QUOTENAME(C.name), ' + ', '''', '"/>') AS XMLColumnName
            , *
    FROM sys.columns AS C
    WHERE C.object_id = OBJECT_ID(@TableName)
    )
SELECT @SQLCommand = 'SELECT ' + STRING_AGG(CONCAT('''', XMLColumnName, ''''), '+') + ' FROM ' + @TableName + ' AS T' FROM CTE_Initial

PRINT @SQLCommand

--EXEC sp_executesql @SQLCommand


Windows Server 2019 and SQL Server Backups issue?

this is a local directory. SQL Server is on the server and is attempting to backup to a directory ON the server

If SQL Server has access permissions on the directly, how can it NOT have permissions on the directory?

You're backing up to a share, not a directory.  When accessing a share the account must have permissions both on the share and the underlying directory.
Also it's possible that a Credential is used for NTLM auth to the share and it's attempting to authenticate using some other configured identity.
Change the backup to point directly to the local folder.


When will autovacuum work in PostgreSQL?

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

PostgreSQL documentation

AUTOVACUUM is the standard form of VACUUM run automatically. If you insist on freeing unused space from tables, then do manual VACUUM FULL.


Run multiple stored procedures in sequence

If you don't need the result values later you can do it shorter this way:

-- procedures to test with
create proc proc1 as print '1' return 0
GO
create proc proc2 as print '2' return 1
GO
create proc proc3 as print '3' return 0
GO

if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)')
GO
alter PROCEDURE dbo.testproc
AS
  DECLARE @result INT
        , @sql nvarchar(max) = N''
  DECLARE @tbl TABLE([step] INT, [pname] nvarchar(513))

  INSERT INTO @tbl ([step], [pname])
  VALUES (1, N'proc1'),
         (2, N'proc2'),
         (3, N'proc3')
  -- Potentially many more procedures here

  select @sql = @sql + 'exec @result = ' + QUOTENAME(pname) + ' if @result <> 0 return;'
  from @tbl order by step

  exec sp_executesql @sql, N'@result int output', @result output

  if @result <> 0
    begin
      print 'do your cleanup'
    end
GO

exec testproc

The variable engine will produce the following query batch

exec @result = [proc1] if @result <> 0 return;
exec @result = [proc2] if @result <> 0 return;
exec @result = [proc3] if @result <> 0 return;

When the batch is executed it will stop executing when there is a @result not zero and keep that value in the output parameter.

More traditional looping

If you want to loop over the procedures. Since there are no parameters (or the parameters are all the same) you can simply call exec @result = @proc

if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)')
GO
alter PROCEDURE dbo.testproc
AS
  DECLARE @result INT
        , @proc sysname
  DECLARE @tbl TABLE([step] INT, [pname] nvarchar(513))

  INSERT INTO @tbl ([step], [pname])
  VALUES (1, N'proc1'),
         (2, N'proc2'),
         (3, N'proc3')
  -- Potentially many more procedures here

  declare c cursor fast_forward local
  for select pname from @tbl order by step

  open c
  fetch next from c into @proc
  while @@FETCH_STATUS = 0
    begin
      exec @result = @proc
      if @result <> 0
          BREAK

      fetch next from c into @proc
    end
  close c
  deallocate c

  if @result <> 0
    begin
      print 'do your cleanup'
    end

GO

exec testproc


Get the latest change in value from a streak of values

Not sure if this is the best solution, but this does the job. The only requirement is that each sensor has a least 2 values.

select *
from sensor_data as t8
inner join (
  select t6.sensor_id, min(t6.date) as date
  from sensor_data t6
  inner join (
     select t4.sensor_id, max(t4.date) as before_last_date
      from sensor_data t4
      left join (
          select t2.sensor_id, t2.val as last_val, t2.date as last_date
          from sensor_data t2
          inner join (
              select sensor_id, max(date) as last_change
              from sensor_data
              where 1
              group by sensor_id
          ) as t1
          ON t2.sensor_id = t1.sensor_id AND t2.date = t1.last_change
      ) as t3
      ON t3.sensor_id = t4.sensor_id
      WHERE t4.val != last_val
      GROUP BY t4.sensor_id
  ) as t5
  ON t5.sensor_id = t6.sensor_id
  WHERE t5.before_last_date < t6.date
  group by t6.sensor_id
) as t7
on t7.sensor_id = t8.sensor_id and t7.date = t8.date


PostgresSQL copying newer entries from a table in one database to a table in a database on a remote machine

Using dblink, you can copy records from one server to another. I installed dblink, using this Installation guide. The databases are the same and have the same table called sometable. This is similar to federated tables on MySQL and openquery on SQL Server.

The older server is listening on port 5432 and the new server is listening on port 5433. I only installed dblink on the new server. The dblink extension was only added to the receiving database on the new server. In my example, I didn't include the host because both postgresql servers are on the same machine.

Here are the basic steps I took using a 9.1 on a CentOS 6 server

Install dblink

-- go to the postgresl_source directory. If you installed via yum or apt-get you will have to install the postgresql_source packages

cd /postgresql_source_directory/contrib/dblink

-- install the dblink binaries

make install

-- Connect to 9.1 listening on 5433

psql -p5433

-- Connect to the database that will be receiving data

\connect somedatabase

-- Add in the dblink extension

CREATE EXTENSION dblink;

-- Verify that the extension exists

\dx
dblink  | 1.0     | public     | connect to other PostgreSQL databases from within a database

Copy the data to the new server

I am using a temporary storage table, because it made using an EXISTS statement easier when copying in new records.

-- truncate the temporary storage table on the new server

truncate table sometable_2;

-- copy in records to the temporary storage table on the new server

insert into sometable_2 select * from dblink('port=5432 dbname=somedatabase', 'select * from sometable') as t1(t int);

If your server is on a different ip address, you would run this instead

insert into sometable_2 select * from dblink('port=5432 dbname=somedatabase host=some.remote.ip.address', 'select * from sometable') as t1(t int);

-- Copy only new records into the new sometable

insert into sometable select * from sometable_2 st2 where not exists(select * from sometable st where st.t = st2.t);
INSERT 0 5

If I run the same queries again, only new records will be copied in. This is because the correlated subquery (select * from sometable st where st.t = st2.t) only retrieves records that don't already exist in the table "sometable" on the new server.


Creating a summary table for an averaging query

Here is a possible scheme:

Add a column to the activity_scores table is_processed which flags whether the row has been summarized into the summary table
Add a partial index to the activity_scores table for only the unprocessed rows, this could include all columns
Now when you go to load yesterday's data you are only looking through rows which you know have not been processed
Identify yesterday's rows as part of the load process and insert them into a temp table, then insert the summary of the rows into the summary table
Join the temp table to the source table on id and mark the rows as processed (should be quick as you have the ids and aren't doing a scan)


Using PostgreSQL this is pretty easy. The input formats support this form, and you can sort easily and limit to the two most recent dates.

SELECT mydate
FROM ( VALUES
  ('Jan 01,2017'::date),
  ('Mar 02, 2017'),
  ('Feb 09,2017'),
  ('Jun 02,2017')
) AS t(mydate)
ORDER BY mydate DESC
LIMIT 2;

If some of those dates are ahead of the current date, you may want to add some selectivity,

SELECT mydate
FROM ( VALUES
  ('Jan 01,2027'::date),
  ('Jan 01,2029'),
  ('Jan 01,2017'),
  ('Mar 02, 2017'),
  ('Feb 09,2017'),
  ('Jun 02,2017')
) AS t(mydate)
WHERE mydate <= CURRENT_DATE
ORDER BY mydate DESC
LIMIT 2;


how to get avg() size by day using two decimal places

You should specify precision and scale when casting to a decimal, and perform that cast last. This blog post explains why you never want to leave out the size declaration for char/varchar/nchar/nvarchar, but similar arguments hold for decimal and a few other numeric types, too.

Also, on 2008+, CONVERT(DATE is a slightly more efficient (and easier to read) method of stripping time (see this post and this follow-up).

Here is how I would write your query:

SELECT
  [date_only] = CONVERT(DATE, InsertedDateTime),
  [Tally] = COUNT(*),
  avgSize = CONVERT(DECIMAL(10, 2), AVG(size/1024.0/1024))
FROM dbo.attachmentdetail
WHERE isinline <> 1
GROUP BY CONVERT(DATE, InsertedDateTime)
ORDER BY [date_only] DESC;

(Also, use schema prefixes and statement terminators. Always.)


Moving a hard disk with PostgreSQL data folder to another server

Yes, that is no problem, as long as

you are using the same major version of PostgreSQL on the new machine
PostgreSQL was built in the same way
both machines have the same architecture and operating system version (the version is relevant because it might affect the collations, changing versions requires a REINDEX)

Running PostgreSQL on a NAS file system is dangerous, because many NAS implementations are not reliable.


Stored Procedure with Optional Parameters

I wouldn't create a proc on a view, but instead just use the proc against the underlying query itself.

In your example you use LIKE instead of = which adds a level of complexity to the mixture. Usually this could be handled like:

WHERE
(Description = @desc or @desc is null)
AND (Supplier = @supplier or @supplier is null)
AND (Manufacturer = @manufacturer or @manufacturer is null)

With LIKE you'd have to concatenate the % on the head and tail end (or just one end if that suffices). Brent has a post about performance about LIKE

WHERE
(Description like '%' +  @desc + '%' or @desc is null)
AND (Supplier = '%' + @supplier + '%' or @supplier is null)
AND (Manufacturer = '%' + @manufacturer + '%' or @manufacturer is null)

Lastly, this is commonly referred to a kitchen sink query, or catch all query. Here's Aaron's blog about performance with this type of query.


Based on your edit, all writes lock exclusively.

No other process can read the row(s) being written unless

you've been silly and are using NOLOCK
you have snapshot isolation where the last committed row is read

There is usually no need to add lock hints in day to day code.

As to why you (apparently) have a table lock, this can be caused by no index on ID. The table is being locked because all rows have to be looked at.


What are the risks of changing oracle sys password?

There should not be any problems. Nobody should use this password for application purposes. In case of RAC cluster do not forget to change the password on each node. This password is not stored in the database but in local node's password file.

Unless you use Oracle Data Guard (physical replication). For some reason having the same password on each side is not sufficient and you must copy the amended password file to the other site too.


To find SQL Server is actually used or not?

SQL Server can log failed logins, successful logins, both or none to the SQL server log.  Check your current setting and if it is not collecting both failed and successful logins change it so that it is.  Once it's been running that way for a few weeks (preferably over a month end & quarter end) check to see if there has been any activity.  If no one is logging in then you can run a final backup of your databases (you should probably include the master and msdb databases as well as the user databases).  Move those backups to long term storage and shut down the instance.  If no one complains after a while then you can de-com.

Under the server properties:


I can see a X lock on a RID although the query uses a non-clustered index

The update still has to lock the rows being updated regardless of any nonclustered index, so RID locks are required. The nonclustered index helps limit the number of RID locks obtained (and/or avoids table escalation) so the second query is no longer blocked.


The answer is pretty "searchable" when you notice, that 1st and 3rd query fetch 1.2M rows from candidato ...just to exclude 90% of them from the results.

2nd query returns 1 row from tb_uf, which forces Nested Loop plan.

This means that planner has wrong assumptions about statistics (expected results count) or costs (of random seek). You could either tune these values:

https://www.postgresql.org/message-id/20060926193553.GA27268@oppetid.no

Have Postgresql query planner use nested loop w/ indices over hash join

or manually force a Nested Loop. Although my intuition tells me it should be better to have Hash Join here.

Not having the data, I'd suggest to try one of:

set enable_mergejoin = off
using CTE to force order of operations, like (3rd query with minimal modifications for easier understanding):

 WITH e AS (SELECT * FROM escola WHERE uf_id = 19)
 SELECT pk_numero_inscricao, pk_codigo
 FROM e
   JOIN candidato AS c
     ON c.fk_codigo_escola = e.pk_codigo


How to create a table with columns from other 4 tables(they don't have primary/foreign key relations)?

Create a table with an extra column to distinguish where it came from
Do something like

...

INSERT INTO new_table (title, price, items_ct, link, which)
    ( SELECT *, '10_05' AS which FROM csv_10_05 )
    UNION ALL
    ( SELECT *, '11_05' AS which FROM csv_11_05 )
    UNION ALL
    ...


Is there a way to easily validate that a SQL Script is compatible with a specific SQL Server version?

There is not a simple tool or existing script for this that I am aware of. In terms of scanning for the use of functions added in a specific version (or enabled by a specific compatibility level) something could be written using the documentation from MS though that might not catch calls via ad-hoc SQL if your stored procedure and other use that pattern for anything, and you'll need to do further research to cover changes to undocumented functions.

  without requiring us to create multiple test environments for each SQL Server version we wish to support?

Would it not be acceptable to test against the minimum version you need to support, rather than testing against every version from that onwards? That would only be one extra instance you need to add to your CI/Test/other processes. If 2016sp1 is the earliest version you need to support then almost everything is available in all editions so you can use Express and not even have any licensing costs for that one instance. You might get away with that with prior versions too, if you don't use any features that were standard-/enterprise- only prior to 2016sp1.


Unable to output data from tables in pgAdmin4 due to missing oid operator

Never seen a negative OID before. This is a first-class "crime scene"!

... attrelid=-1519044407::oid ...
... rel.oid = -1519044407::oid ...

Facts

0.

2^32 - 1519044407 = 2775922889

And we have verified that OID 2775922889 indeed exists in your DB. Tests:

SELECT * FROM pg_class WHERE oid = 2775922889;
SELECT * FROM pg_class WHERE oid = '-1519044407';
SELECT * FROM pg_class WHERE relname = 'test_table';

1.

The manual about object identifier types:

  The oid type is currently implemented as an unsigned four-byte integer.

2.

Postgres cast accepts signed integer anyway (!)

The Postgres I/O conversion from string literals, as well as the cast from integercurrently (pg 12) accepts negative integer values / literals as input anyway. Seems to just binary coerce a signed four-byte integer to unsigned four-byte integer and vice versa. Worth keeping in mind at least.

These, oddly, work:

test=# SELECT '-1519044407'::oid, '-1519044407'::int::oid;
    oid     |    oid
------------+------------
 2775922889 | 2775922889

Leads to different representation when casting to int versus bigint:

test=# SELECT (oid '2775922889')::int
test-#      , (oid '2775922889')::bigint;
    int4     |    int8
-------------+------------
 -1519044407 | 2775922889  -- !!

3.

The manual on Numeric Constants:

  Note that any leading plus or minus sign is not actually considered part of the constant; it is an operator applied to the constant.

4.

The cast operator :: takes precedence over the unary minus operator (-).

Conclusions

1.

I have never seen OID numbers in that range in system catalogs before, and I have been working with all kinds of big databases. You have a problem in your DB (cluster).

Improved with comments from Daniel Vérité:

Either you are burning OID numbers at an insane rate - already 2.8 billion numbers. ~ 1.5 billion remain until OID wraparound. Do you have any tables created with WITH OIDS? (Nobody should any more. The feature is deprecated and removed in Postgres 12.) Or some code excessively creating / dropping new objects? The OID counter is per instance, not per database, so all dbs contribute to OID consumption.
There is a comment in the source code for GetNewOidWithIndex for how OID collisions are dealt with after wraparound. Collisions incur a minor performance penalty.

Or somebody/something messed with your system catalogs.

2.

If the above query was generated by pgAdmin4, there is a serious bug.

Maybe that did not surface, yet, as nobody had OIDs in that range in the system catalogs, yet?

Seems like it operates with the integer representation of OIDs, and naively pastes those as numeric literals including the sign by mistake in queries. A string literal would work: '-1519044407'::oid. Or parentheses would make it work: (-1519044407)::oid.

But this does not:

-1519044407::oid

Because:

1519044407 is taken as numeric literal and initially coerced to integer.

The cast operator :: takes precedence over the sign operator - and the integer is cast to the (wrong!!) oid.

Finally, Postgres tries to apply the sign operator and, luckily, fails with the reported error message:

ERROR:  operator does not exist: - oid at character 125

If it would not fail there, serious nonsense might occur.

db<>fiddle here

I have posted a note to the pgadmin-hackers list.
A related bug has been logged before there, too. (Access with Postgres community account.) It was traced back to a psycopg2 issue using the wrong data type for OID in 32 bit versions. Should be fixed in psycopg2 version 2.8.4 (which pgAdmin4 depends upon).


How can I tell if my tempdb database has a performance issue/need to add more data files?

Paul Randal explains how many tempDB files to create here Correctly adding data files to tempdb

  If your server has less than 8 logical cores (e.g. a one CPU server
  with 4 physical cores and hyperthreading enabled has 8 logical cores),
  use # tempdb data files = # logical cores, equally sized

  If your
  server has more than 8 logical cores, start with 8 tempdb data files,
  and add sets of four at a time, equally sized, until the contention is
  alleviated

Note that it is critical to first alter all existing tempDB files so that they are the same size and with the same AUTOGROW settings before you begin. Then create all new TempDB files with the same size and autogrow settings as the existing files.

As for do you currently have a problem? View your wait statistics and determine your top waits, if PAGELATCH_UP or PAGELATCH_EX are up there then you need to investigate further. Glen Berry has a good query for that included here SQL Server Diagnostic Information Queries for September 2016

When you notice a slow down on your SQL Server you should return sys.dm_exec_requests wait type and wait resource information, and keep an eye out for pagelatch_* wait types. You can then pass the wait resource through DBCC PAGE to determine the database and internal pages experiencing page latching. You can also use the very useful query by SQLSoldier here Breaking Down TempDB Contention (part 2) to better determine which, if any, internal database pages are experiencing issues and if adding additional tempDB files is the answer. Assuming that you are seeing pagelatches on the internal GAM, SGAM or PFS pages then you want to keep adding files, based on the above advice until the PAGELATCH_ contention reduces.


Here is an excellent post from Kendra Little regarding this.

http://www.brentozar.com/archive/2012/06/load-test-sqlserver/

There are several tools out there, both commercial and free. In past when I had need I used
Adam Machanic's SQLQueryStress

SQL Load Generator on CodePlex is another free tool you can use also.


How to get SEEK accessing converted ID via view

The issue is that your query through the view is the same as doing

SELECT *
FROM SomeTable
WHERE CAST(SomeTable.ID as NVARCHAR(10)) = N'1'

Pretty much any CAST of a column in a predicate will render that predicate unsargable. The only exceptions that I am aware of are a CAST of datetime column to date and VARCHAR to NVARCHAR under some collations.

There is no such exception for NVARCHAR to INT.

You might hope that it would do something like

WHERE SomeTable.ID = TRY_CAST('Your search string' as int)

but it is not as simple as that. If your search string is '1' the two would return the same results but for the search string '¹' (superscript 1) the casting to int fails but the string comparison compares equal under some collations. Conversely for the search string ' 1' (with leading space) the int cast and comparison would discard the leading space and compare equal but the string comparison would compare unequal (and similarly with empty string which is cast to 0 when converted to int)

Possible solutions

In general you could create either an indexed view or a new index on the base table referencing a computed column but both seem quite suboptimal compared with removing the CAST from the view so the existing index can be seeked.

Computed Column

You could create a computed column on SomeTable with definition CAST(ID as NVARCHAR(10)) and then index that.

ALTER TABLE SomeTable ADD strID AS CAST(ID as NVARCHAR(10));

CREATE INDEX IX ON SomeTable (strID ) INCLUDE (ID, SomeColumn1, SomeColumn2);

SELECT *
FROM ThirdPartyView
WHERE ID = N'1';

Indexed view

Given the restrictions in the question the computed column idea seems ruled out. An alternative will be to create an indexed view but likely you will need to change the query text to get this to work.

In all editions except Enterprise Edition the NOEXPAND hint will be needed to get the indexed view to be matched.
If you are on Enterprise Edition the automatic matching in principle would work whether you indexed the original view or created a copy...
... but index view matching will only be considered at later stages of optimisation. If the query is cheap enough optimisation will end before it gets to that point. In your case in particular you will also need to fight against trivial plan. I added a million rows of data to the table and the indexed view still wasn't hit - as the plan was below the cost threshold for parallelism and it didn't proceed to further optimisation phases beyond trivial.


Create table using select statement Truncated incorrect datetime value:

From the looks of the error message, the UTC should not be part of the date. My guess is that date is not using the DATETIME or TIMESTAMP data type. It must be a string.

Try running it like this :

Create table dummy_table AS
select Cust_ID User_Id , DATE_FORMAT(LEFT(`Date`,19),'%M %Y') DT
from result_table.user_signup_date;

The expression LEFT(Date,19) will drop off the everything after the timestamp.


Use connection string to connect to Azure database from SSMS?

Given the connection string:

Data Source=tcp:xxxxxxx.database.windows.net,1433;Initial Catalog=DatabaseName;User ID=zzzzz@xxxxxxx;Password=aaaaaa;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;Application Name=YourAppName;

Server name: xxxxxxx.database.windows.net,1433 "xxxxxxx" represents the server name
Login: zzzzz@xxxxxxx "zzzzz" represents your username, and "xxxxxxx" the server name
Password: aaaaaa represents your real password

As Pedro mentioned you will only be able to connect to an Azure database if your IP is white listed. The Microsoft has a step by step write up on how to do this in their new portal.


Select range of non-identical date from different table

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point:

SELECT
  'temp' AS sourceTable,
  id,
  ts,
  temp AS measure1,
  humidity AS measure2,
  NULL AS measure3
FROM
  temp_data
WHERE
  ts >= @StartDate AND
  ts <= @EndDate
UNION ALL
SELECT
  'power' AS SourceTable,
  id,
  ts,
  lux AS Measure1,
  kw AS Measure2,
  volts AS Measure3
FROM
  power_data
WHERE
  ts >= @StartDate AND
  ts <= @EndDate


It is possible to replicate from an older version 'master' to a new version master for the next higher major version (5.1 to 5.5 for example): http://dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html

Of course bin logs are portable to the same architecture running on different machines, or replication would be almost useless (there are difficulties with running on different OSes, though).

Since your question only asked about applying a bin log to the second server, I might mention that if you don't have the 'master position' of when the second server should begin reading the binary log, you might have inconsistencies in data. Especially if it has been a long time since you've taken a full backup and no longer have the relevant log entries.

But at the heart of it, Binary Logs from an older major version to the next major version is supported.


Reference assembly in sqlproj not being deployed to server

I played around a bit to figure out what these settings do... when I commented, I only mentioned what our settings are without understanding them; I'm not the project lead for our migration to Database Projects, so I wasn't familiar with the minutiae of this stuff.

The Model Aware property needs to be set to True.

This property is poorly documented, but from my understanding, enabling it will expose the objects within the assembly to the database model so they can be used in the SQLCLR wrappers (i.e., CREATE ... EXTERNAL ...) and the references can be resolved. If the project doesn't include these objects in the model, there's no way they'll be scripted in the output. Also, if there's a reference to an object in the assembly and this property is set to False, the project will fail to build.

The Generate Sql Script property I mentioned is in a similar state of documentation. This controls whether the publicly visible members in the assembly are automatically scripted into the output. We have it disabled to retain the flexibility of renaming the SQLCLR wrappers and selectively including only the objects we need. Which way you set this is up to your discretion.

Hopefully that helps the public documentation of these properties!


Use a subquery:

SELECT array_agg(x ORDER BY x)
FROM (SELECT my_expensive_function(data) AS x
      FROM jsonb_array_elements(json_set) AS data
     ) AS q;


Howto find out details and reasons for idle transactions?

This is the query I am using to identify idle transactions holding locks.

SELECT
    CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName
    ,s.session_id
    ,s.status
    ,s.login_time
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.original_login_name
    ,s.last_request_end_time
    ,CAST(t.text AS nvarchar(4000)) AS [text]
    ,CAST(s.context_info AS varchar(128)) AS [context_info]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
CROSS APPLY (
    SELECT MAX(DB_NAME(dt.database_id)) AS database_name
    FROM sys.dm_tran_session_transactions AS st
    INNER JOIN sys.dm_tran_database_transactions AS dt
        ON st.transaction_id = dt.transaction_id
    WHERE is_user_transaction = 1
    GROUP BY st.session_id
    HAVING s.session_id = st.session_id
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
        SELECT session_id
        FROM sys.dm_exec_requests
    )
    AND s.session_id IN (
        SELECT request_session_id
        FROM sys.dm_tran_locks
        WHERE request_status = 'GRANT'
    )
    AND STATUS = 'sleeping'
    AND is_user_process = 1
    AND program_name NOT LIKE '%SQLAgent - Job Manager%'
OPTION (RECOMPILE, FORCE ORDER);

The reson why they are dangerous is simple: they are holding locks but doing nothing, waiting for the application to either commit or rollback the transaction.

This typically happens when the application performs some heavy task on the client side, without interacting with the database, still keeping transactions open. The worst scenario is applications showing error messages or waiting for user input during a transaction.

If you want to know what happens, you need to hunt down these applications and understand what they are doing while keeping transactions open.


Should I disable triggers, timestamps in a slave in mysql?

Do not disable the triggers on your slaves, and do not configure your slaves to skip errors.
When replication is set up correctly with consistent data sets and identical configurations, including tables and their data, views, triggers, stored functions, and stored procedures, it just works.  If it isn't "just working," then it was already not consistent in one of these ways.  The duplicate key errors are a red flag that your data is not identical, and you don't want to suppress this.
If you aren't already, you should be using binlog_format = MIXED unless you have a specific reason not to.  The default value prior to MySQL 5.6 was STATEMENT but this was largely for legacy reasons.
Using mixed logging allows the optimizer on the master to choose the best way to log each change to the master's data -- either by:

logging the literal and exact query your client sent to the master, so that the slaves can execute that same query, thereby modifying their data (statement based logging), or
logging the specific rows that were changed on the master so that the slave can change those same rows in the affected table without regard to the actual query that caused the changes (row based-logging).

If a statement is determined to be "unsafe" for statement-based logging (meaning that it could potentially cause the data to diverge from its initial identical state), it will be automatically logged as row-based, if you're using MIXED mode.  Examples of this would be statements involving calls to non-deterministic functions like UUID() which would return different values on master and slave.  Row-based logging handles this without a problem, since it passes the literal values across.
You might think NOW() might also be such a function, and that a similar limitation might apply to automatic timestamp columns, if the server clocks are not in sync, but that's not the case -- because with each binary (replication) log entry, the master logs what its current timestamp was at the time the query was executed -- allowing the slave to have a "pseudo-system clock" to actually fudge-in the master's clock's value at the time that statement was executed when needed.  This log entry, incidentally, is how the slave calculates "seconds behind master," by comparing its system clock to the logged timestamps.  (The exception to this is when the slave has executed everything in its relay logs, it always shows "0" seconds behind, because it doesn't know whether the former logs were delayed by clock skew or by an actual replication lag -- so if it has run out of things to execute, it is no longer technically "behind").
If a query is logged using row-based logging, then any data changed by triggers are also logged as row-based log entries, and the triggers on the slave will automatically not fire.
On the other hand, if the same query is logged using statement-based logging, then replication depends on the identical triggers being present on the slave, because the master will not log what the trigger did -- it only logs the query issued, and depends on the slave triggers firing exactly as they did on the master, assuming you want the data on all servers to be consistent.

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.
This behavior is by design. [...]

http://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html
The documentation often speaks of replication in what sounds like terms of either/or (statement or row) but for each query executed MIXED logging will choose the logging method on a per-query basis.  You may also find statements to the effect that row-based logging is "required" in some conditions but this only means that it must be available (i.e., via MIXED); it does not mean that the server must be configured for ROW.  DDL, of course, is always logged as statements, regardless of the binlog format.


Is it possible to install a MS SQL Express Studio, where the studio tool also is included?

You are probably better off with the newer version of Management Studio Express (which will work against 2008 R2, 2008 and 2005):

http://www.microsoft.com/download/en/details.aspx?id=22985

The reason is that if you use the 2008 version and try to manage a 2008 R2 instance, for example, you may run into issues. Always better to have the client tools >= the highest version of the engine that you need to support.

Note that Management Studio Express is missing some of the SSMS functionality, such as managing agent, so if you have to manage other editions you may be better off investing $49 (or less, depending on vendor) into developer edition, which comes with the full version of SSMS:

http://bit.ly/oPq7NY


Recommended Approach to Programmatically Backing Up/Restoring Oracle DB

If you want to clone prod. db into dev. env. regulary I would recommend you to use RMAN for whole database. Or exp/imp (expdp/impdp) for single schema.
RMAN is not easy to use for developers who have no Oracle experience, but finally you will find this approach fastest and safest.

If you're about to clone your prod db into some reporting server maybe you should look at "Active Data Guard".

In Oracle you will find the "right" technology for every purpose. The final decision will depend on your real need(goal).


WITH RECURSIVE with union among the recursive term

The three parts of the desired recursive term select rows from the same CTE, so you can transform them into a single SELECT:

SELECT ...
FROM cte_name t1
WHERE (t1.type_event = 'Transfering' AND t1.arg2 = t2.arg1 AND t1.arg6 = t2.arg2)
   OR (t1.type_event = 'Replacement' AND t1.arg2 = t2.arg1 AND t1.arg6 = t2.arg2)
   OR (t1.type_event = 'ex')

Reordering the columns can be done with a CASE expression:

SELECT 'ex' AS type_event,   -- same for all three
       t1.arg2 AS arg1,      -- same for all three
       CASE t1.type_event
         WHEN 'Transfering' THEN t1.arg6
         WHEN 'Replacement' THEN t1.arg6
         WHEN 'ex'          THEN t1.arg1
       END AS arg2,
       ...


Cross-database foreign keys vs. database design best practice

MySQL allows to create cross-database foreign keys - but is it a good method of ensuring data integrity? Is it possible to give a general answer or is it project-dependent? What are the advantages and what threat does it pose?

MySQL doesn't really have a concept of "database". This is because MySQL "evolved" confusing "database" for "schema" and never really fixed it. If you're talking to a real DBA, this is not a cross-database foreign key. So for MySQL, this is perfectly fine and acceptable.

Even in version 8.0 of MySQL,

  CREATE SCHEMA is a synonym for CREATE DATABASE.

In PostgreSQL, for instance, we have "schema" and "databases". And cross-schema foreign keys are perfectly fine, but you can't do cross-database fkeys.

psql (9.6.3, server 9.5.6)
You are now connected to database "foobar" as user "ecarroll".
foobar=# CREATE TABLE foo ( id int primary key );
CREATE TABLE
foobar=# INSERT INTO foo SELECT * FROM generate_series(1,10);
INSERT 0 10
foobar=# \connect test;
psql (9.6.3, server 9.5.6)
You are now connected to database "test" as user "ecarroll".
test=# CREATE TABLE foo ( id int references foobar.foo.id, name text );
ERROR:  cross-database references are not implemented: "foobar.foo.id"

That's what a cross-db fkey would look like. Notice it would require explicit schema specification and I'm not aware of any database that implements schema's and databases that permits it.

The downside of the MySQL confusing databases and foreign keys is pretty clear. If you have full access to a single specific database, you do not have the ability to organize your own content. Whereas in PostgreSQL, if you full access to a single database you can create any amount of arbitrary schemas. You don't need SUPER USER nor the ability to CREATE DATABASE


You need to set action disabled which is given in values.

<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[TestServerAudit$A]]></session><action>event disabled</action><object><![CDATA[audit_event]]></object></action_info>

For more information, see SQL Server Audit Log


password expire duration and maximum invalid login attempts

Cassandra doesn't have such functionality (yet). In DSE you can enable audit logs, and log all authentication attempts & their result - but I don't remember such functionality for Cassandra


Are lots of columns or joins better in Django

I'm not sure how to phrase this, but this type of application isn't what Django is made for.

Django is made for simple CRUD based websites, with an admin interface that comes for free, but I don't think trying to shoehorn a search engine in an MVC framework is going to work very well. (however there may be extensions by now, my Django experience is old and rusty)

Creating 300 columns, resulting in 300 ORs is impossible to index for, and neither is 30 tables with 10 columns each joined together, if all columns have to be search-able.

The only reasonable approach I can think of is going for an EAV pattern (for which there is an extension: django-eav), but that is going to create other issues in my opinion.

I also believe you are misunderstanding how Django is supposed to work, but you are supposed to define your data model in your model files and be able to sync it to any db. Since you are asking about a NoSQL database I suspect you are going to have to write custom code anyway.

Depending on how much code you would have to write to shoe-horn your requirements in a framework that was never intended to do this, you might as well just write a custom application.

I think, depending on your requirements (which aren't necessarily all that clear) you should go for a full text index search or any indexing solution since searching 300 columns, or EAV values, whatever you pick as a solution to match your chosen framework, is still going to be a pain.

For an obviously data driven requirement the data should dictate the platform, not the other way around.

You are trying to desperately make a framework do something it wasn't designed for.


External Table: VARCHAR in Create but CHAR in Access Parameters

Datatypes inside the database, and used by SQL*Loader or the ORACLE_LOADER driver are different.

SQL*Loader does not know the VARCHAR2 datatype. It has a VARCHAR type, but that type is nonportable, meaning it is platform dependent.

SQL*Loader Datatypes

  VARCHAR fields can be loaded with correct results only between systems
  where a SHORT data field INT has the same length in bytes. If the byte
  order is different between the systems, or if the VARCHAR field
  contains data in the UTF16 character set, then use the appropriate
  technique to indicate the byte order of the length subfield and of the
  data. The byte order of the data is only an issue for the UTF16
  character set.

The CHAR type of SQL*Loader is portable.

  Portable Datatypes

  The portable datatypes are grouped into value
  datatypes and length-value datatypes. The portable value datatypes are
  as follows:

  CHAR

  Datetime and Interval

  GRAPHIC

  GRAPHIC EXTERNAL

  Numeric EXTERNAL (INTEGER, FLOAT, DECIMAL, ZONED)

  RAW

  The portable length-value datatypes are as follows:

  VARCHARC

  VARRAWC

Using a non-portable type you may or may not be able to load the data on a different platform:

Loading Data Across Different Platforms

  When a data file created on one platform is to be loaded on a
  different platform, the data must be written in a form that the target
  system can read. For example, if the source system has a native,
  floating-point representation that uses 16 bytes, and the target
  system's floating-point numbers are 12 bytes, then the target system
  cannot directly read data generated on the source system.

  The best solution is to load data across an Oracle Net database link,
  taking advantage of the automatic conversion of datatypes. This is the
  recommended approach, whenever feasible, and means that SQL*Loader
  must be run on the source system.

  Problems with interplatform loads typically occur with native
  datatypes. In some situations, it is possible to avoid problems by
  lengthening a field by padding it with zeros, or to read only part of
  the field to shorten it (for example, when an 8-byte integer is to be
  read on a system that uses 4-byte integers, or the reverse). Note,
  however, that incompatible datatype implementation may prevent this.

  If you cannot use an Oracle Net database link and the data file must
  be accessed by SQL*Loader running on the target system, then it is
  advisable to use only the portable SQL*Loader datatypes (for example,
  CHAR, DATE, VARCHARC, and numeric EXTERNAL).


A tail-log backup of the source database will be taken ? Why I am seeing this warning


As long as your recovery model is Simple and full backups for only last 7 days you do not have any chance to see what was deleted in June.

Entries in the log file for Simple recovery are discarded when SQL Server does not need them for crash recovery.

The answer to your second question - yes, you have to switch to Full recovery. Then with the help of Apex software (or some other) you probably will be able to restore deleted in the past records if you have log backups for that period of time.
Be careful when switching to Full recovery - it has gotchas. Make sure to learn this Q/A.


Have you read the msdn documentation and looked at the examples?

https://msdn.microsoft.com/en-us/library/ms146067.aspx

Edit: Try this.

WITH MEMBER Measures.[AVGSALE] AS
AVG (
    DESCENDANTS(
          [DIMPROPERTYLENDER].[Hierarchy].[NAME],
          [DIMPROPERTY TIME].[TIMEDIM].[MONTH].ALLMEMBERS)
          ),
          [Measures].[VAL1]
    )

SELECT NON EMPTY Measures.[AVGSALE] ON 0,
    NON EMPTY ([DIMPROPERTYLENDER].[Hierarchy].[NAME], [DIMPROPERTY TIME].[TIMEDIM].[MONTH]) ON 1

from [PropertyData2016]

PS: you might need to use [DIMPROPERTY TIME].[TIMEDIM].[MONTH].[MONTH] in your final select.


Mysql : foreign key relative to multiple tables

FOREIGN KEYs have severe limitations.  I think you have exceeded them.

Complex data structures cannot depend on FKs for integrity, your code is required.

Suggest that you build Stored Routines and/or subroutines in your application language, and encapsulate the integrity constraints there.  And build a clean API into the routines to make it easy for the clients to achieve their goals while the routine achieves the integrity goals.


REST webservice as a Foreign Data Wrapper in postgresql

I actually had a similar question and found a simple solution that worked for me.
I took Multicorn and wrote a couple of lines of python in order to import a REST API:

Given a REST API

curl  -H "authentication:ZQzBedExV8YGEztzUJdfqe0nsGNGdstZ" -H "Content-Type: ap/json;charset=UTF-8"  -X POST  http://127.0.0.1:3000/system_module/list.json  -d '{}'

I wrote the following simple class:

from . import ForeignDataWrapper
from .utils import log_to_postgres
from logging import INFO
import json
import urllib2
def anubis(apikey):
    req = urllib2.Request('http://127.0.0.1:3000/system_module/list.json')
    req.add_header('apikey', apikey)
    req.add_header('Content-Type', 'application/json;charset=UTF-8')
    resp = urllib2.urlopen(req)
    results = resp.read()
    results = json.loads(results)

    items = results['items']
    for item in items:
        yield {'name': item['name'].encode("utf-8"),
               'description': item["description"].encode("utf-8"),
               'apikey':apikey}
class AnubisFdw(ForeignDataWrapper):
    def execute(self, quals, columns):
        if not quals:
            return (None, None, None)
        for qual in quals:
            if qual.field_name == "apikey" or qual.operator == "=":
                return anubis(qual.value)

Download Multicorn http://multicorn.org
have a look in the subdirectory python/multicorn
In my case a had to retrieve the data form a REST API returning JSON arrays. Thus I had to parse the josn data returned by the REST API. But in your case you'll have to parse your XML data which should not be a big deal in python.

Best Regards

Babak


What does the wait type PREEMPTIVE_XE_CALLBACKEXECUTE mean?

I believe this issue was fixed in SQL Server 2008 R2 SP2 Cumulative Update #1 (10.50.4260):

KB #2511963 - FIX: Query stops responding in SQL Server 2008 or in SQL Server 2008 R2 if the query alters or stops an Extended Events session

They don't mention your specific wait type but I have seen others who have and stated that this fix resolved their issue. So, if you are literally on SP2 with no CU (10.50.4000), you should consider at least that CU, or a later one (the most recent CU for 2008 R2 SP2 is CU7 (10.50.4286)).


Query syntax is checked at java level or Database Level?

Two different things probably:

Syntax is checked in MySQL server. There may be connectors/client libraries which would do the parsing and checking before sending it, but it would only mean doing the same work twice. MySQL server receives query as string and then processes it. Even for prepared statements server receives statement as text and compiles it.
SQLException is probably extended Java Exception, which is purely Java side construct (generally as part of JDBC code). So this exception is thrown by Java MySQL client library when it sends your query and instead of data, server returns some error message. Client and server communicate by socket or tcp, so there is no way how server could throw exception directly to client. It has to signalize error by some other means (probably different response packet type or something similar)


I'm not sure I understand your scenario, but the id returned from the CTE is unlikely to match any other table, since it has just been invented. Would something along:

WITH inserted as (
    INSERT INTO food_update(food_id, value)
    values(1, 12) RETURNING id, food_id
)
SELECT *
FROM inserted
INNER JOIN food
    ON food.id = inserted.food_id

do?


Checking vulnerabilities for Oracle Data Integrator version 12.2.1.3.0

Logon to My Oracle Support and search for "CVE 2020-5398".  I just did that and there are many hits listing patches that address this.
But let's suppose a hypothetical question.  Suppose the vulnerability is not fixed by any available patch.  What is your next course of action?


SQL Error (1093): Table 'j1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

UPDATE jobs j1, ( SELECT jh.job_id
                  FROM jobs_history AS jh
                  INNER JOIN jobs j ON j.id = jh.job_id
                                   AND j.job_last_status =7
                  WHERE jh.job_status = 7
                  GROUP BY jh.job_id
                  HAVING COUNT(jh.job_id) = 2 ) j2
SET j1.job_last_status = 10,
    j1.updated_at = '2020-05-04 09:59:11'
WHERE j1.id = j2.job_id

or the same in JOIN form:

UPDATE jobs j1
  JOIN ( SELECT jh.job_id
         FROM jobs_history AS jh
         INNER JOIN jobs j ON j.id = jh.job_id
                          AND j.job_last_status =7
         WHERE jh.job_status = 7
         GROUP BY jh.job_id
         HAVING COUNT(jh.job_id) = 2 ) j2 ON j1.id = j2.job_id
SET j1.job_last_status = 10,
    j1.updated_at = '2020-05-04 09:59:11'


Transaction and data consistency during a failure

Bear with me, this is a complicated question to clarify and we may go through a few rounds of edit and commenting to plug the gaps. From the way your question is phrased I'm guessing you're not differentiating the atomicity, isolation, consistency and durability elements of ACID.

  When a database begins a transaction, all statements executed in that
  transaction are isolated and atomic (and consistent and durable).
  These are pretty much the definition of a transaction.

The isolation part of ACID is widely misunderstood. There is a degree to which transactions are isolated from each other, as determined by the transaction isolation level. The other elements of ACID are absolute.

  Wikipedia states that there are some databases that insure a
  transaction remains isolated by locking the rows and not unlocking
  them until the transaction has committed.

This relates to the isolation part of ACID, it doesn't have any impact on your main question.

  My question is: How can a database that solely relies on locking
  guarantee consistency? If a power outage occurs mid-transaction, there
  may be data partially written to the row.

Your example is not concerned with consistency, it's durability and atomicity. These are guarenteed by write ahead logging (WAL). With WAL, all changes are written to the undo/redo log before they are applied to the data.

In the event of a power failure, a recovery process is run which will read the log to identify a) "mid-flight" transactions that did not commit and b)  transaction that did commit but which were not applied to the data. The changes from a) transactions are undone (rolled back), returning the data to its pre-transaction state. The changes from b) are redone (rolled forward), ensuring the data is in the expected post-transaction state.

  Even for databases like SQL Server that use a Temporary DB to perform
  all the transactions, what happens if a power outage occurs as the
  database is committing the transactions to disk?

TempDB (assuming that's what you're referring to) has absolutely nothing to do with SQL Servers execution of transactions. Are you confusing the role of TempDB in snapshot isolation levels?


You will indeed want a third table (food_plate) with two foreign keys, food_id to the food table and plate_id to the plate table. The food and plate tables should not reference each other.


T-SQL performance tuning - Query running slower

It's worth pointing out that the estimated cost of the sort (268491.28 optimizer units) probably isn't realistic. The query optimizer thinks that it will need to sort 180 GB of data at that step. However, the sort is performed with a memory grant of 24 GB and it doesn't spill to disk. In fact, the most used memory during the plan is 8 GB. With that said, based on the information that we have the sort is probably the performance bottleneck.

It should be possible to define an index that removes the need for that sort. That's a perfectly valid optimization strategy. If you're looking for an alternative you could consider a parallel apply pattern. The idea is to use parallel nested loops to do a bunch of little sorts instead of one large sort which scales better. I don't know how well it will work with MAXDOP 2 and I can't see your data, but it could be worth a try. Something like this might work:

SELECT MOT.ObjectName,/*mot.Description, */ ObjectID, QueuedAt, ErrorMessage, ua.SfUpdateAction, us.SfUpdateStatus, cte_MaxStatus.ProcessedAt
FROM DBZ.dbo.ModuleObjectTypes MOT
CROSS APPLY
(
    SELECT
    s.QueuedTxUpdateID, s.ObjectID, s.QueuedAt, s.ErrorMessage, s.fkSfUpdateStatus, s.fkSfUpdateAction, s.ObjectType, s.ProcessedAt,
    rn = ROW_NUMBER() OVER(PARTITION BY s.ObjectID ORDER BY s.QueuedAt DESC)
    FROM dbo.SfQueuedTxUpdates s
    WHERE s.ObjectType = MOT.ObjectTypeID
) cte_MaxStatus
       LEFT JOIN dbo.SfUpdateAction UA ON cte_MaxStatus.fkSfUpdateAction = ua.SfUpdateActionID
       LEFT JOIN dbo.SfUpdateStatus US ON cte_MaxStatus.fkSfUpdateStatus = US.SfUpdateStatusID
WHERE fkSfUpdateStatus <> 3
AND cte_MaxStatus.rn = 1;

In the query plan, what you're looking for is nested loops with index seeks against SfQueuedTxUpdates on the inner side. There should be one sort per ObjectTypeID instead of one large sort. The query above may not be sufficient to get a nested loop join plan. You may need to use a superfluous TOP or other tricks (see the linked video for an explanation of the technique).

Note that I'm making an assumption that doing an INNER JOIN to DBZ.dbo.ModuleObjectTypes won't change the results. If that's not true, you could put distinct ObjectType values from SfQueuedTxUpdates into a temp table. Using recursion to get those distinct values can be more efficient than scanning the entire index. It depends on the ratio of rows in the table to distinct values.


Bad idea, but if you insist and your data qualifies for the 8K bytes limitation -

Wide Tables

  A wide table is a table that has defined a column set. Wide tables use
  sparse columns to increase the total of columns that a table can have
  to 30,000. The number of indexes and statistics is also increased to
  1,000 and 30,000, respectively. The maximum size of a wide table row
  is 8,019 bytes. Therefore, most of the data in any particular row
  should be NULL. To create or change a table into a wide table, you add
  a column set to the table definition. The maximum number of nonsparse
  columns plus computed columns in a wide table remains 1,024. By using
  wide tables, you can create flexible schemas within an application.
  You can add or drop columns whenever you want. Keep in mind that using
  wide tables has unique performance considerations, such as increased
  run-time and compile-time memory requirements. For more information,
  see Performance Considerations for Wide Tables.


Why group_concat and SQL_CALC_FOUND_ROWS do not work with each other?

From the question itself, I see two problems

The return value of the query will be, at most, one row. SQL_CALC_FOUND_ROWS is uesless
You are looking for the number of rows that piled up in the GROUP_CONCAT result

You have to extrapolate the row count from the result of the group_concat. Here is how

If there is a return value from the GROUP_CONCAT, set @found_rows = 1 + the number of commas in the return value.
If no value is returned for the GROUP_CONCAT, set @found_rows = 0

BTW there is a heads you need to be aware of: GROUP_CONCAT's max length is 1024 by default. You should extend it to 10M.

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(ca.id)
INTO @IDList
FROM notification AS ca
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 '
AND  ca.date!=""
AND  ca.alert_msg_id
NOT IN(26,27) AND ca.from_id != '777'

SET @found_rows = IF(
    LOCATE(',',IFNULL(@IDList,''))=0,0,
    LENGTH(@IDList) - LENGTH(REPLACE(@IDList,',','')) + 1
);

SELECT @found_rows,@IDList;

Give it a Try !!!


MySQL HAVING Causes Extra Increment of Session Variable

Found a way to do this, though it feels like a hack and I hope somebody is able to find a different/more elegant way. The sessions variables are incremented once in WHERE and HAVING clauses, so I just added a conditional to see whether or not the column has been incremented yet. Now it's only getting incremented in the HAVING clause. Example of my hacky fix: http://sqlfiddle.com/#!9/cb25a4/35


Change posgresql 9.3 slave roll password

the person who set it up left the password the same as the password
  for the super user account on our other server

If the other server is the master, it's normal because having different passwords in the replicated database is not possible.
PostgreSQL replicates everything from the master, including user accounts and their passwords. There's no way to divert that temporarily.

If someone needs to be superuser on the secondary without knowing this password, a different authentication method should be used on this server, such as Certificate Authentication, or external methods (ldap, radius...) . As a configuration file, the pg_hba.conf can be different on the slave.


Mysql Alter table with Instant algorithm

The manual goes into a lot of gory details; you can mostly ignore them.  In general, if you do not provide an "algorithm", the ALTER will pick the fastest way.  If you specify INSTANT, but that algorithm is not possible for what you want to change, it will spit at you.  (No harm done.)
At one extreme (the COPY end):  Changing the PRIMARY KEY (on InnoDB) requires rebuilding the entire table, reordering the data, and rebuilding all the indexes (because the PK is part of every secondary index).
At the other extreme (the INSTANT end):  Adding an option to the end of an ENUM list does not require touching the data at all.


What's faster than Mirroring tables, compare or write?

For a question like this, you really need to time the queries to check performance.

My guess is that the logging overhead on the update is going to be more expensive than any reasonable list of "or" statements.  If you are going to use the comparisons, though, you should have a correct statement that takes NULLs into account:

where (tb_B.value1 <> tb_A.value1) or
      (tb_b.value1 is null and tb_a.value1 is not null) or (tb_b.value1 is not null and tb_a.value1 is null) or
      tb_B.value2 <> tb_A.value2 or
      (tb_b.value2 is null and tb_a.value2 is not null) or (tb_b.value2 is not null and tb_a.value2 is null)

You can make the query more efficient by defining an index on each table with (id, value1, value2).  This will allow all the logic to use the index.  A page would only be necessary in the page cache when a mismatch is found.


I found my answer, thanks!

select cant.id,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST from cant
union
select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT.FK_ID_CHEST from col
    inner join cant
        on cant.id=COL.FK_ID_COL_DMA


Unfortunately, without knowing your system (inside and out), it's going to be difficult for anyone to tell you how an upgrade is going to impact you.

I'd recommend checking the release notes to verify that the upgrade path won't mess up the application.

Here are a couple changes that I noticed that you might need to know about:

Remove ipcclean utility command
Changes in the TRUNCATE and LOCK statements
SELECT DISTINCT and UNION/INTERSECT/EXCEPT no longer always produce sorted output
Disallow LOCK TABLE outside a transaction block
Remove support for the (insecure) crypt authentication method
Modify the ALTER TABLE syntax to allow all reasonable combinations for tables, indexes, sequences, and views

I'd recommend trying this on a test machine first (if possible).


Optimal engine for small lookup tables in MySQL

I answered a similar question in Aug 2011 : Which DBMS is good for super-fast reads and a simple data structure?
Since you are asking about MySQL and which storage engine. To be honest, it is hard to say because there are rare occasions when MyISAM can outperform InnoDB when it comes to SELECTs.
Here are some of my past posts on this controversy

Sep 20, 2011 : Best of MyISAM and InnoDB
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Jul 05, 2012 : InnoDB vs MyISAM with many indexes
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

You might asking right now : Why would I ever favor MyISAM over InnoDB ?
Take a look at this diagram (created by Vadim Tkachenko, Percona CTO)

MyISAM will cache indexes and your table would have 2 indexes (PRIMARY KEY on id and an index of Name). On the other hand, InnoDB has too many moving parts to accommodate for, especially if the InnoDB Buffer Pool has to load and dismiss 16K pages periodically.
To be fair, you should do an experiment.

Go to my post What are the main differences between InnoDB and MyISAM?. Read it carefully.
Setup a server using MyISAM and all your tables using ROW_FORMAT=Fixed (See my post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?) and use a large key_buffer_size.
Setup another server using InnoDB and a large innodb_buffer_pool_size.
Add your data to both server and query them like crazy.

This will give you the best assessment for Storage Engine choice for your dataset.
GIVE IT A TRY !!!


How to do an =ANY(SELECT...query in postgresql?

Unfortunately = ANY (array) only works with an array literal on the right hand side, not a sub-select.

You need to "normalize" your de-normalized model, using unnest():

SELECT latitude, longitude
FROM userloc
WHERE id IN (SELECT unnest(interested)
             FROM donedeals
             WHERE deals_id = 64);

If deals_id is unique in the donedeals table, another option is to "convert" the id on the left side to an array and then use the "is contained by" operator: <@:

SELECT latitude, longitude
FROM userloc
WHERE array[id] <@ (SELECT interested
                    FROM donedeals
                    WHERE deals_id=64 );

Not sure which one would be faster. You will need to check the execution plan.


SQL recursion and cte - dates timeseries

For this you need to change the anchor set of the recursive CTE, so each row starts with the dates from the other table before starting the recursive portion.

DECLARE @AmountDays INT = 10

;WITH GeneratedDays AS
(
    -- Anchor
    SELECT
        DateSeriesIdentifier = T.DateSeriesIdentifier,
        Date = T.Date, -- Assuming it's DATE data type
        RecursionLevel = 1
    FROM
        OtherTable AS T

    UNION ALL

    -- Recursion
    SELECT
        DateSeriesIdentifier = G.DateSeriesIdentifier,
        Date = DATEADD(DAY, 1, G.Date),
        RecursionLevel = G.RecursionLevel + 1
    FROM
        GeneratedDays AS G
    WHERE
        G.RecursionLevel + 1 <= @AmountDays
)
SELECT
    G.DateSeriesIdentifier,
    G.Date,
    G.RecursionLevel
FROM
    GeneratedDays AS G
ORDER BY
    G.DateSeriesIdentifier,
    G.RecursionLevel

This is a result from a set I used to test which had dates 2016-02-04, 2018-01-19 and 2018-08-30:

DateSeriesIdentifier    Date            RecursionLevel
21                      2016-02-04      1
21                      2016-02-05      2
21                      2016-02-06      3
21                      2016-02-07      4
21                      2016-02-08      5
21                      2016-02-09      6
21                      2016-02-10      7
21                      2016-02-11      8
21                      2016-02-12      9
21                      2016-02-13      10
620646                  2018-01-19      1
620646                  2018-01-20      2
620646                  2018-01-21      3
620646                  2018-01-22      4
620646                  2018-01-23      5
620646                  2018-01-24      6
620646                  2018-01-25      7
620646                  2018-01-26      8
620646                  2018-01-27      9
620646                  2018-01-28      10
639701                  2018-08-30      1
639701                  2018-08-31      2
639701                  2018-09-01      3
639701                  2018-09-02      4
639701                  2018-09-03      5
639701                  2018-09-04      6
639701                  2018-09-05      7
639701                  2018-09-06      8
639701                  2018-09-07      9
639701                  2018-09-08      10


Can I use replication/mirroring to have a "Current" database and a "Full History" copy?

Define the data is getting to be a bit much?  I've got clients with 1B+ rows in a table and no performance problems at all.

You could use SQL Server replication to replicate inserts and updates to a remote machine, but basically have it ignore the deletes.

Before I'd start looking at hacking together something like that I'd probably look at index tuning or query rewriting to see what I can do to resolve the performance problems with the queries that I've got.


On the MySQL side...

mysql> SELECT FROM_UNIXTIME(1539167364779001 * 1e-6);
+----------------------------------------+
| FROM_UNIXTIME(1539167364779001 * 1e-6) |
+----------------------------------------+
| 2018-10-10 03:29:24.779001             |
+----------------------------------------+
1 row in set (0.00 sec)

Notes:

Having a TS in microseconds is rather unusual; where is it coming from?
I multiplied by one-millionth instead of dividing by a million because the arithmetic rounds division to some number of decimal places.
Are there timezone issues?
That value should nicely slip into a column declared TIMESTAMP(6) or DATETIME(6).
To remove the leading zero from the hour and/or strip off the fractional sections, use the function DATE_FORMAT().  (Oh, was there an implied am/pm?)  (Oh, you wanted truncation?)


How to change the reporting services report service mode from Native to integrated?

You might be able to do this by creating a new ReportServer database and choosing integrated mode as mentioned here
rather than going through a full re-install.


Depending on Foreign Key Constraints to avoid deletion

It seems like your intention is to delete the parent record when the last child is deleted.

If so, while you can issue an unconditional DELETE to remove the parent and let foreign key relationships with existing children prevent it, this may not be the best strategy. To make it work at all you must do the DELETE of the parent in a different transaction (or sub-transaction) to the DELETE of the child, otherwise the whole transaction's work will be undone when the DELETE of the parent fails and the transaction aborts.

Doing it in an entirely separate transaction is not very safe as it'll leave a window where there's a parent with no children - and if you ever re-use keys it's totally unacceptable.

Deleting the child then deleting the parent in a subtransaction using SAVEPOINT and ROLLBACK TO SAVEPOINT is OK, albeit awkward. You need to think about what happens when two concurrent transactions delete the last two children of the same parent, though; you'd land up with an orphan parent record because each would see the "remaining" child the other one is in the process of deleting. To prevent that you'll need to SELECT ... FOR UPDATE the parent before deleting the child.

BEGIN;
SELECT 1 FROM parent WHERE parent_id = 1 FOR UPDATE;
DELETE FROM child WHERE child_id = 11 and parent_id = 1;
SAVEPOINT delete_parent;
DELETE FROM parent WHERE parent_id = 1;
-- In the application see if the DELETE was successful. If it was, COMMIT.
-- if it failed, run:
ROLLBACK TO SAVEPOINT delete_parent;
-- before committing the deletion of the child.
COMMIT;

Alternately, because you're forcing transactions that remove children from a parent to occur serially, you can just replace the SAVEPOINT and everything below with:

DELETE FROM parent
WHERE parent_id = 1 AND NOT EXISTS (
  SELECT 1 FROM child c WHERE c.parent_id = parent.parent_id
);

An alternative strategy is to use a SERIALIZABLE isolation transaction to optimistically delete the parent instead of using the initial SELECT ... FOR UPDATE. If somebody else creates a new child or does something else that'll conflict, serializable isolation in PostgreSQL 9.1 or above will abort one of the conflicting transactions. Your application must be prepared to deal with errors and re-run aborted transactions.


What is the meaning of this operator: *=

Your query is using the pre-ANSI (and possibly original) JOIN syntax. Back in "the day", joining tables was done by specifying all of the tables, separated by commas, in the FROM clause, and then specifying the join condition(s) in the WHERE clause. Equating the current ANSI style with the old style gives you the following chart of options:

[INNER] JOIN: TableA.Field = TableB.Field
LEFT [OUTER] JOIN: TableA.Field *= TableB.Field
RIGHT [OUTER] JOIN: TableA.Field =* TableB.Field
 FULL [OUTER] JOIN: TableA.Field *=* TableB.Field
CROSS JOIN: {nothing specified to relate the two tables}

The pre-ANSI syntax is / was supported (sometimes with slight variation) by the various RDBMSes. However, it has been deprecated for a while in Microsoft SQL Server, and was officially removed in SQL Server 2012 (as you are now seeing). This was noted in the Discontinued Database Engine Functionality in SQL Server 2012 MSDN page (2nd to last row in the grid).

In your query, you have 3 tables:

FROM survey,assay,geology

and two LEFT JOIN conditions:

AND survey.hole *= assay.hole
AND survey.hole *= geology.hole

But yes, you can rewrite it as follows:

SELECT sy.hole, survey_date, pit, cu, rec, zn, mn, north, east, elevation,
       rtrim(min_type) AS [min_type], rtrim(rock_type) AS [rock_type],
       rtrim(alt_type) AS [alt_type], rtrim(min_style) AS [min_style]
FROM survey sy
LEFT JOIN assay ay
       ON ay.hole = sy.hole
LEFT JOIN geology gy
       ON gy.hole = sy.hole
WHERE  mined_out IS NULL
AND pit = (SELECT fmpit FROM tyrctl WHERE source = 'export by date')
AND (
      survey_date >= (SELECT fmdate FROM tyrctl WHERE source = 'export by date')
  AND survey_date <= (SELECT todate FROM tyrctl WHERE source = 'export by date')
    )

Technically, you don't need the parenthesis around the two survey_date conditions in the WHERE clause as they are all AND conditions, but if you prefer it for readability then it is fine.

As @ypercube pointed out in a comment on this answer, the query can be further simplified to since the 3 subqueries in the WHERE clause all reference the same row of the same table. So that could look at follows:

SELECT sy.hole, survey_date, pit, cu, rec, zn, mn, north, east, elevation,
       rtrim(min_type) AS [min_type], rtrim(rock_type) AS [rock_type],
       rtrim(alt_type) AS [alt_type], rtrim(min_style) AS [min_style]
FROM survey sy
INNER JOIN tyrctl tl
        ON sy.survey_date >= tl.fmdate
       AND sy.survey_date <= tl.todate
       AND sy.pit = tl.fmpit
       AND tl.source = 'export by date'
LEFT JOIN assay ay
       ON ay.hole = sy.hole
LEFT JOIN geology gy
       ON gy.hole = sy.hole
WHERE  mined_out IS NULL;


Is it possible to make a high availability PostgreSQL database cluster by open source softwares?

With all high-availability solutions you will always have some down-time during a failover, typically less than a minute.

The reason is that the clusterware has to determine reliably that a member is down before it can fail over. That means that failover is only initiated after several attempt to communicate with the cluster member have failed.

You can reduce failover time by making these tests try less often, but that increases the risk of an unnecessary failover (for example, caused by a one-time network glitch). Unnecessary failovers reduce the total uptime of your database, so you don't win anything that way.


Reindexing / rebuild Indexes on Linked Server tables

Four-part-naming syntax is not supported for ALTER INDEX.

Try this:

EXEC ('ALTER INDEX ALL ON [dbname].[dbo].[tablename] REBUILD WITH (FILLFACTOR = 90);')
AT LinkedServerName;


Strange Postgres Hstore syntax with curly braces {key=value}

It turns out it was a bug in Jailer ;-)

https://sourceforge.net/p/jailer/discussion/700499/thread/4db0ed1e/?limit=25#9eb5


Sorting Report entries on alphanumeric combination

Your tag says MS Access, if you are using Jet/ACE in MS Access, you can sort by a user defined function.

Very roughly

SQL

SELECT t.ID, t.SortHere, SortOrder([SortHere]) AS ToSort
FROM Table t
ORDER BY SortOrder([SortHere]);

Code

Function SortOrder(ToSort)

Part1 = Left(ToSort, 1)

For i = 2 To Len(ToSort)
    If Not IsNumeric(Mid(ToSort, i, 1)) Then
        Part2 = Part2 & Mid(ToSort, 2, i - 2)
        Part3 = Mid(ToSort, i, 1)
        Part4 = Mid(ToSort, i + 1)
        Exit For
    End If
Next

If Part2 = vbNullString Then
    Part2 = Mid(ToSort, 2)
End If

SortOrder = Part1 & Format(Part2, "0000") _
          & Part3 & Format(Part4, "0000")

End Function

I am sure you can come up with a better function with a little thought.


MySQL passwords SHA512 using AES_ENCRYPT instead of ENCRYPT?

----- 2014-09-25 5.7.5 Milestone 15 -- Security Notes -- -----
Incompatible Change: MySQL 5.6 deprecated passwords that used the older pre-4.1 password hashing format. Support for these passwords is now removed, which involves the following changes. Applications that use any feature no longer supported must be modified.

Playing around with passwords seems like a dumb thing to have in a tutorial.  Please skip that section and move on.


Recover corrupted blocks in Oracle 10g XE

As you discovered by yourself, RECOVER is just the process of applying logs .

Since corruption occurs at physical level, you must restore the affected datafiles from a backup taken BEFORE the corruption occurs. RESTORE rebuilds the physical attributes of the datafile, so, any physical corruption (eg. disk failure) will be fixed.


Why does mysql adds a default timestamp on insert/update even if I did not specify anything?

This is the default behaviour of the (first) timestamp column. Many more details can be read in the documentation: Automatic Initialization and Updating for TIMESTAMP.

If you don't want this, you have to explicitly tell MySQL when you create the table. You can declare a default value (or default null) and the automatic properties will be supressed. Any of the following will do (with differences in behaviour explained):

Option 1: not null and default 0

brought_in timestamp NOT NULL DEFAULT 0,             -- option 1a

brought_in timestamp DEFAULT 0,                      -- option 1b

These two options are identical. The column is defined as NOT NULL and has a default value of 0. Note that TIMESTAMP is the only datatype that has a default property of NOT NULL, in contrast with all other datatypes and with the ISO/ANSI standard.

If you try to insert NULL in the column, the CURRENT_TIMESTAMP is inserted (surprise, another MySQL feature.)

Option 2: null and default null

brought_in timestamp NULL DEFAULT NULL,              -- option 2a

brought_in timestamp NULL,                           -- option 2b

These are identical, they allow NULL (and both result in default being NULL as well.)

If you try to insert NULL in the column, it is allowed and inserted fine (no surprises here.)

Option 3: null and default 0 or some other constant

brought_in timestamp NULL DEFAULT 0,                 -- option 3a

brought_in timestamp NULL DEFAULT '2013-01-01',      -- option 3b

This is similar to option 2 but has a different default value.

If you try to insert NULL in the column, it is allowed and inserted fine, too (no surprises here either.)


Community wiki answer:

You are connecting to PostgreSQL 10, which is not supported.

I am not aware if there is a pgAdmin III version for server version 10 or higher. One choice could be pgAdmin4, or any other client - if you plan to do serious work, consider psql, the command line client.


Two conversions of practically the same number giving two different results

For what it's worth, this behaviour changes in SQL Server 2016. When I try against 2017 with COMPATIBILITY_LEVEL = 120 I see your observed behaviour. When COMPATIBILITY_LEVEL = 130 both input values produce the same rounded output value 9.77.

There is some documentation here but it is not conclusive. I do not have 2014 documentation to hand but it would be interesting to compare for differences.

You may find some helpful information in the Microsoft Support article SQL Server and Azure SQL Database improvements in handling some data types and uncommon operations.


Use federated table to sync only selected columns

The data in federated tables are not stored locally. These tables just provide a way to access the data on the remote server through a local table. So if you want to truly sync with a table on a remote server, then you need to actually copy the data across.

Option 1 - copy data using federated table and event scheduler

To do this, create the federated table with identical column definitions, and then e.g. enable the event scheduler and create an EVENT to sync from there to your local, non-identical table. The details of how you sync the two tables will depend on how the table is being modified on the remote server. (E.g. is it a table that is only ever INSERTed to, not UPDATEd? That will make your job easier. Or if it's UPDATEd, is there at least a timestamp column which will tell you when each row was last updated? In the worst case you'll have to delete all the local rows and re-populate the local table every time the EVENT runs. The EVENT code you write could involve a SELECT into a CURSOR and then a loop where you fetch from the cursor and INSERT/UPDATE to your local table.)

Option 2 - don't copy data, use federated table together with a view

Another option, if you don't mind that the data stays in the remote table, may be to create a local table with any extra columns you need that are not found in the remote table, and then create a VIEW that joins the two tables together. You can then name the view columns whatever you like. Then use this VIEW when you need to SELECT from the remote table. The disadvantage with this is that SELECTs against this view will be slower than using a purely local table, and also this isn't as robust because you depend on the remote database and the connection to it being available at all times.

Option 3 - use replicated table together with a view

A third option could be to use master-slave replication. Make your local database a slave and the remote a master. Then only replicate the particular table. Then create a VIEW similar to option 2.


Is this a good way to store XML data in SQL SERVER 2008?

...is this a good way to store these data?

No, this is probably the worst way to store this data if you want to query it effectively. You need to study some database normalisation examples before you go any further with this

This is a very quick sketch up of a possible schema that could be used. Incomplete and untested but it should be clear how this maps to your XML fragment.

CREATE TABLE dbo.Customer
(
    CustomerId INT NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId ASC)

CREATE TABLE dbo.Employee
(
    EmployeeNumber VARCHAR(10) NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Employee
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeNumber ASC)

CREATE TABLE dbo.Visit
(
    VisitId INT NOT NULL
    , VisitDate DATETIME
    , CustomerId INT
    , EmployeeNumber VARCHAR(10)
)

ALTER TABLE dbo.Visit
ADD CONSTRAINT PK_Visit PRIMARY KEY (VisitId ASC)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Customer] FOREIGN KEY(CustomerId)
REFERENCES dbo.Customer (CustomerId)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Employee] FOREIGN KEY(EmployeeNumber)
REFERENCES dbo.Employee (EmployeeNumber)


Insert a range of numbers from a call to sp_sequence_get_range

This should be good up to about 2,500 values (depending on version):

;WITH x(n) AS
(
  SELECT TOP (@variableNumberOfIdsNeeded)
    (ROW_NUMBER() OVER (ORDER BY number)-1)
    * CONVERT(BIGINT, @SeqIncr)
    + CONVERT(BIGINT, @FirstSeqNum)
  FROM master.dbo.spt_values
  ORDER BY number
)
--INSERT @newIds([NewId])
SELECT n FROM x;

If you need more, or are worried about using the view master.dbo.spt_values, see other possible techniques for generating sets without looping here:

Generate a set or sequence without loops – part 1
Generate a set or sequence without loops – part 2
Generate a set or sequence without loops – part 3


The two inner tables should have INDEX(PK_worker, reg_date, cost) and INDEX(PK_worker, dstart, cost).

Plan A:

The following may help, and it may depend on what version of MySQL you are running:

JOIN ( SELECT str_to_date('10/01/2017','%m/%d/%Y') AS start,
              str_to_date('11/30/2017','%m/%d/%Y') AS end ) AS d

and then use start and end instead of @start and @end.

Rationale:  @variables are sometimes not well optimized.

Plan B:  (But this requires 5.6, perhaps 5.7 - see the EXPLAIN for "auto-key".)

SELECT e.PK_worker,
   concat(e.name,' ',e.surname)   AS name,
    FROM employees AS e
    JOIN ( SELECT PK_worker,
                  coalesce(sum(cost),0) AS to_hand_cost
              FROM employees_to_hand
              WHERE ...
                AND reg_date BETWEEN start AND end
              GROUP BY 1 ) AS ethc
      ON e.PK_worker = ethc.PK_worker
    JOIN ( ... )  AS edc
      ON ...
    ORDER BY 1;

INDEX(reg_date, PK_worker, cost)

Rationale:  Calculating all the costs in one pass over the secondary tables is probably faster than one employee at a time.  Hopefully it is enough faster than the cost of automatically creating INDEX(PK_worker) after generating the 'derived' table.  The different index is a guess as to what would work better for this.

Thanks for dbfiddle, but in this situation, some of the optimizations depend on the size of the table, distribution of the values (esp reg_date, and the version of MySQL).