ORDER BY on field that isn't in a SELECT using DISTINCT or GROUP BY to get distinct records Migrated from MySQL where this query worked:

SELECT DISTINCT
    code,
    title,
    country
FROM
    data_a
ORDER BY
    sortorder

Basically, get a list and sort it by a user/company defined sortorder that is not based on code, title, or country.

Now that I'm in Postgres, this query does not work and gets a:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 12:  sortorder
          ^
SQL state: 42P10
Character: 135

Doing this however returns duplicates because there are additional fields not listed here that make that entire record unique. I just want the unique values from code, title, or country in order of the sortorder.

I tried to do a sub-query thinking I could sort it in the sub-query and bring it into the parent query already sorted, but it just comes over in non particular order... well, maybe it is particular, but it isn't the order we want.

SELECT
    DISTINCT s.*
FROM
    (
        SELECT
            code,
            title,
            country
        FROM
            data_a
        GROUP BY
            code,
            title,
            country,
            sortorder
        ORDER BY
            sortorder
    ) s;

Any suggestions for how to do this?

Actually, did manage to figure it out with:

SELECT
    code,
    title,
    country,
    MIN(sortorder)
FROM
    data_a
GROUP BY
    code,
    title,
    country
ORDER BY
    4


MySQL: OPTIMIZE after TRUNCATE? Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a helpful action?

MyISAM
You should perform this as follows:
TRUNCATE TABLE mydb.myisam_mytable;
ALTER TABLE mydb.myisam_table DISABLE KEYS;
LOAD DATA INFILE ... INTO mydb.myisam_table ... ;
ALTER TABLE mydb.myisam_table ENABLE KEYS;
ANALYZE TABLE mydb.myisam_table;

The DISABLE KEYS will allow the LOAD DATA INFILE perform a linear building of the PRIMARY KEY and any UNIQUE indexes.
The ENABLE KEYS will perform a linear building of all non-unique (secondary) indexes.
Skip doing DISABLE KEYS and ENABLE KEYS if there are no secondary indexes.
ANALYZE TABLE compiles index statistics (the last step of an OPTIMIZE TABLE)
InnoDB
OPTIMIZE TABLE would essentially do a reload of the table a second time. That's like doing LOAD DATA INFILE twice.
In other words doing this:
LOAD DATA INFILE ... INTO mydb.innodb_table ... ;
OPTIMIZE TABLE mydb.innodb_table;

would mechanically behave like
LOAD DATA INFILE ... INTO mydb.innodb_table ... ;
ALTER TABLE mydb.innodb_table ENGINE=InnoDB;

No real benefit in the case of InnoDB. Just the LOAD DATA INFILE would be adequate.


why a convert from varchar do date fails on a different row everytime it runs? I have a database with a field like this (Dont know why it's varchar):

  19950105

i'm trying to convert it to DATE as DDMMYYYY.

I could do this with:

convert(varchar(10), cast(The_ugly_field as date),103)

Then my date is:

  05/01/1995

1) Why I can't convert it directly to date? if I use:

select convert(date,The_ugly_field,103) from Ugly_Table

it returns 1900-01-01 and it doesnt respect the 103. any number there returns the same format YYYY-MM-DD.

2)Now the real question. when I run

select  convert(varchar(10), cast(The_ugly_field as date),103)
From Ugly_Table
order by The_ugly_field

after some time it returns this error:

  Msg 241, Level 16, State 1, Line 29  Conversion failed when converting
  date and/or time from character string.

but the funny part is, it doesnt looks like the errors appears in the same row. If I run this code like 4 times, every time it returns the error, it says a different row. I mean, I execute it, then in the "results" tab I can see that it returned 1.234.555 rows. If I run it again, after the error I return to the "result" table and now the query ran until 1.556.345.

and please. how the hell can I convert this 20140101 in a elegant way to 2014/31/12? Running convert(date,E1_EMISSAO,103) doesn't seems to respect the 103 and it returns 2017-02-24 and not 24/02/2017.

tons of questions and answers but it's all about a simple convert().

The reason the error occurs after varying numbers of rows are processed is because the conversion isn't done in a deterministic order.  It depends on the execution plan.

And especially if the query is parallel (given the number of rows, I expect that it is), rows will be distributed differently across threads on each run and might hit the "Compute Scalar" in a different order each time.

Other reasons are that different indexes might be used (resulting in the rows being ordered differently), although that seems less likely given the static nature of the query.

The reason the formatting parameter doesn't work when you do this:

select convert(date,The_ugly_field,103) from Ugly_Table

...is because the 103 style you're trying to use is for converting a date or datetime to a character data type.  Not a varchar to a date (which is what that CONVERT statement does).  In this case, SQL Server is just displaying the date in the format that is the default for the instance.

You'll need to manually track down the "bad" data that can't be converted:

optional: add a MAXDOP 1 hint to the query to make the order of the data returned slightly more deterministic
look at that last rows that were successfully returned by the query
perform your SELECT without the convert and manually inspect the values in that area
Correct or remove the bad data if possible
Add a CASE statement to the SELECT to avoid trying to convert bad values, like this:

select  CASE WHEN LEN(The_ugly_field) < 8 THEN '01/01/1900' ELSE convert(varchar(10), cast(The_ugly_field as date),103)  END
From Ugly_Table
order by The_ugly_field

I would suggest TRY_CONVERT, but that is only available on SQL Server 2012 and higher.


Copy complete structure of a table in MySQL without using a backup? In MySQL, how can someone copy a table structure without using a backup?

       jcho360> show create table actor\G
*************************** 1. row ***************************
           Table: actor
    Create Table: CREATE TABLE `actor` (
      `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
      `first_name` varchar(45) NOT NULL,
      `last_name` varchar(45) NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`actor_id`),
      KEY `idx_actor_last_name` (`last_name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    jcho360> create table actor_copy select * from actor;
    Query OK, 200 rows affected (0.07 sec)
    Records: 200  Duplicates: 0  Warnings: 0

 jcho360> show create table actor_copy\G
    *************************** 1. row ***************************
           Table: actor_copy
    Create Table: CREATE TABLE `actor_copy` (
      `actor_id` smallint(5) unsigned NOT NULL DEFAULT '0',
      `first_name` varchar(45) CHARACTER SET utf8 NOT NULL,
      `last_name` varchar(45) CHARACTER SET utf8 NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

when I do something like that I lose the structure, like PK, FK, index , ETC.

You should be able to do :

CREATE TABLE `actor_copy` LIKE `actor`;

to get the structure, complete with indexes. However Foreign Keys will still need to be created manually.

The above will give you structure only. Then you can proceed to copy the data if you wish:

INSERT INTO `actor_copy` SELECT * FROM `actor`;


Can I have different publishers and one subscriber in replication? I have the same databases in three cities which each database have three tables:

1- tbl_worker
2-tbl_workshop
3-tbl_users
and cities are:city1,city2,city3

city2 and city3 insert informations in their databases.

The city1 database should be updated every 1 hour from data inserted in city1 and city2 databases.

For example, the data inserted in tbl_kargah in city1 and city2 databases should be merged and inserted into tbl_kargah of city1 database every 1 hour.

Will using replication cover my requirement and which replication?

As I read in MSDN the publisher distribute data in subscribers, but I have different publishers and 1 subscriber.

Yes it is possible to have many publishers and only one subscriber. You can also have one publisher and many subscribers. So in your case you will be able to publish from City1 and City2 and subscribe only City1 on those publishers. You can use this link to have an overview of the replication engine and how to setup it. http://technet.microsoft.com/en-us/library/ms151198.aspx

Hope this was helpful


MySQL query takes too long I have made this query:

SELECT COUNT(*)
from sat_msgPos
where  TIMESTAMPDIFF(MINUTE,tmUpdate,NOW())<=30
  and idTrigger = 9
  and idVeh = 12590;

and an index (idVeh,tmUpdate) on a big table. It's much better after I created the index, but I want to make it faster.

Does anybody have a suggestion?

The WHERE codition has a function that acts on a column. This means that even if an index is used, it will have to do a complete index scan. In such cases, it is good (if possible) to rewrite the condition so the column is used bare, and any function is applied to constants or run-time constants (like NOW()). This way an index can be used with an index seek - and a partial index scan, only rows that are needed will be scanned.

Try rewriting

TIMESTAMPDIFF(MINUTE, tmUpdate, NOW()) <= 30

to:

timestampadd(MINUTE, -30, now()) >= tmUpdate

and add an index on either sat_msgPos (idVeh, idTrigger, tmUpdate) or sat_msgPos (idTrigger, idVeh, tmUpdate).


Will column size change when trying to use single unicode character in place of multiple ASCII characters? I have a column in utf8_unicode_ci collation for text type in MySQL.

The content is in Unicode, I have used tags like {br}, {v}, {t}, {h} etc.., throught the database. I am now thinking of changing these to single characters from Unicode table like ⓑ, ⓥ, ⓣ, Ⓗ and so on and so forth.

Will this have any effect on the size of the table? (Increase or decrease) Should I be worried about any other side effect? Will this have any effect if type of the column was varchar?

"{br}" currently takes 4 bytes in ascii, latin1, utf8, utf8mb4 and perhaps other CHARACTER SETs.  (The "COLLATION" is irrelevant.)

ⓑ is the 3-byte utf8 encoding (in hex) E2 93 91.  See http://www.fileformat.info/info/unicode/char/24d1/index.htm

The size of the table will not change much.  If those things are all that you have, then each {br} will shrink by 1 byte.

If this is an optimization, it is probably not worth doing.

The field (either TEXT or VARCHAR) must be declared CHARACTER SET utf8 (or utf8mb4).  The client must be using utf8.  You should probably do SET NAMES utf8.

There are obscure advantages to VARCHAR over TEXT.  Note that the VARCHAR(100) can hold 100 characters; with CHARSET utf8, that is up to 300 bytes.  {br} is 4 chars and 4 bytes; ⓑ is 1 char and 3 bytes.  The (100) counts chars; disk space counts bytes.

See the following blog for too much more info:
http://mysql.rjweb.org/doc.php/charcoll


How do I correctly use %p and %f in postgresql.conf to make the Write Ahead Log? I am attempting to modify postgresql.conf (Postgre SQL 10.0 under Ubuntu 18.04 VPS) to setup the Write Ahead Log (WAL).

AIUI, the commands should be:

wal_level = replica             # minimal, replica, or logical
archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'cp %p /var/lib/postgresql/10/main/pg_wal/%f'
archive_timeout = 600           # force a logfile segment switch after this
                                # number of seconds; 0 disables

AIUI, %p references the database file (to be archived) and %f references the filename that will contain the copy of said database file.

In this case: I am saying to copy every 5 minutes the database file to the /pg_wal directory.

However, this is clearly not correct as the error log files shows:

cp: 'pg_wal/000000010000000000000002' and '/var/lib/postgresql/10/main/pg_wal/000000010000000000000002' are the same file
2020-02-16 21:01:05.857 UTC [20707] LOG:  archive command failed with exit code 1
2020-02-16 21:01:05.857 UTC [20707] DETAIL:  The failed archive command was: cp pg_wal/000000010000000000000002 /var/lib/postgresql/10/main/pg_wal/000000010000000000000002

So I am not sure how %p and/or %f are supposed to be used. If someone could present the syntax, I would be most grateful.

You need to copy the WAL segment somewhere else, not to its original location.

“Somewhere else” in this context means different storage on a different physical machine. So cp is only a good method of archival if the destination is an NFS mount or similar. Otherwise, scp would be better.

You have to make as sure as you can that any trouble that befalls the original database won't affect the WAL archives.

It is also a good idea to test if the cp would overwrite anything, like the example in the documentation does. Otherwise, if two clusters archive to the same directory by mistake, you could end up with data loss.


Should I use Index or Unique Key for a non PK Uniqueidentifier? Using Sql Server 2014, I have several tables where the PK field is an int, but I also have a UniqueIdentifier field that I need to query by.  This field will always be unique.

I am adding indexes for these and I have the option of choosing Index or Unique Key.

Is there any benefit to choosing one or the other?

Unique keys are indexes.  If your values are guaranteed to be unique, this is the best choice.

  Unique Key: Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.


PGAdmin3 connection with non standard SSH port and SSH tunnel Hi I am trying to connect to Postgres from pgAdmin3 using SSH tunneling. However I am not using standard port 22. How can I do SSH tunneling using a non standard port?

You could setup the ssh connection and tunnel outside pgAdmin and just let pgAdmin connect to the localport.

On windows you could use putty for this on unixes you can use ssh

If you use 15432 as localport then you can let pgAdmin connect to localhost with port 15432 and you leave the SSH tunnel option in pgAdmin OFF!


scheduling medical appointment challenge I'm working on a medical appointment scheduling system project.

What is the best way to get the first free time of a doctor and schedule it for the patient who just asked for an appointment?

database structure:

appointments table:

doctor_id
patient_id
appointment_start_time
appointment_end_time

doctors_working_hours table:

doctor_id
day_of_week
start_time
end_time

When a patient asks for an appointment with a doctor, I want to schedule the first next free time of the doctor according to the doctor's working hours.

the default duration for each appointment is 15 minutes.

What is the most efficient way to do this?

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.


PostgreSQL CASE expression how to write a case expression with Postgres?  I have this

case
  when rc.stat = 0 Then unpaid
  when rc.stat = 1 Then paid
  when rc.stat = 2 Then dispute
  else rc.stat
end

but when I go to run the statement I get an error of

Error: column "unpaid" does not exist

I have also tried double quotes and brackets around the text values, but all of those options give me errors.  Just to note, the values unpaid, paid, dispute are NOT fields in the database, but text values I want to write.

You need a string constant there, e.g. 'unpaid'.

Because all branches of a CASE expression must return the same data type, you will also need to cast the stat column to text as well (assuming it's some kind of number as you compare it to one)

case
  when rc.stat = 0 Then 'unpaid'
  when rc.stat = 1 Then 'paid'
  when rc.stat = 2 Then 'dispute'
  else rc.stat::text
end

As you only use = as the comparison operator, you can simplify that expression to:

case rc.stat
  when 0 Then 'unpaid'
  when 1 Then 'paid'
  when 2 Then 'dispute'
  else rc.stat::text
end


Bizarre crash in T-SQL - what is it? What would be a typical situation under which the following error would occur, and what does it mean?

Also note that I find myself disconnected from the server when the query fails (but I have not myself observed that).

Location:     "xact.cpp":4253
Expression:   !m_parNestedXactCnt
SPID:         56
Process ID:   2208
Description:  Trying to use the transaction while there are 1 parallel nested xacts outstanding
Location:     "xact.cpp":4362
Expression:   !m_parNestedXactCnt
SPID:         56
Process ID:   2208
Description:  Trying to use the transaction while there are 1 parallel nested xacts outstanding
Msg 3624, Level 20, State 1, Line 131
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

This looks like a failed assertion in SQL Server code.  Basically, you've uncovered a bug in SQL Server.  You could submit it to Microsoft.

A quick solution is to add option (maxdop 1) at the end of your query; this instructs SQL Server not to spread the query over multiple processors (aka parallelizing.)


master slave crashing daily I have implemented a master slave config several times and each time the process goes well and works for a day or two but soon bombs out saying that replication stopped because such and such table does not exist.

relevant part of my.cnf:

[client]
port                            = 3306
socket                          = /var/lib/mysql/mysql.sock

[mysqld]
server-id                       = 2
master-host                     = 10.10.10.33
master-connect-retry            = 60
master-user                     = repl
master-password                 = redacted
relay-log                       = /var/lib/mysql/mysql-relay-bin
relay-log-index                 = /var/lib/mysql/mysql-relay-bin.index
log-error                       = /var/lib/mysql/mysql.err
master-info-file                = /var/lib/mysql/mysql-master.info
relay-log-info-file             = /var/lib/mysql/mysql-relay-log.info
log_bin                         = /var/lib/mysql/mysql-bin.log
binlog_format                   = ROW
datadir                         = /var/lib/mysql
pid-file                        = /var/lib/mysql/mysql.pid
port                            = 3306
socket                          = /var/lib/mysql/mysql.sock
binlog_format                   = row
max_connections                 = 200
max_connect_errors              = 1000
key_buffer_size                 = 256M

There are numerous instances where the slave reports that such and such table doesnt exist in a given database but in fact it does exist.

Any ideas on what may be causing this or ways to improve stability ? In case it matters, this mysql server is housing 125 databases and is about 60gb in size total.

First you should check the error logs & post for clarification.
This can happen doue to-

Your slave database is differ to its master when replication start.
So before replication sync both the server.

If your replication is statement type & replicate some specific database. ex-

use not_replica;
alter table replica.test ....
use replica;
insert into replica.test ...

here alter command for test would not replicate. because you are using not_replica database. after that insert statement would fail & replication stopped.


TOTAL column or VIEW that computes? I've got a couple of tables to track volunteer workers at our non-profit.

One stores Volunteers and tracks information about a volunteer such as the person's name, a contact number, primary location and such.

Another table stores Tasks and tracks specific work done by the volunteers.  It references the Volunteers table by ID to allow me to, say, pull back all of the tasks performed by Pete.

Tasks have number of hours so we can track how much work was done on tasks at a certain location, how many total hours were volunteered at that location, etc.

I'd like to keep track of the cumulative amount of volunteer time for each volunteer and this where a design decision needs to be made.

I could add a column to the Volunteers table called VolunteerHours and then add triggers to the Tasks table to call a stored procedure to recompute the volunteer hours when rows are added/updated/deleted on the Tasks table.

Alternatively, I've considered creating a view that would be used to retrieve the totals on demand.

create view VolunteerSummary
as
    select v.VolunteerID, SUM(TaskVolunteerMinutes) as VolunteerTotal
    from Volunteer v
    join Tasks t on t.VolunteerID = v.VolunteerID
    group by v.VolunteerID

go

select * from VolunteerSummary

The view approach is attractive to me because I'm not storing a total column on the table, rather computing the total when I need it.

Assumptions I have made about these tables:

Fairly small (< 1000) number of volunteers
Most volunteers volunteer less than 12 times a calendar year (once a month)

Based on my assumptions and description, is there a good reason to go with the column/trigger/stored procedure route over the view?

I would go with the view for a few reasons:

It is obvious how the sum is generated with a view and it would always be correct.
You won't be able to fully trust the data of a updated column. ie life happens.  (The importance of this depends on the consequences of  bad data.)
This is essentially a premature optimization in a relatively lightly used system.

I don't think there is a good reason given the circumstances you described to go with the column/trigger/stored proc at this time.


I am not able to get the result when joining two tables with where clause with OR condition I have two tables projects and project_user_cross. I want to get the projects if the user id exists in either of the two tables

Here is the projects table

Id | project_manager_id | name | description | created_at | updated_at | progress
1  | 1                  | p 1  | lorem ipsum | 2019-9-16  |            | 0

Here is the project_user_cross table (empty)

Id | project_id  | user_id

Here is the query I tried:

SELECT *
FROM projects p
INNER JOIN project_user_cross c ON c.project_id = p.id
WHERE p.project_manager_id =1 OR c.user_id =1

Since project_manager_id = 1 exists I want the query to return the projects but its returning nothing.

The join happens - logically - before the WHERE clause is applied. You can use an EXISTS, moving the join to a subquery:

SELECT p.*
FROM projects  p
WHERE p.project_manager_id = 1
   OR EXISTS
      ( SELECT 1
        FROM project_user_cross  c
        WHERE c.project_id = p.id
          AND c.user_id = 1
      ) ;


Autogrowth and Merge Replication We are using Merge Replication with SQL 2005. It seems I need to change the Autogrowth settings on the Publisher but I am wondering what the impact is to Replication.

Will it force an Initialization? Anything else that I need to be aware of?

Changes to the storage layer of a database, that is also a Publisher in a replication topology are not replicated to Subscribers. The only thing that can be replicated are those objects defined as Articles.

For example, you can change the autogrowth setting of a given database data file at the Publisher without concern for the Subscribers. You can think of the database storage layer as an abstraction. It has to be because you may be using different storage sub system at the Publisher and Subscriber, requiring different data file placement etc.

This particular type of change can be applied without the need to reinitialize replication.

(For interests sake: If you were using Database Mirroring, then DDL changes of this specific nature would make it to the Mirror. Worth noting because Publishers are also often Mirrored in some environments.)


Single-Table vs. Multi-Table design I'm trying to come up with a database schema which is going to be used for a social network similar to that of Facebook.
Now for posts I have two schemas in mind which are as follows:

Single-Table Design
In this approach there will only be a singular table for all post types (text, photo, video, etc.).

The structure could be similar to the following:

|-----------------------------------------------|
|  id   |  user_id  |  type  |  content  | ...  |
|-----------------------------------------------|

The content column will contain the json_encoded data for all media types.

Multi-Table Design
This approach would consider different tables for each media type, which will be one main table that contains the main post record and each media oriented table will contains respective media data.

Posts table:

|-----------------------------------------------|
|  id   |  user_id  |  type  |  content  | ...  |
|-----------------------------------------------|

Photos Table:

|------------------------------------------------------------|
|  id   |  post_id  |  user_id  |  url  |  thumb_url  | ...  |
|------------------------------------------------------------|

Videos table:

|-----------------------------------------------------------------|
|  id   |  post_id  |  user_id  |  url  |  screenshot_url  | ...  |
|-----------------------------------------------------------------|

and so on...

I would be delighted if you could guide me of which approach is better or any other schema that would serve the purpose in better condition, performance wise.

As with all database design don't think just about what you want to store but also about how you wish to retrieve and modify the information.

If you never need to refer to or search by individual parts of the contents then your first schema is fine. Similarly if you do need to do those things but you are suing a database that allows efficient indexing and manipulation of JSON blocks.

As a general rule though I would normalise the data which means breaking it down as you have in separate tables rather than using one column to store many values.

BTW: the pattern you seem to be heading towards in your second example is called table inheritance - you have a general "posts" object which contains properties common to all your object types and a detail table for each object containing properties that are unique to that object (or at least are not common to other object types). Unless one post could contain multiple photos (or videos, or ...) which is implied by your first design then you don't need a separate ID for each detail table: there will be zero or one rows per row in posts so the post_id is valid as both the primary key and foreign key. Also there is no need to repeat user_id in each table (as that can be derived from the link to posts) unless a different user could add the photo/video to someone else's existing post.


Is storing values of a unique field shared between many web forms bad practice? I am curious if it is considered a bad practice to use a unique table to store values asigned to a unique field that is shared between several webforms.

The site with webforms will carry thousands or hundred of concurrent visitors, I want to know performance implications of this practice.

I think that what you're really asking is twofold:

  Is it general practice to make a table with a few custom fields that are accessed by many pages in the app specifically for those few custom fields on many pages.

While it's unusual, this is rather part of the whole intent of having databases in the first place. A central location to store data that can be retrieved from consistently and easily, and that is abstracted from the design of the site.

  Is it going to cause performance issues to have [many of these tables|tables with such small sets of data]?

No, not at all. Database engine designers know that these things will happen, and they're usually smarter than you or I. We can discuss this more if you like, by discussing the mechanics of database engine design, but it should be sufficient to say that "the smart guys thought of all this already".


How to change name for administrator login on azure sql server I have myserver.database.windows.net with administrator login John.
I'd like to change it to Peter.
It is possible, if so, how ?
Alter which works with local sql server, doesn't work here

ALTER LOGIN John WITH NAME=Peter

So this may be an old question, but the problem is still relevant today.
The only way that you can alter the administrator of an Azure Sql Server, is to create a new SQL Server. Once the administrator has been set, you can reset their password, but not the name.

I just ran into this same issue and had the same #facepalm moment.


Is SQLT safe to run in production? I am debugging poor query performance for my application and recently came across the SQLT tool. The XTRXEC method combines XTRACT and XECUTE and it seems like this provides more info then XTRACT alone. However, one of the statements that I want to investigate is an Insert. Is it safe to run XTRXEC in a live production environment or will the XECUTE try to actually do the insert? If that's the case is it safe to do the XTRACT in production?

This is from the sqlt instruction file itself:

"For statements that modify data, i.e. INSERT/UPDATE/DELETE, a savepoint is created prior to statement execution and the transaction is rolled back to the savepoint at the conclusion of the session. For further information regarding SAVEPOINT please consult the Oracle Concepts reference manual".

This means if you are using XECUTE method, the data it inserted during execution will be rolled back at the end of execution.
If you are running this in a production system with thousands of transactions per second, you might want to consider the additional load this insert/rollback will put on the system.

Hope this helps :)


Restore .Bak file to different Server I Copied some database backups from one server to another server(new instance no databases are present).I tried restoring using below command

Restore database dbname
from disk='g:\e2bak\dbname.bak'
with
stats=10

It failed with below error

  Directory lookup for the file "G:\MSSQL$s73g\MDF\dbname.mdf" failed
  with the operating system error 3(The system cannot find the path
  specified.)Msg 3156, Level 16, State 3, Line 1

Also server level default database settings are to store data and log in

G:\MSSQL10_50.SG\MSSQL

I can overcome the error by specfying move option. But my confusion here is

1.Why database .bak file is looking for G:\MSSQL$s73g\MDF\dbname.mdf",this is the path
  of dbname database files in production from where we copied the .bak file

2.Also when I restored the database with move option and took a backup in same server,
  I was able to restore the Database with below command

Restore database dbname
from disk='G:\e1ebk\dbname.bak'
with replace,stats=10

Can you please help me understand why .Bak file location is pointing out to some other directory(which doesn't even exist in our server and existed only on production server) and in second case why this worked without move option.

When you take a backup all information including the file path is included as part of the backup.  As you found out if you just run restore database it will try to restore to the exact same location.

when you include with replace, the backup overwrites the existing .mdf and .ldf files.  I normally also have the data and log path directives to specify where to put the files (on my phone will edit to include the exact command).

this is normal behavior since if I am restoring a database on the same server you are most likely recovering from something and would want the db files to be the same.

EDIT: Here is my normal restore command, whether on the same server or different.  I like to specify exactly where things are going.

RESTORE DATABASE dbName
   FROM DISK = N'Path\To\Backup.bak'
   WITH MOVE 'dbName_DataFileLogicalName' TO 'Path\To\Data.mdf',
        MOVE 'dbName_LogFileLogicalName' TO 'Path\To\Log.ldf',
       REPLACE,
       STATS = 10

By always specifying the files I am writing to it cuts out some uncertainty and when some one comes and looks it is very clear what is going on.


MySQL Replication issues on server placed in different continents I am running 2 cpanel servers with MySQL Replication set on them,  one in france the other in US. Running a database hungary site. Replication was setup to load balance as well as for failover reason with mysql master being in US.

The issue is slave sometimes lag behind and leaves some rows which creates difference in dbs.

I want to know what could be the possible reason behind this.

Latency between servers is 250ms which some times rises to 400ms or more but sometimes.
Nodes are 32 GB Octa core, tables with around 400k rows and total database size below 5GB and table count around 700. And connectivity at around 100 Mbps.
I know there is always room for optimizations though databases have been optimised.
Our Application receives 10 -15 hits per second in peak time and every hit creates 2/3 database queries.

What could be a possible solution to this problem? What are standard practices followed in the industry for similar situations.

MySQL's replication is based on binary log shipping.

Binary log are flushed whenever a transaction commits. Master server sends logs to slave servers, then slaves replays received logs. Moreover, logs are basically replayed in order, in 5.6 or earlier.
So, when there are a huge bunch  of logs, the lags  may occur because the slave has to replay those logs, as well as those logs have to be send to the slave.

Simply put:

Master                   Slave
begin;
queries;
commit;
flushing binary log
                lag1:sending binary log

                         lag2:replaying binary log

Since MySQL 5.7's parallel replication feature has improved, to use the 5.7 might be the answer you want. See document: slave-parallel-workers and slave-parallel-type.

In addition, if slave's workload is very heavy, lags may also occur since slave is prevented to replay logs.

These are just my experiences. I don't know whether they can be applied to your environment.

Addition: compressing binary log, which other people suggests, is also a good solution, i think.


Remove 5th position character From column using MYSQL Query I want remove 5th character from column using MYSQL Query.

My column lokks like

PRSW000000001
PRSW000000002
PRSW000000003
PRSW000000004

You can use string functions like LEFT and RIGHT, and CONCAT the final result.

create table tbl(`Order` varchar(20));
insert into tbl values
('PRSW000000001'),
('PRSW000000002'),
('PRSW000000003'),
('PRSW000000004');

update tbl
set `Order` = concat(left(`Order`, 4), right(`Order`, length(`Order`) - 5));

select * from tbl;

| Order        |
| :----------- |
| PRSW00000001 |
| PRSW00000002 |
| PRSW00000003 |
| PRSW00000004 |

dbfiddle here


Killing long running queries with user notification Is there any way to inform user that your query has been running more than x minutes, so admin has killed your session. This is especially to the query or read only user.

I have a function which will do exactly what I want (Im running the function periodically). But, I couldn't notice the user that their connection being killed by admin.

Users are using PgAdmin/psql/DBeaver as client. I am not sure, how can I show the message to them. Is there anyway at all notify the user in their session window?

(Initially I tried to do kind of logon trigger. Unfortunately, postgres doesn't have the feature yet)

This feature has been proposed with an implementation, but did not make it into version 12.  Maybe it will be in version 13.  (If you can test the patch and provide feedback, you can increase the odds of that happening)


MySQL: What does event `/io/table/sql/handler` mean? Yesterday I experienced a MySQL incident

the load was extremely high
All select statements takes more than 3 times time than usual.
All insert statements takes 10+ times time than usual.

The MySQL performance monitor tool shows:

there are lots of /io/table/sql/handler has been generated during the period of incident.
lots of active threads within the period of incident.
lots of blocked thread within the period of incident.

Questions

What does /io/table/sql/handler mean?
Do you know the possible root cause?

From documentation of solorwinds

  .../sql/handler = work has been delegated to a storage engine

  examples:

  io/table/sql/handler = storage engine is processing an I/O request against a table
  lock/table/sql/handler = storage engine is processing a table lock operation

Reference

See the MySQL reference manual for more information on Wait Instrument naming (look at the section for "Wait Instrumented Components"):

https://dev.mysql.com/doc/refman/5.6/en/performance-schema-instrument-naming.html


SQL Server Express 2012 Database Size Limit I checked the forum there are other similar topics but my scenario is different.
for myDatabase under database properties -> General tab it shows:

Size : 25285.31 MB
Space Available: 3989.13 MB

And on disk

myDB.mdf = 10.0 GB (10,745,806,848 bytes)
myDB_log.ldf = 14.6 GB (15,767,764,992 bytes)

As per my understanding only 10GB is allowed in Expres edition but here it seems more. What is the space available parameter from DB properties.

SQL Server Express only imposes file size limits on data files, log files can grow to any size. The Size shown on the database properties window is the data and log file combined sizes.

Somewhat confusingly though, the Free Space value shown on this window only relates to the data file(s). If you'd like to see this in more detail, I'd recommend querying the system tables rather than relying on the SSMS GUI (which isn't always going to give you the information you want/need). The following script will show some more detailed information about data file sizes/growth settings:

SELECT
    [TYPE] = DF.TYPE_DESC
    ,[FILE_Name] = DF.name
    --,[FILEGROUP_NAME] = FG.name
    ,[File_Location] = DF.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0 - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((DF.SIZE/128.0 - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0)/(DF.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
        + CASE is_percent_growth WHEN 1 THEN ' [WARNING: Autogrowth by percent]' ELSE '' END
FROM sys.database_files DF
LEFT JOIN sys.filegroups FG
ON DF.data_space_id = FG.data_space_id
order by DF.TYPE desc, DF.NAME;

As an aside: Since your data file is at 10GB already, future growth operations will fail (and whatever transaction triggered the growth will generate a Primary Filegroup Full error). Since the file is set to 10GB exactly, I suspect that it has been grown out to this amount deliberately to take advantage of the full amount of space offered by SQL Express.


Proper resizing of Log File after shrinking - SQL Recently I've discovered our 2012 SQL Server database contains over 100,000 Virtual Log files by running the DBCC LOGINFO command. After doing a good deal of research
I am still uneasy as to what exact size I should set the Log after shrinking.

With using the code snippet below and based on my details, what suggested size should I set my Log file to and would you recommend that I change my autogrowth?

USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO

Current Database Size: 39,482,368 KB
Initial Database Size: 38,557,000 KB
AutoGrowth: 128 MB
Current Log File Size: 34,964,480 KB
Initial Log File Size: 34,145,000 KB
AutoGrowth: 128 MB

A Log backup is taken 8 times a day 6 days a week and Autogrowth is happening pretty much once a week. The Log file is also on a separate storage device from the Database

Also: Many of the VLF's are probably left over from the last DBA not shrinking the file. It has been within a year or two since we updated the autogrowth to 128 MB from the default and set this initial size.

I think you'll find a good starting point here - Set transaction log size and growth

To summarize from the link:

Set your transaction log large enough so it will not grow during
normal operation. All update activity stops in the database when the
logs grows, so you want to minimize log growths.
For an initial log size, you could roughly estimate the log size
needed to rebuild your largest table in db (roughly, that is the size
of that table) and set the initial log size to that value. That would
be the “fit largest transaction” method. Imprecise, but is very
simple.
Log file growth should be changed from a percentage (as is the
default) to a fixed MB size and monitored.
The link references the usage of BULK RECOVERY model to limit the
size of the transaction log due to 'minimally logged' transactions
like BULK INSERT and INDEX REBUILDS. You can only switch to BULK
RECOVERY from FULL RECOVERY, but you should be aware that you cannot
restore to a point of time that encompasses a 'minimally logged'
transaction because individual log (insert/update/delete) records are
not generated during that process. While the actual log file will not
be large for a 'minimally logged' transaction, the log backup will
still be large due to backing up the changed data extents that were
affected during the 'minimally logged' transaction.
In FULL RECOVERY, the transaction log will continue to grow until a
log backup occurs (assuming there aren't any open transactions left)
In SIMPLE RECOVERY, the transaction log is 'basically' available for
reuse after a transaction commits work.
Generally speaking, shrinking the log is discouraged. At some point,
there was activity that caused it to grow to that size (index
rebuild, etc.). There may be situations where an out-of-the-normal
issue occurred and caused the transaction to grow large than normally
needed and in those cases you may need to shrink the log down to a
more realistic size

On a side note, your question states you are taking transaction log backups 8 times per day (6 days per week).  I would highly encourage you to read Back Up Transaction Logs Every Minute. Yes, Really.


Problem using merge into a remote table over dblink I'm attempting to perform a merge into a remote table, let's say on server A (over a dblink), using a local table on server B. Source and target databases are both on Oracle 10.2.0.3.

We already have a similar existing merge that works fine (but with the query running on server A and merging into a table on server B) so I'm a bit flummoxed as to what could be causing the problem.

I've reduced the case to the simplest - having identical tables and using the entire content of the local table for the merge, and still the merge fails with ORA-02064 "distributed operation not supported":

MERGE INTO attendance@dblnk tgt
USING (
  SELECT *
  FROM attendance
  -- WHERE TRUNC(in_date) = TO_DATE('01.09.2013', 'DD.MM.YYYY')
) src
ON (tgt.emp_no = src.emp_no AND tgt.in_date = src.in_date)
WHEN NOT MATCHED THEN
  INSERT (emp_no, in_date, out_date)
  VALUES (src.emp_no, src.in_date, src.out_date)

Any idea what I should be checking next? Many thanks in advance

This is a bug. (Bug number 4311273).

  ORA-2064 using MERGE statement over a database link

... which was fixed in Oracle 10.2.0.4. Upgrade your database software.


Join tables based on variable value contained in column I have two tables with dates as the primary column to join. But, I also need to join based on the value from one column that is partially contained in a column from the other table.

Table 1: date, audience

Table 2: date, channel

'audience' in table 1 has a value that is partially contained in 'channel' in table 2 like this:

audience = "oldpeople"

channel = "oldpeople_email"

There are hundreds of potential 'audience' values and 'channel' values, so I cannot name the values, it has to work dynamically. This is what I have tried that has not worked:

SELECT * FROM table1
left JOIN table2 ON ((table2.Channel like '%table1.audience%')
     AND (table2.date = table1.date) )

With the way you've written that join condition, SQL server will search for exactly "table1.audience", not the column value. Column names within single quotes are just treated as string literals, not the underlying column value.

Try this instead:

SELECT *
FROM table1
LEFT JOIN table2
ON ((table2.Channel like '%' + table1.audience + '%')
AND (table2.date = table1.date))

This will concatenate the value contained within table1.audience and wrap it in wildcards.

I'd recommend dropping the leading wildcard if it's not necessary, as this will significantly hinder performance if you're dealing with a large data set (as you will force a scan).


How to compare time portion of dates for multiple dates I have a postgresql database with various records which have a date column which looks like 2020-10-12 12:45:55. I am wanting to construct a query that looks for activity with timestamps that are on any day, but between 22:00:00 of one day and 05:00:00 the next day in the actual time portion.
How do I ignore the date and just focus on the time portion of this column? I think I need to split up the date column, but still be able to use < and > operators on the time string which is left over.

which have a date column which looks like 2020-10-12 12:45:55

That's not a date column, but a timestamp (date and time). Assuming type timestamp, not timetamptz? (You should always disclose actual table definitions.)
SELECT *
FROM   tbl
WHERE  timestamp_in_disguise::time >= '22:00'
OR     timestamp_in_disguise::time <= '05:00';

See:

Postgres data type cast

Or:
...
WHERE  timestamp_in_disguise::time NOT BETWEEN '05:00' AND '22:00';

The first includes bounds and you can adjust as needed.
The second uses BETWEEN, which always includes bounds - so excludes them in the negated expression.
You can still use a hack to include bounds building on the  inside knowledge that Postgres stores times and timestamps with microsecond resolution (6 fractional decimal digits) in its current implementation.
...
WHERE  timestamp_in_disguise::time NOT BETWEEN '05:00:00.000001' AND '21:59:59.999999';

But I strongly advice against the latter. Building on implementation details is brittle and ugly.
Casting the string literals to the right is optional as their type is derived from the typed column.
If we are, in fact, dealing with timestamptz you need to define where in the world it's supposed to be 22:00 etc. (You may need to think about that with timestamp, too.) See:

Ignoring time zones altogether in Rails and PostgreSQL

About BETWEEN and including lower and upper bound:

How to add a day/night indicator to a timestamp column?

If you run this kind of queries a lot, consider an expression index:
CREATE INDEX ON tbl ((timestamp_in_disguise::time));  -- parentheses needed


MYSQL: Select rows with identical values in one column, and specific values in another I have a MySQL table with the following pattern:

| customer_id | store_id | status_id |
|-------------|----------|-----------|
| 12345       | 12       | 5         |
| 12345       | 8        | 3         |
| 12345       | 6        | 8         |
| 14567       | 5        | 3         |
| 14567       | 5        | 6         |
| 16543       | 3        | 4         |
| 19876       | 3        | 5         |
| 19876       | 6        | 8         |

Say, I want to retrieve all rows...

with identical customer IDs
which in turn have different store IDs
which all should have a status ID higher than 4

The correct output for the example table would therefore be:

| customer_id | store_id | status_id |
|-------------|----------|-----------|
| 19876       | 3        | 5         |
| 19876       | 6        | 8         |

Currently, I successfully wrote down a query to select all rows with an identical customer_id:

SELECT *
FROM table
WHERE customer_id IN (
    SELECT customer_id
    FROM table
    GROUP BY customer_id
    HAVING COUNT(*) > 1
)

This query would pick me all rows with customer_id = 12345 as well, but since one of the status_id of these rows is smaller than 4, I don't want this customer_id.

As an additional question, how would the final query look like when ported to Doctrine/Symfony?

SELECT b.*
    FROM (
        SELECT customer_id
            FROM tbl
            WHERE COUNT(*) = COUNT(DISTINCT STORE_id) -- no dup store_ids
            GROUP BY customer_id      -- per customer
            HAVING MIN(status_id) > 4 -- all status_id > 4
         ) AS a
    JOIN tbl AS b            -- go back to get other columns
        USING(customer_id);


Why does a subquery reduce the row estimate to 1? Consider the following contrived but simple query:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
  END AS ID2
FROM X_HEAP;

I would expect the final row estimate for this query to be equal to the number of rows in the X_HEAP table. Whatever I'm doing in the subquery shouldn't matter for the row estimate because it cannot filter out any rows. However, on SQL Server 2016 I see the row estimate reduced to 1 because of the subquery:

Why does this happen? What can I do about it?

It's very easy to reproduce this issue with the right syntax. Here is one set of table definitions that will do it:

CREATE TABLE dbo.X_HEAP (ID INT NOT NULL)
CREATE TABLE dbo.X_OTHER_TABLE (ID INT NOT NULL);
CREATE TABLE dbo.X_OTHER_TABLE_2 (ID INT NOT NULL);

INSERT INTO dbo.X_HEAP WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE STATISTICS X_HEAP__ID ON X_HEAP (ID) WITH FULLSCAN;

db fiddle link.

This definitely seems like unintended behavior. It is true that cardinality estimates do not need to be consistent at each step of a plan but this is a relatively simple query plan and the final cardinality estimate is inconsistent with what the query is doing. Such a low cardinality estimate could result in poor choices for join types and access methods for other tables downstream in a more complicated plan.

Through trial and error we can come up with a few similar queries for which the issue does not appear:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT -1)
  END AS ID2
FROM dbo.X_HEAP;

SELECT
  ID
, CASE
    WHEN ID < 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    WHEN ID >= 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END AS ID2
FROM dbo.X_HEAP;

We can also come up with more queries for which the issue appears:

SELECT
  ID
, CASE
    WHEN ID < 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    WHEN ID >= 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE)
  END AS ID2
FROM dbo.X_HEAP;

SELECT
  ID
, CASE
    WHEN ID = 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT -1)
  END AS ID2
FROM dbo.X_HEAP;

SELECT
  ID
, CASE
    WHEN ID = 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END AS ID2
FROM dbo.X_HEAP;

There appears to be a pattern: if there is an expression within the CASE that is not expected to be executed and the result expression is a subquery against a table then the row estimate falls to 1 after that expression.

If I write the query against a table with a clustered index the rules change somewhat. We can use the same data:

CREATE TABLE dbo.X_CI (ID INT NOT NULL, PRIMARY KEY (ID))

INSERT INTO dbo.X_CI WITH (TABLOCK)
SELECT * FROM dbo.X_HEAP;

UPDATE STATISTICS X_CI WITH FULLSCAN;

This query has a 1000 row final estimate:

SELECT
  ID
, CASE
    WHEN ID = 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
  END
FROM dbo.X_CI;

But this query has a 1 row final estimate:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END
FROM dbo.X_CI;

To dig into this further we can use the undocumented trace flag 2363 to get information about how the query optimizer performed selectivity calculations. I found it helpful to pair that trace flag with the undocumented trace flag 8606. TF 2363 seems to give selectivity computations for both the simplified tree and the tree after project normalization. Having both trace flags enabled makes it clear which calculations apply to which tree.

Let's try it for the original query posted in the question:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
  END AS ID2
FROM X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

Here is part of the part of output which I think is relevant along with some comments:

Plan for computation:

  CSelCalcColumnInInterval -- this is the type of calculator used

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID -- this is the column used for the calculation

Pass-through selectivity: 0 -- all rows are expected to have a true value for the case expression

Stats collection generated:

  CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter) -- the row estimate after the join will still be 1000

      CStCollBaseTable(ID=1, CARD=1000 TBL: X_HEAP)

      CStCollBaseTable(ID=2, CARD=1 TBL: X_OTHER_TABLE)

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1 -- no rows are expected to have a true value for the case expression

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1 x_jtLeftOuter) -- the row estimate after the join will still be 1

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter) -- here is the row estimate after the previous join

          CStCollBaseTable(ID=1, CARD=1000 TBL: X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: X_OTHER_TABLE_2)

Now let's try it for a similar query that doesn't have the issue. I'm going to use this one:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT -1)
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

Debug output at the very end:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollConstTable(ID=4, CARD=1) -- this is different than before because we select a constant instead of from a table

Let's try another query for which the bad row estimate is present:

SELECT
  ID
, CASE
    WHEN ID < 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    WHEN ID >= 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE)
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

At the very end the cardinality estimate drops to 1 row, again after Pass-through selectivity = 1. The cardinality estimate is preserved after a selectivity of 0.501 and 0.499.

Plan for computation:

 CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.501

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.499

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1

Stats collection generated:

  CStCollOuterJoin(ID=12, CARD=1 x_jtLeftOuter) -- this is associated with the ELSE expression

      CStCollOuterJoin(ID=11, CARD=1000 x_jtLeftOuter)

          CStCollOuterJoin(ID=10, CARD=1000 x_jtLeftOuter)

              CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

              CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

          CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

      CStCollBaseTable(ID=4, CARD=1 TBL: X_OTHER_TABLE)

Let's again switch to another similiar query that does not have the issue. I'm going to use this one:

SELECT
  ID
, CASE
    WHEN ID < 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    WHEN ID >= 500
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

In the debug output there is never a step which has a pass-through selectivity of 1. The cardinality estimate stays at 1000 rows.

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.499

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

End selectivity computation

What about the query when it involves a table with a clustered index? Consider the following query with the row estimate issue:

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
  END
FROM dbo.X_CI
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

The end of the debug output is similar to what we already have seen:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_CI].ID

Pass-through selectivity: 1

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

However, the query against the CI without the issue has different output. Using this query:

SELECT
  ID
, CASE
    WHEN ID = 0
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2)
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE)
  END
FROM dbo.X_CI
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

Results in different calculators being used. CSelCalcColumnInInterval no longer appears:

Plan for computation:

  CSelCalcFixedFilter (0.559)

Pass-through selectivity: 0.559

Stats collection generated:

  CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

      CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

      CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

...

Plan for computation:

  CSelCalcUniqueKeyFilter

Pass-through selectivity: 0.001

Stats collection generated:

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE)

In conclusion, we appear to get a bad row estimate after the subquery under the following conditions:

The CSelCalcColumnInInterval selectivity calculator is used. I don't know exactly when this is used but it seems to show up much more often when the base table is a heap.
Pass-through selectivity = 1. In other words, one of the CASE expressions is expected to evaluated to false for all of the rows. It does not matter if the first CASE expression evaluates to true for all rows.
There is an outer join to CStCollBaseTable. In other words, the CASE result expression is a subquery against a table. A constant value will not work.

Perhaps under those conditions the query optimizer is unintentionally applying the pass-through selectivity to the row estimate of the outer table instead of to the work done on the inner part of the nested loop. That would reduce the row estimate to 1.

I was able to find two workarounds. I was not able to reproduce the issue when using APPLY instead of a subquery. The output of trace flag 2363 was very different with APPLY. Here's one way to rewrite the original query in the question:

SELECT
  h.ID
, a.ID2
FROM X_HEAP h
OUTER APPLY
(
SELECT CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
  END
) a(ID2);

The legacy CE appears to avoid the issue as well.

SELECT
  ID
, CASE
    WHEN ID <> 0
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
  END AS ID2
FROM X_HEAP
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

A connect item was submitted for this issue (with some of the details that Paul White provided in his answer).


Should a SQL Server DBA know Windows Server and vice versa? Are these roles soon to become synonymous. Or at least more encapsulated/? I have Jr. experience with SQL Server and a desire to dive deep into Windows System Administration.

Are these fields too separate to combine skill sets? Would I be marketable as a Windows Admin who knows SQL Server, or vice versa, a SQL Server admin who knows Windows Server?

As a database administrator, you are part of a team whose goal is to make a job easier, or just possible, for someone. The team includes system administrators, database administrators, database programmers, application programmers, network administrators, and so in.

A valuable team member knows not only their own role but a little bit about every role in the team.

Specifically, as a database administrator, it will help you to know some or all of the following.

How to create and modify Active Directory user, group, machine, and managed service accounts.
What Kerberos Constrained Delegation is and how to set it up (including setting up Service Principal Names).
The basics of .NET programming (to help you talk to the application programmers).
The basics (and maybe the intermediates) of database design, e.g. normal forms (to help you talk to the database programmers).
Indexing (to help you get involved in database optimisation).

In addition, anyone working in an Active Directory environment will find it useful to know the basics of Group Policy.

As to whether or not you are better off being a "DBA who knows Windows" or a "Windows Admin who knows databases", that depends on the IT industry where you live. For example, I live in New Zealand (a country of 4½ million people). If you want to work in IT here then you have to be a "Windows who knows databases and email and terminal services and printing and troubleshooting and lots of other stuff." The industry is too small for specialists. :-)


Is it possible to remove a database server from a cluster resource, but keep it running on the node I was wondering if anyone knew whether it was possible to delete the cluster resource, but keep all of the disks and sql instance running on the node instead? So if I was to delete the cluster resource, would the instance continue running on the node?

No. The instance is configured to use cluster resources and as such if the cluster is removed the instance will not function correctly.

If you like to play with fire in an experimental-not-in-production-learn-by-trial-and-error way, you can do the opposite of How to move SQL Server from Standalone to Cluster environment.


Search SQL Server Execution Plan by cost in SSMS I was given a large Execution plan and I'm trying to find the nodes connected to high costs. SSMS offers a search util, however, I did not find the option that searches for the cost value in percent.
Given my image below, how do I search for a node causing 21 percent of the cost? The second input allows to select equals and contains. Is it possible to search for operations that cost more than 15 %?

A much better option would be to use SentryOne's Free Plan explorer. It is 1000% better than SSMS.

You can also have it configured to install it as SSMS addin and then when you right click any plan, it will allow you to open in Plan explorer.


Using floats for PKs. Is it a good idea? Let's say I have an entity PizzaStore which has a location as part of its attributes
Now a PizzaStore can have N of other PizzaStore that are close by.
So seems to be a self referencing relationship 1-N  (optional)
If we know before-hand which exact PizzaStore are close by each other what would be the best way to represent that?
Since we have a self-referencing 1-N relationship I think another table would be needed <pizza_store_id, other_pizza_store_close_by_id>
In this case we would store e.g.
<1, 22>,
 <1, 23>,
 <1, 78>,
 <2, 102> etc  to show that pizza store with id 1 has 22, 23 and 78 near by etc

Now in order to get these rows back in-order I would need to create a PK and query based on that.
I was wondering would an auto-increment guarantee the insertion order?
Or would I need to use a float representing the distance e.g.  <2.04, 1, 23> (where 2.04 is the distance in miles)
I was also thinking is there a better way than this?
We know that if 1 is close to 22 then 22 is also close to 1 right?
Is there a more efficient way to represent this information?
I think it would suffice to store just the row <1,22> to capture that 1 is close to 22 and that 22 is close to 1. But this way we are losing the order

Update:
All the answers are very useful. One thing though that perhaps is not clear.
I already have the order of the entities based on the distance on insert time.
I.e. I have a set of ids of objects already sorted based on distance and I want to insert this set in the database in a way that I can retrieve the rows back in the order I have inserted them. I want to avoid sorting on distance on retrieve since I already had the order at insert time. These ids would be used to join to another table (they are ids) and I want to retrieve the rows in the insert order

To answer your question directly: No. Using floats for a primary key is not a good idea. Why? Because floats can run into problems with rounding and precision (Is 1.20 the same as 1.2? Because mathematically it is.)

Personally I would create a primary key on a single field. Preferably an integer. Integers are typically more efficient than floats and there is no ambiguity about interpretation as with floats and strings.

The most important function of a primary key is to uniquely identify a single row in a table. It doesn't have to have any meaning or purpose beyond that. If it serves another purpose thats a bonus.

In your case, (as your title implies)  I wouldn't use the distance as the primary key (on its own) because it may be possible that 2 combinations of stores are the same distance apart.

You could use it as part of a compound key. But in this example it doesn't make sense unless you wanted to store multiple distances between the same 2 stores.

To go a bit further: This looks to me like a spatial query issue. If you store an XY coordinate with each store, you can then do things like a radius search. "Give me a list of pizza shops with 1 mile, 2, 5 , 10 miles" etc. Or even better from a customer address you could determine which shop is closest.

If a new store is added it would automatically be included in the result set without having to manually calculate the distances. You could also calculate the distances between every shop.

The only shortcoming of this approach i can think of is that the distances are straight line and may not accurately reflect the distance you may have to travel between shops by road.

Depending upon what you are trying to achieve it may be useful to store the distance rather than recalculate it, but i personally prefer to derive values as needed.


Why does Oracle change my date fields? I have an Oracle 11g XE set up in Moscow, Russia, and therefore on MSK time zone. When a client in the same timezone stores a DATE variable from Java with, say time set to 12:00, the time is changed to 08:00 when I read it back. The Oracle data type is DATE (no TZ) and there is no timezone handling in my Java client.

The output of the following queries to check timezone settings in Oracle are:

select dbtimezone, sessiontimezone from dual;
DBTIMEZONE = +00:00
SESSIONTIMEZONE = Europe/Moscow  (which I guess is +03:00)

So the sessiontimezone is probably the culprit, but why does it affect non _TZ columns and what is the best way to solve it?

DBTIMEZONE is different than host machine timezone, and it needs to be set up separately (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions039.htm)  .

The first step is to change DBTIMEZONE to Moscow so it matches server settings which always makes sense.   As a side effect, you will have your current issues "solved" because now db timezone also matches application server timezone .

However, I'd not recommend to stop at this point unless you can always run Oracle and java application[s] with Moscow timezone settings.  It may be feasible in some cases, but it's not very robust solution.  It easily turns to nightmare when you have to deal with switching to daylight saving time .

I'd rather change application to always store datetime in UTC , and perform all necessary conversion on application side before displaying date to the end user.
Another option you can try is replacing DATE columns with one of Oracle timezone aware types (TIMESTAMP WITH LOCAL TIMEZONE or TIMESTAMP WITH TIMEZONE).


Does MERGE updates when not needed? In SQL Server 2012,

Does a merge statement like this :

WHEN MATCHED THEN
update SET A.len = B.len

updates a row even if A.len equals to B.len ?

Or does the SQL SERVER knows to ignore this situations ?

If not, would it be better to add the matching condition the following :

WHEN MATCHED AND A.len <> B.len THEN
update SET A.len = B.len

Is there any difference in this on other Sql servers ?

Thanks,

The short answer is yes. You can read more about the Merge command syntax at the Microsoft MSDN site.


What is the best way to monitor long running queries in SQL Server? I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database.  Is there an alternative to running the query in SQL Server Management Studio and checking on it every hour or so?  I would like to be emailed or sent a message when it's done, but don't know the best tool for this.

Following Gaius post: You can create an .SQL script which does what you need with use db in front of the script -> create a SQL Agent job of Operating system type which calls the script:

sqlcmd -E -S SERVERNAME -i"c:\YOURSCRIPT.sql" -o"C:\YOURSCRIPT_LOG.log"

Add new step and use msdb.dbo.sp_send_dbmail procedure to send email. This feature can be customized to display inside the mail a specific query from SQL tables to confirm the execution of the script... for example dbcc showcontig of your rebuild indexes.


List Postgresql foreign data wrappers without the use of the `psql` command As seen in a stackoverflow answer I can use the psql command and the \dew[+] command in order to list the foreign wrappers.

But the use of universal database tools such as DBEaver, DataGrid, etc make our lives easier. Using these tools how I can list all the available data wrappers and how I can find out whether they are used?

If you want to find out which SQL queries psql uses, start it with the --echo-hidden  parameter, then run the psql command you are interested in, and it will show the query on the console:

$ psql --echo-hidden
psql (11.4)
Type "help" for help.

postgres> \dew
********* QUERY **********
SELECT fdw.fdwname AS "Name",
  pg_catalog.pg_get_userbyid(fdw.fdwowner) AS "Owner",
  fdw.fdwhandler::pg_catalog.regproc AS "Handler",
  fdw.fdwvalidator::pg_catalog.regproc AS "Validator"
FROM pg_catalog.pg_foreign_data_wrapper fdw
ORDER BY 1;
**************************

   List of foreign-data wrappers
 Name | Owner | Handler | Validator
------+-------+---------+-----------
(0 rows)

This works for all internal commands that show information from the system catalogs.


MySQL Call Stored Procedure along with an UPDATE statement I am pretty much convinced that this is not possible but again I wanted to see if there is a possibility.
I have an UPDATE statement and I want to call a SP for each row updated. Is it possible someway to include the SP call along with the UPDATE statement?
Using a temporary table or another select query seems like a solution here though.

No, you can't do it directly.
Convert your stored procedure into a function and use the return and some user defined variables for the update.
you also can call the stored procedure in a function, and youse the return values.


Can you change the value according to the date? Is there a formula that can change the value according to the date of the day?
Examaple: in April it's 10 and in November it's 5, August is 6.
Kind regards
Karsten

A mapping table will do this. It could be implemented as a persisted table, a view, a common table expression (CTE), a CASE expression of maybe some other ways. Reuse, volatility of values and performance, not to mention DBMS capability, will guide the implementation choice.


What is the replacement for EXEC sp_dboption  in SQL Server 2012? From microsoft connect you learn, that it is closed by design. But they do not tell how to replace

EXEC sp_dboption master

to query the current db options.

Most of the options are in sys.databases


How do I find what keys mysql will use for a query? I just had a query encounter a deadlock, where the lock it was trying to acquire was for an index. But when I used explain to visualize the query, I saw that a different index was listed under key and possible_key. Going back to the query, I found that the key causing the deadlock was on a column only used in the order by clause, and nowhere else in the query.

This set me thinking: Given an arbitrary SQL statement, how do I find out all of the indexes mysql is going to need to acquire a lock on in order for it to run?

You are approaching this from the wrong direction.  Any query can deadlock anytime.  So, you must be prepared to replay the entire transaction when a deadlock occurs.

Sure, it is a good idea to try to avoid deadlocks, there there are several things that can decrease their frequency.  The main technique is to make queries faster.

In the case you have mentioned, the Optimizer probably decided that avoiding the 'sort' for the ORDER BY was better than using any other index, or even using no index.  Let's see SHOW CREATE TABLE and EXPLAIN SELECT to further dissect what happened.

Another technique is to include the optimal index -- often a "composite" index over multiple columns.  Sometimes it is even good to have a "covering" index -- one that includes all the columns mentioned in the query.

When using IN, another technique is to sort the values.


count rows in table on some condition I have table, table B:

id   p.id    s.id
1      5       1
2      5       1
3      5       2
4      5       2
5      5       3

The result looks like this:

s.id   count
1       2
2       2
3       1

I have tried:

Select count(s.id) from B group by s.id

But its not giving me the required result. I am using MySQL.

It's simple:

SELECT s.id
     , count(*) as COUNT
FROM B
GROUP BY s.id;


Why RDS PostgreSQL `MaximumUsedTransactionIDs` not decreasing even after data base seems correctly `VACUUM FREEZE`'d? My MaximumUsedTransactionIDs seems to keep growing indefinitely, but I have
these evidences hinting me it maybe should not be happening that way:
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.

my_main_data_base=> SELECT datname FROM pg_database;
     datname
-------------------
 template0
 rdsadmin
 template1
 postgres
 my_main_data_base
 another_data_base
(6 rows)

my_main_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC
LIMIT 5;
VACUUM
       relname        | xid_age
----------------------+---------
 pg_toast_1150794     |   63707
 a_user_table         |   63707
 another_user_table   |   63707
 pg_toast_1223948     |   63707
 pg_toast_1223998     |   63707
(5 rows)

my_main_data_base=> \q
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
another_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.

another_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC
LIMIT 5;
VACUUM
      relname       | xid_age
--------------------+---------
 pg_toast_2964      |   12350
 pg_toast_3592      |   12350
 pg_toast_2396      |   12350
 pg_db_role_setting |   12350
 pg_authid          |   12350
(5 rows)

another_data_base=> \q
$ _

$MY_USER_LAND_RDS_SUPER_USER is what AWS RDS provides me as a superuser.
It is a super user from the RDS point of view, but it is not a superuser
from the PostgreSQL point of view.

I would expect now to have MaximumUsedTransactionIDs decreased, but it does
not. It is still at about ~80.000.000.
I have planned that after it reaches 100.000.000 I'll fork a development
version of the RDS instance where I'll make it so AWS boots the autovacuum
process at it.
Questions

What is happening with MaximumUsedTransactionIDs? Why it does not shrink?

Will autovacuum take care of MaximumUsedTransactionIDs once booted?

Is autovacuum irrelevant in my scenario because I run manually unqualified
VACUUM FREEZEs from time to time on all my user land databases?

Should I just ignore MaximumUsedTransactionIDs?

Can I somehow connect to database rdsadmin to SELECT age(relfrozenxid)
there?

EDIT, fulfilling your first request, adding...
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.6.6, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits:
256, compression: off)
Type "help" for help.

postgresql=> SELECT datname, datfrozenxid FROM pg_database;
     datname       | datfrozenxid
-------------------+--------------
 template0         |         1822
 rdsadmin          |         1822
 template1         |         1822
 postgres          |         1822
 my_main_data_base |     76628948
 another_data_base |     76681257
(6 rows)

postgresql=> \q
$ _

Activate autovacuum in the parameter group and when it hits the configured level (min 100.000.000) it'll start and MaximumUsedTransactionIDs will go down drastically.