I think that you will understand the needing of normalization thinking to the anomalies that happens when you try to delete, insert or update rows in your tables.

Think to a 1NF tables with students, exam and the teacher of the professor of the exam.

An anomaly that happens when you delete a record is that you will lose every information associated to It. For example if only a student took an exam and you delete the tuple Student-Exam-Professor you will lose every information about that exam and the professor (the information about the professor that teaches that exam is lost because it existed in your database only on that tuple). 1NF allow this anomaly.

An anomaly that happens when you try to insert a new tuple is that you have to know all data associated to that tuple. If you want to insert a new tuple, you have to know the professor that teaches it (for example if you have a Not null constraint). For example, a Not null constraint on Student can't allow you to insert a new exam with its professor if you have no students to associate it. 1NF allow this anomaly.

An anomaly that happens when you try to update a value of a tuple in your a table is that you have to update all copies of that value.  For example, if you update some data about the professor, the exam or the student changes you have to update every tuple that have that values or you'll have inconsistency in your DB.  1NF allow this anomaly.

Don't forget the redundancy of a 1NF table that repeats every time the professor associated to that exam even if you need it only one time.

If you change it to a 2NF table, you will have two tables, the students-exams one and the exams-professors one, and you will have no anomalies.

Another NF is the Boyce and Codd Normal Form, BCNF. This one sometimes doesn't preserve some FD.


I'd recommend getting rid of that DISTINCT if possible, they tend to act as "make this subquery a black box to the DB engine" keywords, which doesn't help the query optimiser make good choices.

Try the following, if it works in Oracle (I'm a MS-SQL coder, sorry)

UPDATE orders
SET status = 'ORDERED'
WHERE orders.status = 'PENDING'
    AND EXISTS (
    SELECT 1
    FROM line_items
    INNER JOIN products ON line_items.product_id = products.product_id
    WHERE
        products.product_category = 'COFFEE' AND
        products.product_available_on <= CURRENT_DATE
        AND line_items.orderId = orders.order_id
);


Is there any drawback in altering an existing column datatype from TEXT to VARCHAR(n)?

The only risk is truncation of data.

Effectively the ALTER will

Create a new table with VARCHAR instead of TEXT.
Copy all the rows over.
Rename to put the new table in place of the original.

So, the question is, will copying a TEXT into a VARCHAR of the same CHARACTER SET do any harm?  The only possible thing is truncation.


Your syntax is slightly off. In SQL Server, here is how I would perform this update:

UPDATE s SET s.city = c.city
FROM dbo.Suppliers AS s
INNER JOIN dbo.Customers AS c
ON s.supplier_name = c.customer_name;

From my most popular answer on Stack Overflow.

Remember to always use the schema prefix and also it is often easier to just call those columns name instead of prefixing each with the table name.


How to detect DBCC ShrinkDatabase completion percentage?

Well, you shouldn't ever be using DBCC SHRINKDATABASE, IMHO - if you need to shrink files at all, you should think twice, maybe even three times, and even in the odd case where it really is warranted (hint: this should be rare), you should target each file individually using DBCC SHRINKFILE. Please read every single word on this page.

Anyway, you can see the progress using:

SELECT percent_complete, estimated_completion_time
  FROM sys.dm_exec_requests
  WHERE session_id = <spid running the shrink>;

Documentation for sys.dm_exec_requests.


How many operations SQL Server support per connection

a multi threaded application in which I will use a single SQL Server connection

Bad idea. A database connection usually is not thread-safe, and as you suspect, at any one time one connection can only execute one statement. You should open a separate connection for each application thread instead, or at least use a connection pool shared between the threads.


Trying to count multiple values and total

Try this for a count of competitions in each genre:

SELECT
  COUNT(competition.competition_id) as competition_count,
  genre.genre_id,
  genre.name
FROM
  genre
  INNER JOIN competition on ( genre.genre_id = competition.genre_id )
  INNER JOIN band on ( band.band_id = competition_band.band_id )
GROUP BY genre.genre_id, genre.name
ORDER BY genre.name;

A count of bands who have entered competitions sounds like a different query to me:

SELECT
  COUNT(band.band_id) as bands_in_competitions_count, --double counts bands in multiple competitions
  COUNT(distinct band.band_id) as bands_in_competitions_in_this_genre,
  genre.genre_id,
  genre.name
FROM
  genre
  INNER JOIN competition on ( genre.genre_id = competition.genre_id )
  INNER JOIN competition_band on ( competition.competition_id = competition_band.competition_id )
  INNER JOIN band on ( band.band_id = competition_band.band_id )
GROUP BY genre.genre_id, genre.name
ORDER BY genre.name;


SQL Agent reporting failure when all steps suceed

You probably have a job step that has the On success action: set to Quit the job reporting failure as can be seen in the following screen shot:

This can be verified by querying the sys.sysjobhistory and sys.sysjobsteps tables and checking the on_success_action column of the sys.sysjobsteps table. If the column contains a 2 and the step finishes, then it will quit the job and report a failure (Quit the job reporting failure).

Query Job History and Steps

Here is a script for your convenience to query the job steps and history:

SET DATEFORMAT DMY
GO
SELECT sj.name AS Job_Name,
       sjh.step_name AS Step_Name,
       sjh.step_id AS Step_ID,
       sjh.run_status AS [Job_Status],
       STUFF(STUFF(CAST(sjh.run_date as nvarchar(10)),5,0,'.'),8,0,'.') as HistRunDate,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') as HistRunTime,
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as HistRunDuration
       --,sjh.run_status AS JobStatus
       ,sjs.last_run_outcome as [Job Step Outcome]
       ,sjs.on_success_action as [On Success: 1=success;2=failure;3=next step]
       ,sjs.on_fail_action as [On Fail: 1=success;2=failure;3=next step]
FROM   dbo.sysjobs           AS sj
       left join dbo.sysjobhistory AS sjh --- was sysjobschedule sjc
            on sjh.job_id = sj.job_id
       left join dbo.sysjobsteps as sjs
            on sjh.job_id = sjs.job_id
            and sjh.step_id = sjs.step_id
WHERE  1=1

ORDER BY
       sjh.run_date desc,
       sjh.run_time desc

Sample Output

...of syspolicy_purge_history job which can be found on most SQL Servers:

Job_Name                | Step_Name                            | Step_ID | Job_Status | HistRunDate | HistRunTime | HistRunDuration | Job Step Outcome | On Success:                     | On Fail:
                        |                                      |         |            |             |             |                 |                  | 1=success;2=failure;3=next step | 1=success;2=failure;3=next step
------------------------+--------------------------------------+---------+------------+-------------+-------------+-----------------+------------------+---------------------------------+--------------------------------
syspolicy_purge_history | Verify that automation is enabled.   | 1       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:00     | 1                | 3                               | 1
syspolicy_purge_history | Purge history.                       | 2       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:00     | 1                | 3                               | 2
syspolicy_purge_history | Erase Phantom System Health Records. | 3       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:04     | 1                | 1                               | 2

You might have noticed that this job will report a success when the first step fails (On Fail = 1 ).

References

dbo.sysjobs (Transact-SQL)
dbo.sysjobhistory (Transact-SQL)
dbo.sysjobsteps (Transact-SQL)


How to allow to execute stored procedures but not writes?

This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start:

-- The user-defined role containing all required permissions
CREATE ROLE Readers AUTHORIZATION dbo;

-- Give read-only access to all tables,
-- views on those tables, and in-line
-- functions
ALTER ROLE db_datareader ADD MEMBER Readers;

-- Example: add a user (Bob) to the role
ALTER ROLE Readers ADD MEMBER Bob;

After this, Bob will have wide read-only privileges in the database. He will be able to read from all tables, views on those tables, and in-line functions. He will not be able to execute any procedures or use non-inline functions, however.

You will need to grant specific permissions to the Readers role for safe functions and procedures you wish Readers to have access to. There may be steps you can take to make this process easier (such as grouping objects into a schema and granting execute permission on the schema instead of individual objects) but there are too many details to cover here.

One thing to be aware of is that the lack of data modification privileges would not prevent a Reader from changing data via a stored procedure she has been granted execute permission on, if the procedure and object being modified share a common owner. Nor would an explicit deny be respected in this case. This feature is known as Ownership Chaining.


Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

I've found a fix for this, which was related to the package php71w-mysqlnd.x86_64 / php71w-mysql.x86_64

My local machine had php71w-mysqlnd.x86_64 installed whereas the live server had php71w-mysql.x86_64

After running the following two commands, it's now working

yum erase php71w-mysql.x86_64
yum install php71w-mysqlnd.x86_64

service httpd restart

Ref: https://stackoverflow.com/a/38006719/871150


What is the equivalent of SELECT  in Flux Query Language?

See Writing and Exploring Data as part of the Getting Started documentation.

> SELECT "address", "name" FROM "addresses"
name: addresses
---------
time                              address            name
2020-03-09T07:51:07.580664347Z    123 fake street    Billy Bob

>


Only rows in 7 partitions qualify the predicate(s) so there is no need to ever lookup rows in the other 3 partitions.

The query plan may choose to scan on B and probe (lookup) on A.

A Key Lookup is always a seek into the clustered index. There is no partition elimination in your original example by the way - the query processor is just reporting that it touched 7 distinct partitions (3..9) while executing the query. None of the lookups accessed other partitions.


Constrain/limit certain records in sql table definitions

To have maximum 10 rows for a user_id and post_id and given that MySQL has not even implemented CHECK constraints, only PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, it might be easier to enforce this in the application layer.

Of course it's better to have it in the database, as applications may have bugs and the constraint would have to be implemented in every application that uses the database. So, if you want to enforce this constraint in MySQL, the only way possible I think is to add another comment_no column (not to be confused with the comment_id column you may already have and may already be used, e.g. may be unique in the table or unique per post. This comment_no will be unique per post and user and will allow values only from 1 to 10.) The applications that insert data in the table would still have to provide this numbering (1 to 10). But the database would never allow more than 10 rows per user and post, using an additional table and the following FOREIGN KEY and UNIQUE constraints:

-- auxiliary table
CREATE TABLE number_of_comments_allowed
  ( comment_no TINYINT NOT NULL,
    CONSTRAINT number_of_comments_allowed_PK
        PRIMARY KEY (comment_no)
  ) ;

-- values needed in the auxialry table
INSERT INTO number_of_comments_allowed
  ( comment_no)
VALUES
  (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ;

CREATE TABLE comment
  ( user_id INT NOT NULL,
    post_id INT NOT NULL,
    comment_id INT NOT NULL,
    comment_no TINYINT NOT NULL,
    -- PRIMARY KEY (whatever),
    -- other UNIQUE and FOREIGN KEY constraints
    UNIQUE (post_id, user_id, comment_no),
    FOREIGN KEY (comment_no)
        REFERENCES number_of_comments_allowed (comment_no)
  ) ;

The only other thing that needs taken care of is to restrict access to the auxiliary table once it has been created and populated, so no user/session (except an admin) can insert, delete or update it.

The number (10) of rows allowed per user and post can be easily increased (or decreased) later by adding rows (or removing rows - but this may need to delete some rows from the comment table first) from the auxiliary table.


Can Oracle Text query return the terms found?

You might want to look into the MARKUP or HIGHLIGHT functions of the CTX_DOC package. An example of how MARKUP can be used for your query (this assumes your index is called idx_news and you have an ID column):

declare
  MARKLOB   clob;
  MARK1     number;
  MARK2     number;

  cursor NEWS_SEARCH is
    select * from NEWS;
begin
  for REC in NEWS_SEARCH loop
    CTX_DOC.MARKUP('idx_news', to_char(REC.ID), 'people OR country', MARKLOB);
    MARK1 := instr(MARKLOB, '<<<');
    MARK2 := instr(MARKLOB, '>>>');

    if MARK1 + MARK2 <> 0 then
      dbms_output.
       PUT_LINE(
        REC.ID || ',' || substr(MARKLOB, MARK1 + 3, MARK2 - MARK1 - 3));
    end if;
  end loop;
end;
/

What it does is mark up your text (surround the found items with <<<>>>) and outputs that new text to a clob which is then searched for the markup. All text that is found in your query will be marked up but my PL/SQL will only display the first result; it can be adapted to find them all and display only the unique ones. This is a simple example and perhaps can become too complicated for your original query but it's here to point the way.

Alternatively, a simple SELECT may suffice if all you have is an OR in your text query:

select decode(sign(SCORE(1))
             ,1, decode(sign(SCORE(2)), 1, 'both', 'people')
             ,'country')
         as WHICH
      ,TITLE
  from NEWS A
 where CONTAINS(TITLE, 'people', 1) > 0 or CONTAINS(TITLE, 'country', 2) > 0;

This checks the score for individual searches on 'people' and 'country' and works out which ones were positive, returning 'both' if both search terms were found.


SQL Server trigger (update or insert with inner join problem)

How about changing the trigger to a BEFORE UPDATE? That way you can check the values before any action has taken place.

EDIT

OK so I think I understand what you are trying to do. How about this:

ALTER TRIGGER [dbo].[killertrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  Merge killer as k
  Using (select serial, [kill] from inserted) as I (serial, [kill])
  On (k.serial = I.serial)
  When matched then
   Update set [kill] = I.[kill]
   When not matched then
   Insert (serial, [kill]) values (I.serial, I.[kill]);
END


I drew an ER diagram for the parkinplace model.

(The dia file can be found here)

A parkingplace can be assigned to at most  one employee. There may be parkingplaces that are not assigned to employes (e.g. parkingplaces for guests).
One can store the column/row coordinate of each parking place. This may be expected for this homework problem. One may not do this in practice because these coordinates are not realy useful
when on administrates the parking places. If there are parking places added that are not on this area then column and rows may not make sense. But if one has no column/row attribute a string attribute for specifying the location may be useful. This attribbute could hold the column/row information as string.
So basically the parking place table containts 100 entries, one for each parking place.

create table employee(employee_id number  primary key,
                      first_name varchar2(30),
                      last_name varchar2(30))
/
create table reservation(name varchar2(30) primary key)
/
create table parkingplace(parkingplace_id number
                            primary key,
                          reservation_name varchar2(30)
                            references reservation(name),
                          employee_id number
                            references employee(employee_id),
                          grid_column number not null,
                          grid_row number not null,
                          unique(grid_column,grid_row),
                         check(grid_column between 1 and 5),
                         check(grid_row between 1 and 20),
                         check(employee_id is null
                               or reservation_name !='guest') )
/
create index idx_reservation_name on parkingplace(reservation_name)
/
create index idx_employee_id on parkingplace(employee_id)
/

The following script fills the tables with testdata

insert into reservation(name) values('employees')
/
insert into reservation(name) values('guests')
/
insert into reservation(name) values('handicap')
/
insert into reservation(name) values('executives')
/
insert into reservation(name) values('contractors')
/
declare
n number;
begin
n:=0;
for r in 1..20 loop
  for c in 1..5 loop
    n:=n+1;
    insert into parkingplace(
      parkingplace_id,
      reservation_name,
      grid_column,
      grid_row) values(n,'employees',c,r);
    end loop;
  end loop;
end;
/

update parkingplace
 set reservation_name='handicap'
  where grid_column=1 or grid_column=5
/

update parkingplace
 set reservation_name='guests'
  where grid_row=1
/

insert into employee(employee_id, first_name, last_name)
            values(1,'Clark','Kent')
/
update parkingplace
  set employee_id=1
  where parkingplace_id=6
/
insert into employee(employee_id, first_name, last_name)
            values(2,'Loise','Lane')
/
update parkingplace
  set employee_id=2
  where parkingplace_id=7
/

insert into employee(employee_id, first_name, last_name)
            values(3,'Lana','Lang')
/
update parkingplace
  set employee_id=3
  where parkingplace_id=8
/

insert into employee(employee_id, first_name, last_name)
            values(4,'Bruce','Wayne')
/
update parkingplace
  set employee_id=4
  where parkingplace_id=9
/

If you want to display all 100 parkin places with the employee names if there is an employee assigned then you have to use a LEFT OUTER JOIN.

select
grid_row,
grid_column,
reservation_name,
first_name,last_name
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id);

Because we have stored the geometrical information (row and column number) we can output the row * column matrix with the appropriate query

select grid_row,c1,c2,c3,c4,c5
from (
  select
grid_row,
grid_column,
first_name,
last_name
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id)
 )
pivot (max(first_name||' '||last_name) for grid_column in (
  1 as c1,
  2 as c2,
  3 as c3,
  4 as c4,
  5 as c5))
order by grid_row;

A demonstration can be found in sqlfiddle

This PIVOT extension of the select statement is implemented in Oracle since version 11. Prior version of Oracle have to use technics similar to that shown by @kkarns in a comment.

select
 grid_row,
max(decode(grid_column,1, first_name||' '||last_name,null)) c1,
max(decode(grid_column,2, first_name||' '||last_name,null)) c2,
max(decode(grid_column,3, first_name||' '||last_name,null)) c3,
max(decode(grid_column,4, first_name||' '||last_name,null)) c4,
max(decode(grid_column,5, first_name||' '||last_name,null)) c5
from
parkingplace left outer join
employee on (parkingplace.employee_id=employee.employee_id)
group by grid_row
order by grid_row
;


How to trace a MySQL session like in Oracle

MySQL has "General Query Log". This logs everything that is going on MySQL server: users connecting, disconnecting, queries etc.

This query log is a file on your filesystem or (from 5.1.6 versions) table

Control the general query log at server startup as follows:

Before 5.1.6, the general query log destination is always a file. To enable the log, start mysqld with the --log[=file_name] or -l [file_name] option.
As of MySQL 5.1.6, the destination can be a file or a table, or both. Start mysqld with the --log[=file_name] or -l [file_name] option to enable the general query log, and optionally use --log-output to specify the log destination
As of MySQL 5.1.12, as an alternative to --log or -l, use --general_log[={0|1}] to specify the initial general query log state. In this case, the default general query log file name is used. With no argument or an argument of 1, --general_log enables the log. With an argument of 0, this option disables the log.
As of MySQL 5.1.29, use --general_log[={0|1}] to enable or disable the general query log, and optionally --general_log_file=file_name to specify a log file name. The --log and -l options are deprecated.

After starting query log, investigate the file (or table) for further information.

MySQL Query Log Documentation: http://dev.mysql.com/doc/refman/5.1/en/query-log.html


Run stored procedures from specific package/with specific tag at lower priority

I can't personally think of anything better for on-the-fly resource throttling based on to-the-second stats than Resource Manager. It can be a bit of a hassle to set up, but I don't imagine how writing custom code wouldn't just be reinventing the wheel that is Resource Manager.

I'm not aware of any reliable 3rd party vendors at this time.

I do know that you don't have to do anything to your stored procedures. IMO the best way to use Resource Manager and stored procedures is to use DBMS_SCHEDULER to tie the resource plan in with a scheduled job.

Documentation on dbms_scheduler is here: http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_sched.htm#ARPLS72235

Also, IIRC, remember when implementing that Resource Manager handles CPU and I/O, but not Memory utilization.


You need to unnest the array then extract the keys as columns from the resulting JSON values:

select td.id, td.name, tg.tag_data ->> 'name' as tag_name, tg.tag_data ->> 'scope' as scope
from todos td
  cross join lateral jsonb_array_elements(td.tags) as tg(tag_data)
order by td.id;

Online example: http://rextester.com/DWYIJ81620


OK, without any detailed info about the tables' relationships (1-1, 1-to-many, many-to-many) and the primary keys, it's not easy to deal with this. But lets try.

The FROM clause has a 6-table join:

FROM
  Bac              b0_  INNER JOIN
  Bachelier        b4_   ON b0_.bachelier_cne = b4_.cne INNER JOIN
  EtablissementBac e5_   ON b0_.etabBac_id = e5_.codeLieu INNER JOIN
  Delegation       d1_   ON e5_.delegation_id = d1_.codeLieu INNER JOIN
  MentionBac       m2_   ON b0_.mentionBac_id = m2_.codeMention INNER JOIN
  TypeBac          t3_   ON b0_.typeBac_id = t3_.codeType

Since 2 tables (m2_ and t3_) are involved in the WHERE clause:

WHERE m2_.codeMention IN ('TB',  'B')
  AND t3_.codeType IN ('114',  '129')

and one more (d1_) in the GROUP BY clause:

GROUP BY
  d1_.codeLieu,
  m2_.codeMention, m2_.libelleMention,
  t3_.codeType, t3_.libelleType, t3_.abbrType

we'll assume that these 3 should be join with proper INNER joins.

And since you are counting on b0_, this will probably have to be "LEFT" joined. That leaves with some choices about the other two tables (b4_ and e5_). So, lets rewrite the FROM part. First the 3 (sure JOIN) tables, then the ambiguous 2 and lastly the sure (LEFT JOIN) b0_:

FROM
  TypeBac          t3_  INNER JOIN
  MentionBac       m2_  INNER JOIN
  Delegation       d1_  INNER JOIN

  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu INNER JOIN
  Bachelier        b4_
                        LEFT JOIN
  Bac              b0_   ON b0_.bachelier_cne = b4_.cne
                        AND b0_.etabBac_id = e5_.codeLieu
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention

Well, that doesn't look correct. Because (except for the d1 - e5 join), we don't have a way to join the other 4 tables. All 4 are to be joined with the b4 which has to be last (as we want to LEFT JOIN it.) This observation leaves with one choice:

FROM
  TypeBac          t3_  CROSS JOIN
  MentionBac       m2_  CROSS JOIN
  Delegation       d1_  INNER JOIN

  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu
                        CROSS JOIN
  Bachelier        b4_
                        LEFT JOIN
  Bac              b0_   ON b0_.bachelier_cne = b4_.cne
                        AND b0_.etabBac_id = e5_.codeLieu
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention

Try changing the FROM clause as above. The only other option I see is the removal of the CROSS JOIN  Bachelier  b4_, as no column of this table is included in the SELECT.

After more comments and a request, here is one final trial:

SELECT COUNT( b0_.id ) AS sclr0,
       d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2,
         d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4,
       m2_.libelleMention AS libelleMention5,
       t3_.libelleType AS libelleType6
FROM
  TypeBac          t3_
    CROSS JOIN
  MentionBac       m2_
    CROSS JOIN
  Delegation       d1_
    INNER JOIN
  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu
    LEFT JOIN
  Bac              b0_   ON b0_.etabBac_id = e5_.codeLieu
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention
WHERE m2_.codeMention IN ('TB',  'B')
  AND t3_.codeType IN ('114',  '129')
  AND EXISTS
        ( SELECT *
          FROM Bac b
          WHERE b.etabBac_id = e5_.codeLieu
            AND b.typeBac_id = t3_.codeType
            AND b.mentionBac_id = m2_.codeMention
        )
GROUP BY
    d1_.codeLieu,
    m2_.codeMention, m2_.libelleMention,
    t3_.codeType, t3_.libelleType, t3_.abbrType ;


Notification email when deadlock is encountered in Oracle alert log

You might try using ADRCI.

ADRCI>
ADRCI> show alert -p "message_text like '%Global Enqueue Services Deadlock detected%' and originating_timestamp > systimestamp + INTERVAL '-5' MINUTE"

This will show your error within the past 5-minutes. You could put this into a shell script and add a cron job that runs it every 5-minutes.

#!/bin/bash
LOG = /home/oracle/log.txt
adrci_homes=( $(adrci exec="show homes" | grep -e rdbms ))

for adrci_home in ${adrci_homes[@]}
do
   echo $adrci_home' Alert Log' >> $LOG
   adrci exec="set home ${adrci_home}; show alert -p \\\"%Global Enqueue Services Deadlock detected%' and originating_timestamp > systimestamp + INTERVAL '-5' MINUTE\\\"" -term >> $LOG
done

num_errors=`grep -c -e 'Global Enqueue Services Deadlock detected' $LOG`

if [ $num_errors != 0 ]
then
     mutt -a $LOG -s "Deadlock Detected" admin@mydomain.com
fi


You turned on IMPLICIT_TRANSACTION mode, which will start a new transaction on any statement that writes to the database.  And that transaction must be committed or rolled back explicitly, even if the transaction is started inside a stored procedure.
EG
use tempdb
go
drop table if exists t
create table t(id int)
go

create or alter procedure foo
as
begin
  insert into t(id) values (1)
end
go
SET IMPLICIT_TRANSACTIONS ON
exec foo

select @@trancount

outputs
1


Ola Hallengren Maintenance script - Full database backup delays

It will depend on the resources available on the server.

  Does it take same amount of time even when the backups are made to run
  at the same time at 2AM(by different jobs) due to IO overhead?

You can set up parallel jobs and test.  You will need to watch, how many of full backups can run in parallel before other processes (if any) are effected (cpu, io, network bandwidth etc).

Recently there was another question where Brent Ozar explained the phases of backup and how you can test.

I will not repeat his words but you can check details here.

There are built in switches in SQL Server that you can use to speed up backup.
See this blog post.

I did not understand this question?  Did you mean if full backup run in serialized way?  Depends how you job is set up. You can make them parallel as @Ghauan explained in comment, you can make multiple jobs to run in parallel.

  Is this the normal way the FULL backups are made (with delays)?


Mysqldump multiple tables with where clauses in single transaction

MySQL dump has only a single where clause which makes the use hard in two tables like you do.

One way would be in t1, create a generated column called id that is effectively user_id.

ALTER TABLE t1
ADD id UNSIGNED INT GENERATED ALWAYS AS (user_id),
ADD INDEX id_key ( id)

Adjust type to match the type of user_id.

ref: https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html


MySQL unique constraint across two columns

I have modified ypercube's solution a little bit, so that both source and destination are not null, as is guaranteed by the original design. My CHECK constraints are commented out, as they apparently will not work in MySQL. I kept them as comments because they document my intent, and they will work on other RDBMS.

CREATE TABLE PointType
( PointTypeID tinyint unsigned NOT NULL,
  TypeDescription CHAR(20) NOT NULL,
  PRIMARY KEY (PointTypeID),
  UNIQUE (TypeDescription)
) ;

INSERT INTO PointType
  (PointTypeID, TypeDescription)
VALUES
  (1, 'Source'),
  (2, 'Destination') ;

CREATE TABLE PointUsageQuota
( RouteID int unsigned NOT NULL,
  PointTypeID tinyint unsigned NOT NULL,
  PointID smallint(5) unsigned NOT NULL,
  PRIMARY KEY (PointID),     -- and this is what all the fuss is about
  UNIQUE (RouteID , PointID, PointTypeID), -- target for the foreign keys
  FOREIGN KEY (PointTypeID)
    REFERENCES PointType (PointTypeID)
) ;

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
  SourcePointID smallint(5) unsigned NOT NULL,
  SourceTypeID tinyint unsigned NOT NULL,
      -- CHECK(SourceTypeID = 1),
  FOREIGN KEY (RouteID , SourcePointID, SourceTypeID)
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  DestinationPointID smallint(5) unsigned NOT NULL,
  DestinationTypeID tinyint unsigned NOT NULL,
      -- CHECK(DestinationTypeID = 2),
  FOREIGN KEY (RouteID , DestinationPointID, DestinationTypeID)
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  --  other fields
  PRIMARY KEY (RouteID)
) ;

Testing:

insert into PointUsageQuota
values(1,1,666),(1,2,354);

INSERT INTO Route VALUES (1, 666, 1, 354, 2);
-- this fails:
INSERT INTO Route VALUES (2, 666, 1, 354, 2);
-- this fails too:
INSERT INTO Route VALUES (2, 354, 1, 666, 2);


What kind of deadlock the deadlock_timeout config parameter detects in Postgresql?

A deadlock is a situation where multiple transactions conflict which each other in the locks they have cross-acquired. That situation is impossible to solve without aborting one of the transactions. The engine aborts such a transaction  with ERRCODE_T_R_DEADLOCK_DETECTED as the error.

The log_lock_waits is not primarily related to deadlocks, it's meant to alert about the situation that some queries are stuck waiting for locks, meaning that other transactions keep locks for too long.

  Are these actually two different types of deadlocks that are detected?

No. It seems you're confusing locks and deadlocks here.

  Is there a way to get an error when deadlock_timeout based deadlock detection happens?

It's automatic. If that error does not arise, it's because there is no deadlock.

  How can actually a ERRCODE_T_R_DEADLOCK_DETECTED error be forced to
  happen?

By creating an actual deadlock with at least two transactions conflicting. See postgres deadlock without explicit locking on S.O. for a simple example.


Get first part of result faster in sorting query on hundreds of millions of rows?

First, I have to ask what the client will do with 280M rows?  If it is a web page, that much data will crash the user's machine.

Here are two things to speed up getting data from that query:

Change the indexes to these:

PRIMARY KEY(moment, id),  -- to cluster the data in the order to be fetched
INDEX(id)   -- to keep AUTO_INCREMENT happy.

What you have now will fetch all the data, then sort it by moment, before delivering even the first row.  With the indexing change, the sort is gone, and the rest can be 'streamed' directly.  But will the client take advantage of that?  Well...

Change the fetching mechanism to receive the data in chunks instead of "all at once".  (The details of this choice are buried in the API of how the client talks to MySQL.  I only use the "all at once".  But I never ask for 280M rows.)

Drawbacks of "chunking":

The entire query takes longer.  (That is, more than 37 minutes.)
Other connections may be impacted by such a long-running query.

Consider this alternative:  SELECT ... INTO OUTFILE ... and then process the resulting file as you might process a CSV file.


MS Access Query summarizing 2 tables doesn't produce expected output

Create the query which calculates the amount of bad fruits per year and save it assigning the name [Bad Fruit Year]:

SELECT CYear, Fruit, SUM(Bad) AS Bad
FROM [Bad Fruit]
GROUP BY CYear, Fruit;

Then create the query obtaining the data You need:

SELECT p.ID, p.CYear, p.Fruit, p.Purchased, NZ(b.Bad,0) AS Bad, (p.Purchased-NZ(b.Bad,0)) AS Net
FROM [Purchased Fruit] As p
LEFT JOIN [Bad Fruit Year] As b ON p.CYear=b.CYear AND p.Fruit=b.Fruit;

As a result You will have 2 queries (to clear the Queries tab of DB window You can make the first query hidden). The second one gives the result You need, it automatically calls the first one (You do not need to call/open it) while opening.

If You want You may to combine these queries into one:

SELECT p.ID, p.CYear, p.Fruit, p.Purchased, NZ(b.Bad,0) AS Bad, (p.Purchased-NZ(b.Bad,0)) AS Net
FROM [Purchased Fruit] As p
LEFT JOIN ( SELECT CYear, Fruit, SUM(Bad) AS Bad
            FROM [Bad Fruit]
            GROUP BY CYear, Fruit) AS b ON p.CYear=b.CYear AND p.Fruit=b.Fruit;

But its text maybe damaged by MS Access if You open it for view/edit after create and store (the problem is old and still irreparable).

PS. I assume there is NOT a situation when some fruit is present [Bad Fruit] table for some year while absent in [Purchased Fruit] for the same year.


Please run below command in your SQL server database to check the level of corruption.If database is showing suspect it wont allow you to run this command

  DBCC CHECKDB (DB_NAME) WITH ALL_ERRORMSGS,NO_INFOMSGS

This command can be used to check corruption in SQL Server database. Also I must say you are running very old version of SQL server if you are not on SP4 your SQL server is in un supported state. If above command returns any consisteny and allocation error there is corruption in SQL server database if not then issue is with backup tool you need to contact vendor in that case.

Best method to recover from corruption is to restore from latest valid backup. You can use RESTORE VERIFYONLY to check consistency of backup before restoring although only successful restore can guarantee that backup is in complete consistent state.

Your other task is to look at SQL Server errrorlog and windows event viewer to find any information related to corruption and take appropriate action. Major cause is inconsistent disk subsystem.

Restoring from valid backup would involve first restoring full backup with no recovery then differential backup(if any) with no recovery and then all log backup witn no recovery but the last log backup with recovery.I you just have full database backup you can restore it with recovery.

How to restore database using SSMS


SQL is not my strong point, but this was an interesting question with no answers for such a long period of time.

I would use HTML arrays to set the extra detail:

<input type="text" name="model"> //say AA-1501
<input type="text" name="color[]"> //say red
<input type="text" name="color[]"> //say green
<input type="text" name="color[]"> //say black

<input type="text" name="size[]"> //say 15
<input type="text" name="size[]"> //say 16
<input type="text" name="size[]"> //say 17

On the back end in PHP this will come across as:

<?php
$model = 'AA-1501';
$color = array(red,green,black);
$size  = array(15,16,17);

Then use your SQL to insert the correct data into the fields you want.

PHP implode, array_walk will help you a bit :)

I understand this won't help you now, but I'm sure someone might find it useful.


How to implement many-to-many relation between three table?

A teacher can teach the same class several times in the same day and many times over years. A student can re-take a class. But, a teacher can only teach 1 class at a time and a student can only take one class at a time. I would add class instance and class enrollment tables as your junction tables. Your initial attempt isn't that far off from this:

1.studentInfo
  studentId(primary key)
  name
  location

2.subjectInfo
  subjectId(primary key)
  subjectName
  totalmarks

3.teacherInfo
  teacherId(primary key)
  teacherName

4.classInstance
  classId(primary key)
  subjectId(foreign key from subjectInfo)
  teacherId(foreign key from teacherInfo)
  year, semester, time, building, room nr, etc

5.classEnrollment
  studentId(foreign key from studentInfo)
  classId(foreign key from classInstance)
  marks
  attendance


Display Postgres server output in terminal and record to logs at same time

Just use tail -f /path/to/log and you'll be able to see the log contents in a terminal as well. You can use a terminal tool that can "monitor for activity" if you'd like to be notified when something happens.

A more complex solution would be to set PostgreSQL to log to rsyslog, and set rsyslog to log to a tty as well as a file


One call to `decryptbypassphrase` for decrypting all columns

No. Scalar operators cannot magically transform themselves into relational operators. You use the ENCRYPTBYPASSPHRASE to encrypt a column (there is no way to 'encrypt a table') and you use DECRYPTBYPASSPHRASE to decrypt a column.

Using passphrases to encrypt the data directly is a very very poor choice. A proper key hierarchy is needed where data is encrypted with a set of symmetric keys and the symmetric keys are in turn encrypted with a certificate. Encrypting the data directly with a passphrase requires expensive size-of-data operation to rotate the keys.


SQL Server backup to Azure Blob with different backup plan than local backup

As @LowlyDBA pointed out "having a separate job to upload the files to Azure BLOB" is the right approach here.  You simply can't have two separate jobs managing differential or log backups.  And you don't really want to have any BACKUP command that runs for a really long time.

Just have a single local backup plan, and a secondary job that uses AZCopy to push some or all of the resulting backup files into Azure Blob storage.

Here's an old blog post on this topic Copying SQL Server Backups to Windows Azure Storage using AzCopy.  You can also try the new Azure File Sync  service to automatically move files to Azure.


Full text search on multiple tables in PostgreSQL

General idea of Full-Text Search is to provide more functionality for naitive language searches. it allows using stop words (words, that do not contribute to subject of the search), search for synonims, specify distance between words etc.

To accomplish,  what you need a procedure with several unions is enough. Design layout of the output first. Than do a query for each table you need to search in. If you need to show value found, and number af columns varies too much, then have several queries to same table returining different column of interes.

If column data types cannot be converted implicitly, then explicitly convert them to text in SELECT and WHERE.

CREATE FUNCTION find_everything(searchTerm TEXT)
RETURNS RETURNS TABLE(
   ID integer,
   Description text,
   Source_table text) AS
$$

Declare
    searchIn text;
BEGIN
    searchIn = '%' || searchTerm || '%';

    For results in
      SELECT
        Client_Id as ID,
        Name as Description,
        'Clients Table' as Source_Table
      FROM Clients
      WHERE Client_Id ILIKE searchIn OR Name ILIKE searchIn

      UNION ALL

      SELECT
        Invoice_Id as ID,
        Invoice_Description as Description,
        'Invoices Table' as Source_Table
      FROM Invioces
      WHERE  Invoice_Id ILIKE searchIn OR Invoice_Description ILIKE searchIn

      UNION ALL
      ...

      return next results;
    end loop;
END;
$$ language plpgsql;

P.S. Sorry if there are errors in code. It is to show the idea.


Configure SSMS intellisense / web browser settings to accept UNC directory path inputs

I would say you need to use file URIs:

For example:

You can use UNC paths in a file uri if you convert the \ to / like this:


What are the origins of the Identity Column?

Hi Bob and welcome to DBSE.

I assume (correct me if I'm wrong) that your question is not about the specific properties of SQL Server or Oracle IDENTITY, but about the use of IDENTITY as surrogate keys in general, which will be true for other implementations such as AUTO_INCREMENT, SEQUENCE, IDENTITY, GUIDs, OID as a design choice.

If that is what you mean, I salute you for this inquiry.
Most people new to SQL never stop to ask these questions and just assume 'this is how it's done' because everybody seems to be doing it this way.

This is a controversial topic, and you will find people with strong opinions both ways. A quick search for "Natural Vs. Surrogate keys / artificial keys" will yield you enough reading material for years.

Wikipedia has a nice article that will get you started.
Joe Celko also wrote quite a few excellent articles about this topic, you can start with this one, and this one.

I also find this video session on the subject to be entertaining and educational.

I am of the opinion that both are valid solutions.
However, I also see that surrogate keys are used indiscriminately by most, just because finding the correct keys and modeling it properly is a significant effort, that requires mastering the relational model, which is hard.

IMHO, that is unfortunately also the root cause of many data consistency, modularity, performance, coding, and manageability challenges that later end up very hard and very expensive to correct.

Ask anyone who has ever tried to merge two data sets that use surrogate keys.
At that point, you have no choice but to look for the natural key. But, if the natural keys were never explored, or properly constrained with UNIQUE constraints, the inconsistencies and duplicates are typically in abundance.

In my designs, I find that I use natural keys for the majority of the tables, and carefully plan for the edge cases where a surrogate is beneficial.
These design choice are very much a minority opinion.

HTH


How to remove duplicates from any array and preserve ordering in PostgreSQL?

This can indeed be done using pure SQL:

create function array_unique_stable(p_input anyarray)
  returns anyarray immutable strict parallel safe
  language sql
as
$$
select array_agg(t order by x)
from (
  select distinct on (t) t,x
  from unnest(p_input) with ordinality as p(t,x)
  order by t,x
) t2;
$$

The unnest(p_input) with ordinality will return the original index of the element in the array which is then used to aggregate them back in the outer query.

select array_unique_stable(array['a','x','x','b']) as text_array,
       array_unique_stable(array[10,1,1,5,8,8]) as int_array

returns

text_array | int_array
-----------+-----------
{a,x,b}    | {10,1,5,8}

and


Do databases optimize repeated similar query params on the same column?

I'll ignore the "most databases" in this question, otherwise I'd have to vote to close for lack of focus. Instead, I'll answer about PostgreSQL.
To "merge" the lists in your question, you'd have to build the intersection:
WHERE id IN (1, 2, 3, 4) AND id IN (3, 4, 5, 6)

is the same as
WHERE id IN (3, 4)

PostgreSQL doesn't do that automatically. PostgreSQL may choose to scan an existing index for one or both of the conditions, or it may go with a sequential scan, but the two conditions are not merged.
What is more, PostgreSQL will treat the two conditions as statistically independent and just multiply their selectivity, which may result in a bad estimate.
You'll very likely do better with a query that has a single IN list, which is the intersection of the original lists.
You asked about why the PostgreSQL team has not added this optimization into the code for PostgreSQL, so I have a couple of comments about that.  This is a rather unusual requirement (I had misunderstood the question at first), and when adding optimizer code for such requirements there is always a trade-off: while your query would benefit, many queries would have to pay the price in the shape of extra CPU cycles spent to test if the optimization applies or not.  Now query planning time is a rather sensitive area (it has to happen fast), so we are usually reluctant to add special processing for corner cases, particularly if they can be easily avoided by rewriting the query.
Also of note:  You could do the intersection at the SQL level if you are able to pass the lists as arrays and install the intarray extension which allows you to calculate the intersection between two (integer!) arrays. e.g. where id = any(:ids & :allowedIds)


How to retrieve 'y' and 'n' comma separated values as 'yes' or 'no' from a single column

Some databases would support something like this (Oracle, MySQL, PostgreSQL, SQLite).

select T.id,
       T.name,
       case substr(T.facility_type, 1, 1) when 'y' then 'yes' else 'no' end as Ambulance,
       case substr(T.facility_type, 3, 1) when 'y' then 'yes' else 'no' end as ward,
       case substr(T.facility_type, 5, 1) when 'y' then 'yes' else 'no' end as male_doctors,
       case substr(T.facility_type, 7, 1) when 'y' then 'yes' else 'no' end as female_doctors,
       case substr(T.facility_type, 9, 1) when 'y' then 'yes' else 'no' end as operation_theator
from T;

If you are on SQL Server you need to type out substring.

select T.id,
       T.name,
       case substring(T.facility_type, 1, 1) when 'y' then 'yes' else 'no' end as Ambulance,
       case substring(T.facility_type, 3, 1) when 'y' then 'yes' else 'no' end as ward,
       case substring(T.facility_type, 5, 1) when 'y' then 'yes' else 'no' end as male_doctors,
       case substring(T.facility_type, 7, 1) when 'y' then 'yes' else 'no' end as female_doctors,
       case substring(T.facility_type, 9, 1) when 'y' then 'yes' else 'no' end as operation_theator
from T;


How to normalize a table with common and non-common fields?

Your case looks like an instance of class/subclass (or, if you prefer, type/subtype).  You can find many answers like this one in SO or here in DBA.


In mongodb cluster, Can I shut down one config server and one data server in the shard at the same time

The normal rules of replica set elections apply.

If your shard is backed by a 3-node replica set, then you can have any single node down while still being able to maintain a primary. A strict majority of nodes must be available, so 2/3 in the 3-node example.

If the data-bearing server you are planning to restart happens to be a replica set primary, I would shut it down gracefully with db.shutdownServer() to minimise down time (and make sure there is an eligible secondary whose oplog data is within 10 seconds of the primary). db.shutdownServer() runs the shutdown command against the admin database.


Checking whether two tables have identical content in PostgreSQL

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data):

(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;

Or with EXISTS to return just a boolean value or a string with one of the 2 possible results:

SELECT CASE WHEN EXISTS (TABLE a EXCEPT TABLE b)
              OR EXISTS (TABLE b EXCEPT TABLE a)
            THEN 'different'
            ELSE 'same'
       END AS result ;

Tested  at SQLfiddle

Also not the that EXCEPT removes duplicates (that should not be a worry if your tables have some PRIMARY KEY or UNIQUE constraint but it may be if you are comparing results of arbitrary queries that can potentially produce duplicate rows).

Another thing that EXCEPT keyword does is that it treats NULL values as identical, so if table A has a row with (1,2,NULL) and table B has a row with (1,2,NULL), the first query will not show these rows and the second query will return 'same' if the two tables have no other row.

If you want to count such rows as different, you could use a variation on gsiems' FULL JOIN answer, to get all the (different) rows:

SELECT *
FROM a NATURAL FULL JOIN b
WHERE a.some_not_null_column IS NULL
   OR b.some_not_null_column IS NULL ;

and to get a yes/no answer:

SELECT CASE WHEN EXISTS
            ( SELECT *
              FROM a NATURAL FULL JOIN b
              WHERE a.some_not_null_column IS NULL
                 OR b.some_not_null_column IS NULL
            )
            THEN 'different'
            ELSE 'same'
       END AS result ;

If all the columns of the two tables are not nullable, the two approaches will give identical answers.


Create global temp table on one DB and use it in a query on another DB - is it possible?

Even a local temporary table (#Table) would work as long as you stayed in a session context. A global temporary table (##Table) is only global in that it can be seen by other sessions.

What is probably happening here is your create ##Table session is going out of scope/context and when the session goes away, there goes it's temp table. Whether local or global. So the process that created the temporary table is out of scope and it's temp table is gone.

In other words - the problem isn't the scope of the database you are connected to. any temporary table can be access regardless of the database you are using. A temp table isn't created in any of your user databases (DB1 or DB2), they are always created in TempDB.

Try using a derived table or consider a staging table, etc.


Insert missing records from another table and update timestamp?

Well - I dreamed up the following - now it's up to yo to try and 'break' it with your real data :)

set nocount on
declare @Stores table (Store varchar(100),Address varchar(100), Product varchar(100));
insert into @Stores values ('Walmart','123 Fake St','702640');
insert into @Stores values ('Walmart','123 Fake St','704651');
insert into @Stores values ('Walmart','123 Fake St','709999');
insert into @Stores values ('Target','456 Main St','789562');
insert into @Stores values ('Target','456 Main St','777898');
insert into @Stores values ('Target','456 Main St','723456');

declare @Scanned table (Timestamp Date,Store varchar(100), Address varchar(100), Product varchar(100));
insert into @Scanned values ('2016-10-06','Walmart','123 Fake St','702640');
insert into @Scanned values ('2016-10-06','Walmart','123 Fake St','704651');
insert into @Scanned values ('2016-10-06','Target','456 Main St','789562');

WITH StoreVisits
AS (
    SELECT DISTINCT TIMESTAMP
        ,Store
    FROM @Scanned
    )
SELECT a.TIMESTAMP
    ,b.Store
    ,b.Address
    ,b.product
    ,CASE
        WHEN c.Product IS NULL
            THEN 'not-scanned'
        ELSE 'scanned'
        END
FROM Storevisits a
JOIN @Stores b ON b.store = a.store
LEFT JOIN @Scanned c ON c.Store = b.store
    AND c.Address = b.Address
    AND c.Product = b.Product
    AND C.TIMESTAMP = A.TIMESTAMP
WHERE c.product IS NULL

It produces the following result set


Deleting old data in MySQL becomes slower over time

I would look to the InnoDB redo log file size.

The symptoms you describe is typical if you fill up the redo log with changes, which forces a "synchronous flush" — MySQL blocks further changes until it can free up a portion of the redo log by flushing dirty pages from the buffer pool.

RDS used to use an absurdly small redo log file size by default, 128M if I recall. For years they did not allow changing the size. But in the last couple of years they do allow changing it.

Here's how to check the size of your redo log file in megabytes:

mysql> SELECT @@innodb_log_file_size / 1024 / 1024;

To change it, I think you'd use the RDS parameter groups UI, then restart your RDS instance to apply the change.

To monitor this, I'd watch the number of bytes written to the redo log:

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

Measure that every 10 minutes or so, and plot it. The redo log files are of fixed size, and writes will eventually reach the end and wrap around to the beginning of the file. They must not overwrite changes in the log that represent dirty pages in the buffer pool, so before they get close to doing that, MySQL forces a synchronous flush.

So you can watch the rate of Innodb_os_log_written, by reading that variable periodically at even intervals. Compare this rate of log writes to the log file size (remember that you have two redo log files by default, so your redo log capacity is Innodb_log_file_size * 2).

This allows you to estimate "we overwrite the whole redo log file(s) every N minutes." This should correlate (roughly) to your 10-15 minutes time period when deletes are fast.

I think I recall there are some nuances to this calculation ... the Innodb_os_log_written might include some overwrites, i.e. some writes seek backwards to re-write a block under some circumstances. So there might be some cases where the numbers don't add up. I don't know deep details here.

In any case, InnoDB has long been known to be better able to handle heavy write workload if you increase the size of redo logs. It's tempting to increase it as large as you are allowed, but this may be overkill for most of your day-to-day workload with more modest write traffic.

See also:

https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/
https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/


Based on Lennarts suggestion I have used a UNION to get the results I need:

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID
INNER JOIN itemTypes it ON it.itID = ugit.itID
INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID
WHERE c.total > 0
GROUP BY uID, itID
UNION
SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID
WHERE c.total = 0
GROUP BY uID, itID

dbfiddle

Basically running the same queries twice, one requiring a userGroup to have itemTypes, the other requiring the userGroup NOT to have itemTypes, then using the union to combine the results.

I'm not sure if this is the most efficient way of doing it but it will have to do unless I can find another way of doing it.

Alternative Solution (thanks to Lennart):

Using EXISTS and NOT EXISTS

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID
INNER JOIN itemTypes it ON it.itID = ugit.itID
WHERE EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID
UNION
SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
WHERE NOT EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID

dbfiddle


SQLAnywhere: How to configure length of message output to console/client when using isql

I found a solution at least for my basic issue, which is almost too simple:

    -- ... some code
DECLARE @generatedProcedureCall LONG VARCHAR
SET @generatedProcedureCall = ... contents
SELECT @generatedProcedureCall

This is allowing me to print out >4k characters for e.g. debugging.

When writing to file e.g.

SELECT @generatedProcedureCall INTO #logfoo
UNLOAD #logfoo to 'some/file'

could do the trick.

Maybe not the cleanest one, but as mentioned it solved at least my issue.

The reasons seems to be, that print as well as message cannot handle string >4k of text. print was just failing with an error whereas message catted my string to 255 characters.


The issue is relevant to this whole part and not just  "user_firstname".

SELECT
          "user_firstname"
        , "user_lastname"
        , "course_fullname"
        , "course_completion_completeddate"
        , "course_completion_status"
        , "ESR Employee Number"
        , "DH Job Role"
        , "Department"
        from mdl_course_completions

In MySQL "user_firstname" is interpreted as a string (which probably wasn't you intention)
In PostgreSQL "user_firstname" is interpreted as a column name.

Demo

create table t (x int);
insert into t (x) values (1),(2),(3);

select "x" as col from t;

PostgreSQL

+---+
|col|
+---+
| 1 |
+---+
| 2 |
+---+
| 3 |
+---+

MySQL

+---+
|col|
+---+
| x |
+---+
| x |
+---+
| x |
+---+


Oracle outer join by (+) to Postgres conversion

All those derived tables are useless (even in Oracle).
As far as I can tell, this should be equivalent:
SELECT r1.fibre AS root_edge_sid,
       sp.sid   AS node_sid,
       sp.fid   AS node_fid,
       sp.ftid  AS node_ftid,
       f.sid    AS edge_sid,
       f.fid    AS edge_fid,
       f.ftid   AS edge_ftid
FROM table_a r1
  JOIN table_b sp ON r1.splice_point = sp.sid
  LEFT JOIN table_a r2 ON sp.sid = r2.splice_point
  LEFT JOIN table_c f ON r2.fibre = f.sid
WHERE r1.fibre = 123456
AND r1.fibre <> f.sid;

I think the condition r1.fibre <> f.sid in the WHERE clause was wrong as it turned the outer join back into an inner join.


If you create a FUNCTION and query the sysjobhistory table in MSDB you can get a simple response on the most recent job execution.

Create the function:

CREATE FUNCTION [dbo].[fnGetLatestAgentJobExitCode]
(
    @JobName VARCHAR(100)
)
RETURNS VARCHAR(25)
AS
BEGIN
    RETURN(
        SELECT TOP (1)
            CASE jh.run_status
                WHEN 0 THEN 'Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Canceled'
                WHEN 4 THEN 'In Progress'
            END AS [Status]
        FROM [msdb].[dbo].[sysjobactivity] ja
        INNER JOIN [msdb].[dbo].[sysjobs] j ON j.job_id = ja.job_id
        INNER JOIN [msdb].[dbo].[sysjobhistory] jh ON jh.job_id = j.job_id AND jh.instance_id = ja.job_history_id
        WHERE j.[name] = @JobName
        ORDER BY ja.start_execution_date DESC)
END

Then query it like so:

SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob')

This will give one of the following results:

Failed
Succeeded
Retry
Cancelled
In Progress

Or will return no result if there is no job history. To use this in your specific use case would be simple:

WHILE (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'In Progress')
BEGIN
    WAITFOR DELAY '00:00:05.000'

    IF (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'Failed')
    BEGIN
        BREAK
    END
END

IF (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'Succeeded')
BEGIN
    EXEC msdb.dbo.sp_start_job 'myJob'
END


How to upgrade MySQL to newer version on existing database

Well, first off, if you are upgrading from 5.0 to 5.5, you really need to do this on a test server prior to making the upgrade in a production environment. This will get you comfortable with the steps of upgrading and let you figure out any trouble areas beforehand, which will dramatically reduce your downtime of upgrading.

So to upgrade, I would upgrade from 5.0 to 5.1, then 5.1 to 5.5 as the manual suggests. There will be downtime, but if you do the upgrade steps on a test setup first, you will reduce your downtime dramatically. Make sure you have all the installers downloaded beforehand, don't wait to download them while you're in downtime.

You could do the replication setup as Rolando points out, but please don't do the upgrade on production without testing out the steps first.


Depends on the data distribution. I can easily construct examples for both cases (index vs table scan).

Just think about the case when the columns are unique or nearly unique (index), or when all the rows are the same (table scan).

Technically your index can be used for the above query.

Given the general nature of a column named ID, I would say the index will be chosen, but it is not guaraanteed.

The predicate DATE_TIME is not null will be processed at the table level, not the index level, even if you make sure that the NULL rows are indexed as well:

create index index_1 on table_1(id, name, trunc(date_time), 1);

PLAN_TABLE_OUTPUT
-------------------------------------------------------
Plan hash value: 852675278

-------------------------------------------------------
| Id  | Operation                           | Name    |
-------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_1 |
|*  2 |   INDEX RANGE SCAN                  | INDEX_1 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DATE_TIME" IS NOT NULL)
   2 - access("ID"=1 AND "NAME"='Oracle')


Finding entries which have no foreign key mapping in any other table

This can be accomplished with a NOT EXISTS () clause in your WHERE clause.

SELECT id
FROM A
WHERE NOT EXISTS (
SELECT id
FROM B
WHERE A.ID = B.A_id
)
AND NOT EXISTS (
SELECT id
FROM C
WHERE A.ID = C.A_id)
...
...

You could generate this query programmatically by querying the schema for tables that have a foreign key on A.id, and then iterating over them to create the WHERE clause...


very poor cardinality estimate on a geometry intersection

Sounds like you want to get rowgoals to play their part on the query - so try using TOP(1), maybe with testing to avoid NULLs (in case of non-matching SRIDs). That way you can get the "nearest neighbour" functionality to kick in. I know you're using Contains, but you want to use a method that tells the QO that you're only going to get a single row back.

http://blogs.lobsterpot.com.au/2014/08/14/sql-spatial-getting-nearest-calculations-working-properly/ might have a few tips...


Partition elimination and date variables

Got an answer from Pivotal - I should have realised this, but parameterised queries are planned in ignorance of the parameter value, and partition elimination is part of the query plan, so we can't use variables for partition elimination.

I am re-tooling our partition elimination to use views that are dynamically constructed with an IN list of dates. This specific query is now just looking at the overflow partition as that's a lot simpler.


Since OpenDate is a datetime column, you don't need to use DATE_FORMAT(), you can simply GROUP BY DATE(OpenDate). And the extra CRC32() call seem completely redundant.

The query can be rewritten:

SELECT COUNT(*) AS TotalOpens,
       DATE(OpenDate) AS DayOfWeek
FROM stats_open
WHERE ListID = 38
  AND UserID = 4
GROUP BY DATE(OpenDate)
ORDER BY TotalOpens DESC ;

Add an index on (ListID, UserID, OpenDate), to make it more efficient.


Connecting to MySQL VM remotely (Azure Resource Manager)

After a bit of head banging I have found that changing the bind-address on mysql to the ip address of the node connecting to mysql makes mysql refuse to restart

So instead set the bind-address = 0.0.0.0

Then let the firewall and mysql handle allowing the hsot.

Create a firewall rule on the mysql node like below:

Then create a remote mysql user limiting the host to the internal/private ip address above.


With the help of @mustaccio achieved do as follows:

First I added the user db2inst1:

  db2inst1 useradd

Then, I generated the instance (whatever it means)

  /opt/ibm/db2/V10.5/instance/db2icrt db2inst1

Edit the php configuration file (/etc/php.ini) for add the instance:

 extension = / usr/lib64/php/modules/ibm_db2.so
 ibm_db2.instance_name = db2inst1

Edit the initialization file of the php-fpm service (/etc/init.d/php-fpm), which in my case is the one who runs php, to do the source of the db2profile script under the user running this service each time it starts.

 #for DB2
 . /opt/ibm/db2/V10.5/cfg/db2profile

And finally restart the service:

  php-fpm restart service

Thank you very much for the comments.


Using a recursive CTE, this query builds a table of all languages that point somewhere in their default chain to a language that has the required comment.  It shows each language next to its first default that has the required comment.  It then filters this table to give you the first available text for the language you selected.

DECLARE @CommentId BIGINT = 1;
DECLARE @LanguageCode NVARCHAR(2) = 'en';

WITH languages AS (
    -- base case: language has required comment
    SELECT
          lt.LanguageId AS RootLanguageId
        , lt.LanguageId
        , lt.Code
        , lt.DefaultId
        , 0 AS Level
    FROM
        dbo.common_LanguageType lt
    --WHERE
    --  lt.LanguageId = lt.DefaultId
    WHERE
        EXISTS (
            SELECT *
            FROM dbo.common_Text    t
            WHERE
                    t.CommentId = @CommentId
                AND t.LanguageId = lt.LanguageId
        )

    UNION ALL

    -- recursive case: language is not its own default and
    --                 does not have the required comment
    SELECT
          l_default.RootLanguageId
        , l.LanguageId
        , l.Code
        , l.DefaultId
        , l_default.Level + 1 AS Level
    FROM
                    dbo.common_LanguageType l
        INNER JOIN  languages               l_default
            ON l.DefaultId = l_default.LanguageId
    WHERE
            l.LanguageId <> l.DefaultId
        AND NOT EXISTS (
            SELECT *
            FROM dbo.common_Text    t
            WHERE
                    t.CommentId = @CommentId
                AND t.LanguageId = l.LanguageId
        )
)
SELECT t.Text
FROM
                languages       l
    INNER JOIN  dbo.common_Text t
        ON  l.RootLanguageId = t.LanguageId
WHERE
    l.Code = @LanguageCode
;

Try just running

SELECT *
FROM languages;

if you want to get a feel for what the recursive CTE is doing.  I've uploaded a script to create the tables, insert some sample data, and run the above code to gist.

Pretty sexy, no?


You seem to run the query on the view. Should be substantially faster based on underlying tables. Like:

SELECT DISTINCT parent_id
FROM   children_parent_tab
WHERE  children_id = ANY ('{1,2,3,4,5}'::int[]);

Details depend on undisclosed exact table definitions and requirements.

Be that as it may, you can fix the query you presented with the "overlap" operator && instead of the "contains" operator @>:

SELECT * FROM parent p WHERE ARRAY[1,2,3,4,5]::int[] && children_ids;

This way you get all parents of any of the children in the given array.


Can I Show Multiple "WHERE" Filtered Results In A Single Query/Output Table?

I think you are looking for conditional aggregation:

SELECT b.WorkflowID,
       count(case when a.creationtime <= timestamp '2015-06-10 14:00:00' then b.worflowid end) as count_before,
       sum(  case when a.creationtime <= timestamp '2015-06-10 14:00:00' then (LENGTH(b.data)/1024.)::numeric(10,2) end) as size_before
       count(case when a.creationtime > timestamp '2015-06-10 14:00:00' then b.worflowid end) as count_after,
       sum(  case when a.creationtime > timestamp '2015-06-10 14:00:00' then (LENGTH(b.data)/1024.)::numeric(10,2) end) as size_after
FROM Table1 a
JOIN Table2 b
  ON (a.WorkflowID = b.WorkflowID)
GROUP BY b.WorkflowID;


How to restore several databases with binlogs?

You can use the tool mysqlbinlog  to parse your binlogs, then you can use mysql client to run the parsed binlogs directly.

Maybe you should add some parameter to the tool mysqlbinlog just like '--start-datetime','--start-position'.It will filter out the binlogs which you do not need.


In a simple table with no complex indexes, does a large number of existing rows slow down an INSERT?

When you are mass INSERTs into an InnoDB table with just a Primary Key you are building up the gen_clust_index (a.k.a. Clustered Index).

Secondary Indexes are built in conjunction with the Clustered Index. Since you have no Secondary Indexes, this reload would be just fine.

I would high recommend doing two things when reloading:

Organize your INSERTs as extended INSERTs
Set bulk_insert_buffer_size to 512M or 1G

When you use the bulk insert buffer, it helps load the data into a treelike structure that makes it easier to load into a non-empty table.


While inserting to table date going as 000000 in mysql

From the MySQL manual, DATE uses the YYYY-MM-DD format
https://dev.mysql.com/doc/refman/5.7/en/datetime.html

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

You can select dates out of your database and convert them to whatever format you want to display to your uses, but to store them you'll need to use the YYYY-MM-DD format that MySQL is expecting.


Creating/restoring mdf/ldf to non-default file location giving access denied

I guess the first thing I would do would be to make sure that the permission are set on the correct account.
Run : select servicename, service_account from sys.dm_server_services
to get the account on which the permission should be set.


How do I ensure I always connect to Primary/Secondary node in a MongoDB ReplicaSet?

Ref: https://docs.mongodb.com/manual/reference/connection-string/

If you are using replica set you do not use your mongod hostname in your connecton string. Rather use all mongos hostname and replica set name.

As an example:

  To describe a connection to a replica set named test, with the
  following mongod hosts:

  db1.example.net on port 27017 and db2.example.net on port 2500. You
  would use a connection string that resembles the following:

  mongodb://db1.example.net,db2.example.net:2500/?replicaSet=test

Writes will always go to primary and you do not have to do anything. Config and mongos will communicate among themselves will redirect to primary.

You can configure Write concern which describes the kind of assurances that the mongod and the driver provide to the application regarding the success and durability of the write operation. For a full explanation of write concern and write operations in general, see Write Concern.

Read Preference Options

Read preferences describe the behavior of read operations with regards to replica sets. These parameters allow you to specify read preferences on a per-connection basis in the connection string. The default value is primary, which sends all read operations to the replica set’s primary.

The read preference values are the following:

primary
primaryPreferred
secondary
secondaryPreferred
nearest

For descriptions of each value, see Read Preference Modes.


Not able to make a query that give me the result that I want in MySQL with 4 tables junction

What you really want to be able to do is join the three tables with both a SKU and a location using a FULL JOIN; this joins rows where the indicated columns match, and also includes rows from both tables where the indicated fields do not match.

Unfortunately, MySQL 5.6 doesn't implement FULL JOIN. The standard workaround is to do something like this:

SELECT *
  FROM table1 t1
         LEFT  JOIN table 2 t2 ON t1.id = t2.id
UNION ALL
SELECT *
  FROM table1 t1
         RIGHT JOIN table 2 t2 ON t1.id = t2.id
 WHERE t1.id IS NULL
;

This gives you the results of the LEFT JOIN of the tables, and then unions in the results of the RIGHT JOIN of the tables, excluding the rows that would have been in the LEFT JOIN results.

The exclusion deals with the fact that both the LEFT JOIN and the RIGHT JOIN will return whatever rows an INNER JOIN would return. We want those rows - but we don't want to include each of them twice!

So, I built this query in three steps:

First, I built the query to return the rows from ventes and produitssite.
Then, I used that query as a subquery (identified as s1, for "step 1"), and joined its results to stocksminmax.
Finally, I used the second query as a subquery (identified as s2, for "step 2"), and LEFT JOINed those results to produits. For this final step, since it looks like you don't want to list and products that don't have a match in at least one of the three other tables, we don't have to do both a LEFT JOIN and a RIGHT JOIN.

Here's the query I got:

SELECT `sku`,`description`
      ,`smmsku`,`smmloc`,`smmphy`
      ,`pssku`,`psloc`,`psabcd`
      ,`ventesku`,`ventelocalisation`,`vente1805`
  FROM (
        SELECT `theSku`
              ,`theLoc`
              ,`smmsku`,`smmloc`,`smmphy`
              ,`pssku`,`psloc`,`psabcd`
              ,`ventesku`,`ventelocalisation`,`vente1805`
          FROM (SELECT `pssku` as theSku
                      ,`psloc` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         LEFT  JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                UNION ALL
                SELECT `ventesku` as theSku
                      ,`ventelocalisation` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         RIGHT JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                 WHERE ps.pssku IS NULL
               ) s1
                 LEFT  JOIN stocksminmax smm ON s1.theSku = smm.smmsku
                                            AND s1.theLoc = smm.smmloc
        UNION ALL
        SELECT `smmsku` as theSku
              ,`smmloc` as theLoc
              ,`smmsku`,`smmloc`,`smmphy`
              ,`pssku`,`psloc`,`psabcd`
                ,`ventesku`,`ventelocalisation`,`vente1805`
          FROM (SELECT `pssku` as theSku
                      ,`psloc` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         LEFT  JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                UNION ALL
                SELECT `ventesku` as theSku
                      ,`ventelocalisation` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         RIGHT JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                 WHERE ps.pssku IS NULL
               ) s1
                 RIGHT JOIN stocksminmax smm ON s1.theSku = smm.smmsku
                                            AND s1.theLoc = smm.smmloc
         WHERE s1.theSku IS NULL
       ) s2
         LEFT  JOIN produits p ON s2.theSku = p.sku
;

I believe the results match what you expected - you can see them (and the query, working) in this db-fiddle.


MySQL:- Fetch records between two specific months and years

Try this :

    SELECT material_status, COUNT(*) c
    FROM purchase_order
    WHERE date_created
         between str_to_date(CONCAT(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d')
            AND DATE_ADD(str_to_date(concat(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d'),INTERVAL 1 YEAR)
     AND material_status='Closed'
     GROUP BY 1;

But: BETWEEN is inclusive.  If date_created is really a DATE, then this includes 4/1/2017, but not the rest of 4/2017. Use last_day() function on to date, i.e.

last_day( str_to_date(CONCAT(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d'))


To completely reset everything would involve uninstalling/reinstalling SQL Server.  But this is overkill.  Instead it sounds like you have two things you need to do.

Thing 1

First thing you should do is clean up your endpoints.  I would do this on both your primary and mirror server.  The DROP ENDPOINT command is straightforward.  If you need to see what endpoints are currently out there, you can select from sys.endpoints.

Thing 2

Drop your old database.  If it's stuck in recovery, the first thing to check is your SQL Error Log on the mirroring target and make sure it isn't actually doing anything.  You want to look for messages containing the word "Recovery" and "Step n of 3".  If these are there, it's still in the process of restoring.  Otherwise, you just need to complete the recovery with:

RESTORE DATABASE [foo] WITH RECOVERY;

Once you have completed recovery and the database is online, you should be able to drop it.


You can use SQL Server Change Data Capture  or Change Tracking features to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

To get the changed records for a table and information about these changes, use the CHANGETABLE(CHANGES…) function.

Drawback: is that you have to query the tables periodically and then send the notification email

Create triggers on a table that needs auditing. Triggers will be fired when a record is inserted, deleted or updated and msdb.dbo.sp_send_dbmail should be used to send an email alert to a specified email recipient, immediately:

e.g.

CREATE TRIGGER t_Pers ON Person.Person
AFTER INSERT, UPDATE, DELETE
AS EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profie name',
@recipients = 'abc.xy.com' ,
@body = 'Data in AdventureWorks2012 is changed',
@subject = 'Your records have been changed'
GO

As triggers are fired per batch, not per row, it should be ensured that all batch transactions are captured, and no data is lost.

Therefore as mentioned above both the above solutions are very much helpful, but depends upon how you want to use them and get benefited.

In addition, please read Tracking Data Changes from MSDN and Performance Tuning SQL Server Change Tracking

The method which i have been using for such scenarios is analyzing the changes from default trace file which tracks object level changes using the below query as explained in this great article:The default trace in SQL Server - the power of performance and security auditing

    SELECT  TE.name ,
    v.subclass_name ,
    DB_NAME(t.DatabaseId) AS DBName ,
    T.NTDomainName ,
    t.NTUserName ,
    t.HostName ,
    t.ApplicationName ,
    t.LoginName ,
    t.Duration ,
    t.StartTime ,
    t.ObjectName ,
    CASE t.ObjectType
      WHEN 8259 THEN 'Check Constraint'
      WHEN 8260 THEN 'Default (constraint or standalone)'
      WHEN 8262 THEN 'Foreign-key Constraint'
      WHEN 8272 THEN 'Stored Procedure'
      WHEN 8274 THEN 'Rule'
      WHEN 8275 THEN 'System Table'
      WHEN 8276 THEN 'Trigger on Server'
      WHEN 8277 THEN '(User-defined) Table'
      WHEN 8278 THEN 'View'
      WHEN 8280 THEN 'Extended Stored Procedure'
      WHEN 16724 THEN 'CLR Trigger'
      WHEN 16964 THEN 'Database'
      WHEN 16975 THEN 'Object'
      WHEN 17222 THEN 'FullText Catalog'
      WHEN 17232 THEN 'CLR Stored Procedure'
      WHEN 17235 THEN 'Schema'
      WHEN 17475 THEN 'Credential'
      WHEN 17491 THEN 'DDL Event'
      WHEN 17741 THEN 'Management Event'
      WHEN 17747 THEN 'Security Event'
      WHEN 17749 THEN 'User Event'
      WHEN 17985 THEN 'CLR Aggregate Function'
      WHEN 17993 THEN 'Inline Table-valued SQL Function'
      WHEN 18000 THEN 'Partition Function'
      WHEN 18002 THEN 'Replication Filter Procedure'
      WHEN 18004 THEN 'Table-valued SQL Function'
      WHEN 18259 THEN 'Server Role'
      WHEN 18263 THEN 'Microsoft Windows Group'
      WHEN 19265 THEN 'Asymmetric Key'
      WHEN 19277 THEN 'Master Key'
      WHEN 19280 THEN 'Primary Key'
      WHEN 19283 THEN 'ObfusKey'
      WHEN 19521 THEN 'Asymmetric Key Login'
      WHEN 19523 THEN 'Certificate Login'
      WHEN 19538 THEN 'Role'
      WHEN 19539 THEN 'SQL Login'
      WHEN 19543 THEN 'Windows Login'
      WHEN 20034 THEN 'Remote Service Binding'
      WHEN 20036 THEN 'Event Notification on Database'
      WHEN 20037 THEN 'Event Notification'
      WHEN 20038 THEN 'Scalar SQL Function'
      WHEN 20047 THEN 'Event Notification on Object'
      WHEN 20051 THEN 'Synonym'
      WHEN 20549 THEN 'End Point'
      WHEN 20801 THEN 'Adhoc Queries which may be cached'
      WHEN 20816 THEN 'Prepared Queries which may be cached'
      WHEN 20819 THEN 'Service Broker Service Queue'
      WHEN 20821 THEN 'Unique Constraint'
      WHEN 21057 THEN 'Application Role'
      WHEN 21059 THEN 'Certificate'
      WHEN 21075 THEN 'Server'
      WHEN 21076 THEN 'Transact-SQL Trigger'
      WHEN 21313 THEN 'Assembly'
      WHEN 21318 THEN 'CLR Scalar Function'
      WHEN 21321 THEN 'Inline scalar SQL Function'
      WHEN 21328 THEN 'Partition Scheme'
      WHEN 21333 THEN 'User'
      WHEN 21571 THEN 'Service Broker Service Contract'
      WHEN 21572 THEN 'Trigger on Database'
      WHEN 21574 THEN 'CLR Table-valued Function'
      WHEN 21577
      THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
      WHEN 21581 THEN 'Service Broker Message Type'
      WHEN 21586 THEN 'Service Broker Route'
      WHEN 21587 THEN 'Statistics'
      WHEN 21825 THEN 'User'
      WHEN 21827 THEN 'User'
      WHEN 21831 THEN 'User'
      WHEN 21843 THEN 'User'
      WHEN 21847 THEN 'User'
      WHEN 22099 THEN 'Service Broker Service'
      WHEN 22601 THEN 'Index'
      WHEN 22604 THEN 'Certificate Login'
      WHEN 22611 THEN 'XMLSchema'
      WHEN 22868 THEN 'Type'
      ELSE 'Hmmm???'
    END AS ObjectType
 FROM    [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                        value
                                                FROM    [fn_trace_getinfo](NULL)
                                                WHERE   [property] = 2
                                              )), DEFAULT) T
    JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
    JOIN sys.trace_subclass_values v ON v.trace_event_id =   TE.trace_event_id
                                        AND v.subclass_value = t.EventSubClass
      WHERE   TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )
            -- filter statistics created by SQL server
    AND t.ObjectType NOT IN ( 21587 )
            -- filter tempdb objects
    AND DatabaseID <> 2
            -- get only events in the past 24 hours
    AND StartTime > DATEADD(HH, -24, GETDATE())
   ORDERBY t.StartTime DESC ;


What is this query, involving system views & index properties

Now that you know where this query is coming from, the next step to take will be based on if you think this process is critical or not.

If I were in  your shoes, I would setup a Login Trigger that won't allow this connection in and see who complains or what tool starts erring out.  Just a word of warning, a poorly defined Logon trigger can make your life maddening, but hopefully this example will do the trick.  I modified an example of a different restrictive trigger based from the one found in this post:

CREATE TRIGGER application_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF HOST_NAME() IN (N'ServerNameOfAnnoyingQuery', N'PCNameOfAnnoyingQuery')
AND ORIGINAL_LOGIN() NOT IN ('sa', 'DOMAIN\username')
          BEGIN
            RAISERROR('Connection Denied.  Please contact the Database Administrator Group at dba@companyname.com to resolve this issue.', 16, 1)
            ROLLBACK;
          ENDEND;

If you suspect the query does support a critical process, I would start reaching out to users who have profiles on the originating server in question and start asking them about processes they have used from there.

Finally, if you can't identify any useful information from users and you don't want to go so far as to create the logon trigger, I would then track it down by  process-of-elimination.  Start this by logging into the Server where this query originates from and slowly stop any running services (i.e. services.msc) that look suspect as well as review/disable any suspect Windows Scheduler jobs.  Eventually I would hope you turn off the proper process and eventually figure out what's going on.

Hopefully this gives you some ideas on routes to take.  Good luck and hopefully you figure it out.


ALTER TABLE yourTable ADD  CONSTRAINT constraintName
   DEFAULT ('XYZ') FOR [YourColumn]

To change the default, drop the constraint and re-add it with the new value:

ALTER TABLE yourTable
DROP CONSTRAINT constraintName

ALTER TABLE yourTable ADD  CONSTRAINT constraintName
   DEFAULT ('ABC') FOR [YourColumn]


How to install SQL Server 2017 Express in "Quiet Simple" mode without an extraction folder?

Why not create a PowerShell script to start the install. After the install is finished:

verify success (possibly with the return code or parsing the output log)
verify the existence of the extraction folder
if there are no errors delete the extraction folder


SELECT FOR UPDATE locking other tables in PostgreSQL

We also don't ever update primary keys.

In that case, I think you can use FOR NO KEY UPDATE instead of FOR UPDATE. It's a weaker lock, as explained in Postgres docs about Explicit Locking:

FOR NO KEY UPDATE
Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

Test:
-- Setup
create table a (id int primary key,
                x varchar);
create table b (id int primary key
                    references a (id) on update cascade on delete cascade,
                x varchar);
insert into a values (1), (2);
insert into b values (1, null), (2, null);

-- Left
begin;
select 1 from a for no key update;
/* Keep the transaction open */

            -- Right
            begin;
            update b set x = 'abc' where id = 1;
            update b set x = 'xyz';    -- doesn't block

-- Left
update a set x = 'left' where id = 1;
commit ;

            -- Right
            commit ;


If I understand your question correctly, how about something like this:

DROP TABLE IF EXISTS dbo.Accounts
go
CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)

INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
GO

;WITH _dups
AS (
    SELECT IDNum
        ,count(*) cnt
    FROM accounts
    GROUP BY IDNum
    HAVING count(*) > 1
    )
--now join _cte to the real table to delete the one's with zeros in First and Last name
DELETE a
FROM accounts a
JOIN _dups d
    ON d.idnum = a.idnum
        AND a.firstname = '0'
        AND a.lastname = '0'

SELECT *
FROM accounts

OR

DROP TABLE IF EXISTS dbo.Accounts
go
CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)

INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
GO

DELETE a
FROM accounts a
WHERE FirstName = '0'
    AND LastName = '0'
    AND EXISTS (
        SELECT *
        FROM accounts
        WHERE IDNum = a.IDNum
            AND FirstName <> '0'
            AND LastName <> '0'
        )

SELECT *
FROM accounts

Both of these solutions produce the results you're looking for.

| IDNum | FirstName | LastName  |
|-------|-----------|-----------|
| 123   | Joe       | McDaniels |
| 124   | Mary      | Cain      |
| 125   | John      | Johnson   |
| 126   | Sue       | Bird      |
| 127   | Michael   | Bennett   |
| 128   | Larry     | Jackson   |
| 129   | 0         | 0         |
| 130   | Tanya     | Hall      |
| 131   | 0         | 0         |
| 132   | Joe       | Johnson   |
| 133   | Stephen   | Cole      |

UPDATE 8/2/18 - Excluding records instead of deleting them.

Based on your comment

What about excluding those records from the resultset? So don't delete them just return the others., see if this gives you what you want.

;with ExcludeTheseRecords as
(
select a.*
FROM accounts a
WHERE FirstName = '0'
    AND LastName = '0'
    AND EXISTS (
        SELECT *
        FROM accounts
        WHERE IDNum = a.IDNum
            AND FirstName <> '0'
            AND LastName <> '0'
        )
)

SELECT * from accounts
except
select * from ExcludeTheseRecords


Implementing an encrypted table in SQL Azure

Short answer: as of April 20, 2012 column encryption is not supported in SQL Azure, see: http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx and search for ENCRYPT.  You will see that all of the encryption functions are listed as unsupported.

If you need to encrypt your data at rest and have to support SQL Azure then you are going to have to do your encryption at the application level.  This is more difficult to get right as lots of the good encryption practices (using nonces as initialization vectors for each cell, key management for tracking which keys encrypt which columns, integrity verification, layered protection of encryption keys with role based permissions) you will have to implement yourself.  I strongly recommend getting a security professional to work with you on the encryption as it is challenging to get right and if you miss something then you have a false sense of security that could really burn you later.

You could look at http://securentity.codeplex.com, which claims to be a solution for managing encrypted data and supports SQL Azure.  I have not personally used it, nor can I vouch for it's correctness, but it is a potential option.

Other information:

In SQL Server column level encryption is easy and very secure (even with a view) because you can use roles to manage who can access the encryption keys.  Even if a user can select from a view, if they are not granted access to the key that encrypts the underlying data then they will get back NULLs for those encrypted columns.  I have a set of sample code for SQL Server 2005 and higher that demonstrates the encryption capabilities of SQL Server here: http://sqlcrypto.codeplex.com


Only you can answer if the the change is worth the effort.

Yes restructuring would be marginally more efficient. The main advantage being that new sensors can be added in the future without altering your table structure. The downside is that it's harder to enforce business rules and logic.

The advantage of your current design is that if the sensor types are consistent per column you could have different data types, int, float, bit, varchar etc, whereas if you put all of your sensor values in a single column you'll need to use the lower common denominator (most likely a varchar).

Given that you're talking about sensors, I expect you will be recording over time, so you'd probably need a timestamp, and decide on what else need to be stored with the value. Quality codes? non-detects? what about the sensor type?


Get disk space percentage free with SQL Server 2005?

This can be done through PowerShell.  See the below:

Get-PSDrive |
    Where-Object {$_.Provider -like "*FileSystem"} |
    Select-Object Name, Used, Free, @{Name = "Used_Percent"; expression = {$_.Used / ($_.Used + $_.Free) * 100}}

Or, through WMI:

Get-WmiObject -Class Win32_LogicalDisk |
    Select-Object DeviceID, VolumeName, @{Name = "Used_Percent"; expression = {($_.Size - $_.FreeSpace) / $_.Size * 100}}

Please see Aaron's comment to your question.  I completely agree with him, this is not something that is easily done through SQL Server.  You can finagle this to be run within SQL Server, but it's not necessarily a native way to get this information directly from SQL Server.


MySQL optimization - year column grouping - using temporary table, filesort

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided.  But, those aren't the real problem.  The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too.  So it may come down to whether you want to pay for it now or pay for it later.


How can I create a view that spans multiple databases?

That isn't that hard to do.  A simple view using the three part names of the tables will do it.

CREATE VIEW YourView AS
SELECT Col1, Col2, Col3
FROM exayear2011.dbo.table1
union all
SELECT Col1, Col2, Col3
FROM exayear2012.dbo.table1
union all
SELECT Col1, Col2, Col3
FROM exayear2013.dbo.table1


Violation of PRIMARY KEY constraint when there is no duplicate

Do you have recursive triggers set? If so you might be re-firing the trigger from inside.
In any case, as previous commenters have noted: you are generating a single ID value, but a trigger may get fired (or your internal join may produce) more than 1 row to be put in the table.
There is nothing to stop this clause:

FROM dbo.TR_ASSIGNMENT_ORG as P, inserted as I
    WHERE ID_RESPONSIBLE_WORKER = I.ID_WORKER

from returning many records.

Ideally you would to find a way to remove the ID generation from the inside of the trigger (eg, convert the ID to an identity & set the seed above the current high watermark of the ID column) - may require app change if records are not inserted via stored proc which are in your gift to change.

You might consider aborting if you are given multiple records (insert count > 1). If you (experimentally) add that and still have a problem then for sure it's your trigger code thats generating the additional inserts.