First, join TABLE_2 and TABLE_3 using an inner join and additionally filtering on Value_A = 'a':

SELECT
  t2.ID_1,
  t3.ID_A,
  t3.Value_A
FROM
  TABLE_2 t2
  INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
WHERE
  t3.Value_A = 'a'

This will give you the following result set:

ID_1  ID_A  Value_A
----  ----  -------
1     b     a
4     d     a

Now use the above as a derived table and join it, using an outer join this time, to TABLE_1, additionally filtering the results on Value_1 = 11:

SELECT
  t1 .ID_1,
  t23.ID_A,
  t1 .Value_1,
  t23.Value_A
FROM
  TABLE_1 t1
  LEFT JOIN
  (
    SELECT
      t2.ID_1,
      t3.ID_A,
      t3.Value_A
    FROM
      TABLE_2 t2
      INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
    WHERE
      t3.Value_A = 'a'
  ) t23 ON t1.ID_1 = t23.ID_1
WHERE
  t1.Value_1 = 11
;

That will give you the output you want:

ID_1  ID_A  Value_1  Value_A
----  ----  -------  -------
1     b     11       a
3     NULL  11       NULL

However, nesting a query is not the only way to solve your problem – you can also use a nested join, which is much more concise:

SELECT
  t1.ID_1,
  t3.ID_A,
  t1.Value_1,
  t3.Value_A
FROM
  TABLE_1 t1
  LEFT JOIN
    TABLE_2 t2
    INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  ON t1.ID_1 = t2.ID_1
WHERE
  t1.Value_1 = 11
;

The last query implements exactly the same logic as the previous query: first, tables TABLE_2 and TABLE_3 are joined and the result is filtered, then it is joined to TABLE_1 and the final set is filtered again. Some people also add brackets around a nested join:

FROM
  TABLE_1 t1
  LEFT JOIN
  (
    TABLE_2 t2
    INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  )
  ON t1.ID_1 = t2.ID_1

to make it clearer (perhaps both for themselves and for future maintainers) that the nested join takes place before the outer-level one, logically, although the syntax is unambiguous enough without them.

Nevertheless, many people find it confusing even with brackets, and if you find yourself struggling with it as well, there is another option – right outer join:

SELECT
  t1.ID_1,
  t3.ID_A,
  t1.Value_1,
  t3.Value_A
FROM
  TABLE_2 t2
  INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  RIGHT JOIN TABLE_1 t1 ON t1.ID_1 = t2.ID_1
WHERE
  t1.Value_1 = 11
;

Again, the logical sequence of events specified by the FROM clause repeats that of the derived table solution: a join between TABLE_2 and TABLE_3, filtered, is followed by a join with TABLE_1. The different syntax does not alter the outcome and the results produced still match your requirements.


Restoring MySQL database from small ibdata1

The problem was not that the file was too small, but that it was incomplete. The original file has 90MB, but the copying failed because of the SD card failures.


Index rebuild canceled because of .mdf growth

Your question is bit incomplete as you have not posted the query used to rebuild the index. A lot depends on what options you selected during index rebuild. Did you use the GUI with default options? The size of the mdf file would also be affected by the Fill Factor value chosen during index rebuild.

  1.Why did the .mdf grow that fast? About 1 gb/(10/sec)

Gerenal answer would be "YES", MDF file does grow during index rebuild because an index rebuild requires creating a new index first, and that would be created on the same drive where the data file resides, unless you have used SORT_IN_TEMPDB option, which would force intermediate sorts to be done in tempdb instead. Only after the new index is created will the old one be dropped so you can now see why space grew. Also, the space created by dropping the old index would not be released immediately. To release space you would need to run dbcc shrinkfile, although it is not advised.

If space is an issue on the drive where the data file resides you can use sort_in_tempdb option while rebuilding indexes. Please note that the intermediate sort results that are used to build the index are stored in tempdb. If tempdb is on a different set of disks to the user database, this may reduce the time needed to create an index. However, this increases the total amount of disk space that is used during the index build.

  2.When I stopped my query, can the database get inconsistent in any way?

No, the database won't be in an inconsistent state. Rebuild is considered a single transaction by SQL Server so either it completes fully or things are returned to what they were before the index rebuild.

  3.Can the data in the database have been changed because of this?

No, rest assured data cannot change.

I strongly suggest you to read the Blog Why data file increased after index rebuild
Please also read Misconceptions Around Index rebuild by Paul Randal

My other question would be did you try Ola Hallengren solution for index rebuild and stats updating? It's a good one and uses an intelligent script.


On a case insensitive server, those two names are identical. I am not sure if you are using the UI for this, but I get the same error when I run the following code:

USE msdb;
GO
EXEC dbo.sp_add_operator @name = N'NIT and Arts';
GO
EXEC dbo.sp_update_operator @name = N'NIT and Arts', @new_name = N'NIT and ARTS';

Yields:

  Msg 14261, Level 16, State 1, Procedure sp_verify_operator, Line 38
  The specified @name ('NIT and ARTS') already exists.

If we look at the code for sp_verify_operator, we see that there is a weakness here as a penalty for not using a surrogate key: it checks all existing rows for a match on name, including the row you are trying to change:

  -- The name must be unique
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysoperators
              WHERE (name = @name)))
  BEGIN
    RAISERROR(14261, 16, 1, '@name', @name)

Ideally, it should ignore the row you are trying to change, but it can't because there is no way to filter except by the name. And in a case sensitive collation, the above check fails because that row already has that name.

How do we work around this? Well you could rebuild msdb or something in a case sensitive or binary collation, but I think this is easier, just run this query manually:

UPDATE msdb.dbo.sysoperators
  SET name = N'NIT and ARTS'
  WHERE name = N'NIT and Arts';


Setup Multiple SQL Server Tree Structure similar as RDC based on Enviornments

You can do this with a feature of SQL Server Management Studio (SSMS) called Registered Servers.  Access the feature by going to "View" -> "Registered Servers."

You can then right-click on "Local Server Groups" and choose "New Server Group" to create the top-level containers ("TEST_ENV1", "TEST_ENV2").

You can right-click on those containers and choose "New Server Registrations" to add instances to that group ("SQL_SERVER_1", "SQL_SERVER_2").

There's lots of information about this feature on the Microsoft Docs site here: Register Servers

  Benefits of Registered Servers

  With Registered Servers you can:

  Register servers to preserve the connection information.
  ...
  Create groups of servers.
  Provide user-friendly names for registered servers by providing a value in the Registered server name box that is different from the Server name list.

You can also share these registrations, either by exporting them, or by creating a Central Management Server.


Count percentage of column with GROUP BY

SELECT country,
       COUNT(id) "count",
       COUNT(id) / SUM(COUNT(id)) OVER () * 100 percent
FROM lead
WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE)
GROUP BY country
ORDER BY "count" DESC;


enabling snapshot isolation level on a database - what to monitor?

There are doubtless many blogs that discuss this, but this post by Kendra Little (at BrentOzar.com) discussed the issues that concern you.  See:

http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

Kendra discusses some of the problems that arise and how you can test for potential problems.

After going through what problems SNAPSHOT isolation may cause, you need to also realize that READ COMMITTED isolation level is not as 'simple' as some might think.


What do the values after `execute fetch from` mean in postgresql query logs?

S_26 is a statement name and C_775 a portal name in the extended-query protocol.

See https://www.postgresql.org/docs/current/static/protocol-overview.html

  In the extended-query protocol, execution of SQL commands is divided
  into multiple steps. The state retained between steps is represented
  by two types of objects: prepared statements and portals. A prepared
  statement represents the result of parsing and semantic analysis of a
  textual query string. A prepared statement is not in itself ready to
  execute, because it might lack specific values for parameters. A
  portal represents a ready-to-execute or already-partially-executed
  statement, with any missing parameter values filled in. (For SELECT
  statements, a portal is equivalent to an open cursor, but we choose to
  use a different term since cursors don't handle non-SELECT
  statements.)

To find the query bound to this statement, look upper in the log for the corresponding bind entry.

If it doesn't exist, you might have to increase the logging level to DEBUG2 through log_min_error_statement, or set log_min_duration_statement to 0.


Add database to existing replication which uses binlog_do_db?

Making changes to binlog-do-db will require a restart I'm afraid.

Personally I would remove all the binlog-db-db from the Masters my.cnf, and just use replicate-do-db on the slave to filter what you want to be processed.

In this way you can addd more databases to the master in future without needing to restart.

If I understand correctly, you are trying to copy the new Database (database4) onto a slave that already has 3 databases replicating (these are all on the same MySQL Database).

The problem then is that replication will need to be paused whilst you do this.

The process I would use would be:

1) MASTER - restart the master with new binlog-do-db | remove all binlog-do-db in order to start populating the binary log with database4's data

2) SLAVE - Check the slave Database's replication is up to date (SHOW SLAVE STATUS > Seconds_Behind_master). If not wait till it is

3) MASTER - FLUSH TABLES WITH READ LOCK; To stop new data entering the master

4) SLAVE - Check replication is fully caught up and no data is replicating (read_master_log_pos and exec_master_log_pos should be the same and not changing (in show slave status;))

5) SLAVE - STOP SLAVE; to stop any data replicating into the database once you unlock the master shortly.

6) Run MySQLDUMP with --single-transaction option

7) Once MySQLDUMP has started running unlock the master Database UNLOCK TABLES; In this way your systems can continue reading and writing to the master, hopefully keeping downtime to a minimum

n.b. that MySQLDUMP may lock the database4 schema while it works depending on your system

8) Once MySQLDUMP has completed, import it into the slave Database

9) check the newly imported database4 looks correct

10) Restart the slave Database, adding replicate-do-db=database4 to the my.cnf file as you go

Once it comes back on, it should continue replicating from where it left off, but including database4.


I came up with my own solution (but if you think this can be done better, please post your option):

In the file /libraries/Menu.class.php I changed ...

        if (isset($cfgRelation['menuswork']) && $cfgRelation['menuswork']) {

... with

        $groupTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb']) . "." . PMA_Util::backquote($GLOBALS['cfg']['Server']['usergroups']);
        $userTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb']) . "." . PMA_Util::backquote($GLOBALS['cfg']['Server']['users']);

        if (!empty($GLOBALS['cfg']['Server']['users']) && !empty($GLOBALS['cfg']['Server']['usergroups'])) {
            $defaultUserGroup = 'User';
            $all_mysql_users = PMA_queryAsControlUser("SELECT User FROM mysql.user GROUP BY User", false);
            $all_phpma_users = PMA_queryAsControlUser("SELECT username FROM " . $userTable . " WHERE `usergroup` != ''", false);

            if ($all_mysql_users) {
                $default_user_group = PMA_queryAsControlUser("SELECT `usergroup` FROM " . $groupTable . " WHERE `usergroup` LIKE '" . $defaultUserGroup . "' LIMIT 1", false);

                if ($default_user_group) {
                    $default_user_group = $GLOBALS['dbi']->fetchAssoc($default_user_group);
                    $allPhpmaUsers = array();

                    while ($phpma_user = $GLOBALS['dbi']->fetchAssoc($all_phpma_users))
                        $allPhpmaUsers[] = $phpma_user['username'];

                    while ($mysql_user = $GLOBALS['dbi']->fetchAssoc($all_mysql_users)) {
                        if (!in_array($mysql_user['User'], $allPhpmaUsers))
                            PMA_setUserGroup($mysql_user['User'], $default_user_group['usergroup']);
                    }
                }
            }
        }

        if (isset($cfgRelation['menuswork']) && $cfgRelation['menuswork']) {

And then I replaced ...

        if (isset($cfgRelation['menuswork']) && $cfgRelation['menuswork']) {
            $groupTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb'])
                . "."
                . PMA_Util::backquote($GLOBALS['cfg']['Server']['usergroups']);
            $userTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb'])
                . "." . PMA_Util::backquote($GLOBALS['cfg']['Server']['users']);

            $sql_query = "SELECT `tab` FROM " . $groupTable

... with

        if (isset($cfgRelation['menuswork']) && $cfgRelation['menuswork']) {
            $sql_query = "SELECT `tab` FROM " . $groupTable

Then I created an user group "Admin" with all rights, put the root user into this Admin group. At last I created an user group "User" with less rights, refreshed the page and all other users had this group. It is important to do this in this order, because otherwise the root user will also get the "User" rights. When this group can't edit user settings, you can't change it back from within phpMyAdmin.


How to design database for tree view with an infinite depth

What we have always done is do it as a self join, with a parent_id which points to the parent.  This has the advantage of being simple, easy to work with, etc.  Of course it also requires recursion and WITH RECURSIVE CTE's.  It is easy to work with and as long as you are not working at huge depths and lots of records it performs well (note you can generate to a set depth and then fetch more records when a user expands if you want).


Revoke database-level privileges without the need for "use db_name;" statement

There is really nothing you can do to circumvent this. Why ???

Look at your third bulletpoint:

  Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

That bulletpoint is true for a user with SUPER privileges or root@localhost. Such a user cannot change a live connection's grants in full in the middle of an external or separate session. That has to be true even more so for a non-superuser.

By definition, you should be able to revoke a privilege you could grant.

But look at the grants again

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON mydb.* TO 'theuser';

Notice that this user cannot give away its grants. Why not ??? The WITH GRANT OPTION is missing. So, theuser cannot give away its grants to anyone else.

What you are seeking is the opposite: to revoke the grants. In this case, you are revoking from yourself. Unfortunately, there is no such thing as WITH REVOKE OPTION.

Switching to a database with USE mydb signals mysqld to trigger the reload of theuser's grants from all the grant tables (mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv). Doing this is far easier to implement that creating an option to revoke grants from a live connection (kinda like unsubscribing from a service that sends you email). Otherwise, grants would more frequently reload grants into RAM for mysqld or into a session's internal buffers. This would introduce some significant read traffic from these tables just for establishing grants.

In light of all this, there are only fours things you can do:

Do the USE mydb (Best Option)
Code Changes (Godspeed, Spiderman !!!)

Disconnect and open a new session
Change application to no longer do SELECT if the user is theuser
Use Views, then you can just drops views and trap read errors based on missing views


Lease Timeout VS HealthCheck Timeout - AlwaysON

The HealthCheck is between WSFC and SQL Server. The timeout is how long WSFC will wait to get data back from the sp_server_diagnostics execution.
LeaseTimeout is the SQL Server resource and SQL Server Availability Group. How Lease Timeout works

There are two timeouts because they are for 2 different mechanisms for health of the WSFC and SQL Server. Both are changed via the WSFC property of the AG resource, picture is shown in the linked article above.


Unable to start listener of standby database

Here are the steps I follow when creating a standby for a dataguard environment on windows server, please confirm in comments at which step you currently are to help identify your issue here.
(prd1 is the current alive instance, stby1 is the standby instance you're trying to create)

1- install the oracle binaries and patch them to the same level as the live instance without create a database on the stby1's host server

2- On the prd1 instance create a control file for the standby ALTER DATABASE CREATE STANDBY CONTROLFILE AS  'D:\CONTROL01.CTL';

3- copy the content of %ORACLE_HOME%\database (windows) to the same path from prd1 to stby1

4- use a CMD window to manually create the windows services for the standby database instance

set ORACLE_HOME=[path to oracle_home]

oradim -new -sid STBY1

set ORACLE_SID=STBY1

sqlplus / as sysdba

startup mount;

5- Make sure the listener.ora and tnsnames.ora files are configured correctly on both prd1 and stby1

%ORACLE_HOME%\network\admin\tnsnames.ora on both server contains something like

prd1_server1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prd1)
    )
  )
stby1_server2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby1)
    )
  )

6- %ORACLE_HOME%\network\admin\listener.ora on server1 (prd1) contains

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME =prd1_DGMGRL)
      (ORACLE_HOME = [path to oracle_home])
      (SID_NAME = prd1)
    )
  )

7- listener.ora on stby1 server2 contains something like

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME =stby1_DGMGRL)
      (ORACLE_HOME = [path to oracle_home])
      (SID_NAME = stby1)
    )
  )

8- Once you got all the above settings figured out you go on the stby1 instance and recover the database using SQLPLUS

RECOVER STANDBY DATABASE;


I think I have found the answer: this only works with streaming replication.

My original setup used WAL replication. That was working, except for the promotion. I even tried adding a trigger file, and that didn't help.

Then I changed my setup to use asynchronous streaming replication, and pg_ctl promote worked! When I looked in the log on the secondary server, I saw

received promote request
redo done at 86/8A000028
last completed transaction was at log time 2015-09-14 16:15:46.316812-04
selected new timeline ID: 2
archive recovery complete
autovacuum launcher started
database system is ready to accept connections

and when I ran "select pg_is_in_recovery()" on that server, it returned false.

Also, I see that recovery.conf has been renamed to recovery.done .


Missing files from mysql odbc driver installer

Old, but for those searching, as me recently ;) Part of documentation section 4.1:

  Make sure the Visual C++ Redistributable for Visual Studio is installed.
  • Connector/ODBC 8.0.14 or higher: VC++ Runtime 2015 or VC++ Runtime 2017
  • Connector/ODBC 8.0.11 to 8.0.13: VC++ Runtime 2015
  • Connector/ODBC 5.3: VC++ Runtime 2013
  Use the version of the package that matches the system type of your Connector/ODBC driver: use the
  64-bit version (marked by “x64” in the package's title and filename) if you are running a 64-bit driver, and
  use the 32-bit version (marked by “x86” in the package's title and filename) if you are running a 32-bit
  driver.


Is it possible to schema-qualify an operator?

Be aware that the OPERATOR construct (like OPERATOR(postgis.&&)) is not 100 % identical to the operator itself. Operators lose their implicit precedence and fall back to default operator precedence, which can have sneaky side effects. Consider:

SELECT 3 + 3 * 2                        -- 9
     , 3 + 3 OPERATOR(pg_catalog.*) 2;  -- 12

There is no effect for OPERATOR(postgis.&&) as && has default operator precedence to begin with.

Related:

Use PostgreSQL builtin operator <@ after including extension intarray
GIN index on smallint[] column not used or error “operator is not unique”

Another limitation: the OPERATOR construct does not work for SQL syntax elements like AND, OR, BETWEEN etc., which are also sometimes called "operators" (and have their own operator precedence). Only for operators listed in the system catalog pg_operator.


Is there any better way in oracle to enable parallel other than this?

You should alter your tables and indexes and specify the amount of parallel that you want.

How Parallel Execution Works

ALTER TABLES sales PARALLEL 8;
ALTER TABLE customers PARALLEL 4;


MongoDB Fragmentation affect the Performance in WiredTiger

It will be less efficient to fetch data if you have a lot of space available for reuse. However, that space could be reused if you continue to insert or update documents in the collection.

If you've dropped a significant amount of data (which sounds like ~300GB in one of your collections) you can reclaim space by either:

re-syncing members of a replica set (recommended if you want to reclaim space from all collections)
running the compact command (important caveat: this will block all operations for the database the operation is running on)

For either approach I would use rolling maintenance to maintain availability.

A resync would be the most effective option. The compact command is riskier to run as a replica set member may become stale while running compact offline (and require resync); this operation may also not be able to free as much disk space as a resync and needs to be run per collection.


What does "InnoDB stores its tables and indexes in a tablespace" mean?

A tablespace is essentially a black box that InnoDB manages allby itself

  tablespace which is essentially a virtual filesystem spanning one or more files on disk.InnoDB uses tablespace for many purpose It keeps its undo log,insert buffer,doublewrite buffer,and other internal structure in the table space

  By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.

  Advantages of Per-Table Tablespaces

  You can reclaim disk space when truncating or dropping a table. For tables created when file-per-table mode is turned off, truncating or dropping them creates free space internally in the ibdata files. That free space can only be used for new InnoDB data.

InnoDB File-Per-Table Mode

The InnoDB tablespace


What happens when I change a table column's data type to increase its data length in SQL Server?

Changing a column data type to a wider type will require rows to be moved to other pages once a page is full. This behavior is similar to DML updates of variable length columns to a wider value. This can be observed with this script that uses the undocumented sys.dm_db_database_page_allocations TVF available in SQL Server 2012 and later:

CREATE TABLE ExampleTbl
(
  ID INT
 ,Bool BIT
);
WITH
    t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1000 AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
INSERT INTO ExampleTbl WITH(TABLOCKX)
SELECT num, 1
FROM t1000
WHERE num <= 578;

--single page
DECLARE
      @database_id int = DB_ID()
    , @object_id int = OBJECT_ID(N'dbo.ExampleTbl')
    , @allocated_page_file_id int
    , @allocated_page_page_id int;
SELECT *
FROM sys.dm_db_database_page_allocations(@database_id, @object_id, 0, 1, 'DETAILED')
WHERE
    page_type_desc = 'DATA_PAGE';
GO

ALTER TABLE dbo.ExampleTbl
    ALTER COLUMN Bool INT;
GO

--3 pages, with 2 100% full and last 50% full
DECLARE
      @database_id int = DB_ID()
    , @object_id int = OBJECT_ID(N'dbo.ExampleTbl')
    , @allocated_page_file_id int
    , @allocated_page_page_id int;
SELECT *
FROM sys.dm_db_database_page_allocations(@database_id, @object_id, 0, 1, 'DETAILED')
WHERE
    page_type_desc = 'DATA_PAGE';
GO

Heaps are particularly nasty when DML/DDL increases row length because the original row is retained with a forwarding pointer. Consequently, more space will be required that the equivalent change against a table with clustered index.


bulk copy out huge table on sybase adaptive server

We have done very large table migrations successfully with Sqoop. Yes, there will be a bunch of setup that is needed but it is tuneable to run in multiple threads for faster performance.

From Comments:

Over the years, I have used many methods to extract data from sybase tables. BCP, IBM Optim (very proprietary), Apache MetaModel and Apache Sqoop. Using Sqoop I found is the most effective for very large tables. The setup for Sqoop would need some basic Hadoop libraries to run. Then you would install the sqoop server and the client software. All of this is not very difficult to do. Sqoop does not offically support Sybase but it does work really well. Here is a good starting point for installing Sqoop on a single node.


Automatic index creation for primary vs. foreign keys in Postgresql

I don't think that - a default index generation for foreign key columns  - would lead to serious problems.

It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not.

If they had taken the opposite decision, then we'd be restricted. There would be an index for each and every foreign key constraint. If someone wanted to have some foreign key columns not indexed, they'd be limited or forced.

Now there is only the additional burden of having to add indexes to match the foreign key constraints - for those who want to.

Personally, I almost always add respective indexes for a foreign key constraint. But often, the index does not match exactly the constraint. For example the Fk may be TABLE c (..., FOREIGN KEY (a_id) REFERENCES a (a_id) ) and the index might be single (a_id) or composite (a_id, b_id) because it helps other queries as well, not only those that use a_id.

Or I might have (more rare cases of) 2 foreign key constraints that can be covered but 1 index - eg.: FK (a_id) REFERENCES a (a_id), ... FK (a_id, b_id) REFERENCES b (a_id, b_id).

When creating an index, there are also many options, like the type of index (btree, hash, Gin, Gist and other exotic types), so we're free to choose what is best for our situation.

Also note that Oracle, SQL Server, DB2 and Firebird also do not automatically create indexes for FOREIGN KEY columns, so it's not like that PostgreSQL developers were alone in this. And I'm sure there were many discussions on the matter and they considered both options, I'm only speculating on whey they came to the decision. Looking through the old Postgres discussion lists may reveal more about the actual reasons they considered.


User-shared queries: Dynamic SQL vs. SQLCMD

Just to get these out of the way:

Technically speaking, both of these options are "dynamic" / ad hoc queries that are not parsed / validated until they are submitted. And both are susceptible to SQL Injection since they are not parameterized (though with the SQLCMD scripts, if you are passing in a variable from a CMD script then you do have an opportunity to replace ' with '', which may or may not work depending on where the variables are being used).
There are pros and cons to each approach:

SQL scripts in SSMS can be easily edited (which is great if that is a requirement) and working with results is easier than with output from SQLCMD. On the down-side, the user is in an IDE so it is easy to mess up the SQL, and the IDE makes it easy to make a great variety of change without knowing the SQL to do it.
Running scripts via SQLCMD.EXE does not allow the user to easily make changes (without editing the script in an editor and then saving it first). This is great if the users aren't supposed to be changing the scripts. This method also allows for logging each execution of it. On the down-side, if there is a need to routinely edit the scripts then it would be quite cumbersome. Or, if users need to scan through 100k rows of a result set and/or copy those results to Excel or something, then that is also difficult in this approach.

If your support folks aren't doing ad hoc queries and are just filling out those variables, then they don't need to be in SSMS where they can edit those scripts and make unwanted changes.

I would create CMD scripts to prompt the user for the desired variable values and then call SQLCMD.EXE with those values. The CMD script could even log the execution to a file, complete with time stamp and variable values submitted.

Create one CMD script per SQL script and place in a networked shared folder. A user double-clicks on the CMD script and it just works.

Here is an example that:

prompts the user for the server name (no error checking on that yet)
prompts the user for the database name

if left blank, it will list the databases on the specified server and prompted again
if the database name is invalid, the user will be prompted again

prompts the user for the OrderIDsSeparatedByCommas

if blank, prompts the user again

runs the SQL script, passing in the value of %OrderIDsSeparatedByCommas% as the SQLCMD variable $(OrderIDsSeparatedByCommas)
logs the execution date, time, ServerName, DatabaseName, and OrderIDsSeparatedByCommas to a log file named for the Windows Login running the script (this way, if the log directory is network and there are multiple people using this, there won't be any write contention on the log file like there might be if the USERNAME were to be logged in the file per entry)

if the log file directory does not exists, it will be created

Test SQL script (named: FixProblemX.sql):

SELECT  *
FROM    sys.objects
WHERE   [schema_id] IN ($(OrderIdsSeparatedByCommas));

CMD script (named: FixProblemX.cmd):

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION

SET ScriptLogPath=\\server\share\RunSqlCmdScripts\LogFiles

CLS

SET /P ScriptServerName=Please enter in a Server Name (leave blank to exit):

IF "%ScriptServerName%" == "" GOTO :ThisIsTheEnd

REM echo %ScriptServerName%

:RequestDatabaseName
ECHO.
SET /P ScriptDatabaseName=Please enter in a Database Name (leave blank to list DBs on %ScriptServerName%):

IF "%ScriptDatabaseName%" == "" GOTO :GetDatabaseNames

SQLCMD -b -E -W -h-1 -r0 -S %ScriptServerName% -Q "SET NOCOUNT ON; IF (NOT EXISTS(SELECT [name] FROM sys.databases WHERE [name] = N'%ScriptDatabaseName%')) RAISERROR('Invalid DB name!', 16, 1);" 2> nul

IF !ERRORLEVEL! GTR 0 (
    ECHO.
    ECHO That Database Name is invalid. Please try again.

    SET ScriptDatabaseName=
    GOTO :RequestDatabaseName
)

:RequestOrderIDs
ECHO.
SET /P OrderIdsSeparatedByCommas=Please enter in the OrderIDs (separate multiple IDs with commas):

IF "%OrderIdsSeparatedByCommas%" == "" (

    ECHO.
    ECHO Don't play me like that. You gots ta enter in at least ONE lousy OrderID, right??
    GOTO :RequestOrderIDs
)

REM Finally run SQLCMD!!
SQLCMD -E -W -S %ScriptServerName% -d %ScriptDatabaseName% -i FixProblemX.sql -v OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%

REM Log this execution
SET ScriptLogFile=%ScriptLogPath%\%~n0_%USERNAME%.log
REM echo %ScriptLogFile%

IF NOT EXIST %ScriptLogPath% MKDIR %ScriptLogPath%

ECHO %DATE% %TIME% ServerName=%ScriptServerName%    DatabaseName=[%ScriptDatabaseName%] OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%   >> %ScriptLogFile%

GOTO :ThisIsTheEnd

:GetDatabaseNames
ECHO.
SQLCMD -E -W -h-1 -S %ScriptServerName% -Q "SET NOCOUNT ON; SELECT [name] FROM sys.databases ORDER BY [name];"
ECHO.
GOTO :RequestDatabaseName

:ThisIsTheEnd
PAUSE

Be sure to edit the ScriptLogPath variable towards the top of the script.

Also, the SQL scripts (specified by the -i command-line switch for SQLCMD.EXE) might benefit from having a fully-qualified path, but not entirely sure.


It is highly recommended to create an index on Employee.dept_id.

It should make a significant improvement for such SELECT statements. Even more important the index becomes on updates. You may lock entire table (forcing other sessions to wait) until you finish your transaction.

See more information here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754


Can't cast a stored procedure parameter?

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind.

You can say:

EXEC dbo.myprocedure @foo = 'bar';

But you can't say:

EXEC dbo.myprocedure @foo = 'b' + 'ar';

You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a date parameter, parameterized safely (you may want to read up on "SQL injection"), and not have to worry about ugly string concatenation. Here is an example:

CREATE PROCEDURE dbo.foo
  @dt DATE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT DATEPART(DAY, @dt);';
  EXEC sys.sp_executesql @sql, N'@dt DATE', @dt;
END
GO

DECLARE @fromDay DATETIME = '20010914 03:45';
DECLARE @dt DATE = @fromDay; -- or just declare @fromDay as DATE in the first place;
EXEC dbo.foo;

Results:

----
14


How can I specify which MariaDB instance to launch in a Galera Cluster

According to the output from mysqld_multi --help:

--wsrep-new-cluster  Bootstrap a cluster.

So on the node you want to bootstrap, I'm assuming you should be able to do:

mysqld_multi --wsrep-new-cluster start 4

And then start the instances on the other two the normal way, assuming all the instances have the necessary config files.


Is there a policy or special server configuration to limit SQL Server query results?

There is no native way to accomplish directly what you are looking for.  Why do developers have access to systems they can inherently affect the performance on where it matters?  Are these production systems?  If so, do these developers need access to prod?  If not (non-prod), then why do you care what developers do to development database servers?  Maybe I'm missing something.  Developers and non-operational professionals typically don't have access to environments where performance impact is considered.

Outside of the business process, a solution that you could use if you are unable to change access is Resource Governor to isolate resource usage.

Another thing you could do is create views where you embed your "top" logic, and give the developers access to the views but not the underlying tables.  That way they could do select * from viewname all day, but the underlying definition is whatever you want.


mysql database, 2400 records for 1500 rows

Rolling 100 days -- I recommend using PARTITIONing and break it into weekly partitions.  It will be about 17 partitions.  Details:  http://mysql.rjweb.org/doc.php/partitionmaint .  DROP PARTITION is much faster than DELETE.

It is possible to map between "hour" and "datetime" (or "timestamp") with some arithmetic involving FLOOR( ... / 3600).  Then that will fit nicely in MEDIUMINT UNSIGNED and replace 3 of Vérace's columns.

Don't have an AUTO_INCREMENT if you have a 'natural' PK.  Suggest

PRIMARY KEY(sensor_id, hour)

and

PARTITION BY (hour)

You may, but probably won't, need

INDEX(hour, ...)


Update MySQL Stored Procedure Definers on shared environment

SUGGESTION #1

If you login as root@localhost, you can change the definer in mysql.proc

In your case, you would do

UPDATE mysql.proc SET DEFINER = 'dbname@localhost';
SELECT name,type,definer FROM information_schema.routines;

I am not sure if the stored procedures load automatically into INFORMATION_SCHEMA.ROUTINES

If not, run these

FLUSH PRIVILEGES;
FLUSH TABLES;
SELECT name,type,definer FROM information_schema.routines;

SUGGESTION #2

Based on my old post Modify DEFINER on Many Views, you can do the following

You may have to mysqldump the stored procedures into a test file
Edit the definer user to 'dbname@localhost' in the text file
Then, reload the stored procedures

GIVE IT A TRY !!!


The first message regarding the shutdown means that someone or something has initiated a shutdown of the machine. Take a look at the Windows system logs in Event Viewer for more information. You should look at log events leading up to and just after the shutdown time to look for messages indicated why Windows shutdown.

Is this a relatively newly installed server? If so, have you activated Windows? If not and you've passed the activation grace period this is one potential reason for Windows Server automatically rebooting.


Return a value only when conditions satisfy for entire column

You could use window aggregation (an aggregate function with an OVER clause) to apply the condition to a column of values whilst calculating the results for detail rows. Here is how you could use window aggregation in your situation:

SELECT
  order_number,
  line_item,
  flag,
  CASE COUNT(CASE flag WHEN 'true' THEN 1 END)
       OVER (PARTITION BY order_number)
  WHEN COUNT(*)
       OVER (PARTITION BY order_number)
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;

The COUNT(CASE flag WHEN 'true' THEN 1 END) OVER (PARTITION BY order_number) expression calculates the number of true values per order_number and returns the same value for rows that have the same order_number. As a result, you can generate values identical within groups of rows as well.

A variation on the above (using reverse logic and one count instead of two):

SELECT
  order_number,
  line_item,
  flag,
  CASE WHEN
       COUNT(CASE flag WHEN 'true' THEN NULL ELSE 1 END)
       OVER (PARTITION BY order_number)
       = 0
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;


notes / description string in another table in a relational database

A coworker was suggesting that it is correct for a table to store 'description' and 'notes' as a foreign key rather than in the table for performance reasons, given that some of the records will contain NULL (or empty string in this case), and presumably will be split across pages.

Possibly, depending on RDBMS, the exact data types, and what the performance goal is.

For SQL Server, which it sounds like you're using, you can use sp_tableoption with large value types out of row to keep a LOB (nvarchar(MAX) in this case) column in the table you want (logically), while pushing the bulk of the data storage onto separate pages (physically).

This will make accessing the LOB data slower, but speed up access to only the base data (probably moreso for scanning) as the tradeoff. Depending on the average LOB size, this setting may not make a significant difference. You will have to test this for your exact scenario to see if it improves your workload.

In any event, as long as you want to keep the one-to-one relationship, there's no reason to logically separate the values from the base table because there is an available way around the problem in SQL Server, assuming you're using the MAX type variant. (Note: if you're currently using a fixed-length field, you could switch to the MAX type and add a check constraint to limit the length.)

  This conversation occurred after we noticed that the integrity of the one-to-one table design was broken with a duplicate record.

It seems like the design of these structures is smelly. Are you keeping a single table to store all of these fields, which are then referenced from the parent tables?

A better design would be to only separate the tables where there's an actual need (again, as I mentioned, this may not even be required) with a declarative one-to-one relationship, such as repeating the primary key of the parent table as the primary key in the LOB storage table.

  It seems to me that if the developer who designed the column put it in the source table, all of the code that handles the one-to-one integrity would be unnecessary (and would have not caused this bug)

Quite possibly. The fewer moving parts there are in a system, the more likely it is to be reliable, probably with fewer bugs as a result of complexity.


What is the order when SELECT-ing unordered set from an ordered SELECT?

Can I trust that the inner order is respected by the outer projection as well ...?

No, you cannot.

  ... or do I need to state the ORDER BY in the outer statement explicitly, to be sure?

Yes, you do need to state the ORDER BY you need in the outer statement explicitly, if you want to be sure that the order that the rows are returned is what you want.

While you may - more often in old versions of some DBMS - sometimes find that the order of the rows returned is the one specified in the internal ORDER BY, there is no guarantee about it. If you want a specific order, you need to be explicit.

Related / similar questions:

Why is SSMS inserting new rows at the top of a table not the bottom?
What is the default order of records for a SELECT statement in MySQL?
In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

Quotes from the SQL standard:

  Cursors
  ...
  A cursor in the open state identifies a result set and a position relative to the ordering of that result set. If the cursor's <cursor specification> does not simply contain an <order by clause>, or simply contains an <order by clause> that does not specify the order of the rows completely, then the rows of the result set have an order that is defined only to the extent that the <order by clause> specifies an order and is otherwise implementation-dependent.

  <query expression>
  ...
  5) The rows of T are ordered as follows:

  a) If QE does not immediately contain an <order by clause>, then the ordering of rows in T is implementation-dependent.


Why do I need to reset user mapping always after restoring my DB

I found the fix. I need to set my user as sysadmin then it does not get cleared user mappings after a db is restored.


It might help to define terms up-front.

The term latency is used in (at least) two ways regarding I/O. It can mean:

The average time it takes to service an I/O at any given time. That's how Paul Randal is using it in "How to examine IO subsystem latencies from within SQL Server" and it can be measured using (for example) Performance Monitor with counters such as "Avg. Disk sec/Read". I'm going to call this the 'measured' latency, and it is related (inversely) to the queue length at a given time, but is a much more helpful measure because it is easy to relate to actual performance from a user perspective.
The average time a single I/O takes in isolation. That's how it is typically used on spec sheets for HDDs and SSDs like this one from Seagate. I'll call this the 'inherent' latency (it's also sometimes called 'access time').

Throughput can also mean different things in different contexts:

The maximum rate of sequential data transfer, typically measured in MB/sec
The maximum rate of I/O operations typically measured in IOPS
The current rate of data transfer such as that measured using Performance Monitor with counters such as "Disk Bytes/sec"

  Seems like if I have faster disk I should see an increase in throughput.

Because of the way you phrase this, I think you are specifically talking about the quoted MB/sec and 'inherent' latency for disk drives. If that's the case then the answer is "no, there is no direct correlation between disk latency and throughput". Latency for a HDD is affected by the seek time and the spin speed, but it only applies to the first read: subsequent sequential reads do not have the same latency. The mechanics are different for SSDs, but they can still be tuned by the manufacturer for high 'throughput' or high IOPS with issues like block size being important.


SQLite database replication on the same server

Searching for the same topic, I came across a mention in Using SQLite: Small. Fast. Reliable. Choose Any Three.:

  Replication

  SQLite has no internal support for database replication or
  redundancy. Simple replication can be achieved by copying the database
  file, but this must be done when nothing is attempting to modify the
  database. Replication systems can be built on top of the basic
  database API, but such systems tend to be somewhat fragile. Overall,
  if you're looking for real-time replication — especially at a
  transaction-safe level — you'll need to look at a more complex RDBMS
  platform


Oracle allows inserting into table even after revoking quota

When you inserted the first row in the empty table, Oracle allocated an extent, which typically contains 8 blocks with default settings for the first extent of a table. 8 blocks (typical size of single block is 8 KB) can store a lot more than 1 row, so even though you revoked the quota, that space is already allocated, and you can write into it, which is expected behavior.
Try inserting a lot more rows and eventually you will get the same error message about the quota.


What happens if I can't rebuild indexes after setting PAGE_VERIFY to CHECKSUM?

"Also, that if I rebuild a clustered index the non-clustered indexes will be rebuilt as well." -- if you rebuild a clustered index, the non-clustered indexes won't be rebuild, please see Paul's blog post -- http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed/ -- even though it says 2005, it's true for any version, as far as I know.
1. Nothing will happen
2. Will be usable

Also can read in Paul's blog, Myth a Day -- http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1730-page-checksums/

As you and Jon, added -- there are 2 ways (from Paul's blog):
1. There is no process, background or otherwise, that can put a page checksum on each page. This is a major bummer (technical term :-) as it means you must perform index rebuilds or other size-of-data operations to actually put a page checksum on the pages.
2. A page checksum is only put on a page when it is read into memory, changed, and then written back out to disk.


PostgreSQL find groups with same members

This is called relational division and your query is pretty much what you can do with standard SQL.

A different way to write this in Postgres would be to use arrays:

select group_id
from membership
group by group_id
having array_agg(user_id order by user_id) = array[1,3];

It's important to also order the IDs inside the array[1,3] constant because array[1,3] is a different array than array[3,1];

The above returns those groups that consist exactly of those two members. Your query returns those that consists at least of those two members. That can be written with an array as well:

select group_id
from membership
group by group_id
having array_agg(user_id order by user_id) @> array[1,3];

That uses the "contains" operator for arrays which tests if the array on the left side contains all elements of the array on the right side. But the array on the left side is allowed to have more elements.

The check for an exact match is probably faster using the array (because it would require an additional sub-query with standard SQL), but I don't think that the "at least" query makes a big difference in performance.


Can you please check the error logs, It should tell you the percentage the recovery process is on.

This would give an idea of how long the recovery would take to complete.


Permissions Needed To Attach AdventureWorks Database

You have not given your admin user the correct permissions yet. You must explicitally tell SQL Server that this user is trusted. Running the program as an admin gives the program itself the highest level of permissions, but does nothing for the authentication process when executng a query.

To add your administrator account as a SQL Administrator:

Open 'SQL Server Surface Area Configuration'  (Under Start -> MS SQL Server -> Configuration Tools)
Click "Add New Administrator"
Move the user from the left to the right, then save changes.

In 2008 and later, follow the instructions given here.


EXECUTE denied, but only when I'm connected locally

Your login is Windows login, so it can get its permissions from different Windows groups.

You loose some of them when logging locally, this usually happen when you launch SSMS not as administrator: you are still a member of BUILTIN\Administrators but UAC strips out your rights.

You should execute the following code in both cases: when you are logging remotely and locally, and check if in usage you see DENY ONLY when logging locally:

select distinct name, type, usage
from sys.login_token
where principal_id > 0

What to do next depends on what you see as the results.
If you see BUILTIN\Administrators DENY ONLY just run your SSMS as administrator.
If there is some other difference in the results, please post it here.


Limiting read access in postgres to few rows

You can define a function that returns required limit for some users. The function below set limit for users joe and ben.

Note that limit null is equivalent to limit all, so all other users will have no limits.

create or replace function set_limit()
returns integer language sql as $$
    select case current_user
        when 'joe' then 100
        when 'ben' then 200
        else null
    end
$$;

select * from some_table limit set_limit();

Edit:

  So basically we would like to append a limit clause to any select query fired by a particular user.

Once function set_limit() is created you can append limit set_limit() to any select query.


SQL : get rows from two tables relatively

Here is the query to order it the way the question has it

SELECT c.cat_name,p.prod_name
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY prod_name;

Here is that query formatted

SELECT CONCAT('Category : ',c.cat_name,', Product ; ',p.prod_name) DisplayLine
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY prod_name;

If you want to it order by category, then

SELECT c.cat_name,p.prod_name
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY cat_name,prod_name;

Give it a Try !!!


How to know or recover my pg_dump password?

pg_dump does not have a password specific to itself.  You run pg_dump as a certain database user (often the user spelled 'postgres', but not always), and authenticate however that user would authenticate for any other (non-pg_dump) connection.  So your question really has nothing to do with pg_dump.

You probably can't recovery the password (unless you are willing to crack md5 hashes to brute force it--which is really only feasible with poor passwords or with big bucks), but you can use your "admin account" to reset the password to something you do know.  How you do that depends on whether "admin account" means a database superuser account, or an OS account for the OS user who owns and runs the database files.  Also, it might depend on your settings in pg_hba.conf.


Invalid object name 'sys.dm_os_volume_stats'

You can't use this DMV in RTM; it was introduced in SP1 (though the current version of the documentation does not specify that, the 2008 R2 version does). You should be on Service Pack 3 anyway (that will make those old servers easier to support, not harder).

In RTM you can do some really convoluted things, like this. He is using xp_cmdshell to call PowerShell to grab output from a WMI class (Win32_Volume), then dump those results into a #temp table, and compare them to data from an undocumented and unsupported extended procedure, xp_fixeddrives.

There is a similar solution here. It uses WMI (Win32_PhysicalDisk), VBScript, and PowerShell from a SQL Server Agent job - while they avoid xp_cmdshell here, the overall methodology feels similarly hobbled together.

If you were working in construction, these would be the equivalent of about eight rolls of duct tape and a half gallon of WD-40.

Maybe you should consider performing this task using PowerShell or similar (doesn't this seem a lot simpler?), instead of trying to bend T-SQL to do this. SQL Server isn't really the right tool to manage server disk space.


Merge Replication - Deleting from the subscriber database?

If the same logical row has two different rowguid values at publisher and subscriber, then you're having a conflict.

Changes are replicated using the rowguid column, so different rowguid means different rows as far as replication is concerned. You can safely delete the duplicate row at the subscriber and the changes won't be replicated to the publisher (the row with a matching rowguid is not present at the publisher due to the upload conflict).

Once the row is deleted at the subscriber, the merge agent will be able to insert at the subscriber the row from the publisher.


PostgreSQL: Query DB for rows containing integer[] > or < than specified value

Assuming your table is named readings:

Find cities where at least one temperature is bigger than 80:

select *
from readings r
where exists (select 1
              from unnest(r.temps) i
              where i > 80)

Find cities where all temperatures are bigger than 68:

select *
from readings r
where 68 < all (select i
                from unnest(r.temps) i)


It depends.

The options are -

Sort in the destination file group (normally PRIMARY on the DB the index is on)
Sort in TempDB

If your TempDB is on a different disk array and not active, you could see a performance increase.  There are also space considerations.

There's a pretty good article on MSDN about this topic here.


Is this a valid setup ?

Yes.  This configuration is detailed here:

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

If you want automated failover between data centers, look at using a Cloud Witness for cluster quorum.


SCD Type 2 Dimension -> Is this the correct layout for a type 2 scd with this type of data present?

Making SSAS hierarchies on slowly changing dimensions is a bit of a fiddle.  You need to make surrogate keys for each historical version at each level of the hierarchy.  Then the key has the actual business facing name, which the user selects or reports by.

As an example, imagine worker BloggsJ in Division1, which is in LineOfBusiness1.  Now Division1 gets moved to LineOfBusiness2.  Logically you have the Division entity with two rows now:

DivisionKey     Division     LineOfBusinessKey
          1     Division1                   11
          2     Division2                   12
          3     Division1                   12

and

LineOfBusinessKey    LineOfBusiness
               11    LineOfBusiness1
               12    LineOfBusiness2

Now, we have worker BloggsJ, who is assigned to Division 1, which is subsequently moved

WorkerKey  WorkerName   DivisionKey   Division   LineOfBusinessKey   LineOfBusiness
      101     BloggsJ             1   Division1                 11  LineOfBusiness1
      102     BloggsJ             3   Division1                 12  LineOfBusiness2
      103     SmithF              2   Division2                 12  LineOfBusiness2

In this case the keys remain in a strictly hierachical order:

LineOfBusiness2 (Key=12) has two children: Division2 (Key=2) and Division1 (Key=3).  Division1 (Key=3) has one child: BloggsJ (Key=102) and Division2 (Key=2) has one child: SmithF (Key=103).
LineOfBusiness1 (Key=11) has one child: Division1 (Key=1), which has one Child: BloggsJ (Key=101)

Displaying the name in the cube allows you to build a hierarchy that can support drill-down operations.  You will also probably want to hide the base attributes for this hierarchy and display another set with just the names, so there is something in the dimension that will produce a clean, unique list of members at each level without opaque, confusing repeated names.

*****Update 05012012 3:22 PM CST

Here is an image of my data example.


Convert UNION query to JOIN. Is it actually needed for high performance?

You use LIMIT without ORDER BY - that may return different 30 rows each time it is executed.

Other than that if you combine the two unioned queries into one join, you will have OR in WHERE which will check columns from different tables - that is probably going to be much slower than what you have now, union is good for this. You might want to use UNION ALL instead if you know that there will be no duplicates or that they are no problem to you as it does not have to check and eliminate them and can be even faster that UNION.


The CONCAT function requires passing column names explicitly and there is no such thing as CONCAT(*).

On the other hand, PIVOT requires an explicit definition of the columns you are pivoting: if you are doing it dynamically, you probably have a variable that holds the names of the columns you are pivoting on and you could use the same variable in dynamic SQL with the CONCAT function.


Can't Dump Database Because MS SQL Server Management Studio Won't Show Any Databases If 1 Is Permission Denied

One thing you can do, if you need both the data and schema, is script out a backup of the database instead of trying to use the UI in SSMS. You can find how to do that in the MS Docs: Create a Full Database Backup - Using Transact-SQL.
This is the T-SQL example from the above docs using your database name, for reference:
USE DB_D002;
GO
BACKUP DATABASE DB_D002
TO DISK = 'c:\backup folder\DB_D002.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of DB_D002';
GO

Note if you run into the "Operating system error 5(Access is denied.)" error, then likely the file path you provided to the "TO DISK" option in the query does not provide write access to the SQL Server Service account (in Windows), and you can provide a different path that does or follow this StackOverflow on how to fix it for the specified path.


What's the overhead of rarely used columns

Rarely used columns may be a sign of bad normalization, something that, if they are never used could (and this is highly dependent on your structure, engine, table size, data types and access pattern) help improve disk space and consequently performance. If the tables are tiny to small, there may be hardly any difference, although it may result in cleaner/more elegant design.

However in modern versions of MySQL's default Engine, InnoDB, NULLs may take as little overhead as a single bit, as they can be stored as field header flags.

If you really want to know exactly the difference, I would encourage you to test the difference of performance/disk space by separating optional columns on a separate table and joining on query time.

See other related answers: https://stackoverflow.com/a/230923/342196


How to determine the collation of a table in PostgreSQL?

To check for non-default collations on columns, you can use the following query:

select table_schema,
       table_name,
       column_name,
       collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
         table_name,
         ordinal_position;

To find the collation of the database, you need to query pg_database:

select datname,
       datcollate
from pg_database;

Here are the relevant pages of the PostgreSQL manual:

http://www.postgresql.org/docs/current/static/infoschema-columns.html
http://www.postgresql.org/docs/current/static/infoschema-collations.html
http://www.postgresql.org/docs/current/static/catalog-pg-database.html


Fetch one value from random row where value is not x

At a glance, I'm not convinced that your original query is too far from wrong.

Unless, I'm greatly mistaken, the only time it should be unable to return a results is when the target slug happens to have the MAX(id) as its id, and when that row is the only valid row to consider. If the only id greater than or equal to the randomized id value you've calculated is the one eliminated by the other WHERE condition, there are no rows to return.

If that's the case, then something like this may work:

SELECT slug, id, rand.pk FROM games
JOIN
  (SELECT ROUND(MIN(id) + RAND() * (MAX(id) - MIN(id))) AS pk
   FROM games WHERE slug <> '20piaedfl6ah') AS rand
WHERE slug <> '20piaedfl6ah'
  AND id >= rand.pk
LIMIT 1;

Here's a fork of @Vérace's DBFiddle with your original query and my modification of it. I made sure that the row to avoid has the highest ID

Here, we've ensured that neither end of our range matches our row to avoid. As long as that's true, then we should be OK with any value in our range. If the rand.pk matches the slug to avoid (or matches no rows at all - I assume there may be gaps in the ID sequence), then we know there's at least one row available that's OK - the one that matches our MAX(id)value (which cannot be either a gap row, or the row to avoid).

In numerous runs of the DBfiddle, I never saw the second query (which is the one protected against not finding a match) take longer than the first - however, I must note that the time to set-up the schema is included there.

And, of course, if there's only one row available, and its slug matches, you would still find no rows - however, I'd hold that in that case, finding no rows is the correct behavior.


Does N which is default means to log to output file?

No, it just wouldn't output to the CommandLog table. It would still output to file.

  I also see the recommendation is to log to output file, then what is
  the CommandLog table for?

The CommandLog table makes it easy to read the output log instead of having to read a flat file from your drive. Many people will add follow on jobs to query this table and email / report out errors, etc.

  Which stored procedure has code to log into output file and can set
  location of output file?

The @OutputFileDirectory parameter in the MaintenanceSolution.sql script is where you should set this. If no ouput is specified, the SQL Server error log directory is used. This is referenced in the Advanced page on the job step. Depending on your SQL Version, you will see SQLLOGDIR or the actual output path. SQLLOGDIR is an agent token added in 2014.

  (SQLLOGDIR)   Replacement token for the SQL Server error log folder path
  - for example, $(ESCAPE_SQUOTE(SQLLOGDIR)). This token is only available on SQL Server 2014 and above.

Another way, if you are using Windows Scheduler or wanted to edit the Agent Job directly is by specifying your log location for sqlcmd via the output option.

  The output option (-o) specifies the file in which sqlcmd is to put
  its output.

Thus, something like -o C:\Log\YourLog.txt

In that FAQ, Ola provides an example for a DB Backup:

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt


Please do not grant any permission to any User outside of EXECUTE on a particular Stored Procedure that will do what you are wanting. Granting ALTER on a Table or Schema allows that User to make any change they want. You want them to be able to TRUNCATE, but granting them ALTER to a Table will allow the User to add / remove / change Columns, etc.

You want to create a Stored Procedure to contain only the specific actions that you feel are permissible. Whether that is a loop that goes through all tables and executes TRUNCATE on each one (will reflect new and removed Tables; might need a pattern specific for the Table name, perhaps?), OR is a list of accepted Tables to be truncated (won't reflect new and removed Tables, but maybe new Tables shouldn't be included anyway), is up to you. Either way, you should retain full control over the scenarios in which the User can perform this action.

Fortunately, this is fairly simple to accomplish. All you need to do is:

Create the Stored Procedure
Grant EXECUTE on the Stored Procedure to the User(s) and/or Role(s) that should be able to perform the TRUNCATE
Create a Certificate
Create a User from the Certificate
Grant the User any permissions needed to perform this action and/or add the User to any necessary fixed Database-Roles needed to perform this action.
Sign the Stored Procedure with the Certificate

The following is the T-SQL that corresponds to the steps noted above:

-- 1) Create the Stored Procedure:
GO
CREATE PROCEDURE dbo.[SomeTable_Truncate]
AS
SET NOCOUNT ON;

    TRUNCATE TABLE dbo.[SomeTable];
GO

-- 2) Grant EXECUTE on the Stored Procedure to the User(s) and/or Role(s)
--    that should be able to perform the TRUNCATE.
GRANT EXECUTE ON dbo.[SomeTable_Truncate] TO [SomeUserOrRole];

-- 3) Create a Certificate
CREATE CERTIFICATE [Permission$AlterSchema]
    ENCRYPTION BY PASSWORD = 'choose_a_password'
    WITH SUBJECT = 'Allow for TRUNCATE TABLE',
    EXPIRY_DATE = '2099-12-31';

-- 4) Create a User from the Certificate
CREATE USER [Permission$AlterSchema]
    FROM CERTIFICATE [Permission$AlterSchema];

-- 5) Grant the User any permissions needed to perform this action and/or add the User
--    to any necessary fixed Database-Roles needed to perform this action.
GRANT ALTER ON SCHEMA::[dbo] TO [Permission$AlterSchema];

-- 6) Sign the Stored Procedure with the Certificate.
--    Repeat this step as necessary.
ADD SIGNATURE
    TO dbo.[SomeTable_Truncate]
    BY CERTIFICATE [Permission$AlterSchema]
    WITH PASSWORD = 'choose_a_password';

That's it.


Can I identify duplicates in a large MySQL insert?

This can probably be solved with a more complex set of steps.  You still get the benefit of the bulk INSERT.

Build a temp table (probably permanent, but TRUNCATE between uses).

INSERT the 2K rows into it.

Analyze the rows via a JOIN between the temp table and the real table.

INSERT INTO real ... SELECT ... JOIN ... to put the "new" rows.

More details in http://mysql.rjweb.org/doc.php/staging_table , especially the section on 'Normalization', which uses two queries in a slightly different way -- one to insert 'new' rows, another to pull back the id for every row.

Your app may also benefit from the "flip-flop" technique described there.


Truncate and insert: Heap and build index or directly into Clustered table

I would keep the clustered index in-place especially if the data is being inserted in large lumps (rather than lots of individual inserts).
You should drop the non-clustered indexes if rebuilding the data from scratch.
NOTE: As per your mention of truncating, this answer is talking about rebuilding a table from scratch.
Considerations would be different if you were adding millions of rows to a table that already contained billions.

the clustered index insert operator right before the root node is quite expensive

It will be as that is the step that is writing all the data to permanent storage. You'll get a similarly expensive step with a heap.

(basically all the cost is divided between the sort and the clustered index insert operators).

This is expected. If you turn the index off so that you have a heap, when you readd the clustered index it will have to reread the data, perform the same sort, and rewrite the pages in the new order - so it will be as expensive, probably more so, than the initial insert with the clustered index turned on.

I know that I can test it and measure it

This is a very good point!
Don't just measure the data insert though: remember that creating the index on an already populated table will be expensive too, so it is "unfair" to compare just the "insert heap" & "insert with CI" not "insert heap + build CI" and "insert with CI". Also, it will need more space in the relevant data file as it will have two copies of the data as the index is being rebuilt (the heap and the newly forming clustered index that will replace it when complete).
Try both in otherwise empty fresh (therefore near zero length data files) test DBs to see the different file growth effects too.

but I what to understand the reason

I suggest trying it, with the index rebuilds too, and look at the work done as displayed in the query plans and the IO statistics.


Just to extend on Max Vermon's comment...

INSERT failures should be monitored at the data access layer.

An exception is raised by SQL with information regarding the error for failed INSERTs. If you are accessing data through a Stored Procedure (SP) you can handle the error with the SP using the BEGIN TRY...END TRY...BEGIN CATCH...END CATCH exception handling blocks.

You can easily write code to store the exception information into a generic table.

You can use exception handling in other programming environments such as C#, VB.NET etc...

Here is a good example of handling exceptions in T-SQL: http://www.codeproject.com/Articles/38211/Exception-Handling-in-SQL-Server


Instead of using "Copy-and-paste" to copy from the user-interface; use a T-SQL script to copy the rows from one table to another.

Setup a test-bed with two tables, one will be the source, the other the destination:

USE tempdb;

CREATE TABLE dbo.StatusTable1
(
    StatusNumber NUMERIC(18,10)
);

CREATE TABLE dbo.StatusTable2
(
    StatusNumber NUMERIC(28,2)
);

INSERT INTO dbo.StatusTable1 (StatusNumber)
VALUES (1370.00000000)
    , (2850.00000000)
    , (426.00000000)
    , (289.00000000)
    , (2000.00000000);

this part copies rows from StatusTable1 to StatusTable2:

INSERT INTO dbo.StatusTable2 (StatusNumber)
SELECT st.StatusNumber
FROM dbo.StatusTable1 st;

Show the content of both tables:

SELECT *
FROM dbo.StatusTable1;

SELECT *
FROM dbo.StatusTable2;

Cleanup the test-bed:

DROP TABLE dbo.StatusTable1;
DROP TABLE dbo.StatusTable2;

The results:


How do I copy my SQL Azure database to a local SQL Server instance?

Use the SQL Azure Migration Wizard:

  The SQL Azure Migration Wizard (SQLAzureMW) gives you the options to
  analyzes, generates scripts, and migrate data (via BCP) from:

  SQL Server to SQL Azure
  SQL Azure to SQL Server
  SQL Azure to SQL Azure


The following system error occurred: No mapping between account names and security IDs was done

from the SQL management studio, connect to Analysis services and go to Roles, under the roles, the user that has been removed from active directory will appear in question marks "????????????????" , remove him and reprocess the cube


Is it possible to change recursion level of a CTE in a function?

This is what Mikael was hinting at, I believe.

You will find things work a lot quicker without that MAX parameter, are you really going to need that big a string?

CREATE FUNCTION [dbo].[StringSplit]
(
    @String NVARCHAR(MAX)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
        SELECT N AS Pos, substring(@String, N, 1) AS Digit
        from cteTally
);


Identity management in Transaction replication with updatable subscription

My solution for your problem is skip the primary key error in the replication.

Through the SQL Monitor create a Agent Profile with the code 2627 and let the primary keys error pass. After that the replication should be normal again.

When you create and mark the agent profile you will need stop and run the subscriber job.


How to find the max date stamp in a database?

There is no way to query on a datatype much less across various tables like that, it would imply that the entire database is kept a second time in a set of tables that resemble the system tables. However, dynamic SQL using the INFORMATION_SCHEMA can be of assistance in your search, but it will not be portable to other types of SQL Servers. Most servers should have something similar available but the syntax will be radically different for each one.

You can do the following to find what you're looking for:

SELECT 'SELECT max(modifiedData) FROM ('+
        (SELECT 'SELECT MAX(['+C.COLUMN_NAME+']) as modifiedData FROM ['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+'] UNION ' AS [text()]
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.DATA_TYPE IN ('date','datetime','smalldatetime','datetime2')
        FOR XML PATH('')) + ') AS AllDateTimes'

That dynamic SQL will output a query you can run to find your MAX date. Please note that you will need to trim a trailing UNION from the inner SELECT after you've copied the output to a query window. You can create the same query with the sys tables but you will have to join a few of them together and the query will be a bit more complex if you aren't familiar with how the sys tables are setup.

That all said, this will return the maximum date stored in your database regardless of context. Depending on how it was used there is no guarantee some value could not have been set in the 'future' for any number of reasons.


multiple join HOW to get the following result?

Simply join those two queries:

with tab1 as (
  select branch_code,
         ABS(nvl(sum(cr_bal_lcy - dr_bal_lcy), 0)) as A35
  from gltb_gl_bal_daily
  where gl_code in ('620100000','620230000','620400000','620800000',
                    '620910000','620930000','621010000','621020000',
                    '630000000')
    AND trn_dt = &PM_DATE --entry
  group by branch_code
), tab2 as (
  select branch_code,
         ABS(nvl(sum(cr_bal_lcy - dr_bal_lcy), 0)) as A35
  from fcubs1203.gltb_gl_bal_daily
  where gl_code in ('620820000', '630300000')
  AND trn_dt = &PM_DATE
  group by branch_code
)
select tab1.branch_code, tab1.a35 - coalesce(tab2.a35, 0)
from tab1
  left join tab2 on tab1.branch_code = tab2.branch_code;


How to make a Root to get access to the MariaDB server, trough a client app like MysqlWorkbench?

The answer to this question should help you out:

$ sudo mysql -u root

[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;

In short, the new version of MariaDB checks to see what the system user is and only allows the actual system user root to log in to the DB user root. Disabling all plugins disables that functionality.


Rule of thumb 1: If the table contains stuff that may change (edit, delete or add columns) in the future, it probably belongs to the data level.

Rule of thumb 2: Always have an unique key to every table. It is simpler to edit order records if there is a unique key for each row.

Rule of thumb 3: Rules of thumbs is not always the best way to do things. It always depends...

One way to struct this:

article_type

type_id   1          2         3
type_desc Burger     Drink     Extra stuff

EDIT 1 ---> You do not need to have drink categories. You can have Burger Big, Drink too much etc as several types. The article is the king with all information gathered. Select article and you have all information you need.

article

art_id    1              2               3             4
art_desc  Burger Big     Burger Small    Burger King   Drink little
art_type  1              1               1             2
art_price 50             40              60            10

EDIT 2 ---> I can see no value (at this point) to have a separate price table. The price should be attached to the article. Which also means one less column in the order table.

order

order_id     11
order_status 1 (int) Lookup table
order_date   2018-01-01

order_rec

ordrec_id    1
ordrec_order 11
ordrec_art   1
ordrec_sum   50

When fetching an order you can sum on-the-fly. No need to save sum at order level.

SELECT order_id,sum(ordrec_sum)
LEFT JOIN order_rec ON ordrec_order=order_id
GROUP BY 1
WHERE order_id=11

article_type can be useful if you add more groups of meals to the articles.


ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I've got a similar issue, the solution:

0. login to MySQL

$ sudo mysql -u root
use mysql;

1. Change Password

UPDATE user SET authentication_string=PASSWORD('yourpass') WHERE User='root';

2.Change Plugin

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User='root';

3. Finish It

flush privileges;
exit

Extra info

If you get errors like: "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements" you can view the password rules with:

SHOW VARIABLES LIKE 'validate_password%';


Postresql dynamic index creation for many tables

You can use dynamic SQL with a do statement that iterates over the tables in a schema:

do
$$
declare
 l_statement text;
 l_rec record;
begin
  for l_rec in select y
               from x
  loop
    l_statement := format('create index on %I (z)', l_rec.y);
    execute l_statement;
  end loop;
end;
$$

Postgres will automatically generate a name for the new index.


Enable TLS 1.2 for SQL Server 2016 database mail

TLS1.2 is the only version of TLS considered secure now (March 2019).  It took considerable time and effort to discover that there are 2 essential, additional settings which are required to get this working which are not well known nor well documented, by Microsoft or on the web generally.  The following could save you a great deal of time and effort.
These are the 2 new Registry settings that fixed the problem for us:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

This is a reference to the thread where we eventually found this information, buried halfway down the thread: TLS 1.2 in .NET Framework 4.0

Below is the content for a simple executable registry file that I put together that will make the 2 new settings and the settings already shown on the thread above (i.e. this makes all of the necessary Registry settings*):
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

Note 1: SQL needs to be restarted for these settings to take affect but it is better to restart Windows since the new settings will affect .NET 4.x generally.
Note 2: In SQL, the SSL-checkbox must be ticked in the mail profile to use TLS1.2.
*Note 3: FYI We ran the free tool, Crypto V2, with the "Best Practices" option enabled before starting on getting this working.  We verified our changes afterwards using the new Crypto version 3.
Hopefully this will save considerable time, effort and frustration for others ;)


Use CASE?

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

something like:

lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= (case when parameter2 is null then
sysdate
  else
to_date(parameter2, 'dd.mm.yyyy')


"Table doesn't exist in engine" after moving tables from one VPS to another

You can copy files from one server to the next to move your database. To do this, you must make sure that both instances of your DB are stopped. I also recommend using rsync to copy your database files. In addition to making sure that both database servers are stopped before moving the files, you will most likely need to change the ownership of your files. sudo chown --recursive mysql:mysql /var/lib/mysql before starting your new instance.

There are other options available to move your database. If your database isn't terribly large, mysqldump is probably the most simple. I have attached the documentation for mysqldump below as provided by MariaDB, as you are using MariaDB.

https://mariadb.com/kb/en/mariadb/mysqldump/

Another option to consider, a bit more complex but a little more flexible for size, would be Percona's xtrabackup.

https://www.percona.com/software/mysql-database/percona-xtrabackup


Unfortunately the script itself does not have a built in path to retry a single DB for a backup if it failed. You can view the entire script options here on Ola's Site.

What I would recommend (albeit it may be a little painful) is to have a separate job step for each DB and specify the DB you want to backup. Then in each step you can add the retry options.

You could probably start by creating one or two, and then hit the script button in the SQL Agent. From there you can copy and paste the sections of SQL that create each subsequent job step and make a few alterations rather than manually modifying the job steps through the GUI.

From there I would begin evaluating whether Ola's scripts are meeting my organizations needs and I may start to look to other backup options like DBATools or Minionware or custom backup scripts. You could also alter the Ola scripts but this will be significantly more difficult. There are also paid tools that may be worth evaluating.


Why can an Oracle SET UNUSED COLUMN cause internal UPDATE SET X=X and huge log generation?

Oracle said it wasn't a known issue, and basically asked us to re-run with tracing on. Unfortunately on a large production database, and given large downtime required to rollback last time, this wasn't really an option.

We had suspicion that it is related to this bug fixed in 11.2.0.4.2

Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption (Doc ID 17325413.8)

This problem is introduced in 11.2.0.4 and 12.1.0.1 by the fix for bug 14222244

Drop of a column with a DEFAULT value and a NOT NULL definition ends up with dropped column
data being written to disk leading to block corruptions.
This causes problems for generating undo which cannot be applied; a ROLLBACK fails.

Rediscovery Notes
 If a NOT NULLable column added via "add column optimization" is writing to disk, then it
 is likely you have run into the bug.

 This can result in block corruption causing ORA-600 block check errors with check code
 errors 6264, 6110, or 6103 when db_block_checking is enabled:
  ORA-600 [kdBlkCheckError] [file#] [block#] [6264] --> needs _db_block_check_for_debug to catch it.  This is a ZLC logical corruption (Zero Length Column instead of NULL)
  ORA-600 [kdBlkCheckError] [file#] [block#] [6110]
  ORA-600 [kdBlkCheckError] [file#] [block#] [6103]

or other internal errors when db_block_checking is not enabled like ORA-600 [13013], ORA-600 [17182] or external errors like ORA-12899.

Workaround

 Note that once error occurs, table has to be rebuilt:
   Note:1527738.1 may skip the rows affected and can be used with _disable_block_checking=false as:
      alter session set "_disable_block_checking"=true;
       and in the same session use the PLSQL in Note:1527738.1 section "SKIP ORA-600 in a Table"

After patch to 11.2.0.4.6 the SET UNUSED ran fine in production without the unexpected log production. Might be worth a go if anyone else stumbles into this problem; although I suspect Oracle will eventually have to raise a proper bug for this issue once someone helps them narrow it down properly.


Will using bigint vs mediumint have a performance impact?

Why does the user know the value of the ID column? This should be abstracted from them. They should be able to identify their data by some other natural key (even if this is only implemented as a unique constraint and you use the surrogate ID as a primary key for performance reasons). If you take away the ID column and you can no longer uniquely identify a row, there is likely an issue with the design.

While you can save some space and improve performance in small ways by choosing a different data type, you need to balance performance with actual business requirements. If you're going to exceed the bound of a smallint or mediumint then you shouldn't use either. Don't make your design complex for the exception (e.g. "splitting up the numbers") to try and optimize for the general rule. This complexity is unnecessary IMHO and will only lead to problems.

In SQL Server we can take advantage of data compression, which essentially treats a bigint column in the following way:

if the number fits in a tinyint (0-255), it takes 1 byte
if the number fits in a smallint (-32K -> 32K), it takes 2 bytes
if the number fits in an int (-2Bn -> 2Bn), it takes 4 bytes
only if the number exceeds the bounds of an int does it take 8 bytes

(This is a simplification. And this is the worst case where it treats each value independently only if values on a page are unique and non-sequential, which wouldn't be the case for an auto-increment column. In addition page compression uses other methods like dictionary/prefix, which can reduce storage even further.)

Of course compression trades CPU for I/O, so while you save raw storage and reduce I/O, you pay in CPU. Even today most systems are I/O-bound, so this is a benefit, but shouldn't be considered if you're CPU-bound.

I don't believe there is similar functionality in MySQL, but I am not certain. If there is you should look into it.

And one final note. You say "I do not care about storage, but I do care about speed/performance." If you care about speed/performance, then you should care about storage. The more pages it takes to store your data, the harder the engine is going to have to work to read/write those pages, perform seeks, etc. But again, there needs to be balance. You shouldn't take an extra four weeks over-engineering a solution that is going to save you 10 seconds a week.


How to Update Column in this example in sql?


Should I convert timezones when moving data to a report server/data warehouse

Yes, there is a reason to not convert the timezone, and to keep the times in UTC. Times in UTC can be converted on-the-fly to any given timezone (and timezone's do occasionally shift, and Daylight Savings rules shift even more often). UTC is always accurate, whereas timezones and regions that allow for Daylight Savings Time have overlaps in the "fall-back" period when you repeat a particular hour. Save yourself a lot of pain down the road and keep the data in the current, more accurate format.

For the purposes of efficient searching, either convert incoming time ranges to UTC first, or, create a non-persisted computed column that represents Mountain Time (be sure to look up the historical rules governing start/end dates for DST) and create a NonClustered Index on that computed column.


Count null and not null values in a column

Try

SELECT
   DATE_FORMAT(registDate, '%m-%Y') AS month,
   COUNT(name) AS register,
   SUM(!ISNULL(visited)) AS visited,
   SUM(ISNULL(visited)) AS not_visited
FROM mytable
GROUP BY DATE_FORMAT(registDate, '%m-%Y');

No need to create another column.


Why does mysqldump stop the slave SQL thread for the entire backup?

--single-transaction obviously only preserves a transaction for the transactional tables.

The STOP SLAVE is needed to preserve the consistency of the dump of the MyISAM tables. If there was logic in mysqldump to dump the MyISAM tables first, it could START SLAVE at that point (in a different connection as START SLAVE probably an implicit commit`), and continue dumping the InnodDB tables.

The STOP SLAVE is also required as DLL isn't transactional. What could happen is while tables are being dumped, a CREATE TABLE / DROP TABLE could happen in the replication stream, and as those aren't transactional, the dump would then be inconsistent.

Why --master-data assumes it isn't a slave and doesn't need the same level of protection is anyone's guess. Why --master-data issues a FLUSH TABLES WITH READ LOCK around dumping position information and dump slave doesn't? Why the UNLOCK TABLES happens before the data dump starts? Also a mystery.


Best practice for table to table operation

This is what the MERGE statement was created for:

merge into table_b
using
(
  select pk_col, col1, col2
  from table_a
) t on (t.pk_col = table_b.pk_col)
when matched then
  update
    set col1 = t.col1,
        col2 = t.col2
when not matched then
  insert (pk_col, col1, col2)
  values (t.pk_col, t.col1, t.col2);


Using a many-to-many table with a one-to-many relation?

Your customers may not offer every possible attribute, right?  E.g., "T-Shirt, Cotton" is available in small, medium, and large, but my store only offers small and medium.  Maybe even black shirts in small and white shirts in small and medium, but not black shirts in medium.  To support that, you need a table which normalizes attributes (one record per possible value, and you need to join your Shops table to that.

So suppliers would populate these tables:

CREATE TABLE Products
(
ProductID   INT NOT NULL PRIMARY KEY,
SupplierID  INT NOT NULL,
CategoryID  INT,
Description VARCHAR(100) NOT NULL,
BasePrice   DECIMAL(18, 2) NOT NULL,
Image   VARBINARY(MAX),
UNIQUE (SupplierID, Description)
)

CREATE TABLE ProductAttributes
(
AttributeID INT NOT NULL PRIMARY KEY,
ProductID   INT NOT NULL,
Dimension   VARCHAR(100) NOT NULL,
Value   VARCHAR(100) NOT NULL,
PriceModifier   DECIMAL(18, 2) NOT NULL DEFAULT 0.00,
Image   VARBINARY(MAX),
UNIQUE (ProductID, Dimension, Value)
)

Shops would then populate two tables to show what they offer, one for products and one for each intersection of attributes.  It'll be important that the UI allow store owners to quickly add all attributes; you don't want them ticking a box for every record.

CREATE TABLE ProductsOffered
(
ShopID  INT NOT NULL,
ProductID   INT NOT NULL,
PRIMARY KEY (ShopID, ProductID),
Description VARCHAR(100) NOT NULL,
Image   VARBINARY(MAX),
BasePrice   DECIMAL(18, 2) NOT NULL
)

CREATE TABLE AttributesBuckets
(
ShopID  INT NOT NULL,
BucketID    INT NOT NULL,
AttributeID INT NOT NULL,  -- Implies ProductID
PRIMARY KEY (ShopID, BucketID, AttributeID),
Image   VARBINARY(MAX),
PriceModifier   DECIMAL(18, 2) NOT NULL DEFAULT 0.00,
)

So if a store decides to sell T-shirts in "black" and "small" there will be a record in AttributesBuckets for ShopID = MyShop, ProductID = T-Shirt, BucketID = 1, AttributeID = color/black, and another record with AttributeID = size/small.  That's one "bucket;" one combination of attributes which fully describes a product that can be sold.  If they also sell small white shirts, that's one more bucket, defined by two more records.  If you add a "stitching" option, each bucket will have three records, for color, size, and stitching.

As a bonus, you can now track inventory:

CREATE TABLE Inventory
(
ShopID  INT NOT NULL,
BucketID    INT NOT NULL,
PRIMARY KEY (ShopID, BucketID),
Quantity    INT NOT NULL DEFAULT 0
)


oracle 11g seems to die every 2 days, how can i debug what's happening?

Oracle has an alert log which should tell you when and why it shut down. Depending on how the database was configured, that will be under a directory identified by either the diagnostic_dest or background_dump_dest parameters, and will be called alert.xml or alert.log (in previous versions). If you're running it, you can get information from Enterprise Manager too.

Has the server it's running on rebooted as well? If so the listener may be configured to start  automatically, but the database not; there's a flag in the oratab file that says whether each instance should start, e.g. when dbstart is run, which could be done from an init.d script on boot. You could try changing the flag for your database from N to Y to see if it stops you having to start it manually, at least.

If the server hasn't rebooted then the database may have crashed, in which case I'd expect to see errors in the alert log - search backwards, particularly for ORA-00600 errors which can be fatal, but also for anything close to the time it crashed.

Based on the ORA-600 in the alert log, this looks like it might be bug 6651027, which is related to the resource manager and is supposedly fixed in 11.1.0.7 and 11.2. If you have access to the Oracle Support website you could look at note 559251.1, and the patch set notes. You can also look at the trace file referenced in the alert log for more detail on exactly where it's failing.

If you're on an earlier release I'd recommend you patch up and see if the problem goes away. If it doesn't then you'll need to raise a support assistance request with Oracle I'm afraid. ORA-600s that aren't already fixed in a patch sometimes have a workaround, but often need Oracle's input.


Adding Nullable Column To Production DB taking too much time

Adding a nullable column like that is generally a meta-data-only operation.  That is it will complete nearly instantly as long as it can get a schema stability lock on the table for the short time it takes to add the column.
If you're seeing it take a long time, the cause is most likely that other sessions are blocking the ALTER TABLE statement.  Realize, too, that when you run the ALTER TABLE statement, while it's waiting to get a schema stability lock, it will block subsequent queries on that table.  You probably want to look at setting the lock timeout for your session prior to running the ALTER TABLE statement.
If blocking other sessions is problematic, you could wait until there is a period of lower activity, and run the command then.  Alternately, you could put the database into single user mode, alter the table, then put it back into multi-user mode - of course that would prevent any sessions from accessing the database during that time.
To put the database into single user mode and alter the table, you'd do something similar to this:
USE master;

ALTER DATABASE [MyDB]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

USE [MyDB];
ALTER TABLE [MyDB] ADD [MyColumn] varchar(30) NULL;
GO

USE [master];
ALTER DATABASE [MyDB]
SET MULTI_USER;
GO

Be aware though, setting the database into single user mode like that will cause any in-flight transactions to be rolled back, which, depending on how your client software is designed, might cause a loss of data.  Any transactions in flight at the time of the ALTER DATABASE statement would need to be re-executed once the database is brought back into multi user mode.  Take a look at the Microsoft Docs page for ALTER DATABASE, and in particular the section on WITH ROLLBACK, for various options that control how the server puts the database into single user mode.


Find clients who bought more than 1 million units in 3 consecutive months

Before the edit

I created a table to test here the create code ( you will have to alter your table and add unique identifier for this to work )

CREATE TABLE `milad` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `cCode` int(10) unsigned NOT NULL,
 `gDate` date NOT NULL,
 `buy` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
)

Then I inserted the date you used and test with 3 days not 3 months

SELECT t2.cCode,sum(t2.buy) AS total
FROM milad As t1
INNER JOIN milad AS t2
ON t1.cCode=t2.cCode
WHERE t1.gDate<=DATE_ADD(t2.gDate,INTERVAL 3 day)
group by (t1.id)
having total>1000000
ORDER BY t1.cCode , total DESC

Here is the result

+----------+---------+
| cCode    | total   |
+----------+---------+
| 12400573 | 4008000 |
| 12400573 | 2672000 |
| 12400573 | 1336000 |
| 12600125 | 4859000 |
| 12600125 | 3592800 |
| 15108164 | 3306820 |
| 15108164 | 1653410 |
| 16100286 | 4251290 |
| 16100286 | 2995570 |
| 16100286 | 1255720 |
+----------+---------+

After the edit

SELECT t1.cCode
FROM milad As t1
INNER JOIN milad AS t2
ON t1.cCode=t2.cCode
and (
  (
    year(t2.gDate) = year(DATE_ADD(t1.gDate,INTERVAL 1 month))
    and month(t2.gDate) = month(DATE_ADD(t1.gDate,INTERVAL 1 month))
  )
  or
  (
    year(t2.gDate) = year(DATE_ADD(t1.gDate,INTERVAL 2 month))
    and month(t2.gDate) = month(DATE_ADD(t1.gDate,INTERVAL 2 month))
  )
)
group by t1.cCode,t1.gDate
having count(t1.gDate)=2

The result

+----------+
| cCode    |
+----------+
| 16100286 |
+----------+

Another solution using a function

Function code

DELIMITER $$
CREATE FUNCTION milad(D date,I int) RETURNS boolean
BEGIN
if
(SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL 1 month))
    and month(gDate) = month(DATE_ADD(D,INTERVAL 1 month))
    and buy>1000000
    and cCode=I
 )>0
and
(SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL 2 month))
    and month(gDate) = month(DATE_ADD(D,INTERVAL 2 month))
    and buy>1000000
    and cCode=I
 )>0
THEN RETURN true;
ELSE RETURN false;
end if;
end$$
DELIMITER ;

Then just use this query

SELECT cCode
FROM milad
where milad(gDate, cCode)

The result

+----------+
| cCode    |
+----------+
| 16100286 |
+----------+

This will work if there are two rows in same month for the same person.

Please if someone can do it without a function, write the answer and mention me.


postgres cluster: Crash of master crashes the replicas too

This is harmless.
That message is sent by the server to the client when a process has crashed and the backend is about to die (quickdie in postgres.c) so that crash recovery can begin.
You are only seeing the message sent by the primary server to the WAL receiver. Note the WARNING: this is not even an error. This only gets logged because log_min_messages on the standby is set to warning or lower.
The standby server keeps running – as you see, it catches up from the archive while the primary server recovers. As soon as it has read all archives and the primary is up again, it will reconnect and continue streaming.