scheduling medical appointment challenge

What I would do is something like the process outlined below. I've left in links to pages which I found helpful and I've also left in fields which aren't strictly necessary. I'll outline my thought process as I go.

CREATE and populate doctor and patient tables:

CREATE TABLE doctor
(
  doctor_id   INTEGER NOT NULL AUTO_INCREMENT,
  doctor_name VARCHAR (50) NOT NULL,
  CONSTRAINT doctor_pk PRIMARY KEY (doctor_id)
);
INSERT INTO doctor (doctor_name)
VALUES ('Doctor_1'), ('Doctor_2'), ('Doctor_3'), ('Doctor_4');
SELECT * FROM doctor;

and:

CREATE TABLE patient
(
  patient_id INTEGER NOT NULL AUTO_INCREMENT,
  patient_name VARCHAR (50),
  CONSTRAINT patient_pk PRIMARY KEY (patient_id)
);
INSERT INTO patient (patient_name)
VALUES ('p1'), ('p2'), ('p3'), ('p4'), ('p5'), ('p6'), ('p7'), ('p8'), ('p9'), ('p10'),
('p11'), ('p12'), ('p13'), ('p14'), ('p15');
SELECT * FROM patient;

Then, we have to construct a date range - I chose 2019-10-10 09:00:00 to 2019-10-15 21:00:00. This range covers a weekend when the doctors don't work. It's best to prototype with a manageable number of records, rather than letting yourself be swamped.

I make use of a RECURSIVE CTE - very powerful and worth getting to know. PostgreSQL has a GENERATE_SERIES function which is very handy for this sort of thing (see PostgreSQL fiddle which duplicates what's done here for MySQL). MariaDB has a sequence engine which also has an analogous capability.

-- https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql
WITH RECURSIVE date_range AS
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts < STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
)
SELECT
  DATE_FORMAT(ts, '%a') AS DOW,
  WEEKDAY(ts),
  WEEKDAY(ts) % 6,
  ts from date_range
WHERE CAST(ts AS DATE) >= CAST('2019-10-10 09:00:00' AS DATE)
AND   CAST(ts AS DATE) <= CAST('2019-10-15 21:00:00' AS DATE)
-- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
AND DATE_FORMAT(ts, '%H:%i:%s') >= '09:00:00'
AND DATE_FORMAT(ts, '%H:%i:%s') <= '21:00:00'
AND DATE_FORMAT(ts, '%a') != 'Sat'
AND DATE_FORMAT(ts, '%a') != 'Sun';

Result (1st 10 records):

DOW WEEKDAY(ts) WEEKDAY(ts) % 6 ts
Thu 3   3   2019-10-10 09:00:00
Thu 3   3   2019-10-10 09:15:00
Thu 3   3   2019-10-10 09:30:00
Thu 3   3   2019-10-10 09:45:00
Thu 3   3   2019-10-10 10:00:00
Thu 3   3   2019-10-10 10:15:00
Thu 3   3   2019-10-10 10:30:00
Thu 3   3   2019-10-10 10:45:00
Thu 3   3   2019-10-10 11:00:00
Thu 3   3   2019-10-10 11:15:00
…
10 rows of 196

So, now we have a range of times for the range that we want. But, an appointment has a start_time and an end_time.

So we perform a self JOIN on this table of dates:

First, in order to store these slot times, we create a table:

CREATE TABLE slot
(
  slot_id    INTEGER NOT NULL AUTO_INCREMENT,
  slot_begin TIMESTAMP NOT NULL,
  slot_end   TIMESTAMP NOT NULL,
  CONSTRAINT slot_pk PRIMARY KEY (slot_id),
  CONSTRAINT slot_begin_end_uq UNIQUE (slot_begin, slot_end)
);

And then derive the slot data as follows:

-- https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql
WITH RECURSIVE date_range AS
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts < STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
),
xrange AS
(
  SELECT
    DATE_FORMAT(ts, '%a') AS DOW,
    WEEKDAY(ts),
    WEEKDAY(ts) % 6,
    ts from date_range
  WHERE CAST(ts AS DATE) >= CAST('2019-10-10 09:00:00' AS DATE)
  AND   CAST(ts AS DATE) <= CAST('2019-10-15 21:00:00' AS DATE)
  -- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
  AND DATE_FORMAT(ts, '%H:%i:%s') >= '09:00:00'
  AND DATE_FORMAT(ts, '%H:%i:%s') <= '21:00:00'
  AND DATE_FORMAT(ts, '%a') != 'Sat'
  AND DATE_FORMAT(ts, '%a') != 'Sun'
)
SELECT t1.ts, t2.ts FROM xrange t1
JOIN xrange t2
  ON t1.ts = DATE_ADD(t2.ts, INTERVAL - 15 MINUTE);

Result (first 10 records):

slot_begin          slot_end
2019-10-10 09:00:00 2019-10-10 09:15:00
2019-10-10 09:15:00 2019-10-10 09:30:00
2019-10-10 09:30:00 2019-10-10 09:45:00
2019-10-10 09:45:00 2019-10-10 10:00:00
2019-10-10 10:00:00 2019-10-10 10:15:00
2019-10-10 10:15:00 2019-10-10 10:30:00
2019-10-10 10:30:00 2019-10-10 10:45:00
2019-10-10 10:45:00 2019-10-10 11:00:00
2019-10-10 11:00:00 2019-10-10 11:15:00
2019-10-10 11:15:00 2019-10-10 11:30:00
…
10 rows of 192

And, also, we scroll down to the end to check that there are no boundary issues:

2019-10-15 20:15:00 2019-10-15 20:30:00
2019-10-15 20:30:00 2019-10-15 20:45:00
2019-10-15 20:45:00 2019-10-15 21:00:00
192 rows

So, perfect. We can see that the last slot is from 20:45 to 21:00 which is the desired result.

-- The origin of this syntax is explained in a link below - check out the PostgreSQL
-- fiddle to see how it should be done!

INSERT INTO slot (slot_begin, slot_end)
WITH RECURSIVE date_range AS
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts < STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
),
xrange AS
(
  SELECT
    DATE_FORMAT(ts, '%a') AS DOW,
    WEEKDAY(ts),
    WEEKDAY(ts) % 6,
    ts from date_range
  WHERE CAST(ts AS DATE) >= CAST('2019-10-10 09:00:00' AS DATE)
  AND   CAST(ts AS DATE) <= CAST('2019-10-15 21:00:00' AS DATE)
  -- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
  AND DATE_FORMAT(ts, '%H:%i:%s') >= '09:00:00'
  AND DATE_FORMAT(ts, '%H:%i:%s') <= '21:00:00'
  AND DATE_FORMAT(ts, '%a') != 'Sat'   -- <<======== no work on the weekends
  AND DATE_FORMAT(ts, '%a') != 'Sun'   -- <<========
)
SELECT t1.ts, t2.ts FROM xrange t1
JOIN xrange t2
  ON t1.ts = DATE_ADD(t2.ts, INTERVAL - 15 MINUTE);

Just a quick check - had issues with various boundary conditions - always check as you go!

SELECT -- A check - I had a few wierd errors and this helped!
       -- There are 4 slots/hour over the whole day 12 working
       -- hours (docs do shifts of 8 hours, so, in the case of 4 days = 48 slots!
    COUNT(DAYNAME(s.slot_begin)) AS cnt,
    DATE_FORMAT(s.slot_begin, '%a') AS the_day
FROM slot s
GROUP BY  DATE_FORMAT(s.slot_begin, '%a');

Result:

cnt the_day
48  Thu
48  Fri
48  Mon
48  Tue

So, this is 12 working hours/day by 4 slots/hour = 48 - so far, so good.

-- Double checking - in particular the boundaries between the days,
-- making sure there are no problems with edge cases.

SELECT  DATE_FORMAT(s.slot_begin, '%a') AS the_day, WEEKDAY(s.slot_begin) AS dow,
        DATE_FORMAT(slot_begin, '%Y-%m-%d %H:%i') AS start,
        DATE_FORMAT(slot_end,   '%Y-%m-%d %H:%i') AS end
FROM slot s -- WORKS!
ORDER BY slot_begin;

-- https://stackoverflow.com/questions/6718759/how-to-select-date-and-time-without-the-seconds-in-mysql
-- SELECT DATE_FORMAT(`date`, '%Y-%m-%d %H:%i')`date`, '%Y-%m-%d %H:%i')
-- Result  No days x 12 hours x 4 15 minute slots (4 days = 192 = 4 x 48)

Result:

the_day dow start   end
Thu 3   2019-10-10 09:00    2019-10-10 09:15
Thu 3   2019-10-10 09:15    2019-10-10 09:30
Thu 3   2019-10-10 09:30    2019-10-10 09:45
Thu 3   2019-10-10 09:45    2019-10-10 10:00
Thu 3   2019-10-10 10:00    2019-10-10 10:15
Thu 3   2019-10-10 10:15    2019-10-10 10:30
Thu 3   2019-10-10 10:30    2019-10-10 10:45
Thu 3   2019-10-10 10:45    2019-10-10 11:00
Thu 3   2019-10-10 11:00    2019-10-10 11:15
Thu 3   2019-10-10 11:15    2019-10-10 11:30
…
10 rows of 192

4 x 48 = 192 ✓ End conditions also checked - not shown.

We now create an Associative Entity - better known as a joining or linking table between the doctors and the slots.

CREATE TABLE doc_slot
(
  slot_id   INTEGER NOT NULL,
  doctor_id INTEGER NOT NULL,
  CONSTRAINT doc_slot_pk PRIMARY KEY (doctor_id, slot_id),
  CONSTRAINT ds_doctor_id_fk FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id),
  CONSTRAINT ds_slot_id_fk   FOREIGN KEY (slot_id)   REFERENCES slot   (slot_id)
);

and poplulate it with a CROSS JOIN.

So, doctors 1 & 2 have both been associated with every possible slot between 09:00 and 17:45 - i.e. work till 18:00. They also have a lunch break from 13:00 to 14:00.

-- A CROSS JOIN between slot and doctor for doctors 1 & 2. They work from 09:00 till
-- 18:00 - last appointment at 17:45. They also have an hour off between 13:00
-- and 14:00 - no appointments!
INSERT INTO doc_slot (slot_id, doctor_id)  -- no. of days x 32/slots/day x 2 docs (ids 1 & 2)
SELECT s.slot_id, d.doctor_id
FROM  slot s, doctor d
WHERE EXTRACT(HOUR FROM s.slot_begin) BETWEEN 9 AND 17  -- 17:45 last appt.
AND   EXTRACT(HOUR FROM s.slot_begin) != 13  -- no appts. between 13 and 13:45 - lunch
AND   d.doctor_id IN (1, 2)                  -- docs 1 & 2 work 09 - 18
ORDER BY slot_begin, doctor_id;

Now, we do the same for doctors 3 & 4 who will work from 12:00 to 20:45 - finish at 21:00. They also get an hours break from 16:00 to 17:00.

-- For a bit of realism (or maybe not! - variety is the...), the other two
-- doctors (3 $ 4) are scheduled to work from 12:00 to 21:00 - last appointment
-- 20:45 - and a break between 16:00 and 17:00. Legistation/practices &c. may
-- vary from country to country.

INSERT INTO doc_slot (slot_id, doctor_id)  -- no. of days x 32/slots/day x 2 docs (ids 3 & 4)
SELECT s.slot_id, d.doctor_id              -- = 256 for 4 days
FROM  slot s, doctor d
WHERE EXTRACT(HOUR FROM s.slot_begin) BETWEEN 12 AND 20  -- 20:45 last appt.
AND   EXTRACT(HOUR FROM s.slot_begin) != 16  -- no appts. between 16 and 16:45
AND   d.doctor_id IN (3, 4)                  -- docs 3 & 4 work 12 - 21
ORDER BY slot_begin, doctor_id;

The check for these two INSERTs is given in the fiddle.

Now, we CREATE an appointment table:

CREATE TABLE appointment
(
  appointment_id SERIAL,
  doctor_id INTEGER NOT NULL,
  patient_id INTEGER NOT NULL,
  appt_begin_time TIMESTAMP NOT NULL,
  appt_end_time TIMESTAMP NOT NULL,
  CONSTRAINT app_pk PRIMARY KEY (appointment_id),
  CONSTRAINT app_doc_uq UNIQUE (doctor_id, appt_begin_time),  -- a doc can only have one appt at a time
  -- CONSTRAINT app_pat_uq UNIQUE (patient_id, appt_begin_time), -- a pat can only have one appt at a time
  -- because of the use of the RAND function, this constraint can only be enabled later!
  CONSTRAINT app_doc_fk FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id)  -- but this will have to wait
);

and populate:

-- I got this strange syntax here:
-- https://mydbops.wordpress.com/2019/01/06/common-table-expression-cte-mysql-8-0/
-- Also useful:
-- https://www.techonthenet.com/mysql/functions/rand.php (RAND())
INSERT appointment (doctor_id, patient_id, appt_begin_time, appt_end_time)
WITH cte1 AS
(
  SELECT             -- the no of doc_slots = no. of days x 32/slots/day x 4 docs
    d.doctor_id AS did,
    FLOOR(RAND()*(15 - 1 + 1)) + 1 AS p_no,
    -- DATE_TRUNC('MINUTE', s.slot_begin),
    -- TO_CHAR(s.slot_begin,'YYYY-MM-DD HH24:MM') AS t_mins,
    s.slot_begin AS a_begin,
    s.slot_end AS a_end
    -- s.slot_id,       -- (= 256 for 4 days)

    -- ds.slot_id AS ds_sid, ds.doctor_id AS ds_did,
    -- d.doctor_id AS d_did, d.doctor_name AS d_name
  FROM slot s
  JOIN doc_slot ds
    ON s.slot_id = ds.slot_id
  JOIN doctor d
    ON ds.doctor_id = d.doctor_id
  ORDER BY ds.slot_id, ds.doctor_id
)
SELECT * FROM cte1;

And now our appointment table is populated with every possible combination of doctor and slot + a random patient (FLOOR(RAND()*(15 - 1 + 1)) + 1 AS p_no,).

Because of the random nature of the patient_id, there are some duplicates of patient_id and appointment start_time. We need to delete these, and first we need to identify them. To do this, we use the ROW_NUMBER() function.

-- Because of the RAND function, some patients have two appointments at the same time.
-- We need to get rid of these duplicates - scroll down and you'll see some records
-- where rn_p is > 1 - these are duplicate appointment times for the same patient.

SELECT
  appointment_id,
  appt_begin_time,
  doctor_id,
  patient_id,
  ROW_NUMBER() OVER (PARTITION BY appt_begin_time, patient_id
                         ORDER BY appointment_id) AS rn_p
FROM appointment
ORDER BY appointment_id;

Scrolling down through the results reveals some records with rn_p = 2 (and sometimes 3 or more).

-- Now to "cleanse" the appoinment table of duplicates!
-- i.e. patients who (from the RANDOM() above have 2 or more appointments at the same time)
-- Don't have to worry about the doctors because they've been assigned via the
-- CROSS JOIN.
DELETE FROM appointment
WHERE appointment_id IN
(
  SELECT t1.appointment_id
  FROM
  (
    SELECT
      appointment_id,
      appt_begin_time,
      doctor_id,
      patient_id,
      ROW_NUMBER() OVER (PARTITION BY appt_begin_time, patient_id
                         ORDER BY appointment_id) AS rn_p
    FROM appointment
    ORDER BY appointment_id
  ) AS t1 WHERE rn_p > 1
);

A quick SELECT (not shown, see fiddle) shows that about 40 records (on average) are eliminated in this manner.

We can now add a UNIQUE constraint as follows:

ALTER TABLE appointment
ADD CONSTRAINT appt_patient_appt_begin_uq UNIQUE(patient_id, appt_begin_time);

The appointments table will now be pruned. About 40 records (of the 512 CROSS JOIN) have been deleted, but we need to remove a few more in order to give a reasonable number of available appointments for our queries down the line. This necessitates a weird nested SELECT in the DELETE - see the link for details:

-- The convoluted nested SELECT syntax below was explained here:
-- https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause
-- I got it by searching for
-- "mysql  - You can't specify target table 'appointment' for update in FROM clause"
-- take a look at the PostgreSQL fiddle for how it **_SHOULD_** be done!

DELETE FROM appointment
WHERE appointment_id IN
(
  SELECT appointment_id FROM
  (
    SELECT
      appointment_id FROM appointment   -- takes out approx 25 rows - will vary because
    WHERE                               -- of RANDOM() for patient_id - you can vary this
     -- MOD(appointment_id, 2)  = 0     -- by removing the commented MOD()s.
     -- OR MOD(appointment_id, 3)  = 0
     -- OR MOD(appointment_id, 4)  = 0
     -- OR MOD(appointment_id, 5)  = 0
     -- OR MOD(appointment_id, 7)  = 0
     -- OR MOD(appointment_id, 11) = 0
     -- MOD(appointment_id, 18) = 0
     appointment_id % 23 = 0
   ) AS t1
);

The number of DELETEd records can be varied by adding or removing WHERE conditions.

So, now, we want to delete the slots that correspond to appointments in the appointment table - otherwise it would be possible to double-book the doctors for a given start_time.

-- Here we delete the doc_slots corresponding to the appointments which
-- are filled! This necessitates JOINing to the slot table (for the begin_time)
-- and then deleting the corresponding doc_slots - ~ 450/512 with
-- MOD(appointment_id, 23) = 0 above. You can increase the amount of
-- available appoinments by adding more MODs to the WHERE conditions.

DELETE FROM doc_slot ds
WHERE (ds.slot_id, ds.doctor_id) IN
(
  SELECT s_id, ds_did FROM
  (
    SELECT
      a.appt_begin_time, a.doctor_id,
      t1.s_id, t1.ds_did
    FROM appointment a
    JOIN
    (
      SELECT
        ds.slot_id AS ds_sid, ds.doctor_id AS ds_did,
        s.slot_id AS s_id, s.slot_begin AS s_begin
      FROM
        doc_slot ds
        JOIN slot s ON ds.slot_id = s.slot_id
      ORDER BY s.slot_begin, ds.doctor_id
    )  t1
    ON a.appt_begin_time = t1.s_begin
    AND a.doctor_id = t1.ds_did
  ) AS t2
);

This process normally leaves about 45 - 55 records in the doc_slot table available for future bookings.

-- Finally, the answer to the question!

SELECT
  d.doctor_name, ds.doctor_id ds_di, ds.slot_id ds_is,
  s.slot_id, s.slot_begin
FROM doc_slot ds
  JOIN slot s ON ds.slot_id = s.slot_id
  JOIN doctor d ON ds.doctor_id = d.doctor_id
WHERE CAST(s.slot_begin AS DATE) = CAST('2019-10-11' AS DATE)
-- AND d.doctor_name = 'Whatever'
-- many people will have a regular/preferred doctor
-- depending on the circumstances.
ORDER BY s.slot_begin ASC, ds.doctor_id ASC;

-- Even though ASC is the default ORDER BY, I included it for clarity,
-- So, we have ~ 15 - 20 appointments with app % 23 and 4 days and 4 doctors

Result (will vary according to the output of RAND()):

doctor_name ds_di   ds_is   slot_id slot_begin
Doctor_2    2       53      53      2019-10-11 10:00:00
Doctor_4    4       62      62      2019-10-11 12:15:00
Doctor_1    1       63      63      2019-10-11 12:30:00
Doctor_4    4       70      70      2019-10-11 14:15:00
Doctor_4    4       75      75      2019-10-11 15:30:00
Doctor_3    3       76      76      2019-10-11 15:45:00
Doctor_3    3       81      81      2019-10-11 17:00:00
Doctor_2    2       84      84      2019-10-11 17:45:00
Doctor_3    3       95      95      2019-10-11 20:30:00
9 rows

So, when you have an app that allows surgery staff to book appointments, you will have to perform two operations within  a transaction.

1) CREATE a record in the appointment table

AND

2) DELETE the corresponding record in the doc_slot table.

I'm not sure what the limit is on booking appointments into the future, but once your system is setup, whenever you add a new set of time slots, you can populate doc_slot table with the new potentially available times. These doc_slot records will then be moved into the appointment table and removed from the doc_slot one as new appointments are booked. Your app should allow doctors to mark themselves as unavailable.

Keeping the data in the appointment and doc_slot slot tables for later analysis may provde useful (when no-shows are likely, busy/non-busy periods - other criteria).

You could also search for open source doctor appointment software and see how they do it - that's the beauty of F/LOSS - you can pick 'n mix and borrow ideas.


No.

You should combine receipts and purchases, because they are 1 to 1 correlated - there cant be more than one receipt for one paypal purchase, and vice versa. Same goes for Bank Purchases. Therefore ALL of these basically constitute "Purchases". What you call 'receipt' just seems to be the act of CONFIRMING the purchase, and nothing else.

Withdrawals should be in a single table, and should have its own details as necessary ( paypal out-payment, bank out-payment etc, if applicable in future). Because withdrawals can be more than one - a guy who has $100 in his account can withdraw 4 $25 withdrawals in differing times. Which will have to be paid out in its own payment method and confirmed in its own fashion. Therefore withdrawals is a different identity.

And all the searches involving these entities will happen in that fashion too - you cant search a withdrawal IN RELATION TO two different purchases in Purchases. Or vice verse. They are independent entities which are only linked by user_id, and very loosely by account balance - the latter only as a limiting factor for the other.

Userts table is solid as it is and needs no change.


This is generally developer's choice but in my opinion restaurant_tables implies one-to-many relationship. When a table resolves many-to-many relationship I prefer names like restaurants_X_dishes. Again, this is up to my taste.


Including multiple columns in statistics adds a new dimension that cannot be estimated otherwise: correlation.

Query Predicate Contains Multiple Correlated Columns

  When a query predicate contains multiple columns that have cross-column relationships and dependencies, statistics on the multiple columns might improve the query plan. Statistics on multiple columns contain cross-column correlation statistics, called densities, that are not available in single-column statistics. Densities can improve cardinality estimates when query results depend on data relationships among multiple columns.

Optimizing Queries That Access Correlated datetime Columns
Exploring Column Correlation and Cardinality Estimates

  , if a column is replicated in more than one of these statistics and the first statistic is updated, how does this update affect the other statistics

All statistic need to be updated to be accurate.

Whether all stats recommended by the DTA are necessary, that is a different topic.


Postgresql - newer identical index is used, older one used to be but is not anymore

PostgreSQL grossly overestimates how many rows will be returned by the index scan. It estimates the cost for the index scan and the sequential scan are about the same, so the slightly larger size of the index when it gets bloated probably tips the scales.

You should improve the estimates. Since it is a simple inequality condition on a timestamp, PostgreSQL should be able to come up with a decent estimate.

First, run an ANALYZE on the table and see if that does the trick.
If it does, make sure that autoanalyze runs more often:

ALTER TABLE atable SET (autovacuum_analyze_scale_factor = 0.02);

If not, run

SET default_statistics_target = 1000;

Then ANALYZE again and see if that improves the estimate.

If yes, use a statement like the following to persist the setting:

ALTER TABLE atable ALTER acolumn SET STATISTICS 1000;


Would this INSERT statement work? Errata in textbook?

You are correct, the second would fail due to the extra quote. This may be just a misprint caused by an over-aggressive grammar checker, or indeed it could be an apostrophe rather than a quote, and the font delineation is difficult to determine.

The first one will succeed.
The second one would fail due to the single quote (if it's a quote)
The third one would fail due to registration being longer than 8 characters (and the quote, if it's a quote)
The fourth one would fail as the HullId is out of range for a tinyint

Side note: why they would put a Hovercraft schema in the Hovercraft database is beyond me, and a great example of nonsensical design.


Is it possible to install oracle 12c without X window system

Yes, you can do that by running the runinstaller passing the silent argument. Also, it is possible to save and use response files, which basically contain standard configurations and parameters for the software installation. It's very useful if you will need to perform installations following a specific standard on a frequent basis.

Here's the docs:
https://docs.oracle.com/database/121/LADBI/app_nonint.htm

Another option:
You don't need to have "GUI" installed, you can emulate the Graphical Interface. There are plenty of tools. tigervnc is a great one. To install it, run as sudo: yum install tigervnc and after installation is completed, start it by running vncserver. You will be prompted for the user's password. Then you can issue: ps -ef | grep vnc, which will show you details about the program execution, being the port the most important. Now you can externally gain access to the server through VNC Viewer (needs to be installed), by specifying server's  ip and port/ display #. Note: Yum if your OS support it. Otherwise, use rpm (problem is every dependency will need to be separately installed).

Hope it helps.


How can I configure PostgreSQL 9.0 to start automatically under Windows XP, Part II?

There's also one more setting to be noted...
On the same window where we set Automatic/Manual start up, there's a "Recovery" tab in which we can force the service to restart when a failure occurs.

But this might not be a good idea to use, especially on development environments, since it may help slip some possible problems past the developer's and/or administrator's eyes.


Postgresql: Modify one value inside of a nested json column

You are looking for jsonb_set()
update "Assets"
  set "Parameters" = jsonb_set("Parameters"::jsonb, '{params2, k}', to_jsonb(2.2))
where ...


Redshift table occupies exponentially more disk space in production cluster

Found this in AWS documentation, which describes this behavior clearly.

The default amount of disk storage space allocated to two tables residing on different clusters can vary significantly, even when the tables are created using identical data definition language (DDL) statements and contain the same number of rows.

Because the block size in RedShift is 1MB, all columns will take up 1MB per column at a minimum. On top of this if the DISTSTYLE is EVEN it will be closer to one block per slice in the database. Since there is no way to tweak the block size in RedShift there is no way to reduce the size of an empty table since the table's size follows below formula:

For tables created using the KEY or EVEN distribution style:

Minimum Table Size = block_size (1MB) * (number_of_user_columns + 3 system columns) * number_of_populated_slices * number_of_table_segments.

For tables created using the ALL distribution style:

Minimum Table Size = block_size (1MB) * (number_of_user_columns + 3 system columns) * number_of_cluster_nodes * number_of_table_segments

https://aws.amazon.com/premiumsupport/knowledge-center/redshift-cluster-storage-space/


How do I check that the database master key encryption is valid?

In order to programmatically determine if the current SMK was used to protect the DMK, you should be able to simply attempt an operation that would require the DMK. Such an operation would need to first decrypt the DMK in order to use it. Assuming that you have not opened the DMK explicitly (using the password supplied when creating it), decrypting the DMK will require the SMK. If the current SMK is not the correct SMK, then the DMK won't be automatically decrypted and the operation will fail. So:

If you have a Certificate that is guaranteed to exist in the Database being restored, try using it:

SELECT SIGNBYCERT( CERT_ID( '{certificate_name}' ), 'test' );

That should return a non-NULL VARBINARY value. If the return value is NULL, then the DMK needs to be regenerated (per the instructions below).
If no Certificate is guaranteed to exist in the Database being restored, then try to create one. If the SMK can be used to automatically decrypt the DMK, then the Certificate will be created, else the operation will fail:

BEGIN TRY
  CREATE CERTIFICATE [TestCert] WITH SUBJECT = 'yadda yadda yadda';
  DROP CERTIFICATE [TestCert];
  PRINT 'All good, yo!';
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH;

However, since you just restored a database coming from another instance and did not restore that other instance's SMK into the new instance, it is safe to assume that the answer is: "no, the DMK is not encrypted with this server's SMK."

This is an expected scenario that requires the following steps to remedy:

USE [newly_restored_db];
OPEN MASTER KEY DECRYPTION BY PASSWORD = '{password}'; -- password used to protect DMK
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}';
CLOSE MASTER KEY;

The MSDN page for CREATE MASTER KEY states (emphasis added):

  For SQL Server and Parallel Data Warehouse, the Master Key is typically protected by the Service Master Key and at least one password. In case of the database being physically moved to a different server (log shipping, restoring backup, etc.), the database will contain a copy of the master Key encrypted by the original server Service Master Key (unless this encryption was explicitly removed using ALTER MASTER KEY DDL), and a copy of it encrypted by each password specified during either CREATE MASTER KEY or subsequent ALTER MASTER KEY DDL operations. In order to recover the Master Key, and all the data encrypted using the Master Key as the root in the key hierarchy after the database has been moved, the user will have [to] either use [the] OPEN MASTER KEY statement using one of the password[s] used to protect the Master Key, restore a backup of the Master Key, or restore a backup of the original Service Master Key on the new server.

The MSDN page for OPEN MASTER KEY states:

  When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.

The MSDN page for ALTER MASTER KEY states:

  The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised.


(A non-answer)

There is essentially no way.

atime should be turned off, else performance will be quite bad.

Just because you have not used a database in the last week does not mean that you won't use it next week.


Are there issues with using SELECT * in a view definition?

Yes, there are reasons to not use SELECT * in a view. The most important is that SQL Server caches the metadata of a view's output, and it doesn't magically update if underlying objects change. Here's a quick example to demonstrate:

USE tempdb;
GO
-- simple table with two int columns
CREATE TABLE dbo.x(a INT, b INT);
GO
INSERT dbo.x(a,b) VALUES(1,2);
GO

-- simple view using SELECT *
CREATE VIEW dbo.v_x
AS
  SELECT * FROM dbo.x;
GO

-- view will not be updated to see these changes:
EXEC sys.sp_rename N'dbo.x.b', N'c', N'COLUMN';
ALTER TABLE dbo.x ADD b DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
GO

-- view shows wrong data
SELECT * FROM dbo.x;
SELECT * FROM dbo.v_x;
GO

You need to force SQL Server to refresh its view of the view.

EXEC sys.sp_refreshview @viewname = N'dbo.v_x';
GO

-- now view is correct
SELECT * FROM dbo.v_x;
GO

Now why do you want to make an argument for using SELECT * in a view? Because you don't want to have to type the column names a second time? Did you know that you can easily generate the list of columns from Object Explorer by right-clicking and saying Script View/Table As > SELECT To > clipboard? Or by dragging the columns folder onto the query window?

Just don't do it. What you save in typing (and the time you've already wasted debating this) isn't worth the risk and doesn't justify having some exception where you make it seem like SELECT * is okay. It's a bad practice for a multitude of reasons, and going out of your way to make exceptions is inconsistent and can often encourage the wrong thing to junior developers.

In fact I try to remember to create all of my views WITH SCHEMABINDING so that changing them later is a pain. I don't want to be able to change views willy-nilly precisely because that's a surefire way to break something.


Why does LEN() function badly underestimate cardinality in SQL Server 2014?

For the legacy CE, I see the estimate is for 3.16228 % of the rows – and that is a "magic number" heuristic used for column = literal predicates (there are other heuristics based on predicate construction – but the LEN wrapped around the column for the legacy CE results matches this guess-framework).  You can see examples of this on a post on Selectivity Guesses in absence of Statistics by Joe Sack, and Constant-Constant Comparison Estimation by Ian Jose.
-- Legacy CE: 31622.8 rows
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  ( QUERYTRACEON 9481); -- Legacy CE
GO

Now as for the new CE behavior, it looks like this is now visible to the optimizer (which means we can use statistics).  I went through the exercise of looking at the calculator output below, and you can look at the associated auto-generation of stats as a pointer:
-- New CE: 1.00007 rows
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  ( QUERYTRACEON 2312 ); -- New CE
GO

-- View New CE behavior with 2363 (for supported option use XEvents)
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  (QUERYTRACEON 2312, QUERYTRACEON 2363, QUERYTRACEON 3604, RECOMPILE); -- New CE
GO

/*
Loaded histogram for column QCOL:
[tempdb].[dbo].[#customers].cust_nbr from stats with id 2
Using ambient cardinality 1e+006 to combine distinct counts:
  999927

Combined distinct count: 999927
Selectivity: 1.00007e-006
Stats collection generated:
  CStCollFilter(ID=2, CARD=1.00007)
      CStCollBaseTable(ID=1, CARD=1e+006 TBL: #customers)

End selectivity computation
*/

EXEC tempdb..sp_helpstats '#customers';

--Check out AVG_RANGE_ROWS values (for example - plenty of ~ 1)
DBCC SHOW_STATISTICS('tempdb..#customers', '_WA_Sys_00000001_B0368087');
--That's my Stats name yours is subject to change

Unfortunately the logic relies on an estimate of the number of distinct values, which is not adjusted for the effect of the LEN function.
Possible workaround
You can get a trie-based estimate under both CE models by rewriting the LEN as a LIKE:
SELECT COUNT_BIG(*)
FROM #customers AS C
WHERE C.cust_nbr LIKE REPLICATE('_', 6);

Information on Trace Flags used:

2363: shows a lot of information, including statistics being loaded.
3604: prints the output of DBCC commands to the messages tab.


You can set any of these three BINLOG-FORMAT :

STATEMENT causes logging to be statement based.
ROW causes logging to be row based.
MIXED causes logging to use mixed format.

The default binary logging format depends on the version of MySQL you are using:

For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default.
For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.

You have to make sure that:

Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (When using STATEMENT mode, the binlog_format system variable is not replicated; when using MIXED or ROW logging mode, it is replicated but is ignored by the slave.) Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can thus cause unexpected results, or even cause replication to fail altogether.
When using statement-based logging for replication, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication.

Also, this bug report is related about problem you have mentioned here :

http://bugs.mysql.com/bug.php?id=53259


What's the advantage of the inet datatype in Cassandra vs a plain text string?

It's a special datatype for storing ip addresses. It would be more compact that the string representation of the same information as well as type safe.

inet    An IP address. It can be either 4 bytes long (IPv4) or
16 bytes long (IPv6). There is no inet constant,
IP address should be inputed as strings

https://cassandra.apache.org/doc/cql3/CQL.html


MySQL on Windows Refuses to Start as a Service

It seems it all came down to the " " which it didn't like.
So I changed:
G:/mysql57/bin/mysqld.exe --install MySQL57 --defaults-file="G:/mysql57/my.ini"
to
G:/mysql57/bin/mysqld.exe --install MySQL57 --defaults-file=G:/mysql57/my.ini
and it started up straight away. I can only assume this is something to do with Windows Server 2012, as I repeated the whole process (with " ") on my Windows 10 PC, and it worked fine.


Best way to store multiple values from multiple daily transactions

I would normally expect that you'd have at least the following:

User table: has user's login_id (probably literally the ID they use to login to the system), FullName, plus other user info (don't have enough info to know what else goes in here - department, job title, etc.). This is technically optional, but is helpful to tie logins to people's actual names.

TimeEntry table: user's login_id, EntryTime (probably using the timestamp data type), EntryType ("I" for login, "O" for logout - this is optional, but helpful if it's possible for login or logout values to be missing (for instance, an involuntary logout due to a power failure)); plus, other relevant values (if any).

The login_id makes sure the entries belong to the right user. The times give us the order in which things happened. As noted, the login/logout flag helps if an entry is missing for some reason. Not sure if this is being captured automatically or if it requires action on the user's part; missing entries much more likely with the latter. I'm assuming that, when populating the EntryTime column, you'll just use CURRENT_TIMESTAMP(); should provide maximum available time data, and help line up very rapid login/logouts if EntryType can't be set. (I've captured similar data from the database end on an MS SQL Server system, and the application would usually have one login that lasted for well under a second before actually connecting for real.)

NOTE: If it's possible to have a login without a logout, EntryType will help you properly match the pairs you have. If you have 5 entries for a user on a given day (say, "8AM", "10AM", "1PM", "4PM", "5PM"), and no EntryType flag, you don't know whether the missing entry is a login or a logout, or where it falls; if that is available (["8AM", "I"]; ["10AM", "O"]; ["1PM", "O"]; ["4PM", "I"]; ["5PM", "O"]), then you can easily identify the unmatched entry, and have at least a minimum confirmed hours logged in (3 hours).

AND: remember to allow for the possibility that login and logout aren't on the same calendar day (if applicable).


How to create an object and then alter it

You can't put CREATE FUNCTION inside IF logic since it needs to be in its own batch.

I would do it this way. If the function doesn't exist yet, create an empty stub. Now you can always safely issue an alter.

IF OBJECT_ID('dbo.function_name') IS NULL
BEGIN
  DECLARE @sql NVARCHAR(MAX) = N'CREATE FUNCTION dbo.function_name()
    RETURNS INT
    AS
    BEGIN
      RETURN (1);
    END';

  EXEC sp_executesql @sql;
END

ALTER FUNCTION dbo.function_name
(
  ... more complex logic here ...

Unfortunately I know of no easy way to get SSMS to script it out this way for you, and even doing that through SMO or other methods is going to be a pain IMHO. Simply because the function has to be of the same type (IF, TVF, MSTVF), so your simple stub can't always be the same.

Wouldn't it be great if SQL Server supported CREATE OR REPLACE?


How to upgrade SQL Server 2016 and 2017 Standard to 2017 Enterprise edition

Upgrading your two 2017 instances to Enterprise is a simple process. SQL Server Setup has a function called Edition Upgrade, which is a fairly straightforward process.

Your SQL 2016 Standard to SQL 2017 Enterprise is more complicated. You need to assess your environment and determine what best suits your resources, skills and appetite for risk. Microsoft has a good primer on this. You have two high-level options:

In-Place Upgrade
Migration

An In-Place Upgrade involves upgrading the installed SQL Server instance to a later version/edition in place on the same server. This is a somewhat simpler option but is higher risk. rollback is more difficult and from experience, the potential for things to go wrong are greater.

A Migration upgrade involves building a new SQL Server Instance and migrating your databases and server objects from the current instance to a new one. The advantage of a migration upgrade is that your original instance is left untouched, so rolling back usually involves little more than restoring your pre-upgrade backups and reconfiguring your application connection strings to point to the old server.

You also have the advantage of a 'slow burn' approach, where you can migrate individual databases, or subsets of databases, at a time. This is particularly helpful if your instance is shared by many applications because you can migrate the databases and objects for one application at a time, reducing the overall impact of the upgrade.

The downside to a migration approach is the need for additional hardware and licences for the period that both the old and new instances are running, plus the additional time taken to complete the 'upgrade'. Often this downside is outweighed by the risks involved with an In-Place Upgrade, but that is a business decision you need to determine within your team.

As you've stated in your comments, your goal is to put together a plan for your Senior DBA as part of a training assessment. You've not stated if this needs to be a high-level plan or a detailed plan, but either way, start with the primer from Microsoft and determine your preferred option based on risk appetite, allowable downtime, available resources (people and equipment) and your skillset. From here, you can develop a high-level plan for your Senior DBA to review and, if necessary, develop a detailed plan based on the feedback on your high-level plan.

Don't forget to include a plan for pre-upgrade activities, such as determining application support for the new version and edition, testing the process etc, and post-upgrade activities, such as assessing performance changes, determining if raising the compatibility levels is suitable and potentially enabling newly accessed features. For example, going to Enterprise Edition enabled setting up an Availability Group to take advantage of the advanced HADR capabilities of Enteprise Edition.


In what case is a count(x or null) needed in Gaps and Islands?

It's all about NULL handling. The expression lag(id_type) OVER (ORDER BY date) <> id_type evaluates to a boolean value, which can be TRUE, FALSE or NULL. If either the left or the right operand is NULL, we get NULL. There are various ways NULL can be introduced on either side:

No previous (or next or similar in related cases) row.
Column can be NULL in underlying table.
An OUTER JOIN introduces NULL (no matching row found).
An expression is involved that can evaluate to NULL.

There are various ways to fold three-valued logic to just two cases. A CASE statement, an OR expression etc. Related answer with details:

Select longest continuous sequence

Here is a comparison of the involved techniques, plus (updated) benchmark. Basically, they all perform the same, it's a cheap operation. The aggregate FILTER clause, introduced Postgres 9.4, seems a bit faster in the updated benchmark:

For absolute performance, is SUM faster or COUNT?
Return counts for multiple ranges in a single SELECT statement

There is no case wherein only one of them works, the techniques are interchangeable. What to count or sum exactly depends on details of setup and requirements. In this case we only count TRUE - as long as the underlying table or query yields all non-null values (the corner case of the first row is open for debate).

And this case only requires a simple comparison. Related cases need to look at the previous or next value more closely. Then it may come in handy to provide a default value as 3rd parameter to lag() or lead().


Which folder I use for recovery mysql database

Accidently deleted mysql database ...

It happens.
It shouldn't be the End of the World.

... and reload again.  But I cant find my old database ...

Not sure what you mean by "reload" but if you did anything that involved running "create database" for that particular database then anything and everything about the old database has now been obliterated.

... and no have backup.

This is hard lesson for any DBA to learn and, sadly, you've learned it the Hard Way:

Any Data that you don't have at least two copies of is Data that you do not care about and are perfectly content to lose.

You must take Backups of your databases, for the very reason that, sometimes, when things go badly wrong, you have to Recover them.
If you have backups of the machine itself, you might be able to get something back by restoring the data files from that but do not expect to get everything back.  Databases do a lot in memory, so any files copied out from "under" a running database instance are not consistent.


Performance issues on a MySQL database with 150 million rows

INSERTing one row at a time can be sped up by gathering 100 rows and inserting them in a single batch:  INSERT INTO t (a,b,c) VALUES (1,2,3), (11,22,33), ...  This may speed up things by a factor of 10.
Check for errors after each batch.  If the batch fails, write it to a file for fixing and re-importing.  (This should improve the error-handling pass.)
Assuming the data is only "new" data, it should be easy to summarize the data for each day and write this to a new table.  This may speed up fetching a years's data for graphing by 10x.
The daily 200-400 files might be handled by multiple processes.  Suggest not breaking it into more processes than you have CPUs.

Putting all those together might shrink the elapsed time from hours to minutes.

If you would like to discuss things further, please provide SHOW CREATE TABLE, and maybe some more details on the data and its processing.  What's the value of innodb_buffer_pool_size?


Migrating triggers, functions and procedures from Oracle to PostgreSQL

You can certainly write triggers, functions and stored procedures (since Postgres 11) in languages other than C - the most popular is probably PL/pgSQL which is similar to Oracle's PL/SQL.

You can also write functions in SQL which is more efficient for simply "query encapsulation".

I would implement the function in your question as a simple SQL function:

CREATE OR REPLACE FUNCTION stage.nest(x integer, y integer, z integer)
RETURNS bigint
AS
$$
  select count(*)
   from ABC
   where foo_id = X
     and Y < A
     and Z > B;
$$
language SQL;

I remove the order by from that statement as it is useless for a query that returns a count(*)

The same function implemented with PL/pgSQL would be:

CREATE OR REPLACE FUNCTION stage.nest(x integer, y integer, z integer)
RETURNS bigint
AS
$$
declare
  l_count bigint;
begin

  select count(*)
    into l_count
  from ABC
  where foo_id = X
    and Y < A
    and Z > B;

  return l_count;
end;
$$
language SQL;

But in general PL/pgSQL should only be used if you do need procedural logic (loops, IF statements etc). Otherwise language sql is more efficient.

You don't really need a trigger in Postgres automatically generate ID values from a sequence. Just define the column as serial and things will be taken care of. If you want to force the use of a sequence, define the column as integer generated always as identity

Nevertheless the trigger would be very similar in Postgres, with the biggest difference that Postgres uses a trigger function that is referenced when defining the trigger.

create function f_config_trg()
  returns trigger
as
$$
begin
  new.CONF_ID := nextval('confi_id_seq');
end;
$$
language plpgsql;

create trigger config_trigger
  before insert on config
  for each row
  execute procedure f_config_trg();

You can find much more details on PL/pgSQL, functions and trigger functions in the manual

Unrelated to your question: don't get into the habit of using quoted identifiers in Postgres (or Oracle for that matter). They are much more trouble in the long run than they are worth it.


MSSQL Server - Mirror Between Different Versions

Technically it can work, although it's not usually a good idea to do so as part of a DR strategy, as after you've failed over to the newest version, you can't easily fall-back to the older version. The only reason it is supported is for doing rolling upgrades and minimising downtime.

This MSDN article explains all about it.

Some additional thoughts not strictly in answer to your question, but that come to mind:

I can't think of a good reason for keeping a system on an old version of SQL Server when you have access to a newer version. You're not going to be able to use any of the new features of the newer version, and you could run into development problems (particularly if you ever failed over to the newer version, made changes only available in 2016 and then wanted to go back to the older version - which would be tricky to do with mirroring like that anyway, but impossible if you've used something not available in 2008, and it could cause you to end up in hot water). I suggest just upgrading your main system to the most recent version you have access to.
Bear in mind that SQL Server 2008 is coming to it's end of life, and is in the process of being put out to pasture/sent to the glue factory.
Mainstream support ended two years ago, and it'll be totally out of support in 2019 (and they're not releasing any more updates anyway).


Drive holding my database is running out of space. What are my options?

It depends on how your database is architectured (you do not say) and may also depend on the PostgreSQL version you are using (you do not say).

Anyway, in general, with PostgreSQL, you can create a new "tablespace" that is to make a specific new directory (so you choose the place, like on second drive) available to create new PostgreSQL objects, such as tables, in it.

See https://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html that says Tables, indexes, and entire databases can be assigned to particular tablespaces. and gives this example:

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
CREATE TABLE foo(i int) TABLESPACE space1;

Also, are you sure that the current content is really only data and not temporary stuff or useless indexes or just overblown tables because you are not running autovacuum or things like that (this depends a lot on which PostgreSQL version you run)?

If you have only a single table, with really 1950 GB of data in it, you will first need to partition it, in order to be able to move some of the content to a new tablespace (and/or archive some old content somewhere else).


SQLCMD - add date to CSV. When run inside Command Prompt it works, when in Job then %date

Try Agent Tokens instead, eg

sqlcmd -S .\sql2012 -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "c:\temp\sqlcmd$(ESCAPE_NONE(DATE)).csv"

This worked for me.  Just a reminder, bcp is a more appropriate tool for this kind of extract; you won't have the header and "row(s) affected" problems which you're not dealing with here.


User cannot select from table when having SELECT permissions if CONTROL is denied

You shouldn't have to explicitly DENY any of those permissions since SQL Server is implicitly deny-first, meaning a Login / User / Security Principal (such as a Role) has no access to anything until you've explicitly granted access (either via scripting it with T-SQL using the GRANT keyword or using the UI that SSMS provides). Explicitly denying a permission takes precedent even over an explicit GRANT.
Think of it this way, there are three states a permission can be in and the order of precedence (by highest precedence first) for permissions in SQL Server work like this: Explicit DENY > Explicit GRANT > Implicit deny-first (default)
By explicitly granting CONTROL, you're not solving the problem or understanding the root is, rather you're just sidestepping it with a workaround by granting a very wide permission set (the explicit GRANT here supercedes the implicit deny-first nature).
Outside of that, I'm unsure of what your root issue actually is, but I'd recommend by first revoking (not granting) all of the explicit DENY permissions you've mapped to the TestWriter role, and start testing what your Test user has access to.


Do you restart mysql daily?  That could explain the new logs.

Check these VARIABLES:

log_output = FILE   -- or TABLE,FILE
slow_query_log = ON
slow_query_log_file = /...   -- some valid path, including filename
long_query_time = 1   -- or some small value

Perhaps the general log hit a limit, thereby triggering an internal call to FLUSH LOGS, which rotated all logs?


PostgreSQL is often overlooked but might be worth your attention.  It supports MVCC for concurrency, can do analytic SQL queries easily, supports indexes, scales up well enough, can do hot backups, supports date functions, and even has JSON data types.  Binary JSON (aka: JSONB, BSON) is being added to newer versions, which will give you the ability to index on JSON content and have improved analytic function & performance.

Check this out article on Phoronix:

Binary JSON Support Added To PostgreSQL, Competes With MongoDB
http://www.phoronix.com/scan.php?page=news_item&px=MTY0MTU


What is the best way to rebuild a date from integer inputs?

How about

declare @quarter int = 4
declare @year int = 2018

select datefromparts(@year,(@quarter-1)*3+1,1)

or if you're still using SQL 2008:

select dateadd(month,(@quarter-1)*3,dateadd(year, @year-2018,'20180101'))


How to backup Oracle Production Database without stopping service?

The best tool to create a backup database is rman.

sqlplus "/  as sysdba"
ALTER DATABASE FORCE LOGGING;     -- If you have tablespaces with nologging option
alter system checkpoint global;   -- To flash sga data to disk before backup
ALTER SYSTEM SWITCH LOGFILE;      -- To do a whole cycle of redologs
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
rman target /
-- to open a channel to write the backup
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/internal/backup/RMAN_001/full01_%I_%n_%T_%u_%s_%p';
-- to ensure the controlfile backup
CONFIGURE CONTROLFILE AUTOBACKUP ON;
-- to open a channel to write the oracle controlfile.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/internal/backup/RMAN_001/%F_Autobackup'; # default
-- backup database with controlfile and archivelogs so you will recover the database to a specific point in the time between backup and backup
BACKUP AS BACKUPSET DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG TAG = 'RMAN_001';


Jetbrains 0xDBE : How can I load a SQL Server .bak file?

An IDE is not going to be able to "load" a SQL Server backup. You need to restore the backup to a running instance of SQL Server, then the IDE will be able to connect to it and interact with it. You should be able to connect the IDE to a running instance of SQL Server, and then through a query window run a RESTORE command, documented here:

RESTORE (MSDN)

Depending on where the backup came from, you will likely need to use the WITH MOVE options, as it is unlikely your default data/log folders match those from the source.


('*') IS NULL evaluates to false. As a result the OR part also evaluates to false. We can use the simple rule:

(FALSE AND Something AND Something_Else AND ... )
= FALSE

Of course as a result, if you comment out (('*') IS NULL) the result changes.

Try commenting out the whole OR part:

SELECT ...
WHERE ...
-- OR ( (('*') IS NULL)
--      AND Project.Invalid = 0
--      AND ... )
OR ...

This should have no effect, due to the other rule:

(FALSE OR Something OR Something_Else OR ... )
=
(Something OR Something_Else OR ... )


What's the difference between xp_fixeddrives and this query

You're querying two different things here.

xp_fixeddrives will show you the amount of free space on the disk.

Using sys.dm views will tell you how much free space there is in the tempdb data file. If the data file is 50GB, then you're only using 5GB of it and the rest is empty. Even though it's empty, the file exists and is taking up 50GB of space on your drive.


Mysql Cluster 8 - "Jumpy" Auto Increment Values

the ids aren't jumpy, that is only an interpretation.

Tables are per definition unsorted, that why you put an order by in your querys.

the ids only serve to be unique and can sol identify a single row.

In a single maschine this happens, because processors have the capability to sort on a basic level if they have the need, mysql it self sorts things too and calls it optimizing. Then there a on heavy performing sytsem many processes which the operating system handles around.

Finally there are clusters that have to guaranty the uniqueness of every id, which usually works fine too.

And of course the table types play also a role see https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.7/en/example-auto-increment.html

So ignore the sorting of the ids, use it for identifying the rows.


Transactional Replication to a Version Two Versions Older--Count by Name or Number?

The SQL Server 2012 version of the MSDN page you have linked says (emphasis mine):

  A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server 2005 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012; and a SQL Server 2012 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012.

So, apparently you need to look at numerical versions in this case – meaning that for a SQL Server 2012 publisher the subscriber version should be 11.* ± 2.


Do you still need both the 32 bit and 64 bit connectors installed for SSIS 2016?

In the case of DBMSs that only offer ODBC connectors to interface with SSIS (Among other things) the answer is that only the 32-bit connector needs to be installed to make an ODBC data source that SSIS 2016 can recognize. For my environment this was the case with IBM Informix and Progress OpenEdge.

In the case of Oracle the prevailing wisdom I read back when I was working with SSIS 2012 still applies. I had to install both the 32 and 64 bit instant clients, configure symbolic links so that Windows could leverage both of them as it needed, and make the appropriate TNS files and environment variables for the Oracle connector to function.

MySQL worked with just the most recent, 64-bit .NET connector. The same applied to Teradata. It seems that only ODBC connectors and Oracle need 32-bit access.


How does trigger file in recovery.conf work?

Any reliable failover scenario requires a third party, sometimes called a "witness", "arbitrator", "cluster monitor", "cluster manager" etc. to detect failure of one member and initiate a failover. This process ideally resides on a network separate from the one that connects cluster members to each other. It "pings" node(s) that are deemed to be masters or active nodes and triggers failover when they don't respond within the timeout threshold. In your case this monitor will create trigger_file to initiate the failover.


Displaying the list of all tables in all database

You need a query for each database against sys.tables.

select 'master' as DatabaseName,
       T.name collate database_default as TableName
from master.sys.tables as T
union all
select 'tempdb' as DatabaseName,
       T.name collate database_default as TableName
from tempdb.sys.tables as T
union all
select 'model' as DatabaseName,
       T.name collate database_default as TableName
from model.sys.tables as T
union all
select 'msdb' as DatabaseName,
       T.name collate database_default as TableName
from msdb.sys.tables as T

You can use sys.databases to build and execute the query dynamically.

declare @SQL nvarchar(max)

set @SQL = (select 'union all
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
exec (@SQL)


MongoDB uses only one index on simple query with sorting

MongoDB 2.6+ will consider index intersection in cases where multiple indexes might improve the query performance, however for sorting the index has to have keys in common with the query predicate. In your scenario, this means an index on {price:1} cannot be used for index intersection unless price is included in the query.

  So it scans through all the collection ignoring all indexes.

What's actually happening according to the query explain() is that the price:1 index is being used to return the results in sorted order via an index scan (IXSCAN). Your compound index would require an in-memory sort of the result set, which may be larger than the size of the in-memory sort buffer (32MB) as you discovered by trying to hint that index. If you can add a limit to your query so the results are smaller than 32MB, the compound index may be usable but not ideal.

For your query, the ideal index to support the desired sort order (and an unlimited set of results) is a compound index including the price:

db.product.createIndex({"_type": 1, "_collectivity": 1, "own_risk": 1, "price": 1});

For more information, see:

Index Intersection and Sort
Use Indexes to Sort Query Results
Optimizing MongoDB Compound Indexes


Very complicated query is suddenly taking 35 minutes to finish in SQL Server 2014

SQL Server 2014 with compatibility level 120 uses the new CE. SQL Server 2012 uses the legacy CE. The new CE uses a fundamentally different model for cardinality estimation that is expected by Microsoft to result in better performing queries for most workloads. However, they acknowledge that some workloads or queries may perform worse with the new CE. It is possible that is the situation that you're in.

When investigating a query that isn't finishing quickly enough, one of the first things I try to determine is why the query is slow. Your query is slow because over four billion rows are read from a single index seek operator:

You can verify that this is the most expensive part of the query by looking at the actual time statistics shown at the operator level. The index seek uses 222360 ms of CPU time and the overall query uses 224382 ms of CPU time. The most straightforward way to get better performance is by improving the index on the RSTReclassedJournalEntries table. This table only has 127787 rows, so it's unlikely that the additional maintenance required for a better index would be a concern. Creating an index with key columns Posted and ReclassedJRNENTRY should suffice.

The IX_RSTReclassedJournalEntries_1 index already exists on the table and is a covering index, but you want a index that can filter on Posted and ReclassedJRNENTRY as seek predicates instead of as filter predicates. With such an index SQL Server will read a maximum of 34646 rows from the index instead of 4382443766 rows. This will dramatically improve performance.

The index change that I'm recommending addresses the symptom of the problem instead of the root cause, but sometimes that's enough to meet the business need. The root cause appears to be a join cardinality estimation problem with [SOLMQ].[dbo].[AAG30000].[AK2AAG30000]. Addressing that is likely to be more difficult than making the suggested index change, although it's possible that you'll get lucky and updating statistics on that table will fix the problem as well.


How can I improve a simple join on a very small table? (postgres)

This is the best index

CREATE INDEX ON cars (color, year);

Make sure that you have correct statistics, particularly on the small table:

ANALYZE make_model;
ANALYZE cars;


Column that must be unique only for rows with matching foreign key

I would like for Child.name to be unique, but only for members of Child where Child.parent_id is the same.

This mean you need NOT (Child.name) to be unique.
You need in (Child.name, Child.parent_id) to be unique.


How to join three table using inner join in postgres sql

You can't test for null using = or <> (or !=)

To test for non-null values you need to use IS NOT NULL:

where comp.templateid is not null


No, sequences are not valid as articles for any type of replication.

I don't have a good reason for you (yet). But I'm curious what the purpose of replicating the sequence itself would be. If your intention is to utilize the same sequence on the subscriber to populate other tables there that aren't replicated, this wouldn't really work unless you had some way to notify the source sequence that you've used some values. You could always call a stored procedure on the publisher that generates a new sequence value there, and use it on the subscriber.


MySQL still using one CPU even though extra CPU added

Short answer:  MySQL never uses more than one CPU per connection.  It never has, and there is very little hope on the horizon.
Exceptions:

There are some background threads that many use a small amount of CPU.

There are a limited number of cases in 8.0 of making use of multiple CPUs.

I would argue that it is not worth much effort to make use of multiple CPUs:

The orchestration of splitting up a query and recombining is a lot of overhead and complexity.
Some things virtually prevent using multiple CPUs.  Think ORDER BY.
Many queries are I/O-bound.  Extra cpus don't help in that case.
The typical "busy" system has many connections doing work.  In that case, many CPUs may actually be used.


Handling .bak and .dif files to optimize storage usage

Each differential backup is composed of the pages that have changed since the last full backup. So if you take a full backup on Sunday, then diffs on Monday, Tuesday, etc. then restoring the database will only require Sunday's full backup, followed by the most recent day's differential.

However, by overwriting your diffs each day, you are losing the ability to restore to a previous point in time. Example: Let's assume you take backups at 1 AM daily. On Thursday afternoon, you discover that data got corrupted on Wednesday and you need to get back to the last known good state. You can't, because the differential backup at 1 AM Wednesday (which would be clean) has been overwritten by the Thursday 1 AM backup which is corrupt.

So to answer your question, it is safe if and only if you assume that you will never have to revert to anything but the most recent backup, and that backup is always correct and corruption-free. I am not staking my job on that assumption.

I would not endorse the scheme you're proposing. Storage is relatively cheap - how much is the data worth to the company? Move the backups off to another, larger, cheaper datastore on a regular basis if space is at a premium, so that you can comply with any data retention policies, regulations or laws as needed.


Intermittent EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb'

Resolved this by setting up a maintenance plan to clean up MSDB. It was very full! Waited to post the answer because I wanted to make sure we went through a few weeks without getting that Execute permission denied error.


Add missing column to SSIS, data not extracted to staging table

In addition to adding the new column to the variable User::vSQLBase, you need to open the Data Flow for the staging table being loaded. Edit the source, and click on Columns, verify you see the new column, click OK. Then edit the Destination, and click on Mappings - and make sure that the new column from the source is pointing to the right column in the staging table. That will do the new column mapping the consultant was talking about. You'll need to make sure the mapping is updated for whatever moves data from staging to the destination table too. Then save and deploy again.

An incremental load only populates new or changed data since the last successful run. Do you care about the new column being populated in the destination only going forward? Or is it important for that data to exist in records previously imported as well? For an accounting system, I'd guess you'd want the new column populated for all the records in the table. In that case, I agree with your consultant that a full load is necessary.


Cannot Utilize Maximum CPU and Memory Usage for MySQL

The authors of High Performance MySQL describe part of your problem fairly succinctly (p. 234):

  MySQL can’t execute a single query in parallel on many CPUs. This is a feature offered
  by some other database servers, but not MySQL.

Running a single stored procedure will use no more than 1 of your CPU cores for executing queries, with a little extra on other cores being used by various background and worker threads, such as those performing I/O. MySQL will only really use the full power of a multi-core or multi-processor system when there are multiple queries running at once.

If you're seeing 30% of 1 core being used, that indicates a potential problem, but 1.2 cores out of 4 for a single query is quite normal.


Your problem is the not the syntax of the stored procedure but the DELIMITER statement.
There are two simple solutions
SOLUTION #1
Change the DELIMITER after you drop the procedure
DROP PROCEDURE IF EXISTS add_python_photo;
DELIMITER //
CREATE PROCEDURE add_python_photo(IN file_name VARCHAR(20),
                                  IN file_path VARCHAR(20),
                                  IN file_data LONGBLOB,
                                  IN python_id INT,
                                  IN preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (file_name,
                              file_path,
                              file_data,
                              python_id,
                              preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

SOLUTION #2
Use the DELIMITER // with the DROP PROCEDURE IF EXISTS
DELIMITER //
DROP PROCEDURE IF EXISTS add_python_photo //
CREATE PROCEDURE add_python_photo(IN file_name VARCHAR(20),
                                  IN file_path VARCHAR(20),
                                  IN file_data LONGBLOB,
                                  IN python_id INT,
                                  IN preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (file_name,
                              file_path,
                              file_data,
                              python_id,
                              preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

SUGGESTION
You should change the variables so that they are not the exact same name as the DB Columns
CREATE PROCEDURE add_python_photo(IN given_file_name VARCHAR(20),
                                  IN given_file_path VARCHAR(20),
                                  IN given_file_data LONGBLOB,
                                  IN given_python_id INT,
                                  IN given_preferred TINYINT,
                                  OUT python_photo_id INT)
BEGIN
  INSERT INTO python_photo (  file_name,
                              file_path,
                              file_date,
                              python_id,
                              preferred)
                      VALUES (given_file_name,
                              given_file_path,
                              given_file_data,
                              given_python_id,
                              given_preferred);

  SELECT LAST_INSERT_ID() INTO python_photo_id;
END//
DELIMITER ;

GIVE IT A TRY !!!
UPDATE 2016-10-10 13:51 EDT
Turns out to be the use of VARCHAR in the list of Stored Proc variables. While Other RDBMS products will allow for VARCHAR, MySQL will not accept it. VARCHAR variables must be followed by a width definition such as VARCHAR(20) in MySQL's Stored Proc variable list. This was pointed out by @ypercube in the comments, so all kudos for him on this one today.


Are you a member of db_owner?

See this:

  To use Database Diagram Designer, it must first be set up by a member of the db_owner role to control access to diagrams (https://msdn.microsoft.com/en-us/library/ms189279%28v=SQL.100%29.aspx)


Can drbd be used for mongodb replication or active/passive setting?

Answer is NO, you cannot use it with MongoDB. Result would be corrupted data, sooner or later. MongoDB keeps active data in memory and don't know how to react if data is changed "on-the-fly" at disk.


Write-lock a whole table during transaction

The Slave should be irrelevant, unless you are using (misusing) some form of parallel execution on the Slave.

Try other transaction_isolation_modes.

GET_LOCK()/RELEASE_LOCK() may provide a sufficient "lock"; but all writers to that table(s) would need to use it.

Do not turn on auto-reconnect in your connection.  An network glitch will mess with both the transaction and GET_LOCK.


SSIS 2012 Project Connection Manager does not expose ServerName property for configuration in SSMS 2012

I found the answer by opening the project with Visual Studio 2012.

Open the SSIS project in Visual Studio, and select the .conmgr file in the Solution Explorer pane. Use the context menu and select the "View Code" menu option. This will show the XML within the Connection Manager file.

In the XML, there was this tag:

<DTS:PropertyExpression
    DTS:Name="ServerName">@[$Project::my_server_name]</DTS:PropertyExpression>

The ServerName property was set via an expression!

This fact is not easily discernible via the Solution Explorer tree itself. When the conmgr file is selected, the Properties pane only displays the File Name and Full Path properties.

When you open a package that uses the Connection Manager, and select that Connection Manager within the package editor pane, the properties pane shows many other properties, including Expressions. The ServerName property will be visible in the Expressions list only if it has been set.

There are two ways to resolve this issue:

Within the Visual Studio project, open a package. Select the Connection Manager from the package editor pane, and delete the Property Expression from the Properties pane.
Remove the PropertyExpression tag from the .conmgr file with a text editor.

Either way, once the ServerName property is no longer set by an expression within the connection manager definition file, it is exposed as a configurable parameter in the Configure dialog box in SSMS 2012.


How to delete row from table later than date?

Restore the backup you have from 15.12.2014 to a new database and then you can delete the rows from your production database that does not exist in your restored backup database.

There is no hidden datetime information in the table that you can use.


Try with this query does the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

Info:

See this closely related answer.

pg_stat_activity is a view in the pg_catalog schema.

You can query it by SELECTing from it like any other table, eg SELECT * FROM pg_stat_activity. The manual page you linked to explains its columns.

You'll sometimes find yourself wanting to join on other tables like pg_class (tables), pg_namespace (schemas), etc.

pg_stat_activity does not expose information about back-end memory use. You need to use operating-system level facilities for that. It does tell you the process ID, active user, currently running query, activity status, time the last query started, etc. It's good for identifying long-running idle in transaction sessions, very long running queries, etc.

Frankly, PostgreSQL's built-in monitoring is rather rudimentary. It's one of the areas that's not that exciting to work on, and commercial clients aren't often willing to fund it. Most people couple tools like check_postgres with Icinga and Munin, or use Zabbix or other external monitoring agents.

In your case it sounds like you really want pg_stat_statements, and/or PgBadger log analysis with suitable logging settings and possibly the auto_explain module.

Credits to: Craig Ringer


MySQL won't start, no log ins /var/logs/mysql*

As the log indicates, you might have changed the values of the following parameters:

key_buffer_size
read_buffer_size
sort_buffer_size
max_threads

If you have changed one of these values, try to revert the change and start the MySQL server again.

If you have not changed the values, calculate the memory usage by:

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads

If you find the system installed memory is sufficient, you need to check if there is a corrupted table in your databases:

[mysqld]
innodb_force_recovery = 6

Then, to find out which database table is corrupted:

# mysqlcheck --all-databases

Write down all the tables listed as corrupted, you have to export the corrupted tables by:

# mysqldump -u{username} -p{password} {database_name} {table_name} > table_backup.sql

After that, you can drop the table in Mysql console:

Mysql> drop table {corrupted_table_name};

Then, we have to change back the Mysql configuration:

[mysqld]
innodb_force_recovery = 0

The MySQL server should be able to start up with normal operation mode.

Next, we have to import the data of corrupted tables:

# mysql -u{username} -p{password} {database_name} < table_backup.sql

If everything runs fine, you should have your table data back and working Mysql Server.

Reference: InnoDB table recovery step-by-step-guide


How to use database users as a foreign key field in any table

You cannot create a foreign key that references an Oracle data dictionary table.  So assuming the "users on that database" are Oracle database users that exist in dba_users, you can't reference dba_users in your constraint.

You can, however, create your own copy of the dba_users table and create a job (using dbms_scheduler or dbms_job or some external scheduler) that will periodically copy the data from dba_users to your copy of the table.  You can then create a primary key constraint on the username column in your copy and create a foreign key constraint that references your copy of the table.  You may need to work with the DBA team to ensure that your copy of the table gets refreshed as part of any process that creates new database users depending on how soon after a user is created you would expect rows to be created in the child table.


How to control the excessive use of ram by SQL Server?

That is by design.  SQL Server is supposed to use all the available memory, as it is storing more and more data in memory so that it doesn't need to go back to the disk to get the same memory over and over.

If you need to limit the amount of memory that a single SQL Server instance is using you can do this in SQL Server Management Studio by right clicking on the instance name within the Object Explorer and selecting properties.  Then select the memory tab and set the maximum amount of memory that the SQL Server will be allowed to use.  Now this isn't going to limit all aspects of SQL Server to that amount of memory.  This only controls the buffer pool and the execution plan cache.  Things like CLR, Full Text, the actual memory used by the SQL Server exe files, SQL Agent, extended stored procedures, etc. aren't controlled by this setting.  However these other things typically don't need all that much memory, it's the buffer pool and the execution plan cache which need the bulk of the memory.

If you set this setting on one instance you'll want to set it on all the instances so that they don't step on each other.


Here's a powershell script I wrote for checking connectivity to SQL servers as you are using the default port (1433) all you need to do is pass in the server name.  If the connection fails the exception should give you a clue as to why.

e.g.
CheckSql.ps1 server\instance

CheckSql.ps1

param([Parameter(Mandatory=$true,ValueFromPipeline=$true)][string]$serverInstance);

$connectionString = "Data Source={0};Integrated Security=true;Application Name=CheckSQL.ps1" -f $serverInstance;
$sqlConn = new-object("Data.SqlClient.SQLConnection") $connectionString;

Write-Host $connectionString

try {
    $sqlConn.Open();
    $sqlConn.Close();
    Write-Host "Connected OK"-foregroundcolor white -backgroundcolor green
}
catch {
    Write-Host $_ -foregroundcolor white -backgroundcolor red
}
finally {
    $sqlConn.Dispose();
}


Policies RE database triggers in well-designed applications?

Sounds like we hold similar views on trigger usage but I'm very interested to hear the opposite opinions from other DBAs. Personally, I view them as a last resort and have only implemented triggers where there is no other option. The exception being INSTEAD OF triggers against views.

Triggers are often the only option when you're enhancing a 3rd party solution and the extension points are not exposed by the systems API. Where I have full access to source, I've not yet encountered a situation where a trigger was my first thought.

Audit is a good example. In a 3rd party application, triggers may be the only choice. If you have access to source, it is certainly not the only option, though it may be lowest risk/cheapest/easiest (delete as applicable) option. If the database API is well designed, there will be other entry points to add audit.

Despite my thoughts on triggers, I wouldn't impose the strict policy you are suggesting. In my opinion, the DBA vs Developer mentality that persists in many organisations arises from the way this type of guidance is perceived. Try to encourage consultation, rather than dictating.


optimizing or estimating postgresql table size on disk

The best way is to run a little benchmark:

INSERT INTO tb_user
   (user_firstname, user_familyname, user_email, user_gender, user_crtime)
SELECT substr(md5(random()::text), 1, 15),
       substr(md5(random()::text), 1, 15),
       md5(random()::text),
       'F',
       current_date
FROM generate_series(1, 1000000);

SELECT pg_size_pretty(pg_total_relation_size('tb_user'));

 pg_size_pretty
----------------
 126 MB
(1 row)

A text or varchar that is short enough (which the ones in the table certainly are) will consume one byte more than the number of bytes in the string.

There is some overhead caused by block and tuples headers and other metadata, as well as by fragmentation (some empty space will remain in each 8kB block). And then the index that supports the primary key is also sizeable:

\di+ tb_user_pkey
                            List of relations
 Schema  |     Name     | Type  |  Owner  |  Table  | Size  | Description
---------+--------------+-------+---------+---------+-------+-------------
 laurenz | tb_user_pkey | index | laurenz | tb_user | 21 MB |
(1 row)

Some additional hints:

You can save a little space by having user_crtime in the second position. Otherwise, you might lose up to three bytes to padding.
Use bigint for the primary key so that you can never run out of values. Trying to save on these 4 bytes is usually not a good idea.


Change Linux Family for Postgres Database

The immediate problem you are going to hit is that there will be a different version of the C library, and your collations will change. So, unless you are using the C collation, either move the database with pg_dumpall/restore or , if you copy the data directory, REINDEX all indexes that are defined on columns with a string data type.
Also make sure that both systems are either 32-bit or 64-bit when copying the data directory.
And upgrading PostgreSQL is not a "tricky political enterprise-world question". After all, PostgreSQL doesn't get any money if you upgrade. If you are not running a supported release, you won't get updates, so you will be exposed to data corruption bugs.
Of course you want to have the same release as the client. Your task is to drive the danger home to the customer.


What kind of parametrization/external programs could be used to
  achieve having such a name as default, when for example using a create
  table statements which mentions the fk but does not explicitly provide
  a constraint name?

There's no way to change the default name generated when the DDL doesn't contain a name.  The only thing you can do is to run a batch process to identify non-compliant objects and perhaps drop and recreate them.


How to join to a single list of IDs from two different tables

You contradict yourself.  First you state you would like to treat humans and organization as one group and then you state using one table would cause queries to require more code.

Three tables with one master:
With two tables rather than funnel to, the constituentID is the master the [human].[id_number] and [organization].[id_number] would just be FK.  So you would insert into constituent first to get the master ID.  With this it would be a pain to enforce the same ID not in both [human] and [organization].  If you are going to put a type in constituent to enforce the two other tables then you might as well combine in the first place.

Two tables:
You could have two independent [human] [organization] tables and combine them with with a view with a union but that would not be my preference.

One table:
Not having a last name is not a big difference.  They both have address,  phone number, and EIN/SSN.  I would share name / last name and let first name be null.  A person is still valid without a first name so do you really need to enforce it.  ID is the PK and is an identity.  Type - person or organization is not part of the PK.   Maybe SSN/EIN as PK but then that would not allow for one constituent to have more than one account.  Then you can have views for person and organization.

In summary I don't think a null (enforced or not) is that big of a deal.  Compared to separate tables.


There is no such thing as a "nested table" in Postgres, but it seems you just want a JSON representation of the group assignments for each user.
The following query does this:
select u.user_id,
       u.title,
       jsonb_build_object(
              'user_id', u.user_id,
              'title', u.title,
              'groups', jsonb_agg(jsonb_build_object('group_id', g.group_id, 'user_type', m.user_type))) as groups
from members m
  join users u on u.user_id = m.user_id
  join groups g on g.group_id = m.group_id
group by  u.user_id, u.title;

Given your sample data, the above returns:
user_id | title  | groups
--------+--------+-----------------------------------------------------------------------------------------------------------------------------------------
U_1     | User 1 | {"title": "User 1", "groups": [{"group_id": "G_1", "user_type": "admin"}, {"group_id": "G_2", "user_type": "admin"}], "user_id": "U_1"}
U_2     | User 2 | {"title": "User 2", "groups": [{"group_id": "G_1", "user_type": "member"}, {"group_id": "G_3", "user_type": "admin"}], "user_id": "U_2"}

Online example


Looking to find a way to determine replicated objects from subscriber

On the subscriber database you can run this.

SELECT *
  FROM [SubscribeDBName].[dbo].[MSreplication_objects]


SQL Server doesn't have any direct analog of MySQL's query cache, i.e. an in-memory cache of actual query results. Data and compiled query plans will be cached in memory, however.

If you've got a particularly expensive query with a lot of joins, you may want to look into creating an indexed view.


Postgres long running query not blocking table operations

Ruby will close the cursor when it is done iterating over it.  If you already know that that will take a long time, then there is no reason to think it isn't working correctly.  Can you set log_statement ='all' to see when the CLOSE and COMMIT arrive if you want to double check.

Using single row mode won't change the situation.  It will still hold locks until it is done.  It will just change the chunk size fetched from the server from 1000 (or whatever the cursor uses) to 1.

I know that the dblink function does spool its results to disk, so that it can release locks (on the remote end) as quickly as possible. It is the only thing I know of that does that on the server side. Perhaps you can use dblink to loop back to the same database server and run the query through it, then use single row mode to get the results, although I don't know how you get Ruby to make use of single-row-mode.  I've only seen it done from C.  You can probably get Ruby to use a cursor with a dblink query.


SQL Server OR operator causing massive loop join

There are some redundant JOINs between the 2 tables, and using too many OR conditions with nested queries sometimes overwhelms the SQL Query Optimiser.

After cleaning up and organising the code, I've come up with the following (hopefully equivalent) alternative:

DECLARE @a INT
    ,@b BIT
    ,@c INT
    ,@d INT
    ,@e INT;

SELECT SUM(CNT) AS [C]
FROM (
    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable]
    WHERE 1 = 1
        AND [ACTIVE] = 1
        AND [INS_ID] = @a
        AND [QUEUED_TO_SEND] = @b
        AND [OWNER_USER_ID] <> @c
        AND (
            [OWNER_USER_ID] IN (- 1)
            OR N'Allusers' = [VISIBLE_TO]
            OR [OWNER_USER_ID] = @d
            )

    UNION ALL

    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable] AS [table1]
    WHERE 1 = 1
        AND [table1].[ACTIVE] = 1
        AND [table1].[INS_ID] = @a
        AND [table1].[QUEUED_TO_SEND] = @b
        AND [table1].[OWNER_USER_ID] <> @c
        AND EXISTS (
            SELECT 1 AS [C]
            FROM [dbo].[TeamMembers] AS [table2]
            WHERE [table2].[INS_ID] = [table1].[INS_ID]
                AND [table2].[TEAM_ID] = [table1].[VISIBLE_TEAM_ID]
                AND [table2].[MEMBER_USER_ID] = @d
            )
    ) XYZ
OPTION(RECOMPILE);

Let us know how it works and maybe post the Actual Execution Plan, as some commenters requested.


The DB2 Docs suggest to me that returning a dynamic result set is done via a cursor with a WITH RETURN clause rather than a simple select statement.


For your first question, that's an appropriate approach to balancing the Transactions. Every monetary change should be recorded as a Transaction. So at whatever rate you consolidate things like fraud, e.g. once a day (or as frequent as every minute, doesn't really matter as long as you record the Transaction for the consolidated amount every time) will result in how many of those kinds of Transactions to be generated.
To your second question, there's nothing inherently wrong with it being a one-to-many relationship, and business context aside, that's how I'd first imagine it. Bringing business context back in, normally a financial system in this space would handle these things through journaling. The concept of a journal is usually represented by two tables, JournalHeader and JournalLines. The JournalHeader represents generic information such as who created it and when it was created, whereas the JournalLines represent the individual Transactions consolidated via settlement. Usually there's a field on the JournalLines table that ties back to the original source of the transactions that were consolidated (generally another table, e.g. if there were SalesOrders that generated Transactions, the JournalLines would tie back to the original SalesOrder they consolidated). This relationship is one-to-many in nature, and standard.
I'm a little fuzzy on the exact implementation of such (as I haven't worked with that kind of finance in a while), but hopefully that helps paint the picture. Long story short, it is ok and normal practice to have a one-on-many relationship between the settling Transaction to its settled Transactions.


Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

At our company we have that exact setup. When you create a CLR assembly a binary representation of the assembly is stored within the database that you create it in. This enables you to take it with you (and even script it out) should you move the database at any point in time.

A couple of months back our data center got flooded - filling several servers full of water. When I rebuilt them I only used the backups of the db that had been taken the night before. So far we have had no issues.. (touch wood!)

I am not sure if this is the right thing to do from a security perspective but the way we grant access to the CLR procs etc is to create a role within the shared database, and then add users from other databases to that role. The role is then granted execute on the CLR procs.

There may be access issues if the CLR is trying to do things like access resources outside of the database that it is contained within but you can set the permission on the assembly when you create it. The below link has a lot more information regarding permissions than I can explain here though:

http://msdn.microsoft.com/en-us/library/ms345101.aspx

I hope that this helps you.


SQLServer case when statement doesn't work like that. You need a condition, and then a result. Your query only has a condition.

You could rewrite your query like that :

Update #master12
Set [cg] = [dasboot]
from #master12 m
join [#check1] c1 on m.[uid] = c1.[uid]
WHERE m.[cg] IS NULL

Update #master12
Set [cg] = [redbluegreen]
from #master12 m
join [#check2] c2 on m.[uid] = c2.[uid]
WHERE m.[cg] IS NULL

Or maybe in a single one like :

Update #master12
Set [cg] = isnull([dasboot], [redbluegreen])
from #master12 m
left join [#check1] c1 on m.[uid] = c1.[uid]
left join [#check2] c2 on m.[uid] = c2.[uid]
WHERE m.[cg] IS NULL

But it will also depend on what you want when you have a uid value present in both tables #check1 and #check2 and which value you want to be used for the update.


According to this Microsoft KB article, SQL Server 2005 (in any form, even with all the Service Packs installed) is not supported on Windows 8.

SQL Server 2008 is supported, as long as you install Service Pack 3.
SQL Server 2008 R2 is supported, as long as you install Service Pack
1.
SQL Server 2012 is supported with any version.


In MongoDB, documents stored in a collection require a unique _id
  field that acts as a primary key. MongoDB uses ObjectIds as the
  default value for the _id field if the _id field is not specified;
  i.e. if a document does not contain a top-level _id field, the MongoDB
  driver adds the _id field that holds an ObjectId. In addition, if the
  mongod receives a document to insert that does not contain an _id
  field, mongod will add the _id field that holds an ObjectId.

ObjectId is a 12-byte BSON type, constructed using:

a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
a 2-byte process id, and
a 3-byte counter, starting with a random value.

MongoDB clients should add an _id field with a unique ObjectId. Using ObjectIds for the _id field provides the following additional benefits:

In the mongo shell, you can access the creation time of the ObjectId,
using the getTimestamp() method.
Sorting on an _id field that stores
ObjectId values is roughly equivalent to sorting by creation time.

Alternatives-
The following are common options for storing values for _id:

Use an ObjectId.
Use a natural unique identifier, if available. This saves space and avoids an additional index.
Generate an auto-incrementing number. See Create an Auto-Incrementing Sequence Field.
Generate a UUID in your application code. For a more efficient storage of the UUID values in the collection and in the _id index, store the UUID as a value of the BSON BinData type.
Index keys that are of the BinData type are more efficiently stored in the index if:
the binary subtype value is in the range of 0-7 or 128-135, and
the length of the byte array is: 0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16, 20, 24, or 32.
Use your driver’s BSON UUID facility to generate UUIDs. Be aware that driver implementations may implement UUID serialization and deserialization logic differently, which may not be fully compatible with other drivers. See your driver documentation for information concerning UUID interoperability.

for more detail-
See MongoDB


Change the order of a table according to preference in another table

It is too long for a comment.

Well, that's the problem, then. I take data from A, then apply overrides from B to it and end up with a cycle. I think that there is a cycle, you don't think that there should be a cycle.

So, there is a bigger problem with understanding how overrides should work.

My logic is the following.

Data from A can be represented in the same format as B:

Name   Food    PreferenceOrder
Jon    Burger  1
Jon    Coke    2
Jon    Pizza   3
Jon    Fries   4

So, for Jon we have this chain originally:

Burger -> Coke -> Pizza -> Fries

OR this set of rules:

a) Burger -> Coke
b) Coke -> Pizza
c) Pizza -> Fries

Then we have overrides from B:

Name   Food    PreferredOverFood
Jon    Pizza   Fries
Jon    Coke    Burger
Jon    Fries   Coke

OR this set of rules:

d) Pizza -> Fries
e) Coke -> Burger
f) Fries -> Coke

Now we merge two sets of rules where second set overrides the first set if there is a conflict.

Rule (d) is the same as (c), so (c) is removed.
Rule (e) overrides (a), so (a) is removed.
Rule (f) is a new rule that doesn't exist in the first set.
If any rules from the first set are still there, they are kept.

Final result of rules:

d) Pizza -> Fries
e) Coke -> Burger
f) Fries -> Coke
b) Coke -> Pizza

This is a directional graph. If it represents a tree, it can be ordered. It there are cycles, you need to decide what to do with them. If there are several disconnected trees, you can order each tree individually and then decide the order between trees.

In your example the graph looks like this (if we start from Coke).

    (b)      (d)      (f)
Coke -> Pizza -> Fries -> Coke -> ... the cycle (bdf) ...
   |
   | (e)
   |
   -> Burger


Check ruby gem and SQLite3 for FTS3 or FTS4 support

The documentation states:

  Note that enabling FTS3 also makes FTS4 available. There is not a
  separate SQLITE_ENABLE_FTS4 compile-time option. A build of SQLite
  either supports both FTS3 and FTS4 or it supports neither.

So if FTS3 works, so does FTS4.

Doesn't look like it's possible to tell what options sqlite was compiled with according to this SO answer.


Connect Oracle Database 12c via SQL Developer when it is in restricted mode

The above connection string should work with the addition of (UR=A). Seems like your SQL Developer connection is configured differently.

Alternatively, you can define the connection type as Advanced, and use the below custom JDBC url:

jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.37)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.37)(PORT = 1521)) (CONNECT_DATA = (UR=A) (SERVER = DEDICATED) (SERVICE_NAME = OBIEE) ) )