TSQL - create a function that takes a multi-row query result as an argument

Really what you are looking to do is inline concatenation / aggregation. The two easiest methods are:

SQLCLR (works with SQL Server 2005 and newer, but not Azure SQL Database unless it is the new Managed Instance type)
The STRING_AGG aggregate function (works with SQL Server 2017 and newer, and works on Azure SQL Database)

Currently, neither of these options works for you:

Option #1 is prohibited by policy, and (or due to) the fact that the client(s) will soon be migrating to Azure SQL Database which does not support SQLCLR (anymore; it did for about 18 months).
Option #2 is not possible due to being on SQL Server 2014 which does not have that function

This might be a long-shot, but given that you are only prohibited from using SQLCLR by policy, and that they will be migrating to Azure SQL Datbase, you might could do the following:

Short-term (prior to migrating to Azure SQL Database): use a SQLCLR aggregate function
Long-term (upon migrating to Azure SQL Database): swap out the SQLCLR aggregate for STRING_AGG

While this does entail modifying queries upon migration, the modification is a simple replacement. Assuming that all stored procedures, functions, etc are stored in a repository (e.g. Git, SVN, etc), it should be fairly easy to do a mass replacement across multiple files. Then deploy, test, and commit to the repository.

For example (using the SQL# library that I wrote which has Agg_Join in the Free version):

SELECT so.[schema_id],
       COUNT(*) AS [NumObjects],
       SQL#.Agg_Join(DISTINCT RTRIM(so.[type])) AS [Types]
FROM   sys.objects so
GROUP BY so.[schema_id];

SELECT so.[schema_id],
       COUNT(*) AS [NumObjects],
       STRING_AGG(RTRIM(so.[type]), ',')
              WITHIN GROUP (ORDER BY RTRIM(so.[type])) AS [Types]
FROM   sys.objects so
GROUP BY so.[schema_id];

These don't return identical results sets unless you remove the DISTINCT from SQL#.Agg_Join(DISTINCT RTRIM(so.[type])). I kept that in there to show a case where it helps, and to point out that for some reason, DISTINCT is not supported in the STRING_AGG function :(.

If you can do a Regular Expression (RegEx) replace, you can swap out:

SQL#.Agg_Join({stuff})

for:

STRING_AGG({stuff}, ',') [WITHIN GROUP (ORDER BY {stuff})]

with minimal effort. The WITHIN GROUP part is optional (I added it there since the DISTINCT in the SQLCLR version did the ordering). The main point being that the queries are otherwise untouched and should produce the same results, hence not needing a massive retesting effort.

In fact, if you create your own SQLCLR UDA such that it has the second parameter for the delimiter (the SQL# Agg_Join doesn't such that it can work in SQL Server 2005), you could name it "String_Agg". This would reduce the find/replace to be replacing dbo.String_Agg( with String_Agg(.


MariaDB or MySQL @@SESSION.tx_read_only always returns 0

another fiddle test
So both MariaDB  on tx_read_only and mysql on transaction_read_only have the session variable set to the current session value. The "next transaction access mode" remains hidden.
Catching insert errors seems like the only option.


TDE enabled database backup taking too long

I managed to reduced the time to 28min now, changed @MaxTransferSize= 524288 to 262144 and also removed @Verify='Y' parameter which did the trick.

I believe backup @Verify option was the real issue here, as soon as I removed the option backup time cut to half.


How do I shrink the physical Transaction Log file when it's the principal in a mirror?

From what I can tell, I can't.

I believe much of the log file is waiting to be mirrored to the mirror server, but the mirror is down and can't be resumed because the mirrored transaction log has also grown to take up all the space on the disk.

This theory is further backed up by the fact once I remove the mirroring, the DBCC SHRINKFILE command correctly shrinks the physical log file, and the log_reuse_wait_desc is back to waiting on LOG_BACKUP

I can't shrink the log file on the mirrored server because it's acting as a mirror and can't be opened, so I think the mirror is unrecoverable.

So I am going to delete the mirror entirely, and set everything up again (very slow process with a 300GB database). And this time, I'll make sure the transaction log size is fairly small before starting up the mirror, and that the transaction log backups are running once the mirror is back up and running.

I'm also going to set a limit on how big the transaction log can grow on the production server, so the mirror doesn't ever try to grow its transaction log beyond the size available on the disk.


Should we always prefer clustering over a master-slave configuration if the goal is high-availability?

Generally, Master-Slave is a disaster recovery setup: when master dies, slave becomes master. Manual intervention is needed or you need some redirection layer, You may also have replication lag built in so that master corruption can be stopped before it hits the slave.

Clustering is high availability: failover is automatic.

You need both HA and DR, not just one. You can have distributed clusters that mix it a bit, but generally the concepts are different.

If the 2 VMs are on the same physical host, this is neither HA nor DR of course.

If you do use clustering, then use Percona. MySQL cluster is broken in RDBMS terms


This is string manipulation: nothing to do with datetimes

SELECT
   SUBSTRING(Whatever, 4,2) + '-' + LEFT(Whatever, 2) + '-' + RIGHT(Whatever, 4)
FROM
   SomeTable;

If you stored it as datetime, then you'd format it into whatever locale you wanted on the client. Then it'd be sortable and comparable too.


In SQL Server 2012 nothing comes from buffer pool anymore, except data pages. This is a change from SQL Server 2008R2. See Memory Manager surface area changes in SQL Server 2012. The buffer pool is just another client of the Memory Manager client for allocations, just like any other memory consumer. So the question is basically unasked in SQL Server 2012.


oracle - hashing method, excluding DBMS_CRYPTO

DBMS_OBFUSCATION_TOOLKIT was deprecated and replaced by DBMS_CRYPTO.
ORA_HASH does not produce a unique checksum, per se, and does not support LOB types.
STANDARD_HASH uses DBMS_CRYPTO under the hood, but also does not support LOB types.

Having recently faced a similar problem myself, I was able to get execute privileges on DBMS_CRYPTO for the application schema by demonstrating the need. The DBAs didn't have any problem granting the access as long is it was required and documented. It's the only real option for what you're trying to do. My function to produce a SHA256 hash wound up looking like this:
function get_sha256sum (p_clob in clob) return raw
is
begin
    return dbms_crypto.hash (src => utl_raw.cast_to_raw(p_clob), typ => 4);
end get_sha256sum;

Note: don't try to cast the raw checksum to anything else - it's only accurate as a checksum in the original format.


If you've not changed those parameters they should be enabled by default.

You should only need to uncomment them if you're going to change them.

You can check the running parameters for the server with SHOW ALL; or by specific parameter, e.g. SHOW fsync; on the psql command line.

The wal_sync_method should default to a best guessed method for your platform, most likely fdatasync on Linux or fsync on most other operating systems.

You can read more about these parameters in the WAL runtime config documentation

However, it is awesome that you care enough to double-check all this. =)


MySQL schema for recurring events - Query by specific date

Let's say you made the table like this

CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    station_arr_id INT NOT NULL,
    station_dep_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY station_arr_index (station_arr_id,arr_dt),
    KEY station_dep_index (station_dep_id,dep_dt)
);

Next, you insert into the table the event

Arriving From Station 21 on April 30, 2014
Arrived at Station 29 at 10:00 AM on April 30, 2014
Departing From Station 29 1 minute later

Here is that INSERT query

INSERT INTO TrainEvents (station_arr_id,arr_dt,station_dep_id,dep_dt)
VALUES (21,'2014-04-30 10:00:00',29,'2014-04-30 10:01:00');

OK, now let's see how you can query the following departure

All Departures 10:00 AM on April 30, 2014 from Station 29
Check Departures 15 minutes before and after

Here is that SELECT query

SET @MinuteWindow = 15;
SET @DepartureDT = '2014-04-30 10:00:00';
SET @DepartureDTMinBef = @DepartureDT - INTERVAL @MinuteWindow MINUTE;
SET @DepartureDTMinAft = @DepartureDT + INTERVAL @MinuteWindow MINUTE;
SELECT * FROM TrainEvents
WHERE station_dep_id = 29
AND dep_dt >= @DepartureDTMinBef;
AND dep_dt <= @DepartureDTMinAft;

EPILOGUE

Given your initial idea, you could change the station_arr_id and station_dep_id into a single route_id and store the routes in a route table

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY arr_index (route_id,arr_dt),
    KEY dep_index (route_id,dep_dt)
);

You could also make the event record arrivals and departures separately.

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    event_dt DATETIME NOT NULL,
    event_type TINYINT NOT NULL # 1 for arrival, 2 for departure

    ...
    PRIMARY KEY (id),
    KEY event_index1 (route_id,event_dt,event_type),
    KEY event_index2 (route_id,event_type,event_dt),
);

I am just giving ideas. I'll leave it to you to implement. Perhaps, to give you some UI ideas, see the site I use to commute : http://as0.mta.info/mnr/schedules/sched_form.cfm . The result of the route you pick in that site shows route departures within a 5-hour window (2.5 hours before and 2.5 hours after).

Give it a Try !!!


SQL Server Restore from one database to another

"It just does".  Its just meta data about the backup.

The Books Online article http://msdn.microsoft.com/en-us/library/ms186299.aspx hints at it, albeit not very well in the "Remarks" section.  From that article you'll see a reference to sp_delete_backuphistory.  So yes you can safely remove it if you need to.

The info that goes into that backupset table, from a restore, is pulled out of the backup itself.  Run:

RESTORE HEADERONLY FROM DISK = 'Path to backup file';

and you'll find the information.


Query best possible matches and order them

SELECT col1,
       col2,
       col3,
       col4,
       col5,
       col6
FROM TableX
WHERE col1 = 1
   OR col2 = 2
   OR col3 = 3
ORDER BY (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) +
         (CASE WHEN col2 = 2 THEN 1 ELSE 0 END) +
         (CASE WHEN col3 = 3 THEN 1 ELSE 0 END) DESC,
         col4, col5, col6

or, for MS-Access:

ORDER BY IIF(col1 = 1,1,0) +
         IIF(col2 = 2,1,0) +
         IIF(col3 = 3,1,0) DESC,
         col4, col5, col6


Find highest level of a hierarchical field: with vs without CTEs

You definitely have to script this via MySQL Stored Procedure Language

Here is a Stored Function called GetParentIDByIDto Retrieve a ParentID given an ID to Search For

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetParentIDByID` $$
CREATE FUNCTION `junk`.`GetParentIDByID` (GivenID INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE rv INT;

    SELECT IFNULL(parent_id,-1) INTO rv FROM
    (SELECT parent_id FROM pctable WHERE id = GivenID) A;
    RETURN rv;
END $$
DELIMITER ;

Here is a Stored Function called GetAncestryto Retrieve a List of ParentIDs starting from the First Generation all the up the hierarchy given an ID to start with:

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM pctable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

Here is something to generate sample data:

USE junk
DROP TABLE IF EXISTS pctable;
CREATE TABLE pctable
(
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO pctable (parent_id) VALUES (0);
INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
SELECT * FROM pctable;

Here is what it generates:

mysql> USE junk
Database changed
mysql> DROP TABLE IF EXISTS pctable;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE pctable
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     parent_id INT,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO pctable (parent_id) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pctable;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         0 |
|  2 |         1 |
|  3 |         2 |
|  4 |         3 |
|  5 |         3 |
|  6 |         4 |
|  7 |         5 |
|  8 |         6 |
|  9 |         4 |
| 10 |         5 |
| 11 |         6 |
| 12 |         7 |
| 13 |         7 |
| 14 |         8 |
| 15 |         9 |
| 16 |        10 |
| 17 |         5 |
| 18 |         6 |
| 19 |         7 |
| 20 |         8 |
| 21 |         8 |
| 22 |         9 |
| 23 |        10 |
| 24 |        11 |
| 25 |         9 |
| 26 |        10 |
| 27 |        11 |
| 28 |        12 |
| 29 |        12 |
| 30 |        13 |
| 31 |        14 |
| 32 |        15 |
+----+-----------+
32 rows in set (0.00 sec)

Here are what the functions generate for each value:

mysql> SELECT id,GetParentIDByID(id),GetAncestry(id) FROM pctable;
+----+---------------------+-----------------+
| id | GetParentIDByID(id) | GetAncestry(id) |
+----+---------------------+-----------------+
|  1 |                   0 |                 |
|  2 |                   1 | 1               |
|  3 |                   2 | 2,1             |
|  4 |                   3 | 3,2,1           |
|  5 |                   3 | 3,2,1           |
|  6 |                   4 | 4,3,2,1         |
|  7 |                   5 | 5,3,2,1         |
|  8 |                   6 | 6,4,3,2,1       |
|  9 |                   4 | 4,3,2,1         |
| 10 |                   5 | 5,3,2,1         |
| 11 |                   6 | 6,4,3,2,1       |
| 12 |                   7 | 7,5,3,2,1       |
| 13 |                   7 | 7,5,3,2,1       |
| 14 |                   8 | 8,6,4,3,2,1     |
| 15 |                   9 | 9,4,3,2,1       |
| 16 |                  10 | 10,5,3,2,1      |
| 17 |                   5 | 5,3,2,1         |
| 18 |                   6 | 6,4,3,2,1       |
| 19 |                   7 | 7,5,3,2,1       |
| 20 |                   8 | 8,6,4,3,2,1     |
| 21 |                   8 | 8,6,4,3,2,1     |
| 22 |                   9 | 9,4,3,2,1       |
| 23 |                  10 | 10,5,3,2,1      |
| 24 |                  11 | 11,6,4,3,2,1    |
| 25 |                   9 | 9,4,3,2,1       |
| 26 |                  10 | 10,5,3,2,1      |
| 27 |                  11 | 11,6,4,3,2,1    |
| 28 |                  12 | 12,7,5,3,2,1    |
| 29 |                  12 | 12,7,5,3,2,1    |
| 30 |                  13 | 13,7,5,3,2,1    |
| 31 |                  14 | 14,8,6,4,3,2,1  |
| 32 |                  15 | 15,9,4,3,2,1    |
+----+---------------------+-----------------+
32 rows in set (0.02 sec)

MORAL OF THE STORY : Recursive data retrieval must be scripted in MySQL

UPDATE 2011-10-24 17:17 EDT

Here is the reverse of GetAncestry. I call it GetFamilyTree.

Here is the algorithm:

Place Given ID in Queue
Loop

Dequeue into front_id
Retrieve all ids into queue_children whose parent_id = front_id
Append queue_children to retval_list (rv)
Enqueue queue_children
Repeat until queue and queue_children are concurrently empty

I believe from my Data Structures and Algorithms classes in College, this is called something like preorder/prefix tree traversal.

Here is the code:

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(id) qc
        FROM pctable WHERE parent_id = front_id) A;

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    RETURN rv;

END $$

Here is what each row produces

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM pctable;
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id)                                                                    |
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
|  1 |         0 |                   0 |                 | 2,3,4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31 |
|  2 |         1 |                   1 | 1               | 3,4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31   |
|  3 |         2 |                   2 | 2,1             | 4,5,6,9,7,10,17,8,11,18,15,22,25,12,13,19,16,23,26,14,20,21,24,27,32,28,29,30,31     |
|  4 |         3 |                   3 | 3,2,1           | 6,9,8,11,18,15,22,25,14,20,21,24,27,32,31                                            |
|  5 |         3 |                   3 | 3,2,1           | 7,10,17,12,13,19,16,23,26,28,29,30                                                   |
|  6 |         4 |                   4 | 4,3,2,1         | 8,11,18,14,20,21,24,27,31                                                            |
|  7 |         5 |                   5 | 5,3,2,1         | 12,13,19,28,29,30                                                                    |
|  8 |         6 |                   6 | 6,4,3,2,1       | 14,20,21,31                                                                          |
|  9 |         4 |                   4 | 4,3,2,1         | 15,22,25,32                                                                          |
| 10 |         5 |                   5 | 5,3,2,1         | 16,23,26                                                                             |
| 11 |         6 |                   6 | 6,4,3,2,1       | 24,27                                                                                |
| 12 |         7 |                   7 | 7,5,3,2,1       | 28,29                                                                                |
| 13 |         7 |                   7 | 7,5,3,2,1       | 30                                                                                |
| 14 |         8 |                   8 | 8,6,4,3,2,1     | 31                                                                                |
| 15 |         9 |                   9 | 9,4,3,2,1       | 32                                                                                |
| 16 |        10 |                  10 | 10,5,3,2,1      |                                                                                |
| 17 |         5 |                   5 | 5,3,2,1         |                                                                                |
| 18 |         6 |                   6 | 6,4,3,2,1       |                                                                                |
| 19 |         7 |                   7 | 7,5,3,2,1       |                                                                                |
| 20 |         8 |                   8 | 8,6,4,3,2,1     |                                                                                |
| 21 |         8 |                   8 | 8,6,4,3,2,1     |                                                                                |
| 22 |         9 |                   9 | 9,4,3,2,1       |                                                                                |
| 23 |        10 |                  10 | 10,5,3,2,1      |                                                                                |
| 24 |        11 |                  11 | 11,6,4,3,2,1    |                                                                                |
| 25 |         9 |                   9 | 9,4,3,2,1       |                                                                                |
| 26 |        10 |                  10 | 10,5,3,2,1      |                                                                                |
| 27 |        11 |                  11 | 11,6,4,3,2,1    |                                                                                |
| 28 |        12 |                  12 | 12,7,5,3,2,1    |                                                                                |
| 29 |        12 |                  12 | 12,7,5,3,2,1    |                                                                                |
| 30 |        13 |                  13 | 13,7,5,3,2,1    |                                                                                |
| 31 |        14 |                  14 | 14,8,6,4,3,2,1  |                                                                                |
| 32 |        15 |                  15 | 15,9,4,3,2,1    |                                                                                |
+----+-----------+---------------------+-----------------+--------------------------------------------------------------------------------------+
32 rows in set (0.04 sec)

This algorithm works cleanly provided there are no cyclic paths. If there exists any cyclic paths, you would have to add a 'visited' column to the table.

Once you add visited column, here is the algorithm blocking cyclic relationships:

Place Given ID in Queue
Mark all visited with 0
Loop

Dequeue into front_id
Retrieve all ids into queue_children whose parent_id = front_id and visited = 0
Mark all queue_children just retrieved with visited = 1
Append queue_children to retval_list (rv)
Enqueue queue_children
Repeat until queue and queue_children are concurrently empty

UPDATE 2011-10-24 17:37 EDT

I created a new table called observations and populated your sample data. I changed the stored procedures to use observations instead of pctable. Here is your output:

mysql> CREATE TABLE observations LIKE pctable;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO observations VALUES (1,3), (2,5), (3,0), (4,10),(5,0),(6,1),(7,5),(8,10),(9,10),(10,3);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM observations;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         3 |
|  2 |         5 |
|  3 |         0 |
|  4 |        10 |
|  5 |         0 |
|  6 |         1 |
|  7 |         5 |
|  8 |        10 |
|  9 |        10 |
| 10 |         3 |
+----+-----------+
10 rows in set (0.00 sec)

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM observations;
+----+-----------+---------------------+-----------------+-------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id) |
+----+-----------+---------------------+-----------------+-------------------+
|  1 |         3 |                   3 |                 | 6                 |
|  2 |         5 |                   5 | 5               |                   |
|  3 |         0 |                   0 |                 | 1,10,6,4,8,9      |
|  4 |        10 |                  10 | 10,3            |                   |
|  5 |         0 |                   0 |                 | 2,7               |
|  6 |         1 |                   1 | 1               |                   |
|  7 |         5 |                   5 | 5               |                   |
|  8 |        10 |                  10 | 10,3            |                   |
|  9 |        10 |                  10 | 10,3            |                   |
| 10 |         3 |                   3 | 3               | 4,8,9             |
+----+-----------+---------------------+-----------------+-------------------+
10 rows in set (0.01 sec)

UPDATE 2011-10-24 18:22 EDT

I changed the code for GetAncestry. There was WHILE ch > 1 it should be WHILE ch > 0

mysql> SELECT id,parent_id,GetParentIDByID(id),GetAncestry(id),GetFamilyTree(id) FROM observations;
+----+-----------+---------------------+-----------------+-------------------+
| id | parent_id | GetParentIDByID(id) | GetAncestry(id) | GetFamilyTree(id) |
+----+-----------+---------------------+-----------------+-------------------+
|  1 |         3 |                   3 | 3               | 6                 |
|  2 |         5 |                   5 | 5               |                   |
|  3 |         0 |                   0 |                 | 1,10,6,4,8,9      |
|  4 |        10 |                  10 | 10,3            |                   |
|  5 |         0 |                   0 |                 | 2,7               |
|  6 |         1 |                   1 | 1,3             |                   |
|  7 |         5 |                   5 | 5               |                   |
|  8 |        10 |                  10 | 10,3            |                   |
|  9 |        10 |                  10 | 10,3            |                   |
| 10 |         3 |                   3 | 3               | 4,8,9             |
+----+-----------+---------------------+-----------------+-------------------+
10 rows in set (0.01 sec)

Try it now !!!


MYSQL query to find records between two date range

Your query has a logical flaw: it tries to find people who are NOT in the list of people who DO have coaching but only if the coaching is NOT in the target window.

Instead, join the employee and coaching table on employee_id, using all rows in employee but only using rows in coaching where the stop_date is later than or equal to the desired start date (coaching ends after the window starts) and the the start_date is less than or equal to the desired stop date (coaching starts before the window ends).  Then exclude the employees with one or more matching rows in coaching.

SELECT e.id AS employee_id
  FROM employee e
  LEFT JOIN coaching c ON c.employee_id = e.id
                      AND c.end_date >= '2014-12-26'
                      AND c.start_date <= '2015-01-30'
 WHERE c.employee_id IS NULL;

Live demo: http://sqlfiddle.com/#!9/79b39/4


Excluding clustered key columns from non-clustered indexes definitions

On the SQL Saturday event I've attended, one of the lecturers said it's a always good practice to exclude the clustered key columns from the non-clustered index key definition and include clause, too.

I disagree. Let me explain with the table in your question:

The clustered index is: (RecordID, QuestionID) and there are many more columns.

Any non-clustered index will also have the clustered key columns, too, appended in the end. So, an index like:

(Pts)   is equivalent to:   (Pts, RecordID, QuestionID)

and similarly:

(Pts, PtsOf)           <->:  (Pts, PtsOf, RecordID, QuestionID)

(Pts, RecordId)        <->:  (Pts, RecordID, QuestionID)

(Pts, QuestionID)      <->:  (Pts, QuestionID, RecordID)

(Pts) INCLUDE (PtsOf)  <->:  (Pts, RecordID, QuestionID) INCLUDE (PtsOf)

(QuestionID, RecordID) <->:  (QuestionID, RecordID)

For joint tables or like this one that have a composite primary/unique key - whether it is clustered or not, it's very often to have queries that need the (a,b) index and others that will use better the (b,a) index,  sometimes queries that need both. So, one often needs both of these.

If the composite clustered key has more than two columns - say (a,b,c) - it's often that you may need an index on (b,c,a) or in (d,b) and another on (e,c,a) (which of course will be equivalent to (b,c,a), (d,b,a,c) and (e,c,a,b) respectively.) You can't just remove these columns from the definitions of the non-clustered keys because the column order will change.

The suggestion has one good point though. The clustered key columns can be removed from the INCLUDE part. They are redundant, just noise there.

About the indexes in the question, a non-CI on (Pts, PtsOf) is equivalent to (Pts, PtsOf, RecordID, QuestionID), so it is very different than the original non-CI on (RecordID, QuestionID) INCLUDE (Pts, PtsOf).  It will use a bit more space than the original and of course these two indexes will be useful for different types of queries.

The (Pts, PtsOf) will be be useful, for example, for queries with WHERE Pts BETWEEN 0 AND 100, WHERE Pts = 200 AND PtsOf = 300, etc.
The (RecordID, QuestionID) INCLUDE (Pts, PtsOf) is basically a copy of the table with only the 2 clustered key columns and 2 only extra columns (of the many). This is (rarely) useful and it's a form of vertical partitioning. If you often have queries that need all the rows of the table but only these 2 columns, then it's probably one these (rather rare) cases where the extra space and effort to maintain this index is justified.


Redo log size results into performance degrade

"Free buffer wait" event usually means the buffer cache is not big enough. Increasing the size of the SGA will alleviate this wait event by allowing more changes to happen before all buffers are dirty.

"Redo log check point complete" wait event happens when a block that has been changed in the buffer cache has not yet been written to the disk. The online redo log that contains this change cannot be archived until this has happened.  Adding extra log files or increasing the size of the online redo logs will alleviate this wait event in the same way as increasing the size of the SGA - more changes can be made before this error is encountered.

However, both of these events have the same root cause - too many dirty blocks in the buffer cache. This is caused by poor DBWR performance as you suggest above. Try adding extra DBWR processes. If this doesn't help then you probably have an I/O problem.


Referential integrity after disable/enable trigger all

As it seems that there is no way to check referential integrity after using DISABLE TRIGGER ALL / ENABLE TRIGGER ALL, I finally used DROP/CREATE CONSTRAINT instead.

Inspired by this post on Magnus Hagander's blog I used such queries to generate alter commands for removing and creating FK:

-- Generate ALTER TABLE ... ADD CONSTRAINT
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '||
pg_get_constraintdef(pg_constraint.oid)||';'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
WHERE pg_constraint.contype = 'f'
ORDER BY nspname DESC, relname DESC, conname DESC

-- Generate ALTER TABLE ... DROP CONSTRAINT
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
WHERE pg_constraint.contype = 'f'
ORDER BY nspname, relname, conname

Here is my process:

Start Transaction
Drop Constraints
Do some stuff... (insert, update, whatever...)
Create Constraints
if it fails then rollback, else Commit Transaction


How often should we make a base backup? Can we just create it once and then just archive the WAL files afterwards?

You can, but the more WALs that have accumulated, the longer it will take to restore.  You should periodically do a test restoration.  If it takes "too long" (whatever that means for your organization) then you should take a new base backup which will need to replay less WAL.

What I usually do is keep the first base backup ever taken (for a given major version) so that I can PITR to any point.  Then keep annual base backups, so that PITR can take less time if starting from one of them.  And take monthly base backups so that crash-recovery restoration would take less time.  Once a new monthly base backup has been tested, then the second-previous one is deleted, unless it happens to also be the annual one.  If I do some maintenance operation which generates a large amount of WAL, I might take an out-of-cycle backup because otherwise restoration from the last monthly might take too long to replay.  Of course this is only an example, you have to suit yourself.

  Do we need both approaches or one would suffice?

At some point in the distant future, it might be hard to find suitable hardware unto which to restore a base backup, (with virtual machines that may be less of a concern, but
it just pushes it down a level.  For how long will it be easy to find VM software that still supports 32-bit guests?). A logical backup could help in that case.  That is about the only reason I can think of to take routine "pg_dump" if you are also doing base backups and archiving.  I have wanted historical logical dumps occasionally, so I just did PITR onto a dummy server, then ran pg_dump against that dummy server.


Database Design - Storing Stadium Capacity for Current and Past Events

Three options:

Option One

Create a history table storing all data that can change about the stadium. This could also include its name, though its location is probably set. Lets call this "StadiumHistory". Your Schedule table would then use this as its FK. The StadiumHistory table would have a FK to the Stadium table which uniquely identifies the stadium and its location.

Option Two

Ask yourself "what is my eventual goal?". The answer is probably "to create a system which can be queried really quickly, where calculations including aggregation functions of teams/goals/etc can be performed very quickly to enable me to identify likely results or predict future outcomes based on a variety of factors, or to examine trends etc from the past".

If this is your eventual goal, you may want to keep your 3NF database above plus have it regularly export to another database, one which has dimension normalization - a data warehouse. Your stadium then becomes a Dimension table, probably expanded to "Location" with your results a Fact table. This Dimension table would contain values (which would often repeat) for all of the fields you have specified in Stadium. In fact, you could expand the "Location" table then to include weather information for the day, pitch condition, crowd attendance etc.

This approach has many advantages long term, but requires a new database (development) and an appropriate ETL process to load it. My experience tells me, you will eventually be building this if your database records historical information and you want to begin aggregating information for trend analysis. If you find yourself writing history tables for everything, you will find the interfaces for this difficult to maintain in your current database.

Option Three

Forget about anything that changes. Is it really important that you record the capacity on any day? If not, don't worry about the history of recording stadium capacity. Your database then only contains the most up to date stadium information.

This is the simplest option - it means you don't have to change your database design.


SSIS Package Validation Error when Executed From Windows Task Scheduler

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

SQL Job with one Job Step
The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

an instance-level Credential
a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -


How to resolve Geospatial Data over linked server causing error?

The "memory" being referred to in the term "memory pressure" is physical RAM, not hard-drive space.

What exactly are you attempting to do when you get this error? It seems that it could be due to attempting to invoke CLR code from within an sp_OA* OLE-Automation stored procedure call. That is documented in the following Microsoft forum thread: CLR initialization failed with hresult 0x80131022.

According to the following Microsoft KB article (# 2003681), it could be due to actual memory pressure: Possible causes of "Failed to initialize the Common Language Runtime (CLR)" when using SQL CLR. Memory pressure refers to running low on physical RAM as that is what SQL Server prefers to use. For doing stuff in SQLCLR, these types of errors were much more common prior to SQL Server 2012, especially on 32-bit systems. Are you on a 32-bit system?

Also, in this Microsoft forum thread (related to SQL Server 2008 R2) -- Failed to enter Common Language Runtime (CLR) with HRESULT 0x80131022. This may due to low resource conditions -- Bob Beauchemin states:

  You can increase the amount of memory available to SQLCLR on 64-bit systems by decreasing SQL Server's max_server_memory setting. You can monitor the amount of memory used by SQLCLR with a sum of sys.dm_os_memory_objects where type like '%CLR%' and the appropriate perfmon counters in the .NET CLR memory series.

For more information on SQL Server memory architecture (for SQL Server 2005, 2008, and 2008 R2), please see the following articles:

Basics of SQL Server Memory Architecture (by Karthick P.K)
Understanding the VAS Reservation (aka MemToLeave) in SQL Server (by Jonathan Kehayias)


Created Check Constraint WITH NOCHECK - still trusted

This might not be an accepted answer and I am ok with that but I cannot put the whole thing in a comment.

I tested your scenario with SQL Server 2016, SP2 and SQL Server 2017 CU9. In both cases I get expected result for is_not_trusted column value. Meaning 1 while using WITH NOCHECK and 0 while using `WITH CHECK'

Here is my test code. Some of the code I used is from Search Engine Q&A #9: How to update constraints? written by Paul Randal.

DROP DATABASE IF EXISTS checkConstraints;
CREATE DATABASE checkConstraints;
GO

USE [checkConstraints]
GO

DROP TABLE IF EXISTS dbo.Sales;
CREATE TABLE dbo.Sales (salesID INT IDENTITY, SalesDate DATETIME);
GO

CREATE CLUSTERED INDEX Sales_CL ON Sales (SalesID);
CREATE NONCLUSTERED INDEX Sales_NCL ON Sales (SalesDate);
GO

SET NOCOUNT ON;
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 1000)
BEGIN

INSERT INTO Sales VALUES (GETDATE ());
SELECT @a = @a + 1;

END;
GO

ALTER TABLE Sales DROP CONSTRAINT IF EXISTS  [CK_Sales_nocheck];
GO
ALTER TABLE Sales WITH NOCHECK ADD CONSTRAINT [CK_Sales_nocheck] CHECK (YEAR (SalesDate) >= 2005 AND YEAR (SalesDate) < 2008);
GO

ALTER TABLE Sales DROP CONSTRAINT IF EXISTS [CK_Sales_check];
GO
ALTER TABLE Sales WITH CHECK ADD CONSTRAINT [CK_Sales_check] CHECK (YEAR (SalesDate) >= 2005 AND YEAR (SalesDate) < 2019);
GO

SELECT name, is_not_trusted FROM sys.check_constraints;
GO

Result I get is following:


Is there harm in applying passwords to built-in Oracle Roles like DBA, Connect, etc

These are roles required to function as those roles.  However, users cannot just use those roles.  They are granted to users by a DBA as required.  What they should be auditing is who has the role and should they have them.  To do that they will need to understand what the purpose of the role is.

Many of the roles will be granted to few, if any, non-standard users. But some such as connect will be granted to almost all users.

It may help them if you document those roles granted to not non-standard users.  Include the reasons they would/should be granted to a user.  It is not uncommon to create local roles to simplify granting access.  Theses should also be documented and identified as local.  Having such a document should make dealing with the auditors easier as it will show that roles are being managed.


The built-in way to reduce backup size for VLDBs with read-only data is to parition your large tables, placing older partitions on read-only filegroups, and then take infrequent backups of the read-only filegroups (or whole database), combined with regular backups of the read-write filegroups.  Then you can perform a Piecemeal Restore.

  Will [using UNION ALL views over multiple databases] work ?

Yes, you can typically replace a table with a view without changing the application, unless the application does something unusual.  The risk is in the performance, as the UNION ALL view across multiple databases doesn't really perform the same.

You might just try using columnstore compression on the large tables in the archive database.


On 26 October 2014, following another change in the law, the clocks in most of the country were moved back one hour, but summer Daylight Time was not reintroduced; Moscow Time returned to UTC+03:00 permanently.

-- https://en.wikipedia.org/wiki/Time_in_Russia

It sounds like your system (OS and/or MySQL) need an update to the TZ tables.

Down with DST!


Changing data source on Access front end

You can convert your physical tables in the Access database to the linked tables. The linked table is just this: a link. The actual data is located elsewhere. For this to work you will need to create a connection to the MySQL database supported by Access. Access supports OLEDB and ODBC connections. Download a MySQL OLEDB or ODBC driver, create a DSN (if you chose the ODBC path). Open your Access database and click on the External Data toolbar. Select "Create Link" and then create links to all your data that you have exported to MySQL. The linked tables will be created alongside your existing tables. You can then try deleting the physical tables one-by-one and renaming the matching linked tables, so that the names are the same. You can test the application step-by-steps after each table.

BTW, you could use Access to export data via the driver as well. It would export it together with the indices


There is still a potential for injection here. Let's assume that:

someone has the ability to create tables in this database, but not drop them
that someone has the ability to call this procedure
the procedure runs as an elevated user

That person could call this procedure to get results from foo, but in the meantime create a table called [foo;drop table bar], then pass foo;drop table bar in as the parameter value. It would pass your check, then you would blindly execute it. They get the results from foo but they also drop table bar.

Now, that is a pretty contrived scenario, and you probably aren't worried about internal people dropping tables, but they could also use this to exploit data they don't have access to, for example they could create a table called [foo;SELECT name,salary FROM dbo.Employees;] and end up with two result sets, one with data you probably shouldn't be letting them see.

There is also a potential for getting data from the wrong table, in the event that there are tables with the same names in different schemas. Always, always, always use the schema prefix when creating or referencing objects:

Bad habits to kick : Avoiding the schema prefix

Finally, don't use INFORMATION_SCHEMA. The catalog views and metadata functions are much more complete, current and reliable:

The case against INFORMATION_SCHEMA views

I would probably do it this way (and I'm going to assume for simplicity that all of your tables are in dbo):

DECLARE @table SYSNAME; -- procedure parameter, right?

DECLARE @sql NVARCHAR(MAX);
IF OBJECT_ID(N'dbo.' + QUOTENAME(@table), N'U') IS NOT NULL
BEGIN
  SET @sql = N'SELECT * FROM dbo.' + QUOTENAME(@table) + ';';
  EXEC sp_executesql @sql;
END

This way, even if they pass foo;drop table bar or foo;select name,salary from dbo.employees as the table name, that whole string gets surrounded in square brackets, thus the worst thing that can happen is they'll get the results from the table they created, rather than any other table or worse.

And I would ensure that the procedure runs as that user, rather than escalate using EXECUTE AS (a common bypass for proper permissions configuration). Also see:

Bad habits to kick : Using SELECT *


SQL Server Management Studio: The columns in table do not match an existing primary key or unique constraint

I think you want a junction table. I have no idea what the point-and-click UI is doing behind the scenes or how you are getting into this situation but I think this makes more sense:

CREATE TABLE dbo.Students
(
  StudentID INT PRIMARY KEY,
  ... Other columns ...
);

CREATE TABLE dbo.Courses
(
  CourseID INT PRIMARY KEY,
  ... Other columns ...
);

CREATE TABLE dbo.StudentCourses
(
  StudentID INT NOT NULL FOREIGN KEY
      REFERENCES dbo.Students(StudentID),
  CourseID INT NOT NULL FOREIGN KEY
      REFERENCES dbo.Courses(CourseID),
  PRIMARY KEY(StudentID, CourseID)
);

Now, I am not entirely clear why one of your images shows SemesterID as part of the key for one of the tables; I don't know enough about the business logic and how you define courses to guess where that should be. But for the student-course relationship, again unless I have a completely bizarre definition of "course," should not be declared in the Students table.

Also, I wouldn't use the CHAR data type for strings that will clearly have different lengths. You should use varchar (or maybe nvarchar, depending on location and your school's educational domain) for course name, and nvarchar for proper names.


Changed MyISAM to InnoDB, huge performance loss

You said in your question

  I've read if you have a lot of INSERTS / UPDATES with big data InnoDB is way better then MyISAM. I'm not sure what I'm missing here.

Your server class is too small for your InnoDB settings.

On Apr 14, 2011, I mentioned how the storage engines have distinct ways of caching.

MyISAM will only cache indexes in RAM. Data is always read from disk.
InnoDB caches data and indexes in the InnoDB Buffer Pool

I also noted how your innodb_read_io_threads and innodb_write_io_threads are maxed out. You only have 4GB of RAM or which you only have 1GB free (due to your 3GB or 3072M Buffer Pool). I would lower your threads from 64 to 8.

If you have the budget, you should go upgrade your server class to m3.large or m3.xlarge where you can have 4 CPU and 15 or 16G of RAM. You could then increase innodb_buffer_pool_size to 10G or 12G. Then, your remaining InnoDB settings would make sense. I would still lower your threads from 64 to 8.


This will add a random number of days to 1st of January, 1900:

SELECT DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');

According to the Microsoft Docs, CRYPT_GEN_RANDOM "returns a cryptographic random number generated by the Crypto API (CAPI). The output is a hexadecimal number of the specified number of bytes."

So CRYPT_GEN_RANDOM(2) returns a two-byte number in the range of 0x0000 to 0xFFFF, when converted into a signed-integer and "added" to 1900-01-01, will result in dates in the range of 1900-01-01 to 2079-06-06.

For a table named dbo.MyTable, with a column named [Date of Birth], this will update all column values to randomly generated dates:

UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');

One could reverse the logic such that you have people of various ages from 0 days old to approximately 59 years old with this:

UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY, (1 - CONVERT(int, CRYPT_GEN_RANDOM(2)) / 3), GETDATE());

The following example will randomly choose birth dates resulting in ages between 10 and 20 years old:

DECLARE @MinAge int;
DECLARE @MaxAge int;

SET @MinAge = 10;
SET @MaxAge = 20;
UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY
    , (1 - (CONVERT(int, CRYPT_GEN_RANDOM(2)) % ((@MaxAge - @MinAge) * 365)))
    , CONVERT(date, DATEADD(YEAR, 1 - @MinAge, GETDATE()))
    );


It seems to be some old limitation of the optimizer maybe, aliases to aggregating expressions are not allowed in more complex expressions in ORDER BY.

You can get around it by using the full expression instead of the alias:

ORDER BY
    COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END)
  *
    AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END)


Installing SQL Server 2017, I get E: Unable to locate package mssql-server

Normally, a distro packages things in a specific format, so for instance adding a repository like

deb [arch=amd64] https://packages.microsoft.com/ubuntu/17.10/prod zesty main

Will tell apt to download a package list and look for the packages here,

https://packages.microsoft.com/ubuntu/17.10/prod/pool/

However, with Microsoft that's not the case. They have a repository for 17.10 (as they should) which has all their packages for Ubuntu, but they have another repository specifically for SQL Server. To make matters more confusing, that SQL Server repository is in the tree of 16.04.

So they have both (pay attention to the /pool/s)

https://packages.microsoft.com/ubuntu/16.04/mssql-server/pool/main/m/
https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/

You need the 16.04/mssql-server repository. You can add that with

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

You may think because you have the 17.10/prod and you have SQL Server installed that you don't need the 16.04/prod repository. That's incorrect, you'll need that too if you want the SQL Client sqlcmd provided by mssql-tools

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"

I did file a doc bug upstream with a description of the confusion so maybe they'll fix it someday

If you find your system in an inconsistent state for any reason, you can always remove SQL Server and start over.


Your PHP MySQL library version 5.5.44 differs from your MySQL server version 10.0.20. This may cause unpredictable behavior

I just fixed the problem:

apt-get remove php5-mysql
apt-get install php5-mysqlnd

I had to reinstall phpmyadmin, the errors disappeared now.


MySQL: Show Unique key definition by name

This should do it:

select table_schema, table_name, column_name
from information_schema.key_column_usage
where constraint_name = 'UNQ_CAT_CTGR_ENTT_DTIME_ENTT_TYPE_ID_ENTT_ID_ATTR_ID_STORE_ID'
order by ordinal_position


SQL Server's memory manager is designed to keep as much data cached in memory as it can in order to speed up access for incoming queries.

In order to work successfully with other processes on the machine,
SQL Server will release memory when a low- memory notification is
sent by Windows.
The memory may be committed by the SQL Server process, but it will be
released if another application requests enough to trip the
low-memory notification.

Before you proceed with setting up the MAX and MIN server memory config setting would recommend you to go through this great write up by Jonathan How much memory does my SQL Server actually need?

  Ultimately what I require is the OS utilization should not go above
  90% which is why I kept the max memory to 28GB

If you are changing above just to settle down the threshold, sounds may not be the perfect reason you should be cutting down the memory what SQL needs.

However, the first thing you will need to analyse what process that are running at OS level and for how long that needs the amount of memory you are looking for, because SQL will use what has been assigned.

Baseline the server accordingly and see if you really face OS memory pressure:

Also see here  and here.

In addition there is this excellent article from Shanky : SQL Server Memory and Troubleshooting which should get you going.


Cassandra change replication factor hive datastore

By Default cql will convert the table/keyspace/column name in lowercase.
If you have table/keyspace/column in uppercase you have to enclose it in double quotes.

alter KEYSPACE "HiveMetaStore" with replication =
    {'class': 'NetworkTopologyStrategy', 'DCassandra1': 1};

Opscenter handled it for you:)

CQL UpperCase/lowercase


Does ActualLogicalReads info in query execution plan reports reads generated by scalar udf?

Like Martin said, this is easy enough to test out. Let's use the Stack Overflow data dump as an example. If you want to download your own copy, head over here.

If we hit the Badges table with a scalar valued function with this definition:

CREATE FUNCTION dbo.ScalarFunction ( @uid INT )
RETURNS BIGINT
    WITH RETURNS NULL ON NULL INPUT,
         SCHEMABINDING
AS
    BEGIN
        DECLARE @BCount BIGINT;
        SELECT  @BCount = COUNT_BIG(*)
        FROM    dbo.Badges AS b
        WHERE   b.UserId = @uid
        GROUP BY b.UserId;
        RETURN @BCount;
    END;
GO

And then run a query that uses the scalar valued function:

SELECT TOP 1000 u.DisplayName,
       dbo.ScalarFunction(u.Id)
FROM   dbo.Users AS u;

The query plan looks like this.

And the STATS IO output looks like this:

Table 'Users'. Scan count 1, logical reads 76, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And the plan XML looks the same:

 <RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualRowsRead="1000" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="76" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />

So even though we know our function hits the Badges table and does a bunch of work, none of it registers either in STATS IO or in the query plan. Scalar valued functions (and to a large degree MSTVFs) continue to black out performance counters in modern versions of SQL Server.

To compare, if we just run the function body like this for a single UserId

SELECT  COUNT_BIG(*)
FROM    dbo.Badges AS b
WHERE   b.UserId = 22656
GROUP BY b.UserId;

The STATS IO output looks like this.

Table 'Badges'. Scan count 7, logical reads 107872, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The plan looks like this.

And the plan XML looks like this.

 <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="1" ActualRows="2291" ActualRowsRead="2835393" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="2" ActualRows="2193" ActualRowsRead="2839900" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="94" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="3" ActualRows="2330" ActualRowsRead="2884732" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="94" ActualCPUms="94" ActualScans="1" ActualLogicalReads="18084" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="4" ActualRows="2134" ActualRowsRead="2835817" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="5" ActualRows="2180" ActualRowsRead="2846565" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="6" ActualRows="2472" ActualRowsRead="2964762" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="95" ActualCPUms="95" ActualScans="1" ActualLogicalReads="18546" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />

Since we know that scalar valued functions run once per row, we can safely assume that none of the IO used by the scalar function show up in the query plan for the calling query. This goes for CPU and other resources, as well.


SQL - Find top rows where parameter is the same

You need to calculate the rank per player. You are currently calculating the overall rank.

To do it per player, you need to add a partition by to your window function:

rank() over (PARTITION BY player_name_fkey order by score desc) AS scorerank

You also need to remove the group by from the inner (t1) query:

SELECT player_name_fkey,
       school_name_fkey,
       sum(t1.score) AS sum_scores
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         score,
         rank() over (PARTITION BY player_name_fkey order by score desc) AS scorerank
  FROM simple
    LEFT JOIN players ON players.name = simple.player_name_fkey
  WHERE category = 'Benjamin'
    AND gender = 'Masculin'
) t1
WHERE scorerank <= 2
GROUP BY player_name_fkey,
         school_name_fkey


Paritioning performance for full table scan on non-paritioned index

instead of scanning one large index the database will scan multiple smaller tables in parallel.

PostgreSQL does not currently support parallel query (though hopefully it isn't far away), so it will be digging through your partitions essentially one-at-a-time if you are querying based on something other than the exclusion constraint for your partitions.

It'll be up to you to test whether querying N small indexes works out faster than querying one large one for your particular use-case. FWIW, folk wisdom says probably not, but YMMV,  in particular you may do a better job vacuuming and keeping bloat down on several small partitions and indexes than with one giant table plus index(es), for instance.

See this wiki page for a starting point about the state of Parallel Query in Postgres and workarounds.


Making a new database onto existing AWS RDS instance

CREATE DATABASE db2;

There does not have to be a different user for each database.  But you might want to do it if you want to keep, say, two applications, each using one of the databases, from accidentally stepping on each other.

GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'host-name' IDENTIFIED BY 'password1';
GRANT ALL PRIVILEGES ON db2.* TO 'user2'@'host-name' IDENTIFIED BY 'password2';

Usually I have a separate user for each database; sometimes I have a user that touches multiple databases.

(This answer applies to MySQL and MariaDB in general.)


Have a look at MS Docs about POWER function:

  Return Types
  Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).

CAST left operand as DECIMAL(18, NumDecimals)

-- without CAST

SELECT POWER(1 + (52/100.0) * 0.23, 0.268);
GO

| (No column name) |
| :--------------- |
| 1.03073935       |

-- CAST as DECIMAL(18,12)

SELECT POWER(CAST(1 + (52/100.0) * 0.23 AS DECIMAL(18,12)), 0.268);
GO

| (No column name) |
| :--------------- |
| 1.030739345889   |

dbfiddle here


SQL Query to compare (HL7) value at certain position in string based on delimiters (not position)

This works

SELECT *
FROM
       TABLE
WHERE
       $PIECE($PIECE(HL7Message,'|',4),'^',1) = 'This is the substring I want'

or to get Sendingfacilities based on other criteria

SELECT $PIECE($PIECE(HL7Message,'|',4),'^',1) as SendingFacility
FROM TABLE
WHERE <whatever>


Missing mysqld.sock file on linux server

It should not install mysqld.sock. This file is a unix socket, it doesn't refer to a byte stream on your hard disk.

It is more like a TCP port: programs can listen here, and other programs can connect it to communicate with it. The only significant different is that TCP ports are identified by a number between 1 and 65535. Unix sockets are identified by their path, like /var/run/mysql/mysqld.sock.

mysqld.sock is created by the mysql daemon on start, and it is removed (or, becomes un-connectable) if this daemon stops.

This message is given by some client process, trying to connect your mysqld on this socket without success.

Its most likely reason is that the mysqld doesn't run.

Why it doesn't run? Try to start it (systemctl start mysqld) and check the /var/log/mysql* .


How do I list all schemas in PostgreSQL?

To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA

select schema_name
from information_schema.schemata;

More details in the manual

alternatively:

select nspname
from pg_catalog.pg_namespace;

More details about pg_catalog in the manual


Creating a Friendships database that includes a Block Friend option

I would cover the “Block Friend” requirement introducing the concept of Friendship Status to the system—where “Blocked” is only one of the possible instances, since a Friendship may be also be, e.g., “Accepted” or “Declined”—, as I will detail as follows.

Business rules

When modelling a relational database, I highly recommend (a) identifying and (b) formulating all the rules that apply in the business environment with respect to the informational characteristics of relevance, and do so before (c) creating tables and declaring constraints. In this way you can delineate the corresponding conceptual schema with the needed precision, and then the logical-level declarations are much more easy and natural.

So let us start with some basic expository rules of a Friendships system:

A User is the Requester of zero-one-or-many Friendships
A User is the Addressee of zero-one-or-many Friendships
A Friendship is primarily identified by the combination of its RequesterId and its AddresseeId

Then the Status aspect comes into play:

A Friendship holds one-to-many FriendshipStatuses
A FriendshipStatus is primarily identified by the combination of its RequesterId, its AddresseeId and its SpecifiedDateTime
A User specifies zero-one-or-many FriendshipStatuses
A Status classifies zero-one-or-many FriendshipStatuses
A Status is primarily identified by its StatusCode
A Status is alternately identified by its Name

Expository IDEF1X diagram

After having formulated all the business rules with accuracy, one can then draw an IDEF1Xa diagram like the one expounded in Figure 1 to supply a graphical communication device:

As shown, Requester and Addressee are labels that indicate the specific Role performed by each User taking part in a given Friendship.

The Friendship entity type depicts an association with a many-to-many (M:N) cardinality ratio that can involve distinct occurrences of the same entity type, i.e., User. This is an example of the classic conceptual construct known as “Bill of Materials” or “Parts Explosion”.

Status and Friendship are connected by way of the FriendshipStatus entity type, which portrays what may become a sequence of Status specifications for the involved Friendship instances.

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable modelling technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

Illustrative logical SQL-DDL design

Subsequently, I created the following logical-level arrangement in order to represent the conceptual aspects previously formulated (naturally, you will have to adapt it to your exact needs, as it may entail, e.g., several constraints not considered nor declared here and a different quantity of columns):

-- You should determine which are the most fitting
-- data types and sizes for all the table columns
-- depending on your business context characteristics.

-- At the physical level, you should make accurate tests
-- to define the mostconvenient INDEX strategies based on
-- the pertinent query tendencies.

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

CREATE TABLE UserProfile ( -- Represents an independent entity type.
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- Single-column ALTERNATE KEY.
);

CREATE TABLE Friendship ( -- Stands for an associative entity type.
    RequesterId     INT      NOT NULL,
    AddresseeId     INT      NOT NULL, -- Fixed with a well-delimited data type.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId)
);

--
CREATE TABLE MyStatus ( -- Denotes an independent entity type.
    StatusCode CHAR(1)  NOT NULL,
    Name       CHAR(30) NOT NULL,
    --
    CONSTRAINT MyStatus_PK PRIMARY KEY (StatusCode),
    CONSTRAINT MyStatus_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE FriendshipStatus ( -- Represents an associative entity type.
    RequesterId       INT      NOT NULL,
    AddresseeId       INT      NOT NULL,
    SpecifiedDateTime DATETIME NOT NULL,
    StatusCode        CHAR(1)  NOT NULL,
    SpecifierId       INT      NOT NULL,
    --
    CONSTRAINT FriendshipStatus_PK             PRIMARY KEY (RequesterId, AddresseeId, SpecifiedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipStatusToFriendship_FK FOREIGN KEY (RequesterId, AddresseeId)
        REFERENCES Friendship  (RequesterId, AddresseeId), -- Composite FOREIGN KEY.
    CONSTRAINT FriendshipStatusToMyStatus_FK   FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipStatusToSpecifier_FK  FOREIGN KEY (SpecifierId)
        REFERENCES UserProfile (UserId)
);

Tested on MySQL 5.6 in this SQL Fiddle.

A Friendship row and its earliest FriendshipStatus related counterpart

With this arrangement, when a User requests a Friendship, the corresponding Friendship row has to be inserted along with the complementary FriendshipStatus counterpart, which in turn must contain a value of 'R' in StatusCode, denoting the fact that a User has simply requested the Friendship, and is waiting for the Addressee to accept it ('A'), decline it ('D'), or leave it as requested.

As usual, I would decidedly set up the multi-row operations that have to be executed as a single Unit of Work with the aid of ACID TRANSACTIONS. Needless to say, the assistance of the application program(s) component(s) accessing the database would as well be very helpful to facilitate the processing of the Friendships.

Later FriendshipStatus rows

Subsequently, every time that the Status of a certain Friendship has to be put up-to-date, the Users would only have to INSERT a new FriendshipStatus row, containing:

the respective RequesterId and AddresseeId values —taken from the concerning Friendship row—;
the new and meaningful StatusCode value —drawn from MyStatus.StatusCode—;
the value of the exact INSERTion instant, i.e., SpecifiedDateTime —preferably using a server function so that you can retrieve and retain it in a reliable manner—; and
the SpecifierId value that would indicate the respective UserId that entered the new FriendshipStatus into the system.

Sample data

To that extent, let us suppose that the MyStatus table, serving a “look-up” role, encloses the following data —with PK values that are (1) end user-, app programmer-, and DBA-friendly and (2) small and fast in terms of bytes at the physical implementation level—:

 +-——————————-+-—————————-+
 | StatusCode | Name      |
 +-——————————-+-—————————-+
 | R          | Requested |
 +------------+-----------+
 | A          | Accepted  |
 +------------+-----------+
 | D          | Declined  |
 +------------+-----------+
 | B          | Blocked   |
 +------------+-----------+

So, the FriendshipStatus table may hold data like shown below:

 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 | RequesterId | AddresseeId | SpecifiedDateTime       | StatusCode | SpecifierId |
 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 |        1750 |        1748 | 2018-04-01 16:58:12.000 | R          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-02 09:12:05.000 | A          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-04 10:57:01.000 | B          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-07 07:33:08.000 | R          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-08 12:12:09.000 | A          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+

It can be said that the FriendshipStatus table serves the purpose of comprising a time series, which stands for the evolution of the Friendships of significance.

You can decidedly declare one or more views that comprise columns from several tables to facilitate, e.g., writing code for data retrieval.

Blocking a Friend

As demonstrated in the sample data of the FriendshipStatus table, having a row that holds a value of 'B' in the StatusCode column would represent that some User decided to block one of his or her Friends.

And, yes, you can use your application program(s) component(s) to access the database and check the latest FriendshipStatus row (via MAX(SpecifiedDateTime)) for a given (RequesterId, AddresseId) combination of values to control the flow of the applicable processes of the system.

Other option: Retaining only the most recent Status

In case that, for whatever reason, you have to avoid retaining the FriendShipStatus history in the fashion previously described, you can discard the creation of a FriendshipStatus table and simply set up Friendship so that it includes a FOREIGN KEY constraint making a reference to Status.StatusCode directly:

CREATE TABLE Friendship (
    RequesterId    INT      NOT NULL,
    AddresseeId    INT      NOT NULL,
    CratedDateTime DATETIME NOT NULL,
    StatusCode     CHAR(1)  NOT NULL,
    CreatorId      INT      NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId),
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId)
    CONSTRAINT FriendshipToMyStatus_FK  FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipToCreator_FK   FOREIGN KEY (CreatorId)
        REFERENCES UserProfile (UserId)
);

Tested on MySQL 5.6 in this SQL Fiddle.

In this manner, (i) the INSERT of a certain Friendship row goes with a value of 'R' in the StatusCode column, and each time that the Status of a Friendship is modified you just have to (ii) UPDATE the values in StatusCode, SpecifiedDateTime and CreatorId —which must enclose the identifier value of the latest User who specified the Status of the concerning Friendship—.

Related posts

Various ideas presented in this post are based on this Q & A, where I propose a database design that meets the informational requirements of a similar business environment. I recommend reading those posts because, although the focus of that question is different from yours, that answer is more detailed and contains several points of relevance.


SQL Server cluster resource won't come online unless its on port 1433

A default instance install (instance name of MSSQLSERVER and on port 1433) will disable the SQL Browser service because 1433 is assumed.  On a clustered SQL install the cluster service will try to connect tot he instance and run a "select @@servername" in order to show that the server is up, but because it's a default instance it tries to use 1433 by default.  I've tried to re-enable the SQL Browser service after changing the port so that the mapping of instance:port happens but haven't had any luck.

What you can try is to set up a client side alias (using SQL Server Configuration Manager) on each node with the virtual SQL name of your default install (the network name) and then put the port number in the alias so that SQLVIRTUAL points to SQLVIRTUAL,3876 or whatever port you are trying to change to.  This way when the cluster service tries to poll the instance to see if it's up on 1433 it will be redirected to the actual port by the alias.  You will not be able to use a dynamic port for this.


Why restoring a large sql file via SSH seems to quit partially through the process

Either the problem is with MySQL connections being killed silently, in which case please set:

[mysqld]
net_read_timeout = 7200
net_write_timeout = 7200

Defaults are extremely low (30 seconds). These params set the number of seconds waiting upon a command before aborting a connection.

Or the problem is as you suggest with your SSH. In which case use screen. If you don't know about screen, you are missing on a major tool. In very brief, it splits your shell, and allows you to close the terminal without breaking ongoing processes. It allows you to re-connect at another time or place, share your connection with others, and more.


Compare ID and if true update another table

I propose implementing a different and more versatile database structure, because the addition of a (base) table that retains persons or patients that are affected by only one particular disease introduces update anomalies and is unnecessary.
Business rules
I consider that the following business rules formulations are of prime importance:

A Person is affected by zero-one-or-many Diseases
A Disease affects one-to-many Persons

Therefore, you want to implement a common many-to-many (M:N) relationship.
Illustrative logical model
Then, from such formulations, I have derived an IDEF1X1 logical model that is shown in Figure 1:

As demonstrated, it is very important to define the entity types that are involved in the business domain under consideration, their attributes and how they are interrelated (including the cardinality of the relevant relationships).
In this case  Person and  Disease are independent entity types, each with their own attributes, and they are interrelated by way of an associative entity type, that I called PersonDisease, which holds the attributes that pertain to the relationship that takes place between the two mentioned entity types.
Each PersonDisease occurrence (or row once it is stored in a SQL database) is uniquely identified by PersonId along with DiseaseNumber, so I fixed this combination of attributes as the PRIMARY KEY of this associative entity type.
Derivable data
This model indicates that the Table B included in your answer is derivable data (obtained via DML operations that combine or compute columns from multiple tables), and not an entity type, neither independent nor associative; therefore you should not set it up as a base table.
Expository implementation
Consequently, I created the following DDL structure in terms of the logical model discussed above:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should carry out some testing sessions to define
-- the most convenient INDEXing strategies.

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

CREATE TABLE Person
(
    PersonId        INT       NOT NULL,
    FirstName       CHAR(30)  NOT NULL,
    LastName        CHAR(30)  NOT NULL,
    GenderCode      CHAR(3)   NOT NULL,
    BirthDate       DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (PersonId),
    CONSTRAINT AK_Person_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    )
);

CREATE TABLE Disease
(
    DiseaseNumber    INT       NOT NULL,
    Name             CHAR(30)  NOT NULL,
    ParticularColumn CHAR(30)  NOT NULL,
    SpecificColumn   CHAR(30)  NOT NULL,
    Etcetera         CHAR(30)  NOT NULL,
    CreatedDateTime  TIMESTAMP NOT NULL,
    CONSTRAINT PK_Disease      PRIMARY KEY (DiseaseNumber),
    CONSTRAINT AK_Disease_Name UNIQUE      (Name)-- ALTERNATE KEY.
);

CREATE TABLE PersonDisease -- Associative table.
(
    PersonId        INT       NOT NULL,
    DiseaseNumber   INT       NOT NULL,
    DiagnosedDate   DATE      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    CONSTRAINT PK_PersonDisease                 PRIMARY KEY (PersonId, DiseaseNumber), -- Composite PRIMARY KEY.
    CONSTRAINT FK_from_PersonDisease_to_Person  FOREIGN KEY (PersonId)
        REFERENCES Person  (PersonId),
    CONSTRAINT FK_from_PersonDisease_to_Disease FOREIGN KEY (DiseaseNumber)
        REFERENCES Disease (DiseaseNumber)
);

This structure permits

storing n Disease instances (via simple INSERT operations) and
storing the relationship between n Person instances with n Disease instances.
managing Disease data independently of that which pertains to Person.

And avoids

employing ad hoc procedures that have to be carried out due to the update anomalies introduced by an additional disease-specific table.

Sample data
Let us suposse that the relevant database holds the data that follows:
-- 'Person' sample data.
INSERT INTO
    person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES
    (1, 'David', 'Smith', 'M', CURRENT_DATE, '20161031 13:14:02');

INSERT INTO
    Person (PersonId, FirstName, LastName, GenderCode, BirthDate, CreatedDateTime)
VALUES
    (2, 'Nicole', 'Johnson', 'F', CURRENT_DATE, '20161031 13:14:02');

-- 'Disease' sample data.
INSERT INTO
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
   (1, 'Disease A', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
    (2, 'Disease B', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
    (3, 'Disease C', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

INSERT INTO
    Disease (DiseaseNumber, Name, ParticularColumn, SpecificColumn, Etcetera, CreatedDateTime)
VALUES
    (4, 'Disease D', 'Particular test', 'Specific test', 'Etcetera test', '20161031 13:14:02');

-- 'PersonDisease' sample data.

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
   (1, 1, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
    (1, 2, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
    (2, 1, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
   (2, 2, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
    (2, 3, '20161031', '20161031 13:14:02');

INSERT INTO
    PersonDisease (PersonId, DiseaseNumber, DiagnosedDate, CreatedDateTime)
VALUES
(2, 4, '20161031', '20161031 13:14:02');

So you might like to retrieve all the diseases by which all the persons are affected via, e.g., the statement displayed bellow:
SELECT Person.FirstName            AS "Patient first name",
       Person.LastName             AS "Patient last name",
       Disease.Name                AS "Disease name",
       PersonDisease.DiagnosedDate AS "Diagnosed date"
  FROM PersonDisease
  JOIN Disease
    ON Disease.DiseaseNumber = PersonDisease.DiseaseNumber
  JOIN Person
    ON Person.PersonId       = PersonDisease.PersonId;

As is also exemplified in this SQL Fiddle.
Inspecting all the other diseases suffered by persons that have heart disease
Let us assume that heart disease is primarily identified by disease number 3, so you can construct a SELECT statement like, e.g., the following one to see what other diseases are suffered by people with heart disease:
SELECT PersonDisease.PersonId,
       Person.FirstName,
       Person.LastName,
       PersonDisease.DiseaseNumber,
       Disease.Name
  FROM PersonDisease
  JOIN Person
    ON Person.PersonId              = PersonDisease.PersonId
  JOIN Disease
    ON Disease.DiseaseNumber        = PersonDisease.DiseaseNumber
 WHERE Person.PersonId              IN (SELECT PersonId
                                          FROM PersonDisease
                                         WHERE DiseaseNumber = 3)
   AND PersonDisease.DiseaseNumber <> 3;

You can review a comparable method in action in this new SQL Fiddle.
Of course, this and the query discussed in the preceding section can be defined as VIEWs so that you can SELECT data directly FROM them, if necessary.
There might well be other approaches to solve this requirement like, e.g., comparing the data stored in PersonDisease with a VIEW that includes only the persons who have heart disease, or a CTE as suggested by @Joishi Bodio in their answer. Some of them could be served by faster execution plans than the ones that correspond to the other approaches, so you should naturally make some tests and implement a reliable INDEXing strategy.
Using the CROSSTAB() Function
It appears that you want to construct some kind of PIVOT query, so you may find of help this Stack Overflow answer in which @Erwin Brandsteter details several usages of the CROSSTAB() function that, according to the PostgreSQL documentation, is included in the  tablefunc module.
Answer focus
The objective should be, as discussed above, to avoid appending the superfluous disease-specific base table.

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


Paginated SqlServer Query with Changing Data

Given that each row in the result set has a unique PK aside from the ROW_NUMBER() value, it seems like it might be possible to ask "give me the next 20 rows starting with this PK" to avoid shifting ROW_NUMBER() values, but I can't see a way of expressing this.

I'm assuming that your clustered index is the same thing as your primary key. This is possible to do in SQL Server but the syntax isn't the most intuitive. Your code will look more complicated as the number of primary key columns increases. The basic technique that you want is described here.

I'm going to use the following table with a two column primary key as an example:

CREATE TABLE your_table (
    PK_COL1 INT NOT NULL,
    PK_COL2 INT NOT NULL,
    OTHER_COLUMN VARCHAR(100),
    PRIMARY KEY (PK_COL1, PK_COL2)
);

Consider the following query:

SELECT TOP (20) t.PK_COL1, t.PK_COL2, t.OTHER_COLUMN
FROM your_table t
WHERE (t.PK_COL1 = @id1 AND t.PK_COL2 > @id2) OR t.PK_COL1 > @id1
ORDER BY t.PK_COL1 ASC, t.PK_COL2 ASC;

That will very efficiently give you the next 20 rows following the last primary key that you've already processed in the app. All that you need to do is get 20 rows, keep track of the latest primary key that you processed in the app, then use the latest primary key to set @id1 and @id2 to get the next 20 rows. Since you're using the primary key as an anchor it shouldn't matter if a new row is inserted in the middle of data that you've already processed.

We can throw a little data into the table for a demo:

INSERT INTO your_table WITH (TABLOCK)
SELECT
t.RN / 16
, 1 + t.RN % 16
, REPLICATE('Z', 100)
FROM
(
    SELECT TOP (10000) -1 +
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN  master..spt_values t2
) t
OPTION (MAXDOP 1);

Let's say that we've processed up to the row with PK_COL1 of 10 and PK_COL2 of 3:

DECLARE @id1 INT = 10;
DECLARE @id2 INT = 3;

SELECT TOP (20) t.PK_COL1, t.PK_COL2, t.OTHER_COLUMN
FROM your_table t
WHERE (t.PK_COL1 = @id1 AND t.PK_COL2 > @id2) OR t.PK_COL1 > @id1
ORDER BY t.PK_COL1 ASC, t.PK_COL2 ASC;

I get a very fast clustered index seek:

STATISTICS IO results:

  Table 'your_table'. Scan count 2, logical reads 4

The result set is as follows:

╔═════════╦═════════╗
║ PK_COL1 ║ PK_COL2 ║
╠═════════╬═════════╣
║      10 ║       4 ║
║      10 ║       5 ║
║      10 ║       6 ║
║      10 ║       7 ║
║      10 ║       8 ║
║      10 ║       9 ║
║      10 ║      10 ║
║      10 ║      11 ║
║      10 ║      12 ║
║      10 ║      13 ║
║      10 ║      14 ║
║      10 ║      15 ║
║      10 ║      16 ║
║      11 ║       1 ║
║      11 ║       2 ║
║      11 ║       3 ║
║      11 ║       4 ║
║      11 ║       5 ║
║      11 ║       6 ║
║      11 ║       7 ║
╚═════════╩═════════╝

For the next batch, you would use @id1 INT = 11 and @id2 = 7.


"set-variable = innodb_thread_concurrency=4" or "innodb_thread_concurrency=4"

MySQL works fine without the set-variable in the my.cnf file


How to minimize log operations in SQL Server to avoid "log full" error

•On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE & not for SELECT statement in any case. Am I correct?

Select statement as such is not logged as compared to DML statements like insert , update and delete. If you see output of fn_dblog for select statement it wont have any entry. But there would be numerous entries for DML statements. Please note. The transaction log is maintained and information related to transaction is written there so that during crash recovery or recovery SQL Server can know what transactions were committed and what were uncommitted and reading this it can bring database to consistent state THIS IS WHY log is so much necessary. Without transaction log recovery is not possible

For all practical purposes every transaction is logged in SQL Server. So YOU CANNOT have a scenario where you run a transaction and nothing is logged in transaction log. You have no option to disable logging in SQL Server. I suggest you to read Logging and recovery in SQL Server.

  •How can I minimize logging while inserting updating records? I already have SIMPLE RECOVERY MODE

You are mistaken that logging does not happens in simple recovery model it does happen but moment transaction commits the transaction log is truncated and space used by logs generated for transaction is reutalized. As a fact logging in simple recovery is almost same as full recovery and difference lies when log truncation happens. Yes you can have minimal logging in Bulk Logged recovery model for only certain commands. For rest commands even in bulk logged recovery model logging would be full.

  •I have found that average throughput during this period went from 25 MB approx. to 1.2 MB. What does it mean?

I cannot understand this can you please explain it.

  •Is there any other way to troubleshoot this issue, other than increasing disk space?

As already suggested you are most likely to face issue if you keep @SET_SIZE INT = 500000000(even though its dummy) why such a high limit. Can you decrease it make it to 500000 and see how log file behaves. Make sure auotgrowth of log file is in MB and set to some sensible value. Do transaction in batches. Finally if you read Why transaction log keeps growing or runs out of space you can get some good idea.

  •If only option is to shrink a file, then when should I do it? Can I do it when any active transaction is ON? [Production environment], for guiding me but I need to release space. I have a large Data warehouse which deal with millions of data everyday

Please don't shrink any, data or log file. I would not recommend it. If you really want to shrink ONCE to reclaim space and you have no option other than shrinking you might as well try it. But again remember you have to rebuild all fragmented indexes after you are done with shrinking. You can try method given By Paul in This Article which says not to shrink and gives alternative of shrinking.

You can shrink log file only once but please remember log file grew because you ran transaction which forced it to grow so its basically your mistake not transaction log file. Instead of this whole process of growing and shrinking why not preallocate some amount of space to log file so that it can avoid autogrowth events

Another thing

The above configuration is very bad. 1 MB autogrowth for data file is bound to cause issues. To set correct value please read the article Autogrowth settings. This will help you calculate correct autogrowth setting.


In PostGIS, how do I swap the lat/log of invalid points, like MySQL ST_SwapXY?

With PostGIS you would use the accessors ST_X() and ST_Y(), and recreate the point with a constructor like ST_MakePoint(x,y) for example:

SELECT ST_AsText(geom) AS old, ST_AsText(newgeom) AS new
FROM ( VALUES (ST_MakePoint(0,1)) ) AS t(geom)
CROSS JOIN LATERAL
  ST_SetSRID(  ST_MakePoint(ST_Y(geom),ST_X(geom)), ST_SRID(geom)  )
AS t2(newgeom);
    old     |    new
------------+------------
 POINT(0 1) | POINT(1 0)
(1 row)

You can create a simple function _ST_SwapXY to accomplish this on points.

CREATE FUNCTION _ST_SwapXY(geom geometry(point))
RETURNS geometry(point) AS $$
  SELECT ST_SetSRID(  ST_MakePoint(ST_Y(geom),ST_X(geom)), ST_SRID(geom)  );
$$ LANGUAGE sql
IMMUTABLE;

CREATE FUNCTION _ST_SwapXY(geog geography(point))
RETURNS geography(point) AS $$
  SELECT ST_SetSRID(  ST_MakePoint(ST_Y(geog::geometry),ST_X(geog::geometry)), ST_SRID(geog)  )::geography;
$$ LANGUAGE sql
IMMUTABLE;

Testing it over a geography and geometry object (EWKT is Extended Well-known Text (WKT) to include the SRID.)

SELECT ST_AsEWKT(pt) AS orig,
  ST_AsEWKT(  _ST_SwapXY(pt::geometry)   ) AS geom,
  ST_AsEWKT(  _ST_SwapXY(pt::geography)  ) AS geog
FROM ( VALUES
  (ST_MakePoint(0,1)),
  (ST_SetSRID(ST_MakePoint(0,1),4326))
) AS t(pt);
         orig         |         geom         |         geog
----------------------+----------------------+----------------------
 POINT(0 1)           | POINT(1 0)           | SRID=4326;POINT(1 0)
 SRID=4326;POINT(0 1) | SRID=4326;POINT(1 0) | SRID=4326;POINT(1 0)

Note: MySQL's ST_SwapXY() is substantially more powerful covering LINESTRINGS and POLYGON too, but hopefully your structures are not internally that screwed up. If they are you'll have to recursively do this.


using statistics in order to decide whether or not to add a filtered index

This is probably not the sort of case where filtered indexes really shine - typically that is when the index represents only a small portion of rows in the table (like, a quarter or less) and in this case the filter predicate matches about three quarters of the table.

That's not to say that you wouldn't get gains here, but it might be a bit of a tricky research project to get good designs. (Then again, indexing is always like that.)

What about the current indexes on the table. Are they being used at the moment? Are they only being used in queries that have the predicate that you mentioned? If so, then it might be an easy win to convert some of your existing indexes to have a filter condition (instead of adding a new, filtered index).

You probably already knew this, but filtered indexes won't be used by where the predicate that matches the filter uses a variable or parameter. https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/


You can restore the BACKUP with a different name in the same instance and follow steps below.

1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name.

2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.

3) Now you can do something like this:

USE [Old Database]
GO
SELECT * INTO [the blank table you've created in the new database]
FROM [the table on the backup, that has Data]

This will copy the data from the backup table, to the table you created.


Calling two stored procedures from one, is the order guaranteed?

Yes.  StoredProcedure1 will execute first, and any resultsets from StoredProcedure1 must be consumed by the client before StoredProcedure2 is executed.

It's possible that some client driver or DAL processes all the resultsets and then returns them to your calling code in a different order, but for SQL Server and the Microsoft client drivers, you will get the resultsets in the order in which they are produced.


I got the answer. Stored procedure supported in MYSQL since 5.0 .Older version doesn't support stored procedure .

reference link


Oracle Autodop disabled at the table level

You can't have "Auto DOP set to 1". You can either have:
- have auto DOP, meaning Oracle will attempt to automatically determine optimal DOP
- set DOP to 1, for example with ALTER TABLE PARALLEL 1

The second option disables parallel query, so yes, query will execute serially. Unless you override it with a hint or ALTER SESSION FORCE PARALLEL QUERY.