Count consecutive null rows from a joined table I have the following tables:

members:
----------------------------------------------
| member_id | member_name | member_join_date |
----------------------------------------------

events:
-------------------------
| event_id | event_name |
-------------------------

event_dates:
--------------------------------------
| event_id | event_date | event_week |
--------------------------------------

attendance:
------------------------------------------------------------
| member_id | event_id | attendance_date | attendance_week |
------------------------------------------------------------

So I have members who joined the organization at a certain time and they may attend weekly events (the occurrences of which are monitored by the event_dates table)

What I want to do is get all members that have at least 3-consecutive absences

I did all I could and I think I have each part of the query but don't know how to merge them together.

Get all events that they could've attended:

    SELECT
      m.member_id AS id,
      ed.event_id,
      ed.event_week
    FROM
      members AS m
      LEFT JOIN
        event_dates AS ed
        ON
          ed.event_date >= m.member_join_date

Get all the events they actually attended:

    SELECT
      m.member_id AS id,
      a.event_id
    FROM
      members AS m
      LEFT JOIN
        attendance AS a
        ON
          a.member_id = m.member_id

And probably the answer to this question if I manage to merge the two queries into one:

https://stackoverflow.com/questions/1321670/count-of-consecutive-not-null-values

But this time I want to count NULL values

This is essentially a gaps-and-islands problem. And when I have my SQL Server hat on, I often solve this kind of problem with two ROW_NUMBER() calls. Sadly, MySQL, unlike many other major SQL products, does not support ROW_NUMBER(), nor any other ranking function. To make up for that, however, you can use variable assignment in SELECTs, which MySQL does support (unlike many other major SQL products).

Below is a solution followed by an explanation:

SELECT
  member_id,
  member_name,
  event_id,
  COUNT(*) AS consecutive_times_missed,
  MIN(event_date) AS first_date_missed,
  MAX(event_date) AS last_date_missed
FROM (
  SELECT
    member_id,
    member_name,
    event_id,
    event_date,
    is_missed,
    @occ_ranking := (event_id = @last_event) * (member_id = @last_member) * @occ_ranking + 1,
    @att_ranking := (event_id = @last_event) * (member_id = @last_member)
                                             * (is_missed = @last_missed) * @att_ranking + 1,
    @occ_ranking - @att_ranking AS grp,
    @last_member := member_id,
    @last_event  := event_id,
    @last_missed := is_missed
  FROM (
    SELECT
      m.member_id,
      m.member_name,
      e.event_id,
      e.event_date,
      (a.attendance_date IS NULL) AS is_missed
    FROM       members     m
    INNER JOIN event_dates e ON m.member_join_date <= e.event_date
    LEFT  JOIN attendance  a ON m.member_id = a.member_id
                            AND e.event_id = a.event_id
                            AND e.event_date = a.attendance_date,
    (
      SELECT
        @occ_ranking := 0,
        @att_ranking := 0,
        @last_member := 0,
        @last_event  := 0,
        @last_missed := 0
    ) v
    ORDER BY
      m.member_id,
      e.event_date
  ) s
) s
WHERE
  is_missed = 1
GROUP BY
  member_id,
  member_name,
  event_id,
  grp
HAVING
  COUNT(*) >= 3
;

Basically, you start with joining members and event_dates to get all the event occurrences the members could have attended based on their membership dates. Then you throw in the attendance table (via a left join) to flag the missed occurrences. Here's an example of what you get by this time:

member_id  event_id  event_date  is_missed
---------  --------  ----------  ---------
1          1         2012-07-10  0
2          1         2012-07-10  1
1          1         2012-07-14  0
2          1         2012-07-14  1
…          …         …           …

At this point, the resulting set needs to be sorted by member_id, event_id, because that is crucial to the subsequent rankings calculation.

Two different rankings are produced for every row. One is a ranking within the row's partition of (member_id, event_id) (it is reset as soon as a new event or a new member is encountered). The other is a ranking within the specific group of consecutive event occurrences, either attended or missed, that the row belongs to (this ranking, in addition to being reset upon coming across a new member or event, also gets reset whenever the other group is encountered). And so you get something like this:

member_id  event_id  event_date  is_missed  ranking1  ranking2
---------  --------  ----------  ---------  --------  --------
…          …         …           …          …         …
1          1         2012-07-27  0          4         4
1          1         2012-07-28  0          5         5
1          1         2012-07-29  1          6         1
2          1         2012-07-10  1          1         1
2          1         2012-07-14  1          2         2
2          1         2012-07-25  1          3         3
2          1         2012-07-27  0          4         1
…          …         …           …          …         …

As you may have noticed, the difference between the two ranking numbers is constant throughout the particular group of consecutive event occurrences of the same kind ("attended" or "missed") and is also unique for that group within its partition. Therefore, every such group can be identified by member_id, event_id and the just mentioned difference.

And it now remains simply to filter the events leaving just the missed ones, group the rows and get the necessary aggregated data, like the number of rows and, possibly, as in the query above, the dates of the first and the last event occurrence in the group. The number of rows is also used in an additional filter condition to omit groups with fewer rows than required.


Parent and Child table question Another question on the photo organisation and storage app design I am working on for my class: the specifications mention that people are able to be recorded in the database even if they don't appear in any photograph and that a location can be recorded even if there aren't any photographs associated with them. I'm not sure I understand what this would mean. Would you say this would mean the person and location tables should be parent tables to the photo table or am I missing something?

Basically it comes to this:

Your photo is abourt a house, that belongs to a person. your photo has no persons in iit, but it still belongs to the person, so you can add a relationship to the photo.

The same goes for for locations the Washington Monument  is located between  Capitol and Lincoln Memorial so you can add a relationship between tose .


Query to find if there are more than X occurences within any period of given length minutes I have spent many hours thinking about a solution for my problem but I give up.
Let's imagine a table
user_id | occurred_at
   -- OK match example
   1    | 2020-01-01 08:00:00 <- First match of the set
   1    | 2020-01-01 08:08:00 <- Second match (8 minutes away from the previous so OK)
   1    | 2020-01-01 08:10:30 <- this already exceeds 10 minutes period so the set is excluded

   -- Not matched example
   1    | 2020-01-01 10:00:00 <- First match
   1    | 2020-01-01 10:05:00 <- Second match (5 minutes away from the previous so OK)
   1    | 2020-01-01 10:09:59 <- this fits into 10 minutes period so the set is matched (09:59 away altogether from 10:00:00)

   -- Another OK (4 matched)
   2    | 2020-01-01 14:23:00
   2    | 2020-01-01 14:24:00
   2    | 2020-01-01 14:26:00
   2    | 2020-01-01 14:27:00

   -- Not matched
   3    | 2020-01-01 11:00:00
   3    | 2020-01-01 11:01:00
   3    | 2020-01-01 15:26:00
   3    | 2020-01-01 18:00:00

   -- User mismatch so set is not matched neither
   3    | 2020-01-01 20:00:00
   1    | 2020-01-01 20:01:00
   2    | 2020-01-01 20:02:00

How one can query a table like this to find rows with at least N (=3 in this example) occurrences for the given user that occurred in a explicit minutes interval (=10 in this example)? I think a table example above explains it better.
http://sqlfiddle.com/#!17/54d43/1

Using the window function lag() we can mark all rows where a qualifying set ends:
SELECT *
     , occurred_at - lag(occurred_at, 2) OVER (PARTITION BY user_id ORDER BY occurred_at) <= interval '10 min' AS passed
FROM   timestamps
ORDER  BY user_id, occurred_at;

If the timestamp occurred_at for the same user (user_id) two rows back is within 10 minutes, we have a set of three.
For a given user_id:
SELECT count(*) FILTER (WHERE passed) AS qualifying_sets
FROM (
   SELECT occurred_at - lag(occurred_at, 2) OVER (ORDER BY occurred_at) <= interval '10 min' AS passed
   FROM   timestamps
   WHERE  user_id = 1  -- given user
   ) sub;

To get all user_id that pass the test at least once:
SELECT user_id, count(*) FILTER (WHERE passed) AS qualifying_sets
FROM  (
   SELECT user_id
        , occurred_at - lag(occurred_at, 2) OVER (PARTITION BY user_id ORDER BY occurred_at) <= interval '10 min' AS passed
   FROM   timestamps
   ) sub
GROUP  BY 1
HAVING bool_or(passed)
ORDER  BY 1;

The added count of qualifying_sets is optional.
db<>fiddle here


How do you remove reported non existing tables from mysql? I was trying to export a mysqldump --all-databases and noted errors. A subsequent

mysqlcheck --all-databases

revealed several non existent tables like the following

Error    : Table 'stats.scoring_campaign_countries_99save' doesn't exist
status   : Operation failed

Of course I can't drop a table if it does't exist so how to fix this?

The tables are using MyISAM.

If using MyISAM, you should be able to get rid of the tables by deleting the .frm files from the data folder while the database is down.

Remember to backup everything you can before doing anything like that!


MySQL query to select a record from each group that meets a user-defined criterion I am looking for a query that selects a record from each group that meets a user-defined criterion.  I can explain with the following illustrative table:
CREATE TABLE sample_table
(
  id INT UNSIGNED AUTO_INCREMENT,
  categoryID INT,
  weight INT,

  PRIMARY KEY(id)
);

The table is populated as follows:
INSERT INTO sample_table(categoryID, weight) VALUES(1, 3), (1, 5), (1, 2), (2, 5), (2, 3), (2, 9), (3, 5), (3, 3), (3, 3);

A simple GROUP BY categoryID query returns the first record in each group, as shown below:
SELECT * FROM sample_table GROUP BY categoryID;
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  1 |          1 |      3 |
|  4 |          2 |      5 |
|  7 |          3 |      5 |
+----+------------+--------+

To return the last record in each group we can use the approach suggested here:
SELECT * FROM sample_table WHERE id IN (SELECT MAX(id) FROM sample_table GROUP BY categoryID);
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  3 |          1 |      2 |
|  6 |          2 |      9 |
|  9 |          3 |      3 |
+----+------------+--------+

However, what I want to do is to select the record which the highest value for the weight field in each group.  My output, therefore, should be:
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  2 |          1 |      5 |
|  6 |          2 |      9 |
|  7 |          3 |      3 |
+----+------------+--------+

Kindly suggest the GROUP BY categoryID query which will yield the above output.

select the record which the highest value for the weight field in each group.

WITH cte AS (SELECT id, categoryID, weight,
                    ROW_NUMBER() OVER (PARTITION BY categoryID ORDER BY weight DESC, id) rn
             FROM sample_table)
SELECT id, categoryID, weight
FROM cte
WHERE rn = 1;

fiddle
I.e. we enumerate rows in a group by categoryID ordering them by weight decrease, then get 1st row in each group.


Scape single quote in dynamic query Oracle I have a field street name that would accept names like : O'connor but I have tested with replace function that works fine, but not sure if this is going to cause an exception with numeric values(int). Since my query is built in a v_msql variable dynamically I want to set the value to replace the single quote with two single quotes.

select   replace( ISR.STREET_NAME1 , '''','''''')
           || chr(3) || isr.agency_code || chr(3) || ''
           from isr, isr_udf where isr.isr_no = isr_udf.isr_no and isr.isr_no = 'XXX-20160718-00022'   ;

So I am wondering if Replace would handle the scape correctly. I tested it and shows no problem. Are there other ways to  scape a single quote on street names?

You can use another method to quote strings in Oracle which makes things a lot clearer.
Example:

x := q'[This is John's address]';

y := q'[He said "What's your name ?"]';

You can have as many quote marks as you like so long as they are between the '[]'.
Your code may look something like this

SELECT REPLACE(ISR.STREET_NAME1, q'[']',q'['']')
       || CHR(3) || isr.agency_code || CHR(3) || ''
  FROM isr, isr_udf
 WHERE isr.isr_no = isr_udf.isr_no
   AND isr.isr_no = 'XXX-20160718-00022'

Link: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CBJJDDCG


Reshape table by converting columns to rows The problem

I'm trying to reshape a table with many columns. I'm trying to do it independently of the specific table, so I'm trying to do it for any table.

Let's use a very simple table foo.

CREATE TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');

select * from foo;

| id|  a  |  b |  c  |
|---|-----|----|-----|
|  1| ant | cat|chimp|
|  2|grape|mint|basil|

I want to transform the column a, b and c to rows.

This query works (for this specific table):

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS colvalue
FROM foo;

|id|colname|colvalue|
|--|-------|--------|
| 1|  a    |  ant   |
| 1|  b    |  cat   |
| 1|  c    | chimp  |
| 2|  a    | grape  |
| 2|  b    | mint   |
| 2|  c    | basil  |

But I want to make it generic for any table with lots of columns.

What I've already done

To get all the columns I want to transform in rows, I can use:

SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name   = 'foo' and column_name ~ '^[a-z]$';

So using the previous query, I can do the following:

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$')
select id,
       unnest( tablecolumns.cols ) AS colname,
       unnest( array[a, b, c] ) AS colvalue
FROM foo, tablecolumns;

But I'm not able to replace array[a, b, c] with something dynamic. If I use:

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$')
select id,
       unnest( tablecolumns.cols ) AS colname,
       unnest( tablecolumns.cols ) AS colvalue
FROM foo, tablecolumns;

the result is not the values of the columns, but just the name of the columns.

Question

How can I unnest the values of the columns?

There is no way to have the column names being evaluated as columns, as pointed out in this question https://stackoverflow.com/questions/15800367/select-columns-with-particular-column-names-in-postgresql

The way to do it, is using to_jsonb, as suggested in unnest all columns from a given table, like:

select foo.id, x.*
from foo, jsonb_each_text(to_jsonb(foo)) as x(colname,colvalue)
where x.colname ~ '^[a-z]$';

In my specific case, I was trying to convert all data related to Corona Virus. The original data is available at: https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv. I've imported it as table covid.

It has many columns named after the date, like: '1/22/20','1/23/20','1/24/20','1/25/20','1/26/20', .... The first columns are related with the location, so I want to keep them in the reshaped table.

The final query I'm using is:

select id, geom, "province/state" , "country/region", to_date( reportdate, 'MM/DD/YY'), nofcases
from covid, jsonb_each_text(to_jsonb(covid)) as x(reportdate,nofcases)
where x.reportdate ~ '[0-9]+'
order by 5;


Oracle: UTL_RAW.BIT_OR(2,1) gives me 2 instead of 3. Why? Why am I getting 2 instead of 3 when I perform BIT_OR(2,1)?

SELECT UTL_RAW.CAST_TO_NUMBER (              --> decimal: 3? nope, 2
            UTL_RAW.BIT_OR(                  --> binary: 11? shouldn't it?
                UTL_RAW.CAST_FROM_NUMBER(2), --> binary: 10
                UTL_RAW.CAST_FROM_NUMBER(1)  --> binary:  1
            )
        )
FROM DUAL;

CAST_FROM_NUMBER returns RAW; if you check result of UTL_RAW.CAST_FROM_NUMBER(2) , it's not 2 but "C103" (which I believe means  (03-1)* pow(10,(0xC1-0xC1))) .   You need something like

SELECT   UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.BIT_OR(
            UTL_RAW.CAST_FROM_BINARY_INTEGER(2),
            UTL_RAW.CAST_FROM_BINARY_INTEGER(1)
        ))

FROM DUAL;

Some links about internal number representations: http://www.jlcomp.demon.co.uk/number_format.html , http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci03typ.htm
Your code returns result of C103(internal representation of 2) & C102(internal representation of 1) which is 2


Large MySQL operations slowing down SELECT queries on unrelated tables On a fairly busy SQL server, we sometimes need to do large index-building operations (after importing ~6GB tables).  The indexes are built using the more efficient "Repair by sorting" method, rather than keycache.

We have found that when these operations are running, SELECT queries on other tables in the database can get bogged down.  These tend to be queries that were already fairly expensive in terms of rows scanned; for example, we see queries that normally would take ~5s taking ~25s instead.  All relevant tables are MyISAM, as they are effectively read-only.  (We do these large imports daily, and when they are finished, atomically replace the live table with the newly imported one.  Aside from that, the live tables are not modified.)

We track CPU load and usage, memory, and disk access times, and there appears to be plenty of headroom on all fronts.  So the problem doesn't appear to be that we are simply maxing out on disk I/O for instance.  Therefore my guess is that we're running into a MySQL-specific limit - some sort of buffer or cache that's shared between connections.  I'm not a DBA though, so that's about as far as I've gotten.  Any ideas on what could be causing this?  I'm happy to provide any relevant info.  Here's our my.cnf:

[mysql]
no-auto-rehash
[mysqld]
tmp_table_size=2048M
max_heap_table_size=2048M
init-file="/etc/mysqlinit.sql"
thread_cache_size=8
query_cache_size=256M
query_cache_type=DEMAND
innodb_log_file_size=10485760
character-set-server=utf8
default-storage-engine=MyISAM
myisam_sort_buffer_size=64M
skip-external-locking
ft_min_word_len=3
innodb_buffer_pool_size=2048M
thread_concurrency=8
open_files_limit=10000
max_allowed_packet=268435456
sort_buffer_size=2M
read_rnd_buffer_size=2M
socket="/db/mysql/mysql.sock"
key_buffer=512M
tmpdir="/db/mysql-tmp"
datadir="/db/mysql"
read_buffer_size=2M
innodb_file_per_table=1
wait_timeout=30
max_connections=500
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=128M
read_buffer=2M
write_buffer=2M
sort_buffer_size=128M
[isamchk]
key_buffer=128M
read_buffer=2M
write_buffer=2M
sort_buffer_size=128M
[client]
socket="/db/mysql/mysql.sock"

The server has 16GB of RAM, of which we rarely use more than 6 except for caching.  (Although would like to leave some headroom for traffic spikes, as the web server runs on the same box.)  It has 8 cores, and the databases are on a RAID 10 array of 120GB SSDs (at /db).

Any tuning suggestions would be appreciated.  (Please give reasoning though, not just suggested settings.)

Edit: based on this page, it appears that key_buffer_size might be the culprit.  This buffer is indeed shared between connections (by default), and I expect the indexing operation is using it up, bogging down other queries.  I will experiment with the multiple caches approach recommended there and report back:

  For key_buffer_size, you must take into account that the buffer is
  shared with those users. [...]
  An alternative that avoids this problem is to use a separate key cache,
  assign to it the indexes from the table to be repaired, and deallocate
  it when the repair is complete. See Section 8.9.2.2, “Multiple Key
  Caches”.

Edit 2:
Tangentially related: based on this MariaDB doc page, I increased our overall key buffer size from 512MB to 2GB and noticed an order of magnitude better ratio of key_read_requests to key_reads in the status.  (From about 1:150 to 1:1000. So that change improved key cache hits by about 7 times.)

Edit 3:
While increasing the key buffer size seems to have helped performance overall, using a separate key buffer for the table being imported does not prevent it from slowing queries on other tables, so the initial question remains unanswered.

The real problem is all the I/O that is needed in creating indexes on huge tables.

When the index is being created, does SHOW PROCESSLIST say "Repair by key_buffer" or "by sorting"?  Sorting is better for large tables.

Are you running only MyISAM?  If so, make these changes:

innodb_buffer_pool_size = 0
key_buffer_size = 3G

However, if you switched to InnoDB and a new enough version of MySQL, you could CREATE/DROP indexes "online" -- that is, without recreating the table (as happens in MyISAM).  This would significantly decrease the I/O, hence cut back on the interference with the other queries.

A 5 second query using SSDs and RAID-10?  Yikes!  What is the query doing?  Please show us the query, together with SHOW CREATE TABLE.  It can possibly be sped up to be sub-second.

You say it is not I/O -- would you share the details of the evidence.

Yes, into "Multiple Key Caches".

query_cache_size=256M

is too high.  Don't go above about 50M, so as to avoid lots of overhead in "pruning".

Summary:

Change some settings;
Switch to InnoDB;
Speed up the 5-25s query.


Equivalant MongoDB query of MySQL date query with arithmetic operations How we can perform the below given MySQL query in MongoDB preferably in MongoDB-4.0:

SELECT *,ABS(TIME_TO_SEC(TIMEDIFF(now(), datefield))/3600) as hours,
(field1*field2*field2+field3*(1/(ABS(TIME_TO_SEC(TIMEDIFF(now(), datefield))/3600))))
as time FROM temp_table

The equivalent MongoDB Query is

 db.temp_table.aggregate([{$project:{field1:1,field2:1,hours:{$abs:{$divide:[{$subtract:[ISODate(),"$datefield"]},3600000]}},time:{$sum:[{$multiply:["$field1","$field2","$field2"]},{$multiply:"$field3",{$divide:[1,{$abs:{$divide:[{$subtract:[ISODate(),"$datefield"]},3600000]}}]}]}]}}}])

Fields to be shown can be added as per the requirement.


How can I monitor the import progress of a large CSV File in mysql? I am trying to import an existing CSV file into my database and I want to see the progress. I usually use pipe viewer when I do my sql files but for csv files I cannot figure out how to do it.

I use macOX 10.15 and mysql 8.02 (not home-brew).

My idea was:

  pv users.csv | mysql -u user -p -db < File/to/users.csv

Without using PV I just connect to my mysql change to my db and use this command:

LOAD DATA INFILE '/File/to/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';

but I do not the see process.

Are you looking for something like this?
https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

    create table watcher_tbl(time datetime, num_rows int);

    create event watch_event
    on schedule every 2 second
    do
    insert into watcher_tbl(time, num_rows) values (current_timestamp,
                                                    (select count(*) from watched_table));

    -- to see the progress.
    select * from watcher_tbl;

    -- to stop the event after loading is done.
    drop event if exists watch_event;


Correlated Subquery SQL Server 2014 I am (unsuccessfully) using a correlated subquery on an Invoices  table:

Invoices(InvoiceID,  VendorID, InvoiceTotal, PaymentTotal, CreditTotal,.... ),

to find the sum of largest unpaid invoices by all vendors, where the unpaid condition is given by InvoiceTotal-PaymentTotal-CreditTotal <0:

 Select Sum(LargestUnpaid) from
 (Select   Max(InvoiceTotal) AS LargestUnpaid from Invoices
  where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) ;

The inner query runs , which is good and bad, since the query is supposed
   to be correlated , but the query as a whole does not run, and I get the  error message:

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.

What am I doing wrong?

Add an alias for the derived table before the ; for example VendLargestUnpaidInv:

Select Sum(LargestUnpaid) from
 (Select   Max(InvoiceTotal) AS LargestUnpaid from Invoices
  where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) VendLargestUnpaidInv;


long query takes a long time I have a rather complex query in mysql 5.1.73-community. It involves joining tables and view at the same time.

Here is the query:

VIEW v_summary AS (
select (--some 35 columns fro tabel A B C D F)
from (((((ht_counters_sales_items_b a
       left join ht_counters_sales_b b
         on ((a.tcsi_tcs_id = b.tcs_id)))
      left join ht_counters_trans_b c
        on ((b.tcs_no = c.tct_ref_no)))
     left join **VIEW_counters_activities** d
       on ((c.tct_tca_id = d.tca_id)))
    left join m_items e
      on ((e.mi_id = a.tcsi_mi_id)))
   left join m_items_categories f
     on ((f.mic_id = e.mi_mic_id)))
where ((c.tct_voided = '0')
       and (a.tcsi_voided = '0'))
group by a.tcsi_id)$$

Table A : index (tcsi_id) PRIMARY

Table B : index (tcs_id) PRIMARY

Table C : index (tct_id) PRIMARY

Table E : index (mi_id) PRIMARY, (mi_code,mi_id),(mi_name,mi_id),
(mi_mic_id,mi_id),(mi_mu_id,mi_id),(mi_mu_id,mi_mic_id,mi_id),(mi_id,mi_mu_id,mi_mic_id)

Tabel F : index (mic_id) PRIMARY, (mic_mu_id),(mic_type),(mic_lft,mic_rgt,miclvl),(mic_name,mic_id)

Another view that is involved in the above view is as follows :

VIEW **VIEW_counters_activities** AS(SELECT
  SUM((CASE g.tct_ref_journal WHEN 'CREDIT' THEN (g.tct_ref_value - g.tct_ref_voided) WHEN 'DEBIT' THEN ((0 - g.tct_ref_value) + g.tct_ref_voided) ELSE 0 END)) AS tca_close_cash,
  a.tca_id            AS tca_id,
  a.tca_open_date     AS tca_date,
  a.tca_open_date     AS tca_open_date,
  a.tca_open_date     AS tca_open_time,
  a.tca_close_date    AS tca_close_date,
  a.tca_close_date    AS tca_close_time,
  a.tca_mc_id         AS tca_mc_id,
  a.tca_mst_id        AS tca_mst_id,
  a.tca_state         AS tca_state,
  a.tca_user_id       AS tca_user_id,
  a.tca_open_cash     AS tca_open_cash,
  a.tca_mutation_cash AS tca_mutation_cash,
  a.tca_controller    AS tca_controller,
  a.tca_coordinator   AS tca_coordinator,
  b.mhr_scd_name      AS tca_mhr_scd_name,
  c.fullname          AS tca_user_name,
  d.fullname          AS tca_controller_name,
  e.fullname          AS tca_coordinator_name,
  f.mc_name           AS tca_mc_name,
  f.mc_ip_address     AS tca_mc_ip_address,
  f.mc_mcp_type       AS tca_mc_mcp_type,
  f.mc_mcp_name       AS tca_mc_mcp_name,
  f.mc_ml_id          AS tca_mc_ml_id,
  f.mc_ml_type        AS tca_mc_ml_type,
  f.mc_ml_name        AS tca_mc_ml_name,
  h.mpr_name          AS tca_receipt_printer_name,
  h.mpr_ip_address    AS tca_receipt_printer_address,
  i.mpr_name          AS tca_report_printer_name,
  i.mpr_ip_address    AS tca_report_printer_address,
  j.mst_name          AS tca_mst_name
FROM (((((((((ht_counters_activities_b a
           LEFT JOIN m_hr_schedules b
             ON ((a.tca_mhr_scd_id = b.mhr_scd_id)))
          LEFT JOIN v_users c
            ON ((a.tca_user_id = c.id)))
         LEFT JOIN v_users d
           ON ((a.tca_controller = d.id)))
        LEFT JOIN v_users e
          ON ((a.tca_coordinator = e.id)))
       LEFT JOIN v_counters f
         ON ((a.tca_mc_id = f.mc_id)))
      LEFT JOIN ht_counters_trans_b g
        ON (((a.tca_id = g.tct_tca_id)
             AND (g.tct_ref_mpm_type = 'CASH')
             AND (g.tct_voided = 0))))
     LEFT JOIN m_printers h
       ON ((f.mc_receipt_printer = h.mpr_id)))
    LEFT JOIN m_printers i
      ON ((f.mc_report_printer = i.mpr_id)))
   LEFT JOIN m_sales_types j
     ON ((a.tca_mst_id = j.mst_id)))
GROUP BY a.tca_id))

As i am writing this, the query SELECT * FROM v_summary WHERE MONTH(trx_date) = 7 has run for 70 minutes and still counting.

How can i make it faster ?

All of the tables are INNODB.

MySQL settings:

bulk_insert_buffer_size 8388608
innodb_buffer_pool_size 2147483648
innodb_log_buffer_size  16777216
join_buffer_size    131072
key_buffer_size 57671680
myisam_sort_buffer_size 130023424
net_buffer_length   16384
preload_buffer_size 32768
read_buffer_size    65536
read_rnd_buffer_size    262144
sort_buffer_size    262144
sql_buffer_result   OFF
max_heap_table_size 64M
tmp_table_size 124M
SET PROFILING = ON

Several optimizations tips:

Left joins to inner joins

You can probably simplify the views a little bit by converting some LEFT JOINs into INNER JOINs. For instance, for the first view, you have a WHERE clause of c.tct_voided = 0, this will make the involved join behave like an inner join instead of a left one. If the row on c were not found, the c.tct_voided = 0 won't be true, and won't be returned. Explicitly changing the LEFT to INNER will help the database some. That might improve a little bit the execution time.

Some of the other LEFT JOINs might already by INNER JOINs just because of referential integrity, or the nature of your data. If you know for sure that there's always a corresponding row on your right table, change the LEFT to INNER.
Indexes on the expressions that are part of a join

If you have a join condition that is

(ht_counters_sales_items_b a
LEFT JOIN ht_counters_sales_b b
    ON a.tcsi_tcs_id = b.tcs_id)
LEFT JOIN ht_counters_trans_b c
    ON b.tcs_no = c.tct_ref_no

You are likely to gain a lot of speed if there is a KEY(tct_ref_no) on table ht_counters_trans_b. The same might apply to KEY(tcs_no) on table ht_counters_sales_b (if the LEFT JOIN were an INNER JOIN and MySQL would decide to process it right-to-left, instead of left-to-right).
Not forcing order of join execution

In some occasions (and this really needs to be tested, I don't know how good is MySQL at it), not putting any (...) on the JOINs gives the database more freedom to process them in the order it seems more appropriate. This is normally true for INNER JOINs, but shouldn't have a significant effect on LEFT JOINs.
WHERE clauses

SELECT * FROM v_summary WHERE MONTH(trx_date) = 7

If you want to look for all the data for July this year, you're more likely to get a much faster result if trx_date is indexed, and you write your query like:

SELECT * FROM v_summary WHERE trx_date BETWEEN '2017-07-01' and '2017-07-31'

This will allow the database to use the index. Having a function applied to the column will prevent it from using it, and force a sequential scan on all the results. And that's not what you want.


After successful installation, Is it possible create a database without DBCA? Apologies, for I am not familiar with the Oracle Database environment. Please help me to learn. :)

As the title states, can someone please provide the steps required to create a database manually through SQL, without using DBCA? Any links or posts are welcome.

Thanks.

Yes. First you need a parameter file (init.ora). The only mandatory parameter is db_name to have a fully working database.

Steps

1 - Create the parameter file:

[myhostname@oracle $] vi $ORACLE_HOME/dbs/initorcl.ora

db_name='orcl'

:wq

2 - Export ORACLE_SID to reflect the db_name you which to create:

[myhostname@oracle $] export ORACLE_SID=orcl

3 - Login to sqlplus:

[myhostname@oracle $] sqlplus / as sysdba

4 - Issue the "startup nomount" command, pointing to the parameter file you have created.

SQL> startup nomount pfile = '/u01/app/oracle/product/<version>/dbs/initorcl.ora'

5 - Issue the create database command

SQL> create database;

6 - Run the post database creation scripts:

SQL> @?/rdbms/admin/catalog.sql -- Create dictionary views

SQL> @?/rdbms/admin/catproc.sql

SQL> @?/sqlplus/admin/pupbld.sql

@ means "run an external script in sqlplus"
? is a wildcard character that represents the ORACLE_HOME path, so if you correctly installed the software and set the environment variables, it must work, otherwise provide the full ORACLE_HOME path.

You may also want to know the "whys" of each stage of the database creation process...

At step 1, you don't have a database (datafiles, controlfiles...), so in order to create a database, you first must have an instance, which is a set of memory structures and background processes, and for that purpose, the only need is a parameter file to startup the instance in nomount. To proceed to the mount stage, you need either a controlfile if the database already exists, or issue the create database command to let Oracle take care of creating the necessary files, tablespaces etc. After that, the database can be mounted and opened, but still the data dictionary views aren't populated. So you just need to run the post scripts to do the job.

Reference


Mixing SQL Server 2016 and 2017 in a distributed availability group I have an old Availability Group made up of 2 SQLServer 2016 instances (2 Windows 2016 servers in WSFC)
I also have 2 new SQLServer 2017 instances (2 Windows Server 2016) that I originally wanted to join to the 2016 AG.

This is a 0 downtime migration scenario and the 2016 servers should be dismissed once the DBs were aligned on the 2017 ones.

To my great disappointment, I discovered that it is not possible to join the 2017 instances to an existing 2016 AG, but I cannot afford the burden of stopping production, taking and restoring a backup, waiting for DBs synchronization, changing the name (and possibly the ip) of the new AG to match the original one
unless as the very last resource...

Then I came across the new 2016 service called "Distributed AG Group" and I started thinking about using it for my migration scenario ... basically like this:

Create a new AG with the SQL 2017 instances
Create a Distributed AG between the original 2016 AG and the new 2017 AG (applications continue to connect to the 2016's listener)
Wait for the DBs synchronization to take place in the 2017 AG
Make the 2017 AG as the primary
Remove the 2016 AG from the Distributed AG (short applications downtime)
Change the name and the ip of the 2017's listener (applications are up again)
Remove the distributed AG

It is feasible? Can I mix 2016 and 2017 AGs in a Distributed AG?
Or there is (obviously!) something that I'm missing and there's a simpler or more proper way to make it?

It is feasible? Can I mix 2016 and 2017 AGs in a Distributed AG?

I have confirmed from Allan Hirt SQL Server MVP and HA/DR Guru that this is indeed possible and supported. So please go ahead, the confusion may arise because in BOL two different things are mentioned. I will highlight both

Quoting from Distributed Availability groups document

  A distributed availability group spans multiple availability groups,
  each on its own underlying WSFC cluster, and a distributed
  availability group is a SQL Server-only construct. This means the WSFC
  clusters that house the individual availability groups can have
  different major versions of Windows Server. The major versions of
  SQL Server must be the same

So while you can have different Windows server version in DAG but you cannot have different SQL Server version in DAG. From here it seems like the versions should be same but if you again look at Upgrading Availability group instances it says

  To migrate to a new version of the SQL Server instance using AGs, the only supported method is a distributed AG, which is in SQL Server 2016 Enterprise Edition or later.

Now you should believe this one not the the previous one.

  Or there is (obviously!) something that I'm missing and there's a simpler or more proper way to make it?

Apart from distributed AG, I would consider 2 approaches here

Side By side upgrade

I believe transaction logshipping should help here.

Create new WSFC with SQL Server 2017 installed--No downtime
Do not create AG as of now
Logship database from SQL Server 2016 to SQL Server 2017. --No downtime
During "cutover" stop logshipping and bring database on SQL Server 2017 online.--small downtime
Now point application to this new database.
Go ahead and configure AG on new SQL Server 2017.

I know there is bit of pain involved in configuring logshipping if you have big databases but this is best I see.

In-place upgrade

Do the rolling upgrade of Availability groups. Upgrading Availability group instances


Performance at finding all foreigns with 3 one-to-many relation Basically, I have 2 tables : user and object. One user can be associated to one object by three ways (roles). I want to list all users associated at least to one business object by any role.
This query works, but it takes 5 seconds !
SELECT u.id
FROM user u
LEFT JOIN translation_revision t1
    ON u.id = t1.translator_id
LEFT JOIN translation_revision t2
    ON u.id = t2.proofreader_id
LEFT JOIN translation_revision t3
    ON u.id = t3.reviewer_id
wHERE t1.id IS NOT NULL
    OR t2.id IS NOT NULL
    OR t3.id IS NOT NULL
GROUP BY u.id

Complete fiddle with creation and data here : https://www.db-fiddle.com/f/nojYaUsWicKJDuRQk1Nqd2/7
Any idea on how to improve perf here ?

Instead of doing 3 separate joins, you could accomplish the same thing by simply using an OR clause (the execution time seems to be ~1-2ms):
SELECT DISTINCT u.id
FROM user u
JOIN translation_revision t1
    ON u.id = t1.translator_id OR u.id = t1.proofreader_id OR u.id = t1.reviewer_id
GROUP BY u.id
LIMIT 30

Other ideas would be to denormalize your data and keep some additional info about your users roles.


How to select user that doesn't exist in the second table I am trying to understand how to select a user from users table that is not associated with current user in user_follows table: neither in users_id not in shown_users_id.

Basically a new user that not related to my current user.

Tried something like this:

SELECT
   user.id
FROM
   users AS user
LEFT JOIN
   user_follows AS follow ON user.id = follow.users_id
LEFT JOIN
   user_follows AS follow2 ON user.id = follow2.shown_users_id
WHERE
   user.id != 2 # (user that I don't wan't to select)
LIMIT 1;

But it would still return me the row with user 3 in it.

So if in my users table I have user 2,3,4, and with the following data in user_follows table:

I only want to get user 4, since it is not associated with user 2 at this moment.

You need 2 anti-joins. Written with either two LEFT JOIN / IS NULL:

SELECT
   user.id
FROM
   users AS user
LEFT JOIN
   user_follows AS follower ON  follower.users_id = user.id
                            AND follower.shown_users_id = 2
LEFT JOIN
   user_follows AS followed ON  followed.shown_users_id = user.id
                            AND followed.users_id = 2
WHERE
   follower.users_id IS NULL
  AND
   followed.users_id IS NULL
  AND
   user.id <> 2 ;

or with two NOT EXISTS subqueries, which I find far more readable:

SELECT
   user.id
FROM
   users AS user
WHERE
   NOT EXISTS
      ( SELECT *
        FROM user_follows AS follower
        WHERE follower.users_id = user.id
          AND follower.shown_users_id = 2
      )
AND
   NOT EXISTS
      ( SELECT *
        FROM user_follows AS followed
        WHERE followed.shown_users_id = user.id
          AND followed.users_id = 2
      )
AND
   user.id <> 2 ;


You should create and index anyway.

Run the following query:

SELECT COUNT(1) rowcount,my_column FROM mTable GROUP BY my_column
UNION SELECT COUNT(1) rowcount,'Whole Table' FROM mTable;
SELECT DISTINCT my_column 'Cardinality' from mTable;

If the number of rowcount for NULL is more than 5% of Whole Table, an index might not be used be the Query Optimizer if your WHERE clause is AND my_column IS NULL or AND my_column IS NOT NULL (especially if the number of rows with NULL is about the same as the number of rows with non-NULL). If the cardinality of my_column is very low (indicating a small number of unique values) an index on my_column may never be be used at all.

That warning you mentioned may go away if you create the index on my_column, but the index may never be used if the circumstances I mentioned apply.

The index may be of value if NULL my_column is like 90% of the table.

My recommendation is not to make one at this time.


Postgres function assign query results to multiple variables I need to assign values to 2 variable as below in Postgres function.

a := select col1 from tbl where ...
b := select col2 from tbl where ...

How can I assign 2 values to 2 variables in one line command?

Like

a,b := select col1,col2 from tbl where ...

As said in  "40.5.3. Executing a Query with a Single-row Result" (emphasis mine):

  The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause.

So this should work:

SELECT col1, col2 INTO a, b FROM tbl WHERE...;


How to estimate the storage needed for a database? I ran across this questions trying to dive into a computer measurements worksheet online and for the life of me could not figure it out. This is the question:

Assume each record in a database requires 1Kib and there should be approximately 5 million records that need to be stored. Estimate the amount of storage needed in gibibytes for the database.

Any help would be extremely appreciated because I'm completely lost on this question even though it sounds like it should be simple. Thanks in advance.

The following would be an estimate that assumes full pages and doesn't take into consideration any database overhead (page header, etc.) or compression.  It would be simple math

5,000,000kb / 1024 = 4882.8125mb

4882.8125mb / 1024 = 4.76837158203125gb

But, I agree with other comments - you should create a test database and see how close the numbers match up.


backup postgresql database to submit with project I have a final year project written in Matlab in conjunction to a postgresql database called 'hm'.

I asked in which way could I submit the database and I was told to perform a back up of the database using pgadmin3.

What is the recommended format? I want the person to be correcting the assignment to be able to use my own database in his own postgresql version. Which format and options should I check to ensure that they can work with the backup?

I am using postgresql 9.4.

Use pg_dump -Fc and tell them to load it with pg_restore.

If you've created user accounts and other global objects you'll want to also provide a pg_dumpall --globals-only to them.


SQL Server - Round a number when it's one decimal before .50 I would like to know if there is a way to round a number if it's one decimal before 50.
i.e,
I am using the below code and it works, it gives 8.50:
select Round(8.49,1) = 8.50

The problem comes when I want to round the below one, I would expect this one to be only 8.48 but results in 8.50 as well.
select Round(8.48,1) = 8.50

The idea is to keep the 8.48 as it is, but when it's 8.49, it needs to be 8.50.

If you want something other than a standard rounding, you will probably need to use a nested CASE statement.
Maybe take something like the below and tweak it:
DECLARE @Num DEC (10,2) = 8.49 --8.48

SELECT
CASE
WHEN @Num - FLOOR(@Num) >= 0.5 THEN ROUND(@Num,1)
WHEN @Num - FLOOR(@Num) = 0.49 THEN FLOOR(@Num) + 0.5
ELSE @Num
END AS NewValue


Upgrade a database from SQL Server 2000 to SQL Server 2014 I have database in SQL Server 2008 R2. I took a backup of this database and restored that on a SQL Server 2014 server.  However, some of the database queries are not looking compatible to the new version.

For example *= operator is throwing errors in SQL Server 2014.

I don't want to change each query where I get issue.  Is there any way to upgrade the older version of SQL Server backup to work with the newer version of database?

You cannot restore a SQL Server 2000 database onto a SQL Server 2014 instance. You need to restore to SQL Server 2008 or 2008 R2 instance first, then take a backup of that database which you can then restore to 2014.

There a lot of features from SQL Server 2000 that have been discontinued in SQL Server 2014.  See this list for the details about what features have been discontinued, and what to use as replacements.

Unfortunately, you have no choice but to re-code queries that use the ancient =* or *= join syntax.

SQLMag has a great article on how to do the migration.


Does CUBRID have Information Schema Views? I am trying to access the Information Schema views as defined in SQL92, and cannot find any in the CUBRID RDBMS. Has CUBRID implemented this specification and how can I access them?

CUBRID version: CUBRID 10.1, manual: https://www.cubrid.org/manual/en/10.1/

There is something called System Catalog: https://www.cubrid.org/manual/en/10.1/sql/catalog.html, is this possibly the same, and why is it not called "information_schema" as specified in the SQL92 specification?

When querying the database for my table book i get following:

SELECT * FROM db_class WHERE class_name LIKE 'book';

class_name = book
owner_name = DBA
class_type = CLASS
is_system_class = NO
partitioned = NO
is_reuse_oid_class = YES
collation = iso88591_bin
comment =

Are there no schemas in CUBRID?

        Configuration configuration = new Configuration()
                .withJdbc(new Jdbc()
                                .withDriver(DRIVER)
                                .withUrl(URI))
                .withGenerator(new Generator()
                                .withGenerate(new Generate()
                                                .withDaos(true)
                                                .withImmutablePojos(true))
                                .withDatabase(new Database()
                                                .withName(DATABASE)
                                                // .withIncludes(".*")
                                                // .withExcludes("")
                                                .withInputSchema("jq"))
                                .withTarget(new Target()
                                                .withPackageName(PACKAGE)
                                                .withDirectory(DIRECTORY)
                                                .withClean(true)))
                .withLogging(Logging.TRACE);

        GenerationTool.generate(configuration);

I need it for code-generation with JOOQ, what catalog/schema do I have to specify?

Fixed it. It works only if you fully remove any schema or catalog information:

        Configuration configuration = new Configuration()
                .withJdbc(new Jdbc()
                                .withDriver(DRIVER)
                                .withUser(USER)
                                .withPassword(PASSWORD)
                                .withUrl(URI))
                .withGenerator(new Generator()
                                .withGenerate(new Generate()
                                                .withDaos(true)
                                                .withPojos(true)
                                                .withImmutablePojos(true))
                                .withDatabase(new Database()
                                                .withName(DATABASE)
                                                .withIncludes(".*")
                                                .withExcludes("_?db_.*?"))
                                .withTarget(new Target()
                                                .withPackageName(PACKAGE)
                                                .withDirectory(DIRECTORY)
                                                .withClean(true)))
                .withLogging(Logging.TRACE);

        GenerationTool.generate(configuration);

This archived manual page of the CUBRID project was also helpful: https://web.archive.org/web/20160819080919/https://www.cubrid.org/wiki_apps/entry/jooq-cubrid-tutorial


Declaring a scalar variable that its a table valued parameter? I get error 137 that I must declare a scalar variable (@facilities), although I'm declaring it in the Create stored procedure as a readonly variable, so I wasn't sure what I'm missing here. any advice will be appreciated.
I created a table valued parameter to hold multipple integer values, that I can call later in my stored procedure. the goal is to get all the documents implementing in each facility for the one division (6) for a specific date. Please see a section of the code below:

use DevDocMgt_new
CREATE TYPE dbo.Facilities AS TABLE
(FacilityID TINYINT PRIMARY KEY);
go

declare @F dbo.Facilities;
Insert @F Values (1), (2), (3), (4), (5);
go

Create PROCEDURE dbo.GetImpDocsTwo
(
@facilities dbo.Facilities ReadOnly,
@facilitydivisionID tinyint = 6)
AS
BEGIN
SET NOCOUNT ON;
SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,
        ts.TransmittalNumber, ts.TransmittalVersion,
        CASE WHEN TransmittalSiteLeadImpDate IS NOT NULL
                  AND (TransmittalSiteLeadFacility = @facilities OR TransmittalSiteLeadFacility = @facilityDivisionID)
             THEN TransmittalSiteLeadImpDate ELSE TransmittalImpDate END AS FacilityImpDate
FROM    dbo.DocumentInfo di
        INNER JOIN dbo.TransmittalSheet ts ON di.DocumentTransmittalImp = ts.TransmittalID
        LEFT OUTER JOIN dbo.TransmittalSiteLead tsl ON ts.TransmittalID = tsl.TransmittalSiteLeadTSID
        Inner Join @facilities as F on f.facilityID = tsl.TransmittalSiteLeadFacility

-- Select documents implementing in the facility
WHERE   (tsl.TransmittalSiteLeadFacility = @facilities OR
         tsl.TransmittalSiteLeadFacility = @facilitydivisionID)
end

The problem is here:

WHERE   (tsl.TransmittalSiteLeadFacility = @facilities

You are trying to compare a column on the left to a table on the right. This is like saying:

FROM sys.objects
WHERE object_id = sys.columns

...which obviously makes no sense.

That where clause doesn't belong there at all. You've already ensured that the facilities listed in the incoming table-valued parameter are matched to TransmittalSheet by using it in the JOIN clause. There is no need to filter on that match again.

My guess after several rounds of teeth-pulling is you want this:

CREATE PROCEDURE dbo.GetImpDocsThree
  @facilities dbo.Facilities ReadOnly,
  @facilitydivisionID tinyint = 6
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH f AS
  (
    SELECT FacilityID FROM @faclities
    UNION ALL SELECT @facilitydivisionID
  )
  SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,
    ts.TransmittalNumber, ts.TransmittalVersion,
    FacilityImpDate = COALESCE(ts.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
   FROM dbo.DocumentInfo AS di
   INNER JOIN dbo.TransmittalSheet AS ts
     ON di.DocumentTransmittalImp = ts.TransmittalID
   INNER JOIN f
     ON tsl.TransmittalSiteLeadFacility = f.FacilityID;
END
GO

Or just insert 6 into the TVP up front too, and ditch the additional single facility variable altogether.


Results not returning rows where second parameter should match null I'm trying to left join three tables using columns from the first and third tables as the filters. The second and third tables could have null values where I am trying to join. I want to be able to return all rows if I don't specify the second parameter (:store_number), but it only returns rows where the store_number is not null.

SELECT t1.host_name, t1.vcenter_name, t1.connection_state, t1.power_state, t2.cluster_name, t3.store_number, t3.district_number, t3.time_zone, t3.active
FROM host t1
LEFT JOIN cluster t2 ON t1.cluster_id = t2.cluster_id AND t1.vcenter_name = t2.vcenter_name
LEFT JOIN store t3 on t2.cluster_name IS NOT NULL AND t2.cluster_name = t3.store_number
WHERE t1.host_name LIKE :host_name AND t3.store_number LIKE :store_number
ORDER BY t1.host_name;

To clarify, not all hosts have clusters, and not all clusters associate to store numbers. I want to be able to return all rows even if I don't specify a store number in the parameter, but currently it only returns rows that have a store number that is not null. Passing to the parameter as %value%.

Here is what I am getting currently:

+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host_name   | vcenter_name  | connection_state | power_state | cluster_name | store_number | district_number | time_zone | active |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host1       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host1       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+

Here is what I would like to get:

+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host_name   | vcenter_name  | connection_state | power_state | cluster_name | store_number | district_number | time_zone | active |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host1       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host1       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host1       | vcenter2      | Connected        | PoweredOn   | NULL         | NULL         | NULL            | NULL      | NULL   |
| host1       | vcenter3      | Connected        | PoweredOn   | cluster3     | NULL         | NULL            | NULL      | NULL   |
| host2       | vcenter3      | Connected        | PoweredOn   | cluster3     | NULL         | NULL            | NULL      | NULL   |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+

Put t3.store_number LIKE :store_number in your JOIN ON criteria rather than the WHERE criteria.

Being in WHERE means the the store_number of NULL won't match the criteria however being in a LEFT JOIN, t3.* are padded in the result when the JOIN criteria isn't met.


NULL value self comparison in a table I am always puzzled regarding some mysterious t-sql behavior, like the following

-- Create table t and insert values.
use tempdb
CREATE TABLE dbo.t (a INT NULL);
-- insert 3 values
INSERT INTO dbo.t values (NULL),(0),(1);
GO
set ansi_nulls off -- purposely turn off, so we can allow NULL comparison, such as null = null
go
-- expect 3 rows returned but only 2 returned (without null value row)
select * from dbo.t where a = a

This is not about how to retrieve all rows in a table and also not about avoiding use of ANSI_NULLS.

I just want to solicit some insights why t-sql behaves like this.

This is a surprising behaviour but from MSDN page, SET ANSI_NULLS, we can at least know that is the expected behaviour. One more reason to never use ANSI_NULLS OFF:

  SET ANSI_NULLS affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal  NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.


Have several rows, or one single row with a huge comma separated data? I have many workers processes working at the same time. They grab an ID from a list called available_ids, work on that ID, and put that ID on a list, called used_ids.

The list available_ids can have the same ID several times, so when a worker pulls an ID from it, it has to check if that ID is already in used_ids. If it is, it skips that ID as there's no need to work on it anymore.

This is all file based, and I'd like to transfer the lists over to a mysql database.

My question is, should I have one table called used_ids, and add each used ID to it as rows (one ID per row), or should I have one single row, with all my IDs, comma separated?

There are thousands of IDs. My fear is, since my workers have to check every single ID before doing their task, by running through sereval rows, wouldn't there be a big overhead, slowing down everything?

Thank you.

Use 1 row per ID, add an index on the ID. SQL is pretty good about finding a single value in a table, even if there are millions of rows. If you run periodic cleanup to wipe the old ID values out (like maybe have an automatic timestamp column and then have a job to drop all values older than X days), you wont run into any performance issues.

To give you an idea, in the production environment I work with, we have a similar table, about 40 columns wide, with some instances having >50 billion records, and pulling a single record out, queried by its unique, indexed ID, takes around 0.5s. Its very good hardware but its still pretty performant.


DB Index Maintenance I have a database which has almost 130 tables.

I am using the Ola Hallengren maintenance solution and IndexOptimize scripts.

I have two tables in particular that in my opinion needs to be better handled:

Table1: PK INT, almost 1.5 millions of rows
Table2: PK INT, foreign Key to Table 1, almost 6.5 millions of rows

On table2 I have created an index some time ago, following an azure recommendation, like this:

CREATE NONCLUSTERED INDEX [IX_Table2_ForeignKeyField]
ON[dbo].[Table2] (
    [ForeignKeyField]
)
INCLUDE([AnotherField])
WITH( ONLINE = ON )

When the IndexOptimize runs I can see that to rebuild the IX_Table2_ForeignKeyField index the stored procedure uses the following command

ALTER INDEX [IX_Table2_ForeignKeyField]
ON [mydb].[dbo].[Table2]
REORGANIZE
WITH (LOB_COMPACTION = ON)

The results are that it takes more than two hours to reorganize this index (previous reorganization was done 3 days ago) and at the end it says that

<ExtendedInfo>
  <PageCount>644096</PageCount>
  <Fragmentation>0.752683</Fragmentation>
</ExtendedInfo>

Is there any procedure that I can use to make this command run faster?

EDIT: One more info: Table1 has from 500 to 2500 insert per day. On table2 I have almost 10k insert per day growing.

Index maintenance is an I/O intensive workload. Under these circumstances please consider to scale to premium tiers prior to run index maintenance tasks, and when the workload finishes scale down to previous tier. You can automate that using T-SQL. That should reduce maintenance time.

Additionally, compacting large objects data (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml) can save disk space but it can increase the time it takes to maintain indexes. If you have a restricted time window for reindexing you can set  LOB_COMPACTION to OFF.

Hope this helps.


LAST_DDL_TIME Not Changing after compilation of package body On Nov 24 th,  we created the package body. Again today we recompiled it, but last DDL date has not changed. Is there any reason for this?

Below is the query I'm using

SELECT object_name, object_type, created, last_ddl_time, status
FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'xxxxxxxxx';

You might have looked for different object types. It shows the last DDL time for both package and package body.

I have a package/body EMP_ADMIN.

SQL> select object_type, last_ddl_time from  all_objects where object_name='EMP_ADMIN';

OBJECT_TYPE             LAST_DDL_TIME
----------------------- -----------------
PACKAGE                 05-12-17 22:49:50
PACKAGE BODY            05-12-17 22:52:34

SQL> alter package emp_admin compile body;

Package body altered.

SQL> select object_type, last_ddl_time from  all_objects where object_name='EMP_ADMIN';

OBJECT_TYPE             LAST_DDL_TIME
----------------------- -----------------
PACKAGE                 05-12-17 22:49:50
PACKAGE BODY            05-12-17 22:53:19

Look at the difference for DDL time for Package Body not for the Package.

And let's compile the package.

SQL> alter package EMP_ADMIN compile;

Package altered.

SQL>  select object_type, last_ddl_time from  all_objects where object_name='EMP_ADMIN';

OBJECT_TYPE             LAST_DDL_TIME
----------------------- -----------------
PACKAGE                 05-12-17 22:53:44
PACKAGE BODY            05-12-17 22:53:44

The compilation of the packages changes DDL time for both Package and Body.


Setting default value equal to parent's value I have a table of categories.  Each category has a parent category (other than the root category) and a color.  I currently have parent set as a FK referring to the key of the same table.

Is there a way to set default color equal to the parent's color?  I've tried setting it up as an after insert trigger or a default constraint, but I'm having issues with self reference in the query itself.  I have a feeling I'm just not 100% understanding how to do it as an inner join.

"Redundant" information is a no-no in 'proper' schema design.  The "right" way to solve your problem is to leave NULL in the child and reach for the parent's color in the SELECTs.  (See the COALESCE function and LEFT JOIN to avoid stumbling in the root category.)


Perfmon Resource Utilisation for PROD SQL Server I want to create a new Data collector set to find out my Memory utilization in my PROD SQL Server for at least 2-3 weeks.

Can anyone let me know if this takes too  much resource utilization which in turn reduces Performance.

Below are the performance counters I am planning to create as adviced :

Memory – Available M Bytes

SQL Server: Buffer Manager – Page Life Expectancy (Higher is good)

SQL Server: Memory Manager – Memory Grants Pending (Lower is good)

SQL Server: Buffer Manager, Buffer Cache Hit Ratio (higher is better)

SQL Server: Memory Manager – Target Server Memory

SQL Server: Memory Manager – Total Server Memory

SQL Server: SQL Statistics – Batch Requests/sec

SQL Server: SQL Statistics – Compilations/sec

Perfmon is pretty light weight when it comes to data collection. Collect the data in blg files and set a sensible collection frequency. Jonathan has written about Essential PerfMon counters for DBA that will give you a good start.

For automation part you can use logman and load data into sql server or as shown here

You can even Setup Perfmon with PowerShell and Logman.

Alternatively, you can use PAL or Get-PerfMonSummaryStats.ps1 - Powershell script from MSFT

sys.dm_os_performance_counters exposes perfmon counters in raw form.


How to re-sync the Mysql DB if some Masters have different database incase of MySQL Group replication? Somehow one of my server got into split brain situation, how can we reset & sync back one of servers's data with another master.

i won't mind lossing one's set of new data but i want now that detached server to in sync with others.

suppose i've only two servers. because after split brain now its not more be able to join group replication because it has many more new transaction and another one also have many new.

there is similar question for normal master slave mysql config
How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?

but here i am talking about group replication in mysql.

I assume you have a group with transactions A and B and your diverged server has some transactions C that are not in the group, so you cannot join it.

In this case you can setup a group member (if in single primary mode it shall be the primary) as a slave of this diverged member using master-slave replication.
This way all the data that was written into the diverged member will be replicated to the group.

I'm assuming 1) all the data on the diverged member has GTIDs that were not reused by the group and 2) your data is safe to replicate to the group, i.e., it is not outdated or has any other data logic issue that only you can know.

When group has this data, you can stop the master slave connection and add the diverged member to the group.


How many SELECT/UPDATE/INSERT Operations can you run on a 4 core / 8 GB RAM server? Assume you are working on a web project with html/css/js frontend with php in backend. How many SQL Operations (executed by mysqli for example) can do do with 4 core / 8 GB RAM server ? The database is running on a dedicated server.

I am only talking about some simple SELECT/UPDATE/INSERT Operations with maybe 2-3 subselects.

I guess the higher complexity level of the query the higher the (cpu/ram/io ?) ?

Is there some kind of "table" like 2c/4GB -> 10000 OPS/second , 4c/8GB -> 50000 OPS/second ?

I alreay took some tries in google but i only got links to specific database hardware requirements from ibm or sap hana.

Is this just a stupid question or can't you make a "general" recommendation. Of course i know that you cant make an exact recommendation but maybe a roughly one ?

Thanks in advance.

The maths behind it is far more complex than you seem to be implying.

Sub-selects count as queries in their own right, so a query with 3 sub-selects counts as 4 queries.

Size of the data matters as does indexing and indexability of every query.

If a query is returning 100,000 rows, it'll be orders of magnitude slower than a query returning 1 row.

If a query needs to check 1M rows to compute the result, it'll be slow regardless of the fact that it returns 1 row.

The "unit" of complexity is not a query. The CPU in question might get you 10,000 queries per second of one query on one data set, but 0.001 queries per second of another query on the same data set or 0.00001 qps of the same query on a different data set.

There is almost no limit on how computationally complex "a query" can be.


Using sub-query column in where clause This question has been asked several times and even answered as well but it's not helpful to me, that's why I am posting it again. I have this query:

select u.*,l.*,
(
    select count(cr.id)
    from cloud_recordings cr
    left join cameras c
        on c.owner_id=u.id
    where c.id=cr.camera_id
) valid_licence
from users u
left join licences l
    on l.user_id=u.id;

It works fine, but when I put a where clause at the end:

select u.*,l.*,
(
    select count(cr.id)
    from cloud_recordings cr
    left join cameras c
        on c.owner_id=u.id
    where c.id=cr.camera_id
) valid_licence
from users u
left join licences l
    on l.user_id=u.id
where valid_licence > 0;

It simply gave me an error:

  ERROR:  column "valid_licence" does not exist
  LINE 4: left join licences l on l.user_id=u.id where valid_licence >..

You can't reference an alias on the same "level" where it was defined. You need to wrap your base query into a derived table:

select *
from (
  select u.*,
         l.*,
         (select count(cr.id) from cloud_recordings cr left join cameras c on c.owner_id=u.id where c.id=cr.camera_id) valid_licence
  from users u
    left join licences l on l.user_id=u.id
) t
where valid_licence > 0;


How to put SQL Server 2012 in single-user mode I am trying to restore master DB from a backup and getting below error:

  To restore the master database, the server must be running in
  single-user mode. For information on starting in single-user mode, see
  "How to: Start an Instance of SQL Server (sqlservr.exe)" in Books
  Online.

I thought it would be simple however I have tried three different approach to meet the requirement but none of them worked.

First I used SSMS, right click on master DB > Tasks > Restore > Database.
And check Close existing connections to destination database after selecting the backup.
It returns me with below:

  System.Data.SqlClient.SqlError: Option 'SINGLE_USER' cannot be set in
  database 'master'. (Microsoft.SqlServer.SmoExtended)

So I searched a bit and found an instruction from Microsoft

But after I added the startup parameter with -m and restarted the MSSQL Service.
I still got the first error message while I was trying to restore master by

RESTORE DATABASE master FROM DISK='C:\...\master.bak' WITH REPLACE;

My last attempt was to launch SQL Server service by

net start MSSQLSERVER /m

Then connect it by sqlcmd and send my restore by it, like

sqlcmd -S localhost

And execute the restore cmd in dos command window.

Unfortunately, this also threw the first error message.
It seems all operations above didn't really turn the SQL Server in single user mode for me.

Don't know which part I am doing wrong or what should I do next.

To restore master database SQL Server must be brought in single user mode, not the master database. As a fact you cannot bring master database in single user mode you would get error what you have pasted in the question.

To simply start SQL Server in single user mode. First stop SQL Server agent and then open command prompt with admin privileges.
Run:

net stop MSSQLSERVER

MSSQLSERVER is default instance. If you have named instance it would be.

net stop mssql$instance_name.

After above is done check SQL Server services are stopped.

Now open new command prompt(as administrator) go to Binn folder and run

sqlservr -m--if you have default instance

sqlservr -m -s instance_name--for named instance

NOTE: It is sqlservr

This will cause SQL Server to start in single user mode. You can check more details Here.

Now go ahead and launch new Command prompt as admin and login as below

SQLCMD –S hostanme\instance_name - E--for named instance

sqlcmd -S -E--for default instance

-E parameter is here for trusted connection and -S is for server name

After you are able to connect, run the restore command.

Now Again stop SQL Server service and start, you can do this from SQL Server configuration manager.

Please read This Blog for your reference


Things affected globally in a postgresql transaction Correct me if I am using the correct term: "affected globally" I mean, if I increase the sequence value, it will be increased for all other running transactions without the need for commit....

I know that sequences have this behavior, but my doubt is there is a list with some other roles that are affected globally, if i update it inside a transaction?

For example, if I set alter table mytable enable trigger all; this will affect the trigger behavior only in the local transaction or in other transactions, note that this is only a example, i am thinking about a lot of other things that i am need to be carefully about it.

alter table mytable enable trigger all; cannot affect concurrent transactions directly because the command takes an exclusive lock on the table before the change is applied. And the lock stays until the end of the transaction. (Of course, the lock itself can have indirect effects on concurrent transactions.)

Some effects that are visible to concurrent transactions before COMMIT (or even after ROLLBACK):

Locks, obviously (this is how other conflicts are avoided).
Sequences (like you already mentioned).
Entries in unique indexes (conflict with dupes even when still uncommitted).
Data written to log files (or anything else written to files).
Effects of dblink calls (emulating autonomous transactions, effectively).
Changes to prepared statements (only matters in case of a ROLLBACK, since prepared statements are only visible within the same session).
Long running transactions also can keep VACUUM from doing it's job, since rows still potentially visible in the snapshot cannot be removed. Can even lead to transaction ID wraparound in very extreme cases. (Well, other security measures kick in before that actually happens.)

I am sure there is more ...

Some commands cannot be executed inside a transaction at all. Like CREATE DATABASE, CREATE INDEX CONCURRENTLY or VACUUM.

Related:

Do stored procedures run in database transaction in Postgres?
How to use RETURNING with ON CONFLICT in PostgreSQL?
In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?


SQL Server 2012 data striping Is data striping possible like mentioned here data striping in older SQL Server 2005
in the newest version of SQL Server like SQL Server 2012?

I know this is possible in 2008 and 2008R2.

Yes, as reflected in my comments, this capability still exists in SQL Server 2012. There is just an issue in Books Online currently that makes it appear that there isn't a 2012 version of this topic, if the current version of the topic you're in is not the 2012 version.


Transactional Replication issues I have a question, I am managing 2 SQL servers

is Master Server (Publisher + Distributor)
is Client Server (Subscriber)

The client is basically owned by a client who is buying data from us.

Both Servers are replicated with each other using Transactional-replication.
Only selected Tables of a single database are replicated from the Master Server to Client Server.

Basically there are several problems i want to discuss.

Problem 1:
When I add or delete any data table and take snapshot of replication then all databases vanish from customer side and after that it rebuilds in 20-30 minutes (I only have limited access so don’t get logs)

Problem 2:
When I reinitialize subscription then it takes a lot of time to completing it and it is showing partially applied scripts of tables in monitoring.

Problem 3:
Publisher to Distributor showing high commands and transactions count but actually on distributor to subscriber commands and transaction counts are more than 100’s of times lower as compare to publisher section.

Problem 4:
Latency is Very high between Publisher-Distributor (around 5+ minutes)(Server-1) and jumps to 7 minutes then automatically less and stay on some seconds but average is really very high. Distributor-Subscriber latency seems fine.

Problem 5:
Log Size of Master DB increases and occupying space around 3.5 GB (Normally 3 GB with 80-87% free space) whether Data File Size also increases and now it’s 4 GB+ (Normally 3.3 – 3.5 GB)

First, you can set up Canary table and other detailed monitoring of transactional replication per Kendra Little'ss article Monitoring SQL Server Transactional Replication.  Canary tables, the easiest step in the article, are described as:

  It’s easy to build your own system for tracking replication latency for each publication. Here are the ingredients for the simplest version:

  Add a table named dbo.Canary_PubName to each publication
  dbo.Canary_PubName has a single row with a datetime column in it
  A SQL Server Agent job on the publisher updates the datetime to the current timestamp every minute
  A SQL Server Agent job on the subscriber checks dbo.Canary_PubName every minute and alerts if the difference between the current time and the timestamp is greater than N minutes

Then take a look at the activity happening on the tables being replicated.  It sounds like most of your problems are related to very high activity - your log size moving from 3GB with only 13-20% used, to 3.5GB (meaning all 3GB was used) is an indicator of big transactions, which may be saturating your replication bandwidth.

Also, if they take 20-30 minutes to replicate when reinitialized, that's a combination of their size and your free bandwidth.

So, the general fixes are:

Increase the bandwidth
Reduce the amount of data

Use the smallest integer based keys you can, and simply replicate lookup tables with descriptions, rather than have descriptions in your tables.

BIT when possible, then TINYINT, then SMALLINT, then INT, then BIGINT where you're forced to.

Replicate indexed views instead of actual tables (publisher = indexed view, subscriber = table) - send over only the required columns, and only the required rows.
Don't use NCHAR/NVARCHAR unless you need to - use CHAR/VARCHAR when you know you only have, or perhaps even should have, no UCS-2 "Unicode" data.

Use CHAR when the average size of data is within 2 bytes of the maximum field size, since VARCHAR has a 2 byte overhead to store length

Reduce the number of transactions on the replicated data

Whatever you've got happening that causes all that activity on the replicated tables, analyze it.  Is it required?  Could it be re-written to minimize the amount of data being replicated?

Don't touch the same row more than once, i.e. instead of INSERT, UPDATE, UPDATE, UPDATE, just a single INSERT to the final table - perhaps work in #temp tables before then?  And so on and so forth.

If they don't absolutely need up to the minute data on any given table, then don't replicate your the core tables that get all this activity.

For each non-realtime-required table, create reporting tables on the publisher that get updated only every so often (once a week, once a day, once an hour, whatever), and replicate those.
And make sure to carefully use only the UPDATEs, INSERTs, and DELETEs that are required - the goal is to minimize replication load.
Don't use MERGE; it has too many issues.

Split the tables up into multiple publications, so you aren't taking a snapshot of, and reinitializing, every table every time you have to add or remove one.

I.e. If you add/drop/alter an article in Publication C, you need to reinitialize and snapshot that publication, but publications A, B, D, E, and F aren't affected by this.

And thus it moves less data, which in turn takes less time.
And the subscriber retains access to all the articles in all the other publications
So split them up along functional usage lines; if tables A, E, and G are used together most of the time, while C and D aren't involved in most of those queries, that's a natural split that will leave your client happier; at least some functions continue unobstructed during re-initialization of either set.

Increase the bandwidth


SSISDB Start and Stop time for each Package I am trying to query the SSISDB Project Catalog to review the Start and Stop time for each SSIS package.
Is there any native query to conduct this?

I am checking [internal].[executions],  SSISDB.catalog.operation_messages, etc

Running this query does not work, if I have a Master Package calling Child Packages.

select start_time,end_time,*
from catalog.executions cc
order by cc.start_time desc

So for each package, I need to know Start and Stop time.

You can use the internal.executions and internal.operations tables to view this data. I would think this would also work for packages calling other packages as well, if they are both within the same SSISDB Catalog.

SELECT E.execution_id,
       E.folder_name,
       E.project_name,
       E.package_name,
       E.environment_folder_name,
       E.environment_name,
       CASE O.[status]
           WHEN 1 THEN 'Created'
           WHEN 2 THEN 'Running'
           WHEN 3 THEN 'Canceled'
           WHEN 4 THEN 'Failed'
           WHEN 5 THEN 'Pending'
           WHEN 6 THEN 'Ended Unexpectly'
           WHEN 7 THEN 'Succeeded'
           WHEN 8 THEN 'Stopping'
           WHEN 9 THEN 'Completed'
       END AS [Status],
       O.start_time,
       O.end_time
FROM internal.executions AS E
INNER JOIN internal.operations AS O
    ON E.execution_id = O.operation_id;

You can refer to these articles for more information:

https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-operations-ssisdb-database?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-executions-ssisdb-database?view=sql-server-2017


pg_dump without postgresql I have a server with Postgresql. I don't have access to this server, just the endpoint to connect with psql. I want to backup data from this server to another server using pg_dump. However, my second server does not have postgresql and I don't have root privilege to install it either.
Is there any way to run pg_dump without install postgresql?

It is possible to install postgresql on my client and pg_dump from it but I'm afraid the database is too big for my laptop.

Basically we don't need any Postgresql server to take the backup. Postgresql client is enough for this.
But you must have sudo privilege to install this client.
For Ubuntu:
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >>  /etc/apt/sources.list.d/pgdg.list
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-client

For RedHat/CentOS/Amazon Linux:
Download the rpm here:
https://yum.postgresql.org/repopackages.php#pg96
yum install postgresql

Dump the Database
pg_dump -h remote_address -U username -D dbname -Fp > backup.sql

More about Pg_dump:
https://www.postgresql.org/docs/9.6/static/app-pgdump.html


Why do PostgreSQL arrays have different starting indices for 1D and 2D arrays? I'm working with point data in the form (p1, p2), which is represented in text as ((x, y), (x, y)). I would like to get 4 values out of this string (x1, x2, y1, y2), But I'm having trouble working with array indices.

For example:

(1) Get a point[] representing the 2 points

select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[];

-- This correctly gives me {(64.0,38.0),(-26.0,-40.0)}

(2) Get the first point from that array

select ( select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[] )[1]

-- this gives me (64.0,38.0) (i.e. index = 1 gives me the FIRST point)

(3) Get the x value of the first point

select ( select ( select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[] )[1] )[0]

-- This gives me 64 (i.e. index = 0 gives me the FIRST point)

It seems that in a 2D array, indices start at 1. But in a 1D array, indices start at 0. How can this be????

I've tested this on Dbeaver and separately on PG Admin 4 and I get the same result.

You don't have a two-dimensional array, you have an one dimensional array of points.  That is not the same thing, even if they do have some similarities.  I think a "point" is a type implemented as a fixed length array type, as discussed in the docs

  For historical reasons (i.e., this is clearly wrong but it's far too late to change it), subscripting of fixed-length array types starts from zero, rather than from one as for variable-length arrays.


Compare Strings ignoring accents in SQL I would like to know if there is an easy way to compare two text values ignoring the accents and upper case. Im working with an Oracle database.

Thank You!

Specifying a Case-Insensitive or Accent-Insensitive Collation

  Append _AI to an Oracle Database collation name for an
  accent-insensitive and case-insensitive collation.

So I could set, for example:

alter session set nls_sort=hungarian_ai;

But this above will affect only sorts and not the comparisons. You can set comparions to use the sort settings:

NLS_COMP

  LINGUISTIC

  Comparisons for all SQL operations in the WHERE clause and in PL/SQL
  blocks should use the linguistic sort specified in the NLS_SORT
  parameter. To improve the performance, you can also define a
  linguistic index on the column for which you want linguistic
  comparisons.

alter session set nls_comp=linguistic;

With both of the above set:

SQL> select 'equals' as result from dual where 'ÁbCdEf' = 'aBcDéF';

RESULT
------
equals


db_datareader, db_datawriter and db_owner permissions First of all, sry because I am not a DBA and I'm not familiar with all the permissions.

My question is, if I give a user db_owner permissions on one database. Do I need to give also db_datareader and db_datawriter permissions to have full control? I think db_owner is sufficient to execute all kind of queries on the database? (create, delete, alter, select and all other stuff).

So, in short: If I choose db_owner, then db_datareader, db_datawriter are automatically included. Am I right?

According to Database-Level Roles:

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

A member of this Database level role can do whatever he wants on the database. This also include SELECT, INSERT, UPDATE and DELETE data.

You still have to make sure that you are not giving too much rights to a user when you make him a menber of db_owner...

You can connect with this user on your database and run this query to see effective Database level permissions using sys.fn_my_permissions:

SELECT *
FROM sys.fn_my_permissions(null, 'database')
--WHERE permission_name IN ('SELECT', 'UPDATE', 'DELETE', 'INSERT')
ORDER BY permission_name

Output with only db_owner includes following rows:

entity_name subentity_name  permission_name
...         ...             ...
database                    DELETE
database                    INSERT
database                    SELECT
database                    UPDATE
...         ...             ...


Measuring plan eviction We have a SQL Server 2016 SP1 with max memory set to 24GB.
This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored in the plan cache but the size of the plan cache is not increasing (approx 3.72GB).
I suspect that there is local memory pressure that lead to removal of plans from the cache. The plan cache pressure limit is 5GB. (75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory>64GB). When a cachestore reaches 75% of the pressure limit, plans should be removed from the cache. In my case 75% of 5 GB is 3.75GB. So it is plausible this is the cause of the high compiles.
Is there a way to measure (perfmon, extended events, ...) the removal from plans out of the cache? So I can be certain local memory pressure is really the cause of the high compiles?

There's an XEvent for that:

  query_cache_removal_statistics

  Occurs when a query plan is removed from the plan cache and the
  historical statistics for the object are about to be destroyed

So something like:

CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER
ADD EVENT sqlserver.query_cache_removal_statistics(
    ACTION(sqlserver.sql_text))

Also if your plan cache has a large number of single-use plans consider setting optimize for ad hoc workloads.


SSMS: Import from flat file (.csv) In SQL Server Management Studio (SSMS) 2008 R2, I wish to import a not-quite-properly formatted CSV into a database table. I was trying to use the wizard at <database name>->Tasks->Import Data.

Differences from the CSV "standard"

null values are represented by a space between commas
some fields have commas in the middle of them and are not quote-escaped
there is a terminal comma on all lines except the header line (WTF??)

I eventually addressed these issues manually by editing the CSV prior to import. Issue 2 was fixed by replacing commas with dashes for the 4 families of cell values that were causing it. Issue 3 I addressed with a search-and-replace for those terminal commas. Issue 1, which was a problem for numeric fields, also with a search-and-replace , , -> ,,.

When my file did eventually import properly, null values were replaced with zeros, even though I had allowed null values in the field definitions.

My basic question: is there a way that I can alter the code for CAST/CONVERT operations during an SSMS import? The only stage at which I could get to SQL code was in the field definitions of the created table (from Select Source Tables and Views->Edit Mappings->Edit SQL - which was available if and only if I hadn't changed the destination table name on the Select Source Tables page), but that was just for the CREATE TABLE definition and didn't address the import process at all. It really seems to me that I should be able to allow a single-character space string to be cast to a null value when converted to a number (instead the wizard fails and states that a value is out of bounds). Further, the null string certainly should be cast to the null value integer, rather than 0.

I can save a SSIS package but it wasn't clear to me how to edit and run that to allow for what I want to do.  Is there a template file somewhere that I can edit to allow for null strings to be cast to null integer values (like something here....)? Is there a way to use parts of the Import Wizard to generate tSQL code? Should I instead use a BULK INSERT statement rather than the wizard?

The Import/Export wizard builds an SSIS package under the covers but the package the wizard builds only performs the E and L of ETL (Extract, Transform, Load). What you're looking for is T because you needed to transform this weird data into something more manageable.

For the weird header row, you can always specify that there is no header row and the connection manager should skip N rows.

Unfortunately,  there's not much you can do about the parsing itself when there's embedded delimiters. Logically, you can look and see that Kansas City, MO was all one field but the parser is simplistic and breaks the string on the delimiter, baring an escape character.

For spaces not empty strings, the Import-Export wizard way would be to import to a table of all varchar columns, and then write an extract query performing whatever preferred magic to make it into a NULL NULLIF(RTRIM(MyCol), '') AS MyCol

Random notes for future readers

If you're on 2005 or 2008, then the only way to get the SSIS editor, BIDS (Business Intelligence Design Studio) is to have a copy of the SQL Server installation media. 2012+ Microsoft allows for the download and installation of the SQL Server Data Tools-BI edition without the need for a SQL Server installation.

The Import/Export wizard is available in all editions of SQL Server, including Express, but the limitation here is that you cannot save the generated SSIS package if you are using Express. All the other versions work fine.


Calling MySQL stored procedure in python with field names I am trying to call a MySQL stored procedure that returns a single row, I can get the values back back using cursor.callproc() and cursor.stored_results(), but I can't get name of the field back 'fileHash'

If I call the SP directly I get results such as:

mysql> call GetAllFileHashesForDirectory('/myroot/');
+------------------+
| fileHash         |
+------------------+
| testa            |
| testb            |
+------------------+
2 rows in set (0.00 sec)

But, when I try and get the field name I get the following instead of 'fileHash':

(u'@_GetAllFileHashesForDirectory_arg1',)

The python code I use to get the value is:

self.__mySQLCursor.column_names

Do I need to specify the name in a stored procedure?  I am really confused.

sorry for the late answer, but I really do not think the chosen answer is good advice, the python mysql module has a method for this already, no need to do weird manips in the ddb. For this, just do:

cursor.callproc('sql_procedure', arguments)
for colid in cursor.stored_results():
    columnsProperties = (colid.description)
    print ([column[0] for column in columnsProperties])

you get all properties of the column in colid.description, the first property is the filename which is what you're after.


SQL Server Alerts on VMs Newbie here..

Hello
Searching online and I could not find any articles/blogs that pointed, related or had any opinions regarding SQL Server and SQL Server Alerts installed/running on virtual machines. Question is, how important, or relevant are SQL Server Alerts (19-25,823,824, or 825) if the environment SQL is installed on is running as a guest on a a VM? Are there other solutions.

Regards,

I would suggest that they are equally important on physical or VMs.

suggest you to create all critical alerts as highlighted by Glenn Berry.


Can someone explain why left joining two views in mysql is so slow? Here is a question I asked yesterday - https://stackoverflow.com/questions/22180727/left-joining-two-views-is-slow.

I got a good answer that helped me but I don't understand why the LEFT JOIN is so much slower than the lookup.  The LEFT JOIN was 16 seconds - and I am pretty sure my tables are at least 90% optimized - and when doing the lookup it is just .14 seconds.   When I LEFT JOIN tables it is not this slow so why views?

According to the MySQL Documentation on Views

  Views (including updatable views) are available in MySQL Server 5.6. Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

The first thing that must be realized about a view is that it produces a result set. The result set emerging from query invoked from the view is a virtual table because it is created on-demand. There is no DDL you can summon afterwards to immediately index the result set. For all intents and purposes, the result set is a table without any indexes. In effect, the LEFT JOIN you were executing is basically a Cartesian product with some filtering.

To give you a more granular look at the JOIN of two views, I will refer to a post I made last year explaining the internal mechanisms MySQL uses to evaluate JOINs and WHEREs (Is there an execution difference between a JOIN condition and a WHERE condition?). I will show you the mechanism as published in Understanding MySQL Internals (Page 172):

Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
For each table, decide whether a table scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
Determine the order in which tables should be joined when more than one table is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused tables from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

OK, it seems like indexes should be used. However, look closer. If you substitute word View for Table, look what happens to the mechanism's execution:

MECHANISM MODIFIED

Determine which keys can be used to retrieve the records from views, and choose the best one for each view.
For each view, decide whether a view scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the view scan becomes faster.
Determine the order in which views should be joined when more than one views is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused views from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

Every table (view) has no index. Thus, working with virtual tables, temp tables, or tables with no indexes really becomes indistinct when doing a JOIN. The keys used are just for JOIN operations, not so much for looking things up faster.

Think of your query as picking up two phone books, the 2014 Yellow Pages and the 2013 Yellow Pages. Each Yellow Pages book contains the White Pages for Residential Phone Numbers.

In late 2012, a database table was used to generate the 2013 Yellow Pages.
During 2013

People changed phone numbers
People received new phone numbers
People dropped phone numbers, switching to cell phone

In late 2013, a database table was used to generate the 2014 Yellow Pages.

Obviously, there are differences between the two Phone Books. Doing a JOIN of database tables to figure out the differences between 2013, and 2014 should pose no problem.

Imagine merging the two phone books by hand to locate differences. Sounds insane, doesn't it? Notwithstanding, that is exactly what you are asking mysqld to do when you join two views. Remember, you are not joining real tables and there are no indexes to piggyback from.

Now, let's look back at the actual query.

SELECT DISTINCT
viewA.TRID,
viewA.hits,
viewA.department,
viewA.admin,
viewA.publisher,
viewA.employee,
viewA.logincount,
viewA.registrationdate,
viewA.firstlogin,
viewA.lastlogin,
viewA.`month`,
viewA.`year`,
viewA.businesscategory,
viewA.mail,
viewA.givenname,
viewA.sn,
viewA.departmentnumber,
viewA.sa_title,
viewA.title,
viewA.supemail,
viewA.regionname
FROM
viewA
LEFT JOIN viewB ON viewA.TRID = viewB.TRID
WHERE viewB.TRID IS NULL

You are using a virtual table (table with no indexes), viewA, joining it to another virtual table, viewB. The temp table being generated intermittently would be as large as viewA. Then, you running an internal sort on the large temp table to making it distinct.

EPILOGUE

Given the internal mechanisms of evaluating JOINs, along the transient and indexless nature of the result set of a view, your original query (LEFT JOIN of two views) should be getting running times that are orders of magnitude. At the same time, the answer you got from StackOverflow should perform well, given the same JOIN algorithm I just described.

I hope the gory details I just posted answers your question as to why.