The question asked by yes123 is very relevant in the sense that we all want to know what we can do to improve MySQL's performance without touching the application that's consuming that data.  What is that perfect configuration that will just make everything better?  Although no one program can just spit out that perfect configuration, there are ways to dramatically improve your server's efficency without touching one piece of code.  For starters, I begin with the server's health and what kind of data that server was destined to provide.

Knowing the health of your MySQL database server and how to better configure it must include many factors based on a large number of variables.  What is your MySQL server being used for, a blog, e-commerce, reporting?   What are the principal engines you are using, InnoDB, MyISAM?  What is the OS, Windows, Linux, Unix?  Is there replication involved?

This is important to know because the way you configure MySQL depends on all of these factors and also on the health of the machine that MySQL is installed on.  All to often, people are quick to blame MySQL for a problem that is system wide.   If you setup Magento on a server that only has 1 CPU, 1Gb of ram and and you run out of memory because you have 1000 simultaneous competing on Apache, is it MySQL's fault? Obviously not.

So before you start asking yourself, how is MySQL doing, ask yourself, how is the server its installed on doing?

When dealing with a Linux server for example, here are the steps I follow to perform a general health check

Is there enough RAM? Answer: free -m
Is there enough disk space where MySQL is installed, typically /var/lib/mysql. Answer: df -h
What is the load average, is the CPU or CPUs at 100%.  Answer: top or htop
How much traffic is there on the network.  Answer: bwm-ng

For a Windows Server, I'm going to use perfmon or process explorer.

If I don't have any disk space left, there's no ram left and/or your network bandwith is saturated, it may not be MySQL's fault. Since MySQL stopped working or is timing out,
I may think that MySQL is the cause when its really just the victim of another problem

A very good graphical program for linux that's free is called ksar http://sourceforge.net/projects/ksar/.  You can use ksar to produce graphs based on the statistics collected by the sar service.  sar can be easily installed on both debian and centos and collects statistics about memory, cpu, disk, network usage.  All by itself sar provides you with that information in a numerical report.  ksar goes one step further and puts that information into time based graphs that help to visualize the health of your server at any given point in the day or from any given day in the past.

In Windows,
you need something that could do the same thing, like Process Explorer http://technet.microsoft.com/en-us/sysinternals/bb896653 or perfmon.  ksar and process explorer are both free.

Once I've eliminated the server or other applications such as Apache as possible culprits, that's when I start looking at MySQL.

I'll start with the MySQL's error log if there is one, and I'll look for disk space errors, or table corruption errors or other messages that stand out.  If I don't see anything out of the ordinary, I go to my favorite tool Jet Profiler http://www.jetprofiler.com.

Jet Profiler provides you with real time statistics on the state of your MySQL server over a given period of time. This is much more useful than just a simple snapshot of activity at any given moment.  Knowing how your server behaves during low and high peak periods of time will ultimately shed light on the weaker areas of your infrasture and that's where Jet Profiler comes in.  Its analysis and statistics are based on a server's workload over time.

Find problematic queries

As a first step, I try to identify which the problematic queries are. Check top queries and slow queries, and compare the relative thread load of each of them. Probably, a few of them will stand out clearly from the rest (according to the 90/10 rule or so). These are the queries to focus on, because they will give the most performance improvement per fix.

Load Pattern

The load pattern could also be that the server is doing well for the most time, but with recurring peaks from time to time. It will be very helpful to zoom in on the peaks to discover which queries are heavy during that timeframe. I.e. if you have heavy cron jobs which run once per day, they might not show up too high on the top queries for the last 24 hours so you might not think about them. However, they can still cause so much load and congestion that the server gets really slow precisely at midnight. One database I profiled had this pattern - the developers had added daily cron jobs which did various cleanup, archiving and statistical calculations. The problem was that all of the jobs started at 0 0 * * *. While the server could have handled one or two of these jobs and still serving regular web site visitors, 5+ concurrent jobs was too much. So the site went down for a minute or two every midnight. But those queries probably wouldn't have shown up unless we looked at that specific peak.

Going further, I move on to statistics specific to the engine that my MySQL tables are using MyISAM or InnoDB.

Before though, I ask myself one important question.  Is the database mostly InnoDB tables or MyISAM tables?

    use database;
    show table status \G;

*************************** 148. row ***************************
           Name: sometable
         Engine: InnoDB

Many production databases in MySQL become a mixture on InnoDB and MyISAM.  This requires an optimization of MySQL for both engines.

InnoDB Specific Information

Buffer Pool Hit Rate :  This should typically be at 100%.  If it isn't, MySQL is spending a lot of its time reading the disk instead of memory to retrieve data.  Increasing the buffer pool,
may resolve this problem.
InnoDB disk I/O statistics.  Is the server having disk problems, maybe I need to upgrade to something faster, if InnoDB is writing gigabytes of data to the server.

MyISAM specific information

Locked queries: Queries can easily block eachother in MyISAM tables due to locking. Jet Profiler will show you which queries are locked and on what tables.  You
can then easily resolve this probem by switching the table engine from MyISAM to InnoDB.
MyISAM cache: This should also be as close to 100% as possible.  If not, you can increase the key buffer cache to resolve the problem and/or build better indexes.

Next I look at what's going on globally.

Global Factors

States: What is MySQL doing?  Is it copying data to temporary tables, is it sending data across the network or maybe its sorting. In other words what processes in MySQL are using up the most CPU, memory and IO.
Slow queries:  What queries are taking the most of the CPU's time.  You can also get this by activating the slow query log on the server, but this would impact the server,
because it would be writing those queries to a disk.
Number of simultaneous queries/users.  Perhaps there are just too many users on my little server.

Correlating KSAR and Jet Profiler statistics

With Ksar's and Jet Profiler's graphs, you can actually correlate server performance charts with the MySQL's performance charts.   How is the server behaving when MySQL starts running that batch job.  If you notice a large number of major page faults in KSAR right when MySQL is processing that huge import that you see running as a Top Query in Jet Profiler right when MySQL is serving 1000 simultaneous users, you could just simply run your import when there aren't so many incoming connections.

Then there are other factors like, is the skip-name-resolve feature turned on.  If MySQL has to spend its time resolving domain names, its spending less time retrieving data. I also look at the Query Plan Cache to make sure that's also at 100%

As far as web solutions go.  I am beginning to discover the joys of Zabbix http://www.zabbix.com/.  It is geared towards monitoring multiple servers simultaneously but is capable of providing very precise detail about MySQL and all types of servers and technologies.

It is possible to have that perfect configuration.  With a thourough understanding of your OS, an intelligent analysis of your server's health, the queries its processing and the kind of workload your are asking it to handle you can focus on just those areas that need your attention.  Just adding a couple of gigabytes to your buffer pool or getting a faster disk might improve your server's performance, but why spend money on ram and faster disks, when the solution might just be spending a couple of hours on analyzing your server's workload and how its behaving even when your not there to monitor it.


Views created by SQL server Database Tuning advisor

The short answer is that it's impossible to tell, but it's not terribly likely.

Views without indexes are just obfuscated queries. Unless you've written queries directly against those views, there's generally no advantage for the optimizer to decide to use them.

The safest thing to do would be to

Script their definitions
Script the DROP commands you plan to use
Check them into change management or source control

That way, if you drop any, and something odd happens (like queries failing), you can add them back. If you have a dev/QA environment, make the changes there first and let them burn in for a week.

I'm not sure why you brought up index creation speed. If you're under the impression that creating a view that isn't indexed requires some physical structure gets built, it doesn't.

On the flip side, the views created by DTA are often created with the SCHEMABINDING option, which makes altering the underlying tables and columns that they touch throw errors that are confusing to track down.

Hope this helps!


MongoDB sharding and zone tag that has a range of 1 key value

Your understanding is correct: a chunk can only be associated with a single shard. A chunk range representing a single shard key value will be an indivisible chunk, so assigning this range to multiple shards will not result in data distribution across multiple shards.

If the data in this chunk eventually exceeds the chunk size limit (64 megabytes by default), the chunk will be flagged as a jumbo chunk and will be ignored for future migration activity.

Shard keys with limited cardinality (or outliers with high frequency) should generally be avoided as they will lead to data imbalance and performance challenges.


No there is no global variable for the config file locations. mysqld --help --verbose is best to show which ones are used.

Take a look at the linux processlist to see how mysqld is executed by  init/service/systemd, that might specify a file.

Otherwise you might be running on a default config with no configuration file.


When to cluster a table using an index? [postgresql]

If the index that you use for clustering is on column col of table public.tab, run
SELECT correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'tab'
  AND attname = 'col';

If that value is 1.0, the order is perfect, if it is close to 0, the order has rotted. You best use trial and error to find from what value of correlation on performance gets bad.


Setting and Changing the Server Collation after installation

Based on Sean's comment on the question, it sounds like there is a requirement that the Server / Instance -level Collations be the same. But, even if you could technically set up the Availability Group with differing Server default Collations, you wouldn't want to do that as you could easily experience unpredictable / inconsistent behavior when there is a fail over, such as sorting and/or comparison differences.

You could also have code breaking. For example, if the primary system is case insensitive and you have casing differences in variable names, that code will break if moving to an instance having either a case sensitive or binary Collation. This is due to Server-level Collation affecting variable names  (including cursor variable names).


to disable query cahche you need add the following prameters to cnf file:

query_cache_size=0
query_cache_type=0

to skip external lock you need to comment or remove skip-external-locking

and to disable delay key write you need to add the following parameter

delay-key-write= OFF


Why does key "concatenation" help to bring an unnormalized table to 1NF?

Using the schema shown, you are GOING to have multiple instances of the same PART_NUM, but the ORDER_NUM will be different for each of those. The key is the unique combination of ORDER_NUM and PART_NUM.

The composite key will permit one record per unique combination of order and part. A specific part can be associated with any order. Presumably the NUM_ORDERED column specifies the quantity of that particular part on that order.

Personally, I might use a surrogate key with a somewhat different schema. But that isn’t what your textbook is trying to illustrate in this case.

OP's Comment

  I realized the uniqueness gained by order and part making up the primary key. I guess I'm still wondering if/how that actually gets rid of repeating groups (the whole point of 1NF). I definitely see an improvement in the design, but it feels like my book is leaving out part of the explanation.

Comments incorporated into the answer

I dislike the design in your textbook. I would use 3 tables: orders, parts, and orders_parts (a many-to-many join table). The order and the part are defined one time each, then referenced from the join table to put parts on orders.

There is more to a part and an order than just the id. All of that definition data is in the parts and orders tables, specified just once. The ids are then referenced in the orders_parts join table, where they are foreign keys. The join table is just keys. It lets you put multiple parts on an order, and put the same part on multiple orders, without any redundant data.

Don't let the repetition of keys throw you. The data is not repeated. You don't define any part more than once. You don't define any order more than once. But you do reference the same parts and orders multiple times. In the example just below, Part 10 is referenced on Orders 1 and 2. Part 11 is referenced on Orders 1 and 3, etc.

Order | Part
--------------
  1      10
  1      11
  1      12
  2      10
  2      12
  3      11

The two foreign keys combined are also a composite key (unique and/or primary), if the design calls for it. This is what makes the database relational.


Partition of a table - how to minimize the time offline? How to check if all the statistics were updated?

from the technical article Statistics Used by the Query Optimizer in Microsoft SQL Server 2008statistics are out of date when:

  The table size has gone from 0 to >0 rows.
  The number of rows in the table when the statistics were gathered      was 500 or less, and the colmodctr of the leading column of the
  statistics object has changed by more than 500 since then.
  The table had more than 500 rows when the statistics were gathered,    and the colmodctr of the leading column of the statistics object has
  changed by more than 500 + 20% of the number of rows in the table
  when the statistics were gathered.

Moreover you can use dmv sys.dm_db_stats_properties to check when stats were last updated

SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications"
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'table_name_here');

Also suggest you to go through article Understanding When Statistics Will Automatically Update


How can I update values based on the value from the previous day?

If you are on MySQL >= 8.0
You can take advantage of LATERAL derived tables.
SELECT
    Act.SystemID,
    Act.ReadingDate,
    Act.Reading,
    Act.Diff,
    Prev.Reading as PrevReading
FROM
    Readings AS Act,
LATERAL
    (SELECT   Reading
     FROM     Readings
     WHERE    SystemID = Act.SystemID
              AND ReadingDate < Act.ReadingDate
              AND Reading IS NOT NULL
     ORDER BY SystemID, ReadingDate DESC
     LIMIT 1) Prev

ORDER BY
    Act.SystemID, Act.ReadingDate;

In this case it returns the previous reading that is not null.

SystemID | ReadingDate | Reading | Diff | PrevReading
-------: | :---------- | ------: | ---: | ----------:
       1 | 2021-02-02  |      26 | null |          24
       1 | 2021-02-03  |      23 | null |          26
       1 | 2021-02-04  |    null | null |          23
       1 | 2021-02-05  |    null | null |          23
       1 | 2021-02-06  |      28 | null |          23
       1 | 2021-02-07  |      21 | null |          28
       2 | 2021-02-02  |     126 | null |         124
       2 | 2021-02-03  |     123 | null |         126
       2 | 2021-02-04  |    null | null |         123
       2 | 2021-02-05  |    null | null |         123
       2 | 2021-02-06  |     128 | null |         123
       2 | 2021-02-07  |     121 | null |         128

Now you can use this syntax to update all your rows in a single query:
UPDATE
    Readings Act,
    LATERAL
    (SELECT   Reading
     FROM     Readings
     WHERE    SystemID = Act.SystemID
              AND ReadingDate < Act.ReadingDate
              AND Reading IS NOT NULL
     ORDER BY SystemID, ReadingDate DESC
     LIMIT 1) Prev
SET
    Act.Reading = IF(Act.Reading IS NULL, Prev.Reading, Act.Reading),

    Act.Diff = CASE
                   WHEN Act.Reading IS NULL THEN 0
                   ELSE Act.Reading - Prev.Reading
               END;

SELECT * FROM Readings ORDER BY SystemID, ReadingDate;

And that is the result:

SystemID | ReadingDate | Reading | Diff
-------: | :---------- | ------: | ---:
       1 | 2021-02-01  |      24 | null
       1 | 2021-02-02  |      26 |    2
       1 | 2021-02-03  |      23 |   -3
       1 | 2021-02-04  |      23 |    0
       1 | 2021-02-05  |      23 |    0
       1 | 2021-02-06  |      28 |    5
       1 | 2021-02-07  |      21 |   -7
       2 | 2021-02-01  |     124 | null
       2 | 2021-02-02  |     126 |    2
       2 | 2021-02-03  |     123 |   -3
       2 | 2021-02-04  |     123 |    0
       2 | 2021-02-05  |     123 |    0
       2 | 2021-02-06  |     128 |    5
       2 | 2021-02-07  |     121 |   -7

db<>fiddle here
If you are on MySQL 5.7
You can create a function that returns the corresponding previous Reading:
CREATE FUNCTION PrevReading(sID int, rDate date)
RETURNS INT

BEGIN

   DECLARE val INT;

   SET val := (SELECT Reading
               FROM   Readings
               WHERE  SystemID = sID
                      AND ReadingDate < rDate
                      AND Reading IS NOT NULL
               ORDER BY
                      SystemID, ReadingDate DESC
               LIMIT 1);

   RETURN val;

END;

And then update your table:
UPDATE
    Readings
SET
    Reading = IF(Reading IS NULL, PrevReading(SystemID, ReadingDate), Reading),
    Diff =    IF(Reading IS NULL, 0, Reading - PrevReading(SystemID, ReadingDate))

db<>fiddle here


can't find column stats for newly created table

The optimizer doen't need any stats to optimize those queries, so none are created.

  When the automatic create statistics option, AUTO_CREATE_STATISTICS is
  ON, the Query Optimizer creates statistics on individual columns in
  the query predicate, as necessary, to improve cardinality estimates
  for the query plan.

AUTO_CREATE_STATISTICS Option

Try something like

select *
from recomp
where num = 4

And you should see a statistic created.


Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money

If the intention is to build a relational database, it is really helpful to first carry out (a) an analysis of the business context of interest —in order to delineate a conceptual schema— in terms of entity types, inspecting their properties and associations before (b) thinking in terms of tables, columns and constraints —aspects that correspond to the logical level—. Following this course of action, it is much simplier to capture the meaning of the business domain with accuracy and then reflect it in an actual, well constrained, SQL-DDL design.
One of the numerous advantages offered by the relational paradigm is that it permits managing the data in its natural structure; hence one has to “find” such structure before employing relational instruments to manage it. It does not matter if the scenario at issue is related to a personal project (as you pointed out via comments): the more realistic you define it, the more you will learn from its development (if that is the purpose of this effort). Of course, a realistic personal project may evolve into a comercial one with relatively minor adaptations.
Business rules
In order to present a first progression that you may like to use as a reference, I have formulated some of the conceptual-level business rules that are among the most significant ones, and they are enumerated as follows:

A Person owns zero-one-or-many Accounts
A Person is primarily distinguished by its Id
A Person is alternately distinguished by his/her Firstname, Lastname, BirthDate and Gender
An Organization owns zero-one-or-many Accounts
An Organization is primarily differentiated by its Id
An Organization is alternately differentiated by its Name
An Organization starts operating at a FoundingDate
An Account is the Transferor in zero-one-or-many Transfers
An Account is the Transferee in zero-one-or-many Transfers
An Account is primarily identified by its Number
An Account is issued at an exact CreatedDate
In a Transfer, the Transferor must be different from the Transferee
A Person may log in via zero-or-one UserProfile

Since the associations —or relationships— (1) between Person and Account and (2) between Organization and Account are very similar, this fact reveals that Person and Account are entity subtypes of Party (basically, either an individual or a group of individuals), which is in turn their entity supertype. This is a classic information structure that arises very frequently in multiple conceptual models of diverse kinds. In this manner, two new rules can be asserted:

A PartyType classifies zero-one-or-many Parties
A Party is either a Person or an Organization

And two of the previous business rules can be consolidated into a single one:

A Party owns zero-one-or-many Accounts

Which can be also be stated from the point of view of the Account entity type:

An Account is owned by exactly-one Party

Expository IDEF1X diagram
Consequently, I created an expository (simplified) IDEF1X† diagram that synthesizes the rules formulated above, and it is shown in Figure 1:

Party, Person and Organization: Supertype-subtype structure
As demonstrated, Person and Organization are depicted as mutually exclusive subtypes of Party.
The Party supertype holds a discriminator (i.e., PartyTypeCode) and all the properties (or attributes) that are common to its subtypes, which, in turn, have the properties that apply to each of them.
Account
The Account entity type is directly connected with Party, which provides a subsequent connection between (i) Account and Person, and between (ii) Account and Organization.
Since it is possible that, in the real world, (a) a bank Account is not transferable, i.e., its Owner cannot change and (b) an Account cannot start being current or enabled without an Owner, the PRIMARY KEY of this entity type may be comprised of the properties PartyId and AccountNumber, so you should analyze the scenario even more thoroughly to define this point with high precision.
Transfer
On the other hand, the Transfer entity type presents a composite PRIMARY KEY made up of three properties, i.e., TransferorAccountNumber, TransfereeAccountNumber (role names I assigned to distinguish every one of two Account properties involved in each Transfer instance) and TransferDateTime (which tells the exaxct Instant when a Transfer occurrence was performed).
Factors about AccountNumbers
Be also aware that, in actual banking systems, the format of an AccountNumber data point is usually more complex than a “mere” integer value. There are different format arrangements, e.g., the one that corresponds to the International Bank Account Number (IBAN), defined by the ISO 13616 standard. This aspect implies, evidently, that the (1) conceptual analysis and the later (2) logical definitions require a much more exhaustive approach.
Illustrative logical SQL-DDL declarations
Then, as a derivation from the previous analysis, I have declared a logical design where

each table represents an entity type,
each column stands for a property of the respective entity type, and
multiple constraints are set up (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined at the conceptual tier.

I have supplied notes as comments that explain some of the features that I esteem particularly important with respect to the aforementioned structure, that is shown below:
-- You have to determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on the business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient physical implementation settings; e.g.,
-- a good INDEXing strategy based on query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE PartyType (
    PartyTypeCode CHAR(1)  NOT NULL, -- This one is meant to contain the meaningful values 'P', for 'Person', and 'O' for 'Organization'.
    Name          CHAR(30) NOT NULL,
    --
    CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);

CREATE TABLE Party ( -- Represents the supertype.
    PartyId         INT       NOT NULL,
    PartyTypeCode   CHAR(1)   NOT NULL, -- Denotes the subtype discriminator.
    CreatedDateTime TIMESTAMP NOT NULL,
    Etcetera        CHAR(30)  NOT NULL,
    --
    CONSTRAINT Party_PK            PRIMARY KEY (PartyId),
    CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
        REFERENCES PartyType (PartyTypeCode)
);

CREATE TABLE Person ( -- Stands for a subtype.
    PersonId        INT      NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY at the same time, enforcing an association cardinality of one-to-zero-or-one from Party to Person.
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    BirthDate       DATE     NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    --
    CONSTRAINT Person_PK        PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK        UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
        REFERENCES Party (PartyId)
);

CREATE TABLE Organization ( -- Represents the other subtype.
    OrganizationId  INT      NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY simultaneously, enforcing a association cardinality of one-to-zero-or-one from Party to Organization.
    Name            CHAR(30) NOT NULL,
    FoundingDate    DATE     NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    --
    CONSTRAINT Organization_PK        PRIMARY KEY (OrganizationId),
    CONSTRAINT Organization_AK        UNIQUE      (Name), -- ALTERNATE KEY.
    CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
        REFERENCES Party (PartyId)
);

CREATE TABLE UserProfile (
    UserId          INT       NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY at the same time, enforcing an association cardinality of one-to-zero-or-one from Person to UserProfile.
    UserName        CHAR(30)  NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    Etcetera        CHAR(30)  NOT NULL,
    --
    CONSTRAINT UserProfile_PK         PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK         UNIQUE      (Username),
    CONSTRAINT UserProfileToPerson_FK FOREIGN KEY (UserId)
        REFERENCES Person (PersonId)
);

CREATE TABLE Account (
    AccountNumber   INT       NOT NULL,
    OwnerPartyId    INT       NOT NULL, -- A role name assigned to PartyId in order to depict the meaning it carries in the context of an Account.
    CreatedDateTime TIMESTAMP NOT NULL,
    Etcetera        CHAR(30)  NOT NULL,
    --
    CONSTRAINT Account_PK        PRIMARY KEY (AccountNumber),
    CONSTRAINT AccountToParty_FK FOREIGN KEY (OwnerPartyId)
        REFERENCES Party (PartyId)
);

CREATE TABLE Transfer (
    TransferorAccountNumber INT       NOT NULL, -- Role name assigned to AccountNumber.
    TransfereeAccountNumber INT       NOT NULL, -- Role name assigned to AccountNumber
    TransferDateTime        TIMESTAMP NOT NULL,
    Amount                  INT       NOT NULL, -- Retains the Amount in Cents, but there are other possibilities.
    Etcetera                CHAR(30)  NOT NULL,
    --
    CONSTRAINT Transfer_PK             PRIMARY KEY (TransferorAccountNumber, TransfereeAccountNumber, TransferDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT TransferToTransferor_FK FOREIGN KEY (TransferorAccountNumber)
        REFERENCES Account (AccountNumber),
    CONSTRAINT TransferToTransferee_FK FOREIGN KEY (TransfereeAccountNumber)
        REFERENCES Account (AccountNumber),
    CONSTRAINT AccountsAreDistinct_CK  CHECK       (TransferorAccountNumber <> TransfereeAccountNumber),
    CONSTRAINT AmountIsValid_CK        CHECK       (Amount > 0)
);

As noted, there is no need for the retention of ambiguous and problematic NULL marks in the columns of any of the base tables.
If you want to know if an Account involved in a certain Transfer is owned by an Organization or a Person, you can derive such information in a single SELECT statement via, e.g., the Transfer.TrasnferorAccountNumber, the Account.PartyId, and the Party.PartyTypeCode columns.
So that you can ensure that a Party can own at most one Account (as stated in comments), then you should fix a UNIQUE constraint for the Account.PartyId column. However, in real world scenarios, e.g., in a bank, a Person can own zero-one-or-many Accounts, thus I esteem that a one-to-zero-or-one association does not appear realistic.
As previously mentioned, the approach proposed in this answer is supposed to be utilized as a reference that you can extend and adapt on your own. Naturally, extensions and adaptations made at the conceptual level should be reflected in the logical model.
I tested the declaration of this structure in (i) this db<>fiddle and in (ii) this SQL Fiddle, both running on PostgreSQL 9.6 (you initially attached the tag of this database management system).
Integrity and consistency considerations regarding the tables Party, Person and Organization
With the layout described above, one must guarantee that each “supertype” row is at all times complemented by its corresponding “subtype” counterpart and, in turn, ensure that said “subtype” row is compatible with the value contained in the supertype “discriminator” column.
It would be very handy and elegant to enforce such circumstances declaratively but, unfortunately, none of the major SQL platforms has provided the proper mechanisms to do so (as far as I know). Therefore, it is quite convenient to make use of ACID TRANSACTIONS so that these conditions are always confidently met in a database.
Similar scenarios
In case you are interested in other business domains in which supertype-subtype structures come about, you might like to see my answers to

“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”,
“Advice on a basic design, first time database design”, and
“How to model an entity type that can have different sets of attributes?”.

Relevant resource

These Stack Overflow posts cover very relevant points concerning the data type of a column that holds a currency datum, like Transfer.Amount, in PostgreSQL.

Endnote
† Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early theoretical works authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


How to query cached plans that contain a specific missing index?

This isn't foolproof by any means but as a rough sanity check you can use the following query that leverages the DMVs to filter on the cached plan text's table name and index name that you plan to index:
SELECT
    PlanText.[dbid] AS DatabaseId,
    DB_NAME(PlanText.[dbid]) AS DatabaseName,
    CachedPlans.plan_handle AS PlanHandle,
    OBJECT_NAME(PlanText.objectid) AS ObjectName,
    CachedPlans.objtype AS ObjectType,
    CachedPlans.refcounts AS CachedPlanReferenceCount,
    CachedPlans.usecounts AS CachedPlanUseCount,
    PlanText.[text] AS QueryText
FROM sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY sys.dm_exec_sql_text(CachedPlans.plan_handle) AS PlanText
WHERE PlanText.[text] LIKE '%YourTableName%'
    AND PlanText.[text] LIKE '%YourColumnToBeIndexed%'

This leverages the DMVs sys.dm_exec_cached_plans and sys.dm_exec_sql_text.
Just replace the wildcard filters above with your table name and index. This will give you the actual query that the cached plan is for, so then you can re-test it after you create the index. I also included some potentially helpful columns like DatabaseName, CachedPlanReferenceCount (how many objects reference this cached plan), and CachedPlanUseCount (how many times this plan was used). So you could theoretically add an ORDER BY CachedPlanUseCount DESC clause to get the most popularly used queries too and prioritize your list.
One reason why this isn't perfect is if the query in the QueryText column contains a different column coincidentally with the same name as the one you're indexing, but from a different table, and therefor will be a false positive match.


Opening a stored procedure file multiple times in sql server management studio

No, there is no such option.

SSMS doesn't open the database object and lock it down for you like Visual Studio w/ Data Tools add on does. Instead, it generates a sql script depending on what you want to do. It is more of a "Oh, you want to modify 'dbo.sp_awesomeness'? Not a problem! Here is the sql script containing alter procedure code you need at the time you asked of it." 2 minutes later, you ask again, and it pops open a new window going thru the same as above. It is always giving you the information at the time of the request to SSMS.

Visual Studio does the "Oh hey, you want the dbo.sp_awesomeness! Sweet. I see you checked it out of git hub, that's all good. Here is the copy." When you ask for it two minutes later, it just brings up the original screen. But then again, you are not creating sql scripts from SSDT. You are building a database package to deploy.


setting timing on not getting time in log

\timing is a psql command - that is, it acts on the client side.  If you want to know the timing of your queries on the server side (that is, written to the logs), do
SET log_min_duration_statement TO 0;

This will then result in a log entry for each and every statement.  You can disable this by setting it to -1.  To find the timings, you can grep the server log:
grep duration server.log

The issue with psql's -L option is that

-L filename
--log-file=filename
Write all query output into file filename, in addition to the normal output destination.

That is, anything that is not a query output (and not a header, I'd add) won't be redirected into the log file.  You can either use a shell redirect to write everything into the log:
psql [...] > abc.log

Or, if you want to see the output on screen, too, use tee:
psql [...] | tee abc.log


Dropped tables are not going to Recycle Bin

Well with some digging on the Doc for Oracle 11g Express, it appears the flashback functionality is not supported. So this would be a good indication of why I'm having problems!!


select fieldvalues and column-description of a table

The only way I can think of is something like this:

select t.id, x.remarks ->> 'id',
       t.col1, x.remarks ->> 'col1',
       t.col2, x.remarks ->> 'col2'
from the_table t
 join lateral (
    select jsonb_object_agg(a.attname, col_description(a.attrelid, a.attnum)) as remarks
    from pg_attribute a
    where a.attrelid = t.tableoid
    and a.attnum > 0
 ) x on true

The inner query aggregates all column descriptions of the table into a single JSON value that can be accessed easily in the outer query.


Best index for column where values are mostly the same

First off, like you said yourself, not the best use of the column. Should be a separate boolean and an integer column for your "32-bit identifiers". If that's NULL 99% of the time, that is no problem. NULL storage is very cheap.

Either way, you should definitely use a partial index. (That's the proper term as used in the manual.) Excluding 99 % of the rows from the index makes it massively smaller, which matters for performance with millions of rows.

However, if you have a complete index on event_value anyway, and your common queries are retrieving single rows like:

SELECT * FROM tbl WHERE event_value = 123;

... then an additional partial index won't buy much. It would still be used as it's still a bit faster, but not much faster than a complete index. And the costs for an additional index may outweigh the benefits.

While the rare values are "32-bit identifiers", it may be incorrect to assume those are all > 1. Postgres uses signed integer, and 32-bit entities would also cover negative numbers. (Can we even rule out 0 or 1 as one of those identifiers?) If there can be negative values, too:

CREATE INDEX tbl_event_value_part_idx ON tbl (event_value)
WHERE event_value > 1 OR event_value < 0; -- or similar

event_value does not have to be an index column, regardless of its use in the WHERE clause. That entirely depends on the kinds of queries to expect. Either way, the safe bet is to add the same WHERE conditions literally to any query supposed to use the index, even if that's logically redundant. Postgres can make very basic logical conclusions to determine applicable indexes, but it is no AI and does not try to be (would get too expensive quickly). Like:

SELECT * FROM tbl WHERE event_value > 1 OR event_value < 0

Related:

Postgres partial index on IS NULL not working
PostgreSQL partial index unused when created on a table with existing data
How to index a query with `WHERE field IS NULL`?


Get latest timestamp from column in given table dynamically

Probably simplest with regclass as input and an OUT parameter:
CREATE OR REPLACE FUNCTION f_last_update(_tbl regclass, OUT _last_update timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT max(updated_at) FROM ' || _tbl
   INTO _last_update;
END
$func$;

Call:
SELECT f_last_update('mytable');

Optionally schema-qualified and double-quoted where required:
SELECT f_last_update('"My_odd_ScHeMa"."Unwise table name"');

Safe against SQL injection because regclass is automatically quoted and schema-qualified properly when converted to text.
See:

Table name as a PostgreSQL function parameter
Find tables with columns with empty and NULL values in Postgresql
Check whether empty strings are present in character-type columns


Partitioning MySQL for "expired" transactions to improve performance

If a lot of your queries have conditions similar to what you describe, e.g. range condition on the date column:

WHERE dateColumn >= (CURRENT_DATE() - INTERVAL 1 MONTH)

WHERE dateColumn >= '2012-01-01'
  AND dateColumn < '2013-01-01'

it will be useful to define the primary key of the table as (dateColumn, tableAI), where tableAI is an auto incremented integer column.

For InnoDB tables, the above choice means that this index will be the clustered index of the table and any range (on the date) queries on the table will use it and will only scan the relevant part of the table.

If the HasExpired condition is more complex, e.g. some rows should be marked expired after a month, some others after a year, some after a few days, I would consider splitting the table(s) into 2 parts. One having only expired data and one having all data.

So, when some data expires, you'll simply delete them from the "not_expired" table. The data will already be on the other. When you search for not expired data, you'll be searching only one table (the smallest one).
You also have the benefit of adding different indexes on the tables (for example only the above suggestion for the CI in the "all-data" table and many indexes on the not-expired table, depending on your queries.) Without the split, you would need to have an HasExpired column and attach it to all your indexes (as first column), to have equally good indexes.

If the (not-expired) data is updated many times, it might be beneficial to have the split as "not-expired" and "expired" data. So you won't need to do any updates on the "expired" table. But when some data expires, you'll simply move them (instead of deleting) from the "not_expired" table to the "expired" one.


Removing a Key From HSTORE in Postgresql

You're trying to use the expression item.details::hstore - 'vin'::TEXT as a PL/PgSQL statement.

Where's the result supposed to go?

At a guess, I think what you intended to write is:

item.details := item.details - 'vin'::TEXT;

i.e. "set item.details to the old value of item.details with the key vin removed".

It's kind of hard to be sure without more context.


You may need to elaborate on this a bit more but yes it is possible to store that data as you've described it. Here's an example, using your document model,  of such an array of dates in a document in a collection. Personally, I would look at modifying this as I've described at the end but of course it all depends on what your queries need to do with the data.

{
"_id" : ObjectId("55474c322e8f6b5a9dda1a0f"),
"dates" : [
    ISODate("2015-01-01T00:00:00.000Z"),
    ISODate("2015-01-02T00:00:00.000Z"),
    ISODate("2015-01-03T00:00:00.000Z"),
    ISODate("2015-01-04T00:00:00.000Z"),
    ISODate("2015-01-05T00:00:00.000Z"),
    ISODate("2015-01-06T00:00:00.000Z"),
    ISODate("2015-01-07T00:00:00.000Z"),
    ISODate("2015-01-08T00:00:00.000Z"),
    ISODate("2015-01-09T00:00:00.000Z"),
    ISODate("2015-01-10T00:00:00.000Z")
]
}

Are you perhaps asking "how" to actually do so or how to work with them? Pending what your use case is, you may want to alter that document model and opt for a different approach than a pure array of dates. I may instead use an array of subdocuments as shown in this example and I've included an example of a query that retrieves an element as well. Multiple people could, and likely will herein, suggest doing this in different ways, without more clarity regarding the use case.

//Example using subdocs
{
"_id" : ObjectId("55474e332e8f6b5a9dda1a10"),
"dates" : [
    {
        "date" : ISODate("2015-01-01T00:00:00.000Z")
    },
    {
        "date" : ISODate("2015-01-02T00:00:00.000Z")
    },
... etc

  //using embedded docs
  db.rangeTest.find(
 {'dates.date': {$gte:ISODate("2015-01-08T00:00:00.000Z"), $lte:ISODate("2015-01-09T00:00:00.000Z")}},
{_id:0, 'dates.$':1} )


Mysql & multi-tenant db: the best way to organize?

If you have thousands of tables or thousands of databases, there could be performance problems.
If you let customers connect on their own directly to mysql, then you must control their GRANTs, which can be managed at the database or table level.  (This is one way to 'limit access'.)
If you throw all customers into the same set of tables, then you must provide security via an application layer, and manage logins yourself, not via MySQL's GRANTs.
You could try to use VIEWs, with permissions, to constrain users.  Let users connect to the dataset, but limit them to views only.  Build into the views a way to limit them to tenant_id....  (I have not thought through this approach; it may have fatal flaws.)
A 4th option is to have separate instances of MySQL; each customer has nearly-complete control of his instance.  (You maintain admin control.)
With the 4th option, there are many choices - VMs, Docker, mysql_multi, etc.  Be warned:  those tend to work better on *nix, not Windows.  (In spite of decades of development, Win still lags behind *nix when it comes to being "ready for prime time" in the 'server' arena.)
With the 4th option, CGroups could be used to limit each customer's resource usage.  This way one runaway customer cannot significantly hurt the other customers.
Another consideration:  How easy will it be to "shard"?  That is, have multiple servers, each handling some of your customers.  In particular, what will it take to migrate a customer from one shard to another.  Once you have solved that, you have addressed the questions of how to upgrade the hardware, OS, MySQL version, etc.
Do you provide specific client software (a la WordPress) (SaaS)?  Or are you simply providing a database engine (DaaS)?
Will there be client software on the same server?


The technique you are refering to is called Table Partitioning. However, that is not available in SQ Server 2000. To the best of my knowledge, it was introduced in SQL Server 2005:

http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx

However, you could build similar functionality by means of scripting:

http://msdn.microsoft.com/en-us/library/aa902650%28v=sql.80%29.aspx


Making a server with multiple instances a member of two effectively independent clusters

I cannot see any reasons as to why you would have any issues. Just be sure to add a file share or disk witness to the cluster, as that will ensure there is no "weirdness" in the quorum. As long as you set the AG's up correctly, I cannot see any reasons not to use your suggested approach.
The only negative i can think of, is that it will be an additional environment to manage and maintain, but you are already doing that having 2 instances on your primary. Will you be using the VM's for the Read-Only connections?


RMAN rename datafile created in an archivelog

SET NEWNAME works for RESTORE and SWITCH, but not for RECOVER. Datafiles had been renamed and switched before the recovery started.

There is a flaw in your process though, and the above issue is its side effect:

  Restore the control file from the 3/9 backup.

Why? You do a nearly complete recovery to get as close as possible to the current state, not the state on 3/9. Restore the controlfile from the latest backup taken on 3/14.

  Since the restored control files aren't aware of the backup pieces for 3/10 - 3/14, I run this for each

This step is unnecessary if you use the latest controlfile.


How can we restrict collections on specific shards of Mongodb while using Tag Awareness Sharding?

The answer is no. Tags only "guide" where balancer moves chunks. You need to use movePrimary command after creating DB to "change" that primary shard.


How can I take a full backup from MySQL without any System tables?

Four and a half years ago in my first post to Any option for mysqldump to ignore databases for backup?, I collected the database names to memory rather than to disk.

Here is the code from my first answer

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

This could be adjusted to collect the names in SQL rather than in the shell

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYDBLIST1=`mysql -ANe"${SQLSTMT}"`
MYDBLIST2=`echo "${MYDBLIST1}" | sed 's/,/ /g'`
MYSQLDUMP_DATABASES="--databases ${MYDBLIST2}"
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

A couple of months ago, I briefly mentioned using mysqlpump (See my second post to Any option for mysqldump to ignore databases for backup?) which now has --exclude-databases.


The manual has detailed explanation for the comma in the FROM list in the chapter Table Expressions:

The FROM Clause derives a table from one or more other tables given in
a comma-separated table reference list.
FROM table_reference [, table_reference [, ...]]
A table reference can be a table name (possibly schema-qualified), or
a derived table such as a subquery, a JOIN construct, or complex
combinations of these. If more than one table reference is listed in
the FROM clause, the tables are cross-joined (that is, the Cartesian
product of their rows is formed; see below).

The fact that comma-separated table references have been defined in an earlier version of the SQL standard than explicit JOIN syntax does not make the comma wrong or outdated. Use explicit join syntax, where it's technically necessary (see below) or where it makes the query text clearer.
The manual again:

FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE
(see below). It is also equivalent to FROM T1, T2.

There are corner cases where both are not completely equivalent. The manual:

Note
This latter equivalence does not hold exactly when more than two
tables appear, because JOIN binds more tightly than comma. For example
FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as
FROM T1, T2 INNER JOIN T3 ON condition because the condition can
reference T1 in the first case but not the second.

This related question demonstrates the relevance of the difference:

“invalid reference to FROM-clause entry for table” in Postgres query

Basically, your observation is exactly right:

it seems to me that it's declaring a variable called query so it can use it multiple times.

Any function can be used as "table function" in the FROM list.
And function parameters can reference columns from all tables to the left of the function, because the notation:
FROM apod, to_tsquery('neutrino|(dark & matter)') query

... is really equivalent to:
FROM apod CROSS JOIN LATERAL to_tsquery('neutrino|(dark & matter)') AS query

The manual on LATERAL queries:

Table functions appearing in FROM can also be preceded by the key word
LATERAL, but for functions the key word is optional; the function's
arguments can contain references to columns provided by preceding FROM
items in any case.

Bold emphasis mine.
The keyword AS is completely optional noise before table aliases (as opposed to column aliases, where it's recommended not to omit AS to avoid possible ambiguities). See:

Date column arithmetic in PostgreSQL query


Create unique numeric token on PostgreSQL

If you don't mind the unique token being sequential, you can use a sequence, like so:

CREATE TABLE invoices
(
    invoice_no integer,
    token char(6)
);

CREATE SEQUENCE invoices_token_seq START WITH 100000;

ALTER TABLE invoices
ALTER COLUMN token
SET DEFAULT CAST(NEXTVAL('invoices_token_seq') AS CHAR(6));

To test:

postgres=# INSERT INTO invoices ( invoice_no ) VALUES ( 1 );
INSERT 0 1
postgres=#
postgres=# SELECT * FROM invoices;
 invoice_no | token
------------+--------
          1 | 100000
(1 row)

postgres=#

Note that I have used CAST as you specified that the token should be "numeric characters".

The above is the same as using an AUTO_INCREMENT column in MySQL.

However, to accommodate for the fact that you only want to generate a token when the invoice_no IS NOT NULL, you need to use a trigger as follows:

create table ledgers
(
    invoice_no varchar(10),
    debit numeric(6,2),
    credit numeric(6,2),
    details varchar(20),
    token char(6)
);

CREATE SEQUENCE ledgers_token_seq START WITH 100000;

CREATE OR REPLACE FUNCTION token_insert() RETURNS trigger AS '
BEGIN
  IF NEW.invoice_no IS NOT NULL
  THEN
    NEW.token := CAST(NEXTVAL(''ledgers_token_seq'') AS CHAR(6));
    END IF;
  RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER ledgers_insert BEFORE INSERT ON ledgers FOR EACH ROW EXECUTE PROCEDURE token_insert();

Test:

postgres=# INSERT INTO ledgers ( invoice_no, debit, credit, details ) VALUES ( 1, 2, 3, 'NOT NULL invoice_no' );
INSERT 0 1
postgres=#
postgres=# INSERT INTO ledgers ( debit, credit, details ) VALUES ( 2, 3, 'NULL invoice_no' );
INSERT 0 1
postgres=#
postgres=# select * from ledgers;
 invoice_no | debit | credit |       details       | token
------------+-------+--------+---------------------+--------
 1          |  2.00 |   3.00 | NOT NULL invoice_no | 100000
            |  2.00 |   3.00 | NULL invoice_no     |
(2 rows)

postgres=#


How to recover MySQL table structure from FRM files

If you only see the .frm files, then there is a strong likelihood that the storage engine in use was InnoDB and innodb_file_per_table must have been off by default.
If you transferred everything from datadir on the crashed server onto another disk on another machine, you may be able to startup mysql with that folder as is.
For example, suppose ServerA is your crashed server and ServerB is where you want it placed.

Install MySQL 5.5.8 on ServerB
net stop mysql on ServerB to make sure mysql is down on ServerB
md C:\MySQLData on ServerB
Drop everything from the data folder on ServerA into C:\MySQLData on ServerB
Make C:\MySQLData the new datadir

Add this to my.ini on ServerB
[mysql]
datadir=C:/MySQLData

C:\> del C:\MySQLData\ib_logfile* on ServerB
net start mysql on ServerB

Please try this and tell us what happened
UPDATE 2012-02-03 17:06 EDT
Since you were able to recover everything NOW DO THIS:
mysqldump -u... -p... -A -d --routines --triggers > C:\MySQLSchema.sql

This will give all table structures in the MySQL Instance.


Is it possible to tell if an incoming connection is using an SQL Server Alias?

No, that translation is done on the client side and so, with or without an alias, those connections are going to look the same to SQL Server. There is no evidence in DMVs like sys.dm_exec_connections or sys.dm_exec_sessions what name/IP/port combination was entered by the user to resolve to the server where SQL Server is running. SQL Server itself only cares that the connection was established.

Unless you can infer that anyone not using an alias is using shared memory or named pipes, therefore anyone using TCP must be using an alias. I can assure you that this is not a safe assumption.


How Can I Read Where TempDB's Files Will Be Created After Restoring Master DB?

Refer to Instance will not start

There is a section called: TempDB location does not exist, you should be able to see the location in the SQL server log


Copy data through SSIS package from one machine to another

Depending on how much data you are loading into SQL Server from Excel, you can choose different routes :

When designing SSIS package, you can use configuration file. The advantage is that your packages will be customizable, like when you are developing it can point to your local server and when you want to promote it to prod, you just have to change connection string to point to PROD server. Good example can be found here.
Use Bulk Insert to load data.
As @listik mentioned, you can use Import/Export wizard as well.
You can use linked server too as described here.

SSIS will be more flexible and customizable.


Selecting from a function that returns a sys refcursor or an alternative

I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

This might be true, but is unlikely to be so.

When you apply a condition to a view, the condition is usually merged into the view query. This is called predicate pushing, and ought to allow the optimiser to apply the filter as early as possible in the execution plan.

There are some cases where this cannot happen, for example if your view summed up all of the sales orders by client and then you applied a condition to require that total sales order were greater then $1,000,000.

The explain plan wil tell you whether this has happened, but it's best to extract it using DBMS_Xplan.Display. the resulting text-based plan will show you the lines of the plan at which conditions are applied.


I think that the SUPER privilege allows the user to write to a read_only server.
Do NOT use such a user for anything other than admin purposes.  Use less-privileged user(s) for application actions.  Usually, an "app" user should be granted only ... ON dbname.* ....


Best methods for ingesting XML into Oracle 11gR2

The fastest way to get things in is via SQLLoader, also for XML. The following will show you a method based on bfilename, which should be able to load XML files into the database in around a minute or 2 per file.

https://forums.oracle.com/forums/thread.jspa?threadID=2284390&tstart=30

You got some repeating elements in that file, so if you want to be able to query it efficient you should created specific, based on (x)path setting xmlindexes.

Some extra info can be found here

forums.oracle.com/forums/thread.jspa?threadID=2234618&tstart=120

If you need a short introduction into, or howto examples, search the needed info via blog.gralike.com

HTH

Marco


New to CTE - How to do a count after making table unique

Don't know why you need a CTE (or a subselect), wouldn't this simply be

select person, date_, max(where_) from tbl1
group by person, date_
having count(distinct where_) < 2

(fiddle)

PS. Note I changed the table definition to 1) remove backticks that are only used by MySQL to quote identifiers (Redshift is based on Postgres) and 2) rename columns with names that conflict with SQL keywords.


Why has deadlock behaviour changed between MariaDB 10.1.22 and 10.2.14?

Answer originally left in comments by the question author

Finally I discovered mdb addressed this bug in 10.2.13 but it remains in 10.2.14.

In summary have gotten around the problem by turning INNODB_ADAPTIVE_HASH_INDEX = ON and have not had a problem since Monday AM. See https://jira.mariadb.org/browse/MDEV-14441 for the supposed 10.2.13 fix.

Evidently MDB was bypassing releasing the latches associated with the semaphores when deadlocks occurred, when INNODB_ADAPTIVE_HASH_INDEX  = OFF

The mdb 10.2.13 fix explains mdb hanging during deadlocks in 10.2.13. The root cause of this bug is that in the function btr_cur_update_in_place() we are skipping this call if the adaptive hash index was disabled during the execution:

if (block->index) {
    btr_search_x_unlock(index);

When debugging the code, I mistook the leaked X lock for a leaked S lock. I did not find any other rw-lock leaks during the MDEV-14952 review/refactoring effort.

The lock leak was introduced by me in MDEV-12121.


Is commit necessary after DML operation in Function/Procedure?

In general, procedures should not commit.  Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete.  If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction cannot simply call the procedure directly.

If you call a procedure interactively, you will have to explicitly commit or rollback the transaction because Oracle has no idea if you intend the procedure call to be a logical transaction or if you intend to compose a larger transaction involving multiple procedure calls.  If you use dbms_scheduler, dbms_scheduler assumes that a job is a logical transaction and commits at the end of the job assuming it was successful (dbms_job does the same thing).

Functions should not manipulate data in the first place.  A function that manipulates data cannot be called from a SQL statement (barring the corner case where the function itself is declared to use an autonomous transaction which is almost never appropriate).  The whole point of having both functions and procedures is that functions can be embedded in SQL statements and can be more freely granted to users because they do not change any data.


Insert if names do not exist and return ids if exist

It's the recurring problem of INSERT or SELECT, related to the common UPSERT, but not the same. INSERT ... ON CONFLICT ... DO ... (commonly called UPSERT), introduced with Postgres 9.5, is instrumental in any case.

Assuming the simple case without concurrent write load on the table:

CREATE TABLE foo (
  id   int   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text  UNIQUE
);
INSERT INTO foo (name) VALUES ('a'), ('d');  -- assigned IDs 1 and 2

WITH input(name) AS (SELECT unnest('{a,b,c,d,e}'::text[]))  -- input array once
, ins AS (
   INSERT INTO foo(name)
   TABLE  input
   ON CONFLICT (name) DO NOTHING
   RETURNING id
   )
SELECT f.id
FROM   input i
JOIN   foo   f USING (name)
UNION  ALL
TABLE  ins;

| id |
| -: |
|  1 |
|  2 |
|  4 |
|  5 |
|  7 |

db<>fiddle here

Note the missing serial IDs 3 and 6. A side effect of UPSERT is that conflicting rows burn a serial number since default values are fetched before checking for conflicts, and sequences are never set back. That should be irrelevant, since gaps in serial numbers are to be expected at all times. Meaning, you cannot rely on a result 1,2,3,4,5 for the given example (and assumed setup).

Detailed explanation and alternative solutions - especially for concurrent write load:

Is SELECT or INSERT in a function prone to race conditions?
How to use RETURNING with ON CONFLICT in PostgreSQL?


If the structure is not giving you any other grief, then you could just "tighten" the query a bit, in order to get the result that you want.

Using the following test table / data:

create table categories
as select 1, 'Default Category' from dual ;

create table categorypermissions
as
select 1 category_id, 1 permission_id from dual
union select 1, 2 from dual;

create table categoryrelation
as
select 1 document_id, 1 category_id from dual
union select 2, 1 from dual;

create table documents
as
select 1 id, 'Sample Document' title, 'Content!!!!!' content from dual
union select 2, 'Another Document', 'Hello, World!' from dual;

This query seems to be doing the trick:

select
  D.id
, D.title
, D.content
, CR.category_id
, CP.permission_id
from documents D
  join categoryrelation CR on D.id = CR.document_id
  join categorypermissions CP on CP.category_id = CR.category_id
order by D.id, CP.permission_id
;

-- output:
+----+------------------+---------------+-------------+---------------+
| id | title            | content       | category_id | permission_id |
+----+------------------+---------------+-------------+---------------+
|  1 | Sample Document  | Content!!!!!  |           1 |             1 |
|  1 | Sample Document  | Content!!!!!  |           1 |             2 |
|  2 | Another Document | Hello, World! |           1 |             1 |
|  2 | Another Document | Hello, World! |           1 |             2 |
+----+------------------+---------------+-------------+---------------+
4 rows in set (0.00 sec)

(MySQL 5.7)

Dbfiddle here.


Eelke is almost certainly correct that your locking is blocking autovacuum.  Autovacuum is designed to give way to user activity, deliberately.  If those tables are locked, autovacuum cannot vacuum them.

For posterity, however, I wanted to give an example set of settings for hyper-aggressive autovacuum, since the settings you gave don't quite do it.  Note that making autovacuum more aggressive is unlikely to solve your problem, however.  Also note that the default autovacuum settings are based on running over 200 test runs using DBT2 seeking an optimal combination of settings, so the defaults should be assumed to be good unless you have a solid reason to think otherwise, or unless your database is significantly outside the mainstream for OLTP databases (e.g. a tiny database which gets 10K updates per second, or a 3TB data warehouse).

First, turn on logging so you can check up on whether autovacuum is doing what you think it is:

log_autovacuum_min_duration = 0

Then let's make more autovac workers and have them check tables more often:

autovacuum_max_workers = 6
autovacuum_naptime = 15s

Let's lower the thresholds for auto-vacuum and auto-analyze to trigger sooner:

autovacuum_vacuum_threshold = 25
autovacuum_vacuum_scale_factor = 0.1

autovacuum_analyze_threshold = 10
autovacuum_analyze_scale_factor = 0.05

Then let's make autovacuum less interruptable, so it completes faster, but at the cost of having a greater impact on concurrent user activity:

autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

There's your full program for generically aggressive autovacuum, which might be apppropriate for a small database getting a very high rate of updates, but might have too great of an impact on concurrent user activity.

Also, note that autovacuum parameters can be adjusted per table, which is almost always a better answer for needing to adjust autovacuum's behavior.

Again, though, it's unlikely to address your real problem.


What is the impact of SHOW STATUS, and how often is acceptable to call it?

SHOW STATUS has minor impact on the system.

For monitoring, you really need SHOW GLOBAL STATUS so you can get the system-wide counters, not the SESSION counters for the monitor's connection.

It is probably unreasonable to do SHOW GLOBAL STATUS more than once a second.  And, at that frequency, it has negligible overhead.  Keep in mind that you then have to do something with the hundreds of values.  If you turn around and INSERT it into the same server, that action may be more invasive.

Or is this what you are doing to see what is happening with a query?

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

Again, do it as needed; don't worry.


How to run Sql Server stored procedure in Async mode?

The simplest way is to call sp_start_job and kick off an unscheduled SQL Agent Job.  Parameter values can be stored in a table that the job reads from before calling the procedure.


How to login to Sybase ASE using OS credentials?

I'm assuming sqlplus is designed to allow password-less DB access under certain scenarios (eg, a custom perl script running under the same scenario doesn't, by default, have password-less access to the Oracle DB) ... ?

SAP(Sybase) ASE does not come with any applications which, by default, allow for password-less access to the dataserver.

Having said that ... it is possible to setup password-less access, eg:

design your own custom app that provides password-less access [will likely have to rely on a password file, or an approved method of external authentication]
configure your environment with one of the approved methods of external authentication [see ASE Security Admin, External Authentication for more details]


As Brent Ozar mentioned in the comment section that this is not a simple task to find wait type (and what is causing the wait) between primary and secondary with correlation to time.  I am answering your question about finding the source.
I modified extended event trace definition given in the blog post you mentioned.  Removed the where clause so you can capture all the sessions that is causing wait.

Added few more actions to capture more information. For example:

sqlserver.client_hostname
sqlserver.plan_handle
sqlserver.session_nt_username
sqlserver.sql_text

Here is the full definition.

CREATE event session [redo_wait_info] ON server ADD event sqlos.wait_info( action(package0.event_sequence,sqlos.scheduler_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.plan_handle,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text) ) ADD target package0.event_file(SET filename=N'C:\Redo_Wait_Info.xel',
  max_file_size=(50),
  max_rollover_files=(100)) WITH (max_memory=4096 kb,
event_retention_mode=allow_multiple_event_loss,
max_dispatch_latency=120 seconds,
max_event_size=0 kb,
memory_partition_mode=none,
track_causality=OFF,
startup_state=ON)
GO


Rebuild Index Causing Data File Growth Issue

I think you mean that your TRANSACTION LOG is growing (not your DATA files)? Especially when it's a REBUILD of your indexes it would seem strange that only your DATA files are growing.

You should perform a log backup of your TRANSACTION LOG (when in full recovery mode) and then you can do a shrink files of your LOG files to a desired size (with a minimal size taken into consideration; but don't make it too small, or it will start growing again. Key is to determine a good size of your LOG and then leave it that way).

Do a right-click on your database > Reports > Disk Usage.
There you can see how the total size is divided between DATA and LOG.

You should perform scheduled LOG backups (every 15 mins e.g.) to avoid this large growth of LOG files. After every LOG backup, the transaction log frees up space for the next transactions.

I would recommend Ola Hallengren backup maintenance solution for FULL, DIFF and LOG backups as it also cleans up old files)


RETURNING value from SELECT table in INSERT .. SELECT

You can 'store' your old data in a CTE, then do the change in another (writable) CTE, and return the original values like this:

WITH old AS (
    SELECT status, data
      FROM your_table
     WHERE id = :id
),
new AS (
    INSERT INTO your_table
    SELECT status, :data
      FROM old
)
SELECT * FROM old;

The writable CTE (new) works on the results of the previous one (old).


missing files, installing Oracle DB 12c on Oracle Linux 7.2

The following is the excerpt of runInstaller.sh file

# Abort the install, if install/.oui file is not found in the stage area
#
if [ ! -f $CMDDIR/install/.oui ]
then
  echo "Source location is incomplete. Make sure you have downloaded and extracted
all the relevant archives."
        exit 1
fi

#

Which clearly states that if you are missing the .oui file it returns the given message.

Generally, after extracting the downloaded zip files manually we get two directories like database and database(0). The .oui file is hidden so we don't see it using general ls command and File Explorer. You can see it using ls -a command in Linux.

Solution:

Goto the second directory database(0)/install/ and copy the .oui file to the database/install directory.

[oracle@testsrv Desktop]$ cp 'database(0)'/install/.oui database/install


From the docs on ST_GeomFromGeoJSON

  ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document.

This means you'll want to cut out just the value from the geometry in your GeoJSON document,

SELECT ST_AsText(geom)
FROM ST_GeomFromGeoJSON($${
  "type": "Point",
  "coordinates": [125.6, 10.1]
}$$) AS geom;

     st_astext
-------------------
 POINT(125.6 10.1)

ℹ ST_GeomFromGeoJSON is the inverse of  ST_AsGeoJSON (which only produces the geometry section of GeoJSON document.)


Why does PostgreSQL require a database connection?

I don't think there's a fundamental limitation there, and it's something I'd like to see addressed. I agree with you that it's a bit ugly.

That said, the convention to just use the postgres database as a maintenance database is so strong that the real world impact is negligible.

PostgreSQL has shared catalogs, things like pg_authid and pg_database. It's already possible to start a backend or background worker that connects to no particular database by passing InvalidOID as the database oid to InitPostgres(...) or the bgworker function wrappers. Such a backend can query and AFAIK modify shared catalogs, so I don't see why the executor couldn't be used to service frontend requests instead. Transaction state, user identity, databases, etc are all global.

It might be issues with pg_class and similar relations. Despite the fact that pg_database is a global shared catalog relation, it's listed in pg_class, which gets cloned from the template database. There's no global shared variant of pg_class.

So it's possible the relation cache doesn't handle the "no database" case. I haven't investigated closely. I'll be doing more in 9.5, since some fixes to the background worker API will let me avoid doing ugly hacks relating to shared catalog access by starting a shared-relations-only background worker.


What is the best way to unpack JSON objects in SQL Server?

I think your JSON definition for arrays might be a little odd, you should be able to unpack an array like so:

DECLARE @json                   VARCHAR( 4000 ) =  '
    {
        "Email": "WMogh@starfleet.gov",
        "Prefix":null,
        "FirstName": "Worf",
        "MiddleInitial": "",
        "LastName": "Mogh",
        "Suffix": "Son Of",
        "Title" :"Commander",
        "Groups": ["0", "1", "5"]
    }';

SELECT  ISJSON( @json );

SELECT  j.value
FROM    OPENJSON( @json, 'strict $.Groups' ) j;

  value

  0

  1

  5

The notation "Groups":[{"0", "1", "5"}] is saying that within an object, there is a key "Groups" which has a value of an array containing a single element.  That  element is also an object, but then - "0", "1", "5" is not meaningful in a JSON context.  For instance:

DECLARE @json                   VARCHAR( 4000 ) =  '
    {
        "Email": "WMogh@starfleet.gov",
        "Prefix":null,
        "FirstName": "Worf",
        "MiddleInitial": "",
        "LastName": "Mogh",
        "Suffix": "Son Of",
        "Title" :"Commander",
        "Groups": [{"A": "0", "B": "1", "C": "5"}]
    }';

SELECT  ISJSON( @json );

SELECT  A = JSON_VALUE( j.value, '$.A' ),
        B = JSON_VALUE( j.value, '$.B' ),
        C = JSON_VALUE( j.value, '$.C' )
FROM    OPENJSON( @json, 'strict $.Groups' ) j

  A B   C

  0 1   5


A superkey (and therefore a candidate key) that does not contain all attributes must contain at least an attribute set that is the left side of a functional dependency. Otherwise it would not be possible to generate an additional attribute from the attribute set of this superkey and this contradicts the definition of a superkey.

But a subset of ABCDE containing at least one of C,AB,AE or DE is already a superkey and to be minimal it is necessary that it contains only one of these subsets.

So the only subsets that must me investigated are that that are supersets of AC. But a superset of AC cannot be a minimal superkey because C already is a key.

Therefore the only candidate keys are C,AB,AE and DE


Restoring a mysqldump loses user privileges for routines?

You need to add the --set-gtid-purged=OFF flag to your mysqldump statement. This will stop it from writing the set-gtid-purged line in the dump file.


How to PIVOT data from MSSQL query and Fetch to PHP view

You can do a pivot with CASE statements. It's basically the manual way of pivoting and if you were using a version of SQL Server before 2005, you would need to go that route. SQL Server 2005 added the PIVOT function.
Because you need it to be dynamic, you will need to use dynamic SQL. It's a lot of leg work but definitely doable.
I'm going to create your table content in a global temporary table (has to be global to work in dynamic SQL).
-- CREATE GLOBAL TEMPORARY TABLE
CREATE TABLE ##tablename (
  [nopas] VARCHAR(8),
  [code] VARCHAR(4),
  [name] VARCHAR(64),
  [order] SMALLINT
);

INSERT INTO ##tablename
  ([nopas], [code], [name], [order])
VALUES
 ('00115301','D031','ADAM',  1)
,('00130015','D031','ADAM',  2)
,('00149998','D026','JAMES', 1)
,('00149970','D028','FIKI',  1)
,('00142641','D028','FIKI',  2)
,('00127700','D028','FIKI',  3);

Next, I'm going to SELECT just the unique names into another temporary table and associate them with an IDENTITY with which I can iterate through in a for loop.
SELECT IDENTITY(INT, 1,1) as [id], [name]
INTO #temp
FROM ##tablename
GROUP BY [name];

Now comes the difficult part. The PRINT @dynSQL generates what I'm trying to achieve dynamically (below). We need to SELECT the [order], [name], and [nopas] for the final result. We are pivoting on the [name] in the second part of the statement, and aggregating by MAX([nopas]); but because we contain an [order] grouping in your original table content, it will only retrieve the MAX([nopas]) based on the MAX value of a single row. So, the result is that it removes aggregation. If you did not have an [order] column in your original content, we would need to use the ROW_NUMBER() function* PARTITIONED BY the [name] to generate that [order].
EDIT: I discovered that the ROW_NUMBER() function is only available in SQL Server 2012 and newer.
-- PRINT @dynSQL result
SELECT * FROM
(SELECT [order], [name], [nopas]
  FROM ##tablename) AS sourceTable
PIVOT(
MAX([nopas])
FOR [name] IN ([ADAM],[FIKI],[JAMES])
) AS pivotTable;

-- The leg work to create the dynamic SQL.
DECLARE @count INT = 1;
DECLARE @max INT = (SELECT MAX(id) FROM #temp);
DECLARE @dynSQL VARCHAR(2048) = 'SELECT * FROM
(SELECT [order], [name], [nopas]
  FROM ##tablename) AS sourceTable
PIVOT(
MAX([nopas])
FOR [name] IN (';

SET @count = 1;
WHILE(@count <= @max)
BEGIN
  SET @dynSQL += '[' + (SELECT [name] FROM #temp WHERE id = @count) + '],';
  SET @count += 1;
END

SET @dynSQL = LEFT(@dynSQL, DATALENGTH(@dynSQL) -1) + ')
) AS pivotTable;';

PRINT @dynSQL;
EXEC (@dynSQL);

DROP TABLE ##tablename;
DROP TABLE #temp;

Oh, if you want to remove the [order] column, from the result. You could  use SELECT * INTO ##newGlobalTable in the dynamic SQL and then ALTER the new table and DROP the column.


SQL Authenticated Account is unable to select a View in MS SQL Server

If you check the How to Create a Linked Server doc, you'll understand that your current configuration works like this:

Be made using the login's current security context Specify that a connection will be made using the current security context of the
login for logins not defined in the list. If connected to the local
server using Windows Authentication, your windows credentials will be
used to connect to the remote server. If connected to the local server
using SQL Server Authentication, login name and password will be used
to connect to the remote server. In this case a login with the exact
same name and password must exist on the remote server.

Since your SQL Server login doesn't exist on windows you can't access the file. You should create a mapping to that specific login defining a remote user and password with permissions on that server to read the file.
Just beware that anyone logged in SQL Server with that SQL Server user will have the privileges of the Windows login you defined on the mapping.


Postgres: change database name without interruption?

No, a database can have only a single name.

You will have to disconnect all sessions before you can rename the database.

You will have to update the database name in all connection strings. The latter can be avoided if you use LDAP connection string lookup, but I guess you don't want to run an LDAP server in a small organization.


Why is my maintenance plan save overwriting job ownership?

There is a bug report on Microsoft Connect. There are workarounds in that link.


Join Employees table's unique ID with inserted in trigger.

--- Employees table
CREATE TABLE Employees
( EmpID INT,  --- unique id for employee
  Salary INT
)

--- Dummy data insertion
INSERT INTO Employees
SELECT 11001,5000
UNION ALL
SELECT 10003,6500
UNION ALL
SELECT 10004,6900
UNION
SELECT 10006,300

-- Salary Trigger
CREATE TRIGGER SalaryCannotDecrease ON Employees
AFTER UPDATE
AS
IF EXISTS (
            SELECT *
            FROM inserted INNER JOIN deleted ON inserted.empid=deleted.empid
            WHERE inserted.Salary < deleted.Salary
            )
BEGIN
    RAISERROR 30002 'You cannot decrease salary';
    ROLLBACK TRANSACTION;
    RETURN
END;

-- Update Statement
UPDATE Employees
SET Salary = Salary * 1.05
WHERE empid=10006

--- Verification
SELECT * FROM Employees


You do it with a DDL Trigger that captures the DDL event for CREATE LOGIN

CREATE TRIGGER PolicyStuff
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON;
IF EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(100)') = 'SQL Login'
BEGIN
    DECLARE @sql nvarchar(1000) = 'ALTER LOGIN ' + QUOTENAME(EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')) + ' WITH CHECK_POLICY = OFF;';
    EXEC(@sql);
END
GO

CREATE LOGIN foo WITH PASSWORD='C-mplex654Password', CHECK_POLICY=ON;
GO
SELECT SL.name, SL.is_policy_checked FROM sys.sql_logins SL WHERE SL.name = 'foo'
GO
DROP LOGIN foo
GO


Powershell to dump database schema to text file

Use the dbatools module.

From your svn you need to build your solution. With those dacpacs you can use the following command.

Publish-DbaDacPackage -SqlInstance sql2017 -Database WideWorldImporters -Path C:\temp\sql2016-WideWorldImporters.dacpac -PublishXml C:\temp\sql2016-WideWorldImporters-publish.xml -GenerateDeploymentReport -ScriptOnly

Reference:
https://docs.dbatools.io/#Publish-DbaDacPackage


Oracle listagg forces SORT (GROUP BY) execution plan

The queries are an order of magnitude different !

The access paths themselves shouldn't have such an impact but you're comparing the simplest aggregation function (COUNT(*)) to one of the most complex (LISTAGG) !

Furthermore, you have specified an ORDER BY clause in your LISTAGG, this will force Oracle to sort, which explains the optimizer decision to ignore your hint (this hint is also undocumented as far as I can tell).

If you wish to compare the different access paths, use the exact same query with different hints, eg:

SELECT /*+ USE_HASH_AGGREGATION */ id
--, count(id)
FROM test
group by id

and

SELECT /*+ NO_USE_HASH_AGGREGATION */ id
--, count(id)
FROM test
group by id


Imported SSIS package fails if imported with SSMS on the SQL server, but works when imported from a remote PC

Here is what I found in regards to this issue.

First, I exported the broken package from the server after importing it. I also exported the working package from the server after importing it. I compared the two files side by side and quickly discovered that the script within our package where the errors occurred was missing it's binary data in the BinaryItem element. I was also able to look directly at the package data on the server using the query:

SELECT id
    ,NAME
    ,CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
    ,description
FROM [msdb].[dbo].[sysssispackages]

The missing binary code indicated to me that the script was not being properly compiled during import.

After wasting a lot of time trying to find out why, I realized that the script was fairly simple so I should take a look in the script to see what was in there that might cause the import to fail.

I discovered a reference to Microsoft.Office.Interop.Excel which is part of the Microsoft Office Primary Interop Assemblies installed with Microsoft Office. On the SQL server, Microsoft Office is not installed. As a result, the .DLL file related to this reference is missing on the SQL server.

Removing the faulty, and unneeded reference solved the issue. I was able to confirm with Process Monitor that the server was looking for this .DLL file during import and not able to find it.

Apparently, if this happens during import there is no indication of the failure. Long story short, the error message was accurate. The binary code was missing. That was a result of a failure to compile the source code during import.


Does using LIMIT improve the performance and is it noticeable?

If you want to take advantage of LIMIT to improve performance, you need

understand the data you are retrieving
proper indexing the correct sequence of columns
take responsibility for refactoring the query
using LIMIT before JOIN

These principles can go a long way if you can orchestrate them.
I learned these concepts by watching this YouTube Video (listen carefully through the French accent)
I used those concepts to answer a very tough StackOverflow question about getting the top 40 articles from some tables : May 12, 2011 : Fetching a Single Row from Join Table.
In my answer to that question (May 16, 2011), I wrote the following query and tested it thoroughly:
SELECT
  AAA.author_id,
  AAA.date_created,
  IFNULL(BBB.title,'<NO_TITLE>') title,
  IFNULL(CCC.filename,'<NO-IMAGE>') filename,
  IFNULL(CCC.date_added,'<NO-IMAGE-DATE>') image_date
FROM
(
  SELECT
    AA.id,
    AA.date_added,
    BB.author_id,
    BB.date_created
  FROM
  (
    SELECT
      A.id,IFNULL(MAX(B.date_added),'1900-01-01 00:00:00') date_added
      FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A
      LEFT JOIN article_images B ON A.id = B.article_id
      GROUP BY A.id
  ) AA
  INNER JOIN articles BB USING (id)
) AAA
LEFT JOIN article_contents BBB ON AAA.id=BBB.article_id
LEFT JOIN article_images CCC
ON (AAA.id=CCC.article_id AND AAA.date_added=CCC.date_added)
ORDER BY AAA.date_created DESC;

Please notice the line in the query with the LIMIT
      FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A

This subquery is buried three levels deep. This allowed me to get the last 40 articles using LIMIT. Then, I performed the necessary JOINs afterwards.
LESSONS LEARNED

Doing LIMIT inside subqueries may not always be the answer because of the cardinality of indexes, the data content, and the result set size from the LIMIT. If you have all your "ducks in a row" (Have the four principles in mind for your query), you can get surprisingly good results.
Make your queries as simplistic as possible when doing LIMIT by gathering keys only.


Is there a way to determine if SQL Server queries are running in memory or going to disk?

You can use one of these two queries to see the total logical reads and the total physical reads.

SELECT  DB_NAME(st.dbid) Db,
        OBJECT_NAME(st.objectid, st.dbid) Prc,
        qs.execution_count,
        qs.total_logical_reads,
        qs.total_physical_reads,
        qs.statement_start_offset,
        qs.statement_end_offset,
        st.text
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st;

SELECT  DB_NAME(database_id) Db,
        OBJECT_NAME(object_id, database_id) Prc,
        execution_count,
        total_logical_reads,
        total_physical_reads
FROM    sys.dm_exec_procedure_stats ps;

The first one breaks this down by statement, the second one counts in the entire procedure.

Physical reads are reads against the disk, logical reads are against memory.
You can use this to figure out which procedures or statements are the most expensive ones in your system and try to tune those.

Keep in mind, that while logical reads are significantly cheaper that physical reads, they are still expensive, so reducing the number of them (for example by adding an appropriate index) can make your queries run a lot faster.

There are a lot of additional columns in the DMVs above that you might find interesting too.

How does an index help to reduce logical reads?

In SQL Server all data is organized in blocks, 8KB in size. These blocks are called "pages".

Every table contains "meta" pages that contain information about the struktur of the table as well as pata pages. If no index exists and you run a query like SELECT * FROM tbl WHERE Id = 7 SQL Server has to go looking for this or these rows in the entire table. So it reads in one page at a time, loops through all rows in each page to determine the rows that fit the WHERE clause. So if the table requires 1,000,000 pages to be stored, this query will take 1,000,000 logical reads to execute.

If you have an index, SQL Server sorts the data logically within the pages and establishes a linked list between the pages. This allows for running queries with an ORDER BY to be executed without an expensive sort operation. But mor importantly that the sorting, SQL Server adds a B+Tree to the table. A B+Tree is a structure comparable to the index in a book, where looking for a specific keyword allows me to directly jump to the page that contains the keyword. The typical book has only one index level whereas a B+Tree can have multiple. Just think of a large book, where the index itself is multiple pages long. In a case like that it makes sense the add an additional index layer that tells us on wich page the index words starting with S are to be found.

B+Trees are optimized to have as few levels as possible while providing the property that any record in the index can be found by reading one page per index level. So assume the above WHERE Id = 7 query when you have an index sorted by Id. Let's say that index has 5 levels. Now, to find all records that match this query I have to read one page per index level (that is 5 pages).This is called an "Index Seek". If there are multiple records that fit the bill I might have to follow the sorted index for a while to retrieve all of them. But lets assume there is only one record.

So, without the index running that query required 1,000,000 reads, with indes it required 5 reads. Even though a logical read is an in-memory operation there is stil a substantial cost - in fact it is the most expensive operation in a trivial query like the one above. So reducing the amount of logical reads needed by a factor of 200,000 will speed up your query by a similar factor.

So, a logical read is not equivalent to a table scan, but a table scan causes a whole lot more logical reads than an index seek.


In this case, since the forced collation is defined over the same character set as the column's encoding, there won't be any performance impact (versus defining that collation as the column's default; whereas utf8_general_ci will almost certainly perform slower in comparisons than utf8_bin due the extra lookups/computation required).

However, if one forced a collation that is defined over a different character set, MySQL would have to transcode the column's values (which would have a performance impact); I think it does this automatically if the forced collation is over the Unicode character set, or raises an "illegal mix of collations" error in any other circumstance.

Note that the collation recorded against a column's definition is merely a hint to MySQL over which collation is preferred; it may or may not be used in a given expression, depending on the rules detailed under Collation of Expressions.


Is there a non-DDL way to preserve back-references when staging data into production?

I would suggest adding a (set of) staging tables to the destination or an intermediary that can be better controlled and more stable. Place the tracking info there. Then do all the transformation from the staging to the final destination, either carrying the tracking info with it or discarding it.

There are several ways you can generate a tracking key from multiple systems, as long as they all follow the same algorithm, it doesn't have to be an INT. It could be a 2 char prefix with a sequential number. For that matter it doesn't have to be just one column.


This is an example of getting the rankings:

select version();

| version() |
| :-------- |
| 8.0.13    |

CREATE TABLE person( name CHAR(10), money INT );

✓

INSERT INTO person VALUES
  ('Smith',10),('Jones',15),('White',20),
  ('Black',40),('Green',50),('Brown',20);

✓

SELECT name, money, rank() OVER(win) AS 'Richest'
FROM person
WINDOW win AS (ORDER BY money DESC);

name  | money | Richest
:---- | ----: | ------:
Green |    50 |       1
Black |    40 |       2
White |    20 |       3
Brown |    20 |       3
Jones |    15 |       5
Smith |    10 |       6

db<>fiddle here

Note this code is for MySQL-8.0 or MariaDB-10.2+. For earlier version implementations see this example

To get a value for a single user:

SELECT COUNT(*) + 1 AS "Richest"
FROM person p
JOIN person richer
  ON p.money < richer.money
WHERE p.name="Brown"

This doesn't have any version requirements.

Ref this fiddle.


Storing whether a user is a student or teacher? Is a lookup table necessary?

Your sample data suggests a User can be a Principal, so 0 and 1 won't work.  IF a User can only be 'a student' OR 'a teacher' OR 'a Principal' OR (a Janitor, a Librarian, etc.), this looks like a simple enumeration pattern and your second example would be the best approach.  Make sure your User table has a foreign key defined that points to the Role table to ensure that only valid Roles are used

Create Table dbo.Role (ID INT, RoleName varchar(30)
CONSTRAINT [PK_Role] PRIMARY KEY ([Id]))
Insert into dbo.Role (ID,RoleName)
values(1,'Student'),(2,'Teacher'),(3,'Principal')

Create Table [User] (ID INT, UserName varchar(30), [Role] int
CONSTRAINT [PK_User] PRIMARY KEY ([Id])
CONSTRAINT [FK_User_Role] FOREIGN KEY ([Role]) REFERENCES [Role] ([Id])
)

--Works
Insert into dbo.[User] (ID,UserName,Role) values(1,'John',1),(2,'Teacher',2)

--Fails due to Role type 4 not in Role enum table
Insert into dbo.[User] (ID,UserName,Role) values(3,'Sam',4)


MySQL on MACOS Sierra secure-file-priv setting

on mac os x startup parameters of mysql You can change by edit .plist file

location:

/Library/LaunchDaemons
com.oracle.oss.mysql.mysqld.plist

Use Xcode or for example BBEdit, and add line in section ProgramArguments

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Disabled</key>
    <false/>
    <key>ExitTimeOut</key>
    <integer>600</integer>
    <key>GroupName</key>
    <string>_mysql</string>
    <key>KeepAlive</key>
    <false/>
    <key>Label</key>
    <string>com.oracle.oss.mysql.mysqld</string>
    <key>LaunchOnlyOnce</key>
    <false/>
    <key>ProcessType</key>
    <string>Interactive</string>
    <key>Program</key>
    <string>/usr/local/mysql/bin/mysqld</string>
    <key>ProgramArguments</key>
    <array>
        <string>/usr/local/mysql/bin/mysqld</string>
        <string>--user=_mysql</string>
        <string>--basedir=/usr/local/mysql</string>
        <string>--datadir=/usr/local/mysql/data</string>
        <string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
        <string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
        <string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
        <string>--secure-file-priv=/mysql_exp</string>
    </array>
    <key>RunAtLoad</key>
    <false/>
    <key>SessionCreate</key>
    <true/>
    <key>UserName</key>
    <string>_mysql</string>
    <key>WorkingDirectory</key>
    <string>/usr/local/mysql</string>
</dict>
</plist>

restart MySQL and check:

mysql> show variables like 'secure%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| secure_auth      | ON          |
| secure_file_priv | /mysql_exp/ |
+------------------+-------------+
2 rows in set (0.00 sec)

Add:

You can achieve same result if will create my.cnf file straight in /etc/

sudo nano /etc/my.cnf

You can copy example file from

/usr/local/mysql/support-files/

just notes about comments - "copy/past" it's not a way, even on Your Mac You really do not need file import/export at all
just use normal tools like:

MySQL Workbench
Navicat for MySQL
JetBrain IDE (most of them have database module)
DBVisualizer

any of them can help You:

export result of query into different format - csv, delimited file, json, xls
Import popular format of files direct into table, with mapping columns to datatypes

manual file level import/export could be useful in production environment where You need automate processes, but also for now it is more backup way, primary and proper - ETL scripts and tools.


SQL Server Replication Snapshot Agent login fails

I have used a Domain Account for Snapshot Agent and Log Reader Agent,
  called SQLAgent.

If you check this answer:

The process could not connect to Subscriber

you will see that the SQLAgent account must have a login on your ServerB and must be a db_owner on your subscription database there.

It does not need to be a sysadmin on your ServerA.

It has though, to have sufficient permissions on your snapshot folder wherever you decide that should be.


mysqladmin debug writes out Current locks but PROCESSLIST and INNODB STATUS don't show any

The locks appear to be spurious. Verified bug: http://bugs.mysql.com/bug.php?id=64140