Should I use a RDBMS JSON field or a NoSQL database to store large JSON datasets that I don't need to query?

Neither. What you need is a file system, or cloud blob storage. Choose one with high compression ratios.

Typically data is used intensely for a short while then touched only infrequently thereafter. Choosing a storage service with tiers will allow further cost saving.

As the service becomes popular it is likely several users will have the same dataset. Storage service de-duplication could allow further cost reduction.

Store the meta data in whichever flavour of DBMS you choose for the main application. This will reduce the overall complexity and utilise existing expertise. Relational would be my preference but document storage would work, too.

MongoDB adds empty space to the written data so the on-disk length follows a power-of-two rule. This could waste a lot of space depending on how your source JSON works out. This is a lot like the fill-factor in RDBMS B-Tree indexes. This behaviour can be removed in more recent versions.

Performance-wise I would think a solution based on the file system directly would be marginally faster than one involving a DBMS of either flavour. Both approaches will use the same underlying hardware - spindles, NICs, buses etc. The DBMS, no matter how efficient, is an additional layer in the stack between the disk and the client. If it is not adding additional value, through joins, filters, aggregates, security or whatever, I can't see how having it in the run-time path helps. The meta-data is a different beast to the JSON, of course, and will benefit from all the additional functionality a DBMS provides.

There's a trade-off to be had with compressed data. On one side the storage IO and network communication is faster when the data is compressed. Reading 10GB uncompressed data from disk and moving it over the network will always be slower than reading and moving the same data compressed to 7GB. On the other side is the CPU cost to decompress before usage. My experience from data warehouse environments, with gigabyte-scale reads and ten-plus cores, is that compression lowered elapsed time. In OLTP-like environments (small, random reads with frequent updates) compression is definitely not recommended. We don't have hardware specs, dataset sizes, usage patterns, pricing information or a measure of your speed-versus-cost tolerance so can't calculate this on your behalf. Likely testing at production scale will be the only way to answer this properly.


How to call a stored procedure in an Oracle Scheduler job

You don't need CALL when creating a job (and parenthesis for a procedure that takes no arguments ).  I'd also specify  INTERVAL=1 explicitly.

BEGIN
   dbms_scheduler.create_job (
    job_name => 'emp_test_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'emp_test;',
    start_date => SYSTIMESTAMP,
    enabled => true,
    repeat_interval => 'FREQ=DAILY;INTERVAL=1'
   );
END;


You can store them in the public schema if you're using them in more than one schema. That's what it's there for.

Typically, I don't. If you have a domain in more than one schema that usually indicates to me that either,

the type should be packaged as an extension and in it's own schema.
your two schemas should merge.

But there is nothing wrong with using the public schema.


How can I execute a PL/SQL procedure for Oracle via JDBC?

Instead of EXEC, you should put it between begin ... end, or use the { call } syntax.
Here is an example, in the documentation:

http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#i1014127


Remove db_denydatawriter, db_denydatareader role from admin user

If you need to reset your permissions for the master user to the defaults (dbo access in each database), you can reset the master user password from the AWS console and Apply Immediately to have changes take effect.

Here are the steps to do this:

Go to the AWS Console
Instance Actions -> Modify
Under Settings type in a new password for the master password.
Select Apply Immediately


How to find MYSQL Logs for Drop Delete Record?

You must enable query logging on server as described on the official page:

https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

Here general logging is the easiest approach that can be used to resolve your objective. Link shared by you shows two different methods of achieving the same. You can use following queries as root user to get what you want

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

select event_time,user_host,argument
from mysql.general_log
where command_type='Query' and
upper(argument) rlike '(DROP|DELETE|TRUNCATE)+';

Beware that it will fill server disk space very faster.


Running materialized view refresh in parallel

Set atomic_refresh to false. It will truncate your MV table instead of deleting records.


Search all columns in list of tables for particular string

You can query syscolumns and sysobjects in each database to get the information you need.

select
  distinct so.name as TABLE_NAME,
  sc.name as COLUMN_NAME
from
  syscolumns sc,
  sysobjects so
where
  sc.id = so.id
and   sc.name like '%searchstring%'
order by so.name

If you need to limit your search to specific tables, then you can add an additional 'and' clause:

and so.name in ("Table1","Table2","Table3")


Cannot access mysql from a program outside

From the screen shots it looks like Navicat is installed on the same machine as the MySQL server.
That jives with the messages we see there.
If Bdoc Suite is running from a machine outside the MySQL server, you would need to use either the ip address or the hostname to connect to your server.

There also may be an issue with the permissions on the account. MySQL has you specify not only the user, but the source of the connection to assign permissions.

If you want to connect from ANY other IP, you would want to create a user with a wildcard ip address and use it to connect.

To test this, you would want to log on locally with your root account and do the following:

    CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

    GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

    CREATE USER 'username'@'%' IDENTIFIED BY 'password';

    GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

    FLUSH PRIVILEGES;

Then try connecting remotely with that new user.  If you prefer to scope your user permissions and know what ip they will be coming from, you can replace the '%' with the ip address you will be connecting from.


SSIS Package Error when called from SQL Agent Job

I had similar problem earlier and what I found is because the creator of the Package and executor of Package were different user it was having some issues.

To get around this issue, I had to protect the package with password and I used that password to access the package in my Job.

You should give that a try, I hope it helps.

Also, is your package doing any distributed transactions? You may need to set authentication from Services for distributed transaction.


Queues are schema bound objects and as such they may belong to a different schema than your current default schema (likely dbo). If that's the case the DROP statement must qualify the queue with a two part name:

DROP QUEUE <schema>.<queue>;

You can get the schema name from sys.service_queues:

SELECT SCHEMA_NAME(schema_id), name FROM sys.service_queues;


You can use the SUBSTRING_INDEX() function to get the part before the first dot and then group:

select SUBSTRING_INDEX(programVersion, '.', 1) AS programMajorVersion,
       count(programVersion) AS totalIncrements
     from records R
     inner join Programs P
         on P.programID=R.usageProgramID
     where programName="word"
     group by SUBSTRING_INDEX(programVersion, '.', 1)
     order by CAST(programMajorVersion AS unsigned) ;

The above may not be the most efficient on a large table. If you have indexes that your version uses and is efficient, you may use that as a derived table and then aggregate with the substring function:

select SUBSTRING_INDEX(programVersion, '.', 1) AS programMajorVersion,
       SUM(cnt) AS totalIncrements
     from
     ( select programVersion, count(programVersion) AS cnt
         from records R
         inner join Programs P
             on P.programID=R.usageProgramID
         where programName="word"
         group by programVersion
     ) AS t
     group by SUBSTRING_INDEX(programVersion, '.', 1)
     order by CAST(programMajorVersion AS unsigned) ;


Which of these structures are good for maintaining history data?

One thing missing from the sample tables above is BeginDate and EndDate (or whatever you want to call them).  Since you are tracking history, the dates are obviously valuable and can be used when querying older entries.

Having a StudentPass and a StudentPassHistory table does split out the information, but that is a mixed blessing, since the student's full StudentPass information is split across two different tables.

You might consider implementing StudentPass like this:

CREATE TABLE dbo.StudentPass
(idStudentPass INT,
 Student_idStudent INT,
 BeginDate Date,
 EndDate Date
 PassActive INT);

This way, the BeginDate and EndDate clearly identify not only whether the StudentPass is active, but could also preview for you the approaching EndDate.  Likewise, a future BeginDate could also support setting up a StudentPass that will not be activated for a few more weeks.

The PassActive is not strictly needed, but can be a useful shortcut for querying active StudentPass data without using dates. You just need a process to update the PassActive column setting as defined to be in the inclusive range of the BeginDate and EndDate.

Using a design like this keeps all the information in one table, which I assume will not overwhelm your query processing, so that you can simply filter the data according to your needs.

Of course, if you feel that the StudentPassHistory would server you better for some other needs, then make the choice to also create that table.


SQL Server Restore DB from MDF file, lost LDF and DB was not cleanly shutdown

There is a hack you can try: where you create a dummy DB with the same name and size as your old data and log files. Then you have to stop SQL server, replace the new empty files with the old files that have data in them.

Check out this blog link by Paul Randal where the hack is explained in detail. It looks like you have tried the first method mentioned, but read along and try the other methods. Please let us know how it goes. I do not know how well it will work without log files, but it is worth a try.


Amazon: How to change database name in Amazon RDS MySQL?

you can rename the database using the following command but make sure to take backup first

CREATE database new_db_name;
RENAME TABLE db_name.table1 TO new_db_name, db_name.table2 TO new_db_name;
DROP database db_name;

references:
http://www.rndblog.com/how-to-rename-a-database-in-mysql/
https://stackoverflow.com/questions/12190000/rename-mysql-database


Combining Two Base Tables with a 1:M relationship Into One View with Distinct Rows

You will need to use the PIVOT function with a INNER JOIN to accomplish this:

SELECT StudentId
     , FName
     , LName
     , CASE H WHEN 0 THEN NULL ELSE 'H' END AS [Hispanic]
     , CASE W WHEN 0 THEN NULL ELSE 'W' END AS [White]
     , CASE B WHEN 0 THEN NULL ELSE 'B' END AS [Black]
     , CASE A WHEN A THEN NULL ELSE 'A' END AS [Asian]
     , CASE I WHEN 0 THEN NULL ELSE 'I' END AS [Indian]
 FROM (SELECT s.StudentId, FName, LName, r.[Race Code]
         FROM [Students] AS s
         JOIN [Race] AS r ON r.StudentId = s.StudentId)
PIVOT (COUNT([StudentId])
         FOR [Race Code] IN ([H],[W],[B],[A],[I])) AS p

If you are happy with just having the count as the row values then there is no need for the case statements and the query can be simplified to this:

SELECT StudentId
     , FName
     , LName
     , [H] AS [Hispanic]
     , [W] AS [White]
     , [B] AS [Black]
     , [A] AS [Asian]
     , [I] AS [Indian]
 FROM (SELECT s.StudentId, FName, LName, r.[Race Code]
         FROM [Students] AS s
         JOIN [Race] AS r ON r.StudentId = s.StudentId)
PIVOT (COUNT([StudentId])
         FOR [Race Code] IN ([H],[W],[B],[A],[I])) AS p


How to make a copy from a Emergency Database?

The import/export wizard doesn't copy a lot of stuff, the identity property on columns is just one of the things that it doesn't copy.

If you don't have a functional backup you'll want to manually script out all the objects from the database first, then copy the data using the import/export wizard.

Once your new database is up and running you'll want to look at setting up regular database backups so that if this happens again the database can just be restored from the backups.

You should also keep in mind that SQL Server 2000 has been end of lived and all support for the product is gone.  Upgrading to a newer version of SQL Server would be a good idea.


To your question:

I want to know which users have SYS and SYSDBA privilege before export/import operation.

SYSDBA is a role, not a user or schema. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.
As per the Burleson consulting blog, the v$pwfile_users view contains a list of all users who have been granted the SYSDBA or SYSOPER roles.
SQL> desc v$pwfile_users;

Name Null?  Type
----------------------------------------- -------- ----------------------------
USERNAME    VARCHAR2(30)
SYSDBA      VARCHAR2(5)
SYSOPER     VARCHAR2(5)
SYSASM      VARCHAR2(5)

SQL>
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE TRUE FALSE
TOM                            TRUE FALSE FALSE

In 11g there is a new column in v$pwfile_users for the SYSASM privilege:
SYS AS SYSDBA @ orcl11 SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

For further details see this Tom Kyte article.%3A%3AP11_QUESTION_ID:2659418700346202574">Here


How to reclaim disk space after delete without rebuilding table?

If you use anything but CLUSTER / VACUUM FULL / pg_repack (which all manage locks automatically) you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes.

TABLESPACE

Yes, your last idea could work. Create a new tablespace on the other disk.

CREATE TABLESPACE ts1 LOCATION '/data/disk2';

Then create an optimized copy of the table in the new tablespace:

CREATE TABLE new_tbl
TABLESPACE ts1 AS
SELECT * FROM tbl
ORDER BY ....  -- ORDER BY is optional

The copy will be packed tightly without dead rows.

Then you might just drop the old table and rename the new one to keep using the new disk.

Or, if you have to keep it on the old disk for some reason, delete the old table, rename the new one and move it back to the default tablespace. This step is much faster now with:

ALTER TABLE tbl SET TABLESPACE pg_default

Per documentation:

  This form changes the table's tablespace to the specified tablespace
  and moves the data file(s) associated with the table to the new
  tablespace. Indexes on the table, if any, are not moved; but they can
  be moved separately with additional SET TABLESPACE commands. See also
  CREATE TABLESPACE.

Either way, don't forget to (re)create all depending objects. Indexes, foreign keys, views, ...

Well, you could use ALTER TABLE tbl SET TABLESPACE ... to begin with, but then the table would not be optimized as requested, just moved as is. But you would have enough wiggle room then to run pg_repack etc.

COPY

A complete backup / restore might take too long, but you could do that for only the table in question.

COPY tbl TO '/path/to/other/disk/tbl.pgsql';
TRUNCATE tbl;
COPY tbl FROM '/path/to/other/disk/tbl.pgsql';

The table is now packed tightly.

Temporary table

If you should happen to have enough RAM, you could do something similar with a temporary table in RAM. Would be much faster. Detailed instructions:

Best way to delete millions of rows by ID
How to delete duplicate entries?


Updating a column in one table based on matches with column in another table

You can try something like this, this is setting table1's col3 value to table2's col3 value where they match on col1 and col2. You can replace the col3 with your 'some value'.

create table table1 (
col1 int,
col2 int,
col3 int
);

create table table2 (
col1 int,
col2 int,
col3 int
);

insert into table1 values (1,2,3);
insert into table1 values (4,5,6);

insert into table2 values (1,2,10);
insert into table2 values (4,5,20);

select * from table1;

update table1
set col3 = (
 select table2.col3
  from table2
 where table1.col1 = table2.col1
   and table1.col2 = table2.col2
)
where exists (
select table2.col3
  from table2
 where table1.col1 = table2.col1
   and table1.col2 = table2.col2
);

select * from table1;

DB Fiddle


SQL Server -- More efficient alternatives to repeating an (almost) identical subquery

Hard to say for sure without seeing the full schema and queries, to work on the execution plans. But you could try:

Where
  (Select Count(Distinct fld1) From t1  ...etc... where ...etc... and fld1 In ('X', 'Y', 'Z')) = 3


How would one merge two databases that use different keys?

There's not one specific right answer to your question but the thought process you went through in it is the correct way to try to solve the problem.
In lieu of a randomly generated unique primary key, sometimes tables have something known as the natural key. If the natural key is composed of multiple columns, it is known as a composite key as well. An example of this is a UserAccounts table that has a UserId primary key, but also has a LoginEmail column with a unique constraint on it, making it a natural key.
The best thing you can really do is come up with an algorithm, which generally can be as simple as picking a set of fields that make the best composite natural key candidate and joining the two tables between the two databases on those fields.
Then you have 3.5 cases you have to handle:
1: When there is a truly distinct match of one and only one record as a result of the join. In this case you can be fairly confident it's correct and no need for further processing.
2: When there are multiple rows that match between the two tables. Usually human intervention is needed if no other process exists to decide how to proceed. E.g. if there was a CreatedDate for the records, you could throw away everything but the most latest CreatedDate records (if it made business sense). Human consolidation is not uncommon for this case.
3.5: When no records match in Table1 for Table2 (and visa-versa, there's records in Table1 that don't find a match in Table2). If that's the case then you basically need no further action since there's only one source of truth.


OS backups of the data files will only be valid if the database was shutdown at the time the backup was made, otherwise they most likely will not work because they will not account for the state of any data that was cached being written to disk when the backup was made. The system tablespace in particular is problematic because it contains the entire data dictionary for the database. Without it, the other data files are largely useless.
All data files must be synced to the same system change number that is in the control files before you'll be able to open the database; if you don't have archived redo logs to reapply missing transactions then you'd have to restore all data files and control files from the same cold/offline backup and accept whatever data loss that implied.
I would advise opening a ticket with Oracle Support, but without an RMAN backup or archived redo log files I don't think you're going to have many options. Maybe a recent datapump export of the data that you could use to populate a new db instance?


Is it possible to use one certificate with aliases for MongoDB cluster?

Actually, yes :
https://docs.mongodb.com/manual/core/security-internal-authentication/#x-509

  Either the Common Name (CN) or one of the Subject Alternative Name
  (SAN) entries must match the hostname of the server, used by the other
  members of the cluster.

  For example, the certificates for a cluster could have the following
  subjects: subject= CN=,OU=Dept1,O=MongoDB,ST=NY,C=US
  subject= CN=,OU=Dept1,O=MongoDB,ST=NY,C=US subject=
  CN=,OU=Dept1,O=MongoDB,ST=NY,C=US


client (mysql) and server (mysqld) are different applications with different parameters

"mysql"  =  your mysql-client 'mysql' ... and that FALSE is the client parameter
"/etc/init.d/mysql" =  name of the service that starts your mysql-server 'mysqld' that uses my.cfg for it's parameters


command line tool on linux to convert oracle sql into db2 sql

DCW does not correct all of the possible incompatibilities in the Oracle SQL and PL/SQL, except in trivial cases. Even if there were a command-line tool (though there is not), there would always be some manual tweaking so you would not be able to fully automate the process.


What are object-relational databases, and why is this model necessary in spatial databases?

The relational model and the object-oriented paradigm
The state-of-the-art relational model, originated in 1970 by Dr. E. F. Codd, is applied science concerning the field of database administration. Its two solid pilars are first-order logic and set theory.
The object-oriented paradigm, as devised by Dr. Alan Kay, is an approach useful to build application programs. It has proven to be quite effective in the construction of graphic user interfaces that facilitate the interaction between end-users and information systems.
As noted, each of the two frameworks mentioned above serves a very specific purpose. When utilized to generate the appropriate kind of component, both can be of great help in a software development project.
Database administration, application programs and the term “object-relational”
Before the conception and development of the relational model, (a) the design, creation and administration of application programs was heavily mixed with (b) the design, creation and administration of the data (all this in an ad hoc fashion). There was no science available to handle the data in a general and sound manner. Dr. E. F. Codd was actually a programmer at IBM, and faced first-hand the difficulties caused by said circumstances. With his brilliance, practical experience and mathematical background he was capable of envisioning and developing an elegant and robust foundation to manage the data which, amid other substantial factors, permits handling said resource in an application program-independent manner.
I am not sure but, in case an “‘object-relational’ database management system” might exist, it would be an artefact onto which “‘object-relational’ databases” could be implemented. An “‘object-relational’ database” would be a device that was created by means of an entanglement of object-oriented constructs along with relational instruments.
It is hard to tell if a database management system of the nature described above can exist since, by introducing object-oriented constructs, many of the relational capabilities are put at risk (see the “Codd’s Twelve Rules” for relevant information), therefore it could hardly be considered relational even when it would be partly so.
In fact, (i) appending object-oriented tools to a relational database management system and (ii) using them in the relational databases implemented on top of it is entirely needless.
Nevertheless, there are “current” opinions that advocate, via object-oriented patterns, a mixture of design, creation and administration regarding (a) application programs and (b) databases. This can be interpreted as an invitation to make a regression to a pre-scientific (i.e., pre-relational) era as far as the databases part is concerned (because, naturally, there is not an object-oriented model related to data administration).
For its part, the relational model provides general and strong mechanisms to design (the logical structure), constrain (the values) and manipulate (with abstractions) the data. One of their benefits is that they are very simple (but never simplistic). In order to get the most out of the aforementioned mechanisms, a database administrator/operator must work, necessarily, with relations (usually declared as tables in a given SQL platform), and as you know, a relation or table is not part of the object-oriented paradigm (which neither provides data integrity constraints nor general data manipulation operations, and it is not supposed to do so).
The power of a relational language lies in its expressive capabilities, not in its computational aspects. Roughly speaking, when following relational methods one declares the structure of the things of interest (the way they are, their structure), while with an object-oriented language (e.g., Smalltalk) one should deal mainly with the behaviour of the things of significance (how they do what they do, the processes they carry out), which is paramount with respect to programming an application.
One of the multiple advantages of a relational database is that, as it has to be designed independently of the languages used or paradigms followed at the application programming stage, it can work along with multiple languages and/or application programming paradigms and/or multiple application programs at the same time.
That being said, a relational database must to be built by a designer who follows relational principles, therefore the construction of (1) a particular database onto (2) a certain relational database management system does not automatically grant that particular database the relational label.
The ArcGIS geodatabase
With the intention to help in the understanding of the arcgis.com links you include in your question, I consider that when the term geodatabase is used, it is actually a contextual designation for a complete geographic information system that may include

one or more proper databases built on top of one or more database management systems, and
one or more application programs that work along with said database(s).

In that respect, let us take a look at the architecture of the proper database(s) of a geodatabase, where it is stated that:

The geodatabase storage model is based on a series of simple yet essential relational database concepts and leverages the strengths of the underlying database management system (DBMS). Simple tables and well-defined attribute types are used to store the schema, rule, base, and spatial attribute data for each geographic dataset. This approach provides a formal model for storing and working with your data. Through this approach, structured query language (SQL)—a series of relational functions and operators—can be used to create, modify, and query tables and their data elements.

So that somewhat suggests that the data is being handled independently of the application programs that access it, and it would be very valuable of course.
Then, this page contains the following title (in the form of an assertion) and paragraph:

The geodatabase is object relational
The geodatabase employs a multitier application architecture by implementing advanced logic and behavior in the application tier on top of the data storage tier (managed within various database management systems [DBMS], files, or extensible markup language [XML]). The geodatabase application logic includes support for a series of generic geographic information system (GIS) data objects and behaviors such as feature classes, raster datasets, topologies, networks, and much more.

Which seems to indicate that the objects behaviour (the way in which the application program objects act) is handled where it should be handled, i.e., at the application program level (or “tier”, as described there).
Further, back in the geodatabase architecture page, an identical title and a paragraph that is very similar to the one brought up above are introduced as follows:

The geodatabase is object relational
The geodatabase is implemented using the same multitier application architecture found in other advanced DBMS applications; there is nothing exotic or unusual about in its implementation. The multitier architecture of the geodatabase is sometimes referred to as an object-relational model. The geodatabase objects persist as rows in DBMS tables that have identity, and the behavior is supplied through the geodatabase application logic. The separation of the application logic from the storage is what allows support for several different DBMSs and data formats.

An excerpt that catches my attention in a particular way is “the geodatabase objects persist as rows in DBMS tables that have identity”, which is misleading because the tables (i.e., relations) of a relational database retain rows (i.e., tuples) that represent assertions that carry a specific meaning provided by a certain business domain predicate (that can be used to define an entity type), therefore it does not “persist” objects. Moreover, since an essential feature of an object is its behaviour (basically, its methods), it would be very interesting to know how it is “persisted” in a relational database.
On the other hand, the significant fragment “the separation of the application logic from the storage is what allows support for several different DBMSs and data formats” appears to stress the enormous relevance of handling the data separately from the application program.
Conclusion
My conclusion is that an ArcGIS geodatabase is neither (a) an object-relational database nor (b) an object-relational database management system. It may well be, as previously mentioned, a complete geographic information system that consists of (i) one or more actual databases (some of which could be more or less relational) and (ii) one or more application programs (some of which could be more or less object-oriented) accessing said databases.
Perhaps that is why it is contextually called “object relational”.


SQL Server services could not be started as 2 instances sharing same 'model' file

The paths of the other system databases (model, msdb, and tempdb) can be changed using ALTER DATABASE ... MODIFY FILE. The changes take effect on instance restart.

I blogged about how to move all the system databases here, including a video demo of the whole process. (The demos shows 2008 R2, but it's the same for 2005.) In your case, the files are already in the spot you want, so all you need to do is change the paths.


Why do I have THREADPOOL waits even though I have threads available?

I am not able to understand why I am seeing THREADPOOL waits when I run the first query, but second query shows threads are still available. Can any one please explain?

I've observed that you can see lots of really brief THREADPOOL waits (a handful of milliseconds each) while SQL Server is acquiring threads from the operating system.
This might happen if, for example, SQL Server was idle for a while, and then several parallel queries are executed at once.  These queries will need to wait while the OS provides threads to the sqlservr.exe process, accumulating THREADPOOL waits during that time.
You can confirm this is happening by watching the "Process" -> "Thread Count" PerfMon counter.  Generally, whenever this value climbs rapidly, you might see these THREADPOOL waits.
There's a complete demo of this behavior on my blog (Unusual THREADPOOL Waits), here's the summary:

Going back to that docs link:

Occurs when a task is waiting for a worker to run on.

In the typical case, this is because we’ve reached the logical limit SQL Server has set for how many workers threads you can have at a time.
In the unusual case, the task is waiting for a worker because SQL Server is allocating the thread (requesting it from the OS) that is needed to back that worker.

To be clear, I'd be surprised if these waits were the cause of your application timeouts.


Was using `set datcollate='C'` a mistake?

The datcollate column of pg_database stores

  LC_COLLATE for this database

An other page of the documentation about collations says:

  The collation feature allows specifying the sort order and character
  classification behavior of data per-column, or even per-operation.
  This alleviates the restriction that the LC_COLLATE and LC_CTYPE
  settings of a database cannot be changed after its creation.

That means that by updating that column you managed to cross some river that should not be crossed under any circumstances.

On the other hand, as collations affect the ordering of text, varchar and varchar typed columns (and possibly some others, check the documentation page for details).  This also means that the existing indexes on such columns will be ordered differently from what the LC_COLLATE dictates.  This will result of some weird behaviour, and as you've seen, at least a REINDEX will be needed.  I would expect that doing a

SET enable_indexscan TO off;

would help you finding the user by username, because it would do a sequential scan instead of an index scan, so not trying to use the unusable index.

I cannot tell if this is enough to fix your DB, but I would do the following instead:

set up a new cluster with the desired collation
take a dump from your DB
restore it on the new cluster, watching for errors
test it thoroughly and then start using it instead of the old one

For the future, you cannot change the DB level collation, but you can define collation on the column level.

One can even change the collation on a column like

ALTER TABLE t ALTER COLUMN col1 TYPE text COLLATE "en_US";

where you keep the type as it is, just set a different collation.

Just a final notice: changing the system catalogs directly should always be a last resort.  Before doing that (probably except adding new enum values in pre-9.1 versions), one should carefully examine the possible outcomes from the documentation, blog or mailing list posts and testing.


Messages in SQLCMD script output are not in order

PRINT output can be buffered.  A great way around this is to use RAISERROR (@msg, 0, 1) WITH NOWAIT; instead.

For example:

DECLARE @msg nvarchar(1000);

SET @msg = 'Do the first thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

SET @msg = 'Do the second thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

SET @msg = 'Do the third thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;


How to best store a directory tree in a database?

I'm a DB noob as well, I do recall postresql having json datatype for storing JSON structures. Maybe you can review the postgresql doc and decide if it works for you.


Windows Managed account to report subscription file share

Ok, I have not messed with the file share subscriptions so I had to set this up locally on my machine to mess with it. The only thing I can even come across in MSDN that talks about the username for this type of subscription is here:

  Password:  Specify the password for the Windows user name.

Out of that the only thing I can find is that it requires an actual user account with password. So it is not possible to use virtual or managed account.

The only way I could get this subscription to write to the UNC path was to enter a Windows User account. In this instance my own login. I was just running an example SSRS report against AdventureWorks2012 database.


SQL Server Replication - Only weeks worth of data

For Merge replication:

Static row filters, which are available with all types of replication. I have not done any time based filtering :)

I don't know that either of these is really going to meet your "running 7 days" criteria, which sounds to me like "last 7 days".


Mixing SQL Server 2016 and 2017 in a distributed availability group

It is feasible? Can I mix 2016 and 2017 AGs in a Distributed AG?

I have confirmed from Allan Hirt SQL Server MVP and HA/DR Guru that this is indeed possible and supported. So please go ahead, the confusion may arise because in BOL two different things are mentioned. I will highlight both

Quoting from Distributed Availability groups document

  A distributed availability group spans multiple availability groups,
  each on its own underlying WSFC cluster, and a distributed
  availability group is a SQL Server-only construct. This means the WSFC
  clusters that house the individual availability groups can have
  different major versions of Windows Server. The major versions of
  SQL Server must be the same

So while you can have different Windows server version in DAG but you cannot have different SQL Server version in DAG. From here it seems like the versions should be same but if you again look at Upgrading Availability group instances it says

  To migrate to a new version of the SQL Server instance using AGs, the only supported method is a distributed AG, which is in SQL Server 2016 Enterprise Edition or later.

Now you should believe this one not the the previous one.

  Or there is (obviously!) something that I'm missing and there's a simpler or more proper way to make it?

Apart from distributed AG, I would consider 2 approaches here

Side By side upgrade

I believe transaction logshipping should help here.

Create new WSFC with SQL Server 2017 installed--No downtime
Do not create AG as of now
Logship database from SQL Server 2016 to SQL Server 2017. --No downtime
During "cutover" stop logshipping and bring database on SQL Server 2017 online.--small downtime
Now point application to this new database.
Go ahead and configure AG on new SQL Server 2017.

I know there is bit of pain involved in configuring logshipping if you have big databases but this is best I see.

In-place upgrade

Do the rolling upgrade of Availability groups. Upgrading Availability group instances


BOL: A value of NULL indicates that the value is unknown. A value of NULL
  is different from an empty or zero value. No two null values are
  equal. Comparisons between two null values, or between a NULL and any
  other value, return unknown because the value of each NULL is unknown.

NULL means unknown. No other interpretation is valid.

  If that's true, wouldn't they sort last, since the value could be
  greater than all other values?

There is no could be. There is no potential value. Unknown is unknown is unknown.

As to why it appears first, rather than last, this is not catered for by published SQL standards and is unfortunately left to the discretion of the RDBMS vendor:

  Wikipedia: The SQL standard does not explicitly define a default sort order for
  Nulls. Instead, on conforming systems, Nulls can be sorted before or
  after all data values by using the NULLS FIRST or NULLS LAST clauses
  of the ORDER BY list, respectively. Not all DBMS vendors implement
  this functionality, however. Vendors who do not implement this
  functionality may specify different treatments for Null sorting in the
  DBMS.


How do DELETE / TRUNCATE affect table / tablespace?

From Oracle Truncate by Dan Morgan:

Note: Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.
Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the table back to zero. No row-level locks are taken, no redo or rollback is generated. All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake. (It's also true that you can't selectively truncate -no "WHERE" clause is permitted, unlike with deletes, of course).
By resetting the High Water Mark, the truncate prevents reading of any table's data, so they it has the same effect as a delete, but without the overhead. There is, however, one aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible.

Note that by default, TRUNCATE drops storage even if DROP STORAGE is not specified.


Is there an operator or an easy way to match an expression one or more times with the LIKE operator in SQL?

If you are talking about solving this purely in Transact-SQL, you can cover all those cases in your example data with the following filter:

WHERE FilteredField LIKE '%[a-zA-Z][0-9]'

If you want to additionally stipulate that all the characters before the numeral must be Latin letters, you will need to get a little creative:

WHERE FilteredField     LIKE           '%[a-zA-Z][0-9]'
  AND FilteredField NOT LIKE '%[^a-zA-Z]%[a-zA-Z][0-9]'

Basically, you are saying:

  The value must end with a letter and a numeral, but whatever precedes the letter must not be a non-letter.

There is no way to specify this with a single condition using only built-in syntax, if that was what you were after.


Querying a SQL Server's CNAME alias instead of the actual instance name

The reason the CNAME is not working is you are using a named instance.  When you specify the name of the instance as in SERVER\INSTANCE the SQL Server client looks up the port number of the instance via the SQL Server Browser service.  Because you are using a name that is different from the actual SQL Server instance name, that lookup fails.

Instead of connecting via AN-UATSQL03\DWH, try connecting directly using the port number, as in:

tcp:AN-UATSQL03,xxx

Where xxx is the port number.  You can get the port number either from SQL Server Configuration Manager, or via this query:

SELECT SPID = @@SPID
    , InstanceName = @@SERVERNAME
    , InstancePortNumber = CONVERT(varchar(255), local_tcp_port)
    , dec.auth_scheme
    , USER_NAME = USER_NAME()
    , SUSER_SNAME = SUSER_SNAME()
    , dec.connect_time
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

You'll need to ensure the port number is not being assigned dynamically; use SQL Server Configuration Manager to set it to a manually configured TCP/IP port.  These Docs should help you out.

This query in your question implies you have a SQL Server linked server setup:

SELECT *
FROM [ALT-STG-SQL-ATLNTA1\DWH].[Database].[dbo].[Column]

If you only want to access the DWH instance via that linked server, you can simply define the linked server using the "real" name, but name the linked server object the aliased name.  Something like this should work:

EXEC master.dbo.sp_addlinkedserver @server = N'AN-UATSQL03\DWH'
    , @srvproduct=N''
    , @provider=N'SQLNCLI'
    , @provstr=N'SERVER=ALT-STG-SQL-ATLNTA1\DWH;'
    , @catalog=N'tempdb';

That would allow you to reference the linked server as [AN-UATSQL03\DWH] while actually connecting to [ALT-STG-SQL-ATLNTA1\DWH].  So your sample query would be:

SELECT *
FROM [AN-UATSQL03\DWH].[Database].[dbo].[Column]


Can I send a string over TCP using T-SQL?

The article linked uses the dreaded WMI middle-man for delivering the notifications. That is so error prone that is not worth talking about. Cut out the WMI middle man (that also looses events...) and go straight to the source: create an event notification for the Database Mirroring State Change Event. Deliver the notifications locally to your [msdb] database. Then you can react in your activation procedure directly to the state change notification. Yo do not need any TCP or other communication, each mirroring partner can do this monitoring independently and each partner reacts to the local state change.
For a C# application to react to the notification simply have it monitor a queue (WAITFOR(RECEIVE...)) where, again, you have the system deliver event notifications for mirroring state change event. You can even use the external activation app.
The Pro SQL Server 2008 Mirroring book has an example showing exactly this technique.
Just don't use WMI... and absolutely don't connect to client back from server via TCP.


Can I move SQL Server backup files on a running database?

In general, yes, you can move the backup files.  Backup files are not in use by SQL Server once the associated backup is complete.  .BAK files are typically full or differential backups, and .TRN are typically transaction log backups, and they can all be moved.  It is certainly possible to use different file extensions for the backups, but it's not common.

Please be aware, though, that there may be other processes that use the backup files, such as log shipping or automated restores to non-production environments.  There is, of course, no way we can tell you what may be happening in your environment, so we can't tell you what might break if you move files.  If you are just moving the oldest files, then it's less likely you'll be interrupting something.


where are the infos from 'SHOW CREATE TABLE' coming from? It's not INFORMATION_SCHEMA

Ever since long, long, ago the info has come from the .frm file.  The I_S is relatively new.  In the future (5.8 maybe) the .frm will be replaced by an InnoDB-based table.

CHECK (and a few other things) are syntactically allowed but are not handled anywhere in the code.  (Another example is a DESC index.)  It could be that the .frm contains these strings, but there is no need for such unused information to be replicated in the I_S.


DBCC PAGE read page from buffer pool or directly from the file system?

We know that SQL Server always read pages from disk to memory for all its operations.

The pages are asked for from memory like most things in SQL Server. The same process applies for DBCC PAGE as it does for other read operations in SQL Server.


Moving SQL server 2016 DB's from mirroring to HA

Since you are on standard edition - you will be using Basic Always On availability groups for a single database.

  Once we move to HA with 1 listener for each DB, will this new configuration be any issue for me?

The only issue that I see is that you have to failover all the interrelated databases together (since your app has cross db calls e.g. joins, etc).

I don't see any other issues except the limitations highlighted here.


How to UPDATE/DELETE an Oracle Foreign Table with Foreign Data Wrapper from PostgreSQL?

As the documentation and the error say, you have to define the foreign table with the key column option for all columns that belong to the primary key. Without knowing what the foreign key is, it is impossible to identify the row in the remote table that should be updated or deleted (for INSERT, that is not necessary).

Here is an example for a table definition that sets the key option:

CREATE FOREIGN TABLE atable (
   id    bigint OPTIONS (key 'true') NOT NULL,
   value text
) SERVER oraserver OPTIONS (table 'ATABLE');

In this example id is the primary key.

You can use the IMPORT FOREIGN SCHEMA statement to have oracle_fdw define foreign tables for you, which will automatically set the key option as appropriate.

The ORA-08177 is a different affair. It can be that oracle_fdw has to scan an Oracle table several times during a single statement (for example, if the foreign table scan in on the inner side of a nested loop join), and oracle_fdw has to ensure that the data seen during these scans are consistent.

That would not be guaranteed with the default READ COMMITTED transaction isolation level, so oracle_fdw has to use SERIALIZABLE (which is not serializable, but guarantees read stability).

Now Oracle has done a seriously bad job implementing this so-called SERIALIZABLE isolation level. It is not downright buggy, because according to the book it is always allowed to throw a serialization error in a serializable transaction, but Oracle hat interpreted this quite freely and throws a serialization error whenever implementing read stability correctly would have proven too cumbersome.

For example, if a concurrent INSERT causes an index page split, a serializable transaction that tries to INSERT at the same time will receive a serialization error. This is of course silly. Similarly, any data modification on a table that has received a data modification from a concurrent transaction will result in a serialization error, even if they don't touch the same rows.

Your response should be, like with all serialization errors, to retry the transaction and hope for more luck next time.

Because this is a common problem, and the cries for a different solution have become so loud, I have recently pulled a patch that enables you to set an option isolation_level on the foreign server. You can set this option to read_committed to change the isolation level to the unsafe value READ COMMITTED.

This is not in a released version yet, but you can safely use Git HEAD if you want to try it. Use this unsafe option at your own risk. For INSERTs, nothing can go wrong, but you are not safe from inconsistent query results in the presence of concurrent data modifications.

A word of caution: while bulk data modification works with oracle_fdw, it is not very efficient, because there is a round trip between PostgreSQL and Oracle for each affected row. The reason for this is in the foreign data wrapper API. It would be difficult to work around that, and I don't think bulk data modifications to be such an important use case.


These are your options:

1 master with 2 slaves, standard replication. If you have long running or massive transactions you may want to stick with this.
1 master with 2 slaves + MHA to handle failover
2 masters (active-passive) with 1 multi-source replicating slave. Just to emphasize active-passive masters
3 node Percona XtraDB Cluster/MariaDB Galera Cluster, avoid these if you have long running or big transactions.
3 node PRM cluster

Others might have a better answer but IMHO these are good options.


Will an ALTER TABLE ADD COLUMN on a slave break replication?

I think that, if your purpose is to avoid locking, you should use pt-online-schema-change (a tool from Percona Toolkit) or Gh-ost (from GitHub).

Here is how pt-osc works:

Creates a ghost table identical to the original table, but empty
Runs ALTER TABLE on the ghost table
Creates triggers on the original table, which insert new rows in the new table, and updates and deletes them if they are already there.
It copies the rows in chunks, monitoring the server performance (it can slow down or pause its work if necessary)
Switch the table names

gh-ost is very similar, the main difference is that it creates no triggers, and uses the binary log instead to detect the changes to the original table.

To answer your question more directly - yes, it is possible to add a column on the slave. And most probably it will simply work, as long as it has a default value. But it is very likely that in the future you will regret this choice, because you'll need to do something that will break replication, like adding a column in the master. I recommend to sure to check the documentation and fully understand the dangers, before doing such a thing.


Create table from another table in Oracle Procedure

If you want to create a table (or do any other DDL) in a stored procedure, you'd need to use dynamic SQL

EXECUTE IMMEDIATE 'create table table_2 as select col1, col2, col3, col4 from table_1';

If you create the table dynamically, though, you'd also need to use dynamic SQL every time you wanted to subsequently query the table.  That generally makes your code much harder to build and test.  And you won't be able to have two sessions running the procedure at the same time since their create table statements would conflict with each other.

Taking a step back, in SQL Server, it is very common to create local temporary tables inside procedures.  It is very, very rare to do that in Oracle.  It rarely makes sense to try to port logic from SQL Server to Oracle (or vice versa) directly like this.  If you do, you'll find that Oracle is lousy at running SQL Server code (and SQL Server is lousy at running Oracle code).  You'd generally want to take the logic from SQL Server and figure out the best way to implement that logic in Oracle.

Oracle does have global temporary tables where the table definition is global but the data is local to your session (or transaction).  Potentially, you want to do

create temporary table table_2 (
  col1 number,
  col2 number,
  col3 varchar2(10),
  col4 date
);

and then insert into that table in your stored procedure rather than trying to create the table dynamically.


SQL Server Version - are hotfixes for previous versions implied?

All hotfixes are cumulative unless otherwise stated.

Random pick, http://support.microsoft.com/?Kbid=971409

  Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix.

The "otherwise stated" is like these examples where you have some dependencies (sorry. bit old, for SQL Server 2000 and SP4). http://support.microsoft.com/?Kbid=941203 and http://support.microsoft.com/kb/959420


I'm assuming this is a Unix question, and you're not using OEM Cloud Control etc. I tend to do this by using a temporary file (with a known name) that indicates whether the script is still running or not. For example:

#!/bin/bash

if [ -f /tmp/groupisrunning ] ; then
  # do not execute if the process is already running. This is controlled by the
  # the existence of the above file

  echo "Already running"

else

  touch /tmp/groupisrunning

   # DO YOUR WORK BELOW THIS LINE, eg:
  script1.sh
  script2.sh
  script3.sh
  rm -f /tmp/groupisrunning
fi

There are other ways of doing this (ps parsing, using a service), but I find this simple & reliable.


Building a query to copy user security settings to another user

You could try something like:

CREATE TABLE dbo.MyTable
(
    Logon_User VARCHAR(255)
    , Logon_Level INT
    , Form VARCHAR(255)
);

INSERT INTO dbo.MyTable VALUES
('ABOND', 1, 'COST')
, ('ABOND', 0, 'PRODUCTION')
, ('ABOND', 0, 'ACCOUNTING')
, ('ATEMPLE', 0, 'COST')
, ('ATEMPLE', 0, 'PRODUCTION')
, ('ATEMPLE', 0, 'ACCOUNTING');

UPDATE MyTable
SET Logon_Level = T.Logon_Level
FROM MyTable
    INNER JOIN MyTable T ON MyTable.Form = T.Form
WHERE MyTable.Form = T.Form
    AND T.Logon_User = 'ABOND'
    AND MyTable.Logon_User = 'ATEMPLE';

SELECT *
FROM dbo.MyTable;


Dealing with Mysql Replication and Functions

What you're seeing is completely unacceptable, and developers should not be expected to work around it.

You've suppressed it in your answer, and I don't know if you didn't notice it, or didn't consider it significant, or something is broken in your setup.  Here's what you should have seen:

mysql> insert into t1 values (uuid_short());
Query OK, 1 row affected, 1 warning (0.11 sec)

Notice the 1 warning.

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

So your simple fix is "don't use statement format."

Clean up those discrepant tables, and then...

mysql> SET GLOBAL BINLOG_FORMAT = MIXED;

Then, prepare yourself for what follows... because MIXED mode is more correct and deterministic than STATEMENT mode, it's also more likely to detect cases where your servers are already inconsistent, and replication will stop.  Don't be confused that MIXED mode has broken it... MIXED mode will just tend to uncover problems you already have.

The same thing goes for ROW format, which is even more aggressive at maintaining identical data, and is my preferred BINLOG_FORMAT for that reason, among others.

You also need to set this in your my.cnf file so that the setting persists if the server is restarted.

But once you aren't using STATEMENT mode any more, this issue should completely disappear.

http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html


Does a query with a primary key and foreign keys run faster than a query with just primary keys?

Query
SELECT something FROM table WHERE primary_key = ?

This the fastest possible form. Adding any other predicate can only make it slower. Theoretically.
Exotic exceptions apply, like when the PK index is bloated for some reason, or the PK column is relatively big, or a multi-column PK, resulting in a much larger index, while the index for the added predicate on other_key is smaller. Then Postgres may decide to use the index for the added predicate, access the heap and filter on primary_key = ?. Unlikely, but possible.
If the added predicate evaluates to anything but TRUE, you get no row - a different result, so not a fair comparison - but that's not your case as you asserted.
A FOREIGN KEY constraint has no direct impact on read performance. The referencing column does not even have to be indexed (as opposed to the referenced column).
Covering index for top read performance
With tables of non-trivial size and not too much write activity, consider adding a multicolumn index on (primary_key, something) to allow index-only scans. In Postgres 10 or older that results in at least two indexes (imposing additional write / maintenance / space costs):

the PK index on (primary_key), obviously.
a plain (or, redundantly, UNIQUE) index on (primary_key, something).

Postgres 11 added true covering indexes using the INCLUDE clause, which conveniently allows to piggyback the non-key column something on the PRIMARY KEY:
CREATE TABLE tbl (
   primary_key bigint GENERATED ALWAYS AS IDENTITY
 , other_key   integer NOT NULL REFERENCES other_tbl
 , something   text
 , PRIMARY KEY (primary_key) INCLUDE (something)  -- here's the magic
);

If primary_key happens to be a much wider column than other_key you mentioned (bigint vs. int like in the example would not qualify), you can also piggyback something onto an index on other_key:
CREATE INDEX other_idx ON tbl(other_key) INCLUDE (something);

While either solution can optimize read performance for the given query, other queries not retrieving something then have to work with a bigger index. So weigh benefits and costs (like always when creating indexes).

The manual on CREATE INDEX.

Related blog entry with details from Michael Paquier:

Postgres 11 highlight - Covering Indexes


what is the structure of a Mysql multi-column index?

Note: At the time this answer was written, the question was:

  In a B-Tree index, are all columns of a single row stored together, or
  are the columns stored in separate structures?

Most RDBMSs (including MySQL) implement indexes as B+Tree not B-Tree.
There are two types of B+Tree indexes: Clustered and Non-Clustered.

Both have in common that each intermediate level (including the root) of the index contains all columns that make up the key. The leaf level contains all key columns plus additional information.

In a clustered index, that additional information are all other columns of that table. For non-Clustered indexes, that additional information contains at least a reference to the actual table row. Some RDBMSs allow to also add additional columns here, so called "included" columns.

Either way, a B+Tree always contains the complete tuple relevant to the current index level within a single B-Tree node (which is usually equivalent to a data page. This storage organization is also called the "N-ary Storage Model" or NSM.

Some RDBMSs allow for column oriented storage, like SQL Server with the Column Store Index. That model is called the "Decomposition Storage Model" or DMS. However, a structure using the DSM is not organized as a B+Tree (or even a B-Tree).

There is a third form of storage that combines the two ideas. It is called PAX (partition attributes across). However it is rarely implemented.

References:

http://en.wikipedia.org/wiki/B%2BTree

http://www.pdl.cmu.edu/ftp/Database/pax.pdf

http://en.wikipedia.org/wiki/Column-oriented_DBMS


Copy mysql schemas to a new instance of a new version

You should be able to just mysqldump the individual databases and load them straight into a MySQL instance running on the latest version. I have recommended this before:

May 26, 2015 : mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?
Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
Apr 18, 2013 : Steps to upgrade MySQL from version 5.0 to version 5.5
Apr 11, 2013 : MySQL upgrade 5.0.88 to latest

When doing this, please do not move the grants by dumping the mysql schema. The number of columns have changes between versions :

Oct 10, 2014 : MySQL service stops after trying to grant privileges to a user
Jun 13, 2012 : Fastest way to move a database from one server to another

Try to extracts the grants with pt-show-grants or emulate it as suggested


How to enable CLR on a remote SQL Server?

SQLCLR, or technically the "CLR enabled" server configuration option, is a Server-level option, not Database-level. Since it sounds like you are using shared hosting with no server-level permissions, then the answer is just what the sysadmins told you: No.

Now, if you can convince them to enable it for everyone on the server, then you would be able to do this.

Or, you could possibly consider using SQL Server on Amazon RDS which allows for SQLCLR (SAFE Assemblies only -- I have successfully installed and used SQL# on Amazon RDS) and should have options that are not cost-prohibitive (though I don't know your specific requirements, but worth taking a quick look as they do have a "free" tier).


Should database be denormalized if I need an index across multiple tables

If you are getting the LOWER('Meteora Breaking The Habit') portion of the query from a source that it outside your control, then I would recommend keeping your song/album table structure (although remove album_id from song table), but include a 3rd table:

CREATE TABLE ALBUM_SONG (
  album_id,
  song_id,
  album_song_name -- this would be album.name || song.name
)

This is essentially a join table between the two for M-M relationships ('cause hey - the same song could be on multiple albums)..

Then you can index and query the join table on LOWER(album_song_name)


How can I get SQL Server Management Studio 2012 to connect to multiple servers automatically on open?

In Management Studio, go to the View menu and then Registered Servers. Register each of the servers that you want to keep connections for. Then when you open up Management Studio in the future, you just have to go to your register servers and click the ones you need. You can also use the Central Management Servers feature if there are multiple people on your team that would like to share the registered servers. You can group them in various ways, such as by version, by environment, by location, etc.


Data model
You are saving avg. and max. speed per race, not per driver? Normally this would be something like:

CREATE TABLE race (
  race_id   serial PRIMARY KEY
 ,race_date timestamp
);

CREATE TABLE driver (
  driver_id    serial PRIMARY KEY
 ,driver_name  text
 ,date_started timestamp
);

CREATE TABLE driver_race (
  driver_id int REFERENCES driver(driver_id)
 ,race_id   int REFERENCES race(race_id)
 ,average_speed numeric
 ,max_speed numeric
);

This is also how you should provide your data model: as CREATE scripts ready to use for testing.
numeric and decimal are the same in Postgres.
Query
Your query extended to all drivers:
SELECT driver_id, d.driver_name, x.slope
FROM   driver d
JOIN  (
   SELECT dr.driver_id
         ,regr_slope(dr.average_speed, EXTRACT(EPOCH FROM r.race_date)) AS slope
   FROM   race r
   JOIN   driver_race dr USING (race_id)
   WHERE  r.race_date BETWEEN '2013-3-1'::date AND '2013-3-31'::date
   GROUP  BY dr.driver_id
   ) x USING (driver_id)
ORDER  BY slope DESC;

Simple alternative
A simple alternative would be to match the average speed of the first half of a time period to the average of the second half:
SELECT driver_id, d.driver_name
      ,round(x.avg1::numeric, 2) AS avg1
      ,round(x.avg2::numeric, 2) AS avg2
      ,round((x.avg2 / x.avg1 - 1) * 100, 2) AS pct_change
FROM   driver d
JOIN  (
   SELECT dr.driver_id
         ,avg(CASE WHEN r.race_date BETWEEN '2013-3-01'::date AND '2013-3-15'::date
              THEN dr.average_speed END) AS avg1
         ,avg(CASE WHEN r.race_date BETWEEN '2013-3-16'::date AND '2013-3-31'::date
              THEN dr.average_speed END) AS avg2
   FROM    race r
   JOIN   driver_race dr USING (race_id)
   WHERE  r.race_date BETWEEN '2013-3-1'::date AND '2013-3-31'::date
   GROUP  BY dr.driver_id
   ) x USING (driver_id)
ORDER  BY pct_change DESC;

The CASE expression has no ELSE branch, because the default NULL serves just fine: avg() ignores NULL values.
-> SQLfiddle demo.


SQL Server Management Studio - Cannot connect - Method not found SqlConnectionInfo.set_IsActiveDirectoryUniversalAuth

Note that this answer will allow some operations, but you'll still have a broken environment.

IMPORTANT: This allows connecting to a database and running queries, but you'll still get errors inside SSMS!

I'm not saying it's pretty, but I can now use SQL Server Express 2016.

Copy the file Microsoft.SqlServer.ConnectionInfo.dll from the folder:

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio

to this folder (I strongly suggest you backup the file first, then you can overwrite the file):

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\13.0.0.0__89845dcd8080cc91

Why does it work?

Note that a Microsoft engineer would better answer this question, but here's my theory: for some reason, a different release of the same version of the Microsoft.SqlServer.ConnectionInfo assembly has been released publicly (e.g. as part of Visual Studio and SQL Server Express release). The one in the GAC did not contain the SqlConnectionInfo.IsActiveDirectoryUniversalAuth property, but SQL Server Management Studio Express expects it.

What we are doing here is just replacing the version in the GAC with the version that ships with SSMS Express, so that it loads this one instead. This will affect everything that depends on that dll file, but hopefully there's no breaking changes in an assembly that has the same version number...

How did I find this?

I found which dll file the class was specified in by searching the intertubes. I then used sysinternal's procmon to look at which version of that dll was in fact loaded. I searched my computer for other potential versions of that file, and simply tried disabling versions until I had a different error. I then tried replacing it with other versions in the SSMS Express installation folder, and it worked. So here we are!

Update

It's not solving everything though... here's an error I have when right-clicking on a database and selecting property.

Cannot show requested dialog. (SqlMgmt)
Method not found: 'Microsoft.SqlServer.Management.Common.DatabaseEngineEdition Microsoft.SqlServer.Management.Smo.SqlSmoObject.get_DatabaseEngineEdition()'. (SqlManagerUI)`

I have tried installing everything in the GAC without success either: ls "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\*.d
ll" | % { &"C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe" /if $_.FullName }


Database Design Review - How to model a business domain about posting groups?

I think it looks about right for the defined business rules that you name... There's probably more to it but your structure seems pretty much whats needed in this case.

I would probably separate the Categories altogether, and then add a new table to store categories per group that way you dont end up with a bunch of duplicated categories on that table.

Something like:


DBCC checkdb showing failure after a restore

Should I attempt to restore the database again or proceed with attempting to repair? I only hesitate because it is a rather large DB and restoring takes quite a while.

I'd probably do both, but restore it to a new name.

  All of the table errors are related to a single table I could just drop and recreate. However I don't understand what the IAM error really means or its impact.

If the data in that table really, truly doesn't matter, and you can really, truly drop and recreate the table, that's probably the fastest option.  Especially since "repair_allow_data_loss" will probably cause data loss, yes.

As for what the IAM error means:

Short version:  Your table innards are corrupted.

Longer version:  The Index Allocation Map tells SQL Server where to get the actual objects.  In this case, based on the references to RIDs I'm guessing this is a table without a clustered index (in other words, a heap), and that it's telling SQL where to get the data that was actively updated inside the heap.  (One of the things that happens when a heap has updates is that if the new information doesn't fit in the old space, a pointer is created that tells SQL where to get the new information.)

Here's an article on IAM pages, and here's a good video on heaps in SQL Server that came up when I was looking for IAM info for you.


Microsoft has confirmed that the dbo table is the only table that will be removed.  They recommend writing my own SP


NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

This behaviour is by design, as explained in detail on this Connect bug report.  The most pertinent Microsoft reply is reproduced below for convenience (and in case the link dies at some point):

  Posted by Microsoft on 7/7/2008 at 9:27 AM

  Closing the loop . . . I've discussed this question with the Dev team.
  And eventually we have decided not to change current behavior, for the
  following reasons:

  The optimizer does not guarantee timing or number of executions of
  scalar functions. This is a long-estabilished tenet. It's the
  fundamental 'leeway' tha allows the optimizer enough freedom to gain
  significant improvements in query-plan execution.
  This "once-per-row behavior" is not a new issue, although it's not
  widely discussed. We started to tweak its behavior back in the Yukon
  release. But it's quite hard to pin down precisely, in all cases,
  exactly what it means! For example, does it a apply to interim rows
  calculated 'on the way' to the final result? - in which case it
  clearly depends on the plan chosen. Or does it apply only to the rows
  that will eventually appear in the completed result? - there's a nasty
  recursion going on here, as I'm sure you'll agree!
  As I mentioned earlier, we default to "optimize performance" -
  which is good for 99% of cases. The 1% of cases where it might change
  results are fairly easy to spot - side-effecting 'functions' such as
  NEWID - and easy to 'fix' (trading perf, as a consequence). This
  default to "optimize performance" again, is long-established, and
  accepted. (Yes, it's not the stance chosen by compilers for
  conventional programming languages, but so be it).

  So, our recommendations are:

  Avoid reliance on non-guaranteed timing and number-of-executions
  semantics.
  Avoid using NEWID() deep in table expressions.
  Use
  OPTION to force a particular behavior (trading perf)

  Hope this explanation helps clarify our reasons for closing this bug
  as "won't fix".

The GETDATE and SYSDATETIME functions are indeed non-deterministic, but they are treated as runtime constants for a particular query.  Broadly, this means the function's value is cached when query execution starts, and the result re-used for all references within the query.

None of the 'workarounds' in the question are safe; there is no guarantee the behaviour will not change the next time the plan is compiled, when you next apply a service pack or cumulative update...or for other reasons.

The only safe solution is to use a temporary object of some kind - a variable, table, or multi-statement function for example. Using a workaround that appears to work today based on observation is a great way to experience unexpected behaviours in future, typically in the form of a paging alert at 3am on Sunday morning.


Select rows from table having specific values depending on another values in the same table

SELECT t1.*
FROM mytable t1
WHERE t1.Tr_type = 7
  /* AND another conditions by Userpin list, TrDate, etc. */
  AND NOT EXISTS ( SELECT NULL
                   FROM mytable t2
                   WHERE t1.Userpin = t2.Userpin
                     /* AND another conditions */
                     AND t2.Tr_type = 8 )


When the utility runs, just have it (or whatever calls the utility) run this command:

DISABLE TRIGGER dbo.trigger_name ON dbo.table_name;

And then afterward:

ENABLE TRIGGER dbo.trigger_name ON dbo.table_name;

It would be better to have the utility do this just around the statement(s) affecting this table specifically (and wrap that in a transaction), since the trigger is disabled for all users, not just the utility.

Another way - if the utility passes a specific application name in the connection string, or is known to be the only thing connecting as a certain user or from a certain host - is to check sys.dm_exec_connections/sys.dm_exec_sessions within the trigger.

ALTER TRIGGER dbo.trigger_name
ON dbo.table_name
FOR INSERT, ...
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS
  (
    SELECT 1 FROM sys.dm_exec_connections
    WHERE client_net_address = '10.65.10.5' AND session_id = @@SPID

    /* or
    SELECT 1 FROM sys.dm_exec_sessions
    WHERE host_name = N'MELVIN' AND session_id = @@SPID
    */

    /* or
    SELECT 1 FROM sys.dm_exec_sessions
    WHERE program_name = N'My Utility App Name' AND session_id = @@SPID
    */
 )
  BEGIN
    -- trigger logic here
  END
END

If the utility logs in as a specific login separate from the main app, then you could say:

IF USER_NAME() <> N'user name' /* or SUSER_SNAME() or ORIGINAL_LOGIN() */
BEGIN
  -- trigger logic here
END

If the utility always handles multiple rows, and the main app always only handles one row, then you could do this:

IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN
  -- trigger logic here
END

If the utility could call a stored procedure instead of using ad hoc SQL, your life would be a lot easier, because you could lock the table in a transaction, disable the trigger, do your thing, then enable the trigger again. (Having this control is yet another benefit of using stored procedures and not compiling your SQL code into an app.)

If you can't fix any of these things, then you may want to look into disabling the trigger and achieving its goal in some other way. Without understanding exactly what the trigger does, it's difficult to be any more precise than that.


You can use sp_refreshview to do it.  I've put this little script together that will select all views in a database, run sp_refreshview against it and tell you which ones had a problem.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshview-transact-sql?view=sql-server-2017

DECLARE @Objects TABLE
    (
    OID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , FQN NVARCHAR(256) NOT NULL
    , IsIssue BIT NOT NULL DEFAULT (CONVERT(BIT, 0))
    )

DECLARE @SQLCommand NVARCHAR(4000)
DECLARE @VName NVARCHAR(256)
DECLARE @OID INT

INSERT INTO @Objects
    (FQN)
SELECT QUOTENAME(S.name) + '.' + QUOTENAME(V.name)
FROM sys.views AS V
    INNER JOIN sys.schemas AS S ON S.Schema_id = V.schema_id

DECLARE curLoop CURSOR LOCAL STATIC FORWARD_ONLY
FOR SELECT OID, FQN
    FROM @Objects AS O

OPEN curLoop

FETCH NEXT FROM curLoop
INTO @OID, @VName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQLCommand = N'EXEC sp_refreshview N' + QUOTENAME(@VName, '''')

    BEGIN TRY

        EXEC sp_executesql @SQLCommand

    END TRY
    BEGIN CATCH

        UPDATE @Objects
        SET IsIssue = 1
        WHERE OID = @OID

    END CATCH

    FETCH NEXT FROM curLoop
    INTO @OID, @VName

END

CLOSE curLoop
DEALLOCATE curLoop

SELECT OID, FQN, IsIssue
FROM @Objects
WHERE IsIssue = 1


mysql 5.6 flushing all dirty pages on ib_logfile rotation creating checkpoint stall

The sentence about requiring a checkpoint before the log file rotation/reuse may have been removed from documentation, but the logic hasn't changed in version 5.6 -- you cannot overwrite log records that belong to not-yet-persisted data, so you have to checkpoint still. The fact that it is happening in your environment indicates that flushing of dirty pages with fuzzy checkpoints is not aggressive enough for your workload.

I'd try raising the innodb_adaptive_flushing_lwm threshold and lowering innodb_max_dirty_pages_pct to make page cleaning more proactive.

There are more hints and links in this article


Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

With InnoDB tables, all secondary indexes include the columns of the clustered index (which is the primary key), appended in the end. So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column.

When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose any one of the two indexes. In fact, as explained in InnoDB Table and Index Structures, the clustered index contains some additional information that costs 13 more bytes per row:

  Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

This makes your unique index less wide than the primary key index, which is why it is chosen by the optimizer.


What is the meaning of "Monotonically Changing Shard Keys"?

Monotonic here is used in the strict mathematical sense. A monotonically changing key is such that every key value is not less than (monotonically increasing) or not greater than (monotonically decreasing) all previously generated keys. A sequence, in the SQL sense of this word, would be an example of a monotonic key.

A non-monotonically changing key, conversely, is such that its value could be less than, greater than, or equal to any of previously generated values. A random value would be an example of that.

A monotonically changing key used for sharding causes newly inserted documents to be routed to a single shard, creating a performance hotspot. With a non-monotonic key writes will be distributed across all shards (hopefully), thus improving performance.


DB Links can be configured with different accounts for each DB Link. But it would still be an ugly mess as far as security and management.


Restore DIfferential backup on azure managed instance?

Question: Can I restore a differential backup on azure managed instance?

No. Borrowing from my answer here, Managed Instance only supports the restore of full backups; no log or diff. See this thread:

Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup

And the limited syntax diagram for RESTORE in the documentation for Managed Instance:

RESTORE Statements (Transact-SQL)

The docs imply that RESTORE ... FROM URL will implicitly add WITH RECOVERY and so there will be no opportunity for logs or diffs to be applied after the initial restore. So your downtime will be >= the time it takes to take a COPY_ONLY full backup, put it somewhere MI can access, restore it, and re-point your app(s). You might also want to read through this tutorial, for a different approach.


Any way to provide Sqlplans to end users who don't have a DBMS

They don't require SQL Server full installation to view the execution plans. They just need the tools/client software, which they should realize this if they want to look at them.

They can use the following to view a plan file:

Download SSMS
SentryOne Plan Explorer


problem to join several tables without relation

While there isn't enough information to be certain this is your problem, it seems likely since you mention joining many tables.

When you have joins to a number of tables, you can wind up with redundant data in your results. Here's how it works.

Sometimes, a number of joins may be fundamentally linear. For example:

SELECT cust.Name, SUM(prod.Price * oline.Qty) as totalPriceCharged
  FROM Customer cust
         INNER JOIN Order ohead ON (cust.custNo = ohead.custNo)
           INNER JOIN OrderLine line ON (ohead.orderNo = oline.orderNo)
             INNER JOIN Product prod ON (oline.prodID = prod.prodID)
 WHERE ohead.orderDate >= '2018-10-01'
   AND ohead.orderDate <  '2018-11-01'

This query would be intended to show the total price of goods ordered by each customer who placed an order in October of 2018. Each Order ties to a Customer; each OrderLine to an Order; and each Product to an OrderLine entry. Each of the four tables is a part of each row of the output; this is simple, because every row returned from Product is tied to a specific row from OrderLine, and so on. In effect, every table involved has a specific relationship with every other table involved, through that chain of joins:

However, you also have non-linear joins:

SELECT cust.Name
      ,SUM(empTime.Hours) as totalHrs
      ,SUM(inv.Total) as totalBilled
  FROM Customer cust
         INNER JOIN EmployeeTime empTime ON (cust.custNo = empTime.custNo)
         INNER JOIN Invoice inv ON (cust.custNo = inv.custNo)

Presumably, the goal of this statement would be to see the total amount charged to each customer, versus the total hours employees worked for that customer (yes, we've just changed from provided products to services, apparently).

However, this query won't give the person who wrote it back what they want.

Unlike our previous query, the only connection between EmployeeTime and Invoice is the custNo that links those tables to Customer. Each row returned has to represent one row from Customer, one from EmployeeTime, and one from Invoice. Let's assume our company is just starting out:

Customer:
 custNo |     Name
--------+--------------
    1   | AAA Chemical
    2   | ZZZ Cleaning

EmpTime:
 custNo |  Hours
--------+---------
    1   |   2.0
    1   |   2.5
    2   |   5.9
    2   |   1.1
    2   |   3.0

Invoice:
 custNo |   Total
--------+-----------
    1   |   100.00
    1   |   125.00
    2   |   295.00
    2   |   205.00

So, the expected results would be:

 custNo |  Hours  |   Total
--------+---------+-----------
    1   |   4.5   |   225.00
    2   |  10.0   |   500.00

What you'd actually get would be:

 custNo |  Hours  |   Total
--------+---------+-----------
    1   |   9.0   |   450.00
    2   |  20.0   |  1500.00

Why?

Again, each row from our result set (before we do our SUMs) has to contain one row from each of the three tables. And, the only way that EmployeeTime and Invoice are connected are through their connection to custNo.

So, how does the database engine know which EmployeeTime rows to connect to which Invoice rows? By the custNo, of course.

Giving us:

 custNo |     Name     |  Hours  |   Total
--------+--------------+---------+-----------
    1   | AAA Chemical |   2.0   |   100.00
    1   | AAA Chemical |   2.0   |   125.00
    1   | AAA Chemical |   2.5   |   100.00
    1   | AAA Chemical |   2.5   |   125.00
    2   | ZZZ Cleaning |   5.9   |   295.00
    2   | ZZZ Cleaning |   5.9   |   205.00
    2   | ZZZ Cleaning |   1.1   |   295.00
    2   | ZZZ Cleaning |   1.1   |   205.00
    2   | ZZZ Cleaning |   3.0   |   295.00
    2   | ZZZ Cleaning |   3.0   |   205.00

as our underlying data to feed to the SUM functions.

Each row from EmployeeTime for a given Customer has been matched against each row from Invoice for the same customer. This is called a cross-product.

How do you avoid this?

You make linear joins, where the relationship between each table is well-defined, and where we don't need cross-products to fill things out (not an option, for this query);
You prevent the tables that don't have well-defined connections from "joining" more than one row; or
you return a simple set of calculated values, instead of doing joins.

The last two options tend to look very similar; they'll both rely on subqueries to get our values.

Here's our previous query, where we're just joining single rows:

SELECT cust.Name
      ,hours.totalHrs
      ,inv.totalBilled
  FROM Customer cust
         INNER JOIN (SELECT custNo, SUM(Hours)
                       FROM employeeTime
                      GROUP BY custNo
                    ) hours
         INNER JOIN (SELECT custNo, SUM(Total)
                       FROM Invoice
                      GROUP BY custNo
                    ) inv

Technically, this still does a cross-product; however, 1 * 1 = 1

This should work just as well if you're using LEFT OUTER JOIN (or RIGHT...) instead of INNER JOIN.

Or, we just avoid the join altogether, and calculate the totals using suBqueries in the SELECT list:

SELECT cust.Name
      ,(SELECT SUM(Hours) FROM EmployeeTime WHERE custNo = cust.custNo) as totalHrs
      ,(SELECT SUM(Total) FROM Invoice WHERE custNo = cust.custNo) as totalBilled
  FROM Customer cust

Either method should work, if this is indeed the root of your problem.


Exclude Generated Objects - SQL Developer

There is no such thing. Unfortunately you can not create fully customized filters, you can use only the predefined ones. Even if you could, there is no reliable way of filtering built-in objects. Starting with 12c, there is a new column in the USER|ALL|DBA_OBJECTS views, called ORACLE_MAINTAINED, but we can not rely on that, because its is not accurate (for example, AWR objects or some built-in directories and queues are not accounted as maintained by Oracle).

As it was already mentioned, do not use SYS, that is for administrative tasks. Create your own user, and log in as that user.


mysql insert into indexed table taking long time after few million records

You should load such a huge file in chunk for faster loading of data,
Here it is mentioned problems loading huge Load local data file and solution how to solve it and make it faster.

http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/

MyISAM tables you should follow this steps to fasten loading data:

With some extra work, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes.

Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.
Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name to remove all use of indexes for the table.
Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.
Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.
Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.

LOAD DATA INFILE performs the preceding optimization automatically if the MyISAM table into which you insert data is empty. The main difference between automatic optimization and using the procedure explicitly is that you can let myisamchk allocate much more temporary memory for the index creation than you might want the server to allocate for index re-creation when it executes the LOAD DATA INFILE statement.

You can also disable or enable the nonunique indexes for a MyISAM table by using the following statements rather than myisamchk. If you use these statements, you can skip the FLUSH TABLE operations:

ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS;

For more information refer these:

http://dev.mysql.com/doc/refman/5.5/en/optimizing-myisam-bulk-data-loading.html

https://wiki.rice.edu/confluence/display/~as43/Make+LOAD+DATA+INFILE+run+even+faster+for+a+MyISAM+table


how to select rows where for a column there are two rows one with null and another with non-null value then select only row with non-null value

SELECT Column_A, MAX(Column_B) Column_B
FROM test
GROUP BY Column_A;

fiddle


How to enforce SQL Server policies in order to avoid users enabling bad practices

I assume you are referring to Policies in SQL Server here. Some types/level of policies, you cannot prevent. Technically, to "prevent" something using a policy in SQL Server means that the operation is performed, but the policy engine then does a rollback. Not all operations are rollback-able, hence the prevent functionality doesn't exist for all policy types.
So, in the end, you need to work out who has sysadmin privileges and make sure that only trusted persons has. In the end, this might have to go higher in the organization to get support.


The Windows Server Failover Clustering (WSFC) resource control API returned error code 19

Here are the details of that error code (19):

ERROR_WRITE_PROTECT
19 (0x13)
The media is write protected.

You need to consult the cluster log (use Get-ClusterLog to get that) for additional details about what writes failed within the cluster operation being performed.  Check that out and update your question with any errors you see.
That being said, combined with this symptom:

...if you reboot second node, database wont up.

You might be experiencing disk problems.  Check the Windows system event log and SQL Server error log for messages related to failed writes or corruption.


Mysql Slave Claims to be Running but New Records Not Appearing and No Delay

When running an intermediary replication that is also a master, log-slave-updates needs to be enabled to ensure that the upstream master, replicates to the downstream slave.


with (tablock) does not seem to make code faster for insert select

How many rows are you inserting at one time? Despite what the comments say, minimal logging does affect performance, but it's very minute and maybe a moot thing to worry about.
Using the TABLOCK query hint moreso helps with large data insert performance because it tells the SQL Optimizer to take a Table Lock upfront, as opposed to going through Lock Escalation which can be costly at runtime for large batches of data.
Generally without that hint, an insert might cause a bunch of Row Level Locks to occur first, and then when the SQL Engine realizes it crossed a certain threshold of Row Level Locks, it escalates to a Table Lock anyway, which means it wasted it's time spinning it's wheels acquiring Row Level Locks to start with. This is why for a large insert of rows, using that hint can be helpful.
Typically you need to be inserting at least a certain number of rows (I believe at least 102,400 rows per parallel thread executing the insert) for the TABLOCK query hint to be helpful with performance, otherwise it doesn't matter or in some cases can even hinder performance as Erik Darling wrote up in this interesting article.


SQL Server Management Studio - Processor counters for different sessions do not add up

You are measuring CPU time, not real SQL Server query execution time or real CPU usage. Why do you expect these numbers to add up?
You have to use SQL Profiler or Extended events to capture CPU usage by those queries.


postgres: http://rextester.com/TAYO71756

create table menutable (
    id int primary key
  , menuadi text
  , ustmenuid int null references menutable (id)
);
  insert into menutable values
 (1,'Ortak Konulu Mansetler',null)
,(2,'Kayseri Deki Bombalı Saldırının Yansımaları',1)
,(3,'Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları',1)
,(4,'Ortak Haberler',null)
,(5,'Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları',4)
,(6,'Türk Meslektaşlarımızın Yanındayız',4);

select mt1.*, json_agg(mt2.*)
from menutable mt1
join menutable mt2 on mt1.id = mt2.ustmenuid
group by mt1.id;

postgres results

+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
| id |        menuadi         | ustmenuid |                                                       json_agg                                                        |
+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
|  1 | Ortak Konulu Mansetler | NULL      | [{"id":2,"menuadi":"Kayseri Deki Bombalı Saldırının Yansımaları","ustmenuid":1}                                       |
|    |                        |           | , {"id":3,"menuadi":"Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları","ustmenuid":1}]               |
|  4 | Ortak Haberler         | NULL      | [{"id":5,"menuadi":"Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları","ustmenuid":4} |
|    |                        |           | ,  {"id":6,"menuadi":"Türk Meslektaşlarımızın Yanındayız","ustmenuid":4}]                                             |
+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+

sql server 2014: http://rextester.com/DPK57600

rextester is currently running sql server 2014, which does not have native json support.
Instead, I will use dbo.FlattenedJSON() from Producing JSON Documents from SQL Server queries via TSQL - Phil Factor

create table menutable (
    id int primary key
  , menuadi nvarchar(128)
  , ustmenuid int null references menutable (id)
);
  insert into menutable values
 (1,'Ortak Konulu Mansetler',null)
,(2,'Kayseri Deki Bombalı Saldırının Yansımaları',1)
,(3,'Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları',1)
,(4,'Ortak Haberler',null)
,(5,'Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları',4)
,(6,'Türk Meslektaşlarımızın Yanındayız',4);

select mt1.*, [json_agg]=dbo.FlattenedJson (
      ( select mt2.*
        from menutable mt2
        where mt2.ustmenuid = mt1.id
        for xml path, root
        )
    )
from menutable mt1
where mt1.ustmenuid is null

results:

+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+
| id |        menuadi         | ustmenuid |                                                         json_agg                                                          |
+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+
|  1 | Ortak Konulu Mansetler | NULL      | [                                                                                |
|    |                        |           | {"id":"2","menuadi":"Kayseri Deki Bombali Saldirinin Yansimalari","ustmenuid":"1"},                                       |
|    |                        |           | {"id":"3","menuadi":"Sayin Cumhurbaskanimizin Trabzon Daki Açiklamalarinin Yansimalari","ustmenuid":"1"}                  |
|    |                        |           | ]                                                                                |
|  4 | Ortak Haberler         | NULL      | [                                                                                |
|    |                        |           | {"id":"5","menuadi":"Sayin Hanimefendinin Trabzonda Sehit Aileleriyle Bir Araya Gelmesinin Yansimalari","ustmenuid":"4"}, |
|    |                        |           | {"id":"6","menuadi":"Türk Meslektaslarimizin Yanindayiz","ustmenuid":"4"}                                                 |
|    |                        |           | ]                                                                                |
+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+


How can we add BOOLEAN datatype in mysql query browser?

BOOL and BOOLEAN are synonyms of TINYINT in MySQL. See MySQL docs: Numeric Type Overview

You probably want to use the BIT datatype.


ALTER innoDB table from row to page compression

I tried different methods and read many articles. The standard way to do so is
ALTER TABLE t1 KEY_BLOCK_SIZE=0 `PAGE_COMPRESSED`='ON';

However, this will leave KEY_BLOCK_SIZE on all keys.
Some say this is just the SHOW CREATE TABLE display, as KEY_BLOCK_SIZE=8 standards for the largest block size for the keys, but the value is exactly that of the COMPRESSED table. If the table was created with KEY_BLOCK_SIZE=1, keys will have KEY_BLOCK_SIZE=1 after uncompression.
Some, on the other hand, say this bug has been fixed in MySQL 8, but I confirm it exists in MariaDB 10.5.9.
VERDICT
ALTER is a clean way, but I favour INSERT INTO SELECT for two reasons:

ALTER writes the uncompressed table on disk while INSERT writes the compressed pages on the disk.
We can create new table on a separate disk to reduce I/O.


PostgreSQL update JSONB without jsonb_set

Update: If the result value of jsonb is the same, then the only difference is jsonb_set would take additional CPU (and ms) to run. In both cases you run SET column = VALUE, but if resulted jsonb value is bifferent,both your statements are very much different, here is example.

sample:

t=# create table so63(j jsonb);
CREATE TABLE
Time: 6.290 ms
t=# insert into so63 select '{"a":0,"b":true}';
INSERT 0 1
Time: 1.137 ms

jsonb_set

t=# update so63 set j = jsonb_set(j,'{a}','[2,3,4]');
UPDATE 1
Time: 1.699 ms
t=# select j from so63;
              j
-----------------------------
 {"a": [2, 3, 4], "b": true}
(1 row)

jsonb value changed at specified path! And now with update .. set:

Time: 0.278 ms
t=# update so63 set j = '{"a":[2,3,4]}';
UPDATE 1
Time: 0.918 ms
t=# select j from so63;
        j
------------------
 {"a": [2, 3, 4]}
(1 row)

Time: 0.241 ms

whole jsonb is overwritten. not just "a" key