How to find the number of rows changed by an UPDATE statement in Oracle? Imagine the following table:

id | name | family
---+------+--------
 0 | John | Smith
 1 | Mary | Winters
 2 | John | Doe

and now the following statement:

UPDATE t SET name = 'John' WHERE id = 0;

This statement in fact would update nothing – as it reflects what already is there. Now all the blogs, manuals and whatsnots said to check SQL%ROWCOUNT for the number of updated rows. Doing that returns 1 (affected rows – i.e. those the WHERE clause did match). Let's have another one:

UPDATE t SET family = 'Smith' WHERE name = 'John';

De facto, that would only change the row with id=2, so I want something stating "1 row updated" – but SQL%ROWCOUNT gives us 2 (again, rows affected).

So here comes the final question: What gives the number of rows changed?

If you issue an update statement on your table that effectively updates the data to the same value that is already there, then the database does update the row, so the SQL%ROWCOUNT is accurate even though the new value is the same as the old value.

You can use a flashback versions query to see that database has indeed updated the record:

select t.*
     , VERSIONS_OPERATION
     , VERSIONS_STARTTIME
     , VERSIONS_ENDTIME
     , VERSIONS_XID
  from t
  versions between timestamp systimestamp - interval '10' minute and systimestamp
 where id = 0;

UPDATE t SET name = 'John' WHERE id = 0;
commit;

select t.*
     , VERSIONS_OPERATION
     , VERSIONS_STARTTIME
     , VERSIONS_ENDTIME
     , VERSIONS_XID
  from t
  versions between timestamp systimestamp - interval '10' minute and systimestamp
 where id = 0;

In the first query above there will likely be only one row returned for ID=0.  Then after issuing and committing an update against ID=0 the second time the flashback versions query is run you should get back two records, one for the original record where the VERSIONS_ENDTIME column has been filled in and one for the new version of the record showing that the operation against it was an Update and that the record has a VERSIONS_STARTTIME equal to the end time of the original record.

The only way to prevent records from being updated with the same values is either with an appropriate predicate in the where clause, or in an on update for each row trigger that rejects the change by raising an error.


Issues using pt-kill to kill log queries I'm trying to get pt-kill to work to kill queries running longer than n.

The command as I'm running now is;

pt-kill --busy-time 15 --print -u root -p `cat mysqlpw` --victims all --log /var/log/pt-kill.log --match-all

The problem is that I'm seeing lots of queries under 15 seconds being logged;

# 2016-08-06T20:29:34 KILL 187563 (Sleep 1 sec) NULL
# 2016-08-06T20:29:34 KILL 204826 (Sleep 0 sec) NULL
# 2016-08-06T20:29:34 KILL 204819 (Execute 0 sec) SELECT <snip>

Obviously I have no intention of killing these queries, so i haven't been able to actually use this in anger yet.

Am I missing something in the args? Maybe its an issue with Maria10.1? I'd love to be able to use this.

Thanks.

Try this one:

pt-kill --busy-time 15 --print -u root -p password --match-command Query --victims all

Then add --kill-query if you think this one is what you intend to use.

Edit

Changed --match-all to --victims all


Always On Availability Group manual failover and OS upgrade considerations New to AG - have inherited my first existing system. Have spent quite some time reading MS docs and watching a few training videos but a) concepts seem blurred (terminology similar between different technologies) and b) all this reading raises more questions than answers.

My question is essentially how to proceed with taking over and managing this AG, and how to prepare for, and carry out, some failovers in order to upgrade the OS on the servers?

There are 3 replicas - SQL01, SQL02, SQl04. SQL01 is currently primary.

Firstly - is there a good script to give me an overview of AG configuration and health? I understand the console in SSMS gives me an overview, but is there no script that can automate the task and be used to detect or report on servers lagging behind, etc?

Secondly, how can I check whether any secondary has been set up for read-only routing, and/or backups? I believe backups are run only against SQL01, so what happens if/when there is a failover making SQL02 the primary?

Thirdly and probably related - if the AG exists to route queries to a given node, I can understand that all databases are kept in sync so that applications can continue querying and any failover between nodes should be transparent to the application, but if backups (or mailing of query/report results) are run by scheduled Agent jobs, how are these handled after failover? Is this something manually kept in sync by the DBA?

Fourth, along similar lines - is it conceivable that there are differences between databases across two nodes. ie. I'm not talking about differences that will synchronise automatically; I mean might someone have made a change - such as adding a trigger to a table - on a replica that won't have replicated across all nodes?

Fifth - to start thinking about manual failovers, what things should be checked ahead of failover? I saw a question here talking about checking how quorum is formed - do I need some kind of report on Quorum setup before I fail over? If so, what am I looking for?

Sixth, another question here mentions checking the is_failover_ready column on sys.dm_hadr_database_cluster_states - but when I query that table, I'm told "invalid object name". What gives?

Seventh To the specifics of OS upgrade, what is the basic approach - Should I, say, take SQL04 off the AG (or pause syncing), then upgrade its OS, restart the machine, then re-add to the AG / continue syncing? If so, how? And how do I know at the end of everything that all is ok? What would be the "rollback plan" if something doesn't work out as hoped?

Eighth Continuing with the OS update, do I do the same thing for SQL02, then failover from SQL01 to SQL02, upgrade SQL01 OS then fail back? Again, how to determine each step is "successful"?

Ninth - what happens if I initiate a manual failover but SQL01 is in the middle of backing up 20 or so databases? Is there something to check for me ahead of time, on how database backups might be affected, or something to check after failover, to ensure database backups are still functioning as required, post failover? We have full nightly backups on all databases plus partial and incremental backups - but only on some databases.

Tenth - similarly, what if automated jobs are running at the time of failover? These might be Agent jobs updating data or running queries for mailout, or they might be a third-party BI system pulling data for ETL prior to import into the BI end. Is it safe to presume the BI systems will continue receiving their data post-failover, or should I be checking how they're making their connections to the DB first? (Is it possible they are connecting to SQL01 specifically (or any other node really), in spite of the AG, so that when SQL01 goes down for the OS upgrade, the BI extract fails?

Like I said, have put a fair amount of reading into AGs overall, but not easy to find these big picture answers :/

Firstly - is there a good script to give me an overview of AG
  configuration and health? I understand the console in SSMS gives me an
  overview, but is there no script that can automate the task and be
  used to detect or report on servers lagging behind, etc?

Keep in mind that there are many things that are important regarding Always On Availability Groups that cannot be answered in one or multiple answers alone, below are some considerations.

The dashboard in ssms that you mention is a view into the AG, but you there are many columns unchecked. A few of these:

This dashboard is running queries behind the scenes, you could in theory trace your login with the profiler / extended events and get that you are running with SSMS.

An example of important monitoring is the secondary not being able to follow in redoing the log records, resulting in longer failover times.
This can be checked with the REDO QUEUE size metric.

More on that here

Querying the redo queue size is not that hard:

SELECT redo_queue_size
FROM master.sys.dm_hadr_database_replica_states;

Some of the important dmv's you should look through:

master.sys.availability_groups
master.sys.dm_hadr_database_replica_states
master.sys.availability_replicas
master.sys.dm_hadr_database_replica_states

  Secondly, how can I check whether any secondary has been set up for
  read-only routing, and/or backups? I believe backups are run only
  against SQL01, so what happens if/when there is a failover making
  SQL02 the primary?

This depends on your backup preferences in the AG properties:

In your case, either Primary, Prefer Secondary or Any replica will be set.
If Primary is set then you should change this to one of these two other options.

Even better is that you opt for a solution that is AG aware, one free solution is Ola Hallengren's Maintenance Solution Backups

You could check if read only routing is set up by querying the read_only_routing_url column.

SELECT replica_server_name
    , read_only_routing_url
    , secondary_role_allow_connections_desc
FROM sys.availability_replicas

  Thirdly and probably related - if the AG exists to route queries to a
  given node, I can understand that all databases are kept in sync so
  that applications can continue querying and any failover between nodes
  should be transparent to the application, but if backups (or mailing
  of query/report results) are run by scheduled Agent jobs, how are
  these handled after failover? Is this something manually kept in sync
  by the DBA?

Backups running on the primary will be rolled back when a failover occurs.

Not sure about when a secondary transfers to primary

You could add retry attempts to your jobstep / other process used to take backups.

  Fourth, along similar lines - is it conceivable that there are
  differences between databases across two nodes. ie. I'm not talking
  about differences that will synchronise automatically; I mean might
  someone have made a change - such as adding a trigger to a table - on
  a replica that won't have replicated across all nodes?

Operations on your primary Always On AG databases are transferred over to other nodes.
The trigger will be replicated in normal circumstances.

What you do need to manage yourself are Jobs and Logins.

You can find more of that in this post: Management of Logins and Jobs for the Databases of an Availability Group (SQL Server)

An idea for managing these is by using the commands Copy-DbaLogin and Copy-DbaAgentJob from dbatools to copy these jobs over.

Running these on a set schedule via a job is a possibility.

  Fifth - to start thinking about manual failovers, what things should
  be checked ahead of failover? I saw a question here talking about
  checking how quorum is formed - do I need some kind of report on
  Quorum setup before I fail over? If so, what am I looking for?

The main database health (Synchronized for Sync replica's, Synchronizing for Async replica's), redo_queue size, log_send_queue_size, if any big queries are running on the primary that will take a while to rollback.

Regarding quorum, starting from Windows Server 2012 you have something called dynamic quorum, meaning that votes are recalculated when the quorum changes. This is done by changing the vote state. For example when you have two nodes without a fileshare witness in your WSFC, one of the secondaries will have a vote state of 0 instead of 1.

Read up on dynamic quorum here

  Sixth, another question here mentions checking the is_failover_ready
  column on sys.dm_hadr_database_cluster_states - but when I query that
  table, I'm told "invalid object name". What gives?

I remember that question and asked him about it, that person meant sys.dm_hadr_availability_replica_cluster_nodes.

  Seventh To the specifics of OS upgrade, what is the basic approach -
  Should I, say, take SQL04 off the AG (or pause syncing), then upgrade
  its OS, restart the machine, then re-add to the AG / continue syncing?
  If so, how? And how do I know at the end of everything that all is ok?
  What would be the "rollback plan" if something doesn't work out as
  hoped?

A main walktrough of patching AG's can be found here

Do not remove these databases from the AG, as synchronization might not work when log backups where taken. You would have to restore the log backups before being able to add them again.

Regarding the steps you should execute, there are some differences between what AG setup you have.

When you have only two nodes to remember to change the failover mode to manual because you don't want unexpected failovers while you are patching this secondary.

If you have only two possible secondaries you should do the same when patching these nodes. In short, always set the failover mode of secondaries that are being patched to manual.

The other considerations before turning of the secondary / failing over align with these for a manual failover process discussed earlier.
After the node comes back online, validate the synchronization & database states.

The rollback plan would be that you still have the other nodes, re-add the databases to the node that failed.

  Eighth Continuing with the OS update, do I do the same thing for
  SQL02, then failover from SQL01 to SQL02, upgrade SQL01 OS then fail
  back? Again, how to determine each step is "successful"?

Answered in the answer to question #8.

  Ninth - what happens if I initiate a manual failover but SQL01 is in
  the middle of backing up 20 or so databases? Is there something to
  check for me ahead of time, on how database backups might be affected,
  or something to check after failover, to ensure database backups are
  still functioning as required, post failover? We have full nightly
  backups on all databases plus partial and incremental backups - but
  only on some databases.

Backups on the primary will rollback on failover, you would have to add retry logic.
Not sure about when a secondary transfers to primary

  Tenth - similarly, what if automated jobs are running at the time of
  failover? These might be Agent jobs updating data or running queries
  for mailout, or they might be a third-party BI system pulling data for
  ETL prior to import into the BI end. Is it safe to presume the BI
  systems will continue receiving their data post-failover, or should I
  be checking how they're making their connections to the DB first? (Is
  it possible they are connecting to SQL01 specifically (or any other
  node really), in spite of the AG, so that when SQL01 goes down for the
  OS upgrade, the BI extract fails?

These uncommitted transactions will be rolled back and the job will fail, you would have to add retry logic.

If it is a big operation, consider splitting it up in smaller parts.


Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index? Informix 11.70.TC4DE:

CREATE TABLE cluster_tbl
    (
fk_id INT,
data CHAR(2048)
    );

LOAD FROM "presorted.ld" INSERT INTO cluster_tbl;

CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id);

{###}

CREATE TABLE noncluster_tbl
    (
fk_id INT,
data CHAR(2048)
    );

LOAD FROM "presorted.ld" INSERT INTO noncluster_tbl;

CREATE UNIQUE INDEX ncl_idx ON noncluster_tbl(fk_id);

{###}

UPDATE STATISTICS;

It's my understanding that a cluster index is essentially the same as pre-sorting the load file, inserting it into the table and creating the index for the foreign key column.

I created two tables, each loaded with 2 Million rows of identically data, pre-sorted by the foreign key. However, one table has a cluster index on fk_id, the other table with a non-clustered index on fk_id.

Is there any difference in the tree structure or additional overhead in maintaining or accessing data from a table with a cluster index, versus the same table/data with a non-cluster index?

The order you insert data into a table does not guarantee the order of the data on this disk.

Even if the order happens to be as you wish, it's not structured in a way that the optimiser will know that it's searchable by that order.  (It will be treated as un-ordered.)

When you have a non-clustered index, and you need to use fields not covered by the index itself, the index only contains a pointer to the data.  This is similar to introducing an extra join; a quick one, but a definite extra step in loading that data.

In short, it should nearly always be advantageous to have a clustered index (excepting circumstances like repeatedly inserting data at random positions in the table).

Which is contrary to your experience...

So, what are the queries that you are running?  It may be that something counter intuitive is happening, but it may still be explainable.

You've given us the schema, thanks.
Could you also include the test queries you are using?
And could you also include the methodology and results for measuring performance?
And, finally, if possible, the execution plans for each query?


How can I modify an existing column into a foregin key in postgres? Given for example the following:

CREATE TABLE agency(agency_id, agency_name)
  AS VALUES (1,'Test Agency');

CREATE TABLE employee(employee_id,agency,first_name)
  AS VALUES (16,'Test Agency','Paul');

Desired output:

|---------------------|------------------|------------------|
|      employee_id    |      agency_id   |     first_name   |
|---------------------|------------------|------------------|
|          15         |         1        |       Paul       |
|---------------------|------------------|------------------|

I populated the Employee table from a JSON using ogr2ogr and naturally it wouldn't catch that the agency value is supposed to be referenced from the Agency table.

How can I ALTER or UPDATE the existing column agency in Employee table to take the agency_id of the Agency table and change its type as foreign key?

First make sure you have no duplicate agency_names, or you may have problems.

SELECT agency_name
FROM agency
GROUP BY agency_name
HAVING count(*) > 1;

Make sure your primary key is set on agency,

ALTER TABLE agency
  ADD PRIMARY KEY (agency_id);

Then wrap the modifications all in a transaction.

BEGIN;

    ALTER TABLE employee
      ADD COLUMN agency_id int
      REFERENCES agency;
    UPDATE employee
      SET agency_id = a.agency_id
      FROM agency AS a
      WHERE employee.agency = a.agency_name;
    ALTER TABLE employee
      DROP COLUMN agency;

COMMIT;

Output,

TABLE employee ;
 employee_id | first_name | agency_id
-------------+------------+-----------
          16 | Paul       |         1
(1 row)

Note I assumed you wanted the primary key on the agency_id based on the wording of the question. You can, as @Craig Ringer points out, create an FKEY using the text of the column too. That column will still have to be unique -- see the above. Doing so would look like this,

CREATE UNIQUE INDEX
  ON agency (agency_name);
ALTER TABLE employee
  ADD FOREIGN KEY (agency)
  REFERENCES agency(agency_name);


MongoDB eats 100% CPU when idle I am running in OpenVZ container with Ubuntu 14.04 LTS.

MongoDB version:

db version v3.0.4
git version: 0481c958daeb2969800511e7475dc66986fa9ed5

I use clean installation like described here - http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/

top shows around 100% - 150% CPU

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
30480 mongodb   20   0  548096  66920  39884 S 150.2  1.6   4:55.38 mongod

This is while nothing else is going on. Nothing else is running in the container. No queries, no connections.

I have tried lock file in this question: https://stackoverflow.com/questions/10747580/mongodb-service-at-100-cpu. It's not it.

Edit (Log file upon start)

2015-07-09T14:05:48.998+0200 I JOURNAL  [initandlisten] journal dir=/var/lib/mongodb/journal
2015-07-09T14:05:48.998+0200 I JOURNAL  [initandlisten] recover : no journal files present, no recovery needed
2015-07-09T14:05:49.061+0200 I JOURNAL  [durability] Durability thread started
2015-07-09T14:05:49.061+0200 I JOURNAL  [journal writer] Journal writer thread started
2015-07-09T14:05:49.062+0200 I CONTROL  [initandlisten] MongoDB starting : pid=30480 port=27017 dbpath=/var/lib/mongodb 64-bit host=euler-dev
2015-07-09T14:05:49.062+0200 I CONTROL  [initandlisten]
2015-07-09T14:05:49.062+0200 I CONTROL  [initandlisten] ** WARNING: You are running in OpenVZ which can cause issues on versions of RHEL older than RHEL6.
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten]
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] db version v3.0.4
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] git version: 0481c958daeb2969800511e7475dc66986fa9ed5
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1f 6 Jan 2014
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] build info: Linux ip-10-45-73-23 3.13.0-24-generic #46-Ubuntu SMP Thu Apr 10 19:11:08 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] allocator: tcmalloc
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { bindIp: "127.0.0.1", port: 27017 }, storage: { dbPath: "/var/lib/mongodb" }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongodb/mongod.log" } }
2015-07-09T14:05:49.092+0200 I NETWORK  [initandlisten] waiting for connections on port 27017

Edit2 more detailed logs

A log with journaling enabled: pastebin.com/kKx4SJ98

Journaling disabled: pastebin.com/s2GT71w7

With journaling disabled the CPU usage cuts in half (around 40% - 80%). This all when idle.

This is the same issue as described here (for MySQL): High CPU usage from MySQL with no queries at all running

sudo date -s now

Caused by leap second and a bug in the kernel.


How to teach the optimizer to use index instead of fts with join on data logging master/detail tables? SQL Server 2012 optimizer does not get it right.

Test case, summary:

This is a simplified test scenario. DDL statements at the bottom.

I have two tables for data logging, A and B. There is a 1:n relationship - A has header records with a datetime called a_timeand B has detail records, with a field B.akeyare referencing A.id, and fields name and (data).

A has approx. 25,000,000 records, B has aprox. 500,000,000 records. B has roughly 200 records referencing
each record in A. One A and approx. 200 B records are inserted at a time together every
five minutes, reflected by A.a_time.

Clustered indexes are the primary keys, id, type int identity.

B has one non-clustered index, named IX_B_akey, on B.akey.

A.a_time is (non-clustered) indexed, too.

Now this query:

 SELECT A.a_time, B.*
 FROM B
   join A on B.akey = A.id
 where
    A.a_time > '2017-01-13T01:30:00' and A.a_time < '2017-01-14T07:30:00'
       and B.name in ('name33', 'name55', 'name66')

takes about 3 minutes on my database server.  Execution plan: here (see below for more exact execution plans)

When I add a simple hint to use
IX_B_akey:

 SELECT A.a_time, B.*
 FROM B
   with (index(IX_B_akey))
   join A on B.akey = A.id
 where
    A.a_time > '2017-01-13T01:30:00' and A.a_time < '2017-01-14T07:30:00'
       and B.name in ('name33', 'name55', 'name66')

it runs in less than one second. Execution plan:here (see below for more exact execution plans)

This does not change when I manually update statistics on both tables.

The query plan for the query without the hint shows the server will do a table
scan on B, looking for matching names. It is no surprise that this will take a while. With the hint, it uses the index and does a lookup via index for the
B records referencing matching A records. This is much faster.

I do not want to put query optimizer code into my software. Also, I use NHibernate.
Although it is possible, it would be ugly to use NHibernate interceptors and edit its
SQL.

Maybe the optimizer does not know that all B records referencing one A record are
physically next to each other. They are next to each other because they have been inserted
at the same time. If they were scattered throughout the database, it may be more expensive
to do all the lookups.

Question: How do I help the optimizer to choose the fast plan without a hint in the query? Can I add specific statistics to help here? Do I need a stored query plan?

For reference, here are the DDL statements used to create the tables.

create table A (
    id int not null identity(1,1),
    a_time datetime,
    constraint pkA primary key (id)
)

create table B (
    id int not null identity(1,1),
    akey int not null references A (id),
    name nvarchar(50),
    d decimal(5,3),
    constraint pkB primary key (id)
    )

create index IX_B_akey on B (akey)
create index IX_A_a_time on A (a_time)

Update: Adding name to the index IX_B_Akey would probably help, but it would also nearly double the data volume. This is not a good option.

Update on execution plans: After posting the question, I created another test scenario with the same data structure but more data. The queries are the same, but the date range which is queried has been extended. The database contains 1 mio records in A and 200 mio records in B. This allows me to privide actual execution plans:

Query without hint, takes 27s, time is reproducable

Query with hint, takes 3s, also reproducable

Bad plans come from hard choices.  Instead of making the optimizer choose between a plan with two nested loops joins and a plan with a big parallel hash join, you could reorganize B to optimize for the access path from  A to B.

The best index here is probably to make the clustered PK of B (akey,id). Then there would be only one nested loops join in the already-faster plan, making it obviously better than the parallel hash join plan.


Corruption of files in postgres: The database subdirectory "base/13067" is missing Right, my PostgreSQL database 11.1 is on top of a GlusterFS.
My GlusterFS break, my backup was being done on another table and I don't have backups anymore.
I started the database on a docker container, and i recived some erros:
2020-10-07 22:03:43.677 UTC [359] FATAL:  database "template1" does not exist
2020-10-07 22:03:43.677 UTC [359] DETAIL:  The database subdirectory "base/73729" is missing.

Basically, I only have the postgres data directory. The files on directory are:
base                  opa           pg_ident.conf  pg_replslot   pg_stat_tmp  PG_VERSION            postgresql.conf
core.48               pg_commit_ts  pg_logical     pg_serial     pg_subtrans  pg_wal                postmaster.opts
docker-entrypoint.sh  pg_dynshmem   pg_multixact   pg_snapshots  pg_tblspc    pg_xact               postmaster.pid
global                pg_hba.conf   pg_notify      pg_stat       pg_twophase  postgresql.auto.conf

When i try to pg_dumpall:
pg_dumpall -U user1 -g
pg_dumpall: could not connect to database "template1": FATAL:  database "template1" does not exist
DETAIL:  The database subdirectory "base/73729" is missing.

When i try to pg_dump:
pg_dump -Uuser1 desired_database
pg_dump: [archiver (db)] query failed: ERROR:  could not open file "base/16385/2839": No such file or directory
LINE 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...
                                                             ^
pg_dump: [archiver (db)] query was: SELECT n.tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) as nspacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0)

Theres any way to recovery, make a partial dump or something to extract at least some part of the tables?

It is hard to know what the limit of the corruption is.  If this is the only problem, you should be able to get the globals with:
pg_dumpall -g

And the data with:
pg_dump desired_database

If this works, it would allow you to reload your data into a new cluster.


Why does the column content say “Array”? I got this problem when I'm doing mysql that every time that I post pictures on the blog it will say to the database that I got array on image column. it is supposed to have the image that I inserted

It does have the image that you inserted. The database does not have a renderer that it can use to display the images. All files are stored as an array of bytes (binary format). You would need to extract the file using code and convert it to an image to be displayed. As it stands, there is nothing wrong.


Constraining foreign keys to two parent tables For the model above I want to make it so a robot must have a power supply and motor with matching voltages.  Is there a way to constrain the robot entity so it cannot have a power supply and motor that don't match voltages? Or is there a better way to model this so I don't need a constraint?

Here is the definitive answer for which I can't take credit. That belongs to ypercube who has given me an insight into the power of FOREIGN KEYs with multiple fields. I've taken the liberty of removing most of the previous discussion - it can be followed through the edits.

My first (primitive) answer would have worked, but the DDL below is so much more elegant - it's like comparing the Lascaux cave paintings with Van Gogh's masterpieces.

Definitive DDL/DML with test (using MySQL).

Power supply table.

CREATE TABLE power_supply
(
  part_no INT PRIMARY KEY,
  weight INT,
  voltage INT,
  UNIQUE(part_no, voltage)
);

Motor table.

CREATE TABLE motor
(
  part_no INT PRIMARY KEY,
  model VARCHAR(45),
  voltage INT,
  UNIQUE (part_no, voltage)
);

Robot table.

CREATE TABLE robot
(
  name VARCHAR(30) PRIMARY KEY,
  power_supply INT,
  motor INT,
  voltage INT,
  FOREIGN KEY (power_supply, voltage)
        REFERENCES power_supply (part_no, voltage),
  FOREIGN KEY (motor, voltage)
        REFERENCES motor (part_no, voltage)
);

DML.

INSERT INTO power_supply VALUES(1, 25, 10);
INSERT INTO power_supply VALUES(2, 25, 10);
INSERT INTO power_supply VALUES(3, 15, 15);
INSERT INTO power_supply VALUES(4, 25, 15);
INSERT INTO power_supply VALUES(5, 25, 15);
INSERT INTO power_supply VALUES(6, 25, 20);
INSERT INTO power_supply VALUES(7, 25, 20);
INSERT INTO power_supply VALUES(8, 25, 25);

INSERT INTO motor VALUES(1, 'Model Name', 10);
INSERT INTO motor VALUES(2, 'Model Name', 10);
INSERT INTO motor VALUES(3, 'Model Name', 15);
INSERT INTO motor VALUES(4, 'Model Name', 15);
INSERT INTO motor VALUES(5, 'Model Name', 20);
INSERT INTO motor VALUES(6, 'Model Name', 20);
INSERT INTO motor VALUES(7, 'Model Name', 25);

Test.

INSERT INTO robot VALUES('Robo Name', 2, 6, 15); -- Voltages don't match, update fails
INSERT INTO robot VALUES('Robo Name', 2, 2, 10); -- Voltages match, update succeeds


Zero Date Values & Converting Entire DB From MyISAM to InnoDB I'm trying to update the MYSQL DB for an older wordpress/woocommerce site that's currently using  MyISAM by switching the engine type to InnoDB. The issue I'm having using Alter Table is that there's a number of tables that have a zero date set as the default value, which SQL is not allowing me to change to null.

I don't want to set mysql to ignore the error since it's been deprecated and will likely cause additional problems down the road. Any suggestions would be appreciated.

Also, should I be concerned about causing conflicts by converting all tables to InnoDB from MyISAM?

It would be great if your provide the table schema and mention the column name.

But you can follow this method to mitigate this.

Get the table schema
Create a new table with the same schema, but the engine=InnoDB.
Insert into the new table using select * from the MyISAM table.
Rename the current table as tbl_older
Rename the new table as the actual name.


Read from large table without causing disk queue length issues I have a report that reads from many tables, one of which is 48GB.

To help with performance I planned to create a table which stored a subsection of values from this large table.

However, whenever I read from this table, disk queue length goes from 0.1-0.5 to 10-100.  I've also tried bcp and it has a similar effect on queue length.

How I can get the data out of this table without affecting performance of the database server?

Disk queue length isn't a problem, by itself: SQL Server will batch off IO requests. It's a question of whether your storage subsystem can deliver data fast enough to keep up with your queries.

While the report runs, gather wait statistics - the list of bottlenecks on the server. My favorite way to grab it is with sp_BlitzFirst in the open source First Responder Kit (disclaimer: I'm one of the authors). To gather wait stats for a period of time, run:

sp_BlitzFirst @ExpertMode = 1, @Seconds = 30

To capture wait stats for 30 seconds. Then, you can tune based on your top wait type, and how much of it you're actually seeing.

For example, if your top wait type is PAGEIOLATCH, that means reading data pages from a data file. To reduce that wait, here's the fixes that I'd recommend, in order:

Tune the query
Tune indexes in the execution plan
Add memory to the SQL Server (to cache more data, giving the storage subsystem more time to pull data from disk)
Make the storage go faster

I order 'em that way because that's usually cheapest/easiest up to hardest/most-expensive.

If your wait type is something else, then don't worry about disk queue length. (Actually, don't worry about disk queue length, regardless.)


How to handle nested groupings with possible null elements in one query As you can see in this sql-fiddle, I have three tables: events, event_dates, and event_times.

The original tables contain various other fields, which I omitted as they aren't relevant, I just need to have them in my final result, but I do no operation/sort/filtering/grouping on them.

An event has 1+ event_dates and an event_date has 0+ event_times.

From an object point of view, an event object has a list of event_date objects, which in turn can have a list of event_time objects.

My final goal is to obtain an array of event objects.

My plan is to design a query returning the ids of matching records and from these results fetch the records from their tables in 3 queries and assemble everything in code (this is necessary because of various data-transformations outside the scope of SQL).

My problem is getting the first query right

At the moment I have the following query

SELECT events.id,
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids
FROM events
INNER JOIN event_dates ON event_dates.event_id = events.id
LEFT JOIN (
  SELECT date_id AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids
  FROM event_dates
  LEFT JOIN event_times ON event_times.date_id = event_dates.id
  GROUP BY event_dates.id
) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id
GROUP BY events.id

which fails in the following situation:

Given event e with three dates d1,d2 and d3, where d1 has two times (t1,t2), d2 has zero times, d3 has two times(t3,t4).
The result of the query would be:

event_id  |  event_date_ids  |  event_time_ids
1         |  [1,2,3]         |  [[1,2],[3,4]]

What I'd expect would be an empty array in event_time_ids, so that I could match the indices of the array in event_date_ids with the one for the array in event_time_ids:

event_id  |  event_date_ids  |  event_time_ids
1         |  [1,2,3]         |  [[1,2], [], [3,4]]

How can I obtain this?

P.S.: I deeply apologise if my question is chaotic or unclear and I'm ready to provide every other necessary information.

Thank you!

UPDATE 1

Thanks a lot to @Andriy M for spotting my mistake.

Moving on, I have to add to the equation event_categories where events have 1+ categories.

Adding a DISTINCT in the GROUP_CONCAT would break my current solution as empty arrays would be removed.
The solution is to make this query into a subquery, and join its results to a query fetching all other event data and grouping by events.id.
The event_time_ids would be duplicate for every event row, thus selecting any row for that column is fine.

You need to apply one small change to your query which is emphasised below:

SELECT events.id,
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids
FROM events
INNER JOIN event_dates ON event_dates.event_id = events.id
LEFT JOIN (
  SELECT event_dates.id AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids
  FROM event_dates
  LEFT JOIN event_times ON event_times.date_id = event_dates.id
  GROUP BY event_dates.id
) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id
GROUP BY events.id;

In the derived table you are correctly grouping by event_dates.id but retrieving event_times.date_id instead. That is a mistake because when there is no match, it is null. And it is because one value is returned as null that the outer query omits the corresponding empty group. Replacing event_times.date_id AS event_date_id with event_dates.id AS event_date_id fixes the issue.


SQL Server managed backup missing in ssms Trying to configure Sql Managed backup to azure, and it seems that "Managed backup" is missing from the GUI under Management in SSMS 17.2

All guides I see kind of implies that it's already there. But if its missing, then what is needed to make it show in the Gui? An Azure backup client installation? A command?

Now it seems that you need to run a command to enable the managed backup on the instance, but if that fails, you can't use the gui, because it doesn't show.

Tried SQL Server 2016 Standard and 2016 datacenter evaluation. Using latest SSMS 17.2.

It seems it was added in 2014, but then removed in SQL Server 2016 ... that is what i have personally discovered; but i have not found an authoritive document anywhere saying it was done, all i can find is the CMD line options to enable it.


Padding in info given a types table Suppose I have the following schema for professions: id, type, likelihood. id is the id of the person in question, and I have info regarding the likelihood that that person will read a certain type of book.

What if I want to pad in values? So, let's say I currently have:

id     type     likelihood

1      fantasy  0.9
1      romance  0.5
2      horror   0.2

But, I have a table with all of the possible types:

type

fantasy
romance
horror
mystery
academic

And now I want to pad the table so that I have every person (id) listed with every single type. If the type wasn't in the table to begin with, place the likelihood as 0.

So, I want

id     type     likelihood

1      fantasy   0.9
1      romance   0.2
1      horror    0.0
1      mystery   0.0
1      academic  0.0

I'm having difficulties with this. I've tried using different joins but I can't seem to preserve the info I want while adding in information.

Thanks!

Suppose we have the following tables (and data):

-- test tables and test data
create table t_1 (
  type_ varchar2(64) unique
);

create table t_2 (
  id_ number
, type_ varchar2(64) references t_1(type_)
, likelihood_ number(7,2)
);

begin
  insert into t_1 (type_) values ('fantasy') ;
  insert into t_1 (type_) values ('romance') ;
  insert into t_1 (type_) values ('horror') ;
  insert into t_1 (type_) values ('mystery') ;
  insert into t_1 (type_) values ('academic') ;
  insert into t_2 (id_, type_, likelihood_) values (1,'fantasy', 0.9) ;
  insert into t_2 (id_, type_, likelihood_) values (1,'romance', 0.5) ;
  insert into t_2 (id_, type_, likelihood_) values (2,'horror', 0.2) ;
end;
/

For getting every "userid" mapped to every single type, you could use something like:

select
 *
from
( select distinct id_ from t_2 ) users ,
( select type_ from t_1 ) types
;

ID_  TYPE_
1    fantasy
1    romance
1    horror
1    mystery
1    academic
2    fantasy
2    romance
2    horror
2    mystery
2    academic

Expanding the previous query, we can select all the columns you need (number formatting omitted for clarity):

select
  users.id_
, types.type_
, coalesce (
    ( select
        likelihood_
      from t_2
      where t_2.id_ = users.id_ and t_2.type_ = types.type_
    ) , 0
   ) likelihood
from
( select distinct id_ from t_2 ) users ,
( select type_ from t_1 ) types
;

-- output
ID_  TYPE_     LIKELIHOOD
1    fantasy   0.9
1    romance   0.5
1    horror    0
1    mystery   0
1    academic  0
2    fantasy   0
2    romance   0
2    horror    0.2
2    mystery   0
2    academic  0

Tested w/ Oracle 12c and 11g. Dbfiddle here.


Why can the SA account no longer access the master database? I'm somewhat stuck. We have a database server which was set up and configured by a third party. It's for a third party product so unfortunately we're stuck with the way they've configured it.

Their brilliant configuration includes using the sa account to make a number of connections. For about a week now we've had connection failures filling up all our event logs, two every ten seconds. I have managed to identify that it is the master database this login failure is for.

It's definitely the sa account accessing the master database. It is not an incorrect password (which is good, because we don't know the sa password - and neither does the vendor, it seems. Yay...) because we're getting error state 16 so I believe the sa account has some problems with the master DB.

It's connecting to everything else without problem - I can see the connections made to all other databases successfully. I have checked the sa account permissions and I really can't see anything wrong. I'm at the point of restoring the master database from a backup two weeks old and wanted to open this up to you awesome people out there in case any of you have come across this before and have any alternative suggestions...

Yours hopefully...

Summer

Edit: Aaron kindly took a look as per his answer so I was going to post up a screen print, but I've been on stackoverflow mostly, and only recently come to the dba site...therefore I don't have 10 reputation yet, and can't post an image.  However I've added it to stackoverflow so please check it out here.

We know you are getting 18456 errors. The message (other than the 'Login failed' part) and the error state are much more important for diagnosing. In the SQL Server error log, there will be more information than you get from the application or the dialog in SSMS. In SSMS open Object Explorer for the server in question, expand Management, expand SQL Server Logs, right-click "Current - ..." and choose View SQL Server Log. You should be able to find events like this:

This number and the extended reason are not reflected elsewhere because it's meant to obscure the actual failure from the end user, in case that user is malicious. (For example, the state for wrong password might help them see that they are on the right track.)

You can see a list of all the states I know here, which should help you resolve the issue:

https://sqlblog.org/2011/01/14/troubleshooting-error-18456

For state 16 it doesn't make sense that the database is offline or inaccessible, unless someone has somehow demoted sa from the sysadmin role and/or explicitly denied access to master, since master can't be offline. I suspect it is much more likely that their default database has changed, or that the database they tried to connect to explicitly is not online. What happens if you run:

ALTER LOGIN sa WITH DEFAULT_DATABASE = master;

Also, why is your application using the sa login? Have you considered creating a separate, less-privileged account to dedicate to your application?


Oracle 12c Client Compatible with 9i Server? I'm developing on a new .NET application that needs to read/write data from an Oracle 9i database. I've got it working with ODAC 11.2.0.4 which comes with Instant Client 11.2. I had selected this version because I had read that Oracle 12c Client was only backwards Compatible to version 10g release 2 servers. Unfortunately I can't find that reference.

I would prefer to use latest version of ODAC 12.2, but I need to confirm that I'll still be able to connect to a 9i database.

Note: All the links I found to Oracle Compatibility charts are hidden behind an Oracle registration wall that isn't currently working for new signups. So I'm asking the question here.

This is from behind the Oracle registration wall:

Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)

Client Version: 12.2.0 or 12.1.0
Server Version: 9.2.0
Supported: No #3

Where:

#3: An ORA-3134 error is correctly reported when attempting to connect to this version.

So I would not expect it to work at all.


minimum space is required on disk to restore a backup in SQl Server? Suppose I am going to restore a backup (2 GB) in my sql server, I have sufficient space (20 GB) in my disk, but is there any algorithm that SQL server follows to calculate the minimum disk space required or just randomly checks the disk space and the backup to do a successful restore.

As I want to know the internal working of SQL Server.

Thanks in Advance.

The backup file maintains metadata about the database and files that SQL Server uses during the restore process. Part of that metadata is a listing of data and log files that are in the database, and their respective sizes (as well as other information).

If you run RESTORE FILELISTONLY you can see the minimum size required for each data and log file in the database that will be restored. When SQL Server performs a normal restore it first checks these values and validates that the disk where each file is to be restore has sufficient space. If not, SQL Server throws an error indicating how much space is required and how much is available on the disk.


get oracle execution plan by sql_id Is it possible to get an execution plan of currently running query by sql_id?

I couldn't succeed with:

select
   DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
        (sql_id=>'b3x6apqyskn7x') report
from dual;

select * from table(dbms_xplan.display_cursor('sql_id', child_number));

You can find sql_id and child_number in V$SESSION (sql_id and sql_child_number columns).


MySQL order by - one column conditional on another column In MySQL  I have a table (simplified for this question)

CREATE TABLE `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `priority` int(11) DEFAULT '1000000',
  `status` enum('new','in_progress','complete') DEFAULT 'new',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8

I want to select from the table ordered by priority but with rows where status is 'complete' to come last.

This works:

SELECT * FROM projects
order by
    case
        when status='complete' then 999999
        when status!='complete' then priority
    end ASC

but changing the 999999 to (select max(priority)+1 from projects) gives unexpected results, half way down the first page of results rows with status=complete appear.

Also this last method probably causes repeated calculation of the same value.

What is the best way to do this?

How about...

ORDER BY (`status` = 'complete'), `priority`

The expression status = 'complete' would resolve to 0 for non-complete, and 1 for complete... exactly the order you want... so you'd get all of the non-completed items sorted by priority, followed by all the completed items sorted by priority.


Column in query should be selected according to value from another column Assuming I have table1 with following data:
table1
id| coffee_sender | coffee_receiver | received_bag_size | sent_bag_size| price |
21  tux              -1                  m                 -1            45
22  alsa            -1                  -1                 xl            36
23  -1               tux                 l                 -1            51

And I use the following query:
select t1.*
from (
select t.price, t.id
from table1 t
where coffee_sender in (tux, alsa)
or coffee_receiver in (tux, alsa)
and received_bag_size in (m, xl)
or sent_bag_size in (m, xl)) t1

What i want is to only fetch the rows that have coffee_sender/coffee_receiver AND sent_bag_size/received_bag_size filled with a value (rows that match the following conditions):
coffee_sender = tux, sent_bag_size= xl

OR
coffee_receiver = tux, received_bag_size= m

OR
coffee_sender = tux, sent_bag_size = xl AND coffee_receiver = tux, received_bag_size= m

So id = 21 would be excluded from the example table1.
How can i update the above query for this ? Thank you in advance.
Update: to make the question a little more clear:
What i want to see is the actual rows where coffee vendor and size are linked. Each coffee vendor (tux, alsa) has a bag size at a a certain point in time. This will be inserted in the table when shipment is done.
I hope this clarifies what I wanted to ask.

select t.price, t.id
from table1 t
where (coffee_sender = tux and sent_bag_size= xl)
or (coffee_receiver = tux and received_bag_size= m)
or (coffee_sender = tux and sent_bag_size = xl AND coffee_receiver = tux and received_bag_size= m)


How to stop MySQL recreating SSL files on restart We're using MySQL 5.7.22. Each time I restart MySQL it creates a set of SSL files ( ca-key.pem etc) in the datadir.
The problem is that I already have these files on the server, in /etc/mysql/ssl.

Running show global variables like '%ssl%' shows me that it is looking at the correct files in /etc/mysql/ssl, but it still creates the new ones in the datadir.

I can see that there is an option to auto_generate_certs=off
but when I add this to my.cnf file, it just comes up as unknown variable and MySQL won't start

Is there a way to prevent it from creating these certificates every time I restart the database?

From the documentation page you linked to:

  This variable is available if the server was compiled using OpenSSL

It sounds like you're using the standard MySQL community edition binary distribution, which is not compiled with OpenSSL, and this is why you're getting the error message when you try to start up with auto_generate_certs=OFF.

See OpenSSL vs yaSSL for details.

See also documentation for the ssl_capath variable, which is what I assume you looked at to deduce that MySQL was looking for your certificate files in /etc/mysql/ssl:

  MySQL distributions compiled using OpenSSL support the --ssl-capath option (see Section 6.4.4, “OpenSSL Versus yaSSL”). Distributions compiled using yaSSL do not because yaSSL does not look in any directory and does not follow a chained certificate tree. yaSSL requires that all components of the CA certificate tree be contained within a single CA certificate tree and that each certificate in the file has a unique SubjectName value. To work around this yaSSL limitation, concatenate the individual certificate files comprising the certificate tree into a new file and specify that file as the value of the --ssl-ca option.


Question about Oracle's interpretation of "host cpu utilization" in v$sysmetric view I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization"

It says in the metric_unit as % busy/(idle + busy), but what is busy?  Is that the sum of usr + sys time?  I tried comparing the query results from v$sysmetric against output from mpstat but it didn't seem to match.

The figures in the v$sysmetric view are aggregated from v$osstat over a period of time (either 15 or 60 seconds).

v$osstat figures should closely mirror what you'd see from a tool such as iostat, sar, mpstat, top or nmon, but they will never be identical as it is impossible to synchronise the performance monitoring tool data collection with the data collection performed by the Oracle MMON process. As a result, the aggregated data in v$sysmetric will also never perfectly match monitoring tool output, but should be pretty close.

Busy% is indeed Usr% + Sys%.


How to show how many times the server was restarted on SQL Server? Is there any way to show how many times the server was restarted this month?

I'm using SQL Server 2008.

Thanks

Well, I think this can help:

you create a table, insert errorLog data inside this tabel and just select it:

create table ErrorLog (
LogDate datetime,
ProcessInfo varchar(100),
Text varchar(max))

insert into errorlog  EXEC sp_readerrorlog

select * from errorlog
    where Year(logdate)='2018'
    and text like '%Something%'

Or with a count:

select count(*) from errorlog
        where Year(logdate)='2018'
            and text like '%backup%'

We just need to use a string that is used during a server restart. Maybe '%Server name is%'. I can see this string on every restart.


What happens to queries that are executing when SSMS crashes? Lets imagine that we use SQL Server Management Studio to execute a very long delete query. For the sake of this example lets say that it's execute time is 10 hours. While it executes a few hours into the process SSMS crashes. What will happen to the query? Will it be rolled back or will it continue executing despite the fact that SSMS is no longer running?

Database restores are terminated when SSMS crashes, but I'm curious to know what happens with alters and delets.

When SSMS closes for whatever reason, your session will end and any uncommitted transactions will be rolled back (and yup, database restores will stop).

BUT, if SSMS crashes and gives you the 'This Program Has Terminated Unexpectedly' or whatever the error message is, don't touch anything.  At this point you might find that your session is still active and will be until you click 'Close' or 'Restart'.

I've had this a few times but if you open up another instance of SSMS and run sp_who2 you'll be able to see if your transaction is still active.


Selecting 1st and 3rd Tuesdays So here is my situation. I am trying to write a script which will (among other things) allow me to measure prime time block utilization. For reasons unknown to and unchangeable by me, various departments here consider "prime time" to be different on one day of the week. I've gotten around this by writing the following into SQL:

CASE WHEN DATENAME(DW, OD.CALENDAR_DT) = 'Tuesday'
     THEN CAST(OD.CALENDAR_DT + '08:00:00.000' AS DATETIME)
     ELSE CAST(OD.CALENDAR_DT + '07:00:00.000' AS DATETIME)
END AS PT_BLOCK_START

However, one department has their prime time block measured differently on Tuesdays, but not consistently. Prime Time is measured most days from 7:30-5, but on the 1st and 3rd Tuesday of a month it's 9 to 5 and on the 2nd and 4th Tuesday it is 8 to 5. I am trying to figure out how to write a statement similar to the above that accounts for which Tuesday of the month it is, but the little hamster than runs on the wheel in my skull seems to have keeled over dead.

Any thoughts will be greatly appreciated and will surely bring mounds of good karma, if you believe in that sort of thing.

This code works by passing a date value, such as "getdate()", to a function.

Works great.  Not my work.

Source:  http://mikedefehr.com/2011/07/25/finding-the-nth-weekday-of-a-given-month/

CREATE FUNCTION nth_weekday_of_month_fn (
@dt DATETIME,
@dow VARCHAR(10),
@num INT
)
RETURNS DATETIME
AS
BEGIN

DECLARE @RetDate DATETIME;

WITH MonthDays AS
(
SELECT DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) AS
MonthDate
FROM MASTER..spt_values
WHERE number > 0
   AND TYPE = 'P'
)
, WeekDays AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY MonthDate) DayIndex
FROM MonthDays
WHERE YEAR(@dt) = YEAR(MonthDate)
   AND MONTH(@dt) = MONTH(MonthDate)
   AND DATENAME(dw,MonthDate) LIKE @dow + '%'
)
SELECT @RetDate = MonthDate
FROM WeekDays
WHERE DayIndex = @num RETURN (@RetDate) END
GO

For 1st and 3rd Tuesdays of current month:

SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Tuesday',1)
SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Tuesday',3)


Enabling SQL Instant File Initialization when Restoring to a Network Path We have a large Read Only SQL DB (SQL 2012) that we’re restoring to a NAS as a current duct tape solution. I had wondered why the restore process seemed to be hanging.

Part of my guidance along the way has come from:

http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/
and
http://www.brentozar.com/blitz/instant-file-initialization/

I believe I’ve identified that Instant File Initialization may not available when creating or restoring to a UNC path (or may need additional setup). SQL code below demonstrates that

DBCC TRACEON(3004,3605,-1)
GO
CREATE DATABASE [DestinedForGreatness]
GO
DROP DATABASE DestinedForGreatness
GO
EXEC sp_readerrorlog
GO
CREATE DATABASE [DestinedForGreatness]
ON PRIMARY
( NAME = N’DestinedForGreatness’, FILENAME = N’\\LittleBlackBox\SQL2012data\MSSQL\Data\DestinedForGreatness.mdf’ )
LOG ON
( NAME = N’DestinedForGreatness_log’, FILENAME = N’\\LittleBlackBox\SQL2012data\MSSQL\Data\DestinedForGreatness_log.ldf’)
GO
DROP DATABASE DestinedForGreatness
GO
EXEC sp_readerrorlog
GO
DBCC TRACEOFF(3004,3605,-1)

I can see in the first instance when creating a DB locally it doesn't 0 fill the MDF from the errorlog.  In the second instance it does.

I’d be interested to know if there’s any workaround for this to take advantage of IFI.  Would application of the Windows security settings on the server I'm restoring to resolve this?

This restriction of 'instant file initialization' (aka. SetFileValidData) is documented:

  Note  The file cannot be a network file, or be compressed, sparse, or transacted.


Every batch causes a compile We have a third party application that sends T-SQL statements in batches.

The database is hosted on a SQL Server 2016 Enterprise SP1 CU7, 16 cores and 256GB memory. Optimize for Ad-Hoc is enabled.

This is a dummy example of the queries that are being executed:

exec sp_executesql N'
IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT

select field1, field2 from table1 where field1=@1
option(keep plan, keepfixed, loop join)

select field3, field4 from table2 where field3=@1
option(keep plan, keepfixed, loop join)', N'@1 nvarchar(6)',@1=N'test'

When I monitor the database and I look at batches/sec and compiles/sec, I notice they are always the same.  Under heavy load, this can be 1000 batches/sec and 1000 compiles/sec.  Under average load, there are 150 batches/sec.

I analyze the query cache for recently compiled plans:

SELECT TOP (1000) qs.creation_time
    , DatabaseName = DB_NAME(st.dbid)
    , qs.execution_count
    , st.text
    , qs.plan_handle
    , qs.sql_handle
    , qs.query_hash
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st
ORDER BY creation_time DESC;

When I run above query I only see 10-20 new query plans/sec.

It's like every sp_executesql call triggers a compile but the queryplan is not cached.

What can be the cause of batches/sec being equal to compiles/sec ?

It's like every sp_executesql call triggers a compile but the query plan is not cached.

SQL Server does not cache a query plan for batches containing only an sp_executesql call. Without a cached plan, a compilation occurs each time. This is by design, and expected.

SQL Server avoids caching batches with a low cost to compile. The details of what is and is not cached has changed many times over the years. See my answer to Trace flag 2861 and what a 'zero-cost' plan actually means for details.

In short, the probability of reuse (including specific parameter values) is small, and the cost of compiling the ad hoc text containing the sp_executesql call is very small. The inner parameterized batch produced by sp_executesql is of course cached and reused - this is the value of it. The extended stored procedure sp_executesql itself is also cached.

To be cached and reused, the sp_executesql statement would have to be part of a larger batch that is considered worth caching. For example:

-- Show compilation counter
SELECT
    DOPC.[object_name],
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.counter_name = N'SQL Compilations/sec'
GO
-- This is only here to make the batch worth caching
DECLARE @TC integer =
(
    SELECT TOP (1) @@TRANCOUNT
    FROM master.dbo.spt_values AS SV
);

-- Example call we are testing
-- (use anything for the inner query, this example uses the Stack Overflow database
EXECUTE sys.sp_executesql
    N'SELECT LT.Type FROM dbo.LinkTypes AS LT WHERE LT.Id = @id;',
    N'@id int',
    @id = 1;
GO
-- Show compilation counter again
SELECT
    DOPC.[object_name],
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.counter_name = N'SQL Compilations/sec'

Run that code several times. On the first time though, many compilations are reported as expected. On the second time, no compilations are reported, unless optimize for ad hoc workloads is enabled (so only a Compiled Plan Stub is cached). On the third time, no compilations are reported in any case, since any stub is promoted to a fully cached ad hoc plan.

Remove the DECLARE @TC statement to see that the sys.sp_executesql statement is never cached without it, regardless of the number of times it is executed.

View the associated plan cache entries with:

-- Show cached plans
SELECT
    DECP.refcounts,
    DECP.usecounts,
    DECP.size_in_bytes,
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DECP.parent_plan_handle,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    DEST.[text] LIKE N'%sp_executesql%'
    AND DEST.[text] NOT LIKE N'%dm_exec_cached_plans%';

Related Q & A: Do triggers compile each time?


Why is my AWS RDS (postgres) at a high percentage of cpu with no active sessions? I have two postgres db, prod and dev.  Lately, dev has been really slow, so I checked into it, and it appears to be using CPU credits faster than I'm getting them.  I could not figure out why, so I checked the cpu usage history, and it appears that after a strange spike, the baseline has settled to twice the usage it was before:

I could not find any active sessions which would warrant such cpu usage, but just to be sure, I stopped the db, let it sit for a bit, then started it again.  Even after a fresh start, though, the cpu usage is still strangely high, even as compared to a very similar db:

How can I figure out what all of this usage is coming from?

I can't guarantee this will always work, but what managed to be effective for me was switching to a different instance, then switching back.

To do so, go up to Modify button in the RDS > Databases > [MY_DB} view, and then change your instance to any other instance type, wait for the update, then switch back.

I suppose I just had a bad ec2.mirco, and the method above got me a different (better) one which does not exhaust more cpu credits at idle than it gains.


How to convert PostgreSQL to MySQL How can I convert database from PostgreSQL to MySQL?

I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it produces this error:

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the right
  syntax to use near 'bigserial NOT NULL,
    created timestamp NOT NULL,
    sql0 varchar(250) DEFAUL' at line 2

Use following command to get the backup data.

pg_dump --inserts -f filename.dump your_database

This commands generates the sql script that can be used to restore schema as well as data to other sql databases. The --inserts options tells it to use inserts instead of default copy commands.

Here is the manual - http://www.postgresql.org/docs/current/interactive/app-pgdump.html


Postgres role without attribute Login I am a bit confused by the role attribute LOGIN | NONLOGIN, where postgres docs says:

  These clauses determine whether a role is allowed to log in; that is,
  whether the role can be given as the initial session authorization
  name during client connection. A role having the LOGIN attribute can
  be thought of as a user. Roles without this attribute are useful for
  managing database privileges, but are not users in the usual sense of
  the word. If not specified, NONLOGIN is the default, except when CREATE
  ROLE is invoked through its alternative spelling CREATE USER.

What is a possible restriction with NONLOGIN? Why would people want a role with NONLOGIN in the first place?

The clause is NOLOGIN, not NONLOGIN. (I suggest copy/paste for quotes.)

The clause is for "group" roles, which used to actually be GROUP before Postgres 8.1 - which is still supported. See CREATE GROUP in the manual. Related:

Why did PostgreSQL merge users and groups into roles?

Group roles are very useful to bundle privileges and then grant / revoke the whole package to LOGIN roles. See:

Grant privileges for a particular database in PostgreSQL

Or to act as demons within the database:

Allow insertion only from within a trigger


Best database design for payments with multiple constraints I have to register payments.

There are two things to pay. thingC or thingD (they may not be paid).
You can only pay one thingD per payment transaction.
You can pay many thingC per payment transaction.
You can pay for thingC or thingD per payment, not both.
There are three codes that have to be generated for each thingC or thingD that was paid.

Existing tables

thingC

idthingC serial PRIMARY KEY
--more columns

thingD

idthingD serial PRIMARY KEY
--more columns

Tables I think I need:

payment

idpayment serial PRIMARY KEY
idthingD integer REFERENCES thingD -- the payment may be for a thingD
type character(1) NOT NULL --C or D
c1 integer
c2 integer
c3 integer

payment_C

idpaymentC serial NOT NULL
idpayment integer NOT NULL REFERENCES payment
idthingC integer NOT NULL REFERENCES thingC
c1 integer NOT NULL
c2 integer NOT NULL
c3 integer NOT NULL

In table payment I'll have values in idthingD, c1, c2 and c3 if type is 'D'. The columns mentioned above will be null if type is 'C', and there will be values in table payment_C.

I have to be able to say:

idpayment 1 was for thingD 2 and have the codes 1, 4, 8.
idpayment 2 was for thingC 5 with codes 2, 3, 6 AND thingC 8 with codes 4, 5, 8

What do you think? In which table should the codes be? Can the design be improved.

You obviously put some thought into this design. But you would regret that you have to search for codes in two tables. And what if you want to add details for codes?

I suggest to store all codes in the same table like this:

CREATE TABLE payment (
   idpayment serial PRIMARY KEY
   -- more payment details?
);

CREATE TABLE payment_code (
   idpayment_code serial PRIMARY KEY
 , idpayment      int NOT NULL REFERENCES payment
 , idthingC       int REFERENCES thingC  -- can be NULL
 , idthingD       int REFERENCES thingD  -- can be NULL
 , c1             int NOT NULL
 , c2             int NOT NULL
 , c3             int NOT NULL
 , CONSTRAINT either_c_or_d              -- reference either to c or to d
      CHECK (idthingC IS NULL AND idthingD IS NOT NULL
          OR idthingD IS NULL AND idthingC IS NOT NULL)
 , CONSTRAINT c_distinct_per_payment     -- distinct idthingC per payment (?)
      UNIQUE (idpayment, idthingC)
 );

CREATE UNIQUE INDEX idx_one_d_per_idpayment ON payment_code (idpayment)
WHERE idthingD IS NOT NULL;              -- only one idthingD per payment

(Depending on the nature of the codes, maybe even just 1 code per row.)

The partial unique index idx_one_d_per_idpayment disallows more than one reference to thingD per payment.

This allows at most 1 thingD per payment and any number of distinct thingC.

Mutually exclusive payment types

You later clarified, those are mutually exclusive.
This allows at most 1 thingD per payment or any number of distinct thingC (but not both):

CREATE TABLE payment (
   idpayment serial PRIMARY KEY
 , type_cd   "char" NOT NULL DEFAULT 'C'  -- optional default
   -- more payment details?
, CONSTRAINT type_cd_valid CHECK (type_cd = 'C' OR type_cd = 'D')
, CONSTRAINT redundant_uni_for_fk_constraint UNIQUE (idpayment, type_cd)
);

CREATE TABLE payment_code (
   idpayment_code serial PRIMARY KEY
 , idpayment      int NOT NULL      -- multicolumn ...
 , type_cd        "char" NOT NULL   -- ... FK reference
 , idthingC       int REFERENCES thingC
 , idthingD       int REFERENCES thingD
 , c1             int NOT NULL
 , c2             int NOT NULL
 , c3             int NOT NULL
 , CONSTRAINT either_c_or_d
      CHECK (type_cd = 'C' AND idthingC IS NOT NULL
          OR type_cd = 'D' AND idthingD IS NOT NULL)
 , CONSTRAINT c_distinct_per_payment
      UNIQUE (idpayment, idthingC)
 , CONSTRAINT payment_c_or_d
      FOREIGN KEY (idpayment, type_cd) REFERENCES payment(idpayment, type_cd)
 );

CREATE UNIQUE INDEX idx_one_d_per_idpayment ON payment_code (idpayment)
WHERE type_cd = 'D';  -- slightly simpler now

To enforce your rules, add a type column in table payment (I named it type_cd since "type" is too generic, and I chose data type "char" - with double quotes - efficient for tiny enumerations). Include that column in the FK reference (redundantly). This allows a CHECK constraint (either_c_or_d) in table payment_code to enforce valid references.

The UNIQUE constraint redundant_uni_for_fk_constraint seems redundant, but is required for the multicolumn FK reference on (idpayment, type_cd). Related, with more explanation:

Enforcing constraints "two tables away"

Both FK columns (idpayment, type_cd) must be NOT NULL to enforce referential integrity. Or (if you need rows without assigned payment) declare the FK constraint as MATCH FULL. Details:

Two-column foreign key constraint only when third column is NOT NULL
MATCH FULL vs MATCH SIMPLE in foreign key constraints


Partition table behavior for Postgres I have created a partitioned table with 1 partition. below is the script for the same:

CREATE TABLE public.ms (
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
a int8 NOT NULL,
b int4 NOT NULL,
c varchar(2000) NULL,
d int2 NOT NULL,
e bool NOT NULL DEFAULT false,
f timestamp NOT NULL DEFAULT timezone('UTC'::text, CURRENT_TIMESTAMP),
g jsonb NULL,
h int2 NOT NULL DEFAULT 1,
i bool NOT NULL DEFAULT true,
j int2 NULL,
CONSTRAINT me_pkey PRIMARY KEY (id)
)PARTITION BY RANGE (id) ;

CREATE TABLE ms3_0  PARTITION OF public.msg
FOR VALUES FROM (1) to (1000);

Now, when I'm running insert statement for this via below loop, I'm getting below error message:

do $$
begin
for i in 1..1000
loop
INSERT INTO public.ms
(a, b, c, d, e, f,  h, i , j)
VALUES(2, 0, '', 0, false, '2019-01-28 23:59:51', 1, true, 0);
end loop;
end;$$

SQL Error [23514]: ERROR: no partition of relation "ms" found for row
  Detail: Partition key of the failing row contains (id) = (1001).

However, till id '1000', partition has been created already.  After that, I dropped the existing table and recreated the same table and partition again. Now, I started inserting records manually via a single 'insert' statement. Then, I could insert into them.

After, this, I have created 2nd partition like below:

CREATE TABLE ms3_1  PARTITION OF public.ms
FOR VALUES FROM (1001) to (2000);

And, again executed the same loop for 1000 entries and got below error:

SQL Error [23514]: ERROR: no partition of relation "ms" found for row
  Detail: Partition key of the failing row contains (id) = (1000)

However, when I insert in the batch of 100 , like below: I could insert  that:

do $$
begin
for i in 0..100
loop
INSERT INTO public.ms
(a, b, c, d, e, f,  h, i, j)
VALUES(2, 0, '', 0, false, '2019-01-28 23:59:51', 1, true, 0);
end loop;
end;$$

From the above whole scenario , I have a few queries:

In the first case, why it wasn't inserting data (1000 values) into first partition when partition for first 1000 values was  created already?
After inserting 3-4 records into first partition and then After creating again another partition (ms3_1) with 1000 records, when I try to perform bulk insert of 1000 records into the same ms table, why I'm not able to make that?

Any help/reason for this will be appreciated here. Since, it will help me to configure partition as per my business requirement.

I can answer the first question, the rest of your description is too confused for me to follow.

The first 1000 rows were inserted successfully into the existing partition, but inserting the row with id = 1000 failed. This error caused the whole transaction to be rolled back, including the previously inserted rows.

One thing that may also confuse you is that with range partitioning, the upper end of the range is excluded. So if you create a partition

FOR VALUES FROM (1) to (1000)

it can only accommodate values up to 999. This looks surprising with discrete data types like integer, but makes life much easier when using other data types:

FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00')

is so much nicer than having to use an upper limit of 2019-12-31 23:59:59.999999.


Windows Clustering and SQL Availability Groups I am new to Clustering and Availability groups.  I am creating a 4 node Windows 2012 Cluster with SQL 2016 enterprise.  SQL will not be clustered we are just using the availability group option.  The cluster is setup like: 3 servers on one site and 1 server offsite.  Our goal is to have multiple databases on the servers Server 1 would be primary for availability group 1 and server 2 would be primary for availability group 2.  Server 3 and 4 would be primary failover availability groups as well as server 1 being a failover for server 2 and server 2 being a failover for server 1.  Server 4 would also be the offsite DR server.

I have the windows cluster setup to have the Quorum drive as a file share that all 4 servers can get too.  One of the databases on the servers will need access to the file system for import and export of csv, xls, etc. files.

How can all 4 servers have access to this drive, when I have one server in a different location on a different subnet?

Any help or direction would be appreciated.

How can all 4 servers have access to this drive, when I have one server in a different location on a different subnet?

Get really friendly with your network and security teams. Ask them, politely if need be, to create a file share for that folder and allow your service accounts access to said file share.

Other than the service account, this really doesn't have any involvement with SQL Server.


designing a star schema I am a newbie and would appreciate suggestions/material to research
I have 3 types on information in a single table

problems

reasons

fixes

problem data is related to reasons by 1 to many
reasons data is related to fixes by 1 to many
I am thinking of creating a dimension table for dates as mentioned here
How could I design a star schema out of it? As of now the entire data is in a single table (all joined together)
#####################
update 1 - example data and my 1st solution below. Please guide
my columns are
problem id
problem description
problem associate department
problem due date
reason id
reason description
reason associated person
reason due date
fix id
fix description
fix owning employee
fix due date

example data

+------------+---------------------+------------------------------+------------------+-----------+--------------------------+--------------------------+-----------------+--------+----------------------+---------------------+--------------+
| problem id | problem description | problem associate department | problem due date | reason id |    reason description    | reason associated person | reason due date | fix id |   fix description    | fix owning employee | fix due date |
+------------+---------------------+------------------------------+------------------+-----------+--------------------------+--------------------------+-----------------+--------+----------------------+---------------------+--------------+
|          1 | wrong customer info | sales                        | 1-Jan-21         |         1 | customer gave wrong info | a                        | 15-Dec-20       |      1 | contact customer     | bb                  | 10-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         2 | customer gave wrong info | a                        | 15-Dec-20       |      2 | verify customer info | cc                  | 15-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         1 | data entry error         | b                        | 16-Dec-20       |      1 | correct data entry   | bb                  | 16-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         1 | database wrong join      | c                        | 20-Dec-20       |      1 | fix join             | aa                  | 19-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         2 | database wrong join      | c                        | 20-Dec-20       |      2 | something else       | dd                  | 19-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         3 | database wrong join      | c                        | 20-Dec-20       |      3 | modify tables        | bb                  | 19-Dec-20    |
+------------+---------------------+------------------------------+------------------+-----------+--------------------------+--------------------------+-----------------+--------+----------------------+---------------------+--------------+

I am thinking of a design like below. all joins would be 1:1 in case of fact table. In case of fact table2 calendar there would be 1:many join as that fact table would have one row per date while the dim tables could have same date on multiple rows

A very basic star-schema for your situation might look like this:

The T-SQL statements to create the dimension tables for this schema look like:
CREATE TABLE dbo.problems
(
    problem_id int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT problems_pk
        PRIMARY KEY
        CLUSTERED
    , problem_description nvarchar(400) NOT NULL
);

CREATE TABLE dbo.reasons
(
    reason_id int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT reasons_pk
        PRIMARY KEY
        CLUSTERED
    , problem_id int NOT NULL
        CONSTRAINT reasons_problem_fk
        FOREIGN KEY
        REFERENCES dbo.problems(problem_id)
    , reason_text nvarchar(500) NOT NULL
);

CREATE TABLE dbo.fixes
(
    fix_id int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT fixes_pk
        PRIMARY KEY
        CLUSTERED
    , reason_id int NOT NULL
        CONSTRAINT fixes_reason_fk
        FOREIGN KEY
        REFERENCES dbo.reasons(reason_id)
    , fix_text nvarchar(525) NOT NULL
);

CREATE TABLE dbo.dates
(
    date_id int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT dates_pk
        PRIMARY KEY
        CLUSTERED
    , starting_date datetime NOT NULL
    , ending_date datetime NOT NULL
    , CONSTRAINT dates_dates
        CHECK (ending_date > starting_date)
);

The fact table, which you seem to have left out of your question, and I've named incidents, looks like:
CREATE TABLE dbo.incidents
(
    incident_id int NOT NULL
        CONSTRAINT incidents_pk
        PRIMARY KEY
        CLUSTERED
    , incident_date_id int NOT NULL
        CONSTRAINT incidents_incident_date
        FOREIGN KEY
        REFERENCES dbo.dates (date_id)
    , problem_id int NOT NULL
        CONSTRAINT incidents_problem_fk
        FOREIGN KEY
        REFERENCES dbo.problems(problem_id)
    , fix_id int NULL
        CONSTRAINT incidents_fix_fk
        FOREIGN KEY
        REFERENCES dbo.fixes(fix_id)
    , fix_date_id int NOT NULL
        CONSTRAINT incidents_fix_date
        FOREIGN KEY
        REFERENCES dbo.dates (date_id)
);

incidents references the dimension tables such that you are storing the bare minimum of information in the fact table.  The fix_id column is referenced in the incidents table to indicate which of the possibly many fixes was accepted as the actual fix used in this incident.  Since each fix is related to only a single reason, you can infer the reason from the fix.  I've included a discrete column to indicate the problem in this table to enable incidents that don't yet have a fix.
You could add a reason_id foreign-key column to the incidents table if desired.  For instance, maybe you identify a reason prior to determining the fix.


How to mark an item as "Expired" when it's ExpiryDate value is in the past? I have a table of items, each of which has a Status and ExpiryDate.

What is the best way of enforcing this:

when ExpireyDate > DateTime.Now
update Status to "expired"

I could have a stored procedure which is run periodically, which checks all items and updates as needed. However I would like to avoid any delay of the item expiring, and being marked as expired.

Is there a better way of doing this? Some kind of constraint in the Db?

How would an auction site do this, for example? Where the 'closing' of an auction at a very precise time is important.

Edit

I marked gbn s answer as the solution, as it provides a solution to my issue. However I think user212102 is getting at the crux of the issue - why am I storing status, rather than computing it based on the state of the item? I am going to look into a better way of achieving this.

Assuming you want the expired flag to be real time...

You can use a view to access the data that has a CASE with GETDATE(). A computed column also works.

Note: you can't index the view because GETDATE is non-deterministic

CREATE TABLE myTestTable (foo int IDENTITY (1,1), bar varchar(100), ExpiryDate datetime)
GO
DECLARE @now datetime= GETDATE()
INSERT myTestTable (bar, ExpiryDate)
SELECT 'row1', @now - 2.1
UNION ALL SELECT 'row2', @now - 1.6
UNION ALL SELECT 'row3', @now - 1
UNION ALL SELECT 'row4', @now - 0.1
UNION ALL SELECT 'row5', @now + 2.1
UNION ALL SELECT 'row6', @now + 1.6
UNION ALL SELECT 'row7', @now + 1
UNION ALL SELECT 'row8', @now + 0.1
GO

CREATE VIEW MytestTableWithEXPIRED
WITH SCHEMABINDING
AS
SELECT
   foo, bar, ExpiryDate,
   CASE WHEN ExpiryDate < GETDATE()THEN 1 ELSE 0 END AS HasExpiredView
FROM
   dbo.myTestTable
GO
SELECT * FROM MytestTableWithEXPIRED
GO

ALTER TABLE MytestTable ADD
    HasExpiredCol AS CASE WHEN ExpiryDate < GETDATE()THEN 1 ELSE 0 END
GO
SELECT * FROM MytestTable
GO


Indexes and Statistics on Temp tables I've just upgraded our Data warehouse to SQL 2016. I'm seeing some really interesting graphs in the Query Store (I love this feature!). Below is the weirdest example i've seen.
22 plans for the same query.

It's making me consider performance tuning of my ETL process and the pros and cons of temporary tables and how you could influence execution plan behavior.

My ETL process uses a number of stored procedures which use mix of standard and temporary #tables as staging tables. The #tables are typically used once and then dropped. Some are only a few thousand rows. Some are millions. SSMS advises that there are missing indexes, but on smaller tables would they make enough of difference to be worth the effort of adding them? Are better statistics sufficient?

I've just read this Brent Ozar blog post about Statistics on Temp tables, and Paul White's article on Temporary Tables in Stored procedures

It says that statistics are created automatically, when the #table is queried, and then presumably used by the optimizer.

My questions are: Is there much point or benefit in creating an index on a #table. And/or: Is it worth explicitly updating statistics as a step in the stored procedure before using it in queries given they're only used once.

Are the additional steps and overhead worth it? Would it result in significantly better or different execution plans?

There can be benefit in creating indexes on temporary tables, but maybe not for a staging table. It's an "it depends" answer, unfortunately. You will need to test. If you posted the code for how you are interacting with the staging table, we could help determine if any indexes would help. An example of where an index might help is if you were joining the temp table to another table. If you were to index the joined column, there could be performance gains, especially if there are a lot of rows in the temp table.

You probably do not need to update statistics on the temporary tables. It's also an "it depends" answer, though I've never seen an update stats on temp tables in any of the thousands upon thousands of stored procedures I've looked at, nor have I needed to add it to resolve a performance issue.


How to remove Alter, Drop, Create SQL from deployment script created in VS 2015 using SSDT 14.06 I need to remove the below auto-generated deployment script for my SQL project. I am using VS 2015 with SSDT 14.06. Would this be something that can be set in the Advanced Deployment settings under the Drop tab ? This is for an existing Database deployment.

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON
    PRIMARY(NAME = [$(DatabaseName)], FILENAME =
N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
    LOG ON (NAME = [$(DatabaseName)_log], FILENAME =
N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE
SQL_Latin1_General_CP1_CI_AS
GO
USE [$(DatabaseName)];

I have the following items all unchecked for my deployment options in the properties:

-- Deploy database properties
-- Always re-create database
-- Block incremental deployment if data loss might occur
-- DROP objects in target but not in project
-- Do not use ALTER ASSEMBLY statements to update CLR types

I've only ever seen that code get scripted in two situations:

When the "Always re-create database" advanced publish option is checked.

That option can be set in multiple ways depending on how your deployment process works.

Here it is in the user interface, when you do a one-off publish or save a re-usable ".publish.xml" file:

Without using the UI, you can see this in the publish file XML:

<CreateNewDatabase>True</CreateNewDatabase>

Note that the default is "False", so the absence of the element means false as well

You can also pass this as a command line parameter to sqlpackage.exe:

/p: CreateNewDatabase=True

When the TargetDatabase doesn't exist

If the target database doesn't exist, then that is added to the script for safety reasons to just make sure it doesn't fail on creating the new database.

You'll need to double check that these properties in the publish XML point to a valid database that exists:

<TargetDatabaseName>YourDatabaseName</TargetDatabaseName>
<TargetConnectionString>Data Source=...etc...</TargetConnectionString>

And here it is in the UI:

If you could share more details about how you generate the publish script (the step-by-step of what you do, e.g. right-click on the SSDT project in Visual Studio, Publish, etc), we can help track down why that's showing up for you.


Why does my DELETE command require huge amount of temporary run storage? I am trying to perform a cleanup operation on a table using DELETE and I receive the following error:

  Could not allocate space for object 'dbo.SORT temporary run storage:
  140767697436672' in database 'tempdb' because the 'PRIMARY' filegroup
  is full. Create disk space by deleting unneeded files, dropping
  objects in the filegroup, adding additional files to the filegroup, or
  setting autogrowth on for existing files in the filegroup.

Before running the DELETE I have more than 11G of free disk space. When the error is issued, I have next to nothing on that partition. Context information comes below:

1) Problematic query:

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
delete from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)

2) Cardinality for involved tables

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
select count(1) from art.Article    -- 137181
select count(1) from art.Article where PublishDate < @deleteDate    -- 111450
select count(1) from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)      -- 12153045
exec sp_spaceused 'art.ArticleConcept'
-- name             rows       reserved     data        index_size   unused
-- ArticleConcept   14624589   1702000 KB   616488 KB   1084272 KB   1240 KB

3) Indexes

-- index_name   index_description   index_keys
-- IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq   nonclustered located on PRIMARY ArticleId

CREATE NONCLUSTERED INDEX [IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq] ON [art].[ArticleConcept]
(
[ArticleId] ASC
)
INCLUDE (   [LexemId],
[Freq]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

4) Server

Select @@version
-- Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
-- Feb 20 2014 20:04:26
-- Copyright (c) Microsoft Corporation
-- Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

5) Execution plan (estimated)

I understand that I am performing a large DELETE, but I cannot understand why it requires so much space to do it: the whole ArticleConcept table has less than 2GB (reserved space), yet to remove records from it requires more than 11GB.

Question: Why does my DELETE command require huge amount of temporary run storage?

I have removed all secondary indexes and I could perform the DELETE. However, why so much more space is needed to perform the DELETE when having them, looks strange to me.

I am trying to delete 12,153,045 out of 14,624,589 records (quite many). I have not monitored the transaction log, but once I received an error related to it:

  The transaction log for database ... is full due to 'ACTIVE_TRANSACTION'

You need to batch your deletes so that you don't lock the table and fill your transaction log.

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
 DECLARE
    @RC INT = 1;

WHILE (@RC > 0)
BEGIN

  delete top 1000 art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate);

  SET @RC = @@ROWCOUNT

END

You can experiment with larger batch sizes (I usually go 5000-50000) but this is a good place to start. You do have to be careful about lock escalation attempts when choosing batch sizes.

You'll probably also see an improvement by sending your list of ArticleIds to a temp table so that you only have to scan art.Article one time.

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
create table #ArticlesToDelete (ArticleId int primary key not null);
insert into #ArticlesToDelete (ArticleId) select ArticleId from art.Article where PublishDate < @deleteDate;

DECLARE @RC INT = 1;

WHILE (@RC > 0)
BEGIN
  delete top 1000 A from art.ArticleConcept A join #ArticlesToDelete D on A.ArticleId = D.ArticleId;
  SET @RC = @@ROWCOUNT
END
drop table #ArticlesToDelete


Need to migrate SQL Server to MySQL I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the MySQL ODBC driver, and it correctly accesses both databases, but the xml files containing the specifications for type conversion did not exist and the specifications were too limited for me to correctly create them. Does anyone know either how to create the type conversion files or where to get a better tool for transferring this data?

I have two suggestions:

1) I hate bringing up commercial products but there is a $49.00 tools to Migrate MSSQL to MySQL

2) Try MySQL's MSSQL Migration forums for further suggestions

UPDATE 2011-06-03 18:03 EDT

There is an old product that went EOL back in January 2010 called the MySQL Migration Toolkit. If you can get a hold of it, you can use it.

UPDATE 2011-06-03 18:06 EDT

I found the archives !!! Here is the MySQL Migration Toolkit

UPDATE 2011-06-03 18:11 EDT

Here is the MySQL Migration Toolkit Overview

UPDATE 2011-06-03 19:08 EDT

Another commerical product ($29)

UPDATE 2011-06-03 19:30 EDT

Here is a list of Freeware tools that Migrate MSSQL to MySQL.

UPDATE 2011-06-15 17:47 EDT

Get the WhitePaper (PDF) from Oracle on the Guide to Migrating Microsoft SQL Server to MySQL (Still Commercial)

UPDATE 2012-08-21 01:24 EDT

According to this MySQL WebPage, the section MySQL Workbench: Database Migration Wizard claims that the MySQL Workbench has the capability of Migrating DB Objects from SQL Server to MySQL.


Query "all of" across many-to-many relation Imagine a setup of three tables, User, Group, and UserGroup, where UserGroup consists of simple a foreign key to each of User and Group tables.

User
----
id
name

Group
-----
id
name

UserGroup
---------
user_id
group_id

Now, I want to write a query selecting all users that are in all of some specified groups. e.g. Select * from users where the user is part of every one of "group1", "group2", and "group3".

With a Django ORM query, I'd do something like

users = (
    User.objects
    .filter(user_group__group_id=group1.id)
    .filter(user_group__group_id=group2.id)
    .filter(user_group__group_id=group2.id)
)

Which would produce a join for each call to .filter, e.g.

SELECT * FROM users
INNER JOIN user_group g1 ON g1.user_id = id
INNER JOIN user_group g2 ON g2.user_id = id
INNER JOIN user_group g3 ON g3.user_id = id
WHERE g1.group_id = %s
  AND g2.group_id = %s
  AND g3.group_id = %s

This becomes a bit hairy if I were to query a bigger set to match by.

So what is a better way to do this? If I were to ask for "any" rather that "all", if would be a simple matter of

SELECT * FROM users
INNER JOIN user_group g1 ON g1.user_id = id
WHERE g1.group_id in %s

But that is not what I need.

A small note: My specific environment is on Postgres, so no fancy MSSql thing will help me here. Preferably, the answer should be general enough to use in any SQL flavour.

You can do it with "fancy Postgres" features - much easier than "fancy MS SQL features" ;)

You can aggregate all the group IDs into an array and then compare that.

If with "all of" you mean those users that are assigned to exactly those groups, you can use something like this:

SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order by ug.group_id) = array[1,2,3];

Note that the = operator for arrays depends on the order [1,2,3] is a different array than [3,1,2] that's why array_agg() uses an order by and the values in the array are sorted as well.

If with "all of" you mean those users that are assigned to at least those groups (but could be assigned to more) then you can use a simple "contains" operator:

SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order) @> array[1,2,3];

The "contains" operator @ does not depend on the order of the elements.

If you need to return the complete row from the users table, you can do the aggregation in a derived table and join to that:

SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group
    GROUP BY user_id
    HAVING array_agg(group_id) @> array[1,2,3]
  ) ug on ug.user_id = u.id

The second query can also be done using standard SQL:

SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group
    WHERE group_id in (1,2,3)
    GROUP BY user_id
    HAVING count(distinct group_id) = 3
  ) ug on ug.user_id = u.id;

This solution has the disadvantages that you need to synchronize the values for the IN list and the count(..) = 3 expressions when you change the list of IDs


How do I add a user that has read access to only ONE database? I have two databases admin and testdb ; Added a user Alice in the test database

db.addUser( { user: "Alice",pwd: "Moon1234",roles: [ "readWrite" ] } );

But when I login using the user Alice, I can run "use admin" and run all read queries on that database.

Then I tried doing this:

db.addUser( { user: "Alice",userSource: "testdb",roles: [ "readWrite" ] } )

I get this error:

uncaught exception: couldn't add user: 'testdb' is not a valid value for the userSource field in testdb.system.users entries

Mongodb documentation is not very clear about it.

Basically my use case is that Alice should be able to read write ONLY in the "testdb"

I don't know MongoDB too well and I assume you have you seen the information set out on the MongoDB System Users page as there would seem to be one or two differences across different DB mangement procedures.

As a suggestion have you set the database you wish to work on/from by adding the 'use ....' before the command. It would seem that you might try:-

use admin
db.addUser( { user: "Alice",userSource: "testdb",roles: [ "readWrite" ] } )

and see if that makes a difference.