MySQL overhead of calling a Stored Procedure

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons

  So there are good reasons to use prepared statements:

  Save on query parsing
  Save on data conversion and copying
  Avoid SQL Injection
  Save memory on handling blobs

  There are also drawbacks and chewats of using prepared statements:

  Query cache does not work
  Extra server round trip required if statement used only once
  Not all statements can be prepared. So you can’t use prepared API exclusively you’ll need to fall back to normal API for some statements
  Newer and sometimes buggy code. I had a lot of problems with PHP prepared statements. It is getting better but still it is less mature than standard API
  You can’t use placeholders in place of all identifiers. For example you can’t use them for table name. In certain version it even does not work for LIMIT boundaries
  Inconvenient list handling. Unlike in for example PEAR emulated
  prepared statements there is no nice way to pass list of values to IN
  Harder tracing. Logs were now fixed to include full statement text not
  only “Execute” but in SHOW INNODB STATUS you would still see
  statements without actual values – quite inconvenient for analyses

Since what you are experiencing is the expected behavior, you should not use Prepare Statements. You are making many calls to execute a single command. If you simply ran this

stmt = conn.createStatement();
rs = stmt.executeQuery("call sp_one('a','b')");

the complete parsing of the SQL, preparing for execution, and the parameters already being passed in through the SQL statement are all done on the server side with one call.

Please read that Percona blog for more details.


How can I view the number of locks held by MySQL InnoDB, when the total is larger than 10?

I recommend using the SYS schema that ships with MySQL 5.7.  This will show you lock waits (more useful than locks) in a pretty easy to diagnose way:

mysql> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2014-11-11 13:39:20
                    wait_age: 00:00:07
               wait_age_secs: 7
                locked_table: `db1`.`t1`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 867158
         waiting_trx_started: 2014-11-11 13:39:15
             waiting_trx_age: 00:00:12
     waiting_trx_rows_locked: 0
   waiting_trx_rows_modified: 0
                 waiting_pid: 3
               waiting_query: UPDATE t1 SET val = val + 1 WHERE id = 2
             waiting_lock_id: 867158:2363:3:3
           waiting_lock_mode: X
             blocking_trx_id: 867157
                blocking_pid: 4
              blocking_query: UPDATE t1 SET val = val + 1 + SLEEP(10) WHERE id = 2
            blocking_lock_id: 867157:2363:3:3
          blocking_lock_mode: X
        blocking_trx_started: 2014-11-11 13:39:11
            blocking_trx_age: 00:00:16
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
1 row in set (0.01 sec)


Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?

As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything.

Right -- if you see that pg_stat_activity.waiting is "true" for an ALTER TABLE, that almost certainly means that it's patiently waiting for the ACCESS EXCLUSIVE lock on its target table, and its real work (rewriting the table if necessary, changing catalogs, rebuilding indexes, etc.) hasn't started yet.

  Is it safe for us to outright cancel our ALTER TABLE query? Or is it possible that the query has already modified something and cancelling it would leave our database in a halfway state of some kind?

Canceling queries (or, equivalently, rolling back a transaction) in PostgreSQL doesn't have any database corruption hazards which you might have been spooked by in certain other databases (e.g. the terrifying warning at the bottom of this page). That's why non-superusers are, in recent versions, free to use pg_cancel_backend() and pg_terminate_backend() to kill their own queries running in other backends -- they are safe to use without fretting about database corruption. After all, PostgreSQL has to be prepared to deal with any process getting killed off e.g. SIGKILL from the OOM killer, server shutdown, etc. That's what the WAL log is for.

You may have also seen that in PostgreSQL, it's possible to perform most DDL commands nested inside a (multi-statement) transaction, e.g.

BEGIN;
ALTER TABLE foo ...;
ALTER TABLE bar ...;
-- more stuff
COMMIT; -- or ROLLBACK; if you've changed your mind

(awesome for making sure that schema migrations go in either all-together or not at all.) You said, though:

  We did not wrap the ALTER TABLE in a transaction.

That's fine for a single command -- from the docs,

  PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

So canceling that ALTER TABLE, either through pg_cancel_backend() or a Ctrl-C issued from the controlling psql prompt, will have a similar effect as if you had done

BEGIN;
ALTER TABLE ... ;
ROLLBACK;

(though as you hopefully got to see, canceling that expensive ALTER TABLE can save the database from a lot of unnecessary grinding if you're just going to ROLLBACK anyway.)


MySQL Workbench - Create new server instance error

http://code-remind.blogspot.com.au/2011/12/on-macbookpro-10.html

NOTE: Where the author suggests using the path '/Applications/MAMP/bin/stoptMysql.sh' at the end... there's a typo.

Should be '/Applications/MAMP/bin/stopMysql.sh'


How To Prevent Live Servers From Detecting a Forced Failover?

Snapshot
Assuming the use of VMs, take a snapshot of the DR server after the source site is disconnected.  I might recommend shutting down the server first just so the snapshot is in a good, clean state.  After the DR testing is done, shut down the DR server and then revert it to the snapshot.  This is easy and cheap.  The only downsides are that the source site is unprotected during the test, and the DR node may be a little slower due to the snapshot depending on your storage infrastructure.  Oh, and your primary will have to have enough disk space to queue database replication until the sites are reconnected.

Use a Clone
Assuming that you're using VMs and have adequate disk space, clone the DR node, shut down the original DR node, and do the testing on the clone. When done, delete the clone and bring the original DR node online.  This solves two problems: 1) You don't have to resynchronize the entire database(s), and 2) You don't have to evict/join cluster nodes.

The downside of this approach is that during the time of the testing, the primary server has to queue all of the data, so transaction logs may get quite large.  In the test you performed, this was occurring so you apparently had enough disk space for your logs that it wasn't a problem.

Use Even More Clones
Some organizations create a "bubble" network at the DR site that cannot communicate with the production DR network.  Then infrastructure servers (domain controllers, DNS, etc.) are cloned and the clones are brought online in the bubble.  Then the other servers that need to be part of the DR test are cloned into the bubble.  This allows you to keep your real DR servers online and functional during the DR test.  Other than truly failing over everything, this is about the safest, because what happens if the primary site gets destroyed 5 hours after the DR test starts?  If you've disconnected the sites, you lose 5 hours of data.

The downside of this, of course, is that it can become a massive undertaking and require a lot of staff time, hardware, etc.

Evict and Rejoin
As mentioned in the comments above, you can evict the DR node from the cluster before the DR site is reconnected, and then rejoin it back to the cluster and add the node back to the availability group.  This is fairly simple and requires the least hardware, but it does have the downside of leaving your primary site unprotected during the time of the DR test.


First Normal Form: Definitive Definition

Preliminary
The definition of normal form (which from the presentation of “Further Normalization of the Data Base Relational Model” in 1971 is known as first normal form) and the definition of the relational paradigm itself was published in 1970 in the scientific paper that provided a strong foundation for the practice of database administration, i.e., “A Relational Model of Data for Large Shared Data Banks” (RM for brevity) created by Dr. E. F. Codd, who is a Turing Award recipient and the authority with regard to the relational framework.
Yes, there is plenty of explanations, interpretations, expositions, deviations and opinions about Dr. Codd’s text, but I personally prefer to stick to the original source and I highly suggest that you analyze it by yourself so that you can draw your own conclusions.
I certainly do not understand the RM in its entirety, but what I do understand of it permits me appreciating its excellence, vision, intention and scope, and although decades later one can note that it has a few slight imprecisions, they do not reduce, in any way, its genius and elegance. In its field, the RM has stood the test of time in a unique way, and remains unmatched.
The act of emphasizing the aforementioned imprecisions would be —using a charitable term— unfair because, seeing it from from a considerable distance, this seminal material required a few refinements and extensions, yes, but the main body of the work was rock solid from the very conception (and, indeed, Dr. Codd made most —if not all— of such refinements and extensions himself).
I continue rereading the RM constantly in order to strengthen my comprehension of this exceptional source of knowledge (and my esteem of it keeps growing on every reread); the objective is to stand on the shoulders of giants.
Relations and tables
It is important to note that as relations are abstract resources,  Dr. Codd envisioned the utility of representing them in tabular form (he initially used the term “array representation” but subsequently utilized “table” or “r-table”), so that the users, designers and administrators of a relational database (RDB) can approach them in a more familiar or concrete manner. Therefore, within the context of a RDB implementation, it is valid to use table as a shorthand for relation, as long as said table stands for an actual relation. This feature is —although obvious— quite significant because before evaluating whether or not a table represents a relation that complies with first normal form (1NF), it has to represent, precisely, a relation.
The RM naturally contains the qualities that a table must have to determine if it in fact portrays a relation, but I will offer an informal and unpretentious interpretation about them here (another one, yes!):

It must have a name (each particular relation in a database structure must be distinguished from the rest).
Each of its rows must depict exactly one tuple of the pertinent relation.
The order of its rows is not important at all.
Each of its columns must have a name that stands for the meaning of exactly one domain of the concerning relation, and said name must be different from the names of the rest of the columns of the table (a column must be uniquely differentiated and must carry a distinctive meaning and, yes, the role played by a database modeler and the business experts to define each domain of significance with accuracy is paramount)
The order of its columns has no significance.
All of its rows must have the same number of columns.
It must have at least one column, or one combination of columns, that uniquely identifies every one of the tuples depicted via rows; in this way, all of the rows must be different (yes, this stresses the importance of having at least one KEY declared, and when there are two or more KEYs one should be defined as PRIMARY based on pragmatic reasons, while the rest can be deemed as ALTERNATE; but yes, before making the decision, each of the KEYs was a “candidate” for a definition as PRIMARY).

Having a table that in actual fact represents a relation is critical as, when it undergoes manipulation operations of a relational kind, then the result is, again, a table that represents a relation. In this manner, the behavior of said table is predictable.
Atomic domains (columns)
In the first sections of the RM, Dr. Codd presents several samples of relations in order to introduce some concepts; so, in order to comprehend the meaning of atomic domain, let us start with the following excerpt from the RM that details some pertinent points:

So far, we have discussed examples of relations which are defined on simple domains—domains whose elements are atomic (nondecomposable) values. Nonatomic values can be discussed within the relational framework. Thus, some domains may have relations as elements. These relations may, in turn, be defined on nonsimple domains, and so on.

In this way, one can say that each of the aforementioned expository relations fits in one of two kinds, say either kind A or kind B:

Kind A groups only relations (tables) that are structured with domains (columns) that contain exclusively simple values in every one of their tuples (rows), i.e., such domains (columns) do not contain relations (tables) as values, which in this context means that the values are atomic because they cannot be decomposed successively into new relations (tables). Hence, the relations of this class are the ones that are normalized, i.e., they comply with 1NF, their form is desirable.

Kind B is exclusively integrated by relations (tables) that have one or more domains (columns) that hold relations as values in each respective tuple (row), and that signifies that said values are nonatomic since they can be subsequently broken down into new relations (tables), i.e., they are decomposable. Thus, the relations of this sort are unnormalized, i.e., they infringe 1NF, they are in an undesirable form.

Normalization
Dr. Codd introduces the section about normalization in the RM with the following paragraph:

A relation whose domains are all simple can be represented in storage by a two-dimensional column-homogeneous array of the kind discussed above. Some more complicated data structure is necessary for a relation with one or more nonsimple domains. For this reason (and others to be cited below) the possibility of eliminating nonsimple domains appears worth investigating! There is, in fact, a very simple elimination procedure, which we shall call normalization.

Then he goes on to show:

A group of relations where one is unnormalized (it has domains that contain relations as values, i.e., they are nonatomic; i.e., they are non-simple)

A group of relations that are a normalized (i.e., one that was decomposed; i.e., one which relation valued domains were broken down into simple ones which signifies that they are atomic)

And then he describes the procedure for obtaining normalized relations from unnormalized ones.
In this respect, the relations he employed to illustrate a normalization exercise and the exercise description itself are quite clear, and I recommend again that you analyze them yourself (and I as well hope this encourages some readers to engage with the text).
Sucessively, he indicates:

Further operations of a normalizing kind are possible. These are not discussed in this paper.

And said operations, i.e., second and third normal form (2NF and 3NF) are actually detailed in “Further Normalization of the Data Base Relational Model”, and as mentioned above, after the presentation (and the later printing and publication) of this paper, the original normal form became known as first normal form.
As a practitioner can observe, having unnormalized relations (tables) introduces (almost always unnecessary) convolution into RDB implementations.
A relation that satisfies 1NF, eases the definition of constraints and data manipulation operations which can be implemented by means of a data sublanguage that is less complex than that required for unnormalized relations (tables), as Dr. Codd points out in the following lines:

The adoption of a relational model of data, as described above, permits the development of a universal data sublanguage based on an applied predicate calculus. A first-order predicate calculus suffices if the collection of relations is in normal form. Such a language would provide a yardstick of linguistic power for all other proposed data languages, and would itself be a strong candidate for embedding (with appropriate syntactic modification) in a variety of host languages (programming, command- or problem- oriented). […]
[…]
The universality of the data sublanguage lies in its descriptive ability (not its computing ability).

The bewilderment
From my point of view, the bewilderment has arisen, due to (a) the aforementioned excess of interpretations, explanations, etc., about 1NF and the RM itself, and because of (b) further attempts to redefine 1NF that state that having relations with domains that hold values that are, in turn, relations comply with 1NF as long as they are one single value for each corresponding tuple.
My take on your other pointss

There should not be any relationship between rows, other than that they conform to the same headers.

I am not sure if I understand the intention of that statement correctly but, apart from conforming to the same headers, there must be a connection between the (tuples) rows of a relation (table) as each of them should be an assertion about a particular occurrence of the specific entity type (defined in terms of the business context of interest) that the relation (table) is supposed to represent.

There should also be no relationship between columns, but I believe that is the subject of higher normal forms.

I do not know if I am properly interpreting the meaning of that statement either but, in fact, and in accordance with my response to the previous aspect, there must be a relationship between the domains (columns) of a relation (table) as well, which is precisely why it is a relation (the essential structure of the relational model and of a concrete RDB implementation).
To exemplify, with regard to the hypothetical relation (table)

Salary (PersonNumber, EffectiveDate, Amount)

the tuple (row)

Salary (x, y, z)

would convey the meaning

The Salary payed to the Person identified by PersonNumber x, on EffectiveDate y corresponds to the Amount of z

Therefore, each tuple (row) of the Salary relation (table) must fit into the structure of the assertion shown above, and the difference would be the replacement of the pertinent domain (column) values, but there must exist a relationship between (a) all the Salary domains (columns) and also between (b) all their corresponding values with respect to each tuple (row); such a relationship it is indispensable.
Higher normal forms (2NF and 3NF) are useful to get rid of functional dependencies between domains (columns) of a relation (table), they assist in avoiding undesirable connections between domains (columns), as said undesirable connections allow the introduction of update anomalies. Both 2NF and 3NF are helpful to test the soundness of the structure of the relations (tables) in a certain RDB implementation.


I'd highly recommend using Ola Hallengren's index maintenance scripts, they're very widely used.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

I personally set the script to Reorganise at 50% and Rebuild at 80% (and only check indexes over 1000 pages). It's recommended to have this as an overnight run if you have the maintenance window.

Beware that the first time you run this it may have to do quite a lot of work.

As a side note, do you know that fragmentation is actually causing your performance slow down? It could be any number of things unless you've already narrowed this down. I'd recommend using Brent Ozar's scripts for a health check if you haven't already. Start with sp_Blitz and go from there.

https://www.brentozar.com/blitz/


No, they hide the standard implementation in implscope.cpp

  // This builtin map is a javascript 6 thing.  We want our version.  so
  // take theirs out

and implement a new one in types.js.

Well, you can workaround doing:

Map.prototype.set = Map.prototype.put


How do I connect to a SQL Server 2008 instance on my network?

To answer your question we really need more information like errors.

Firstly need to ensure your instance allows remote connections by configuring network protocols in SQL server configuration tool.

Also need to check if your connecting to a default instance or named..

If your still stuck post more information about your scenario.


Best practices, MySQL table with 150+ columns, many null

Consider an Entity-Attribute-Value design.  The general concept is that you put all of the data in a very long, narrow table which may take the form of:

CREATE TABLE dbo.PropertyAttributes
(
PropertyID   INT NOT NULL
             REFERENCES dbo.Properties(PropertyID),
AttributeID  INT NOT NULL
             REFERENCES dbo.Attributes(AttributeID),
StringValue  NVARCHAR(1024),
NumericValue DECIMAL(16,4),
DateValue    SMALLDATETIME,
ModifiedDate SMALLDATETIME NOT NULL
             DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (PropertyID, AttributeID)
);

Then you store metadata about the attributes.  At a minimum you'd need something like this:

CREATE TABLE dbo.Attributes
(
AttributeID INT PRIMARY KEY,
PropertyID  INT NOT NULL
            REFERENCES dbo.Properties(PropertyID),
[Name]      NVARCHAR(32) NOT NULL UNIQUE,
DataTypeID  TINYINT NOT NULL -- 1 = string, 2 = numeric, 3 = date
);

Taking this approach offers some benefits because when new attributes show up in the data, you don't have to modify the database structure to house the data and you don't have to construct new queries to get at the new columns you created.

Like other aspects of life, these benefits come with tradeoffs.  Creating a very wide view requires you to Pivot the table which can be an expensive query to run.

Aaron Bertrand, who posts here quite a bit, described his experience with this approach in 2009.  It's still worth a read today.

MDCCL's suggestion to review attributes and potentially decompose the most relevant ones is a good one, move the most commonly used attributes into the base Properties table so you pivot the EAV table less frequently.


Mongodb Cross-region failover replication aws

I have deployed 3 MongoDB nodes(node-1, node-2, node-3) in AWS. The
  first two nodes (node-1, node-2) are deployed in Virginia region and
  the third node(node-3) is deployed in Oregon region and enabled VPC
  peering and the replication is working fine . But, Now if the Virginia
  goes down the third node(node-3) is still in Secondary. I have to
  manually make the node as Primary. Is there any automated way or
  alternate solution to do this.

Yes, As per MongoDB Blog documentation here MongoDB Atlas ensures high availability with a fault-tolerant and self-healing architecture. Replica set members are distributed across availability zones in a customer’s selected region; should a node fail, the election and failover process happen automatically without intervention. You can also choose from 41 regions across AWS, Azure, GCP, ensuring that your managed databases are in close proximity to your application servers for minimal geographic latency.

You can deploy across multiple regions for better availability guarantees, meaning that replica set members in additional regions will participate in the election and automated failover process should your primary fail. Note that you can also select your preferred region; this is the region where reads and writes will default to assuming that there are no active failure or failover conditions.

You can also deploy read-only replica set members. Read-only replica set members allow you to optimize for local reads (reduce read latency) across different geographic regions using a single MongoDB deployment. As the name suggests, these replica set members will not participate in the election and failover process and can never be elected to a primary replica set member.

For further your ref here and here


Print the execution time on messages Pane?

DATEDIFF will be useful

DECLARE @StartTime1 datetime

SET @StartTime1 = GETDATE()

select query 1

SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime1, getdate())

GO

similarly do for other queries. @starttime2, 3


A simple T-SQL query plan reuse question

...it fails to reuse the plan if the length of one of the VARCHAR fields changes. Do I have any recourse short of padding the data?

This is by design, and no you should not pad the data.
The ideal solution is to declare your variable size to the same length as the data type of the column it's being inserted into.  This will give you predictable plan reuse without having to send extra whitespace data.
The way you do this will vary based on how the insert is being performed.  If you're using stored procedures, just update the parameters to use the same length as the columns.  If you're using .NET's AddWithValue method (which is evil), use one of the APIs that allows you to set the parameter length.


concurrent insertion in mutually exclusive tables in oracle

One way is to add discriminator column, something like

create table c (id_c int not null, val char(1))
create index idx_c1 on c(id_c,val);
alter table c add constraint chk_val check(val in ('a','b'));

alter table c add constraint c_pk primary key (id_c) using index idx_c1;
alter table c add constraint c_uq unique (id_c,val) using index idx_c1;

create table a(id_a int not null primary key,
val char(1) default 'a' not null ,
constraint fk_t foreign key(id_a,val)  references c (id_c,val));

alter table a add constraint chk_val_a check (val ='a');

Similar for table (but check constraint should b check(val='b').

Then detail record can be in table a or b, but not in both. Overhead is small thanks to the fact Oracle allows multiple constraint supported by the same index.


Query challenge: Creating even sized buckets, based on a measure not row count

My first thought was

select
    <best solution>
from
    <all possible combinations>

The "best solution" part is defined in the question - the smallest difference between the most loaded and least loaded trucks. The other bit - all combinations - caused me pause for thought.

Consider a situation where we have three orders A, B and C and three trucks. The possibilities are

Truck 1 Truck 2 Truck 3
------- ------- -------
A       B       C
A       C       B
B       A       C
B       C       A
C       A       B
C       B       A
AB      C       -
AB      -       C
C       AB      -
-       AB      C
C       -       AB
-       C       AB
AC      B       -
AC      -       B
B       AC      -
-       AC      B
B       -       AC
-       B       AC
BC      A       -
BC      -       A
A       BC      -
-       BC      A
A       -       BC
-       A       BC
ABC     -       -
-       ABC     -
-       -       ABC

Table A: all permutations.

Many of these are symmetric. The first six rows, for example, differ only in which truck each order is placed. Since the trucks are fungible these arrangemets will produce the same outcome. I shall ignore this for now.

There are known queries for producing permutations and combinations. However, these will produce arrangements within a single bucket. For this problem I need arrangements across multiple buckets.

Looking at the output from the standard "all combinations" query

;with Numbers as
(
    select n = 1
    union
    select 2
    union
    select 3
)
select
    a.n,
    b.n,
    c.n
from Numbers as a
cross join Numbers as b
cross join Numbers as c
order by 1, 2, 3;

  n   n   n
--- --- ---
  1   1   1
  1   1   2
  1   1   3
  1   2   1
 <snip>
  3   2   3
  3   3   1
  3   3   2
  3   3   3

Table B: cross join of three values.

I noted the results formed the same pattern as Table A. By making the congnitive leap of considering each column to be an Order1, the values to say which truck will hold that Order, and a row to be an arrangement of Orders within trucks. The query then becomes

select
    Arrangement             = ROW_NUMBER() over(order by (select null)),
    First_order_goes_in     = a.TruckNumber,
    Second_order_goes_in    = b.TruckNumber,
    Third_order_goes_in     = c.TruckNumber
from Trucks a   -- aka Numbers in Table B
cross join Trucks b
cross join Trucks c

Arrangement First_order_goes_in Second_order_goes_in Third_order_goes_in
----------- ------------------- -------------------- -------------------
          1                   1                    1                   1
          2                   1                    1                   2
          3                   1                    1                   3
          4                   1                    2                   1
  <snip>

Query C: Orders in trucks.

Expaning this to cover the fourteen Orders in the example data, and simplifying the names we get this:

;with Trucks as
(
    select *
    from (values (1), (2), (3)) as T(TruckNumber)
)
select
    arrangement = ROW_NUMBER() over(order by (select null)),
    First       = a.TruckNumber,
    Second      = b.TruckNumber,
    Third       = c.TruckNumber,
    Fourth      = d.TruckNumber,
    Fifth       = e.TruckNumber,
    Sixth       = f.TruckNumber,
    Seventh     = g.TruckNumber,
    Eigth       = h.TruckNumber,
    Ninth       = i.TruckNumber,
    Tenth       = j.TruckNumber,
    Eleventh    = k.TruckNumber,
    Twelth      = l.TruckNumber,
    Thirteenth  = m.TruckNumber,
    Fourteenth  = n.TruckNumber
into #Arrangements
from Trucks a
cross join Trucks b
cross join Trucks c
cross join Trucks d
cross join Trucks e
cross join Trucks f
cross join Trucks g
cross join Trucks h
cross join Trucks i
cross join Trucks j
cross join Trucks k
cross join Trucks l
cross join Trucks m
cross join Trucks n;

Query D: Orders spread over trucks.

I choose to hold the intermediate results in temporary tables for convenience.

Subsequent steps will be much easier if the data is first UNPIVOTED.

select
    Arrangement,
    TruckNumber,
    ItemNumber  = case NewColumn
                    when 'First'        then 1
                    when 'Second'       then 2
                    when 'Third'        then 3
                    when 'Fourth'       then 4
                    when 'Fifth'        then 5
                    when 'Sixth'        then 6
                    when 'Seventh'      then 7
                    when 'Eigth'        then 8
                    when 'Ninth'        then 9
                    when 'Tenth'        then 10
                    when 'Eleventh'     then 11
                    when 'Twelth'       then 12
                    when 'Thirteenth'   then 13
                    when 'Fourteenth'   then 14
                    else -1
                end
into #FilledTrucks
from #Arrangements
unpivot
(
    TruckNumber
    for NewColumn IN
    (
        First,
        Second,
        Third,
        Fourth,
        Fifth,
        Sixth,
        Seventh,
        Eigth,
        Ninth,
        Tenth,
        Eleventh,
        Twelth,
        Thirteenth,
        Fourteenth
    )
) as q;

Query E: Filled trucks, unpivoted.

Weights can be introduced by joining to the Orders table.

select
    ft.arrangement,
    ft.TruckNumber,
    TruckWeight = sum(i.Size)
into #TruckWeights
from #FilledTrucks as ft
inner join #Order as i
    on i.OrderId = ft.ItemNumber
group by
    ft.arrangement,
    ft.TruckNumber;

Query F: truck weights

The question can now be answered by finding the arrangement(s) which have the smallest difference between most-loaded and least-loaded trucks

select
    Arrangement,
    LightestTruck   = MIN(TruckWeight),
    HeaviestTruck   = MAX(TruckWeight),
    Delta           = MAX(TruckWeight) - MIN(TruckWeight)
from #TruckWeights
group by
    arrangement
order by
    4 ASC;

Query G: most balanced arrangements

Discussion

There are a great many problems with this. First it is a brute-force algorithm. The number of rows in the working tables is exponential in the number of trucks and orders. The number of rows in #Arrangements is (number of trucks)^(number of orders). This will not scale well.

Second is that the SQL queries have the number of Orders embedded in them. The only way around this is to use dynamic SQL, which has problems of its own. If the number of orders is in the thousands there may come a time when the generated SQL becomes too long.

Third is the redundancy in the arrangements. This bloats the intermediate tables increasing runtime hugely.

Fourth, many rows in #Arrangements leave one or more trucks empty. This cannot possibly be the optimum configuration. It would be easy to filter out these rows upon creation. I've chosen not to do so to keep the code simpler and focused.

On the up side this handles negative weights, should your enterprise ever start shipping filled helium baloons!

Thoughts

If there were a way to populate #FilledTrucks directly from the list of trucks and Orders I think the worst of these concerns would be managable. Sadly my immagination stumbled on that hurdle. My hope is some future contributor may be able to supply that which eluded me.

1 You say all items for an order must be on the same truck. This means the atom of assignment is the Order, not the OrderDetail. I have generated these from your test data thus:

select
    OrderId,
    Size = sum(OrderDetailSize)
into #Order
from #OrderDetail
group by OrderId;

It makes no difference, though, whether we label the items in question 'Order' or 'OrderDetail', the solution remains the same.


Backup with date to azure blob storage url

You can't do concatenation in the backup command. But you can have a variable for the file name, so construct the file name first into the variable and use that variable in the backup command.


Unbelievably slow and unusable Query Store

I have the same issue, and upon investigating, discovered the root issue in slow Query Store queries is repeated access of the in-memory TVFs. It doesn't matter how many rows are returned from these TVFs, only the number of times they are accessed. The fix (until MS fixes underlying stuff) will be whatever you can do to nudge your plans away from reading them multiple times. Some more information in this related question and in my blog post.

Ultimately, I was able to fix this with a plan guide in production, using hints that I found moved the optimizer away from repeated TVF executions. Since the exact query text is required, and the query rarely finishes, I built a script that would create a plan guide while the report is running by pulling the text from exec_requests. Follow the below steps to create a plan guide.

Open up Top Resource Consuming Queries
Run the below script
while the report is still spinning
Cancel the report and run
again.

--USE YourDB
DECLARE @badqry NVARCHAR(MAX)

SELECT TOP (1) @badqry = SUBSTRING(st.text, (r.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE r.statement_end_offset
    END - r.statement_start_offset)/2) + 1)
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE st.text LIKE '(@results_row_count int,@interval_start_time%'

IF @badqry IS NULL
RAISERROR('Missed the plan',16,1)

EXEC sp_create_plan_guide
@name = N'Fixing QueryStore Top Duration',
@stmt = @badqry,
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@results_row_count int,@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',
@hints = N'OPTION (HASH JOIN, LOOP JOIN)'

You should be able to run the above script for most of the canned Top Resource queries, but make sure to change the plan guide name if you do. Also, there's certainly a better hint out there than HASH JOIN, LOOP JOIN, but it's worked well enough for me so far.


Better database for "keep always the 5 latest entries per ID and delete older"?

One option I can think of is to delete the oldest row as soon as you insert a new one. That will only require a very quick lookup limited to at most 6 rows rather then going through all rows at a time.

To do that efficiently you need a unique key on the table:

create table history
(
  id          serial primary key, -- to make a lookup on a single row efficient
  fooid       text not null,
  target      text not null,
  updated_at  timestamp not null default now()
);
-- to make finding the oldest row for one fooid efficient
create index on history(fooid, updated_at);

Then create a trigger that only keeps the 5 most recent rows for a fooid:

create or replace function remove_last()
  returns trigger
as
$$
begin
  with ranked as (
    select id, row_number() over (partition by fooid order by updated_at) as rn
    from history
    where id <> new.id
      and fooid = new.fooid
  )
  delete from history
  where id in (select id
               from ranked
               where rn >= 5);
  return new;
end;
$$
language plpgsql;

create trigger remove_last_trigger
  after insert on history
  for each row execute procedure remove_last();

On my laptop, a simple test setup with 5 million rows (1 million different fooid values) showed a trigger overhead that was less then a millisecond:

insert into history (fooid, target) values ('1', 'new stuff');

QUERY PLAN
----------------------------------------------------------------------------------------------------
Insert on stuff.history  (cost=0.00..0.01 rows=1 width=76) (actual time=0.062..0.062 rows=0 loops=1)
  Buffers: shared hit=8
  ->  Result  (cost=0.00..0.01 rows=1 width=76) (actual time=0.017..0.017 rows=1 loops=1)
        Output: nextval('history_id_seq'::regclass), '1'::text, 'new stuff'::text, now()
        Buffers: shared hit=1
Planning time: 0.024 ms
Trigger remove_last_trigger: time=0.438 calls=1
Execution time: 0.524 ms

How much overhead that is in your system and whether or not that is acceptable for you I don't know, but "thousand changes per day" doesn't sound like a very busy system.


ALTER PROCEDURE dbo.SEARCH_QUERYS -- always use schema prefix!
        @ID   int,
        @LIVE bit,
        @DATA datetime
AS
BEGIN
        SELECT ID, LIVE, DATA --, ... don't use SELECT *!
        FROM dbo.TB_SEARCH -- always use schema prefix!
        WHERE
        (ID = @ID OR @ID IS NULL)
        AND (LIVE = @LIVE OR @LIVE IS NULL)
        AND (DATA = @DATA OR @DATA IS NULL); -- always use semi-colons!
END
GO

In some cases though you are better off building the query dynamically.

Refs:

Not using SELECT *
Using schema prefix
Using statement terminators


List database CONNECT privileges on PostgreSQL

You can use the function as part of a select that retrieves all users:

SELECT rolname, has_database_privilege(rolname, 'postgres', 'connect')
from pg_roles
where rolcanlogin;

You can extend that, to show this information for every user in every database:

select db.datname, r.rolname, has_database_privilege(r.rolname, db.datname, 'connect')
from pg_roles r
  cross join pg_database db
where r.rolcanlogin
  and db.datallowconn
order by db.datname, r.rolname;


UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 0  AND id < 10;
 UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 10 AND id < 20;;
  UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 20  AND id < 30;

That's insane especially if the query is not CPU bound. Just run one query with one update.

UPDATE targate_table set col1_target = col1_source
FROM source_table
WHERE (id BETWEEN 0 AND 10)
  OR (id BETWEEN 10 AND 20)
  OR (id BETWEEN 20 AND 30);

PostgreSQL doesn't lock the table for row updates. It only locks the rows. There is no reason to break this up.


How to select * if sets of data in two columns matches >= 4 times

You are counting the occurrences of Unit and Equipment across the entire dataset, not specifically within the period, in which some combinations may well occur more than 3 times while others fewer times or never.

Therefore, you need to apply the same date range filter to the derived table to make the results reflect your requirements:

select   m.mrid, a.unit, a.Region, m.Equipment
from     MASTER36 m
join     MASTER36_ABDATA a
on       m.mrid = a.mrid
join     (select   a.unit, m.Equipment
           from     MASTER36 m
           join     MASTER36_ABDATA a
           on       m.mrid = a.mrid
           where    mrSUBMITDATE between '2/7/17' and '3/7/17'
           group by a.unit, m.Equipment
           having   count(*) > 3
         ) t2
on       a.unit = t2.unit
and      m.Equipment = t2.Equipment
where    mrSUBMITDATE between '2/7/17' and '3/7/17'
and      mrstatus <> 'DELETED'   -- these two can be inlined:
and      mrstatus <> 'Canceled'  -- mrstatus not in ('DELETED', 'Canceled')
and      m.Equipment  <> 'BLDNG';

Note: as you can see, I have also rewritten the join to the derived table to use the explicit join syntax, for consistency.

You could also try this alternative which references each table only once and does not repeat any part of the logic:

select   mrid, unit, Region, Equipment
from     (
           select   m.mrid, a.unit, a.Region, m.Equipment, mrStatus,
                    cnt = count(*) over (partition by a.unit, m.Equipment)
           from     MASTER36 m
           join     MASTER36_ABDATA a
           where    mrSUBMITDATE between '2/7/17' and '3/7/17'
           and      m.Equipment  <> 'BLDNG'
         ) as derived
where    cnt > 3
and      mrstatus not in ('DELETED', 'Canceled');

Instead of counting rows with a normal aggregate function, the query uses a window aggregate function. A window aggregate function lets you return aggregate results along with non-aggregate data.

In the above query you can see that part of the logic is applied directly to the joined tables, at the scope where the rows are counted. Other filters are applied at the outer level, to the derived table, so as not to affect the results of count(*), thus matching the logic of the original query.

The filter on count(*) is also applied outside the derived table, because computed columns cannot be filtered on in the same scope where they are defined and window functions in particular cannot be used in a WHERE clause.

As a final note, please consider these points for maintainability and clarity of your queries:

Use a locale-/culture-independent format for your date/time literals. As you are using dates, consider YYYYMMDD.
Use open-ended ranges for dates and/or times.
Always qualify columns with table names or aliases where more than one table is involved.
End your statements with the ; statement terminator.


Extra quotes around dates are a problem (as an aside you cannot pass character strings into float columns, which is why your table variable insert also fails).

Using extra quotes can be done at the dynamic layer, and using CHAR(39) rather than embedding a whole load of quotes can make the code a lot more readable don the line.

As an addition, initially selecting out the dynamic SQL so that you can run the generated statement in another window to test your results is also a good thing when starting out.

DECLARE @Table1 TABLE
    (
      name VARCHAR(100) ,
      d1 DATE ,
      amt FLOAT
    );
INSERT  INTO @Table1
        ( name, d1, amt )
VALUES  ( 'Red', '01/01/2015', 14.00 ),
        ( 'Red', '01/01/2016', 23.00 ),
        ( 'Red', '01/03/2015', 22.00 ),
        ( 'Red', '02/01/2015', 33.00 ),
        ( 'Red', '03/04/2015', 10.00 ),
        ( 'Red', '04/04/2016', 10.00 ),
        ( 'Pink', '01/01/2015', 4.00 ),
        ( 'Pink', '04/05/2016', 5.00 );

DECLARE @ItemList NVARCHAR(500) = N'''Red''';
DECLARE @startdate DATE = '01/01/2015';
DECLARE @enddate DATE = '12/31/2015';

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'Select *
FROM OPENQUERY(linkedserver,
''Select
name
,SUM(CAST(amt As DECIMAL(10,2)) [Total]
FROM @Table1
WHERE name IN (' + REPLACE(@ItemList, '''', '''''') + N')
    AND d1 BETWEEN (' + CHAR(39) + CHAR(39)
    + CAST(@startdate AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
AND (' + CHAR(39) + CHAR(39) + CAST(@enddate AS NVARCHAR(500)) + CHAR(39)
    + CHAR(39) + N')
''
);
';
SELECT @sql;
--EXEC sys.sp_executesql @sql;


The GRAPHIC data type seems to be a remnant from the times when there was no widespread Unicode support. It was there to facilitate storage of double-byte characters. In essence GRAPHIC(3) is equivalent to CHAR(3 CODEUNITS16).

Apart from the length semantics (1 byte vs. 2 byte characters) (VAR)CHAR and (VAR)GRAPHIC are functionally equivalent. Now that you have an option of specifying the length units along with the data type, I really see no reason to use the GRAPHIC types.

Since a UTF-8 character can occupy anywhere between 1 to 4 bytes, using any fixed-number-of-bytes character assumption would cause either risk of truncation or wasted space. To avoid the latter you might consider using VARCHAR instead of CHAR if your application can tolerate that.


Microsoft Assessment and Planning Toolkit should do the trick.

  MAP performs a detailed analysis of hardware for migration to Windows 8, Windows 7, Windows Server 2012, Windows Server 2008 R2, SQL Server 2012, SQL Server 2008 R2, Microsoft Office 2010, and Office 365.

It specifically mentions SQL 2012 at some point, but I guess the same would work for 2008 R2 aswell:

  Discovery of SQL Server 2012 databases, instances, and selected characteristics.

And here it says:

  Detailed reporting:
  Covers specific SQL Server instances that include
  component name, version, edition, and more. Includes wide-ranging
  details of databases and server instances that can be used for
  consolidation.


Having identical column names across tables is a good thing and a common design practice. It makes writing queries easier because the naming standards are more consistent. When joining them in a query, you'll just have to qualify each identical column name with the table name or alias.
If you wanted to get all the post titles and any comments along with the date the comment was made, it would look like this:
SELECT p.Id, p.Title, c.CreatedOn, c.Content
FROM Posts AS p
LEFT JOIN Comments AS c ON c.PostId = p.Id

If you leave off the table name or alias from the CreatedOn column (the "c.", in this case), SQL Server can't tell which CreatedOn column you're asking it for, and you'll get an error like this:

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'CreatedOn'.

Technically, you can get away with not aliasing the unambiguous columns (like Title, in this example, because it doesn't exist in the Comments table), but it's better to alias all or none of the columns for readability's sake.
Is what you're doing with two tables a bad design? If my assumption is correct that there can be multiple comments per post, you're right to split them into separate tables. It's a true one-to-many relationship and those belong in separate tables. Entity Framework shouldn't have any trouble with this design.


What Is the Next Best Ingredient to Add?

If you assume a 3NF design in the entity relationship diagram below:

Recipes are stored in a Recipe table, a recipe's ingredients are stored in  RecipeIngredients table, and the MyIngredients table contains an entry for each ingredient on hand.

Your Next Best Ingredient(s) is found with the following query:

SELECT Ingredients.IngredientID
  , Ingredients.IngredientName
  , count() RecipesCompleted
FROM Ingredients
 INNER JOIN RecipeIngredients on Ingredients.IngredientID = RecipeIngredients.IngredientID
 INNER JOIN (
   SELECT RecipeID
     , count() RemainingIngredients
   FROM Recipes
     INNER JOIN RecipeIngredients on Recipes.RecipeID = RecipeIngredients.IngredientID
     LEFT JOIN MyIngredients on RecipeIngredients.IngredientID = MyIngredients.IngredientID
   WHERE MyIngredients.IngredientID is null
   GROUP BY RecipeID
   HAVING count(*) = 1) RecipesWOneIngLeft
      on RecipeIngredients.RecipeID = RecipesWOneIngLeft.RecipeID
 LEFT JOIN MyIngredients on RecipIngredients.IngredientID = MyIngredients.IngredientID
WHERE MyIngredients.IngredientID is null
GROUP BY Ingredients.IngredientID
 , Ingredients.IngredientName
ORDER BY RecipesCompleted DESC

Let's break the query down a bit.  It works using the following theories:

By inserting your ingredients into the MyIngredients table allows, you're allowed to have N ingredients.
The next step is to use your algorithm to identify all recipes which are missing just one ingredient.  This is done in the RecipesWOneIngLeft sub-query
The final step counts the number of recipes which are completed by each ingredient and then orders the result so the NBI is first.

Consider rewording your plain english algorithm to the following:

When user searches N ingredients, isolate ALL remaining recipes such that there is 1 (and only 1) vacant slot for an “unsearched” ingredient. Tally up the frequency of all “unsearched” ingredients. Whichever “unsearched” ingredient has the highest frequency becomes the NEXT BEST INGREDIENT.


Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

PARTITIONing is not a performance panacea.

For

WHERE artist = 'constant'
  AND uploadDate BETWEEN ...

INDEX(artist, uploadDate) on a non-partitioned table is as good (or better) than using partitioning.

Do you have other queries?  So far you have not presented a convincing case for partitioning.

Will you be purging 'old' data?  Being able to use DROP PARTITION instead of DELETE is one of the few benefits of PARTITION BY RANGE.

More Discussion


Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds

My suggestion would be:

(a) talk to Azure support. This is not how it should be working AFAIK.

(b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column:

SELECT name, etc.
 FROM sys.indexes AS i
 INNER JOIN sys.dm_db_index_physical_stats AS s
 ON ...
 WHERE ... criteria ...
 AND NOT EXISTS
 (
   SELECT 1 FROM sys.index_columns AS ic
     INNER JOIN sys.columns AS c
     ON ic.object_id = c.object_id
     AND ic.column_id = c.column_id
     AND ic.key_ordinal = 1 -- only leading column
     AND c.system_type_id = 36 -- uniqueidentifer
     WHERE ic.index_id = i.index_id
 );

(c) before deciding to manually deal with these columns, wait until they actually cause you a performance problem that you can demonstrate and undeniably identify the fragmentation of this index as the problem.

(d) or, just use the inverse query in (b) to perform manual drop/create all the time instead of dealing with the problem over and over.


Really "a Class must have at least one Instructor" constraint could be checked only at transaction commit, because first we need insert a Classes and only after at least one Classes exists we can refer it in Instructors_Classes. No, currently most DBMSes do not implement features like that. Constraints are checked per statements, this check is not defered till transaction commit. Postrgesql can defer FK and some others constraints untill commit (thanks, ypercubeᵀᴹ).
You may implement a trigger on delete from Instructors_Classes which will prevent deletion of the last instructor of the class.

But your second "primary instructor for a class" requirement helps a bit. Add Classes.PrimaryInstructor  referencing Instructors_Classes nullable. After  Classes.PrimaryInstructor value  is set this FK will ensure (with proper ON DELETE/UPDATE action) at least primary instructor for a class persists.
EDIT
To be precise above FK should be composite including also Classes PK ,

create table Classes (
...
constraint FK_prim_instr foreign key (class_id, primary_instructor) references class_instructors(class_id,instructor_id)
...
);

EDIT 2
Sample script

-- MS Sql Server 2014

create table classes (
    class_id int not null primary key
    , primary_instructor int null
);

create table class_instructors(
    class_id int not null
    , instructor_id int not null
    , constraint PK_cl_instr
      primary key (class_id, instructor_id)
    , constraint FK_class_instr
      foreign key (class_id)
      references classes (class_id)
);
go
alter table classes
add constraint FK_Prim_instr
      foreign key (class_id, primary_instructor)
      references class_instructors(class_id, instructor_id)

insert classes (class_id)
values (1);

insert class_instructors (class_id,instructor_id)
values (1, 10),(1,20);

update classes
set primary_instructor = 20
where class_id = 1;

select * from classes;
select * from class_instructors ;

-- this will fail
insert class_instructors (class_id,instructor_id)
values (2,10);

-- this will fail too
update classes
set primary_instructor = 30
where class_id = 1;


Why does In-Memory feature not support foreign keys?

Because they had to ship the feature. If they waited until it was complete, we'd still be waiting for it, and all of those customers who didn't need foreign keys (or were willing to sacrifice them temporarily for the performance gains) would never have been able to deploy.

As with many large features with deep roots into the engine (remember the first iteration of ColumnStore? How about the limited surface area in the inaugural version of Azure SQL Database?), the v1 always has a limited amount of features.

They come around later, though. For example, in SQL Server 2016, you will be able to have a foreign key reference to a primary key constraint (but I don't believe to a unique constraint). You can see a lot of the enhancements that will be available in "v2" in the following posts:

SQL Server 2016 : In-Memory OLTP Enhancements
In-Memory OLTP - what's new in SQL2016 CTP3
SQL Server 2016 Community Technology Preview 3.1 is available


Why do these foreign key constraints cause problems? (Azure SQL Server 12)

The problem seem to be the ON UPDATE CASCADEs, the ON DELETEs are fine. I don't get an error if I remove the ON UPDATEs.

According to the accepted answer in "Foreign key constraint may cause cycles or multiple cascade paths?" on Stack Overflow, SQL Server does no "deep" inspection of the cascade paths but just a shallow one. If there is any cascading constraint to a table A in a table B, that already induces an edge in the cascade graph between A and B, regardless of the targeted columns.

So here we have a path directly from Worker to Note and a path from Worker via Business to Note. Note appears twice in the graph.

Though here, this raises no conflict on a specific column, this is already enough as the Microsoft support article "Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths:" states:

  You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

Triggers are suggested to work around this. However, I see a major problem with triggers, as I don't know any way to map rows from the inserted pseudo table with them of the deleted pseudo table other than via the primary key. But if that has changed in the update, there is no possible match and no way to detect the pair of changed values.

So I guess there is no way other than not updating primary keys and use an ON UPDATE NO ACTION. And usually one doesn't really need to update primary keys anyway, even though it would be a nice to have as an option.


Best way to store immutable/read-only copies of data for logging?

You could treat your reference tables as slowly changing dimensions and perform type 2 maintenance i.e. add a new reference row when any reference data changes.  Over time you may choose to horizontally partition your reference tables into "active" and "archive" parts, depending on the data churn you experience.  This can be achieved using using SQL Server's built-in partitioning functionality or a roll-your-own approach with two separate tables.  Your needs at the time will dictate which.

There is no good reason why your active OLTP table and your logging table should look the same.  They perform different roles and have different read and write requirements.  If the logging table needs to be wide and sparse then that's what it needs to be.  Create the objects to solve the problem you have.

And one last suggestion, which is defininetly from the "clutching at straws" bucket: define TASK_DONE (id int, reference_values xml).  Extract your pertinent values at the point in time and save them away.  This allows for changes in the reference data schema without having to bring existing log records up to the new schema.  That will make historical searching more complicated, of course.


When will PostgreSQL execute archive_command to archive wal files?

The archive command is executed every time it switches the archive log to a new one.
Which as you say can be triggered manually by calling the pg_switch_xlog() function.

Other than that, an archive log needs to be changed to a new one when it is full, which by default is when it reaches 16MB, but can be changed at compile time.

You can also specify a timeout value using the parameter archive_timeout which will execute the command after the set amount of seconds, which is useful for databases that have low activity.

Update:

Version 10 introduced a change that renames everything referencing "xlog" to "wal", so pg_switch_xlog() is now called pg_switch_wal()


How do you see which database on a Server uses most resources

Only some resources can be tracked to databases. IO and buffer pool usage can clearly be tracked to a database:

IO usage: sys.dm_io_virtual_file_stats
Buffer pool usage: sys.dm_os_buffer_descriptors

The query execution statistics (like logical reads) can be attributed to queries, but cannot be associated (correctly) with a database because queries can (and often do) span databases. sys.dm_exec_query_stats is the main entry point for these stats.


How do I determine the appropriate size for my tablespace when importing a .dmp file?

At least 14 GB.

Maybe more if the dump file is compressed.

Maybe less if the tables are compressed.

A bit more or lot more if there are indexes.

Even Data Pump will miscalculate the required space in case of compressed tables.

Index data is not stored in the dump file, only index definitions. You can have a 14 GB dump file with 14 GB table data and 0 index definitions or hundreds of index definitions, and building those indexes may consume a lot more storage than the base tables, or just a fraction of it.

So the correct answer in my opinion is, test and measure it.


Type the following command right after you receive your error-message:

show errors

Show here:

SQL> CREATE TYPE boolean_typ AS OBJECT
  2  (valor char(5) CHECK (VALUE IN ('true', 'false', NULL)) );
  3  /

Warning: Type created with compilation errors.

SQL> show errors
Errors for TYPE BOOLEAN_TYP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16     PLS-00103: Encountered the symbol "CHECK" when expecting one of
         the following:
         := ) , not null default external character

SQL> drop type boolean_typ;

Type dropped.

SQL> exit


MS SQL Server 2016 - Always On Node-2 Stuck In Resolving State

If I read correctly, your failover mode is set to "manual". In this case, SQL will not trigger a failover by itself.
If you are expecting node 2 to be promoted as primary automatically when node 1 goes down, then you will need to change the failover mode to "automatic" and then, that should do it.


You could use CASE WHEN operator for solving this:

ORDER BY status,
    CASE status
        WHEN 'active' THEN planned_date - now()
        WHEN 'inactive' THEN now() - planned_date
    END


Try this one instead, it appears to be more active than the one you're using.
Another very active project for such tools with SQLite is here. Your tool hasn't been updated for over a year - that's usually a sign that the project is oprhanware.


How do I select data with a case statement and group by?

The problem is that you can't use the alias Stops in the GROUP BY. In your query when you are using the GROUP BY it is using the individual values for each row. You'd need to use a subquery to get the result:

select stops, count(*) Total
from
(
  select
    CASE
      when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop'
      ELSE Stops
    END as Stops
  from yourtable
) d
group by stops;

See SQL Fiddle with Demo.

Or if you don't want to use a subquery, then you could repeat the CASE expression in the GROUP BY:

select
  CASE
    when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop'
    ELSE Stops
  END as Stops,
  count(*)  as Total
from yourtable
group by
  CASE
    when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop'
    ELSE Stops
  END

See SQL Fiddle with Demo


You must divide what value is an attribute for what entity.

For example, Name is an attribute of a Person, so it is stored in Person table, and when you need the Name for some Employee you'll join these tables and get the Name by reference (i.e. there is no Name field in Employee table). But when you need Employee position you will take (and store) it from Employee table because the position is not an attribute of Person, but Employee.

  some person (customer and employee) can be a user (can be login)

The same.

If the login does not depend on whether the Person is an Employee or a Customer (i.e. after logging in the Person may perform actions which belongs to both/either as Employee and/or as Customer), it is an attribute of Person and is stored in the Person table.

But if a certain login is the login of the Employee only (can perform Employee actions, but cannot be used by the Customer and perform Customer actions) - it is not an attribute of Person, but Employee, and is stored in Employee table.


Query to get data in one status not in another status

So if I understand correctly, you want the latest status and be able to filter on it. I don't know what version of SQL Server you're using but APPLY has been out since 2005 so try this out:

SELECT  DISTINCT TagNo,
        TruckNo,
        CA.VerifiedDateTime,
        CA.Status
FROM yourTable A
CROSS APPLY
(
    SELECT TOP 1 VerifiedDateTime,Status
    FROM yourTable
    WHERE TagNo = A.TagNo
    ORDER BY VerifiedDateTime DESC
) CA
WHERE CA.Status = 'A';


How to do a kind of selective grouping in a SQL result?

You will need some column value that differentiates the x3 values. For example you could group by both x1 and x3, then you will only lose rows where both x1 and x3 are the same.

select x1, sum(x2), x3 from foo where bar group by x1, x3.

This would look like:

_____________________
|    |         |    |
| x1 | sum(x2) | x3 |
|____|_________|____|
|    |         |    |
| 00 | 01      | 03 |
| 01 | 03      | 03 |
| 02 | 09      | 05 |
| 03 | 08      | 05 |
| 03 | 24      | 03 |
| 03 | 08      | 09 |
|____|_________|____|

For any other output you would need to use programmatic logic to loop through the SQL output, sum x2, and only present new info when x3 changes. You could do this in your application, or you might be able to write a function for it in PostGREs, but I'm pretty sure it is not supported by ANSI SQL.


PostgreSQL schemas, select same table from all schemas

This might be a good case for using table inheritance.  Create the initial table in the master schema and then make all user tables inherit it.

The key to making this work well is to see the master table as as abstract mixin rather than a typical public class inheritance.  The parent table then gives you, essentially, a catalog of all child tables' contents.   This becomes problematic if you also insert into the parent table so you may want to use a trigger to avoid that.


Can we change the default owner of mongodb to other than 'mongod' user?

Yes! In /usr/lib/systemd/system/mongod.service. Changing User parameter value to wanted user name. Remember to change Group too and then chown -R all files and directories to that new user.

[Unit]
Description=High-performance, schema-free document-oriented database
After=network.target
Documentation=https://docs.mongodb.org/manual

[Service]
User=mongod
Group=mongod
Environment="OPTIONS=--quiet -f /etc/mongod.conf"
ExecStart=/usr/bin/mongod $OPTIONS run
ExecStartPre=/usr/bin/mkdir -p /var/run/mongodb
ExecStartPre=/usr/bin/chown mongod:mongod /var/run/mongodb
ExecStartPre=/usr/bin/chmod 0755 /var/run/mongodb
PermissionsStartOnly=true
PIDFile=/var/run/mongodb/mongod.pid
# file size
LimitFSIZE=infinity
# cpu time
LimitCPU=infinity
# virtual memory size
LimitAS=infinity
# open files
LimitNOFILE=64000
# processes/threads
LimitNPROC=64000
# total threads (user+kernel)
TasksMax=infinity
TasksAccounting=false
# Recommended limits for for mongod as specified in
# http://docs.mongodb.org/manual/reference/ulimit/#recommended-settings

[Install]
WantedBy=multi-user.target


No. The numeric data types are all signed.

For example an int is from approx. -2^31 to +2^31.

If you have a value just shy of 2^32 you will have to use a larger data type such as bigint. There are finer points here, but out of scope of this discussion.

Related Q & A:

Why aren't unsigned integer types available in the top database platforms?

Documentation:

int, bigint, smallint, and tinyint (Transact-SQL)
Data Types (Transact-SQL)


Replace all column values with other values in the same column according to primary key

This expects one one value besides of unknown, if this si not the case you must choose another aggregation function

CREATE TABLE table1
    ([id] varchar(4), [value] int, [applicable] varchar(7))
;

INSERT INTO table1
    ([id], [value], [applicable])
VALUES
    ('id1', 5, 'Unknown'),
    ('id1', 5, 'Unknown'),
    ('id1', 6, 'Yes'),
    ('id 2', 5, 'Unknown'),
    ('id 2', 5, 'No'),
    ('id 2', 3, 'Unknown')
;
GO

UPDATE table1
SET    table1.[applicable]  = t1.[applicable]
FROM   table1
       INNER JOIN (SELECT [id],MIN([applicable]) as [applicable] FROM table1  WHERE  [applicable] <> 'Unknown' GROUP BY [id]) t1
         ON table1.id = t1.id

GO

SELECT * FROM    table1
GO

id   | value | applicable
:--- | ----: | :---------
id1  |     5 | Yes
id1  |     5 | Yes
id1  |     6 | Yes
id 2 |     5 | No
id 2 |     5 | No
id 2 |     3 | No

db<>fiddle here


Restore DB Backup with limited amount of disk space

SQL Server need to create the database with the size it had when the backup was produced. No way around that.

You can possibly have the backup file on a different machine and restore using an UNC path to that machine. Something like

RESTORE DATABASE myDB FROM DISK = '\\server\share\db.bak'

Above assumes that the service account for the SQL Server service has permissions on the backup share/file.

But, again, investigate what size each database file had when the backup was produced using RESTORE FILELISTONLY - that is the size the files will be created with.


Is it okay to define a foreign key referencing a ‘user_id’ column from every table?

For your first question, I've managed many systems that do this. Some actually defined that Foreign Key relationship. Others had the "foreign key" field, but didn't define it in the database as key. This led to interesting quirks like data loaded outside of the application (pre-populated rows) having "updated by" fields with values that weren't users in the user table. Typically the tables had "ChangedBy" columns that stored User IDs and "ChangedOn" fields that stored a date/time for the change event.

For your second, why wouldn't it be normalized, if you set up the Foreign Key relationship back to a users table? You aren't duplicating the user information at the menu, restaurant, etc. tables, right? Just the foreign key ID field.


I found the following answer from a post written by Paul Randal.

Is It Possible to Run Out of Log Sequence Numbers?

Paul writes:

  There’s no need to worry because for all practical purposes it isn’t
  possible to run out of log sequence numbers. As a bit of background, a
  log sequence number is a three-part number used to uniquely identify a
  transaction log record—constructed from the sequence number of the
  virtual log file (VLF) containing the log record, the log block number
  within the virtual log file, and the log record number within the log
  block.

  This isn’t really important, but what is important is that the VLF
  sequence number is a 64-bit number. Whenever a VLF is reused in the
  transaction log, the VLF sequence is increased by 1. So let’s do a
  little math.

  Imagine a transaction log with 65,536 VLFs, each 1/4MB in size (not a
  nonsensical situation, depending on how your transaction log is being
  managed—for more details on this and on transaction log internals, see
  “Importance of proper transaction log size
  management.".
  Each time the log is completely used and wraps around to the start,
  the VLF sequence number will increase by 65,536, which is 2 to the
  power of 16 (2^16).

  A 64-bit number can support 2^64 values. To be able to exhaust the
  2^64 possible VLF sequence numbers, our example transaction log would
  have to wrap 2^64 / 2^16 = 2^48 times. That’s a lot of log wrapping.
  But how much transaction log does that equate to?

  Our example log is 65,536 x 1/4MB in size, which is 16GB. To wrap that
  log 2^48 times, you’d need to generate 2^48 x 16GB of transaction log,
  which equates to four billion petabytes (a petabyte = 1024 terabytes)
  of transaction log—quite an undertaking!

  Even being able to write that log out to a solid-state drive (SSD)
  capable of a sustained 600MBps, it would take four billion petabytes /
  600 megabytes = approximately 240 million years to generate four
  billion petabytes of transaction log. As you can clearly see, no one’s
  in any danger of running out of log sequence numbers!


How to flatten results of a table with two related "many" tables?

This can be done using the PIVOT function, but since it sounds like you want to change the query based on the schemaId, then you will want to use dynamic SQL.

If you had a known number of values or knew the columns for a specific schemaID, then you could hard-coded the query. A static query would be:

select loannumber,
  [First Name],
  [Middle Name],
  [Last Name]
from
(
  select
    l.loannumber,
    sf.fieldname,
    lf.fieldvalue
  from loans l
  left join loanfields lf
    on l.id = lf.loanid
  left join schemafields sf
    on lf.schemafieldid = sf.id
    and l.schemaid = sf.schemaid
) src
pivot
(
  max(fieldvalue)
  for fieldname in ([First Name], [Middle Name], [Last Name])
)piv;

See SQL Fiddle with Demo.

If you had an unknown number or you want the columns to change based on a SchemaId that you are passing into a procedure, then you will use dynamic SQL to generate the SQL string:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @schemaId int = 1

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName)
                    from SchemaFields
                    where schemaid = @schemaid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT loannumber,' + @cols + '
            from
            (
              select
                l.loannumber,
                sf.fieldname,
                lf.fieldvalue
              from loans l
              left join loanfields lf
                on l.id = lf.loanid
              left join schemafields sf
                on lf.schemafieldid = sf.id
                and l.schemaid = sf.schemaid
              where sf.schemaid = '+cast(@schemaid as varchar(10))+'
            ) x
            pivot
            (
                max(fieldvalue)
                for fieldname in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. Both of these queries will generate the result:

| LOANNUMBER | FIRST NAME | LAST NAME | MIDDLE NAME |
-----------------------------------------------------
|     ABC123 |       John |       Doe |      (null) |
|     XYZ789 |    Charles |     Smith |         Lee |


Datetime fields return as integers in view

This has happened because the definition of the table has changed since the view was last modified. Maybe someone altered one or more columns, or the table was dropped and re-created with columns in a different order, different types, etc.

Temporary fix:

EXEC sys.sp_refreshview N'dbo.viewname';

Permanent fix:

Stop using SELECT * in views (and maybe even consider always using WITH SCHEMABINDING for all views).
I blogged about the SELECT * symptom here.
I blogged about the benefits of SCHEMABINDING here, and they include being unable to alter a table when a view is schema-bound to it.


Logical Replication to different schema name

This does not seem to be possible with built-in logical replication: https://www.postgresql.org/docs/current/logical-replication-subscription.html

  The tables are matched between the publisher and the subscriber using
  the fully qualified table name. Replication to differently-named
  tables on the subscriber is not supported.

Since it uses fully qualified names, a different schema would count as a different name, and is not supported.

pub/sub is just a user friendly wrapping around a more general facility, you might be able to create decoding plugins to give you finer control.  I doubt that doing so is for the faint of heart, though.


What characters are word breakers in English for SQL Server 2005 and 2008 R2?

This isn't an official list, but using a loop to work through a list of characters, and using sys.dm_fts_parser like so:

declare @i integer
declare @cnt integer
set @i=0
while @i<255
begin
  set @cnt=0
  select @cnt=COUNT(1) FROM sys.dm_fts_parser ('"word1'+REPLACE(CHAR(@i),'"','""')+'word2"', 1033, 0, 0)
  if @cnt>1
    begin
      print 'this char - '+CASE WHEN @i > 31 THEN char(@i) ELSE '' END+' - char('+convert(varchar(3),@i)+') is a word breaker'
    end
  set @i=@i+1
end

I can generate a list of characters that sys.dm_fts_parser reckons break the words. (sys.dm_fts_parser returns a row for every 'word' found in the import, so if it returns more than 1 row we had a word breaker)

This could be expanded to extended/non-english character sets by using nchar() rather than char() (and a bigger value for @i), and changing parameter 2 (lcid) in the call to sys.dm_fts_parser


Using PostgreSQL what is the difference between a smallint and a bool for storing boolean?

Always store boolean data as boolean. Only exotic exception imaginable.
Just to address the storage angle in addition to what you posted as answer:
boolean requires 1 byte on disk, smallint requires 2. But that's not the whole story.
smallint (like other integer types and unlike boolean) also has special needs for alignment padding. It can only start at an even offset from the start of the tuple data. So another byte is consumed every odd time (literally).
In a worst case scenario, when mixing with types that require 8-byte alignment like bigint or timestamp / timestamptz:
SELECT pg_column_size(row("char" 'a', FALSE   )) AS char_bool
     , pg_column_size(row("char" 'a', int2 '1')) AS char_int2
     , pg_column_size(row(text 'abcdef', TRUE    , now())) AS text7_bool_ts
     , pg_column_size(row(text 'abcdef', int2 '1', now())) AS text7_int2_ts;  -- worst case

 char_bool | char_int2 | text7_bool_ts | text7_int2_ts
-----------+-----------+---------------+---------------
        26 |        28 |            40 |            48

Details:

Calculating and saving space in PostgreSQL (updated)
Configuring PostgreSQL for read performance
What is the overhead for varchar(n)?

If you have many boolean NOT NULL values and want to optimize space on disk:

Should I use the PostgreSQL bit string?


How to Upgrade Redis Database from 5.x to 6.x

The upgrade is automatic on first run of Redis after upgrading the packages. Subsequent times that Redis starts it reports Loading RDB produced by version 6.0.3.


Oracle index on column with length > maximum varchar2 length

I think its a good suggestion done by @raj moving to 12C as 11g is nearing EOSL.
If you are considering using CLOB with Oracle Text Search, consider below.

Maintaining Indexes and Synchronization
In 11g users can specify at index creation the index update preference: manually,
on commit, or at regular intervals. Users can also specify a transnational text index,
where documents are searchable immediately after being inserted or updated. Note
that the catalog index type - designed specifically for the short pieces of text
typically found in eBusiness catalogs – is always transactional and needs no
synchronization.

Pleaese read: http://www.oracle.com/technetwork/testcontent/11goracletexttwp-133192.pdf (Page 12)


Secure postgres 9.5 database packed on Windows desktop application

It is not necessary to break your database to make it secure.

Just configure pg_hba.conf to make sure that nobody except the operating system user postgres can log in, and restrict login to that account.

There is no way to protect the database from an administrative user who can change operating system passwords and override file permissions. Don't even try.


how to retrieve rows of different dates using IN clause

If you are doing a range of consecutive dates, there is no need to use the IN operator.

If you use this to compute midnight seven days ago

DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND

here is what you get back:

mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND;
+--------------------------------------------------+
| DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND |
+--------------------------------------------------+
| 2012-11-21 00:00:00                              |
+--------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Your query will look like this:

SELECT COUNT(DISTINCT `user`) FROM `tracks`
WHERE `track_time` >= ( DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND );

You also need an index to support the WHERE and COUNT

ALTER TABLE tracks ADD INDEX track_time_user_ndx (track_time,user);

Then, the query should go faster.

If you are doing a range of consecutive dates from some earlier period, simply supply the first and last date from midnight to midnight. For example, for the range 2012-11-12 to 2012-11-19:

SELECT COUNT(DISTINCT `user`) FROM `tracks`
WHERE `track_time` >= '2012-11-12 00:00:00'
AND   `track_time` <  '2012-11-19 00:00:00';

If you are doing a range of non-consecutive dates, simply structure the query using UNION. For example, for dates 2012-11-12,2012-11-19,2012-11-26:

SELECT COUNT(DISTINCT `user`) FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-12 00:00:00'
    AND   `track_time` <= '2012-11-12 23:59:59'
    UNION
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-19 00:00:00'
    AND   `track_time` <= '2012-11-19 23:59:59'
    UNION
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-26 00:00:00'
    AND   `track_time` <= '2012-11-26 23:59:59'
) A;

Give it a Try !!!

UPDATE 2012-11-27 13:52

If you want count of distinct users for just one date 2012-11-12

SELECT COUNT(DISTINCT user) usercount FROM `tracks`
WHERE `track_time` >= '2012-11-12 00:00:00'
AND   `track_time` <= '2012-11-12 23:59:59';

If you want count of distinct users for 7 consecutive dates 2012-11-12-2012-11-18 (You cannot include 2012-11-19)

SELECT
    DATE(track_time) track_date,
    COUNT(DISTINCT user) usercount
FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-12 00:00:00'
    AND   `track_time` <  '2012-11-19 00:00:00'
) A GROUP BY DATE(track_time);

UPDATE 2012-11-27 14:41 EDT

If you want this dynamically done as of NOW(), try this:

SELECT
    DATE(track_time) track_date,
    COUNT(DISTINCT user) usercount
FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= ( DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND )
) A GROUP BY DATE(track_time);


when to use embedded PL/SQL gateway with Oracle APEX?

The options you note:

(1) Oracle HTTP Server with MOD_PLSQL
(2) Oracle APEX Listener
(3) Embedded PL/SQL Gateway

For each option, pro/con:

(1) Original solution. Full flexibility of an http server with rewrite rules, access rules, etc. However, loading additional modules for mixed application server use can be painful because of the custom Apache build. Also, licensing additional products from Oracle can be pricy.

(2) Latest solution. Most flexible, it can be used in any j2ee container (Tomcat, Glassfish, Weblogic, old OC4J, etc), but should be combined with a true http server with access rules, etc for security (Tomcat is typically combined with HTTPD for this purpose). This does have the problem in that currently this must be deployed for each connection configuration, while (1) is deployed for the whole server with the single configuration.

(3) This is typically not recommended for external deployment in any case. Included is the issue with direct access to the database server from external clients. This can be helped with HTTPD as a proxy server (like Tomcat above).

In existing installs, where you already have mod_plsql applications in place, put the APEX dad along side those and you should be set using solution (1). For new installs, I would go with (2). (3) is a viable option for initial setup and testing, but should be replaced prior to production deployment.


Prematurely exiting a while loop in T-SQL and viewing the output

Success will depend a lot on where the results are stored. If they're written to a normal table then you can simply query that table. If there's a transaction open which would block new queries use the NOLOCK hint. If you're using snapshot isolation I don't know how you can get at the open transaction's versioned data.

If all the results are in temporary tables you'll have a harder time. Those tables are scoped to the session so accessing them from a different session will be tricky. You may be able to look in TempDB's metadata to find the table's full name and query that explicitly. I don't know if that will work, I've never tried. (I tried; it doesn't work.)

If the code is running through SSMS you can stop execution without ending the session. This will leave the temp tables in tact and available to the session. Cancel using the menu Query -> Cancel Executing Query (mapped to the red square in the toolbar). If there's an open transaction that transaction will remain open. You will have to manually commit it or roll it back.

Cancellation is achieved by sending an "attention" message to SQL Server. Other clients may or may not support this. From a scripted environment (e.g. Powershell, Python), if the query was submitted asynchronously it may be possible to call the "Cancel" method (or equivalent) on the original connection or query object.

With sufficiently high access rights you could look at the on-disk structures. Paul White has written up a method here. Depending on how much has been written it may take as long to transcribe the dumps from DBCC PAGE as it would to re-run the query.

If results are in variables you could connect a debugger to the SQL Server process, pause its execution and go hunting. Good luck with that. If there are other users on the system, or background system activities, it may be difficult to distinguish your desired values from all the others present. Certainly, it is not documented or supported in any way. Do NOT try this on a shared production instance.

Finally, if you're using SSMS have a look in the messages tab. You never know, whoever wrote the script may have placed PRINT statements to track progress.

Ending the loop is easy. Find the SPID from the SSMS tab or DMVs (sys.dm_exec_sessions et al) then issue KILL session_id. Unfortunately it will end the script entirely, loosing local variables and temp tables, and disconnect the targeted session. Any open transaction will have to roll back, which will take roughly as long as the transaction has been open, give or take.


Maintaining permissions on a replicated view after reinitialization

I ended up using a post-initialization script to grant this permission at the subscriber and it worked great.


SELECT multiple values from one table having matching record in another table in one row

Actually, since

  there can be n number of values for a single record of table first.

the number of columns to return is not exactly arbitrary. There is a maximum of columns, and this has a clean solution - unless you have more columns than Postgres allows for a table:

  250 - 1600 depending on column types

BTW, typically, you would also have a third table listing all possible values of value, the whole of it implementing a classical many-to-many relationship.

How to implement a many-to-many relationship in PostgreSQL?

You can use CASE statements, or more elegantly, the crosstab() function of the additional module tablefunc. If you are unfamiliar with it, read basic instructions here first:

PostgreSQL Crosstab Query

Columns to the right of actual values are filled with NULL.
Assuming a maximum of 5 possible values and building on this setup:

CREATE TABLE first  (id int, name text);
CREATE TABLE second (id int, value "char", fid int);

INSERT INTO first  VALUES
  (1,'Ahmad')
, (2,'Sami')
, (3,'Khan')
, (4,'Nobody');  -- Added to demonstrate difference
INSERT INTO second VALUES
  (1,'a',1)
, (2,'b',1)
, (3,'c',2)
, (4,'d',1)
, (5,'e',2)
, (6,'f',3);

Either use crosstab(text) (1 parameter form) and join to table first another time:

SELECT id, f.name, value1, value2, value3, value4, value5
FROM   crosstab(
        'SELECT f.id, 1 AS dummy_category, s.value
         FROM   first  f
         JOIN   second s ON s.fid = f.id
         ORDER  BY f.id, s.value'
       ) ct (id int
           , value1 "char", value2 "char", value3 "char", value4 "char", value5 "char")
JOIN first f USING (id);

Or use crosstab(text, text) (2 parameter form) and generate dummy categories for your values:

SELECT *
FROM   crosstab(
        'SELECT f.id, f.name
              , row_number() OVER (PARTITION BY f.id ORDER BY s.value) AS dummy_category
              , s.value
         FROM   first  f
         JOIN   second s ON s.fid = f.id
         ORDER  BY f.id, s.value'
      , ('SELECT generate_series(1,5)')
       ) ct (id int, name text
           , value1 "char", value2 "char", value3 "char", value4 "char", value5 "char");

Result is the same either way:

 id | name  | value1 | value2 | value3 | value4 | value5
----+-------+--------+--------+--------+--------+--------
  1 | Ahmad | a      | b      | d      |        |
  2 | Sami  | c      | e      |        |        |
  3 | Khan  | f      |        |        |        |

If you want to include all rows from table first, make it a LEFT [OUTER] JOIN:

         ...
         LEFT JOIN second s ON s.fid = f.id
         ...

Then we get one additional result row for the above example:

  4 | Nobody |        |        |        |        |


Storing historical data in DB vs on disk

I think you've answered your own question.

If there's no need for foreign key consistency and you are only ever reading back this data then the difference in search times makes a compelling argument for storing the history data as plain text rather than in a database.


Create View using table and column names from data in a table, then build SQL

You can use a dynamic query.

create table my_table (tabname varchar(200), site_id int, logger_field varchar(200));

insert into my_table values
('[big_slough]', 2589, 'G0025890'),
('[big_slough]', 2590, 'G0025891');

create table [big_slough]
(
    TmStamp datetime,
    RecNum int,
    Batt_Volt decimal(18,2),
    G0025890 decimal(18,2),
    G0025891 decimal(18,2)
);

insert into [big_slough] values
('20170725 08:00:00', 0, 13.25, 32.52, 29.63229),
('20170725 09:00:00', 1, 13.48, 32.51, 29.61947),
('20170725 10:00:00', 2, 14.07, 32.50, 29.61757),
('20170725 11:00:00', 3, 14.14, 32.49, 29.61356),
('20170725 12:00:00', 4, 13.43, 32.49, 29.61185),
('20170725 13:00:00', 5, 13.34, 32.50, 29.60452);

GO

8 rows affected

create procedure sp_generate_view
(
@tabname varchar(200)
)
as
begin

    declare @cols nvarchar(max);
    declare @cmd nvarchar(max);

    set @cols = stuff((select ',' + quotename(logger_field)
                       from my_table
                       for xml path(''),
                       type).value('.', 'nvarchar(max)'),1,1,'');

     set @cmd = 'select TmStamp, site_id, value
                 from
                      (select TmStamp, ' + @cols + '
                       from   ' + @tabname + '
                       where TmStamp = (select max(TmStamp)
                                        from ' + @tabname + ')) p
                 unpivot
                 (value FOR site_id IN (' + @cols + ')) AS unpvt';

    exec (@cmd);

end
GO

✓

exec sp_generate_view @tabname = '[big_slough]';
GO

TmStamp             | site_id  | value
:------------------ | :------- | :----
25/07/2017 13:00:00 | G0025890 | 32.50
25/07/2017 13:00:00 | G0025891 | 29.60

dbfiddle here


The closest thing is a database snapshot. You take a snapshot of your database, that will act as the 'base image'. Then customers go nuts on the database and hack it to pieces. When all said and done, you revert the database back to the snapshot and voila, everything is back to its original state. See Revert a Database to a Database Snapshot.

You do have space savings between the snapshot and the DB, because the copy-on-write semantics of the snapshot. But unfortunately this only works on a single customer, you cannot have one snapshot serving as origin for multiple writable customers, which is what you ask. Snapshots can server something like a demo boot, where your restore it every morning.


Why are these two queries having such different executions?

LEFT is killing performance; remove it unless you have a good reason for keeping it.  To elaborate...

((but first... The purpose for the LEFT was missing from the original query; this answer assumes the LEFT was not necessary.))

LEFT JOIN says that you want data from the right table, whether or not there was a match with the left table.

JOIN says to display only the rows that match (via ON) both tables.

(OUTER is optional and adds no semantics.)

If you are running a version before 5.6, the derived table (subquery in LEFT JOIN) will have no index, so it must be scanned repeatedly.  This is a big reason to get rid of LEFT.

Without the LEFT, the Optimizer is likely to evaluate the subquery once, then efficiently JOIN to reporter to finish the query.

For JOIN, the inner query needs this composite (and covering) index INDEX(messagetypeenum, createdOn, reporterid).

There is another technique (I think)...  Get rid of the inner SELECT, simply JOIN (or LEFT JOIN) to the table:

select  r.*
    from  reporter r
    left join  nubamessage m  ON m.reporterid = r.id
      AND  m.messagetypeenum = 7
      and  m.createdOn >= '2016-06-18 00:00:00'
      and  m.createdOn <= '2016-06-18 09:30:00'
    WHERE  r.enabled = 1
      AND  m.reporterid is null;

In this case, it may needs INDEX(reporterid, messagetypeenum, createdOn).

Yet another variant would use EXISTS and, I think, provides the equivalent of the LEFT

select  r.*
    from  reporter r
    WHERE  r.enabled=1
      AND NOT EXISTS
    (
        SELECT  *
            from  nubamessage m
            where  m.messagetypeenum =7
              and  m.createdOn>='2016-06-18 00:00:00'
              and  m.createdOn<='2016-06-18 09:30:00'
              AND  m.reporterid=r.id
    )

I can't predict which variant will be fastest.  It partially depends on how "many" in the many:1 mapping of m.reporterid : r.id.


How do I force the delete of users from my database?

It seems the user that you are trying to drop has played a role of grantor and hence you need to revoke those accesses before performing drop of user.

Try running below command to get details of grantee first:

select
     permission_name,
     state_desc,
     object_name(major_id) as securable,
     user_name(grantor_principal_id) as grantor
from sys.database_permissions
where grantee_principal_id = user_id('User Name to be dropped')

Next will be to find details on grantor using below:

select *
from sys.database_permissions
where grantor_principal_id = user_id ('User Name to be dropped');

Basis result what you get from above query, you need to perform one of below:

REVOKE VIEW DEFINITION ON USER::User Name to be dropped TO public
REVOKE CONTROL ON USER::User Name to be dropped TO public
REVOKE ALTER ON USER::User Name to be dropped TO public

REVOKE ALTER ON USER::User Name to be dropped TO *grantee*
REVOKE CONTROL ON USER::User Name to be dropped TO *grantee*
REVOKE VIEW DEFINITION ON USER::User Name to be dropped TO *grantee*

This is just a possibility, there could be some other type of access which has been granted. so, you need to check the same and execute revoke accordingly.

You may find more of these examples from below forum:

https://ask.sqlservercentral.com/questions/24721/the-database-principal-has-granted-or-denied-permi.html

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/07b40291-6c4d-427e-a5e2-568df482c550/drop-user-fails-with-quotthe-database-principal-has-granted-or-denied-permissions-to-catalog?forum=sqlsecurity

https://stackoverflow.com/questions/41817820/how-to-solve-sql-server-error-15284-the-database-principal-has-granted

Hope above helps.


How to capture workload from production to replay on test?

You can record the production workload with SQL Server Profiler and replay it against the test environment. There might be a bottleneck because the Profiler only replays from one computer. If you need more load from several computers (and have SQL Server 2012) you could use Distributed Replay (as long as you don't run into the same bugs with TVPs in traces as I've run into).

Note: the distributed replay requirement for SQL 2012 is for the replay client, you can use a 2008 trace file and target a 2008 instance if you want. See Distributed replay requirements


There's nothing to fix, really.

The message refers to this computed column in the Sales.SalesOrderHeader table:

[SalesOrderNumber]  AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')),

The warning is informational in this case, to be sure you're aware in case it was a problem, as discussed in this Q&A: Type conversion in expression may affect “CardinalityEstimate” - on a computed column?


Creatin Products Database with Multiple Color Variations

Firstly I would advise strongly against having a column for each colour - This will cause very slow queries and will scale poorly (There is also a data size limit on the ammount of data in a single row in Microsoft SQL Server so this could cause you problems if your number of colours grows).

To represent the many-to-many relationship you have described I would use an intermediary table like this:

CREATE TABLE [Product]
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Name NVARCHAR(250)
)

CREATE TABLE [Colour]
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Colour NVARCHAR(250)
)

CREATE TABLE [Product_Colour_Availability] -- Many to Many relationship table
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Product_ID INT
,Colour_ID INT
,Available bit -- 1=available, 0=not available
)

--
-- Insert some data for a single product (T-Shirt) that will be both red and blue.

INSERT INTO [Product] (Name)
VALUES ('T-Shirt')

INSERT INTO [Colour] (Colour)
VALUES ('Red'), ('Blue')

INSERT INTO [Product_Colour_Availability] ([Product_ID], [Colour_ID])
VALUES (1,1,1), (1,2,1)

-- Get availability info about a specific product:

SELECT P.[ID] AS 'Product ID'
,P.[Name] AS 'Product Name'
,C.[Colour] AS 'Product Colour'
,PCA.[Available] AS 'Availability'
FROM [Product_Colour_Availability] PCA
LEFT JOIN [Product] P ON PCA.Product_ID=P.[ID]
LEFT JOIN [Colour] C ON PCA.Colour_ID=C.[ID]
WHERE P.[ID] = 1


Azure SQL Row Level Security Full Index Scan

We have implemented SQL Row-level ...[which] allows our application to
  be clueless to the fact that only 1 tenant's data is being returned
  based on the logged in user.  ... However, we've come across some
  performance issues

You will continue to run into performance issues with multi-tenant databases.  RLS is really neither here nor there.  If you added your own tenant filters in your application code, you would have the same issues.

To manage a multi-tenant database schema is always a challenge.  First you change every table to support tenant-based filtering.  This typically means including the TenantId in the clustered index of every table, either as the leading column, or as a traling column with a partition scheme.  Then you have to manage query plans, which can't differ between tenants in a multi-tenant database.  Then you're stuck upgrading all the tenants at the same time, which, critically, reduces your ability to quickly patch issues affecting a single tenant.  Eventually you'll have to build processes to Export and Import a single tenant. You can work through this stuff, and many companies do.  But it's fundamentally a challenging model, and one that only really makes sense when reducing your database footprint actually reduces your costs somehow.  IE not in Azure SQL Database.

So you really might be better off to "scrap RLS completely and move to an Azure SQL elastic pool and horizontal sharding based on customer id".

Now the benefits of database-per-tenant are many, and performance is just the start. The best thing is that greatly improves your data security story.  It allows you to upgrade or patch on a tenant-by-tenant basis.  It gives you tenant-level backup, restore, oops-recovery, export.  It provides tenant-level security and reporting. It gives you horizontal scalability, and the option of provisioning dedicated resources for a tenant. etc.

And while it may appear that it's more complicated to manage, and does require some additional automation, in the long run it's actually easier too.