Query: select all rooms which are not booked

Let's look at some example data:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE booking
    (`id` int, `user` varchar(2), `begin` datetime, `end` datetime)
;

INSERT INTO booking
    (`id`, `user`, `begin`, `end`)
VALUES
    (1, 'u1', '2013-02-03 00:00:00', '2013-02-04 00:00:00'),
    (2, 'u2', '2013-02-03 00:00:00', '2013-02-06 00:00:00'),
    (3, 'u3', '2013-02-05 00:00:00', '2013-02-07 00:00:00'),
    (4, 'u4', '2013-02-10 00:00:00', '2013-02-11 00:00:00')
;

CREATE TABLE room
    (`id` int, `name` varchar(2))
;

INSERT INTO room
    (`id`, `name`)
VALUES
    (1, 'r1'),
    (2, 'r2'),
    (3, 'r3'),
    (4, 'r4'),
    (5, 'r5'),
    (6, 'r6')
;

CREATE TABLE booking_map
    (`id` int, `room_id` int)
;

INSERT INTO booking_map
    (`id`, `room_id`)
VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 1)
;

First we need to find all bookings for the day:

Query 1:

SELECT *
  FROM booking
 WHERE begin <= '2013-02-04'
   AND end >=   '2013-02-04'

[Results][2]:

| ID | USER |                           BEGIN |                             END |
|----|------|---------------------------------|---------------------------------|
|  1 |   u1 | February, 03 2013 00:00:00+0000 | February, 04 2013 00:00:00+0000 |
|  2 |   u2 | February, 03 2013 00:00:00+0000 | February, 06 2013 00:00:00+0000 |

Or really, we need a list of all booked rooms for the day. For that we just join booking to booking_map:

Query 2:

SELECT m.room_id
  FROM booking AS b
  JOIN booking_map AS m
    ON b.id = m.id
 WHERE b.begin <= '2013-02-04'
   AND b.end >=   '2013-02-04'

[Results][3]:

| ROOM_ID |
|---------|
|       1 |
|       2 |

Now, with that information we can just return all rooms that are not in the list of booked rooms:

Query 3:

SELECT *
  FROM room AS r
 WHERE r.id NOT IN(
    SELECT m.room_id
      FROM booking AS b
      JOIN booking_map AS m
        ON b.id = m.id
     WHERE b.begin <= '2013-02-04'
       AND b.end >=   '2013-02-04'
   )

[Results][4]:

| ID | NAME |
|----|------|
|  3 |   r3 |
|  4 |   r4 |
|  5 |   r5 |
|  6 |   r6 |


Fileshare witness for Multisite AvailabilityGroup setup

Best case - third independent site and/or online somewhere that you can reach it from on premises. Otherwise, you need to put it in one of the sites (and it also needs to be highly available). QUorum is a model. You're configuring a witness. General rule of thumb: The core cluster group and the witness should always be in your primary site since that's where you care more.

You may potentially need to adjust votes on the nodes, and both sides should be able to see that FSW. If you need to fail over to Site B, you'll need a new FSW.


Design star schema for relatonal database

You could put a many-to-many relationship within a data warehouse, but many people consider it bad practice to do so - even so far as some data warehousing tools do not permit it to be created at all. Here is how I would create a star-schema from your design:

As your Author table and Category table only have one valuable attribute (the name) I would roll them into the Book table which would then become your first dimension. The Customer table can stay as-is and become a dimension as well. You would then roll the two Order tables into one and create a Order fact table which consists of OrderID, Date, BookID, CustomerID, Price - like so:

CREATE TABLE DimBook
(
    BookID      INT          NOT NULL PRIMARY KEY,
    Author      VARCHAR(50)  NOT NULL,
    Category    VARCHAR(50)  NOT NULL,
    Title       VARCHAR(50)  NOT NULL,
    ISBN        VARCHAR(50)  NOT NULL,
    Year        SMALLINT     NOT NULL,
    Price       DECIMAL(9,2) NOT NULL,
    NoPages     SMALLINT     NOT NULL,
    Description VARCHAR(100) NOT NULL
);

CREATE TABLE DimCustomer
(
    CustomerID INT         NOT NULL PRIMARY KEY,
    FirstName  VARCHAR(50) NOT NULL,
    LastName   VARCHAR(50) NOT NULL,
    ZipCode    VARCHAR(20) NOT NULL,
    City       VARCHAR(50) NOT NULL,
    State      VARCHAR(50) NOT NULL
);

CREATE TABLE FactOrders
(
    OrderID    INT          NOT NULL,
    "Date"     DATETIME     NOT NULL,
    BookID     INT          NOT NULL REFERENCES DimBook(BookID),
    CustomerID INT          NOT NULL REFERENCES DimCustomer(CustomerID),
    Price      DECIMAL(9,2) NOT NULL
);

You may also want to consider a Date dimension which is also commonly found in star-schemas and data warehouses to make searching by dates easier. A very basic implementation is below:

CREATE TABLE DimDate
(
    "Date"  DATETIME NOT NULL PRIMARY KEY,
    "Year"  SMALLINT NOT NULL,
    "Month" TINYINT  NOT NULL,
    "Day"   TINYINT  NOT NULL
);

Then, just add a foreign key from your Date attribute in the fact table to the Date key in the DimDate table. This would produce something like:

If you need to handle scenarios where a book can have many authors (which frequently happens), there are a couple of ways to do so.

The first, and my recommendation, is to have all of the authors within the Author attribute. This would allow you to easily search for all books written by the same combination of authors.

The second approach denormalises the Author attribute into its own dimension which is then referenced by the book dimension. This would create a snowflake schema (your question stated you wanted a star schema so I avoided this approach) and would also be slower when trying to search by multiple authors.

Ultimately, it depends on your exact needs and the requirements you are trying to meet. I would personally stick with having all authors in the same attribute as this is the easiest design and meets your star schema requirement.


In a search, I would like to get all the rows that exactly match the
bit string.

Use a B-Tree index, the default type. I don't see a case for a GIN index here.
Up to 1000 bits result in up to 133 bytes (or slightly more) storage size on disk for a bit varying type.
SELECT pg_column_size(repeat('1', 1000)::varbit)  -- 133

Not that much. A plain B-Tree index should do. But maybe the column is big enough that the following tricks improve performance.
If a small part of the bitstring column is distinctive enough to narrow your search down to few hits, an index on an expression might give you better performance, because the smaller index can fit into RAM and is faster to process all around. Don't bother for small tables, the overhead would eat the benefit. But could make a big difference for big tables.
Example
Given table:
CREATE TABLE tbl(id serial PRIMARY KEY, b_col varbit);

If the first 10 bit are enough to narrow down a search to a few hits, you could create an index on the expression b_col::bit(10). Casting to bin(n) truncates the bitstring to n bit.
CREATE INDEX tbl_b_col10_idx ON tbl ((b_col::bit(10)))

Extra parentheses are required for the cast operator in an index definition. See:

How to create an index on an integer json property in postgres

Then, instead of the query
SELECT * FROM tbl WHERE b_col = '1111011110111101'::varbit; -- 16 bit

You would use:
SELECT *
FROM   tbl
WHERE  b_col::bit(10) = '1111011110111101'::bit(10) -- utilize index
AND    b_col = '1111011110111101'::varbit;  -- filter to exact match

Be aware that shorter values are padded with 0's to the right (least significant bits) when cast to bit(n).
In a real world application this starts to make sense with several 100s of bits. Test for the turning point.
Optimize further
Since most installations operate with a MAXALIGN of 8 bytes (64 bit OS) (more details here), your index size is the same for any data not exceeding 8 bytes. Effectively, per row:

 4 bytes item identifier
 8 bytes for the index tuple header (or 23 + 1 byte for heap tuples)
 ? actual space for data
 ? padding to the nearest multiple of 8 bytes

Plus some minor overhead per page and index / table. Details in the manual or in this related answer on SO.
Therefore, you should be able to further optimize the above approach. Take the first 64 bit (or last or whatever is most distinctive and works for you), cast it to bigint and build an index on this expression.
CREATE INDEX tbl_b_col64_idx ON tbl ((b_col::bit(64)::bigint))

I cast twice (b_col::bit(64)::bigint) for there is no cast defined between varbit and bigint. Details in this related answer on SO:

Convert hex in text representation to decimal number

Effectively, this is just a very fast and simple hash function, where the hash value also allows to look up ranges of values. Depending on exact requirements you could go one step further and use any IMMUTABLE hash function - like md5(). Details in the answer linked above.
The query to go along with that:
SELECT *
FROM   tbl
WHERE  b_col::bit(64)::bigint = '1111011110111101'::bit(64)::bigint -- utilize index
AND    b_col = '1111011110111101'::varbit;  -- narrow down to exact match

The resulting index should be just as big as the one in the first example, but queries should be considerably faster for three reasons:

The index typically returns much fewer hits (64 bit of information vs. 10 bit)

Postgres can work with integer arithmetic, which should be faster, even for a plain = operation. (Didn't test to verify that.)

The type integer has no overhead like varbit - 5 or 8 bytes. (In my installation 5 bytes for up to 960 bit, 8 bytes for more).
Effectively, to keep the index at its minimum size, you can only pack 24 bit into a varbit index - compared to 64 bit of information for a bigint index.

CLUSTER
In such a case CLUSTER should improve performance:
CLUSTER TABLE tbl USING tbl_b_col10_idx;

It's a one-time operation and has to be repeated at intervals of your design. Be sure to read the manual on CLUSTER if you want to use that. Or consider the alternative pg_repack. Details:

Configuring PostgreSQL for read performance

If the first 64 bit of your values are unique most of the time, CLUSTER will barely help, since the index scan will return a single row in most cases. If not, CLUSTER will help a lot. Consequently, the effect will be far greater for the first example with the less optimized index.


What happen if i start a Postgresql replication using tar

Assuming the replication data directory is starting out empty (which it should be, or you are probably doing something wrong), these commands are basically equivalent.  Both read all the data and do something with it.

The tar is going to use a bit more CPU on the server, because it will be running gzip, which will probably max out one CPU as long as there is one free.  It will also consume a bit more IO capacity, because it will be writing the .tgz file to disk rather than streaming directly over the network (but you could change the tar command to stream over ssh instead)

Neither of these should make much of a difference, unless you are already on the brink.

Considering your comment about how much slower rsync is:  I find rsync somewhat slower, but not drastically so.  I wonder if you have dysfunctional implementation of it.  I've heard (vaguely) of versions of rsync which interacted with versions of the Linux kernel in a way that cause context swapping storms.  Anyway, if tar is so much faster, than it will naturally be imposing a higher IO load on the server as it gets all the data read in less time, this might interfere with server operation while it occurs.  On the other hand, whatever rsync is doing that makes it slow might also be consuming resources the database would like to use.

Unfortunately when you got to issues of implementations of one tools interacting with kernel versions, etc. you have an issue that can really only addressed experimentally.

If you are concerned about correctness, both rsync (when used like you show and on an empty target directory) and tar should preserve your data integrity, it is just a matter of which one performs better, and causes the least performance impact on the production server by competing with it for resources.  I prefer tar, because rsync invites people to try to do clever things which place their data at risk.


Contradiction in book related to variable scope in Stored Procedures

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating how variable scope often works):

int OuterVariable = 5;

function int Sub1 (int Param1)
{
  int InnerVariable = 10;

  return InnerVariable + OuterVariable + Param1;
}

int Result = Sub1(3);

Since variables declared in an outer scope are often visible to inner scopes (i.e. global), the code above wouldn't error when calling Sub1 stating that OuterVariable hasn't been declared. Instead, the subroutine would have access to the value of OuterVariable (and in some languages, it might even be able to overwrite that value). Hence, the value of Result at the end would be 18.

So, when the book states:

  "Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by Proc1"

they are saying that T-SQL does not work like that. They are not saying that variables in Proc1 cannot be passed into a sub-Proc call; they are saying that if you want those values in the sub-Proc call, then you must pass them in explicitly via input parameters. Essentially, "visible" means "available without explicitly passing in via input parameters".

Maybe this will help illustrate:

CREATE PROCEDURE dbo.InnerProc(@InnerInputParam INT, @InnerName NVARCHAR(30))
AS
SELECT @InnerInputParam AS [InnerParam], @InnerName AS [InnerName];

-- SELECT @OuterName; -- commented out as this is a parse error
GO

CREATE PROCEDURE dbo.OuterProc(@OuterInputParam INT)
AS
DECLARE @OuterName NVARCHAR(30) = N'Mr. Outer Scope';

EXEC dbo.InnerProc
        @InnerInputParam = @OuterInputParam,
        @InnerName = @OuterName;
GO

Executing EXEC dbo.OuterProc 5; or EXEC dbo.OuterProc @OuterInputParam = 5; (same thing) will return:

InnerParam     InnerName
5              Mr. Outer Scope

Those values are "known" by dbo.InnerProc because they were passed in. And they were passed in because they were not "visible" to dbo.InnerProc, even though they existed in the outer / parent scope of dbo.OuterProc during execution. But neither @OuterInputParam nor @OuterName can be referenced in dbo.InnerProc because inner / child scopes have no knowledge of outer / parent scopes, at least not for variables (including Table Variables) and parameters.

Finally, to relate all of this back to the initial example in the book, this is why I do my best to change variable names when using sp_executesql: to make the code more readable. Changing one variable name might make it quite a bit clearer:

SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = @InnerAddress';

EXECUTE sp_executesql
         @statement = @SQLString,
         @params = N'@address NVARCHAR(60)',
         @InnerAddress = @address;


Using Galera for simple master-master replication

1) Is a two node configuration safe\supported?

Galera will still run in a two node setup. However, there is always the threat of a split-brain scenario. For example, suppose you have DB1 and DB2 form a two node Galera Cluster. If DB1 goes down, you need to failover to DB2. While DB1 is having maintenance done to bring it back up, DB2 gets all your changes. By the time DB1 is back up, doing an IST is virtually impossible (or at least too late at this point). You would have to perform a full SST from DB2. Easiest way to do this is to delete the Galera cache files before starting up DB1. During the SST, DB2 is in a read-only state (as a donor) and cannot process any inserts, updates, or deletes. When there is a third node in the Cluster, at least one server can collect inserts, updates, and deletes.

You will also find
Percona XtraDB Cluster: Failure Scenarios with only 2 nodes more informative.

  2) In a 3 node setup, if the third node dies it now becomes a two node setup. Is this now unsafe meaning I should really have a 4 node setup?

A two node Cluster is still operational. When introducing the third node for the first time or bringing back the third node from being down, one of the two operating nodes enters a read-only state (as a donor) to help the third node play catchup. Only one

As I said in the first question: When there is a third node in the Cluster, at least one server can collect inserts, updates, and deletes.

  3) What exactly is a Galera "arbiter"?

I think you mean Arbitrator (See the subheading Using an arbitrator). It is a mechanism that helps decide who plays the role of donor and who play the role of the cluster when introducing node back into a Cluster. It helps with mitigating split-brain scenarios More can be found in the Galera Documentation.


InnoDB "holes" in large table 200 Million Rows?

InnoDB stores data (and indexes) in 16KB blocks.

It sounds like you are shrinking each row by an average of 7% (300/4K).

If the shrinkage had been more than 50%, then probably InnoDB would have noticed adjacent half-full blocks and coalesced them, thereby freeing up some of the predicted savings.  (A block is 16KB.)  I say only "some" because it is not aggressive at packing the blocks after UPDATEs or DELETEs.

If you had not done the OPTIMIZE, future inserts/deletes/updates, if they were scattered around the table, would have filled in the 'holes' and/or led to blocking coalescing.  If, on the other hand, the main activity is to add new rows at the 'end' of the PRIMARY KEY values, then this subsequent cleanup would not happen, and the OPTIMIZE would be desirable.

How much did the OPTIMIZE benefit you?  Performance probably changed very little.  Sure, you freed up some space, but, if you are not running out of disk space, that does not matter much.

Another issue...  Tablespaces.  The old default was to put all tables into a single tablespace (the file named ibdata1).  It would grow but never shrink.  Any freed blocks would be available for future database work, but would not be given back to the OS.

The new default (innodb_file_per_table) has the same problem.  However, an explicit OPTIMIZE rebuilds the table, and does give the freed space back to the OS.  Caveat:  During the process, you need 2x the disk space.   So, if you were very tight on space, OPTIMIZE would fail.  However...

OPTIMIZE TABLE (for InnoDB) is "Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with FULLTEXT indexes. The operation uses the INPLACE algorithm, but ALGORITHM and LOCK syntax is not permitted. " -- https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html


Why do I get ERROR 1824 (HY000) while importing MySQL dump from 5.7 to 8.0.x?

This error happens when you have a classic “chicken and egg” problem. The most common situations for this would be:

populating a self-referencing table where a lower ID requires the existence of a higher ID⇢ a good example of this would be an Employee table where record 33 had a manager with an ID of 999. 33 could not be recorded until 999 existed in the table.
populating a table that depends on another table that has not yet been created⇢ this can happen when you have an Employee table that has references to User, but User isn’t created until after Employee.

The error you received was:
… Failed to open the referenced table 'table-name'

This points to the second item as being the most likely culprit.
The simplest way to resolve this would be to remove the foreign key checks when importing the data, then restore the checks after the import is complete. You can do this be editing your 12GB .sql file to have this at the very start:
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;

Then, at the very end of the import file:
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;

Note: With the foreign key check disabled, there is a possibility of recording bad data. If this is something you need to avoid for regulatory purposes, do not follow this “quick” merhod.


Update badly formed XML to allow parsing

You can get this information on SQL Server without adding a root element:

create table tbl (foo xml);

insert into tbl values ('<PROG>PROGX_</PROG><STNTYPE>LEAKTEST</STNTYPE><STNID>S1</STNID><TRCODE>SN_REQUEST</TRCODE><OPERATORID>222</OPERATORID><PARTID>PARTID</PARTID><TRSTATUS>0000</TRSTATUS><TRTEXT>TEST OK</TRTEXT><T1_USL>0.45</T1_USL><T1_LSL>-0.45</T1_LSL><T1_RESULT>05,CC/M</T1_RESULT><T1_STATUS>OK</T1_STATUS><OVERALL_RESULT>1</OVERALL_RESULT><OVERALL_STATUS>OK</OVERALL_STATUS>
');

select
    foo.value('(/PROG)[1]', 'nvarchar(100)') PROG,
    foo.value('(/STNTYPE)[1]', 'nvarchar(100)') STNTYPE,
    foo.value('(/STNID)[1]', 'nvarchar(100)') STNID
from   tbl;
GO

PROG   | STNTYPE  | STNID
:----- | :------- | :----
PROGX_ | LEAKTEST | S1

db<>fiddle here


Not knowing your data, the view definition would be:

SELECT
    [SalesID],
    [Delivery Date],
    ROW_NUMBER() OVER (PARTITION BY [SalesID] ORDER BY [Delivery Date]) AS DateNo
FROM
    [WhatEverTable]

http://technet.microsoft.com/en-us/library/ms186734(v=sql.105).aspx

ROW_NUMBER() by itself will return a running count of the rows, but when you add the OVER(PARTITION BY [SalesID] ORDER BY [Delivery Date]) it gives a running count per sales id, ordered by the date.

Does that make sense?

EDIT: If you do make this a view, just be aware that the results will be filtered by your WHERE clause. Using your data above, if you queried for sales that occurred after 2014-03-20, SalesID SO00000020 will only have a single entry for 2014-03-21 with a DateNo of 1.

If you need the data to persist regardless of the WHERE clause, you will likely just have to scrap the idea of a view and get creative with the query that accesses the data.


Does cross-database permission chaining work for objects owned by sa/dbo?

You need to create a user for bob in dbRestricted.  You do not need to grant bob access to the table in dbRestricted.

This minimally complete verifiable example shows this in action:

USE master;
CREATE DATABASE db_a;
ALTER DATABASE db_a SET DB_CHAINING ON;
CREATE DATABASE db_b;
ALTER DATABASE db_b SET DB_CHAINING ON;
CREATE LOGIN db_a_login WITH PASSWORD = 'aasdfasdfasdf78723%';
GO

USE db_a;
CREATE TABLE dbo.t
(
    someval varchar(10) NOT NULL
);
INSERT INTO dbo.t (someval) VALUES ('db_a');

CREATE USER db_a_login FOR LOGIN db_a_login;

USE db_b;
CREATE TABLE dbo.t
(
    someval varchar(10) NOT NULL
);
INSERT INTO dbo.t (someval) VALUES ('db_b');
CREATE USER db_a_login FOR LOGIN db_a_login;

USE db_a;
GO
CREATE VIEW dbo.both_t
AS
SELECT *
FROM db_a.dbo.t
UNION ALL
SELECT *
FROM db_b.dbo.t;
GO
GRANT SELECT ON dbo.both_t TO db_a_login;
GRANT SELECT ON dbo.t TO db_a_login;
GO

EXECUTE AS LOGIN = 'db_a_login';
SELECT *
FROM dbo.both_t;
REVERT

╔═════════╗
║ someval ║
╠═════════╣
║ db_a    ║
║ db_b    ║
╚═════════╝

If we remove cross-database-ownership-chaining, we see this fails:

ALTER DATABASE db_a SET DB_CHAINING OFF;
ALTER DATABASE db_b SET DB_CHAINING OFF;

EXECUTE AS LOGIN = 'db_a_login';
SELECT *
FROM dbo.both_t;
REVERT

  Msg 229, Level 14, State 5, Line 50
  The SELECT permission was denied on the object 't', database 'db_b', schema 'dbo'.

Use this to clean up afterwards:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'db_a')
DROP DATABASE db_a;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'db_b')
DROP DATABASE db_b;
IF EXISTS (SELECT 1 FROM sys.server_principals sp WHERE sp.name = 'db_a_login')
DROP LOGIN db_a_login;
GO


MAX(UPPER(range)) can't be accelerated with a GiST index on the range?

Any index can only be used for WHERE conditions of the form

<indexed expression> <operator> <constant>

Here <operator> must be of an operator class supported by the index, and <constant> must be constant for the duration of the index scan.

Other than that, indexes can speed up the following (where the first two are only possible with B-tree indexes):

max(<indexed expression>) and min(<indexed expression>)
ORDER BY <indexed expression>
functions returning boolean in the WHERE condition, if they have a support function.


Turn Emails into Usernames With MySQL Query

I'd probably suggest doing it in the code that will be creating the insert statement but it can be done in the insert statement itself too.

INSERT INTO yourtable SET username = LOWER(SUBSTR(useremail,1,LOCATE('@',useremail) - 1))

I included the LOWER so the style of the user name would be consistent in case a person entered their email address in MixedCase.


Select N rows before/after specified row

WITH
cte1 AS ( SELECT created_at, 10 N
          FROM posts WHERE id=5869 ), /* parameters */
cte2 AS ( SELECT posts.created_at,
                 LAG(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () lbound,
                 LEAD(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () ubound
          FROM posts, cte1 )
SELECT posts.*
FROM posts, cte1, cte2
WHERE posts.created_at BETWEEN cte2.lbound AND cte2.ubound
  AND cte2.created_at = cte1.created_at
ORDER BY posts.id

sample fiddle (N set to 3, id set to 10).

PS. I understand that window in LAG/LEAD must be specified like ORDER BY posts.created_at ASC ROWS BETWEEN [UNBOUNDED | cte1.N] PRECEIDING AND [UNBOUNDED | cte1.N] FOLLOWING. May be added if needed really.


You can use a UNION of two subqueries:

SELECT
    user_id,
    MAX(ts)  AS last_timestamp,
    SUM(cnt) AS number_of_messages
FROM
  ( SELECT  to_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE from_user_id = 1
    GROUP BY to_user_id
  UNION ALL
    SELECT  from_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE to_user_id = 1
    GROUP BY from_user_id
  ) AS tmp
GROUP BY
    user_id ;

Two indexes, on (from_user_id, to_user_id, timestamp) and (to_user_id, from_user_id, timestamp) would help efficiency of the subqueries.


Configuring a non-nullable foreign key uniqueidentifier with a default Guid.Empty value that exists in foreign table

To add a default is easy:

CREATE TABLE dbo.t
( tid INT PRIMARY KEY,
  ggid UNIQUEIDENTIFIER NOT NULL
      DEFAULT '00000000-0000-0000-0000-000000000000'
 ) ;

Then the following insert will work:

INSERT INTO dbo.t
  (tid)
VALUES
  (1), (2) ;

Test at rextester.

If you want NULLs to not raise errors but be converted to the default, I think the easiest way is by using an INSTEAD insert trigger on the table. Something like this:

CREATE TRIGGER t_replace_null_guid_with_default
ON dbo.t
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.t (tid, ggid)
    SELECT tid, COALESCE(ggid, '00000000-0000-0000-0000-000000000000')
    FROM inserted;
END ;


You have almost 40000 rows where word='Facebook*'. I suppose that these rows are somewhat randomly scattered on the whole table. This means that Postgres has to read tens of thousands of random blocks from a disk, which might be slow.

You can try to cluster rows that have the same word together. You can use the command cluster misspelled using test_scanid;. This would take some time and block the table until done, so plan accordingly.


Table 'performance_schema.threads' doesn't exist. SQL Error: 1146)

mysql_upgrade — Check and Upgrade MySQL Tables


MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?

Batch statements are only ever executed serially in the order they appear in the batch.

Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside).

Take for example the following code:

CREATE TABLE #t
(
    i int
);

INSERT INTO #t (i) VALUES (0);

The CREATE TABLE always runs prior to the INSERT INTO statement.  Consider this:

SELECT 1;

SELECT 2;

The above code will always be ran in order, i.e. SELECT 1 runs first, then after it completes, SELECT 2 runs.

There are multiple ways independent batches can be ran simultaneously, including the use of Multiple Active Result Sets, or MARS, however none of those affect the serial processing of statements within a single batch.


Foreign key reference to column that is no primary key

First a note about hierarchies. The technique you are currently trying to use for modules is called adjacency list. I would encourage you to look-up few other, more appropriate, SQL techniques like: path enumeration, nested sets, closure table.

However, something seems to be wrong with this sentence:

  But the parent module always has the same values as the child modules in all 4 categories, so these columns would be redundant.

This sounds unusual at best, a hierarchy may look like:

1        region/
1.1      region/school/
1.1.1    region/school/class/
1.1.1.1  region/school/class/subject/

I will go out on a limb and claim that the problem here is not about foreign keys, but about implementing a hierarchy in SQL.

To simplify the problem, I would suggest to experiment with path enumeration for your project and explore other techniques if required.

-- Module titled TITLE is categorized as CAT_PATH.
--
module {TITLE, CAT_PATH}
    PK {TITLE}

Where CAT_PATH is one of:

region/
region/school/
region/school/class/
region/school/class/subject/

This will likely result in a different set of problems and questions, but those are going to be easier to solve.


Implications of changing SQL Server collation

I posted a detailed analysis of the implications of making any collation changes to an instance and/or database:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are quite a few areas that could be affected, and it depends greatly on if you are talking just about one particular database, or the instance and one or more of the databases on that instance.

HOWEVER, perhaps in addition to asking what might the affect of such a change be, you should probably also be asking the vendor:

Why they are making this recommendation? If they do not have a very specific reason for it, then maybe you shouldn't do it.
What is the specific recommendation: is it to make the instance case-sensitive, or just this particular database?
Is the vendor installing objects into an existing database, or do they create their own? If they create their own database, they can ensure that it is case-sensitive by setting the collation when the database is created. And if they are installing into an existing database, they can simply use the COLLATE clause in the CREATE TABLE statements to override the default from the database; they can use the COLLATE clause in any expression to override the database's default collation.
Why are they using obsolete SQL Server collations instead of the newer, more appropriate Windows collations? For anyone using SQL Server 2008 or newer , Latin1_General_100_CS_AS_SC would be the preferred equivalent of SQL_Latin1_General_CP1_CS_AS.


ORA-01652: Extend a tablespace in Oracle 12c, no DBA_DATA_FILES table

EDIT/ !!! Now i see you wrote this:

  Mainly because select * from DBA_DATA_FILES; shows me an error when
  executing, because the table doesn't exist in the database.

In my opinion you just don't have enought rights. You should login as sysdba.

Use this select first, you should see location of datafiles (Change the "EXAMPLE" at the end of this command):

select file_name, AUTOEXTENSIBLE, round(BYTES/1024/1024,1), round(MAXBYTES/1024/1024,1), INCREMENT_BY, INCREMENT_BY/1024  from dba_data_files where tablespace_name='EXAMPLE';

Then add datafile to Tablespace, for example:

alter tablespace EXAMPLE add datafile '/oracle/data/EXAMPLE02.dbf' size 100m autoextend on next 100m maxsize unlimited;


Is dbo the default schema when querying if it is not specified?

According to the Books Online:

  The value of DEFAULT_SCHEMA is ignored if the user is a member of the
  sysadmin fixed server role. All members of the sysadmin fixed server
  role have a default schema of dbo.

So it seems the account used where the default schema is not working is a sysadmin role member. The default schema of the dbo user is always dbo and cannot be changed.

The best practice is generally is to schema-qualify object references to avoid ambiguity and improve performance.


You can find the solution here : Errors in the OLAP storage engine: A duplicate attribute key has been found when processing


You can stop automatic seeding by setting seeding mode for that secondary replica to "manual."  This will stop seeding for all databases.
See this docs article for the details: Use automatic seeding to initialize an Always On availability group - Stop automatic seeding

To stop automatic seeding for an availability group, run the following script on the primary replica:
ALTER AVAILABILITY GROUP [<availability_group_name>]
MODIFY REPLICA ON '<secondary_node>'
WITH (SEEDING_MODE = MANUAL)

Once seeding has been stopped, double check that the "problem" database has been removed from the AG (and is removed from the secondary), and then restart seeding:

You can set automatic seeding on an existing database. The following command changes an availability group to use automatic seeding. Run the following command on the primary replica.
ALTER AVAILABILITY GROUP [<availability_group_name>]
MODIFY REPLICA ON '<secondary_node>'
WITH (SEEDING_MODE = AUTOMATIC)

From your question, I'm not sure if you deleted the problematic database from the primary server, or if you just removed it from the AG.


MySQL BEFORE INSERT trigger with IF conditions

As @dezso says, you can't compare NULL with arithmetic comparison operators as described in the MySQL's documentation.

  You cannot use arithmetic comparison operators such as =, <, or <> to
  test for NULL. To demonstrate this for yourself, try the following
  query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Edit this part:

IF NumberHP IS null then # <-- IS replaced =
    insert into smsra_sms
    set idsms = IDx, SendingDateTime = WaktuKirim,
    isi = IsiSMS, pengirim = NumberHP
    ON duplicate key update idsms = idsms + IDx;
ELSE
    insert into smsra_trash
    set Nohp = NumberHP
    ON duplicate key update Nohp = Nohp + NumberHP;
END IF;


PostgreSQL not SELECTing by RETURNING value of INSERT

The basic problem is that the insert (that happens in the CTE) in not visible (via the my_table snapshot) in the main query at that time.
So you could finish first the CTE and then select for example from a temporary table

CREATE tABLE my_table(id serIAL,some_row
 INT, some_other_row varchar(199))

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)
SELECT new_row.id INTO films_recent  FROM new_row;
SELECT
   my_table.*
FROM my_table
WHERE my_table.id = (SELECT id FROM films_recent)

1 rows affected

id | some_row | some_other_row
-: | -------: | :-------------
 1 |        0 | whatever

db<>fiddle here

Or use new_row in the main query, which makes the insert changes visible!:

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING *
)
SELECT
   new_row.*
FROM new_row ;

It's like a schrödinger cat, the new row can be visible or not ;)


Mongo assigning user permsions issue on docker

okay, I make it work like this

#!/bin/bash
set -e;

mongo -u mongo -p asdfasdf --authenticationDatabase admin <<-EOJS
    use notifications;
    db.createUser({
      user: "mongouser",
      pwd: "asdfasdf",
      roles: [  {role: "readWrite", db: "notifications" }  ]
    });
    use translations;
    db.createUser({
      user: "mongouser",
      pwd: "asdfasdf",
      roles: [  {role: "readWrite", db: "translations" }  ]
    });

    exit;
    EOJS

but why I need to create separate users, why I can't assign the same usr to multiple databases?


Substring without the first n characters

Your SUBSTRING solution seems good enough, I am not sure why you would need to ask for anything more. I would only like to note that if you want to skip the first 18 characters, then you should specify 19 as the second argument for SUBSTRING, because in SQL the character positions in a string start from 1. So this makes perfect sense and should work well for you:

SUBSTRING(Serial, 19, 38)

If you specify 18, then you will be skipping 17 characters.

The solution using RIGHT and LEN, suggested by George.Palacios will work as well but the results of these two solutions might not be the same depending on whether Serial can have trailing spaces. The reason for the possible difference is that the LEN function ignores trailing spaces, while RIGHT does not, which means that for a string with trailing spaces the result will most likely not be what you would expect.

Let me demonstrate that using a simple example with a shorter string. Suppose the maximum length is 10, the number of characters to skip is 4, and the specific string is 'abcde  '. (The quotation marks here are just string delimiters, for you to be able to see the trailing spaces.) If you do

SUBSTRING('abcde  ', 5, 10)

you will get 'e  ' as the result, because SUBSTRING will simply start cutting the string at the 5th character and include all the characters up to the end of the string.

In contrast, the RIGHT/LEN option

RIGHT('abcde  ', LEN('abcde  ') - 4)

will yield ' '. The LEN function will ignore the two trailing spaces and return 5. Subtracting 4 from 5 gives you 1, thus RIGHT will return just one rightmost character of the string, which is a space.

As I said, though, if Serial can never have trailing spaces, either option will do. For completeness, let me suggest one more, which uses the STUFF function:

STUFF(Serial, 1, 18, '')

The STUFF function allows you to delete a substring from and/or insert another one to a given string at a given position. So, in order to skip 18 characters using this function, you specify starting position 1, 18 characters to delete, and an empty string ('') to replace them with. Like SUBSTRING, this solution will correctly work with trailing spaces, should you have the need to take them into account.


How to get service broker database queue status?

PoSh just wraps SMO. ServiceQueue exposes IsEnqueueEnabled for queue status, IsActivationEnabled for activation status,
IsRetentionEnabled for retention status and finally IsPoisonMessageHandlingEnabled for, well, poison message handling status.

Also PoSh has the habit of hiding properties. See how to see all properties.


Bruce Momjian has an update of his old but useful presentation here:

http://momjian.us/main/writings/pgsql/internalpics.pdf

and there's Tom Lane's classic "tour of PostgreSQL internals" as mentioned by Neil, and the back-end flowchart that illustrates the high level overview of the execution path.

Then take a look at the further resources here:

http://wiki.postgresql.org/wiki/Developer_and_Contributor_Resources
http://www.postgresql.org/developer/

After reading the useful links from Neil and those mentioned above, your next port of call is:

https://github.com/postgres/postgres

i.e. the best reference is the source code. The PostgreSQL sources have some pretty detailed comments on data structures and functions, but you'll still land up wanting to attach a debugger and step through the code to see what it's doing sometimes. Use the tools provided, like the pprint function, to examine the state of the server.


Receiving the following error in step failure of an agent job:

Annoying isn't it. I've done some research and it turns out it is incorrectly reporting a ANSI warning as an error (resolved in SQL Server 2012, no patch available for previous versions).

One way to stop the error is to go through all your code, and wherever you have an aggregate function (e.g. SUM), use ISNULL to ensure the aggregated value can never be NULL:

i.e. change

SUM(Field1+Field2)

to

SUM(ISNULL(Field1,0)+ISNULL(Field2,0))

This makes the error go away but might be impractical.


My issue got resolved using the information in URL below:

https://vladster.net/en/instructions/install-mysql-mac/

Steps taken:

Changed to MySQL directory:
cd /usr/local/mysql
Since my initial root password was expired and was not able to connect to root, so ran the installation again:
sudo bin/mysql_secure_installation
After providing machine password it automatically asked for new root password.

Above steps resolved my issue.


How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on?

Also "reducing fragmentation" is not per se a performance goal.  On many (most?) modern storage platforms there is little difference between sequential and random IO, which is a major historical reason for defragmenting.

I've worked on systems where the difference in throughput between sequential and random IO was 10x or more.  As SQL Server attempts to scan a fragmented index, the physical file locations of the linked-list of pages jumps around every few extents, reducing the IO size, randomizing the IO, preventing read-ahead IO, and eliminating the benefit of speculative reads into the SAN controller cache.

On modern systems, the storage either has lots of spindles, or is flash-based.  Both of these reduce the performance differential between small, random IOs and large, sequential IOs.

Also if tables are heavily cached, the benefit of defragmenting their storage on disk diminishes.  And small rowstore tables will typically be cached.


How to remove the WITH ENCRYPTION from the code of the procedure - via T-SQL

As noted in Sebastian Meine's answer to the question How to view an encrypted view or stored procedure, there is a T-SQL solution to perform the decryption. The code to do so can be found on Sebastian's blog.

The solution requires connecting with the DAC, so you have very few options to do so from a T-SQL stored procedure. One possible way is using a linked server:

DECLARE @srv nvarchar(4000);
SET @srv = 'ADMIN:' + @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
    @server     = N'DAC_LOOPBACK',
    @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
    @provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
    @datasrc    = @srv;

-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'DAC_LOOPBACK',
    @useself     = N'True',
    @locallogin  = NULL,
    @rmtuser     = NULL,
    @rmtpassword = NULL;

EXEC master.dbo.sp_serveroption
    @server=N'DAC_LOOPBACK',
    @optname=N'rpc',
    @optvalue=N'true'
EXEC master.dbo.sp_serveroption
    @server=N'DAC_LOOPBACK',
    @optname=N'rpc out',
    @optvalue=N'true'
GO

Once you have the loopback linked server using the DAC, you can execute Sebastian's stored procedure to gather the encrypted text. Unfortunately, the procedure returns an XML column (which cannot be returned over a linked server), so you will have to change the last SELECT to return nvarchar(max):

SET @cmd = N'SELECT CAST(@plain AS NVARCHAR(MAX)) AS [object_definition for ' +
    REPLACE(@object_name, ']', ']]') + ']';

Now you can call the procedure using the linked server:

EXEC DAC_LOOPBACK.SomeDatabase.dbo.ObjectEncryptionCracker
   'SomeEncryptedProcedure'

Do I recommend this method? Hell, NO!
The proper way to keep track of changes in your stored procedures is source control, as you already have been told in your original question. Don't rely on this code for backing up your code (it's unsupported and uses several hacks). Do yourself a favor and put your database objects under source control.


I have found it whit a hint from @JeffersonB.Elias. I've execute this code:

ALTER AUTHORIZATION ON DATABASE::FreeTimeDBSSA TO sa;
GO


How to troubleshoot large transaction log file size?

It's probable that you had a long running query that caused a bunch of transactions. Something like a large batch of inserts/deletes/updates. You can track these down in the query store if you have a recent version of SQL Server, or you can query the plan cache for long running transactions.

Once you've found the query, you can figure out what it's doing and go from there. You may just be stuck with it, but finding out what's causing the large amount of transactions is your first step.

Also, you should be aware that an autogrowth of 5MB is extremely small, and can result in a lot of Virtual Log Files when it grows, which can hinder startup performance. I suggest looking into tuning the growth rate as well.

https://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/


sp_Blitz - undocumented DBCC events related to the replication marked as a finding

Brent has just replied on Github it is going to be excluded from the alerts.


Let's start by dissecting the original query, based on the provided example data:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Stocks
    (`id` int, `ST_StockCode` int, `SM_StockCode` int, `ST_ItemSize` int)
;

INSERT INTO Stocks
    (`id`, `ST_StockCode`, `SM_StockCode`, `ST_ItemSize`)
VALUES
    (1, 679783, 678649, 7.00),
    (2, 679789, 688622, 7.00),
    (3, 679792, 679793, 8.00),
    (4, 679792, 686376, 8.00),
    (5, 679793, 679792, 7.00),
    (6, 679793, 686376, 8.00),
    (7, 679795, 679796, 8.00),
    (8, 679796, 679795, 7.00),
    (9, 679797, 617114, 7.00),
    (10, 679797, 627339, 7.00)
;

Query 1:

SELECT
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

Results:

| ST_STOCKCODE | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|--------------|-------------|
|       679792 |       679792 |           7 |
|       679793 |       679793 |           8 |
|       679795 |       679795 |           7 |
|       679796 |       679796 |           8 |

First thing to note is that, this being an inner join, the two values  ST_StockCode and SM_StockCode are always identical. In the following examples I therefore mention only one of them as it is easy to add the missing one back in.

Second, this query is using a non SQL Standard extension to the GROUP BY clause. The query groups by ST_StockCode and then references two other columns without an aggregate. MySQL will return for those columns the first value it comes across. It does not check if the values are distinct and you might get different results each execution. If we assume that the original query writer was aware of this behavior we can do this instead:

Query 2:

SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 GROUP BY SM_StockCode;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       617114 |           7 |
|       627339 |           7 |
|       678649 |           7 |
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |
|       686376 |           8 |
|       688622 |           7 |

This returns for each SM_StockCode the minimum ST_ItemSize (instead of a random one). However, it returns potentially more rows then the original query because there might be SM_StockCodes that do not have a matching ST_StockCode (as in the provided example data). However, that is easily fixed:

Query 3:

SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 WHERE EXISTS(SELECT 1 FROM stocks AS t2 WHERE t2.ST_StockCode = t1.SM_StockCode)
 GROUP BY SM_StockCode;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |

Now only SM_StockCodes that have a matching ST_StockCodes are returned. (You have to duplicate the SM_StockCode column if you really need that value twice.)

With the query written this way, the indexing strategy becomes fairly obvious:

Create an index on ST_StockCode to support the EXISTS lookup and create a second index on SM_StockCode, ST_ItemSize to support the GROUP BY.

If you are sure that every SM_StockCode has a matching ST_StockCode (for example because a foreign key has been declared between the two, or because two of the analysts swear that this is always the case) you can shorten the query even more:

Query 4:

SELECT DISTINCT SM_StockCode, ST_ItemSize
  FROM stocks;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       678649 |           7 |
|       688622 |           7 |
|       679793 |           8 |
|       686376 |           8 |
|       679792 |           7 |
|       679796 |           8 |
|       679795 |           7 |
|       617114 |           7 |
|       627339 |           7 |

However, as in the example data excerpt of 10 random rows this condition is not given, the above result does not match in this case.


Postgres installation on MacOSX - problem with psql

Postgres.app has a short documentation here:
http://postgresapp.com/documentation/
which tells among other things:

Configure your $PATH
Postgres.app includes many command line tools. If you want to use
them, you must configure the $PATH variable.
If you are using bash (default shell on OS X), add the following line
to ~/.bash_profile:
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.4/bin
...

Not doing that results in the error message you're getting.


Driver properties for db2jcc in Squirrell and MON_GET_CONNECTION table function

It appears to be a bug in the way Squirrel handles additional parameters. Data Studio acts as expected with both db2jcc and db2jcc4 drivers. I have filed a bug to:

https://sourceforge.net/p/squirrel-sql/bugs/1201/


Update two tables, each on separate MySQL servers, on two different machines in one query?

If you update one server only, master/slave replication is sufficient, otherwise group replication or galera


Windows Azure Table Row Key strategy, what makes a useful RowKey?

It's just a requirement of TableStorage that a unique row identifier has to exist. I would probably make the RowKey just a timestamp like DateTime.UtcNow.Ticks.ToString() because you can then sort the records by time. Or you could always use a Guid.NewGuid().ToString() if you're worried about conflicts.


InnoDB buffer. Should I increase buffer size?

Short answer:  Set innodb_buffer_pool_size to 70% of available RAM (assuming you have more than 4GB of RAM).

Long answer:  For deeper investigation, we would need SHOW VARIABLES, SHOW GLOBAL STATUS and the RAM size.  And the buffer being "full" does not mean that things are running poorly -- it is a cache.


Mongodump don't understand Timestamp-function, so...

mongodump $credentials -d local -c oplog.rs --query '{"ts":{"$timestamp":{"t":1591199252,"i":0}}}' --out /test/


PostgreSQL Generate create Foreign keys from information_schema or pg_constraint

I think you are looking for pg_constraint_def, like in the following example:
SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conname = 'child_accountid_itemid_fkey';


You can create duplicates of steps 1 and 2 as steps 3 and 4, and set the following flow:

Success - go to step two, failure - exit(f).
Success - exit(s), failure - go to step three
Success - go to step four, failure - exit(f).
Success - exit(s), failure - exit(f).

But if you can instead wrap steps 1 and 2 in a stored procedure or ad hoc query that handles the retry logic without duplication, then your solution will be much more understandable and maintenance-safe.


Is there a way to order a PostgreSQL table by its primary key automatically?

By default, simple SELECT * FROM %table_name% will not order results by primary key.

But you can obtain list of columns of primary key of table:

SELECT ind_column.attname AS columns_of_pk
FROM pg_class tbl
  INNER JOIN pg_index ind ON ind.indrelid = tbl.oid
  INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid
  INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid
WHERE tbl.relname = 'my_table'
  AND ind.indisprimary;

And then build dynamic query with this columns in ORDER BY clause.

Add INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace and WHERE condition on sch.nspname if you have multiple tables with the same name in different schemas.

You can obtain PK's for all tables in 1 query:

SELECT sch.nspname AS "schema"
  , tbl.relname AS "table"
  , array_agg(ind_column.attname) AS columns_of_pk
FROM pg_class tbl
  INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
  INNER JOIN pg_index ind ON ind.indrelid = tbl.oid
  INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid
  INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid
WHERE sch.nspname <> 'pg_toast'
  AND ind.indisprimary
GROUP BY "schema", "table";


How to lock a sql login after N unsuccessful login attempts

Yes, you can but you have to set everything up.

SQL Server (SQL) Login

When you enable the Enforce password policy option in the SQL Server Login window, you are basically telling the SQL Server to adhere either to the local security policy or to the policy defined in the domain.

  The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN (Transact-SQL) to configure the password policy options of a SQL Server login. The following rules apply to the configuration of password policy enforcement:

  When CHECK_POLICY is changed to ON, the following behaviors occur:

  CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
  The password history is initialized with the value of the current password hash.
  Account lockout duration, account lockout threshold, and reset account lockout counter after are also enabled.

and also:

  The security policy might be set in Windows, or might be received from the domain. To view the password policy on the computer, use the Local Security Policy MMC snap-in (secpol.msc).

Reference: Password Policy (MSDN / SQL Server 2012)

Setting Policy (locally)

So if you require an account to be locked out after 3 wrong tries, then you either have to define a local policy with secpol.msc or define a domain policy for account lockout.

Example in secpol.msc

SQL Server will then use this policy if the Enforce password policy option is checked.

A technical overview of the account lockout policy can be found here:

Reference: Account Lockout Policy Technical Overview (MSDN)
Reference: Account lockout threshold (MSDN)

Locked out SQL Login

Here is what happens after a SQL Login has been locked out after the set amount of incorrect logins (15 in my case as domain policy). You can see the Login is locked out is set. This can be unset to unblock the account.


DB2 window function - running total of X for preceding N minutes worth of rows


Different MySQL index sizes after restore

Random insert into or delete from a secondary index can cause the index fragmentation. Basically it will use extra space than required. There are several ways to reclaim this space. One of the way to reclaim space is, taking mysqldump and restoring it. This helps to reclaim the space.

As far as the restoration completed without any error this should be fine and you don't need to worry.

For more information on fragmentation you can refer to below link.
https://dev.mysql.com/doc/refman/5.6/en/innodb-file-defragmenting.html
https://dev.mysql.com/doc/refman/5.6/en/myisam-optimization.html


Arguments can be made either way depending on whether you want to handle populating these tables using triggers or the application(s).

I think this depends on what exactly you want to store in these logging tables, if you're logging anything specific to your 3 "data" tables then you'd be better off with 3 logging tables (1 logging table per "data" table which will be easier to populate using triggers), otherwise you can store everything in 1 table (with 1 field to reference each data table) which will make reporting easier.

As regards referential integrity, if you're actually deleting the original data record (rather than just marking it as deleted) then your Foreign Key will have nothing to link to anyway.

This is one of these situations where I de-normalize to gain centralized reporting and application logic (though triggers could be used for this, depending what you're storing).


Looks like I figured it out.
Update Items
Set Monitored ='1'
Where Items.Id in (Select Items.Id from Items Left Join ItemsLoc on ItemsLoc.ItemID = Items.Id where ItemsLoc.Loc is null)


Parenthesis will probably help:

col BETWEEN (now() - interval 30 day) AND now()

now() - interval 30 day means "30 days ago" - the whole expression is "between 30 days ago and now". Actually, unless you have values higher than current datetime, you can simply use:

col >= NOW() - interval 30 day

This page is a good explanation of the INTERVAL syntax. MySQL documentation about time and date functions DATE_ADD() and DATE_SUB() explains the syntax as well: Date and time functions.

now() - interval 30 day is equivalent to date_sub(now(), interval 30 day), so you could also write the condition like this:

col BETWEEN date_sub(now(), interval 30 day) AND now()

Note that NOW() - interval 30 day means 30 days ago at this exact time, because NOW() returns a DATETIME value.


Export structure and data without a wizard

I know that Red Gate SQL compare has a command line interface so you could write and schedule a batch file to generate the scripts. Details here
You could then use SQLcmd to execute the scripts.


I can't find any evidence that 8.3 autovacuum is buggy. Autovacuum has been improved in 8.4 with free space maps which obsoletes some parameters which:

  (when set incorrectly) could make vacuum less effective

so with 8.3 it is important to set them correctly.
The postgres wiki says (and I'm inclined to agree):

  As of 8.3, autovacuum is turned on by default, and you should keep it that way.

and:

  The answer to almost all vacuuming problems is to vacuum more often,
  not less, so that each individual vacuum operation has less to clean up.

As you say, "adding entries to pg_autovacuum does the trick" when autovacuum needs to be tweaked - but just be aware that "pg_autovacuum [is] not saved in database dumps"


The fastest way to get the most recent records

Self-joins seem cheap at low row counts, but I/O is exponential as the row count increases. I would prefer to solve this the CTE way, unless you are on SQL Server 2000 (please always specify the version you need to support, using a version-specific tag):

;WITH cte AS
(
  SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo,
    rn = ROW_NUMBER() OVER (PARTITION BY Id
      ORDER BY EffectiveDate DESC, SequenceId DESC)
  FROM dbo.TestTable
)
SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo
  FROM cte
  WHERE rn = 1
  ORDER BY Id; -- because you can't rely on sorting without ORDER BY

This still has to scan, but it only has to scan once, compared to all the self-join variants, which will always have two scans (or potentially a scan and a seek executed multiple times, with better indexes).

If you want a more efficient query (eliminating an expensive sort, at the potential cost of writes, and perhaps other queries that don't need to support this sorting), change the primary key to match the query pattern:

PRIMARY KEY CLUSTERED
(
  [Id] ASC,
  [EffectiveDate] DESC,
  [SequenceId] DESC
)

The direction of the secondary columns has no effect on uniqueness, and should change writes minimally as long as the table isn't terribly wide and there aren't an extremely large number of rows per id.


Used Memory not freed up after a SQL BULK Insert / BCP Export

It is normal behaviour for SQL Server to allocate as much memory as it can into it's buffer pool. Databases work best with lots of buffer. If you want to change the behaviour, you can set the 'max server memory' setting. Some good background reading on that is here.


trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

BOL says:

syntax:

nodes (XQuery) as Table(Column)

Here is simple example:

DECLARE @x xml ;
SET @x='<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>moe</name></row>
    <row id="3" />
</Root>';

SELECT T.c.query('.') AS result
FROM   @x.nodes('/Root/row') T(c);
GO

It is a special notician to "convert" or shred xml data type into relational data. It just maps xml parts into the table columns. T - table, c - column, nodes() - method

value (XQuery, SQLType)

So T.c.value('title[1]','varchar(50)') reads value of element title and converts it to varchar(50) data type. [1] is added at the end of the path expression in the value() method to explicitly indicate that the path expression returns a singleton (just it confuses me, it means the first element in group in XPath).

So T.c.value('(title/@lang)[1]','varchar(20)') reads value of attribute lang at element title and converts it into varchar(20) data type.

And @xml.nodes('/bookstore/book') is located the point to start reading xml, in this case it returns all book elements (nodes) from this xml.

This query has 2 aliases T1(Locations) and T2(steps)

ADDED

SELECT
ProductModelID
, Locations.value('./@LocationID','int') as LocID
, steps.query('.') as Step
FROM Production.ProductModel

CROSS APPLY Instructions.nodes('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/MI:root/MI:Location')
as T1(Locations)

CROSS APPLY T1.Locations.nodes('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
./MI:step ')
as T2(steps)
GO


How do data checksums interact with streaming replication?

If the data was corrupted while on disk, the corruption should not get replicated unless it was already present in the backup from which the replica was created.

Of course if you ignore checksum failures then the master may start going wrong things, and that wrongness will be replicated.

If the corruption is occurring in memory then all bets are off.


Error: "The certificate cannot be dropped because one or more entities are either signed or encrypted using it."

To find items associated with Certificates and Asymmetric Keys, first try the query posted in this DBA.SE Answer:

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

If that does not return any objects, next try the following queries which look for:

Logins
Users
Service Broker Endpoints
Database Mirroring Endpoints
Symmetric Keys
Database Encryption Keys (used for TDE)

Please note that Logins are Server/Instance-level while everything else is Database-level. Also, Database Encryption Keys, while being at the Database-level, are reported in a DMV that returns data for all databases, and so does not change based on the "current" Database.

-- Server / Instance Logins (results not sensitive to local / current Database)
;WITH certs_n_keys AS
(
  SELECT 'Certifcate' AS [Type], crts.name, crts.certificate_id AS [cert_or_asymkey_id],
         crts.principal_id, crts.pvt_key_encryption_type_desc, crts.[sid],
         crts.thumbprint
  FROM   [master].sys.certificates crts
  UNION ALL
  SELECT 'Asymmetric Key' AS [Type], asym.name, asym.asymmetric_key_id AS
         [cert_or_asymkey_id], asym.principal_id, asym.pvt_key_encryption_type_desc,
         asym.[sid], asym.thumbprint
  FROM   [master].sys.asymmetric_keys asym
)
SELECT cnk.*, '---' AS [---],
       sp.[name] AS [PrincipalName], sp.principal_id, sp.type_desc,
       sp.create_date, sp.modify_date
FROM   certs_n_keys cnk
INNER JOIN sys.server_principals sp
        ON sp.[sid] = cnk.[sid];

-- Database Users
;WITH certs_n_keys AS
(
  SELECT 'Certifcate' AS [Type], crts.name, crts.certificate_id AS [cert_or_asymkey_id],
         crts.principal_id, crts.pvt_key_encryption_type_desc, crts.[sid],
         crts.thumbprint
  FROM   sys.certificates crts
  UNION ALL
  SELECT 'Asymmetric Key' AS [Type], asym.name, asym.asymmetric_key_id AS
         [cert_or_asymkey_id], asym.principal_id, asym.pvt_key_encryption_type_desc,
         asym.[sid], asym.thumbprint
  FROM   sys.asymmetric_keys asym
)
SELECT cnk.*, '---' AS [---],
       dp.[name] AS [PrincipalName], dp.principal_id, dp.type_desc,
       dp.create_date, dp.modify_date
FROM   certs_n_keys cnk
INNER JOIN sys.database_principals dp
        ON dp.[sid] = cnk.[sid];

-- Service Broker Endpoints
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       endpts.*
FROM   sys.certificates crts
INNER JOIN sys.service_broker_endpoints endpts
        ON endpts.certificate_id = crts.certificate_id;

-- Database Mirroring Endpoints
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       endpts.*
FROM   sys.certificates crts
INNER JOIN sys.database_mirroring_endpoints endpts
        ON endpts.certificate_id = crts.certificate_id;

-- Symmetric Keys (scroll results to the right to see Key name)
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       ncrptns.*, '---' AS [---], symkys.*
FROM   sys.certificates crts
INNER JOIN sys.key_encryptions ncrptns
        ON ncrptns.[thumbprint] = crts.[thumbprint]
INNER JOIN sys.symmetric_keys symkys
        ON symkys.[symmetric_key_id] = ncrptns.[key_id];

-- Database Encryption Keys (for TDE; results not sensitive to local / current Database)
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       DB_NAME(dbkeys.[database_id]) AS [DatabaseName], dbkeys.*
FROM   [master].sys.certificates crts
INNER JOIN sys.dm_database_encryption_keys dbkeys
        ON dbkeys.[encryptor_thumbprint] = crts.[thumbprint];


Analyse what caused a pagesplit afterwards?

I must say first that using fn_dblog and fn_dump_dblog is unsupported as these commands are not supported by Microsoft. Jonathan Kehayias in his article even pointed out that using fn_dump_dblog multiple times has grave performance implication so use these undocumented commands on your own risk. There is also a way to track page splits, not perfectly though but will help, using DMV Sys.dm_db_index_operational_stats the columns leaf_allocation_count in output points to page split which occurred for index and nonleaf_allocation_count gives you page allocation due to page splits read the documentation please. You can include object(table) for which you want to track page split by editing the query

Below is the code which may tell you page splits for index and heap does not have page splits they have forwarding pointers

select
ios.index_id,
o.name as Object_Name,
i.name as Index_Name,
ios.leaf_allocation_count as Page_split_for_index,
ios.nonleaf_allocation_count Page_allocation_caused_by_pagesplit
from sys.dm_db_index_operational_stats(DB_ID(N'db_name'),Null,Null,Null) ios
join
sys.indexes i
on
ios.index_id=i.index_id
join
sys.objects o
on
ios.object_id=o.object_id
where o.type_desc='user_table'

Other method is by using Extended events, below link by Jonathan has steps( applicable to SQL 2012 or later)

http://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/

Ofcourse you can read below article by Paul Randal where he showed how to track page split using fn_dblog command

http://www.sqlskills.com/blogs/paul/tracking-page-splits-using-the-transaction-log/


Geting rows from self-referencing single table using Postgres

Thank's too @dezso comments i found an answer how to do it!

WITH RECURSIVE temp(procid, input_proc_id, capture) AS (
  SELECT procid, input_proc_id, capture
  FROM table_A
  WHERE procid = 1
  UNION ALL
    SELECT table_A.procid, table_A.input_proc_id, table_A.capture
  FROM temp, table_A
  WHERE  temp.input_proc_id = table_A.procid
)

SELECT * FROM temp;

Which for given "procid" gives correct output, but it works on assumption that input_proc_id is null when "capture" column is "YES" otherwise it is not going to work.


I usually prefer to start with lower shared buffers on both Linux and Windows, and keep work mem low also.  Maintenance_work_mem can be left generally the default too.  Keep in mind that too low a maintenance_work_mem may slow down db restores but it won't stop it.  Generally you are better off starting low and then tweaking upward to find sweet spots than starting too high and working your way down.

Secondly you are reserving half of the memory on your box for PostgreSQL and only PostgreSQL.  This can have very unfortunate side effects if you are doing anything else.  Since this is Windows XP I am assuming it is not a dedicated server, and you are going to do far better with conservative settings here.


Counting non duplicates between 2 columns

Should be fairly simple:

SELECT COUNT(DISTINCT a.Start)
FROM TableName a
LEFT OUTER JOIN TableName b ON a.Start = b.End
WHERE b.Start IS NULL


I had the same issue. I ran an SQL trace on a remote server and transferred
the trace files to a local directory on my workstation so that I load the
data into a table on my local SQL Server instance for running queries
against.

SELECT * INTO ProfileTracesTable
FROM ::fn_trace_gettable('C:\Users\anthony\Documents\SQL_traces\first.trc', default)

However, each time I tried, I got the following error:

  File 'C:\Users\anthony\Documents\SQL_traces\first.trc' either does not
  exist or is not a recognizable trace file. Or there was an error opening
  the file.

At first I thought the error might be related permission but I ruled this
out since I had no problem loading the .trc files directly into SQL Profiler
or as a file into SSMS.

After trying a few other ideas, I thought about it a bit more and realised
that it was due to permissions after all: the query was being run by the SQL
Server process (sqlsrvr.exe) as the user NT AUTHORITY\NETWORK SERVICE –
not my own Windows account.

The solution was to grant Read and Execute permissions to NETWORK
SERVICE on the directory that the trace files were stored in and the trace
files themselves.

You can do this by right-clicking on the directory, go to the Security
tab, add NETWORK SERVICE as a user and then select Read & Execute for
its Permissions (this should automatically also select Read and
List folder contents). These file permissions (ACLs) should automatically
propagate to the directory contents (if not, open the Properties of the
folder and navigate to the Security → Advanced → Permissions tab and Enable
Inheritance).

If you prefer to use the command line, you can grant the necessary permissions to
the directory – and its contents – by running the following:

icacls C:\Users\anthony\Documents\SQL_traces /t /grant "Network Service:(RX)"


is it possible to using index in unix datetime colum in MySQL 5.7 when using function

using generate column and created a index on it:
ALTER TABLE report_user
ADD COLUMN statistic_date varchar(16)
GENERATED ALWAYS AS (date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')) STORED;

then created index:
CREATE INDEX report_user_userid_staticdate_idx
ON report_user(user_id,statistic_date);


HAProxy + Patroni - Configure reads from a master when no slaves available

This pattern of defining a frontend will use the replica as the main target, but if the read service is dead, then it will route traffic to the write servers - users should use 5911 for connecting to the read replica service.
frontend a_listen_fe
    bind *:5911
    acl is-read-service-dead nbsrv(cluster-a-slave) lt 1
    use_backend cluster-a-master if is-read-service-dead
    default_backend cluster-a-slave

listen cluster-a-master
    bind *:5011
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_a1 127.0.0.1:6011 maxconn 100 check port 8011
    server postgresql_a2 127.0.0.1:6012 maxconn 100 check port 8012
    server postgresql_a3 127.0.0.1:6013 maxconn 100 check port 8013

backend cluster-a-slave
    option httpchk OPTIONS /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_a1 127.0.0.1:6011 maxconn 100 check port 8011
    server postgresql_a2 127.0.0.1:6012 maxconn 100 check port 8012
    server postgresql_a3 127.0.0.1:6013 maxconn 100 check port 8013


Dropped the Distibution database before disabling publishing and distribution, how to proceed?

I Googled it -- http://www.sqlrepl.com/sql-server/accidentally-dropped-distribution-database/ -- try to follow his advice!


SQL Server 2008 R2: Restore latest backup to new database from script

This Is a Striped down version of the script I use to restore a great many databases at the same time. Hopefully I took out all of the parts that make it work for my scenario and left them with appropriate hints as to what to place in your version.

    -- Remove old DBs:
use [master]
go

DROP Database [YourDBName]
DROP Database [YourNextDBName]
DROP Database [AndSoOn]
DROP Database [AndSoForth]

GO

-- Create Restore List

Insert into [dbo].[BackupList] (DBName) Values ('YourDBName')
Insert into [dbo].[BackupList] (DBName) Values ('YourNextDBName')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoOn')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoForth')

GO

declare @counter [int]
declare @maxcounter [int]
declare @DBName [varchar](50)
declare @SQL1 [varchar](4000)
declare @SQL2 [varchar](4000)
declare @SQL3 [varchar](4000)
declare @SQL4 [varchar](4000)
set @counter = 0
select @maxcounter = max(id) from [dbo].[BackupList]

while @counter <= @maxcounter
BEGIN
                select @DBname = DBname from  .[dbo].[BackupList] where ID = @Counter

                IF @DBname LIKE '%WhateverYoursIsLike'
                BEGIN
                SET @SQL1 = 'RESTORE DATABASE '+ QUOTENAME(@DBName) + ' FROM  DISK = ''YourBack-UpLocation' + @DBName + '.bak'' WITH  FILE = 1,
                MOVE N''YourFileName'' TO ''YourRestoreLocation' + @DBName + '.mdf'',
                MOVE N''YourFileName_log'' TO ''YourRestoreLocation' + @DBName + '.ldf'''
                exec(@sql1)
                print(@sql1)
                END

                set @counter = @counter + 1
END

GO
Use [em_support]
GO
truncate table [em_support].[dbo].[BackupList]
GO
DBCC CHECKIDENT (BackupList, RESEED, 0)
GO

--> Modify DB properties
DECLARE @DBName SYSNAME
DECLARE @Login SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE curModifyProperties CURSOR FOR
                SELECT [name]
                FROM sys.databases
                WHERE [name] LIKE '%WhateverYoursIsLike'

OPEN curModifyProperties
FETCH NEXT FROM curModifyProperties
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

                --> BEG - Set trustworthy property
                SET @SQL = 'ALTER DATABASE '+QUOTENAME(@DBName)+' SET TRUSTWORTHY ON'
                EXEC (@SQL)
                --> END - Set trustworthy property

                --> BEG - Set database owner
                SET @SQL = QUOTENAME(@DBName)+'.[dbo].[sp_changedbowner] ''sa'''
                EXEC (@SQL)
                --> END - Set database owner

FETCH NEXT FROM curModifyProperties
INTO @DBName
END
CLOSE curModifyProperties
DEALLOCATE curModifyProperties
GO

I hope that helps or at least points you in the right direction.


Import database from MSSQL export dump on Ubuntu localhost

The problem was that the export dump file didn't have any extension and the SQLCMD couldn't recognize the file and didn't accept it.

I added the a .bak extension to the file and the SQL Server Management Tool restored the database from this modified file. It's still doesn't work on Ubuntu but that's a permissions problem. I'll maybe ask a separate question or look for it if it's already asked.


Generally it is better to load an InnoDB table with nothing more than the PRIMARY KEY.  Then do one ALTER TABLE to add all the indexes and foreign keys.

If you don't have an explicit PRIMARY KEY, that is bad.  Change a UNIQUE key to PRIMARY, if practical.

But, the foreign keys can be a problem because of dependencies.

Plan A:  ALTER one table at a time, but in an order such that there cannot be any FK issues.

Plan B:  Disable FKs while doing the ALTERs, then enable them.

In InnoDB, the PK is 'clustered' with the data.  Hence, the data is sorted by the PK and stored on disk in that order.  So, it is best to have the PK defined, and to supply the data to the loader in PK order.  The PK+Data is in a BTree.

Secondary keys (and FKs) involve separate BTrees.  Building them incrementally can lead to thrashing in the "buffer pool" (see innodb_buffer_pool_size).  Building such an index separately can involve a unix sort -- more efficient for really big indexes.

I would call 9M rows rather big (90th percentile), but not huge.

Another caution:  UNIQUE keys need to check as they are inserted.  I think that implies that you define UNIQUE keys up front, rather than afterwards.


when applying a sql server cumulative update or service pack on a virtual machine - what could be a safe way of stopping VMWaretools?

None of the services in your screenshot have anything to do with VMWare.

The one you highlighted is Wmiprvse.exe which is basically WMI

  WMI resides in a shared service host with several other services. To avoid stopping all the services when a provider fails, providers are loaded into a separate host process named "Wmiprvse.exe".

The other ones are

SSISTELEMETRY is basically the customer improvement service
Windows Agent Service is probably something for NetIQ.
PBIEgwService is an on-prem Power BI gateway.

In any case, just update the box and reboot it afterwards (I'm assuming you have a maintenance window for the update anyway), I'm fairly sure you can stop those 4 services during the update.


Version Store usage and entries in dm_tran_active_snapshot_database_transactions

Perhaps triggers are involved, which use the version store to provide the before/after row images exposed by the inserted and deleted virtual tables. As a first step, check active queries using the version store based on the session_id:
SELECT sql_text.text
FROM sys.dm_tran_active_snapshot_database_transactions AS tranactions
JOIN sys.dm_exec_requests AS requests ON requests.session_id = tranactions.session_id
CROSS APPLY sys.dm_exec_sql_text(requests.plan_handle) AS sql_text;


Is the same thing(internally) putting conditions in a query over a view that directly on the view?

Generally speaking a simple view is the same as a query, behind the scenes the view is expanded and passed along to the query optimizer as a normal query. Such a view is an organizational tool, not a performance enhancing one. Use these sort of views to limit what data some users can access directly (if users are accessing your DB directly instead of through Store Procedures) or to simplify referencing a commonly repeated query.

Indexed Views are a type of view for MSSQL Server implementations that can have performance implications as they possess their own unique clustered index on a subset of the underlying tables' data. This sort of view can improve performance as the clustered index is a copy of the data kept logically separate from the original tables, however it entirely depends on how the Indexed View is built. If you see references to views improving performance they may be talking about these and this option is only available on MSSQL Server to my knowledge.


Approaches for deleting unnecessary records from tables

I can suggest you another approach/solution. You can partition the table and delete partition older than 3 months just dropping them. This is fast and does not impact other inserts.
However seems that you need to keep at least one value; can you calculate values to be kept and insert them in another table?


Oracle dbms_redefinition fails with ora-01749, why?

Having SELECT privilege on your own table is unnecessary.

Granting privileges on your own objects added extra rows in the dictionary in earlier versions and it was considered as a bug, fixed in newer releases.

This note decribes this and why redefinition fails becasue of this:

Bug 8984274 - ORA-1749 Error Running DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (Doc ID 1345539.1)

Just revoke the privilege on your own objects.


Take a backup of the entire data directory for each.
Reinstall the version of postgresql you need
Point its data directory at your existing one

If you are running a packaged version of postgresql then there might be nothing to do for 3


Transaction log backup file larger than expected?

Having spent some time looking at this in more detail it would appear that the unusually large transaction log backups are due to excessive internal fragmentation of the transaction log itself. This is due to unrestricted autogrowth of the transaction log over a long period of time.

The following excerpt from a SQLSkills.com article more precisely defines the problem

  Often, when transaction logs are not pre-allocated and/or when there's
  been a lot of autogrowths, the transaction log can become internally
  fragmented. Internally your transaction logs are broken down into
  smaller more granular chunks called VLFs (Virtual Log Files). The size
  and number of VLFs you'll have depends largely on the size that the
  chunk is when it's added to you transaction log. If you add a new
  chunk to the transaction log which is 20MB (through autogrowth or
  through manual growth) then the number of VLFs that are added is 4. If
  you add a chunk which is greater than 64MB but less than or equal to
  1GB, you'll add 8 VLFs. If you add more than 1GB then you'll add
  16VLFs. In general, most transaction logs will only have 20 or 30 VLFs
  – even 50 could be reasonable depending on the total size of the
  transaction log. However, in many cases what happens is that excessive
  autogrowths can cause an excessive number of VLFs to be added –
  sometimes resulting in hundreds of VLFs. Having an excessive number of
  VLFs can negatively impact all transaction log related activities and
  you may even see degradation in performance when transaction log
  backups occur. To see how many VLFs you have solely look at the number
  of rows returned by DBCC LOGINFO. The number of rows returned equals
  the number of VLFs your transaction log file has. If you have more
  than 50, I would recommend fixing it and adjusting your autogrowth so
  that it doesn't occur as fequently. To get rid of all of the
  execessive VLFs, follow these easy steps to shrink off the fragmented
  chunk and add a new, clean chunk to your transaction log.

On this particular database it turns out we had 3011 VLFs, some degree over the recommended limit of 50 :)

We are now working through the steps in the above guide to reduce the number of internal VLFs. The QA database has been shrunk and the start size set to 2.5 gb, we now have 23 internal VLFs. So the resulting TLOG backup is in the region of 128 kb.


The amount of benefit from dynamic sampling will depend on the degree of cardinality estimate errors and how the query plan changes after you correct those errors. It is possible that your query will benefit and a good next step for you is to add a DYNAMIC_SAMPLING hint in your query and to observe how the EXPLAIN PLAN changes. However, a better solution would be to just fix your statistics. All queries will benefit from that instead of just the ones that you tune by hand.

In the workloads that I've observed, the queries that benefit the most from dynamic sampling go the other way: the optimizer estimates that very few rows will be returned from a large table which leads to nested loop joins and index use throughout the plan. If the optimizer knew that the large table returned many rows it would pick a better plan. Suppose the following query returns 100% of the data in X_TABLE:

SELECT COUNT(*)
FROM X_TABLE
WHERE NULLIF(X_COLUMN.NETWORKED_ID, 'X') IS NOT NULL;

On my machine Oracle uses a cardinality estimate of 5% of the rows from the table. This is a default cardinality estimate used in some cases when the optimizer doesn't have enough information. If I add a dynamic sampling hint then the estimate is fixed:

SELECT /*+ DYNAMIC_SAMPLING(X_TABLE 10) */ COUNT(*)
FROM X_TABLE
WHERE NULLIF(X_COLUMN.NETWORKED_ID, 'X') IS NOT NULL;

My rule of thumb is to use a sampling level of 10 for small tables and to use something less than 10 for larger tables. If you use 10 then Oracle will read all blocks from the table before generating an EXPLAIN PLAN.


Cannot do a restore using PgAdmin4 after a new install of PostgreSQL - pg_restore.exe file not found

I was able to fix (or patch) the problem by coping files.

Searched for pg_restore.exe from folder 'C:\Program Files\PostgreSQL\13'. I found the file in 'C:\Program Files\PostgreSQL\13\bin'.
Then I copied all the files from 'C:\Program Files\PostgreSQL\13\bin' folder to 'C:\Program Files\PostgreSQL\13\pgAdmin 4\runtime' where the error message says file is missing. Note: After install, there was no runtime folder. I had to create it.
The error went away and restore seems to work. I am not sure this is the right solution, but for now, it works.


That sounds like a problem with the shared drive arrangement, rather then one specific to sqlite. It would be worth stating which virtualisation method you are using as people may know if there are common problems with it.

The slower run from a resourse within the VM may be due to I/O overhead from the virt method, or extra IO contention because the input data was on a different physical drive to the VM. It may also be that the guest OS is memory starved, which will increase IO contention for large inputs as they can't be held in cache as easily on the host with it's larger memory. Again more detail would be needed for a surer answer.

Of course both problems could imply an intermittent IO error, so check you OS logs and the drives' SMART readings.

(this is likely to get more traction on serverfault, as it is likely not database specific)


Non Buffer Latch - Forefront Identity Manager

You mention Paul Randal in your question, so I assume you have seen this post where he talks about this stuff in great detail.   The ACCESS_METHODS_DATASET_PARENT Waits frequently deal with Parallel queries (as do your 30% CXPACKETS) and as Paul says in the blog that I linked, don't knee-jerk and set MaxDOP to 1 to get rid of those.   But I would wager based on the information provided that you do have some parallelism going on within your server, possibly for a bunch of things.   The question for you is do you think this is a problem?   That will depend on your situation, what the server does, how things are set, etc...     Another good link that describes this wait can be found here and in this case a very specific situation is described (and sounds similar to your situation) and the resolution to this one is described in the link.

Depending on what you find to be true on your server, you may want to run some tests with MaxDOP or possible with changing the Cost Threshold For Parallelism (it defaults to 5 and in todays world that is frequently too low, but you need to see for yourself for your particular server).   Cost Threshold For Parallelism is one that Jeremiah Peschka says is one of the 5 settings to change for SQL.   Read through those links run some tests with those properties on your server if you have the ability to do so and hopefully that will help to get you where you want to go.


Prevent reset of auto_increment id in Innodb database after server restart

(avoiding the issue by never deleting)

Since you want to keep the "Posted by =User #123=" information after you delete the user with id=123, you could also consider using 2 tables for storing users data. One for Active users  and one for all (including deleted ones from the active users). And never delete those ids from the AllUser table:

CREATE TABLE AllUser
( user_id INT AUTO_INCREMENT
, ...
, PRIMARY KEY (user_id)
) ;

------
--- Forum posts FK should reference the `AllUser` table

CREATE TABLE ActiveUser
( user_id INT
, ...
, PRIMARY KEY (user_id)
, FOREIGN KEY (user_id)
    REFERENCES AllUser (user_id)
) ;

------
--- All other FKs should reference the `ActiveUser` table

This will of course complicate the insert new user operation. Any new user will mean 2 inserts, one in each table. Deleting a user though will be by deleting from the ActiveUser table only. All FKs will be deleted with cascading, except the forum posts, which will be referencing the Alluser table (where no deleting will ever happen).


Generate DACPAC diff using predefined config

Per the documentation, use /Profile instead of /p:SqlPublishProfilePath:

  Parameter:  /Profile
  Description:
  Specifies the file path to a DAC Publish Profile. The profile defines a collection of properties and variables to use when generating outputs

So this should work:

/Profile: "F:\TMap\Root\Application\Dev\Database\Financials.SQL\Financials.SQL\Publish Configs\Financials.SQL_Dev.publish.xml"


You can use SQLCMD mode in SSMS (or SQLCMD itself) to do this.

The following query would connect to an instance, check for the database in question, create the login if it does not exist, and then connect to the database and add a user for that login.

:CONNECT InstanceName
GO
IF EXISTS (SELECT Name FROM sys.databases WHERE name = 'DBName')
    EXEC sp_executeSQL N'   IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = ''LOGIN_NAME'')
        CREATE LOGIN LOGIN_NAME FROM WINDOWS;

    USE DBName;
    IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = ''LOGIN_NAME'')
        CREATE USER LOGIN_NAME;'
GO

You can do other kinds of variable substitution to make things a little quicker, it's worth reading up at https://docs.microsoft.com/en-us/sql/relational-databases/scripting/edit-sqlcmd-scripts-with-query-editor on the things that you can do.


No, you can't add custom languages to the SQL Server system.

An end user won't interact with the system as such: you'd implement multi-language within your database design/application. The system language is irrelevant.


Special Considerations for SQL Server with 512 GB RAM / 64 Cores?

Max Server Memory should be no higher than 445 GB. This gives Windows enough memory to manage itself. Yes, it really does need that much.

Max Degree of Parallelism should be set to the number of logical cores in one NUMA node, up to a maximum of 8.

If it's running an OLTP environment, make sure Optimize for Ad Hoc Workloads is enabled.

Ensure power saving on the OS is set to High Performance.

TempDB should be on your fastest storage, followed by transaction logs.

There's a whole lot more you can look at, but these are the main items.

As some comments have noted, SQL Server 2008 R2 is out of mainstream support. Extended support ends in July 2019 (not this year), so there's just about enough time to start a project to move to a newer version. I would be looking firmly at SQL Server 2017 or later.


Replacing MYSQL table with data from another table - no downtime

How about a view? Point your application at the view and then alter the view at an instant when there is no access to point to table2. Once that is done, you could drop table1.


Optimized date compare in WHERE clause, convert or datediff=0?

Instead, try

WHERE InsertedOn>=CAST(GETDATE() AS date) AND
      InsertedOn<DATEADD(day, 1, CAST(GETDATE() AS date))

This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the query are included in the index. If you have lots of columns in the query, using the table's clustered index is probably best. Read up on covering indexes.


You need to use

--authenticationDatabase admin

parameter...  Because, even you give "use mydb" before creating user...  User is created at the admin -database.
So,
mongo -u nameuser -p password  --authenticationDatabase admin localhost:12345/mydb


Should child comment be treated as separate table?

Your code is:

INSERT INTO comments
SET parent = 0,
    post = 385,
    comment = 'Hello, World' ;

Do you have a row in comments with id = 0? If not, the error is what one would expect. Put parent=NULL, not parent=0, if you want a comment to not have a parent.


The 'Database' with 'ID' = 'xxxxxxx' doesn't exist in the collection

One thing that could cause this is if you use the Name of the database instead of the ID.

For example, I created a database with with the name Demo1, so the ID defaulted to the same string.

Afterwards I renamed the database, but that doesn't change the ID.

If your AMO script uses the name string instead of the ID string that could cause this error.


I don't know of any way to do that with jsonpath.

I'd suggest pulling apartments with any number of rooms and filtering that list, but I suspect that would get quite large as the dataset grows ...

... but then, if you're talking about large data volumes here, I would have to ask why you're not doing this is a proper, Relational way?  Yes, you can get postgres to do this with JSON data but should you?  This is a very simple data structure that you could represent in a single table which, properly indexed, will cope with huge data volumes.

select level, no, size, rooms
from apts
order by 1, 2 ;

+-------+----+------+-------+
| level | no | size | rooms |
+-------+----+------+-------+
|     1 |  1 |   40 |     1 |
|     1 |  2 |   80 |     3 |
|     1 |  3 | null |     2 |
|     2 |  4 |  100 |     3 |
|     2 |  4 |   60 |     2 |
+-------+----+------+-------+

select *
from apts
where rooms in ( 3, 5, 6, 10, 11 ) ;

+-------+----+------+-------+
| level | no | size | rooms |
+-------+----+------+-------+
|     1 |  2 |   80 |     3 |
|     2 |  4 |  100 |     3 |
+-------+----+------+-------+

Relational databases are really good at finding small bits of [related] data and bolting them back together again.  They're generally pretty rubbish at taking large blocks of data, like chunks of JSON, and pulling them apart again.  Yes, RDBMS providers are trying to improve this, but I would suggest it'll still be way slower than doing it the "old-fashioned" way.


sp_BlitzFirst - option to save more than only SQL Server PerfMon stats?

Q: For Perfmon stats from sp_BlitzFirst, is there an option or parameter to capture additional perfmon stats?
Not at the moment! They're all hard-coded into the proc. If there are additional ones you'd like, they'd have to be added to the list. You can make that suggestion over on GitHub.


professional services (consultants) resource allocation system

Google "open source project management software" - I got 49 million hits. There appear to be interesting options here, here and here.

There is very little that is truly original in software. What I would advise you to do is to download (a few of the) projects which look interesting, then check out their data models - that's the beauty of Open Source - look at the ideas of others and then adapt them to your own particular situation.

Many of the projects appear to be right up your alley, with many time assignment and/or management options which appear to be the core of your question.


How to increase the size of query audit log?

For DBA_HIST_SQLTEXT, you need Enterprise Edition with the Diagnostic Pack. If you have that licensed, you can enable collection of historical data with:

alter system set control_management_pack_access='DIAG';

You can control the retention time of this data with:

begin
  dbms_workload_repository.modify_snapshot_settings
  (
    retention => 5184000
  );
end;
/

Where 5184000 is 60 days (5184000 minutes).


You can also use your favorite hex editor :). This can even be a good learning lesson to see how things work under the hood. For example, you could have a look at those posts/videos:

How does sql server store data by Brent Ozar;
Corrupting Databases for Dummies- Hex Editor Edition by Kendra Little.

But using a high level language like SQL or your vendors implementation (tsql, pl/sql...) makes things so much easier.


What grants should I give to allow `COPY ... TO STDOUT`?

The replication user needs to read the tables:
GRANT SELECT ON table1, table2, ... TO replication;

This must of course be run on the logical replication primary server.


avoid lock and allow two scripts to insert data in one table

idle in transaction means it is waiting for data from the client, not that is waiting on a lock.

And your excerpt from pg_lock supports this, assuming the last column is the "granted" column.  All locks have been granted, no one is blocking.

If your scripts are blocking each other, it seems to be happening outside of the database.  Or at least, outside of that particular database.


MongoDB replica set node failure without arbiter

The context you are missing is that failure of a replica member does not affect the number of configured members the replica set has (or the required voting majority to maintain a primary). Any changes in fault tolerance or replica set election requirements will involve a re-configuration of the replica set (eg. adding/removing members or changing voting members) rather than a replica set member state change (eg. DOWN, RECOVERING, ..).

A three node replica set with one down member still has three members (with a strict majority of two). If you think of it as analogous to a RAID configuration, a three node replica set with one member down is running in degraded mode and cannot tolerate the failure of any further voting members. This allows for continued availability, but you will want to recover or replace the unavailable member to return the replica set to a healthy state.

You can check the current replica set configuration with rs.conf() and the current state of members with rs.status().