The name locked is either referenced in the query being sent to SQL Server or referenced in a programmability object (e.g. trigger) being executed. I suggest you start with a trace of batch and rpc starting events to rule out the first case. The name lockedsuggests it may be used for application level concurrency so perhaps there's an ORM lay or similar injecting it.

The trace can be done using the legacy Profiler/SQL Trace or an Extended Events Trace. Below is an example XE trace that filters by application name to reduce the noise or you could run an unfiltered one on a test box.

CREATE EVENT SESSION [batch_and_rpc_completed] ON SERVER
ADD EVENT sqlserver.rpc_starting(
    ACTION(package0.event_sequence,sqlserver.session_id)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'YourApplication'))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(package0.event_sequence,sqlserver.session_id)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'YourApplication')))
ADD TARGET package0.event_file(SET filename=N'batch_and_rpc_completed')
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);

EDIT:

Here's another trace that captures only error_reported events. This is a SQL 2014+ version gleaned from the Brent Ozar's SQL 2012 version of this trace @sp_BlitzErik referenced in his comment.

CREATE EVENT SESSION failed_queries ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.username)
    WHERE ([severity]> 10))
ADD TARGET package0.asynchronous_file_target(SET filename = 'failed_queries' ,max_file_size = 5,max_rollover_files = 5)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);


Mysql Connection Pooling Similar To PostgreSQL's PgBouncer

This started off as a comment but it's getting a bit long.

Perhaps you could provide a better description of what it is you actually want rather than simply say it's like something else.

If you want multiple clients to use the same connection, then it must be implemented in the DBMS, otherwise you have no session management. Further you need to advise how you intend dealing with issues of state spanning more than a single request/response (i.e. transactions).

It would also be helpful to know why you want it. MySQL has a very different setup cost and per-session overhead cost compared to PostgreSQL - why do you think it needs it, and why you think it would actually yeild any benefit.


How do you list all items from all databases located in a specific tablespace in postgresql?

pg_tables seems to be db-specific, whereas (I think) tablespaces are global for a postgres instance.

That's true. But there is no way of knowing what's inside of a tablespace without connecting to the specific databases and checking their catalogs. Iterating is the only option. Think of it like a glorified virtual storage system.

You may consider running pg_dumpall --schema-only and trying to parse that output if you're interested.


Reclaim disk space after drop table from database in mysql

You need to "reorganize/rebuild" the table truncated  which can be achieved by below.

mysql> OPTIMIZE table table_name;

for InnoDB tables, it run under ALTER operation.

reference: https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html


How to LOCK/BLOCK selected row in table to be ReadOnly?

When would you unlock it?

A typical pattern is:

BEGIN;
SELECT ... FOR UPDATE;
scratch your head, then eventually
UPDATE that row (or not)
COMMIT;

That keeps anyone else from messing with the row, but they can read it.  It's not quite "ReadOnly", and the "lock" is released at the COMMIT.

If this does not suffice, please describe your use case more thoroughly.


Default trace is just a collection of behavioral information about the system. It's not the same as auditing. They do different things and support different purposes. One doesn't supplant the other.
Further, default trace is actually inferior to another thing running on all systems by default, the system_health Extended Events session. In terms of information gathered and load on the system, system_health is a better alternative to the default trace. The information there is all about errors, deadlocks, long wait times, long running queries, stuff like that. It's not about security and change auditing.
SQL Server Audit is using Extended Events under the covers as a mechanism for tracking security, change auditing, and the rest of stuff that's needed for auditing your servers. That's distinct from the default trace or system_health.
So, I'd take it to your more senior people that, you probably need both. The good news is, system_health is already there. Now, implementing auditing carries with it quite a bit of work and some changes in how you do deployments and other things. You'll want quite a bit of testing before you implement it.


What's the difference between MySQL Fabric and MySQL Cluster

These 3 things have nothing to do one with each other. I will give you an introduction and you can search or ask for more specifics separately:

MySQL Cluster is the marketing name of what many of us call - to avoid confusions like the one you are currently suffering from - "NDB Cluster" or "MySQL NDB Cluster". It is a synchronous mostly memory-only key-value store that allows a shared-nothing architecture for MySQL (providing a backend to the NDB Engine). It provides automatic sharding (so, better read and write throughput) and high availability. It works well for high-throughput key-value small-data systems, but it is relatively complex to setup (leading to problems if it is not configured correctly, or it is used in unintended ways) and it is not the best thing latency-wise (raw speed). This is simplified version (it can be used for more thing than memory-only and key-value, but it was designed around it). In my opinion, a very good piece of engineering, but it has limited use cases, as it is a different product than regular MySQL server with separate downloads (and requires the usage of a specific engine).

MySQL Fabric is a set of tools/middleware written in python that allows the managing of a set of regular MySQL servers in a replicated GTID environment. It is not a cluster per se, but it facilitates the managing of one. It has links to (for now) Java and Python connectors, so it can handle automatically the sharding and high availability of servers, but it is a layer on top of regular MySQL servers and regular replication (regular storage engines like InnoDB can be used). Replication, up to now is asynchronous (or semi-synchronous), so it may not provide by itself the best consistency and security; this may change in future 5.7 versions. However, this approach has less overhead on latency and uses MySQL DBA's more familiar replication technology. MySQL Fabric is a relatively new kid in the neighbourhood, so not yet very extended, but it seem to be Oracle's reaction (together with announced synchronous replication) to competitor's Galera-based clusters.

If you needed sharding and HA, you may use one of the 2 former products, but probably for totally different scenarios.

WebScaleSQL is not an HA solution, like the previous ones, it is just the name that several companies gave to a fork/version of MySQL where they share specific patches that they need and maybe Oracle has not applied. As far as I know it has been created by Facebook, Twitter, LinkedIn and Google, and they rebase their own MySQL versions on top of that common base. They do not provide support or binary releases. If you are an end user, you probably should not care to use it, but you should keep an eye on the project to see what will evolve from it, or the response from other vendors.

If you are trying to learn/start with MySQL HA/scaling, start with basic MySQL replication (it may not be the best, but it is the easiest thing to start with), then you can continue from there to other solutions: DRBD[sic], GTID-replication, NDB, Galera, Tungsten.


Toad For MySql Give error on new connection

Please check following condition

root user has privilege to connect to localhost
you have given right password for the user root


Analysis
Since we are talking about InnoDB, let's zero in on the gen_clust_index. It's a special index that keeps the PRIMARY KEY and associated row data accessible from the same InnoDB pages.
According to MySQL Documentation on the gen_clust_index

If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

This being the case, you would really feel efficiency or deficiency in the following areas:
DiskSpace
Having the single auto_increment column as the PRIMARY KEY keeps the overall size of the PRIMARY KEY smaller that that of having two columns. Why? BTREE pages would be twice as big if the PRIMARY KEY was two INTs instead of one. This becomes even more painful if you use foreign key constraints and secondary indexes as they must subsequently blow up in size as well.
In this instance, you would choose the second schema for better index usage.
Insert Performance
Inserting row data into an InnoDB table that has two UNIQUE indexes calls for twice as much BTREE management and unique checks.

One for the gen_clust_index (dictated by auto_increment)
One for the other UNIQUE index (dictated by (storeId, zoneId))

Additional microseconds for each additional UNIQUE index would add up in CPU time when inserting millions of rows.
In this instance, you would choose the first schema for faster INSERTs.
Conclusion
The diskspace and insert performance issues pretty much force you to choose. Obviously, you choose which one to live with. If you use foreign key constraints that represent (storeId, zoneId), then you need the second schema using the auto_increment as the reference in outside tables.


How to escape special characters in MySQL

To escape all special char except NULL, new line and tab:
select * .. | mysql ... | sed 's/[\x04-\x08\x0B-\x1F\x7F]/ /g' > /tmp/file
useful link:
https://stackoverflow.com/questions/6534556/how-to-remove-and-all-of-the-escape-sequences-in-a-file-using-linux-shell-sc


Prove or disprove that `T=R÷S` implies `S=R÷T`?

First, your answer is "Yes" but without any proof, only a single example. You have proven nothing about the general case. If I were to mark your answer, you'd get a straight 0 (OK, maybe a 1 because you understand what relational division is and how to perform it) and a note to read up on mathematical proofs.

For the actual question, the answer is: No, it is not correct. Now to prove that, we do only need a single counter-example. Here it is:

 R                        S          ==>    T=R÷S
 ------------         ---------             -----
| A | B | C |         | A | B |             | C |
| 1 | 2 | 4 |         | 1 | 2 |             | 4 |
| 1 | 2 | 9 |                               | 9 |
| 3 | 3 | 4 |
| 3 | 3 | 8 |
| 3 | 3 | 9 |
| 4 | 1 | 2 |

But:

 R                      T      ==>       R÷T != S
 ------------         -----           ---------
| A | B | C |         | C |           | A | B |
| 1 | 2 | 4 |         | 4 |           | 1 | 2 |
| 1 | 2 | 9 |         | 9 |           | 3 | 3 |
| 3 | 3 | 4 |
| 3 | 3 | 8 |
| 3 | 3 | 9 |
| 4 | 1 | 2 |

This is actually similar to integer division (when we discard the remainder). Sometimes it's Yes:

37 / 7 = 5 (the remainder 2 is discarded) and
37 / 5 = 7 (the remainder 2 is discarded)

and sometimes is No:

37 / 10 = 3 (the remainder 7 is discarded) but
37 / 3 = 12 (the remainder 1 is discarded)

and of course 12 != 10, and the single counter-example disproves the general case.

What can be proven is this:

If R÷S = T, then S is a (not necessarily strict) subset of R÷T.


What is an "empty query" in information_schema.CLIENT_STATISTICS?

Unfortunately this is not documented in the MySQL manual - the page would be this one.  I didn't know the answer, but I did manage to debug it from the 5.5 source:

It is queries to the server that do not produce SQL queries.  i.e.

laptop1113-2:~ morgant$ mysql -e "SHOW GLOBAL status like 'Com_empty_query';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_empty_query | 2     |
+-----------------+-------+
laptop1113-2:~ morgant$ mysql --comments -e '-- sql comment'
laptop1113-2:~ morgant$ mysql -e "SHOW GLOBAL status like 'com_empty_query';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_empty_query | 3     |
+-----------------+-------+

(You need to add --comments, because the command line utility by default will strip them.)

How to debug something like this in MySQL:

The COM_ variables mean commands - this is a clue that it's some sort of statement.  The code that generates SHOW GLOBAL STATUS is in ./sql/mysqld.cc (you could have found this if you didn't know by grepping for query and empty on the same line).  At ~line 3025:

{"drop_view",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_VIEW]), SHOW_LONG_STATUS},
{"empty_query",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EMPTY_QUERY]), SHOW_LONG_STATUS},
{"execute_sql",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EXECUTE]), SHOW_LONG_STATUS},

So this tells us that show global status is just reading the value of SQLCOM_EMPTY_QUERY.  We can then grep for this string:

    laptop1113-2:5.5 morgant$ grep -riF 'SQLCOM_EMPTY_QUERY' .
    ./mysql-test/r/implicit_commit.result:# SQLCOM_EMPTY_QUERY
    ./mysql-test/t/implicit_commit.test:--echo # SQLCOM_EMPTY_QUERY
    ./sql/mysqld.cc:  {"empty_query",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EMPTY_QUERY]), SHOW_LONG_STATUS},
    ./sql/sql_lex.h:  SQLCOM_SHOW_WARNS, SQLCOM_EMPTY_QUERY, SQLCOM_SHOW_ERRORS,
    ./sql/sql_parse.cc:  case SQLCOM_EMPTY_QUERY:
    ./sql/sql_yacc.yy:            thd->lex->sql_command= SQLCOM_EMPTY_QUERY;

Ignore the tests, and the file we just looked at.  The only other files mentioned here are those that relate to the parser (yacc).  If we open sql_parse.cc, we just see a return with nothing of interest:

case SQLCOM_EMPTY_QUERY:
my_ok(thd);
break;

If we open sql_yacc.yy, we can see that this somehow relates to being at the END_OF_INPUT, and no query was found:

query:
      END_OF_INPUT
      {
        THD *thd= YYTHD;
        if (!thd->bootstrap &&
          (!(thd->lex->select_lex.options & OPTION_FOUND_COMMENT)))
        {
          my_message(ER_EMPTY_QUERY, ER(ER_EMPTY_QUERY), MYF(0));
          MYSQL_YYABORT;
        }
        thd->lex->sql_command= SQLCOM_EMPTY_QUERY;
        YYLIP->found_semicolon= NULL;
      }
    | verb_clause

From here, I pretty much made a guess.  Everything is handled at the parser, and never enters other components of mysqld.


SELECT INTO OUTFILE vs INSERT INTO ... SELECT

You should use SELECT ... LOCK IN SHARE MODE. Why ?

  SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

In your case, you could attempt this

START TRANSACTION;
SELECT ... LOCK IN SHARE MODE;
SELECT ... INTO OUTFILE;
ROLLBACK;

This would do two SELECT queries

First SELECT to lock the rows in the table you wish
Second SELECT to perform SELECT ... INTO OUTFILE

Personally, I do not think you have to be this heavy-handed. Transaction isolation should be smart enough to pull off this atomic SELECT and use the same rows for the INSERT. I know I said should be which is why your are asking you question in the first place.

Whether you do SELECT ... INTO OUTFILE as one command or in the heavy-handed manner I am proposing, the row data of the source table will be fully readable.

GIVE IT A TRY !!!

UPDATE 2014-12-10 15:12 EST

Your comment

  Thx for the answer, and it does help, but the main point of the OP was to determine if there is a benefit for using SELECT INTO OUTFILE over INSERT INTO ... SELECT?

They are operationally different

SELECT INTO OUTFILE creates a text file
INSERT INTO SELECT loads one table from the results of the SELECT

UPDATE 2014-12-11 12:21 EST

The only thing I can think of in this context is the point-in-time of the data and when you are using it. With both types of operations, there will some implicit shared locking.

With SELECT INTO OUTFILE, you are preparing a result and saving it externally. Loading that data into a table using LOAD DATA INFILE will not involve any shared locking during the load process. Keep in mind that SELECT INTO OUTFILE will incur disk I/O and still impose some caching along the way.

With INSERT INTO SELECT, the shared locks would probably have to live longer in InnoDB because you are locking rows and using those same rows to INSERT into another table.

Therefore, if I was looking for a performance bonus, I would have give the edge to INSERT INTO SELECT because you are doing the same amount of shared row locking, The disk I/O for the single operation would have to be less than the separate SELECT INTO OUTFILE and subsequent LOAD DATA INFILE. Of course, you would have to compare the two methods against your dataset. What could a performance bonus for one dataset might be a performance cost for another dataset.

UPDATE 2014-12-17 00:00 EST

Your Comment

  I wasn't notified that you updated your answer, so I did bounty assuming it wasn't. Your explanation does make sense, in theory, however I am looking for a more authoritative response, in hopes that the overhead of the separate file (as you correctly mentioned) may be worth the trade-off in complexity for improved performance.

The only authoritative response would to come from the MySQL Documentation.

First, what does the MySQL Documentation LOAD DATA INFILE say ?

  The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See Section 13.2.9.1, “SELECT ... INTO Syntax”.) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE.

Two paragraphs later, it says

  For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see Section 8.2.2.1, “Speed of INSERT Statements”.

When you look at Speed of INSERT Statements, it says this:

  To optimize insert speed, combine many small operations into a single
  large operation. Ideally, you make a single connection, send the data
  for many new rows at once, and delay all index updates and consistency
  checking until the very end.

  The time required for inserting a row is determined by the following
  factors, where the numbers indicate approximate proportions:

  Connecting: (3)

  Sending query to server: (2)

  Parsing query: (2)

  Inserting row: (1 × size of row)

  Inserting indexes: (1 × number of indexes)

  Closing: (1)

  This does not take into consideration the initial overhead to open
  tables, which is done once for each concurrently running query.

  The size of the table slows down the insertion of indexes by log N,
  assuming B-tree indexes.

  You can use the following methods to speed up inserts:

  If you are inserting many rows from the same client at the same time,
  use INSERT statements with multiple VALUES lists to insert several
  rows at a time. This is considerably faster (many times faster in some
  cases) than using separate single-row INSERT statements. If you are
  adding data to a nonempty table, you can tune the
  bulk_insert_buffer_size variable to make data insertion even faster.
  See Section 5.1.4, “Server System Variables”.

  When loading a table from a text file, use LOAD DATA INFILE. This is
  usually 20 times faster than using INSERT statements. See Section
  13.2.6, “LOAD DATA INFILE Syntax”.

  Take advantage of the fact that columns have default values. Insert
  values explicitly only when the value to be inserted differs from the
  default. This reduces the parsing that MySQL must do and improves the
  insert speed.

  See Section 8.5.4, “Bulk Data Loading for InnoDB Tables” for tips
  specific to InnoDB tables.

  See Section 8.6.2, “Bulk Data Loading for MyISAM Tables” for tips
  specific to MyISAM tables.

Things start to look a little nebulous at this point because you must tune the load process in terms of the storage engine. MyISAM is rather straight forward in this statement because the bulk insert buffer is for MyISAM only and LOAD DATA INFILE will harness the bulk insert buffer. InnoDB will not.

Take a look at this Pictorial Representation of InnoDB (Percona CTO Vadim Tchachenko)

There are other considerations to tweak options but LOAD DATA INFILE will literally slam everything into the InnoDB Buffer Pool, funneling changes through the Log Buffer, Double Write Buffer, Insert Buffer (if the target table has nonunique indexes), Redo Logs (ib_logfile0,ib_logfile1), and the Physical File of the Table. This is where LOAD DATA INFILE's benefits have to get nullified.

I wrote about this

Feb 06, 2012 : LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?
Apr 20, 2012 : Why is 'LOAD DATA INFILE' faster than normal INSERT statements?
Jul 13, 2012 : Mysql load from infile stuck waiting on hard drive
Jan 11, 2013 : MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

EPILOGUE

As I already said in my previous update to this answer

  Therefore, if I was looking for a performance bonus, I would have give the edge to INSERT INTO SELECT because you are doing the same amount of shared row locking, The disk I/O for the single operation would have to be less than the separate SELECT INTO OUTFILE and subsequent LOAD DATA INFILE. Of course, you would have to compare the two methods against your dataset. What could a performance bonus for one dataset might be a performance cost for another dataset.

Basically, you will have to test SELECT INTO OUTFILE/LOAD DATA INFILE against INSERT INTO SELECT. It may be 6 of one, half a dozen of the other for one dataset, and a landside victory for another dataset.

All being said from the MySQL Docs and my past posts, I still give the edge to INSERT INTO SELECT. You will just have to test the two methods.


I would try making two composite partial indexes like

CREATE INDEX idx1 ON tw_schedules (scenario_id, type_well_id) WHERE type_well_id IS NOT NULL;

CREATE INDEX idx2 ON tw_schedules (scenario_id, tw_import_id) WHERE tw_import_id IS NOT NULL;

But, of course, everything depends on the selectivity of these indexes.


High availability sql server, secondary replicas can't be connected on the Availability group

The AG are using "Database Mirroring" Endpoint to communicate between all nodes.
Your SQL Service account need to have "connect" permission on the endpoint.
You also need to grant "ConnectSQL" to the other node service account (or the Server - AITS\PLSBDATEST2$-  if using local service account).

So to recap, on PLSBDATEST1, you will have a login for PLSBDATEST2$ and you will grant permission to the AG endpoint to it. on PLSBDATEST2, you will have a login for PLSBDATEST1$ with same permission.

That should do the job.


Clear SQL Server Buffer after SSIS is done?

Leaving it in cache will have no downsides since SQL Server does a great job managing the cache without interference.  In fact, it may be beneficial to keep some of those pages cached.

Are you attempting to solve some problem?


Merge row size overflow in SQL Server - "Cannot create a row of size.."

Why the second time I tried to merge the same row which already was inserted it resulted in an error. If this row exceeded maximum row size, it would expect for it not to be possible to insert it in the first place.

First, thank you for the reproduction script.

The problem is not that SQL Server cannot insert or update a particular user-visible  row. As you noted, a row that has already been inserted to a table certainly cannot be fundamentally too large for SQL Server to handle.

The problem occurs because the SQL Server MERGE implementation adds computed information (as extra columns) during intermediate steps in the execution plan. This extra information is needed for technical reasons, to keep track of whether each row should result in a insert, update, or delete; and also related to the way SQL Server generically avoids transient key violations during changes to indexes.

The SQL Server Storage Engine requires indexes to be unique (internally, including any hidden uniquifier) at all times - as each row is processed - rather than at the start and end of the complete transaction. In more complex MERGE scenarios, this requires a Split (converting an update to a separate delete and insert), Sort, and an optional Collapse (turning adjacent inserts and updates on the same key into an update). More information.

As an aside, note that the issue does not occur if the target table is a heap (drop the clustered index to see this). I am not recommending this as a fix, just mentioning it to highlight the connection between maintaining index uniqueness at all times (clustered in the present case), and the Split-Sort-Collapse.

In simple MERGE queries, with suitable unique indexes, and a straightforward relationship between source and target rows (typically matching using an ON clause that features all key columns), the query optimizer can simplify much of the generic logic away, resulting in comparatively simple plans that do not require a Split-Sort-Collapse, or Segment-Sequence Project to check that target rows are only touched once.

In complex MERGE queries, with more opaque logic, the optimizer is usually unable to apply these simplifications, exposing much more of the fundamentally complex logic required for correct processing (product bugs notwithstanding, and there have been plenty).

Your query certainly qualifies as complex. The ON clause does not match the index keys (and I understand why), and the 'source table' is a self-join involving a ranking window function (again, with reasons):

MERGE MERGE_REPRO_TARGET AS targetTable
USING
(
    SELECT * FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY ww,id, tenant
                ORDER BY
                (
                    SELECT COUNT(1)
                    FROM MERGE_REPRO_SOURCE AS targetTable
                    WHERE
                        targetTable.[ibi_bulk_id] = sourceTable.[ibi_bulk_id]
                        AND targetTable.[ibi_row_id] <> sourceTable.[ibi_row_id]
                        AND
                        (
                            (targetTable.[ww] = sourceTable.[ww])
                            AND (targetTable.[id] = sourceTable.[id])
                            AND (targetTable.[tenant] = sourceTable.[tenant])
                        )
                        AND NOT ((targetTable.[sampletime] <= sourceTable.[sampletime]))
                ),
                sourceTable.ibi_row_id DESC
            ) AS idx
        FROM MERGE_REPRO_SOURCE sourceTable
        WHERE [ibi_bulk_id] in (20150803110418887)
    ) AS bulkData
    where idx = 1
) AS sourceTable
ON
    (targetTable.[ww] = sourceTable.[ww])
    AND (targetTable.[id] = sourceTable.[id])
    AND (targetTable.[tenant] = sourceTable.[tenant])
...

This results in many extra computed columns, primarily associated with the Split and the data needed when an update is converted to an insert/update pair. These extra columns result in an intermediate row exceeding the allowed 8060 bytes at an earlier Sort - the one just after a Filter:

Note that the Filter has 1,319 columns (expressions and base columns) in its Output List. Attaching a debugger shows the call stack at the point the fatal exception is raised:

Note in passing that the problem is not at the Spool - the exception there is converted to a warning about the potential for a row to be too large.

  Why does update using merge is not succeeding, while insert does, and direct update also does?

A direct update does not have the same internal complexity as the MERGE. It is a fundamentally simpler operation that tends to simplify and optimizer better. Removing the NOT MATCHED clause may also remove enough of the complexity such that the error is not generated in some cases. That does not happen with the repro, however.

Ultimately, my advice is to avoid MERGE for larger or more complex tasks. My experience is that separate insert/update/delete statements tend to optimize better, are simpler to understand, and also often perform better overall, compared with MERGE.


How to get transaction command text from SQL server Log file

This isn't logged anywhere (by default). You can investigate the "server audit" functionality and see if that gives you what you want.


Utilizing a single core to 100% doesn't show in perf mon as single cpu being fully utilized

1: The scheduler is moving the load between cores. This is a known behavior.

2: Because the processor affinity is actually a core affinity and you can not move a task away from a core if that is the only core allowed to run it.

3: Not really. Things get a lot more complex because of parallelization anyway. In general, SQL Server - unless you do very complex analysis - is a lot more memory / IO bound than CPU bound. Now, with SSD you can actually overcome this - but mostly I dont see that.

You can find that a query uses a lot of CPU - which is always something nice to optimize - but not necessarily whether it is single core bound.

If you need that, maybe an instance configured to use only one core may work. I mostly find that to be of limited use - because in large scenarios, as I said, you either are not CPU bound or with paralellism not single core bound.


Extract mdf & ldf files from backup file?

ApexSQL has recently released a tool that can do this. It's ApexSQL Restore. I haven't tested it fully yet, but you have to connect to a SQL Server instance to do this. It attaches database backup file and creates MDF and LDF files for the database.


How do I solve this problem of a correlated subquery

Your query should be like:

SELECT FIRST_NAME, MIN_EMP_SALARY FROM
(
    SELECT  FIRST_NAME , (SELECT MIN(SALARY)
                         FROM EMPLOYEES EMP
                         WHERE E.EMPLOYEE_ID = EMP.EMPLOYEE_ID
                         GROUP BY EMPLOYEE_ID) AS MIN_EMP_SALARY
    FROM  EMPLOYEES E
    GROUP BY FIRST_NAME , EMPLOYEE_ID
) OUTER_TABLE WHERE MIN_EMP_SALARY >= 6000
ORDER BY MIN_EMP_SALARY DESC


Special Characters in MySQL using UTF8 Unicode Collation and LOAD DATA INFILE

I figured that I would answer this now that I found the solution. Because I am using Java to run the LOAD DATA INFILE via JDBC the JDBC driver seems to be checking the collation at the DB and not the actual table being loaded as it is parsing the file. So you can't have the DB set to UTF-8 and have a Latin collated table as you would be able to do with an INSERT statement. I had tried to set the Table collation as Latin and even had the field in question Latin, but until I changed the entire DB to Latin it was failing. The CSV files are large so checking every char in question is not easy, but I was catching the Exceptions in Java and was able to determine that the error was generated by the JDBC driver and was complaining that "Character at line xx is not a UTF-8 character" Running in Debug allowed me to see more details.

I then concluded it must not be looking at the Latin collated table it would be filling, but was looking at the DB which was still set to UTF-8. Changing the DB to Latin was all I needed to do.

I want to thank @Rick James for his help, I hope this will help others in the future.

Pat


Skipping some data from Indexing in SQL Server

I wrote a post about this kind of situation here, which goes into more depth than this answer.

  is there any way to say in index definition to include only first 800 characters from the column?

This can be done by creating a computed column as LEFT(MyColumn, 800), and then indexing that column. Note that the column doesn't have to be materialized in the table using the PERSISTED clause -- creating an index on the column will materialize the values into only the index. Use caution with the computed column approach, though, because it may not behave as you might expect in some cases (issues with computed columns in general, not specific to this situation).

This approach allows you to do both exact matching and LIKE 'xyz%' directly as long as the search term is shorter than the indexed length.

  You can use CHECKSUM to generate a small hash

The result of the CHECKSUM function may be small, but it's not a very good hash function. Use HASHBYTES or a CLR function instead.

Same thing applies as above regarding computed columns. Or, this value is small enough (16 bytes) that it can be materialized in the table using a trigger or application code and indexed from there.

The downsides are that this method only allows for exact matching, because there's no way to match on a partial input, and of course the change management required to add a new non-NULL column to an existing table.

  read queries are still querying on original column.

You'll need to change how queries access the table to make one of these solutions work, so if that's not possible, you're out of luck.

Because we're talking about lossy compression (hashing or truncation), queries always have to also compare the original value against the search term, unless the index is made unique, which may open you up to users reporting weird errors (first 800 characters are identical, or an unlikely hash collision). If the index isn't unique and the original values aren't compared, incorrect results could be returned (note: this could be a security hole).

The additional comparison will likely effect the query plans, and I'm not getting into that without a sample query, but you can find some small examples in the blog post I linked to above. Using your existing table schema and queries, you should be able to make an educated guess about what's going to happen to the plans.


See if this helps.  I would take a backup right away because if vacuum cleans out the deleted row it's gone and you won't have any chance to recover it.

Recover Deleted Row


with bd as (
    select count(*) as pages_in_memory, bd.allocation_unit_id
    from sys.dm_os_buffer_descriptors bd
    where bd.database_id = db_id()
    group by bd.allocation_unit_id)
select p.object_id,
    p.index_id,
    p.partition_number,
    bd.pages_in_memory,
    au.total_pages as pages_on_disk,
    au.type_desc
from bd
join sys.allocation_units au
    on au.allocation_unit_id = bd.allocation_unit_id
join sys.partitions p
    on p.partition_id = au.container_id


to me the most important thing in this context is to use transactions. PostgreSQL supports transactional DDLs and people should make heavy use of it.

the rule of thumb is: COMMIT stuff to the real system as late in the process as possible. this avoids the need to rollback stuff and to produce conflicts of all kinds. so transactions are really the key to making things work in a reasonable way. deployment should always happen in a transactional way as well.

one thing is also to use includes:

BEGIN;
\i module_1.sql
\i module_2.sql ...

it allows people to work on various files concurrently. it makes using git and so on a lot easier.

also, consider looking at CREATE EXTENSION. it allows a pretty nice way to handle versions and all that. i strongly encourage to use that one as well. git is also a good thing to have in general.


creating a report on the mysql jasperReport server

You must first create a report using iReport (the report designer) i don't remember if it comes included in the Jasper Server, but if it doesn't you can download it from Jasper's web site; after that using the same iReport you must deploy the report to your Jasper Server. If you need more guidance on deploying a report you can look here: http://randomallsorts.blogspot.mx/2009/12/jasper-reports-how-to-deploy-report-to.html


Transform 14 columns into 2 (SQL Server 2008) - Pivot/Cursor not helping

If I figured out correctly what you're doing, you could also do it using unpivot, something like this with the separate columns in 2 CTEs and join them side by side using number for row_number:

;with D1 as (
select row_number() over (order by (select null)) as RN, p8_14
from (
  select prodt_8, prodt_9, prodt_10
  from test
) p unpivot (
    p8_14  for colname1 in (prodt_8,  prodt_9,  prodt_10)
) as up),

D2 as (
select row_number() over (order by (select null)) as RN, p15_17
from (
  select prodt_15, prodt_16, prodt_17
  from test
) p unpivot (
    p15_17  for colname1 in (prodt_15, prodt_16,  prodt_17)
) as up)

select p8_14, p15_17 from D1 join D2 on D1.RN = D2.RN

That's just couple of the columns, made also SQL Fiddle: http://sqlfiddle.com/#!6/33d41/1


This guide works very well:

https://gist.github.com/ibraheem4/ce5ccd3e4d7a65589ce84f2a3b7c23a3

It's necessary to create db and role manually since they aren't exist.


Is it possible to migrate data from a shard MongoDB to a non-shard MongoDB with dump

It's definitely possible to mongodump from a sharded cluster and mongorestore to another deployment (standalone, replica set, or sharded cluster).

With this approach there are some general considerations to be aware of:

You should always mongodump data via mongos for a sharded cluster.
mongodump needs to read all requested data through the memory of the mongod process(es), so this can have a significant resource and performance impact for the deployment being backed up (particularly if the uncompressed data set is much larger than available RAM).
mongorestore will rebuild all indexes for collections being restored, which can have a significant performance impact on the target cluster. If you are using a version of MongoDB server older than 4.2, the default foreground index builds will block other reads & writes to the destination database. MongoDB 4.2 has less impactful index build process (see: Index Builds on Populated Collections).
mongodump does not take a point-in-time backup, so if the source cluster is being actively updated your backup may not be consistent.
If your deployment is using MongoDB 4.2 or newer and distributed transactions, mongodump/mongorestore should not be used (the lack of point-in-time consistency is particularly problematic in this case).

Your original approach of writing a script to migrate the data would also be viable if you iterate rather than trying to store the whole result set in RAM. You could make this approach resumable by scanning a collection in a predictable order (such as sorting by _id, assuming this is monotonically increasing if new documents continue to be inserted).

Unless you have very custom requirements for data export or throttling, I would be inclined to use mongodump and mongorestore  with appropriate options for compression and concurrency (for example parallel collections to dump and number of insertion workers per collection).


ERROR - Function 'innodb' already exists

The plugins throwing errors in the error.log were not specifically specified in my.cnf. So I ran...

select * from mysql.plugin

for MySQL 5.6.17 I deleted the following rows in mysql.plugin

innodb ha_innodb.so
federated ha_federated.so
blackhole ha_blackhole.so
archive ha_archive.so

Do not delete anything you are not sure about. The above worked for me as...

I do not use Federated ( accesses data in tables of remote databases )
INNODB is built into 5.6, no plugin required
I do not use Blackhole ( accepts data but throws it away and does not store it )
I do not use Archive ( storing large amounts of data without indexes in a very small footprint )


You can do this with a normal view, as long as the users involved haven't already got access to the base table.

EG:

SQL> create user reportuser identified by reportuser;

User created.

SQL> grant create session to reportuser;

Grant succeeded.

SQL> grant create synonym to reportuser;

Grant succeeded.

SQL> select user from dual;

USER
------------------------------
PHIL

SQL> create table basetable
(
  id number primary key,
  viewable varchar2(30),
  secret varchar2(30)
);

Table created.

SQL> insert into basetable values ( 1, 'hello world','this is secret' );

1 row created.

SQL> commit;

Commit complete.

SQL> create view reportview
as
select id, viewable
from basetable;

View created.

SQL> grant select on reportview to reportuser;

Grant succeeded.

SQL> conn reportuser/reportuser
Connected.
SQL> select * from phil.basetable;
select * from phil.basetable
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select secret from phil.basetable;
select secret from phil.basetable
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from phil.reportview;

        ID VIEWABLE
---------- ------------------------------
         1 hello world

SQL>

If you revoke permissions on the tables in question & create the views, along with a synonym for each users view that has the same name as the original table, it should be transparent.

EG:

SQL> select user from dual;

USER
------------------------------
REPORTUSER

SQL> create synonym basetable for phil.reportview;

Synonym created.

SQL> select * from basetable;

        ID VIEWABLE
---------- ------------------------------
         1 hello world

SQL>

You can also do this with Virtual Private Database, but I think it's an expensive extra licensed option. You use DBMS_RLS to configure the relevant security policies that you require.


Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant?

I don't believe that any version of any database platform on earth is fully compliant with any version of the standard. This is probably why you're not finding any claims of 100% compliance...

You can find some promising starting points over on Stack Overflow in the following Q & A: Database Engines and ANSI SQL Compliance

Many references linked from there are not maintained because database platforms are evolving all the time and this would be many full-time jobs to stay on top of it all. Basically:

no database platform is 100% compliant, but several come close
platforms have proprietary additions on top of the standard, so your definition of "fully compliant" may vary from someone else's


Uncommitable T-SQL transaction after trigger applied to table

I definitely would not say that only 1 row is ever passed into the trigger.  Triggers handle every row updated in a single statement.  (I see Jon Seigel has made the same point.)

The problem is that inside a trigger an error can occur that results in a state where the transaction cannot be committed.  Your main issue will be determining why the transaction became uncommittable.

See http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

In part it says:

  Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.

So, your TRY/CATCH and testing against XACT_STATE is correct.  It is just too late to overcome whatever already happened to produce a "doomed" transaction.

Balmukund has a blog post on this topic below.  He points out that he cannot define every condition that might cause a doomed transaction, but he offers some hints:

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/11/errors-raised-with-severity-level-16-may-cause-transactions-into-doomed-state.aspx

  As per our intensive research we asserted that XACT_STATE changes is not governed by a single rule and depends on the transaction state (user or system initiated and if an active transaction or not) and the type of error being raised. For example, transactions having CONVERT\CAST errors and DDL operations (ALTER TABLE\DATABASE\INDEX etc.) on failure will make the state of the transaction as -1 and will terminate the transaction.

  One more example which could get your transaction into doomed state is when you attempt to convert a value that is incompatible with target data type.


SQL Server FCI installation with Mount Point permissions issues

You may need to set permissions on the mount point volume instead of at a folder level.  I suspect this is the issue you're running into.

From Guidelines for Setting SQL Permissions on Mount Point Folders (highlighting is Microsoft's):

  Gotchas

  Unfortunately, it is still possible to set/view permissions on the
  mount-point root folder via Windows Explorer, which can lead to
  unexpected results because the permissions of the mount-point root
  folder may seem valid and you can see “proper” inherited permissions,
  however these are not the permissions applied to the mounted volume.

  https://connect.microsoft.com/SQLServer/feedback/details/569895/installation-of-sql-server-2008-r2-on-mount-points-fails

  Guidelines

  It is recommended that you do not place any files directly in the mount-point root folder. This will make permissions management much
  simpler, because the tendency is to always check the folder
  permissions, which in this case is misleading. Instead, create a
  subfolder under the mount-point root folder, and set the proper
  permissions to that subfolder.  Since the subfolder is a regular
  folder, the folder permissions you observe and set are indeed the
  permissions being applied. So using the previous example, you would
  want to create a new folder: D:\FolderForVol3**SubfolderXYZ**. Now,
  set your folder permissions against that new SubfolderXYZ folder
  as you normally would.
  If you absolutely must place items directly in the mount-point root folder (Not the recommend approach), then you will need to set
  volume permissions, not folder permissions.  Recall, that this is
  because the mount-point root folder permissions are not the
  permissions which will actually get set on the mounted volume (because
  the mount-point root folder is not a real folder). You can set volume
  permissions as follows:

  Start->run->diskmgmt.msc (View the properties of a volume  http://technet.microsoft.com/en-us/library/cc740097.aspx )
  Select the volume->Properties->Security tab

  If you are adding a new folder for SQL to use, be aware of the required permissions for SQL access:

  Beginning with SQL Server 2012 permissions are assigned to the per-service SID for each of its services.
  http://msdn.microsoft.com/en-us/library/jj219062.aspx
  Configure Windows Service Accounts and Permissions  http://msdn.microsoft.com/en-us/library/ms143504.aspx

The way I had to solve this was by using the cacls.exe utility.  Detailed instructions for it can be found here.  I suspect your command to grant full permission to a user would be as follows:

cacls E:\MSSQL\Log\L1 /M /E /G YourUserName:F
cacls E:\MSSQL\Data\Tempdb1 /M /E /G YourUserName:F

You'll want to run this for your account and the SQL Server Service Accounts or group(s) as well.

Final note here, it is imperative that you include the /E flag, otherwise it will overwrite permissions on that path.  This behavior carries additional side effects of ulcers and heavy drinking, so make sure you don't forget the edit flag when running this command.

Another option is to delete the drives and repartition them from scratch.  They're still likely messed up from the original attempt at setting up security via Windows Explorer (e.g. check out the Gotcha clause from the first MS article).

In this case if you still want to write to the root folder on the mount path, you'll want to use the cacls utility.  I would also suggest setting up three sub folders on E:\ such as Data, Logs, and TempDB and then install everything to the sub folders.  Don't nest the mount points into a directory you'll install files to.


Model multiple 1-m relationship to a single entity

To sum up your added comment: Preferred RDBMS: Postgres 9.6, enforce referential integrity even under concurrent write load, at most 20 different sources, 500k rows, keep it simple.

Referential integrity is the hard part. The one rock solid way is with foreign key constraints. But you want to refer to one of multiple tables. From the same column?

How to have a foreign key pointing to two primary keys?

Short answer: not possible.

That leaves no other choice than either have one column for each source (your approach 1) or one table (your approach 2). Tables would make sense for a n:m relationship, not so much for 1:n. Your approach 2 is too complex, wastes too much storage, and it does not even match your data model to begin with.

Leaves us with approach 1. You worry:

  the problem is they are mutually exclusive. Which means there is only one column with actual value at any point in time and the rest of the columns are all null.

mutual exclusive - enforce that with a simple CHECK constraint. Standard SQL, a bit verbose but fast.
NULL storage - is extremely cheap in Postgres. Basically, 1 bit in the NULL bitmap. Details:

Do nullable columns occupy additional space in PostgreSQL?

So I would do this (Postgres implementation):

CREATE TABLE box (
   box_id      serial PRIMARY KEY
 , box_content text -- NOT NULL?
 , from_user   int  REFERENCES users(user_id)
 , from_office int  REFERENCES office(office_id)
 , from_import int  REFERENCES import(import_id)
  -- more?
 , CONSTRAINT exactly_one_from
   CHECK (CASE WHEN from_user   IS NULL THEN 0 ELSE 1 END
        + CASE WHEN from_office IS NULL THEN 0 ELSE 1 END
        + CASE WHEN from_import IS NULL THEN 0 ELSE 1 END = 1)
);

The CHECK constraints enforces exactly 1 reference, the FOREIGN KEY constraints enforce referential integrity under all circumstances.

If the many NULL columns are a burden to the human eye, consider a VIEW like:

CREATE VIEW box_view AS
SELECT box_id, box_content
     , COALESCE( from_user, from_office, from_import) AS from_id
     , CASE WHEN from_user   IS NOT NULL THEN 'user'
            WHEN from_office IS NOT NULL THEN 'office'
            WHEN from_import IS NOT NULL THEN 'import' END AS from_type
FROM   box;

Again, verbose but fast.

db<>fiddle here

There are many other possible approaches:

A document type like hstore or jsonb.
Multicolumn FK contraints like here:

Enforcing constraints "two tables away"

Enforce referential integrity with trigger instead of FK constraint.

But none of these guarantee referential integrity under concurrent write load like this approach does. Good performance and the smallest disk footprint are additional pros.
The downside: you have to alter the table definition (and view, queries, ...) for every additional "from" type.


What permissions does HAS_DBACCESS check?

HAS_DBACCESS returns 1 if:

the current Login / User has the ability to access the DB (whether through an Instance-level permission, or by having a User in that DB that is mapped to the current Login)
the DB is not OFFLINE or SUSPECT
the DB is in

MULTI_USER mode, or
SINGLE_USER mode and the current Login / User is that single user
RESTRICTED_USER mode and the current Login / User is either a member of the dbcreator or sysadmin fixed server roles, or is a member of the db_owner fixed database role

As far as my testing (with a low-privileged Login / User) shows, this appears to be equivalent to the following:

SELECT HAS_PERMS_BY_NAME('{database_name}', 'DATABASE', 'ANY');

I tried other permissions besides ANY (i.e. AUTHENTICATE and CONNECT) and they did not always match HAS_DBACCESS.


Why does the log file grow larger than data file for an availability group database on the secondary replica?

Since the log file is only growing on the secondary replica, something is preventing transaction log truncation on that database (but not on the primary).
You'll need to check the value of log_reuse_wait_desc in sys.databases on the secondary replica to see why the transaction log isn't being cleared there.

Since log_reuse_wait_desc is currently showing "NOTHING" you should check on the health of the AG itself.  Open up the AG dashboard (in SSMS, under "Always on High Availability" right-click the AG and choose "Show Dashboard).  Check to see if there are any errors or warnings, and confirm that the secondary replica is listed and is healthy / synchronized.
Additional, check out the SQL Server error log on the secondary to see if there are any errors or warnings related to the AG or the database in question.  This is a weird one!


Export SQL Agent jobs with steps and scripts

Would something like this work?

USE [msdb]
GO
SELECT
    j.Name
    , j.[Description]
    , js.Step_ID
    , js.Step_Name
    , js.Database_Name
    , js.Command
    --, j.*, js.*
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id
--WHERE j.enabled = 1
ORDER BY j.name, js.Step_ID


Data migration: copy data with insert select and fill unique column

Why not use row_number()

insert into bar (txt, n)
select txt, row_number() over ()
from foo;

I left out an order by which would slow down the query and wouldn't really be an advantage in your case as you just want a unique number.

An alternative would be to create a sequence for it, if the performance of the window function isn't sufficient.

create sequence seq_foo_nr;

insert into bar (txt, n)
select txt, nextval('seq_foo_nr')
from foo;

drop sequence seq_foo_nr;

If you need an auto-generated number anyway, then define nr as an identity column:

create table bar (
  txt text,
  n integer not null unique generated always as identity
);

insert into bar (txt)
select txt
from foo;


According to the documentation, AUTOCOMMIT should be turned off in InnoDB.

When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert.
When doing bulk inserts into tables with auto-increment columns, set innodb_autoinc_lock_mode to 2 instead of the default value 1


Design for storing data only applicable to a small subset of records

To be truly flexible, you could go the Entity-Attribute-Value route, but that would probably be overkill for this and there are many who would argue that EAV is a terrible anti-pattern.

I think the client-specific tables might be the better way to go if you think you will eventually end up with dozens and dozens of client-specific columns in a single table.


Out of Memory Exception When Creating BACPAC File

Unfortunately, you can't use .back failes in Azure SQL Database (not Managed Instance). I'd sugesst that you should consider to use BCP which is export to local csv or binary files and can import there to Azure SQL Database.


How to mirror the privileges of an user to another in Oracle Database?

Get all privileges from AAA

 SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','AAA') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','AAA') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','AAA') FROM DUAL;

Change the DDL commands with the user 'BBB' and execute.


How do I restore a SQL backup, while excluding a specific table?

You don't.  You restore the backup as a new DB, drop the unwanted table from it, then take a new backup of this working DB.


How to enhance the SQL query performance for thousands and millions of huge data

Possible this be helpful for you -

;WITH thread AS
(
    SELECT
          th.ThreadID
        , th.CreateTime
        , th.ThreadTitle
        , th.ThreadUrl
    FROM dbo.Thread th
    WHERE th.LanguageID = 58
        AND th.TechnologyID = ''
        AND th.BigintTime BETWEEN 20130101 AND 20130501
        AND th.IsAnswer = 1
)
SELECT
      t1.ThreadID
    , t1.CreateTime
    , t1.MaxAnswerDate
    , t1.MinAnswerDate
    , p1.MaxReplyDate
    , p1.Replies
    , p1.Users
FROM
(
    SELECT
          th.ThreadID
        , th.CreateTime
        , MaxAnswerDate = MAX(p.CreateTime)
        , MinAnswerDate = MIN(p.CreateTime)
    FROM thread th
    JOIN dbo.TopConcernTest tt ON tt.ThreadID = th.ThreadID
    JOIN dbo.Post p ON p.ThreadID = th.ThreadID
    WHERE tt.[Type] = 1
        AND p.IsAnswer = 1
    GROUP BY
          th.ThreadID
        , th.CreateTime
)t1
CROSS APPLY (
    SELECT
          MaxReplyDate = MAX(p.CreateTime)
        , Replies = COUNT(p.PostID)
        , Users = COUNT(DISTINCT p.AuthorID)
    FROM dbo.Post p
    WHERE p.IsAnswer != 1
        AND t1.ThreadID = p.ThreadID
        AND p.CreateTime > t1.MinAnswerDate
)p1
JOIN (
    SELECT
          TopConcerID = tt.ID
        , th.ThreadID
        , th.ThreadTitle
        , th.ThreadUrl
        , p.PostID
        , p.AuthorID
        , PostCreateTime = p.CreateTime
        , tt.Entities
        , tt.NegativeWords
        , tt.Verbatim
    FROM thread th
    JOIN TopConcernTest tt ON tt.ThreadID = th.ThreadID
    JOIN Post p ON tt.PostID = p.PostID AND p.ThreadID = th.ThreadID
    WHERE tt.[Type] = 1
)t3 ON t2.ThreadID = p2.ThreadID AND p2.PostCreateTime > t2.MinAnswerDate
WHERE p1.MaxReplyDate IS NOT NULL
    AND t3.TopConcerID BETWEEN 1 AND 20


Simple query with JOIN on 60M rows using index takes forever in MariaDB/MySQL

Adding an index to data_sources(source_id, log_time) solved the problem.

create index index_data_sources_source_id_log_time on data_sources(source_id, log_time);

This brought the execution time down to a couple of seconds.

Apparently the calculation of interval sources.retention_days day < current_timestamp doesn't have such a massive performance impact, as I suspected.

Thanks for the helpful comments!


SQL Server Backup and Restore Incrementally

From the way I see it, you have two pretty good options here:

Replication
Log Shipping

If you setup transactional replication you could have your data modifications replicated to a subscriber database (or databases).

With log shipping it essentially would be transferring transaction log backups (note, you'd need to have your database in the full recovery model to take transaction log backups).  Then you could have your secondary database in standby mode, allowing for read-only traffic.

The way I would choose between these two options is the level in which you want reporting and data movement to your secondary server.  Are you reporting on the entire database?  In that case, I'd opt for log shipping.  But if you're primary reporting on a single table or just a handful of tables and you don't necessarily want the entire database's footprint living on the secondary server, then replication would be a better option.

EDIT

As per your comments below, it looks like you don't want to transmit DELETE operations to your reporting server.  In this case, Log Shipping wouldn't meet the requirements because all logged operations would be on the reporting server, including your DELETEs.

What you can do with replication is set it up so that it only replicates INSERT and UPDATE operations.

See the below screenshot.  Basically when you create your publication, you can set many properties for each article.  One of the options is what action to take for each operation:  INSERT, UPDATE, and DELETE.  All you need to do is specify to not replicate DELETE operations:


"copy to tmp table" for 24 hours, then timeout; when ALTERing to add PK & AI

Well, there's nothing to be done at this stage. You can't terminate the process (rather, you shouldn't) while running. You cannot modify major memory settings without restarting MySQL.

I, for one, am not enthusiast about loading all table data then loading indexes. However, doing this for the PRIMARY KEY is plain wrong. The advice you read related to secondary indexes. With adding a PRIMARY KEY InnoDB will rebuild your entire table all over again; so I'm afraid your entire first load step was redundant.

Nevertheless 7 hours for PK compared to 30 seconds for secondary index does not make sense. What is your innodb_buffer_pool_size? Which disks? RAID? Which file system?


SQL SERVER – Find Most Expensive Queries

No, the DMVs track aggregates, not individual executions, so you can't pinpoint things like that.

Want to drill in, you'll need to capture queries using server-side trace*, extended events, management data warehouse or one of many 3rd party monitoring tools. The latter cost money but, IMHO, you get what you pay for. We have one I could recommend...

* Note: trace and profiler are deprecated, so it is highly recommended to use extended events going forward.


Need an efficient routine to pass object name, with or without "[" and "]" brackets, to get ObjectID

Some notes:

You can get rid of PARSENAME and get the object name from your existing sys.objects query since you are using the OBJECT_ID() built-in function to handle getting the object regardless of how it is named.
Getting rid of PARSENAME means that you won't be handling Database names, but the comment at the top of your original code in the question does specify that you are only concerned about checking "within the current database".
You can then JOIN to sys.schemas to get the Schema name at the same time.
It is very easy to concatenate the SchemaName and ObjectName together, so leave them separate so that you can deal with the pieces if needed.
You do not need / want an IF block to check for Types since that ignores an edge case. Objects and Types are in two separate tables so there is no uniqueness enforced for names between them. Meaning, any schema-bound object residing in sys.objects can have the same name as a type in sys.types. You need to handle the case where an object and a type both have the same schema and name.
Schema names and Object / Type names should be wrapped in square brackets via the QUOTENAME built-in function.
There are four different types of Types: System, User-Defined Data Types (UDDT), SQLCLR User-Defined Types (UDT), and User-Defined Table Types (UDTT). You will probably need to distinguish between these as they aren't always handled the same way.
Always use NVARCHAR for names of anything in the system: object, schema, column, type, index, job, assembly, queue, database, login, server, etc, etc. Never use VARCHAR. Most names are defined as being the sysname type, which is just an alias (that lives in master but is available everywhere, similar to system stored procedures) for NVARCHAR(128).

When dealing with a single-part name, use sysname (there are a few instances where sysname isn't being used, but this should generally always work since most names never get that large anyway). When dealing with multi-part names, you need to increase the size to account for each part plus the square brackets plus the connecting period / dot. Here we are handling SchemaName + ObjectName, so that is 128 for each "name" + 4 square brackets + 1 dot between them = 261 max characters. If you needed to also account for Database names, that would be another 131 characters (128 + 2 + 1).

The reasons for using NVARCHAR instead of VARCHAR are:

At the most basic level, this should be done for datatype consistency in your code. Since names are all internally NVARCHAR, using VARCHAR causes explicit conversions everywhere you those parameters / variables / columns (if storing names in tables).
Since all of the names in the system are internally stored as NVARCHAR, it is technically ok to use any Unicode characters in any name, including combining characters, supplementary characters, etc. Yes, there is a note that supplementary characters don't work in Database names, but they actually ;-) (there are just some nuances to deal with in that case). So, if you use VARCHAR for parameters / variables, you could be a mangled version of the name and not the name itself, which will produce odd behavior.

The Inline Table-Valued Function (ITVF) below encapsulates all of the above suggestions. The system Types can be further broken down into base types (INT, VARCHAR, etc) and CLR types (HierarchyID, Geometry, and Geography) if need be, but here they are all just system "Types".

Inline-TVF

CREATE FUNCTION dbo.GetObjectInfo (@Name NVARCHAR(261))
RETURNS TABLE
--WITH SCHEMABINDING -- cannot schema bind due to referencing system objects
AS RETURN
SELECT obj.[object_id],
       QUOTENAME(sch.[name]) AS [SchemaName],
       QUOTENAME(obj.[name]) AS [ObjectName],
       obj.[type]
FROM   sys.all_objects obj WITH (NOLOCK)
INNER JOIN sys.schemas sch WITH (NOLOCK)
        ON sch.[schema_id] = obj.[schema_id]
WHERE  obj.[object_id] = OBJECT_ID(@Name)
UNION ALL
SELECT typ.[user_type_id],
       QUOTENAME(sch.[name]) AS [SchemaName],
       QUOTENAME(typ.[name]) AS [ObjectName],
       CASE typ.[is_user_defined]
         WHEN 1 THEN CASE
                       WHEN typ.[is_assembly_type] = 1 THEN 'UDT'
                       WHEN typ.[is_table_type] = 1 THEN 'UDTT'
                       ELSE 'UDDT'
                     END
         ELSE 'Type'
       END AS [type]
FROM   sys.types typ WITH (NOLOCK)
INNER JOIN sys.schemas sch WITH (NOLOCK)
        ON sch.[schema_id] = typ.[schema_id]
WHERE  typ.[user_type_id] = TYPE_ID(@Name);
GO

Tests

USE [tempdb];
CREATE TABLE dbo.[user] (Col1 INT);
CREATE TYPE dbo.[user] FROM INT NOT NULL;
CREATE TYPE dbo.[table] AS TABLE (Col1 INT);

SELECT * FROM dbo.GetObjectInfo(N'dbo.user'); -- returns 2 rows
SELECT * FROM dbo.GetObjectInfo(N'[dbo].[user]'); -- returns 2 rows

SELECT * FROM dbo.GetObjectInfo(N'dbo.table'); -- returns 1 row
SELECT * FROM dbo.GetObjectInfo(N'[dbo].[table]'); -- returns 1 row


What is the latest version of SQL Server native client that is compatible with Sql Server 2008 r2

Microsoft SQL Server Native Client 11.0 is the latest and is backward compatible with 2008 and 2008R2 (as of July 9 2019, both windows and sql server 2008 and 2008r2 are fully unsupported).

BOL warns you :

  The SQL Server Native Client (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.


Some points

As @swasheck mentioned, you can't have a condition like WHERE a = b = c in SQL, it's not valid (unlike other languages). You need to make it
 WHERE a = b AND b = c
Using implict joins with WHERE is not good practise any more, 25 years since SQL-92 standards adopted the JOIN syntax (a JOIN b ON <condition>), which has several advantages and should be preferred. One reason is that there are several types of joins available (and all queries except those that use only INNER joins are hard to write using the WHERE syntax):

INNER JOIN   or just JOIN
this is the most common type of join, combines rows from the two joined tables when they match the ON  condition.
LEFT OUTER JOIN  or just LEFT JOIN
very common, too: gets all combinations of INNER JOIN plus all unmatched rows of the left table.
RIGHT OUTER JOIN or just RIGHT JOIN
(not so common) the reverse of LEFT join: gets all combinations of INNER JOIN plus all unmatched rows of the right table.
FULL OUTER JOIN  or just FULL JOIN
this is LEFT and RIGHT join, combined.
CROSS JOIN
NATURAL JOIN   and variations (not supported by SQL-Server)

There are many references and tutorials on the Web about Joins. You can start with MSDN online documentation.
You also need to study how COUNT() works:

COUNT(*) counts the number of rows (of a group).
COUNT(column/expression) counts the number of rows (of a group) where the column or expression is not null. If the column cannot be NULL, this is the same as COUNT(*)
COUNT(DISTINCT column/expression) counts the number of distinct values of the column or expression (within a group).

And here are a few ways to write your query:

Option 1 - inline subqueries:

SELECT
    Person.Name,

    ( SELECT COUNT(*)
      FROM Book
      WHERE PersonID = Person.ID
    ) AS BookCount,

    ( SELECT COUNT(*)
      FROM Car
      WHERE PersonID = Person.ID
    ) AS CarCount
FROM
    Person ;

Option 2 - two LEFT Joins, then GROUP BY and use of COUNT(DISTINCT):

This is very similar to your approach, but has the implicit joins with WHERE turned into explicit joins.

The GROUP BY p.ID, p.Name was added, too, so the query can group rows per Person.

We have to use the COUNT(DISTINCT) in this version because the two joins may produce multiple rows per Person. (If a person has 2 Cars and 500 books, 1000 rows will be produced and then collapsed into 1 with the grouping. You can try with COUNT(*) there to see what (erroneous) results are produced.)

SELECT
    p.Name,
    COUNT(DISTINCT b.BookID)  AS BookCount,
    COUNT(DISTINCT c.CarID)  AS CarCount
FROM
    Person AS p
  LEFT JOIN
    Book AS b
        ON b.PersonID = p.ID
  LEFT JOIN
    Car AS c
        ON c.PersonID = p.ID
GROUP BY
    p.ID, p.Name ;

Option 3 (my preference) - two LEFT Joins to (derived) GROUP BY subqueries:

SELECT
    p.Name,
    COALESCE(BookCount, 0) AS BookCount,   --- using COALESCE() so the NULLs produced
    COALESCE(CarCount, 0)  AS CarCount     --- by the (LEFT) outer joins for persons
                                           --- that have no car or no book (shame!)
                                           --- are turned into 0
FROM
    Person AS p
  LEFT JOIN
    ( SELECT PersonID
           , COUNT(*) AS BookCount,
      FROM Book
      GROUP BY PersonID
    ) AS b
        ON b.PersonID = p.ID
  LEFT JOIN
    ( SELECT PersonID
           , COUNT(*) AS CarCount,
      FROM Car
      GROUP BY PersonID
    ) AS c
        ON c.PersonID = p.ID ;

Option 4 - In SQL Server, there is also the option of using OUTER APPLY to (derived) GROUP BY subqueries. This is similar to LEFT joins but has even more flexibility, which can be very useful in more complex cases. (in other DBMS like PostgreSQL and DB2, the same functionality exists as well, with LATERAL joins).
  Notice how the ON conditions from option 3 have been moved to WHERE, inside the outer apply subqueries:

SELECT
    p.Name,
    COALESCE(BookCount, 0) AS BookCount,   --- using COALESCE() so the NULLs produced
    COALESCE(CarCount, 0)  AS CarCount     --- by the (OUTER APLY) joins for persons
                                           --- that have no car or no book (shame!)
                                           --- are turned into 0
FROM
    Person AS p
  OUTER APPLY
    ( SELECT PersonID
           , COUNT(*) AS BookCount,
      FROM Book
      WHERE PersonID = p.ID
      GROUP BY PersonID
    ) AS b
  OUTER APPLY
    ( SELECT PersonID
           , COUNT(*) AS CarCount,
      FROM Car
      WHERE PersonID = p.ID
      GROUP BY PersonID
    ) AS c ;

All 4 queries will give same results - all Persons and the Count of their Books and Cars, even if they have no book or no car. If you want to show only Person that have at least one Book or at least one Car (or both), options 2, 3 and 4 can be easily modified: just change the respective LEFT OUTER JOIN (or both of them) to INNER JOIN - and the OUTER APPLY to CROSS APPLY.

If the problem involves calculating other aggregates, like MAX(), SUM(), etc., then the options are essentially the same - but note that option 2 can be used with MIN and MAX but not with SUM or AVG. Option 1 can be used with any aggregate function but needs a separate subquery for each aggregate, so if one needs many aggregates from the same table, options 3 and 4 are preferable.


Need help about giving relationships in database design

Try something like this

user (user_id(pk), user_name)

post (post_id(pk),user_id(fk), post_content)

comment(comment_id(pk),post_id(fk),user_id(fk), comment)

likes(post_id(fk), user_id(fk) status)

In status column in likes table you can save it as like or unlike can be tinyint field with

NULL - Default value
True - Like and False - unlike(if you have unlike option)


How do I go about creating a script to compare , update and match structure of two databases

SQL Server Data Tools comes with Schema Compare (and a lot more).

1) Import either DB to a Database Project.

2) Bi-direction Schema Compare to remaining DB. Import and Deploy changes as necessary.

3) Save Solution to Source Control and deploy the exact same DB to any number of servers.

At any stage you can either script out the operations or simply execute it from the UI. The script includes checks to prevent data loss or other unwanted issues, but it's best to review especially if you're new to the tool.


How to find out what operator class and access method should be used for an exclusion constraint?

PostgreSQL Indexes consist of two parts, from the docs on Interfacing Extensions To Indexes

Index Methods (Access Method), for those you're talking about brin, btree, gin, gist, hash, sp-gist (you can see this with \dA+ in psql.)
Operator Class

GIST is the Index Method. For why GIST or SP-GIST is required on EXCLUSION CONSTRAINTS see this post. The Operator Class you need is inet_ops. The problem you're encountering is for cidr and inet two GIST operator classes are provided, and the wrong one is the default,

the operator class gist_cidr_ops is the default. It's part of the "Additional Supplied Modules" and most distros package it as -contrib. It is provided by the btree_gist extension (source)
the operator class inet_ops is core, and not the default.

How would you know this? You probably wouldn't. As far as the access type, good luck. The docs themselves while distributed with PostgreSQL are sorely lacking here, though they do touch on this,

  For historical reasons, the inet_ops operator class is not the default class for types inet and cidr. To use it, mention the class name in CREATE INDEX, for example

CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);

The best you could do is submit a doc patch to btree_gist where this should probably also be documented.


Getting multiple out when using NOT EQUAL operator

To achieve this, I did the following:

I created two tables, Employee and Dependent - note that I only created the important fields - name and SSN - you can add the details for yourself.

CREATE TABLE `Employee`
(
  `LName` varchar(20) DEFAULT NULL,
  `SSN` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and

CREATE TABLE `Dependent` (
  `Name` varchar(10) DEFAULT NULL,
  `Essn` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Employee` VALUES ('Smith','12345-6789'),('Wong','33344-5555'),('Zelaya','99988-7777'),('Wallace','98765-4321'),('Mr-X','22222-3333');  -- For NULL values of Essn, see below.

and

INSERT INTO `Dependent` VALUES ('Alice','33344-5555'),('Theodore','33344-5555'),('Joy','33344-5555'),('Abner','98765-4321'),('Michael','12345-6789'),('Alice','12345-6789'),('Elizabeth','12345-6789');

You can see that Mr-X and Zelaya (employees) have no Essn in the Dependent tables - i.e. they have no dependents. Now for the SQL:

mysql> SELECT * FROM Employee e
WHERE e.SSN
NOT IN
(
  SELECT DISTINCT(Essn)
  FROM Dependent
);

Only works if the tables join on a NOT NULL field.

+--------+------------+
| LName  |       SSN  |
+--------+------------+
| Zelaya | 99988-7777 |
| Mr-X   | 22222-3333 |
+--------+------------+
2 rows in set (0.00 sec)
mysql>

For NULL SSN in the Employee table, use the following query, (as pointed out by @Paul White). I also added this line to test:

mysql> INSERT INTO Employee VALUES('EBorg', NULL);
Query OK, 1 row affected (0.05 sec)

SELECT * FROM Employee emp
WHERE emp.LName NOT IN
(
  SELECT DISTINCT(e.LName)
  FROM Employee e
  JOIN Dependent d
  ON e.SSN = d.Essn
);

Result:

+--------+------------+
| LName  |       SSN  |
+--------+------------+
| Zelaya | 99988-7777 |
| Mr-X   | 22222-3333 |
| EBorg  | NULL       |
+--------+------------+
3 rows in set (0.00 sec)

Initially, I assumed that everyone would have an SSN, however if they
don't, then the second query will work - I'm assuming no NULL LNames :-). It's very bad practice not to have a PRIMARY KEY which would mean that the simpler query would word.

Or, for NULLs, you could just a add a UNION of my first query and SSN IS NULL thus

SELECT * FROM Employee e
WHERE e.SSN
NOT IN
(
  SELECT DISTINCT(Essn)
  FROM Dependent
)
UNION
SELECT * FROM Employee e
WHERE e.SSN IS NULL;  -- same result as above.

Following more discussion with @PaulWhite, there are two more queries which will work providing the (correct 3 line) result shown above.

SELECT e.*
FROM Employee e
WHERE NOT Exists
(SELECT 1
 FROM Dependent d
 WHERE e.SSN = d.Essn
);

And (the best one!)

SELECT  e.*
FROM Employee e
LEFT JOIN
Dependent d
ON      e.SSN = d.Essn
WHERE   d.Essn IS NULL;


Getting the top count of records from a table for each month and user

Due to lack of windows functions and cte, mysql version is quite verbose, but I hope it will give you desired results in reasonable time (I know it's not fast at all):

SELECT a.*, c.*
FROM
(
   SELECT yr,mn, MAX(translations_per_month) as max_user_translations
   FROM
   (
     SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id,
     COUNT(1) as translations_per_month
     FROM `gp_translations` t1
     INNER JOIN gp_users u ON
      (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
     GROUP BY  YEAR(`date_added`), MONTH(`date_added`),user_id
    )b
    GROUP BY yr,mn
)a
INNER JOIN
(
   SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, display_name,
   COUNT(1) as translations_per_month
   FROM `gp_translations` t1
   INNER JOIN gp_users u ON (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
   GROUP BY YEAR(`date_added`), MONTH(`date_added`),user_id, display_name
)c ON  (c.yr = a.yr and c.mn = a.mn and c.translations_per_month = a.max_user_translations)

Also, I made a guess that status column is in gb_users table ; if not, the query needs to be modified a bit.


Automatic MySQL Sync from master, where Slave was offline for long period of time

The only DB I could think of would be Percona XtraDB Cluster (PXC)
It has two methods for copying an entire Cluster Node

SST (State Snapshot Transfer) is the full copy of data from one node to another. It’s used when a new node joins the cluster, it has to transfer data from existing node. There are three methods of SST available in Percona XtraDB Cluster: mysqldump, rsync and xtrabackup (Percona XtraBackup with support of XtraDB Cluster will be released soon, currently you need to use our source code repository). The downside of mysqldump and rsync is that your cluster becomes READ-ONLY while data is being copied from one node to another (SST applies FLUSH TABLES WITH READ LOCK command). Xtrabackup SST does not require READ LOCK for the entire syncing process, only for syncing .frm files (the same as with regular backup).
IST (Incremental State Transfer) : Functionality which instead of whole state snapshot can catch up with te group by receiving the missing writesets, but only if the writeset is still in the donor’s writeset cache.

I addressed this before

Oct 25, 2012 : mysql read slave to fetch entire database on start
Oct 03, 2012 : Does this make my XtraDB Cluster hang?

CAVEAT
This would be the ideal solution if the entire database is InnoDB. If you have read-only MyISAM tables, that would be OK provided you copy the MyISAM to every node manually. SST will do a full copy of MyISAM tables, but the MultiMaster Replication feature of PXC will not replication MyISAM data.
As for PXC, I rigorously tested it in Amazon EC2 and it works like a dream. PXC works as advertised.
UPDATE 2013-02-06 16:40 EDT
drogart made the following comment:

I don't think having one node of your production cluster be a dev/testing environment is a good idea. That would mix dev write traffic back into the prod db. It would also create a situation where devs could impact the prod db cluster by spinning up a new node.

Here has a very valid point. In light of this, be very carful and sparing as to what developers can do this.
Nevertheless, should you ever need a full copy of production in a dev server, offpeak:

STEP01) Setup new PXC server
STEP02) Configure wsrep options
STEP03) Start MySQL (SST should kick off)
STEP04) Shutdown MySQL
STEP05) Comment out wsrep options
STEP06) Restart MySQL (Full of Prod Data with no connectivity to the Real Cluster)

Once this dev server is set up, do the following to update it:

STEP01) Shutdown MySQL
STEP02) Uncomment wsrep options
STEP03) rm -f /var/lib/mysql/galera.cache
STEP04) rm -f /var/lib/mysql/grastate.dat
STEP05) Start MySQL (SST should kick off)
STEP06) Comment out wsrep options
STEP07) Restart MySQL (Full of Prod Data with no connectivity to the Real Cluster)

CAVEAT : Do this copy of Prod to Dev during offpeak hours !!!


How to drop all of my functions in PostgreSQL?

To drop all functions (including aggregates) in a given schema (be careful with this!):
Postgres 11 or later

Procedures have been added.
The system catalog pg_proc slightly changed: prokind replaces proisagg and proiswindow - and also tags functions and the new procedures

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE prokind
                              WHEN 'f' THEN 'FUNCTION'
                              WHEN 'a' THEN 'AGGREGATE'
                              WHEN 'p' THEN 'PROCEDURE'
                              WHEN 'w' THEN 'FUNCTION'  -- window function (rarely applicable)
                              -- ELSE NULL              -- not possible in pg 11
                            END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace  -- schema name here!
   -- AND    prokind = ANY ('{f,a,p,w}')         -- optionally filter kinds
   ;

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;  -- debug / check first
      -- EXECUTE _sql;         -- uncomment payload once you are sure
   ELSE
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$;

The schema name is case sensitive in this context.
The executing role needs to have the necessary privileges of course.
You might add CASCADE like demonstrated by mehmet but that will also drop depending objects, recursively - not just functions. Makes it even more dangerous. You better know exactly what you are doing.
Related, with more explanation:

How to drop function from all schemas
DROP FUNCTION without knowing the number/type of parameters?

Postgres 10 or older
DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace;  -- schema name here!

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;  -- debug / check first
      -- EXECUTE _sql;         -- uncomment payload once you are sure
   ELSE
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$;


Is it possible to create a foreign key with a condition?

It is not possible create a foreign key with a condition as you like. But you might use an intermediate table to simulate the condition. I call this table category_root. The category_root table will be updated only by triggers on category table. So you can create the foreign key between site table and category_root tables. No users must be have grants to update category_root to prevent inconsistencies. See diagram to see how the tables are related to each other.

create table category (
  id int auto_increment, parent_id int, name varchar(45),
  primary key (id),
  constraint fk_parent foreign key (parent_id) references category (id)
  on delete no action on update no action
) engine=innodb;

create table category_root (
  category_id int not null,
  primary key (category_id),
  constraint fk_category_id foreign key (category_id) references category (id)
  on delete cascade on update no action
) engine=innodb;

create table site (
  id int auto_increment, category_root_id int not null, name varchar(45),
  primary key (id),
  constraint fk_category_root foreign key (category_root_id) references category_root (category_id)
  on delete no action on update no action
) engine=innodb;

The trigger for insert is defined so when an new category without parent_id is inserted into category a new row is insert also into category_root table:

delimiter $$
create trigger t_category_ins after insert on category for each row
begin
  if NEW.parent_id is NULL then
    insert into category_root(category_id) values (NEW.id);
  end if;
end
$$
delimiter ;

The trigger for update is a little more complex:

delimiter $$
create trigger t_category_upd after update on category for each row
begin
  set @new_parent_id = NEW.parent_id;
  set @old_parent_id = OLD.parent_id;

  if (@new_parent_id is NULL) then
    if (@old_parent_id is not NULL) then
      insert into category_root(category_id) values (NEW.id);
    end if;
  else
    if @old_parent_id is NULL then
      delete from category_root where category_id = NEW.id;
    end if;
  end if;
end
$$
delimiter ;

Let try to insert some values:

insert into category(name) values('root1');
insert into category(name, parent_id) values('a', last_insert_id()), ('b', last_insert_id());
insert into site(category_root_id, name) values (1, "site1");

select * from category;
+----+-----------+-------+
| id | parent_id | name  |
+----+-----------+-------+
|  1 |      NULL | root1 |
|  2 |         1 | a     |
|  3 |         1 | b     |
+----+-----------+-------+

select * from category_root;
+-------------+
| category_id |
+-------------+
|           1 |
+-------------+

select * from site;
+----+------------------+-------+
| id | category_root_id | name  |
+----+------------------+-------+
|  1 |                1 | site1 |
+----+------------------+-------+

If we try to add a site with a category_id with parent_id not null we got an error:

insert into site(category_root_id, name) values (2, "site1");

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`site`, CONSTRAINT `fk_category_root` FOREIGN KEY (`category_root_id`)
REFERENCES `category_root` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)


Transpose rows data into columns in MYSQL

Please provide Data as Text, your image, is for an ocr not really good enough

CREATE TABLE table1 (
  `regno` VARCHAR(6),
  `subject` VARCHAR(16),
  `grade` VARCHAR(2)
);

INSERT INTO table1
  (`regno`, `subject`, `grade`)
VALUES
  ('7007gd', 'mathematics', 'C6'),
  ('7007gd', 'english language', 'CS'),
  ('7007gd', 'economics', 'C5'),
  ('7007gd', 'government', '83'),
  ('7007gd', 'biology', 'C6'),
  ('7010gd', 'mathematics', 'C6'),
  ('7010gd', 'english language', 'C5'),
  ('7010gd', 'economics', 'C5'),
  ('7010gd', 'government', 'C5'),
  ('7010gd', 'biology', 'C5'),
  ('70151f', 'mathematics', 'C6'),
  ('70151f', 'english language', 'C5'),
  ('70151f', 'economics', 'C6'),
  ('70151f', 'government', 'C5'),
  ('70151f', 'biology', 'C5'),
  ('70151f', 'chemistry', 'C5'),
  ('70151f', 'physics', '83');

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(`subject` = "', `subject`,'", `grade`,"")) AS "',`subject`,'"')
              ) INTO @sql
FROM table1;

SET @sql = CONCAT('SELECT  `regno`,  ', @sql, '
                  FROM table1 s
                 GROUP BY s.`regno`
                 ORDER BY s.`regno`');
#SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;

regno  | biology | chemistry | economics | english language | government | mathematics | physics
:----- | :------ | :-------- | :-------- | :--------------- | :--------- | :---------- | :------
7007gd | C6      |           | C5        | CS               | 83         | C6          |
7010gd | C5      |           | C5        | C5               | C5         | C6          |
70151f | C5      | C5        | C6        | C5               | C5         | C6          | 83

db<>fiddle here


When do you remove entries from a write-ahead log (WAL)?

First up, well done for trying to get to grips with how WAL works. It's often misunderstood but when people grasp the concept it's a genuine lightbulb moment for understanding databases.

We need to re-word your description of the steps that occur to commit a transaction:

Database documents the change in the write-ahead log (WAL).
Database updates the in-memory record.

That's it, we're done, the transaction is committed. The in-memory record is modified, details of what was modified is stored in the log, negating the need to harden every record change to physical disk.

On to your questions:

  When does the database know it's safe to remove the entry from the WAL?

The one liner explanation would be that the log entry isn't required once modified database records are flushed to disk. When this occurs is dependent on a variety of factors which vary according to the database. SQL Server for instance has a configurable recovery interval which controls the checkpoint frequency (the action of flushing changed records to disk).

  Does a WAL implying writing the record to disk twice (once in the WAL,
  once in the database file)?

Yes, kind of, no, depending on your perspective. Change a record once and the modification will be recorded to the log and when a checkpoint occurs, written to disk. Change the record 15 times between checkpoints and you have 15 changes recorded in the log but the modified record will only be written to disk once, on the next checkpoint.

  Postgres claims that WAL "significantly reduced number of disk writes,
  because only the log file needs to be flushed to disk" but (coming
  back to question #1) don't you have to flush the main database file
  before removing an entry from the WAL?

Yes you do but see above for why WAL reduces disk writes.


Unable to delete Log Shipping configuration

If something is still setup on the secondary server you need to run sp_delete_log_shipping_secondary_database there.

You can clear the log shipping configuration on the primary by executing sp_delete_log_shipping_primary_secondary

USE master;
GO
EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = N'<PrimaryDatabase>'
   ,@secondary_server = N'<SecondaryServer>'
   ,@secondary_database = N'<SecondaryDatabase>'
GO


Invoke-SqlCmd - does casting as integer prevent injection

You can, but there's problems with doing that.

Primarily, it's still not a great idea because the pattern isn't extendable.  It only works for certain data types.  As soon as you have a string parameter, you're back where you were with injection.  It also means that you have to care about how certain data types (byte arrays, GUIDs, datetimes) get cast to strings.

You also have to be aware that null-valued parameters will get silently modified to 0 since [int32] is a non-nullable type.  You'd have to specify [nullable[int32]] to prevent that behavior.  Even then, since null values that are cast to a string will result in an empty string, you get a much more descriptive error with a parameter.

Would you rather debug this:

  Invoke-Sqlcmd : Incorrect syntax near ';'.

Or this:

  Exception calling "Fill" with "1" argument(s): "The parameterized query '(@object_id int)select type_desc from sys.objects where object_i' expects the parameter '@object_id', which was not supplied."

Finally, if you're going to use the query a lot it might be harder for the system to reuse the query plan.

It's really not that difficult to specify once you're used to it:

function foo {
    [CmdletBinding()]
    Param(
        $object_id
    )

    $Query = 'select type_desc from sys.objects where object_id = @object_id;'
    $ConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f 'localhost', 'tempdb'

    $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString

    $SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $Query, $SqlConnection
    $SqlCommand.Parameters.Add('@object_id', [System.Data.SqlDbType]::Int).Value = $object_id

    $SqlDataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter -ArgumentList $SqlCommand

    $DataTable = New-Object -TypeName System.Data.DataTable -ArgumentList 'objects'

    [void]$SqlDataAdapter.Fill($DataTable)
    $SqlConnection.Dispose()

    , $DataTable
}

[If you're fine with outputting DataRows only, you can drop the comma from the last line.  The comma forces the function to output the DataTable whole.  Without the comma, you'll get a stream of DataRows.]

It looks complicated, but you can reuse this pattern over and over again.  I essentially copied the above code from my own scripts.

If your concern is code deployment, then I'd argue that this method is still better.  The above code requires only the .Net Framework.  Indeed, the code even works on PowerShell Core 6.0 (granted, you have to call (foo <value>).Rows to get equivalent output because Core hasn't been taught how to display DataTables at the command line).  Using Invoke-Sqlcmd adds a dependency on the SqlServer PowerShell module being installed.  Sure, it's a first party module, but it still doesn't ship with Windows by default.  You definitely lose some benefits of encapsulation being done by others without Invoke-Sqlcmd, but there's still a trade-off.


Selecting Distinct Rows but Counting All Rows

If you want the interestingData and timestamp from the same row (the most recent row that exceeds the threshold), and if you want to include all rows that exceed the threshold even if some rows for that ID don't meet the threshold, then:

;WITH x AS
(
  SELECT ID, interestingData, [timestamp],
    [count] = COUNT(1) OVER (PARTITION BY ID),
    rn = ROW_NUMBER()
      OVER (PARTITION BY ID ORDER BY [timestamp] DESC)
  FROM dbo.tablename
  WHERE interestingData > 300
)
SELECT ID, interestingData, [timestamp], [count]
  FROM x
  WHERE rn = 1;

Also, try to avoid data types and/or reserved keywords as column names. timestamp is not a great choice because (a) it's not very meaningful and (b) it requires square brackets in a lot of scenarios.


It should be a pretty straight forward group by and count:

select start, institution_code  , year, count(*) as Count
from yourtable
group by start, institution_code  , year;


What database name should I use to connect Tomcat with my database

In Oracle 12c there are 4 key name-related concepts you need to know:

SID, or instance ID.
Database name / database unique name
Container / Pluggable database names
Service names

The instance ID is an ID that uniquely identifies one database instance running on one server.  On a Linux server, this is normally specified in the $ORACLE_SID variable, but also combined with $ORACLE_HOME to uniquely identify the binaries and data files that make up that database.

Database name is defined at database creation time (and can also be changed later with the nid tool).  This is embedded within the data files for this database.  The DB name can be the same as, or different to the instance ID.  It's possible to have multiple databases of the same name running on one host.  Database unique name can be the same or different to database name in a standalone setup, but in a data guard setup, each database must have a different unique name.

CDBs/PDBs. Oracle 12c introduced the concept of Pluggable DataBases.  With these, you have one "master" database (the container) into which you can plug/unplug one or more PDBs.  This matters because you can't use the SID any more to connect to a pluggable DB - there may be many PDBs open within that same SID.

Service names are a little more ephemeral in nature.  They're a construct which can be used to separate, monitor, control and report on database traffic.  They can be dynamically created or destroyed.  To connect to a PDB you must use (one of) the database's service(s).  They're useful because no matter what the database instance is called, the available services can remain the same.

An example of why services can be useful.  In dev/test/prod environments, if we used SIDs to connect to a database, we'd need to have either all our database instances named the same, or we'd have to change configuration files between environments.  Because we use service names, we can name the instance what we like but ensure the same services are running in all 3 environments.

By default when you create an instance, you end up with an service the same name as the SID + domain.

e.g.:

SID = orcl
domain = oracle.com
default service name = orcl.oracle.com

So, to get back to your question, you will almost certainly need to know the service name.  This can normally be found (on a Linux instance, anyhow) by running (as the oracle user, or equivalent in your environment):

lsnrctl status

e.g.

Service "devcdb.xxxx.national" has 1 instance(s).
  Instance "devcdb", status READY, has 1 handler(s) for this service...
Service "devpdb1.xxxx.national" has 1 instance(s).
  Instance "devcdb", status READY, has 1 handler(s) for this service...


Full-Text Search against serial numbers?

Full-Text Search would be able to efficiently Search for a word with a prefix (Prefix Term) or Search for a specific word or phrase (Simple Term), but not a suffix as you described. You can achieve what you need using LIKE.

As you can see in Eugene Elutin's answer for a Using Full-Text Search thread:

  partial and suffix match in T-SQL can be only done using LIKE, which
  will do full table scan to find relevant rows.

Here's one way of doing the search you described

  Full-text search only supports prefix search. In some circumstances,
  reversing the string, indexing that, and then searching for the
  reverse of the suffix can be made to work reasonably well.
  Fundamentally, though, FTS is not designed for the purpose you
  require.

But that approach would fail to find this other serial KN5672281 when trying to search for 7228.