Returning setof derived from for loop in plpgsql I have the below function.  It is intended to loop through every schema and return all user IDs from a table in that schema.  The schemas have the format: users[0-100].

$body$
declare
  r varchar;
  sql text := 'select userid from $1.users';
begin
  for r in select schema_name from information_schema.schemata where schema_name ~ '^users'
  loop
    return query select format('select userid from %s.users', r);
  end loop;
  return;
end
$body$

I have tried a few different ways, but I haven't been able to return a set.  Where am I going wrong?

This code:

return query select format('select userid from %s.users', r);

returns the text of queries to the caller instead of executing them and returning their results. You want a three-steps operation: build-execute-return.

These steps are easier to figure out if the code is broken into two lines (assuming query_text is a text variable declared above):

query_text := format('select userid from %I.users', r);
return query execute query_text;

Note: I've also changed %s to %I, the specifier dedicated to identifiers. If the schema identifier required quoting, it would happen automatically, whereas with %s the query would fail.


Does Postgres preserve insertion order of records? For example when I'm using query which returns record ids

INSERT INTO projects(name)
VALUES (name1), (name2), (name3) returning id;

Which produce output:

1
2
3

Will this ids point to corresponding inserted values?

1 -> name1
2 -> name2
3 -> name3

The answer for this simple case is Yes. Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial type, values from the underlying sequence will be fetched in that order.

But this is an implementation detail and there are no guarantees. In particular, the order is not necessarily maintained in more complex queries with WHERE conditions or joins.

You might also get gaps or other rows mixed in if you have concurrent transactions writing to the same table at the same time. Unlikely, but possible.

There is no "natural" order in a database table. While the physical order of rows (which is reflected in the system column ctid) will correspond to their inserted order initially, that may change any time. UPDATE, DELETE, VACUUM and other commands can change the physical order of rows. But the generated values for id are stable and not in any way connected to that, of course.


calculating the total number of rows in a table - fails when it is a heap - is there any fix? I have a procedure that I use to find out how big is a table.

select * from sys.objects
where name = 'WMS_SLALoad'
-- to get the object_id of my table
-- in this case it is 2041058307

SELECT o.object_id,
       s_name=s.name,
       o.name,
       ddps.row_count
       ,[Statistics_Updated]=STATS_DATE(I.OBJECT_ID,I.INDEX_ID)
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id
WHERE i.index_id < 2
  and o.object_id = 2041058307
go

but when I select from that table, there is always 7 rows less.
my count always tell me the table has 7 rows more that it actually has.

one thing I thought might be the case, is this is actually not a table, it is a heap, because it does not have any indexes
as you can see on the script below.

CREATE TABLE [dbo].[WMS_SLALoad](
    [MessageCycleNumber] [int] NOT NULL,
    [Timestamp] [varchar](50) NOT NULL,
    [Warehouse] [varchar](20) NOT NULL,
    [ProdID] [varchar](8) NOT NULL,
    [QuantityChange] [int] NOT NULL,
    [Sign] [char](1) NOT NULL,
    [Status] [varchar](20) NOT NULL,
    [ReasonCode] [char](2) NULL,
    [InUse] [int] NULL CONSTRAINT [DF_WMS_SLALoad_InUse]  DEFAULT (0)
) ON [PRIMARY]

how can I make sure my script gives me the correct number of rows, whether the object is a table or a heap?

make use of DBCC UPDATEUSAGE
as mentioned in the comments by  Dan Guzman
has produced the following results:

DBCC UPDATEUSAGE(N'WMSData',N'dbo.WMS_SLALoad') WITH COUNT_ROWS

After that the results of my original select was accurate enough for my purposes.


Will SQL Server "int" datatype reliably truncate (and not round) decimal values when they are input? I have a user with software that sometimes sends back a UNIT's ID as an integer(ex. 1000), and sometimes with a small decimal value appended (ex. 1000.001).  The software automatically generates MSSQL (2016 SP1) tables with "real" datatypes, and to "top it off" makes this UNIT columns part of a Primary Key.  This causes issues on the data input side and the reporting side.

I realize this is really a "bug" with the software, but I'm trying to be helpful on the DB side.  I have proven in a Sandbox region that I can change the "real" datatype to an "int", that this does not cause data loss with existing data, and every input scenario shows that the decimal values of any input will be truncated in the "int" column(not rounded).

My questions are, "Will SQL Server always reliably truncate decimal values when input into an "int" datatype (We do not have a way to dig into the Packaged App and force it to ROUND)?  Are there any scenarios where this will error, or cause issues?"  I can't find this documentation anywhere.

The Microsoft Docs page for the float and real data types, says this:

Values of float are truncated when they are converted to any integer type.

In addition the CAST and CONVERT (Transact-SQL) page says:

When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.

I'd prefer to get the source software modified to not return floating point data, but short of that, if the data is not used by some process that would be life-threatening, then it looks like an implicit conversion to int should be safe.
I qualified it with "non-life-threatening" because it's possible there is some situation where the implicit conversion does not operate the way it is documented.  Take for instance, the details presented in the "Caution" section of the int Docs page.


mongodump: fact.bson: input/output error We are trying to export 1.5TB database into mongodb. But its fail after 100gb and giving us below error. As we notice root mount point got 100% space. We are using below command:

mongodump -u admin -p xx_admin_db -d xx --host xx.xx.xx.xx --authenticationDatabase admin

2018-06-28T12:20:30.687+0000    [........................]  xx1.fact  14679565/468546715  (3.1%)
2018-06-28T12:20:31.192+0000    [........................]  xx1.fact  14680477/468546715  (3.1%)
2018-06-28T12:20:31.195+0000    Failed: error writing to file: write dump/xx/xxx.bson: input/output error

Use the --out/-o parameter to indicate a directory on a filesystem where you have enough room.


Relationship Delete and Insert Rule This below Image is for My messaging database :

This is database for server side of asp site and client side was wrote by android app.
So my Question is how to set ruls for my relationship in this database?
Another Question is what are the problems of this design?

When you define the foreign keys you can include ON DELETE CASCADE to enforce the delete rule.

As for problems in the design. One immediate issue I see is that you've defined the Identity columns as "Id". This design pattern almost always leads to trouble. What you want to use instead is an ID column name that includes the table name. For example, Id in the User table should be named UserID.


I need to audit all actions for a user on all databases - not sure best way I have a user on a SQL Server that is used for executing SSRS queries. This user was made a sysadmin before I started at this job. I want to remove the sysadmin permission, but not break any reports. I do not know what DDL/DML the user is actually performing.

With that said, I want to run an audit, which I have done before, but since the auditing is so fine grained, I can't wrap my head around what all I should audit to catch the user actions on all databases.

Can anyone shed some light?

Thanks in advance!

Colleen Morrow's got some great resources regarding SQL Server Audit, including deployable code via PowerShell.  Her articles are well worth a read and I've deployed her solutions in my environment to great fanfare:

http://colleenmorrow.com/tag/auditing/


“GROUP BY” in ranges? Suppose I have a table with a numeric column (lets call it "score").

I'd like to generate a table of counts, that shows how many times scores appeared in each range.

For example:

score range  | number of occurrences
-------------------------------------
   0-9       |        11
  10-19      |        14
  20-29      |         3
   ...       |       ...

In the example I have given explicit ranges. But  don't know what range could be possible.

Is there an easy way to set this up?  What do you recommend?

SUGGESTED QUERY

SELECT
    CONCAT(A.ndx,' - ',A.ndx+9) "score range",
    IFNULL(B.rowcount ,0) "number of occurrences"
FROM
(
    SELECT 0 ndx UNION SELECT 10 UNION SELECT 20 UNION
    SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60
    UNION SELECT 70 UNION SELECT 80 UNION SELECT 90
) A
LEFT JOIN
(
    SELECT ndx,COUNT(1) rowcount FROM
    (SELECT FLOOR(score/10)*10 ndx FROM yoshi_scores) AA
    GROUP BY ndx
) B USING (ndx);

SAMPLE DATA

use test
DROP TABLE IF EXISTS yoshi_scores;
CREATE TABLE yoshi_scores
(id int not null auto_increment,
score int not null,primary key (id), key (score));
INSERT INTO yoshi_scores (score) VALUES
(97),(74),(22),(98),(65),(62),(47),(64),(82),( 8),(60),(12),
(27),(14),(13),(28),(60),(12),(27),(34),(32),(89),(15),( 2);

Data would look like this

mysql> SELECT * FROM yoshi_scores ORDER BY id;
+----+-------+
| id | score |
+----+-------+
|  1 |    97 |
|  2 |    74 |
|  3 |    22 |
|  4 |    98 |
|  5 |    65 |
|  6 |    62 |
|  7 |    47 |
|  8 |    64 |
|  9 |    82 |
| 10 |     8 |
| 11 |    60 |
| 12 |    12 |
| 13 |    27 |
| 14 |    14 |
| 15 |    13 |
| 16 |    28 |
| 17 |    60 |
| 18 |    12 |
| 19 |    27 |
| 20 |    34 |
| 21 |    32 |
| 22 |    89 |
| 23 |    15 |
| 24 |     2 |
+----+-------+
24 rows in set (0.00 sec)

SUGGESTED QUERY EXECUTED

mysql> SELECT
    ->     CONCAT(A.ndx,' - ',A.ndx+9) "score range",
    ->     IFNULL(B.rowcount ,0) "number of occurrences"
    -> FROM
    -> (
    ->     SELECT 0 ndx UNION SELECT 10 UNION SELECT 20 UNION
    ->     SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60
    ->     UNION SELECT 70 UNION SELECT 80 UNION SELECT 90
    -> ) A
    -> LEFT JOIN
    -> (
    ->     SELECT ndx,COUNT(1) rowcount FROM
    ->     (SELECT FLOOR(score/10)*10 ndx FROM yoshi_scores) AA
    ->     GROUP BY ndx
    -> ) B USING (ndx);
+-------------+-----------------------+
| score range | number of occurrences |
+-------------+-----------------------+
| 0 - 9       |                     2 |
| 10 - 19     |                     5 |
| 20 - 29     |                     4 |
| 30 - 39     |                     2 |
| 40 - 49     |                     1 |
| 50 - 59     |                     0 |
| 60 - 69     |                     5 |
| 70 - 79     |                     1 |
| 80 - 89     |                     2 |
| 90 - 99     |                     2 |
+-------------+-----------------------+
10 rows in set (0.00 sec)

mysql>

Why is the Query designed this way ??? Look at the first subquery

(
    SELECT 0 ndx UNION SELECT 10 UNION SELECT 20 UNION
    SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60
    UNION SELECT 70 UNION SELECT 80 UNION SELECT 90
) A

I performed a LEFT JOIN of this to the counts for a reason. Notice that the dataset has nothing in the 50 - 59 range. Its count would not show up in the second query:

mysql> SELECT ndx,COUNT(1) rowcount FROM
    -> (SELECT FLOOR(score/10)*10 ndx FROM yoshi_scores) AA
    -> GROUP BY ndx;
+------+----------+
| ndx  | rowcount |
+------+----------+
|    0 |        2 |
|   10 |        5 |
|   20 |        4 |
|   30 |        2 |
|   40 |        1 |
|   60 |        5 |
|   70 |        1 |
|   80 |        2 |
|   90 |        2 |
+------+----------+
9 rows in set (0.00 sec)

mysql>

I am sure you wanted the range 50 - 59 to show up, so the query is designd to catch all ranges. Any missing range is essentially defaulted to zero.

GIVE IT A TRY !!!

CAVEAT: Unfortunately, the first subquery requires you to hardcode it. It will be the only hardcoding required to make it work.


first and last record in one query i want to select first and last score from such data

WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as score
UNION ALL
SELECT 2 user_id, '2019-05-15'::date AS created_at, 7 as score
UNION ALL
SELECT 3 user_id, '2019-05-13'::date AS created_at, 6 as score
),
first_score AS (
    SELECT DISTINCT ON (user_id)
        created_at, score, user_id
    FROM t
    ORDER BY user_id, created_at ASC
),
last_score AS (
    SELECT DISTINCT ON (user_id)
        created_at, score, user_id
    FROM t
    ORDER BY user_id, created_at DESC
)

select
    md5(u.user_id::varchar) user_id,
    u.created_at signup_date,
    fhs.created_at first_score_created_at,
    fhs.score first_score,
    lhs.created_at last_score_created_at,
    lhs.score last_score
from t as u
    INNER JOIN first_score fhs ON fhs.user_id = u.user_id
    INNER JOIN last_score lhs ON lhs.user_id = u.user_id;

my question: is exists a way to make one subquery to join the first_score and last_score in single query?

WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as score
UNION ALL
SELECT 2 user_id, '2019-05-15'::date AS created_at, 7 as score
UNION ALL
SELECT 3 user_id, '2019-05-13'::date AS created_at, 6 as score
)
SELECT
    md5(user_id::varchar) user_id,
    created_at signup_date,
    MIN(created_at) OVER (PARTITION BY user_id) first_score_created_at,
    FIRST_VALUE(score) OVER (PARTITION BY user_id ORDER BY created_at ASC) first_score,
    MAX(created_at) OVER (PARTITION BY user_id) last_score_created_at,
    FIRST_VALUE(score) OVER (PARTITION BY user_id ORDER BY created_at DESC) last_score
FROM t


DDL for PostgreSQL for DB2 tables (migration) How would I create the DDL for PostgreSQL for the following DB2 tables?
It should be a one-to-one migration, so I would like to change only as little as possible at the schema (I have difficulties with the BLOB/CLOB datatypes and the default values).

CREATE TABLE table1
(
   field_1  DECIMAL(15)   NOT NULL,
   field_2  ROWID         NOT NULL WITH DEFAULT,
   field_3  DECIMAL(15)   NOT NULL WITH DEFAULT,
   field_4  BLOB          NOT NULL WITH DEFAULT,
   field_5  TIMESTAMP     NOT NULL WITH DEFAULT,
   field_6  INTEGER       NOT NULL WITH DEFAULT,
   field_7  VARCHAR(64)   NOT NULL WITH DEFAULT,
   field_8  SMALLINT      NOT NULL WITH DEFAULT,
   field_9  CHAR(60)      NOT NULL WITH DEFAULT,
   field_10 DATE          NOT NULL WITH DEFAULT,
   field_11 DECIMAL(12,3) NOT NULL WITH DEFAULT,
   field_12 TIME          NOT NULL WITH DEFAULT,
   field_13 BIGINT        NOT NULL WITH DEFAULT
);

Second table:

CREATE TABLE table2
(
   field_1  DECIMAL(15)     NOT NULL,
   field_2  ROWID           NOT NULL,
   field_3  VARCHAR(2500)   NOT NULL,
   field_4  CLOB
);

BLOB should be translated to bytea in Postgres and CLOB to text.

DECIMAL(15) is probably better done as bigint and char() should not be used at all. I would replace that with varchar(30)

I don't know what rowid is in DB2.

The with default option is invalid for Postgres. If you want a default value you need to specify it explicitly, e.g.:

field_6  INTEGER       NOT NULL DEFAULT 42,


Why is the Read-Only Routing not functioning in this Availability Group? First off, my apologies if this has already been answered elsewhere. I have not been able to find any articles or threads describing my exact situation, but no one's Google-fu is perfect.

With that out of the way, here's my situation: I have a SQL Server 2017 (14.0.1000.169) Enterprise environment with three AG replicas (one primary, one secondary with synchronous commit, and one secondary with asynchronous commit), and whenever I try to connect to the Listener using ApplicationIntent=ReadOnly and specifying an AG database, it gives me the following error:

  A network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections.

It connects with no issues if I don't specify the ApplicationIntent flag, or if I don't specify a database. Here are the things I have already checked:

Listener is configured, and can be connected to normally.
Read-Only Routing URLs are configured for each replica, using the FQDN and TCP port for that instance (ran the script provided here to verify that I have those correct).
Read-Only Routing Lists are configured for each replica.
The sync secondary is in Synchronized status, and the async secondary is in Synchronizing status.
The secondary replicas are set to allow all connections (ALLOW_CONNECTIONS = ALL) and can be queried directly using their own instance names.

Each SQL Server instance that hosts a replica is using a different TCP port from the others for both its Endpoint URL and its Read-Only Routing URL (six different ports between the three instances), and the Listener is listening on a different port from all of those.  I don't think this has anything to do with the problem, but I'm mentioning it for the sake of thoroughness.

Like I said above, I haven't found anything useful for my exact situation, since all the results I've been able to find have just been people who haven't set up their routing lists or whatever. Any ideas on next steps here or anything I might be missing? Please let me know if you need any additional info about the environment.

The human element was the problem, as usual. Turns out I had accidentally used the primary replica's server name in the Read-Only Routing URLs for all three replicas. There being only one character difference between each name made it difficult to spot, even though I'd looked at them dozens of times.

Thanks to @SeanGallardy for prompting me to pull the URLs, which caused me to notice the problem.

Thanks to @clifton_h for also making a good-faith effort to help.  Y'all good people!


Can I rely on this SQL shortcut to save a query? Consider a table with a status field that holds one of two values:

| status | set('active','default') | YES  |  | active |

I need to know how many records are in the table, and additionally how many have the status set to active. I notice that SUM returns 1 for each record set to active and 2 for each record set to default:

mysql> select count(*), sum(status) from users;
+----------+-------------+
| count(*) | sum(status) |
+----------+-------------+
|        3 |           4 |
+----------+-------------+
1 row in set (0.00 sec)

mysql> select status from users;
+---------+
| status  |
+---------+
| default |
| active  |
| active  |
+---------+
3 rows in set (0.00 sec)

Can I rely on this behaviour? Is this a bad practice?

Answer to self!

A better way to perform the query in the OP in a single query would be to explicitly group by status:

mysql> SELECT COUNT(*) AS total, status FROM users GROUP BY status;
+-------+---------+
| total | status  |
+-------+---------+
|     2 | active  |
|     1 | default |
+-------+---------+
2 rows in set (0.00 sec)

In addition to being clearer as to intention, this solution also works for the case in which status has more than two possible values.


How to minimize the writes needed to update a counter field multiple times I need to minimize the number of queries that are executed just for updating a counter field.

One of my example counter fields is raised by 1 everytime a page is visited, I was wondering what could be the best method to update it with just one occasionally query, maybe a can raise it by 100 with 1 query instead of raising it 100 times by 1.

How could I do that?

I think I need a place where to store by how much I need to raise that specific counter field, and this place should be the fastest possible, I thought about memcached but I think there will be problems with concurrent accesses to the same resource.

MyISAM is the worst offender for updating individual rows on concurrency. First thing you want to do is use InnoDB to be able to execute UPDATEs in parallel. InnoDB may have lower insertion rate, but that can be solved by reducing its durability setting (innodb_flush_log_at_trx_commit = 2). That, combined with a large enough buffer pool, will make most of your updates on-memory only.

If that is still too slow for a single row, memcached is certainly one solution to buffer queries, and it is even integrated into MySQL 5.6. Operations in memcached are guaranteed to be done atomically, so on that side that wouldn't be a concurrency problem. However, reading, adding plus 1 on client code, and updating the result is not guaranteed to be atomic, so you should control concurrency on application side (syncronization/locking)- Java has very good support for that, PHP doesn't, although it can be done. That can be too much overhead if the cache server is over the network.

One trick to avoid concurrency problems (works for DB tables and any other system) is to do partitioning: instead of having one counter, having several per value to prevent blocking problems on write. When calculating the final number, just add up the different rows into one.


Is there a limit to the number of database connections from a single machine in PostgreSQL? We are planning on setting up multiple front ends behind a load balancer.

I think our database can handle the load because I'm currently measuring a database TPS of about 450  for approximately 42000 web requests (93000 transactions).

Our requirements are 278 messages/second average for the front end. I can only get around 92 non-idle queries running in the database, at a given time, and I was wondering if there's some limit to the number of connections from a single machine.

I have tried upping various connection pools and thread pools.  I was able to up the query count from 33 to 92, but I'm unable to get higher.  I am not even sure how many queries from a single machine are reasonable.  I read connections should be (2 * core_count) + effective_spindle_count.

I have been getting errors from the front end stating that it failed in getting a connection ("The connection attempt failed"), but I don't know yet if this is a failure from the front end (by hitting a limit) or the database (a configuration on the database).

I believe the max # of connections for the database is 2000 last I checked.

Is there another limit to the number of connections the database will accept from one machine?

The database is running Linux 2.6.18 on x64.

I guess I'm also wondering if the number of connections is limiting.  I don't think it is, but we did get the connection attempt failures.  The front end is running at 300% CPU (8 processors) and 25% memory. I would think we could get more bang out of both our front end and our database, but with two test servers, we normally get 250 tests/second (maxed out at 254/s for one server and 334/sec for the other ).

You probably don't want more than 100 or so concurrent queries running on the DB. Use a connection pooler like pgbouncer if you need one and your app doesn't have one built-in.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Setting a high max_connections won't make things go faster. It'll make things go slower.


CREATE DATABASE ... WITH TEMPLATE seems to lose relations in PostgreSQL I'm trying to Create a copy of a database in postgresql per the SO answer from 2009, but running into problems.

In Postgres 9.3.9, this creates a database with no relations (the gcis db exists and has tables and data):

postgres=# CREATE DATABASE gcis_rollback WITH TEMPLATE gcis OWNER postgres;
CREATE DATABASE
postgres=# \c gcis_rollback
You are now connected to database "gcis_rollback" as user "postgres".
gcis_rollback=# \d
No relations found.

I get the same using the commandline createdb:

~$ createdb -O postgres -T gcis gcis_rollback2
~$ psql gcis_rollback2
psql (9.3.9)
Type "help" for help.

gcis_rollback2=# \d
No relations found.

Why don't I see a full copy of this DB?

Background - This is a dev server, where I can take down the connections to make a copy.  What I want is just a local copy for ease of rollback purposes while developing/testing DB schema changes using the Perl framework Module::Build::Database to build a patch.

Additional info:

gcis=# \l+ gcis
                                               List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description
------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------
 gcis | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 35 MB | pg_default |

gcis=# \d
                        List of relations
    Schema     |             Name             |   Type   | Owner
---------------+------------------------------+----------+--------
 gcis_metadata | _report_editor               | table    | ubuntu
...
(57 rows)

Thanks to @a_horse_with_no_name for leading me to this answer.

The relations appear to be missing because Postgres does not copy the search_path to the new database.  If you explicitly look in the specific schema, all relations are there:

\dt <schema>.*

So, the search_path needs to be explicitly set on the new database:

ALTER DATABASE gcis_rollback2 SET SEARCH_PATH gcis_metadata, public

@Erwin Brandstetter also provides more information on viewing the search_path for roles/dbs.


Export synonyms content to tables I have a distant database to replicate having huge data but I have access only to synonyms and I would like to use Oracle export/import tool to import these data into tables of local database.

What is the best solution to do this ?

To sum up:

Distant db :Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

Synoymns (through dblink) :

HUGE_TABLE :  about 10M rows
HUGE_TABLE_WITH_BLOBS : about 1M rows, 10GB

Local db: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

Tables:

HUGE_TABLE : empty (to fill)
HUGE_TABLE_WITH_BLOBS : empty (to fill)

I think the most efficient way in your case will be:

--uncomment if you are allowed do this: drop table HUGE_TABLE;
--uncomment if you are allowed do this: drop table HUGE_TABLE_WITH_BLOBS;
create table HUGE_TABLE NOLOGGING PARALLEL 4 as select * from HUGE_TABLE@Distantdb;
create table HUGE_TABLE_WITH_BLOBS NOLOGGING PARALLEL 4 as select * from HUGE_TABLE_WITH_BLOBS@Distantdb;

You can omit: "NOLOGGING PARALLEL 4" if normal mode will be enough fast.
Remember about creating constraint and indexes on newly created tables, default only  NOT NULL constraints will be copied.


No Primary Key in some sql server tables I have downloaded WorldWideImporters Database as a standard db (Microsoft Sample Database for SQL Server). In this db, some of the tables don't have primary keys but clustered indexes can be seen. Can anybody tell me why some tables are working without PK? What is the reason?

Any comments would be appreciated!

To see which tables have no primary key, run the following script:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
EXCEPT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'

You will get the following list:

TABLE_NAME
----------
BuyingGroups_Archive
Cities_Archive
ColdRoomTemperatures_Archive
Colors_Archive
Countries_Archive
CustomerCategories_Archive
Customers_Archive
DeliveryMethods_Archive
PackageTypes_Archive
PaymentMethods_Archive
People_Archive
StateProvinces_Archive
StockGroups_Archive
StockItems_Archive
SupplierCategories_Archive
Suppliers_Archive
TransactionTypes_Archive

Now it's obvious that the Archive tables have no primary keys.
I guess it was a design choice, you can argue that a table without a primary key is not a relation and of course has no guarantee of being able to identify every row. But, I can see how someone would think that since the only source of data comes from the non archive table that have PKs, so there is less risk.
IMHO that is wrong, but not uncommon.

Since the project is on GitHub, you can contribute PKs to the schema.

HTH

UPDATE:
Martin correctly commented that these are in fact the temporal history tables, and therefore can't have PKs or other constraints as they are system managed.


Mysql 8 accidentally lost ROOT user all privileges in localhost wampserver 3.2 Mysql 8 root user all privileges lost.I changed some permissions from phpmyadmin.

. After that all tables disappeared.
I found this answer Lost All Privileges in MYSQL.but did'nt help me.

I am using Wampserver 3.2 on windows 7 and mysql version is 8.0.18
Now showing only information_schema table.
I login as root user and run all below commands.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql;

mysql> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table '
user';

mysql> SHOW GRANTS;
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `root`@`localhost`                     |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+

  How to revoke all privileges for 'ROOT' user

Please help

Thank god,Finally it worked out.
first I created a file named as mysql-init.txt in c drive.The file contains

update mysql.user
set Select_priv='Y',
    Insert_priv='Y',
    Update_priv='Y',
    Delete_priv='Y',
    Create_priv='Y',
    Drop_priv='Y',
    Reload_priv='Y',
    Shutdown_priv='Y',
    Process_priv='Y',
    File_priv='Y',
    Grant_priv='Y',
    References_priv='Y',
    Index_priv='Y',
    Alter_priv='Y',
    Show_db_priv='Y',
    Super_priv='Y',
    Create_tmp_table_priv='Y',
    Lock_tables_priv='Y',
    Execute_priv='Y',
    Repl_slave_priv='Y',
    Repl_client_priv='Y',
    Create_view_priv='Y',
    Show_view_priv='Y',
    Create_routine_priv='Y',
    Alter_routine_priv='Y',
    Create_user_priv='Y',
    Event_priv='Y',
    Trigger_priv='Y'
where User='root';

Then I run this command.

D:\wamp3\bin\mysql\mysql8.0.18\bin>mysqld --init-file=c:\\mysql-init.txt --conso
le

Then login as root user.Now all database showing up.


Column name or number of supplied values doesn not match table definition I have created three tables using

USE UserDecomAudit
GO
CREATE TABLE ADUserData
(
ADUserID int NOT NULL PRIMARY KEY,
Name varchar(50),
SamAccountName varchar(10) NOT NULL,
DistinguishedName varchar(200) NOT NULL,
HomeDirectory varchar(100),
OfficePhone varchar(20),
Extension varchar(4),
Manager varchar(50),
Title varchar(50),
Department varchar(50),
Company varchar(20),
DateOfAccountDecommissioning DATETIME NOT NULL
)

USE UserDecomAudit
GO
CREATE TABLE CSUserData
(
CSUserID int NOT NULL PRIMARY KEY,
ADUserID int NOT NULL,
SIPAddress varchar(50),
LineURI varchar(20),
EnterpriseVoiceEnabled varchar(3),
ExternalAccessPolicy varchar(50),
PersistentChatPolicy varchar(50)
)

USE UserDecomAudit
GO
CREATE TABLE ADGroups
(
GroupID int NOT NULL PRIMARY KEY,
ADUserID int NOT NULL,
ADGroup varchar (200)
)

And am trying to create a stored procedure using

USE UserDecomAudit
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.LogUserDetails
-- Add the parameters for the stored procedure here
@Name varchar(50)= null,
@SamAccountName varchar(10)= null,
@DistinguishedName varchar(200)= null,
@HomeDirectory varchar(100)= null,
@OfficePhone varchar(20)= null,
@Extension varchar(4)= null,
@Manager varchar(50)= null,
@Title varchar(50)= null,
@Department varchar(50)= null,
@Company varchar(20)= null,
@SIPAddress varchar(50)= null,
@LineURI varchar(20)= null,
@EnterpriseVoiceEnabled varchar(3)= null,
@ExternalAccessPolicy varchar(50)= null,
@PersistentChatPolicy varchar(50)= null,
@ADGroup1 varchar (200)= null,
@ADGroup2 varchar (200)= null,
@ADGroup3 varchar (200)= null,
@ADGroup4 varchar (200)= null,
@ADGroup5 varchar (200)= null
AS
BEGIN
DECLARE @ID INT

INSERT INTO dbo.ADUserData (Name,SamAccountName,Distinguishedname,HomeDirectory,OfficePhone,Extension,Manager,Title,Department,DateOfAccountDecommissioning)
VALUES (@Name,@SamAccountName,@Distinguishedname,@HomeDirectory,@OfficePhone,@Extension,@Manager,@Title,@Department,getdate())

SET @ID = cast(scope_identity() AS INT);

INSERT INTO dbo.CSUserData (ADUserID,SIPAddress,LineURI,EnterpriseVoiceEnabled,ExternalAccessPolicy,PersistentChatPolicy)
VALUES (@ID,@SIPAddress,@LineURI,@EnterpriseVoiceEnabled,@ExternalAccessPolicy,@PersistentChatPolicy)

WITH UserADGroups AS
(
    SELECT @ADGroup1 AS ADGroup UNION all
    SELECT @ADGroup2 UNION all
    SELECT @ADGroup3 UNION all
    SELECT @ADGroup4 UNION all
    SELECT @ADGroup5

)
INSERT INTO dbo.ADGroups
SELECT @ID, ADGroup
FROM UserADGroups
WHERE ADGroup IS NOT NULL

END
GO

But I'm receiving an error saying column name or number of supplied values does not match the definition. I'm not sure what I've done wrong

dbo.ADGroups requieres 3 columns/values:

GroupID int NOT NULL PRIMARY KEY,
ADUserID int NOT NULL,
ADGroup varchar (200)

But your query only selects 2 values:

@ID
ADGroup

And SQL Server does not know which one is missing and what to do with the other 2.

You can add a third value for GroupId but it is a lot better to add the column names you are using after the table name when you insert something into a table:

INSERT INTO dbo.ADGroups(ADUserID, ADGroup)
SELECT @ID, ADGroup
FROM UserADGroups
WHERE ADGroup IS NOT NULL

You should always use this syntax with a list of column name: insert into table(colA, colB, ...). It will allow SQL Server to know which value belongs to which column.

Since GroupId cannot be null, it will fail as well. You then have 2 option:

Supply a valid GroupId value
Use Identity GroupID int identity(0, 1) NOT NULL PRIMARY KEY


Why would SET ARITHABORT ON dramatically speed up a query? The query is a single select containing a lot of grouping levels and aggragate operations.
With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this behavior on SQL Server 2000 and 2008.

A little dated, but for anyone ending up here with a similar problem...

I had the same problem. For me it turned out to be parameter sniffing, which at first I didn't understand enough to care about. I added a 'set arithabort on' which fixed the problem but then it came back. Then I read:

http://www.sommarskog.se/query-plan-mysteries.html

It cleared -everything- up. Because I was using Linq to SQL and had limited options to fix the issue, I ended up using a query plan guide (see end of link) to force the query plan I wanted.


How can I handle conditional fields? I am in the process of designing a database for my company to assist in asset management.  I'm not a database administrator, but I had some previous course experience with databases. Our assets are used in the field at various job sites or in a warehouse or vendor facility.  My question relates to best practices for handling the case where a table can have one of two fields completed.  In my case, the asset can be at a shop or at a job.

The way I am considering handling this is adding a flag field that indicates whether the asset is on a job.  If true, then the JOB_ID column should have a value and the SHOP_ID should be null.  If false, the opposite should be the case.  Those IDs will reference the appropriate JOB and SHOP tables.

My question is, am I approaching this from the right direction?  Are there best practices for these cases?  I'm assuming tracking inventory is far from a novel problem.

Here is the schema for the ASSET table with some columns excluded for brevity's sake: ASSET(ASSET_ID, IS_ON_JOB, JOB_ID, SHOP_ID)

Your approach is reasonable.  Put a check constraint on the ASSET table to ensure exactly one of your foreign keys is ever set at any point in time.

The flag is redundant since the same information is captured by which of the foreign keys is NULL.  Keeping it just adds maintenance overhead to the application.  Eventually it will become inconsistent and then you'll have a mess to sort out.

An alternative design is to combine some of the information from SHOP and JOB into a LOCATION table.  The schema then becomes

ASSET(ASSET_ID, LOCATION_ID)
LOCATION(LOCATION_ID, NAME)
JOB(JOB_ID, LOCATION_ID)
SHOP(SHOP_ID, LOCATION_ID)

Put one row in LOCATION for each "job site or .. warehouse or vendor facility," as you say in your question.


Check for overlaps for several arrays in one query I have such table

create table tbl (
    id serial primary key,
    arr text[]
);

With such data

INSERT INTO tbl(arr) VALUES ('{dog, mouse}'), ('{fish, cat, rat}');

If my input is a single array - {dog} - everything is simple:

select id from tbl where arr && '{dog}'

| Id |
|----|
| 1  |

But what I need is the ability to pass several arrays as an input. For example with input {cat}, {dog} I expect output to be:

| Id |
|----|
| 2  |
| 1  |

And even more how can I with input {cat}, {bird}, {dog} receive:

| Id   |
|------|
| 2    |
| null |
| 1    |

Looping through the input array and make a query for each its item is not an option, I want to fire as few requests as possible

Here's the corresponding sqlfiddle - http://sqlfiddle.com/#!15/5740d/2

I suppose you want something like this:

with param (p) as
  ( values ('{cat}'),
           ('{bird}'),
           ('{dog}')
  )
select *
from param left join tbl
     on param.p::text[] && tbl.arr ;

Result:

   p    | id |      arr
--------+----+----------------
 {cat}  |  2 | {fish,cat,rat}
 {bird} |    |
 {dog}  |  1 | {dog,mouse}
(3 rows)


How to set mariadb read_write for some specific tables while starting as read_only=1 I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But as I use my slave db as a stage server of my website, I need some tables like cache_* writable to make my stage server work perfectly.

Can you please help me if it is possible or not ? If not then is there any workaround for it ?

Plan A:  Turn off read_only and hope that no one abuses it (accidentally or deliberately).

Plan B:  Run the 'cache' stuff as some user with SUPER so that it can bypass read_only.  This seems more secure than Plan A, but opens up other risks.


Spatial Index on an InnoDB table I have a database which is in InnoDB, because I wanted consistency (cascading delete).

Now, I want to use spatial indexes for selecting rows ordered by geometric distance.

However, spatial indexes require MyISAM. What is the best thing to do?

The best I could think of is to put the location in a separate MyISAM table and join the InnoDB table with it? (Note that I have removed the spatial magic from the statement to simplify things.)

select * from inno join isam using (id) order by isam.distance

Other solutions?

Yes, that's the best I think. You have the benefit of both InnoDB (referential integrity) and MyISAM (spatial indexes.)

The only technical difficulty is to ensure that the MyISAM table is synchronized with the InnoDB one. This can be done either with triggers or by stored procedures (so every operation on the InnoDB table is done through a procedure.)

Another "benefit" is that since the coordinate values are duplicated at both tables, you can skip backing up the MyISAM table (or backing up separately) and have a procedure to recreate it after a database restore. You probably need a procedure to check periodically that that the two tables are in sync, too.

To speed up queries that need the spatial index - and if the table is not huge - you can make sure that the index is loaded in memory in advance, even in a separate cache. See LOAD INDEX INTO CACHE syntax and Multiple Key Caches


How do you stop, take offline, and delete a SQL Server database in recovery? I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so.

I need to either stop the recovery, or kill it in some way.

I don't care one bit about the database or any of the data, I have deployment scripts.

Any ideas?

Stop SQL Server
Delete MDF + LDF
Start SQL Server
Restore (may need to drop first, comes up suspect)

If the MDF and LDF are present on service start up, it will go into rollforward/rollback recovery. You have to drop them to prevent this.


MySQL Cluster On Cloud vs MySQL Cluster On Dedicated Cluster I saw this interesting article : http://mikaelronstrom.blogspot.com/2012/02/105bn-qpm-using-mysql-cluster-72.html and it made me get interested in MySQL Cluster.

After little chat with hosting provider and i was told i may not be able to achieve to such performance on the Cloud Architecture which brings me to my question :

MySQL Cluster On Cloud  vs MySQL Cluster On Dedicated Cluster

Are there really any performance difference
Which is better and why ( using different factors eg.  Scalability ,  Cost Implication  etc)

Thanks

The biggest difference will come from networking - Mikael's tests were run over Infiniband whereas most hosting or cloud providers will not give you that type of dedicated bandwidth.  As a distributed database, MySQL Cluster needs high network bandwidth to support message passing between the nodes, so the higher the network latency, the lower the read or write performance, though this can be offset by increasing concurrent load to the database

If you want to know more about the benchmark itself and how to run it on your own cloud vendors environment, check out this blog:
https://blogs.oracle.com/MySQL/entry/performance_testing_of_mysql_cluster


gMSAs and SQL Server 2017 I am setting up a brand new SQL 2017 installation. I want to set up and use gMSA(s) for the Service Accounts. What are the best practices for SQL Server? Should I use individual gMSAs for each service? Should there be individual gMSAs for each server or instance or site?

I have read the following and several other articles and posts:

Follow best practices described here:
https://docs.microsoft.com/en-us/sql/sql-server/install/security-considerations-for-a-sql-server-installation?view=sql-server-2017
Consider the use of Managed Service Accounts and Group Managed Service Accounts described here:

https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/
https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/

Any help that you can offer would be awesome.

We have created gMSAs for SQL Agent, Database Engine, Analysis Services and Integration Services.  I recommend the following:

MICROSOFT SQL SERVER 2016 INSTALLATION USING GMSA (GROUP MANAGED SERVICE ACCOUNTS) – PART I

Also, make sure that follow:

Using a gMSA with SQL Server

This will help you escape the pitfall of restarting the server.


MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB.  I converted all row formats to dynamic first for all the tables to be on Barracuda then running alter table <tablename> engine = InnoDB for 16 tables.  12 of the 16 tables changed file formats as well without an alter table command.  I am at a loss to understand this.  I think this may be related to the .frm files, but I'm not sure how.  I've checked environment variables:

innodb_file_format is showing Barracuda

innodb_file_format_check is ON

A couple of the tables: Articletranslations is showing as compressed, pubmedabstractauthors and pubmedtranslated are showing as compact.
The create table statements from tables that I had changed to dynamic file format before changing the storage engine to InnoDB.

Table: articletranslations

Create Table: CREATE TABLE `articletranslations` (
  `TranslationID` int(11) NOT NULL AUTO_INCREMENT,
  `ArticleID` int(11) NOT NULL,
  `language` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `TextContent` longtext COLLATE utf8_unicode_ci,
  `Name` text COLLATE utf8_unicode_ci,
  `Tags` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Detail_Abstract` longtext COLLATE utf8_unicode_ci,
  `Disclosures` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Discussion` longtext COLLATE utf8_unicode_ci,
  `Acknowledgements` longtext COLLATE utf8_unicode_ci,
  `D` longtext COLLATE utf8_unicode_ci,
  `Materials` text COLLATE utf8_unicode_ci,
  `HTMLTopContent` text COLLATE utf8_unicode_ci,
  `Rep_Results` longtext COLLATE utf8_unicode_ci,
  `Introduction` text COLLATE utf8_unicode_ci,
  `IsMachine` tinyint(1) NOT NULL DEFAULT '1',
  `DateTranslated` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TranslationID`),
  KEY `ArticleTranslations_Language_ArticleID` (`language`,`ArticleID`),
  KEY `ArticleTranslations_ArticleID` (`ArticleID`)
) ENGINE=InnoDB AUTO_INCREMENT=177437 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

Table: pubmedabstractauthors

Create Table: CREATE TABLE `pubmedabstractauthors` (
  `AuthorID` int(11) NOT NULL AUTO_INCREMENT,
  `ForeName` varchar(255) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `Initials` varchar(255) NOT NULL,
  PRIMARY KEY (`AuthorID`),
  KEY `names` (`ForeName`,`LastName`,`Initials`)
) ENGINE=InnoDB AUTO_INCREMENT=712515 DEFAULT CHARSET=latin1

Table: pubmedtranslated

Create Table: CREATE TABLE `pubmedtranslated` (
  `PMID` int(11) NOT NULL,
  `ArticleTitle` text COLLATE utf8_unicode_ci NOT NULL,
  `ArticleAbstract` text COLLATE utf8_unicode_ci NOT NULL,
  `LanguageID` smallint(6) NOT NULL,
  PRIMARY KEY (`PMID`,`LanguageID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Please note that you ran ALTER TABLE ... ENGINE=InnoDB; That is a full table rebuild whether you go from MyISAM to InnoDB, or InnoDB to InnoDB (known as the NULL ALTER).

Please note the paragraph from the MySQL 5.7 Docs on innodb_file_format:

  The innodb_file_format default value was changed to Barracuda in MySQL 5.7.

  The innodb_file_format setting is ignored when creating tables that use the DYNAMIC row format. A table created using the DYNAMIC row format always uses the Barracuda file format, regardless of the innodb_file_format setting. To use the COMPRESSED row format, innodb_file_format must be set to Barracuda.


Same tables with same data, different SQL Server stored procedure performance I have 2 SQL Server 2008 instances installed on the same server. They have same table structures with same data. I have a stored procedure deployed on both of them. The stored procedure performs differently on them. It takes 5 seconds on one instance and 50 seconds on the other to execute the procedure. I think the possible reasons for this are: index fragmentation, outdated statistics. I defragmented the indexes and updated the statistics but still no luck. Any ideas on this issue?

Thanks

Are the two instances indentically configured?

One thing to check is if the instances have the same memory allocation.


Working of indexes in PostgreSQL I have a couple of questions regarding working of indexes in PostgreSQL.
I have a Friends table with the following index:

   Friends ( user_id1 ,user_id2)

user_id1 and user_id2 are foreign keys to user table

Are these equivalent? If not then why?

Index(user_id1,user_id2) and Index(user_id2,user_id1)

If I create Primary Key(user_id1,user_id2), does it automatically create indexes for it and

If the indexes in the first question are not equivalent, then which index is created on above primary key command?

This answer is about (default) B-tree indexes. See this later, related answer about GiST, GIN etc.:

Why is a GiST index used for filtering on non-leading column?

Here are the results of querying a table on the second column of a multicolumn index.
The effects are easy to reproduce for anybody. Try it at home.
I tested with PostgreSQL 9.0.5 on Debian using a medium sized table of a real-life database with 23322 rows. It implements the n:m relationship between the tables adr (address) and att (attribute), but that's not relevant here. Simplified schema:
CREATE TABLE adratt (
  adratt_id serial PRIMARY KEY
, adr_id    integer NOT NULL
, att_id    integer NOT NULL
, log_up    timestamp NOT NULL DEFAULT (now()::timestamp)
, CONSTRAINT adratt_uni UNIQUE (adr_id, att_id)
);

The UNIQUE constraint effectively implements a unique index. I repeated the test with a plain index to be sure and got identical results as expected.
CREATE INDEX adratt_idx ON adratt(adr_id, att_id);

The table is clustered on the adratt_uni index and before the test I ran:
CLUSTER adratt;
ANALYZE adratt;

Sequential scans for queries on (adr_id, att_id) are as fast as they can possibly be. The multicolumn index can still be used for a query condition on the second index column alone.
I ran the queries a couple of times to populate the cache and the picked the best of ten runs to get comparable results.
1. Query using both columns
SELECT *
FROM   adratt
WHERE  att_id = 90
AND    adr_id = 10;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
(1 row)

Output of EXPLAIN ANALYZE:
Index Scan using adratt_uni on adratt  (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1)
  Index Cond: ((adr_id = 10) AND (att_id = 90))
Total runtime: 0.067 ms

2. Query using first column
SELECT * FROM adratt WHERE adr_id = 10;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       126 |     10 |     10 | 2008-07-29 09:35:54
       125 |     10 |     13 | 2008-07-29 09:35:54
      4711 |     10 |     21 | 2008-07-29 09:35:54
     29322 |     10 |     22 | 2011-06-06 15:50:38
     29321 |     10 |     30 | 2011-06-06 15:47:17
       124 |     10 |     62 | 2008-07-29 09:35:54
     21913 |     10 |     78 | 2008-07-29 09:35:54
       123 |     10 |     90 | 2008-07-29 09:35:54
     28352 |     10 |    106 | 2010-11-22 12:37:50
(9 rows)

Output of EXPLAIN ANALYZE:
Index Scan using adratt_uni on adratt  (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1)
  Index Cond: (adr_id = 10)
Total runtime: 0.058 ms

3. Query using second column
SELECT * FROM adratt WHERE att_id = 90;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
       180 |     39 |     90 | 2008-08-29 15:46:07
...
(83 rows)

Output of EXPLAIN ANALYZE:
Index Scan using adratt_uni on adratt  (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1)
  Index Cond: (att_id = 90)
Total runtime: 0.849 ms

4. Disable indexscan & bitmapscan
SET enable_indexscan = off;
SELECT * FROM adratt WHERE att_id = 90;

Output of EXPLAIN ANALYZE:
Bitmap Heap Scan on adratt  (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1)
  Recheck Cond: (att_id = 90)
  ->  Bitmap Index Scan on adratt_uni  (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1)
        Index Cond: (att_id = 90)
Total runtime: 0.894 ms

SET enable_bitmapscan = off;
SELECT * FROM adratt WHERE att_id = 90;

Output of EXPLAIN ANALYZE:
Seq Scan on adratt  (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1)
  Filter: (att_id = 90)
Total runtime: 2.680 ms

Conclusion
As expected, the multi-column index is used for a query on the second column alone.
As expected, it is less efficient, but the query is still 3x faster than without the index.
After disabling index scans, the query planner chooses a bitmap heap scan, which performs almost as fast. Only after disabling that, too, it falls back to a sequential scan.
See other answer for the original quote from the manual.
Updates since Postgres 9.0
Everything still basically true in Postgres 13. Most notable changes:

index-only scans in Postgres 9.2
True covering indexes with the INCLUDE keyword in Postgres 11
Multiple performance and space improvements (especially for multicolumn indexes) in Postgres 12
Deduplication in Postgres 13.

All in favor of index performance. (Sequential scans got faster, too, though.)


Best way to shrink a DB after nulling data from varbinary(max)? We have a database with a large amount of data stored in a field of type varbinary(max). At some point we can purge that data for most rows, but not all. Our plan is to make that field nullable, and simply null out the data when it is no longer needed. Once we do that we would like to reduce the size of the DB. What is the best way to accomplish this?

If there isn't a good way to reclaim space with the current setup, one idea I have is to move that data field to a separate table with just two columns: the key to the main table and the data field. Then we could simply delete the rows when they are no longer needed. (And then do some sort of shrink.) However, this would be a much more difficult change to make than simply making the existing field nullable.

Note: I actually don't care so much about making the database file smaller, but I do care about the newly freed up space becoming re-usable.

Over 90% of the DB size is this one field. I'm at 3TB already.

It looks to me like just updating the columns to NULL will release pages for reuse. Here's a Very Scottish® demo, to celebrate it being almost 5PM, EST.

USE tempdb;

DROP TABLE IF EXISTS dbo.RobertBurns;

CREATE TABLE dbo.RobertBurns
(
    Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    Scotch VARCHAR(50),
    HaggisAddress VARBINARY(MAX)
);

DECLARE @AddressToAVarbinaryHaggis VARBINARY(MAX);
DECLARE @AddressToAHaggis NVARCHAR(MAX) = N'
Good luck to you and your honest, plump face,
Great chieftain of the pudding race!
Above them all you take your place,
        gut, stomach-lining, or intestine,
You''re well worth a grace
        as long as my arm.

The overloaded serving tray there you fill,
Your buttocks shaped like a distant hilltop,
Your wooden skewer could be used to fix a mill
         if need be,
While through your pores your juices drip
         like liquid gold.

His knife see the serving-man clean,
And then cut you up with great skill,
Making a trench in your bright, gushing guts
        To form a ditch,
And then, 0h! What a glorious sight!
        Warm, steaming, and rich!

Then, spoonful after spoonful, they eagerly eat,
The devil will get the last bit, on they go,
Until all their well-stretched stomachs, by-and-by,
        are bent like drums,
Then the head of the family, about to burst,
        murmurs “Thank the Lord".

Is there a pretentious soul who, over his French ragout,
Or Italian cuisine that would make a pig sick,
Or French stew that would make that same pig ill
        with complete and utter disgust,
Looks down with a sneering, scornful attitude,
        on such a meal? (as Haggis)

Poor devil! See him over his trash!
As feeble as a withered bullrush,
His skinny leg no thicker than a thin rope,
        His fist the size of a nut,
Through a river or field to travel,
        Completely unfit!

But look at the healthy, Haggis-fed person!
The trembling earth respects him as a man!
Put a knife in his fist,
        He''ll make it work!
And legs, and arms, and heads will come off,
        Like the tops of thistle.

You Powers who look after mankind,
And dish out his bill of fare,
Old Scotland wants no watery, wimpy stuff
        That splashes about in little wooden bowls!
But, if You will grant her a grateful prayer,
        Give her a Haggis!';

INSERT dbo.RobertBurns (Scotch, HaggisAddress )
SELECT TOP 1000
CASE WHEN x.c % 15 = 0 THEN 'Laphroaig'
     WHEN x.c % 5 = 0 THEN 'Lagavulin'
     WHEN x.c % 3 = 0 THEN 'Port Ellen'
     ELSE 'Ardbeg'
END AS Scotch,
CONVERT(VARBINARY(MAX), REPLICATE(@AddressToAHaggis, x.c % 20 + 1))
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS c
FROM sys.messages AS m
) AS x;

CREATE INDEX ix_novarbinary ON  dbo.RobertBurns (Scotch, Id);
CREATE INDEX ix_yesvarbinary ON dbo.RobertBurns (Scotch, Id) INCLUDE (HaggisAddress);

With rows inserted, let's check on our index pages.

SELECT   OBJECT_NAME(i.object_id) AS table_name,
         i.name AS index_name,
         MAX(a.used_pages) AS leaf_me_alone
FROM     sys.indexes AS i
JOIN     sys.partitions AS p
ON p.object_id = i.object_id
   AND p.index_id = i.index_id
JOIN     sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = 'RobertBurns'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY OBJECT_NAME(i.object_id), i.index_id;

After the insert, I get this. Actual pages may vary for you.

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5587
RobertBurns ix_novarbinary                  10
RobertBurns ix_yesvarbinary                 5581

Let's NULL out some rows!

UPDATE rb
    SET rb.HaggisAddress = NULL
FROM dbo.RobertBurns AS rb
WHERE rb.Id % 15 = 0;

And check back in on our pages:

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5300
RobertBurns ix_novarbinary                  10
RobertBurns ix_yesvarbinary                 5273

So page count was reduced. Huzzah! For the two indexes that touch our VARBINARY data, they lost a buncha pages. That means they're back in circulation for other objects to use. Since I'm in tempdb, they probably get gobbled up pretty quickly by all the garbage things that go on here.

Now let's put some data back in:

INSERT dbo.RobertBurns (Scotch, HaggisAddress )
SELECT TOP 10 rb.Scotch, rb.HaggisAddress
FROM dbo.RobertBurns AS rb;

And checking back in:

table_name  index_name                      leaf_me_alone
RobertBurns PK__RobertBu__3214EC074BE633A2  5330
RobertBurns ix_novarbinary                  11
RobertBurns ix_yesvarbinary                 5305

Page counts inched up a bit.

So, looks like you don't have to do anything too crazy, or even shrink your database to get space reuse. I think you were conflating the behavior of dropping columns and needing to run DBCC CLEANTABLE with what you're actually doing.

Hope this helps!


How to insert values in junction table for many to many relationships (MySQL)? I'm new to relational database design and I've hit a stumbling block.

I have three tables...

PROJECTS
project_id, name, ...

PROJECTS_UNDERGROUND
projects_FK, underground_FK

(I have both FKs setup as a composite key to eleminate duplicate data)

UNDERGROUND
underground_id, underground_name

—
Both PROJECTS and UNDERGROUND have a columns and rows of data already populated, such as id's and names but at present, there is no connection with the Junction table, they're currently sitting unconnected.
I'm puzzled to the queries I have to write to link the projects with underground station names?
The concept of composite keys make sense, but I can't seem to fiqure out the queries to update the PROJECTS_UNDERGROUND table.
Thanks for your help, everyone.

Knowing a name, you can easily get the matching ID by filtering by the name. For the PROJECTS table, it would look like this:

SELECT
  project_id
FROM
  PROJECTS
WHERE
  name = @project_name
;

Same for UNDERGROUND:

SELECT
  underground_id
FROM
  UNDERGROUND
WHERE
  underground_name = @underground_name
;

Each query returns the result as a row set. The two row sets can be combined into a single row set if you cross-join them:

SELECT
  p.project_id,
  u.underground_id
FROM
  (
    SELECT
      project_id
    FROM
      PROJECTS
    WHERE
      name = @project_name
  ) AS p
CROSS JOIN
  (
    SELECT
      underground_id
    FROM
      UNDERGROUND
    WHERE
      underground_name = @underground_name
  ) AS u
;

And now you just need to insert the result returned by the above query into PROJECTS_UNDERGROUND:

INSERT INTO
  PROJECTS_UNDERGROUND (project_FK, underground_FK)
SELECT
  p.project_id,
  u.underground_id
FROM
  ... /* same query as previously */

It is also possible to rewrite the SELECT part like this:

SELECT
  p.project_id,
  u.underground_id
FROM
  PROJECTS AS p
CROSS JOIN
  UNDERGROUND AS u
WHERE
  name = @project_name
AND
  underground_name = @underground_name
;

Logically, this simpler looking query prescribes the server to first create a cartesian product of the two tables and then filter the result set by the condition in the WHERE clause. If that was indeed how the server would execute the query, it would probably not work very fast. However, it is likely that MySQL can figure out the possibility to push down the conditions in such a simple case, thus effectively changing the logical order of execution, and come up with an efficient execution plan. I invite you to test and compare both solutions.


Oracle - User to not see data from other users I've done some research about it but I found some answers that are not clearly saying if it is possible or not.

Right now, I have a user admin. Everyone in my team is using it to create other users. One user = one project.
When we create the users, we grant all privileges to him.

Now, we would like to be a bit more secure (I know, it's absolutely not secured right now).

I thought, we could have a user, let's name him "User_Creator", to have the sole purpose of creating other users (projects).
The new user, let's name him "Project1", can do whatever he wants in his own schema (create/drop/alter/insert/delete).
When User_Creator, creates another project (Project2), I would like that Project1 can not see data from Project2 and Project2 from Project1.

Is that even possible ? I understand that the grant/revoke is done on the object-level right ? Do I need to create a trigger to revoke access to other users immediately after the creation of a new object?

Edit1: I've created my user "User_Creator" like this:

CREATE USER user_creation IDENTIFIED BY abcdef;
GRANT RESOURCE TO user_creation WITH ADMIN OPTION;
GRANT dba TO user_creation with admin option;
CREATE ROLE user_creation_role;
GRANT CREATE SESSION TO user_creation_role with admin option;
GRANT ALTER USER TO user_creation_role;
GRANT CREATE USER TO user_creation_role WITH ADMIN OPTION;
GRANT DROP USER TO user_creation_role;
GRANT CREATE TABLE TO user_creation_role with admin option;
GRANT user_creation_role TO user_creation ;

And a project is created like this:

CREATE USER project1 IDENTIFIED BY abc;
GRANT CREATE SESSION TO project1;
GRANT dba TO project1;

Edit2:

The answer is to not grant DBA and only grant the creation.
That way, user will not share data.
Thank you for your help guys.
Sorry if it was a bit of a rookie question.

I create users like this now:

CREATE USER user_creator IDENTIFIED BY abcdef;
GRANT RESOURCE TO user_creator WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO user_creator with admin option;
CREATE ROLE user_creation_role;
GRANT CREATE SESSION TO user_creation_role with admin option;
GRANT CREATE USER TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE TABLE TO user_creation_role WITH ADMIN OPTION;
GRANT CREATE VIEW TO user_creation_role WITH ADMIN OPTION;
CREATE USER project1 IDENTIFIED BY abc;
GRANT CREATE SESSION TO project1 ;
GRANT CREATE TABLE TO project1 ;
GRANT UNLIMITED TABLESPACE TO project1 ;
GRANT CREATE VIEW TO project1 ;
GRANT CREATE PROCEDURE TO project1 ;

This is the default behaviour in Oracle. Users do not have access to other user's objects unless explicitly granted.

The DBA role includes the "GRANT ANY OBJECT" privilege, allowing the user to see all database objects:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba005.htm
https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm

You should revoke the DBA role from every app user and stop granting it to new users.


Golden Gate activity auditing with SQL? In a scenario where Oracle Golden Gate is used to replicate a primary site with an Oracle RAC database to a secondary site (and active/active back) we suspect unexpected changes from the unused secondary site.

The issue is a bit hard to debug as we do not have direct DBA access. I wonder is there an easy way with unprivileged SQL access on the primary side to see if any changes are received from the other database?

Can I see counters or timestamps of OGG activity which helps me to track down DML made?

As I understand it I could see changes from the OGG user when setting up triggers or auditing - however both is not available in this situation.

Short answer is 'No', you can't reliably prove this without lower level details.  Auditing and Triggers will work from the database layer.  If you have access to the GoldenGate Trail files (files with all the extracted changes), you can see exactly what GoldenGate will replicate by using logdump (another binary that comes with OGG).

It sounds like you have bi-directional replication (active-active back).  If so, your Extract process should be configured to ignore your OGG user, since you don't want to replicate changes back to the source.

Another thing to considers whether or not triggers are enabled on your unused/secondary site.  I have seen weird things happen when triggers are not disabled on the receiving side (duplicated history records, unique key violations, etc).

Also, if your secondary site is unused, maybe DataGuard is a better solution.  It all depends on your software and infrastructure architecture.  It won't be a bi-directional solution, but arguably a simpler solution than OGG.


represent multiple entities to one entity relationship If suppose, I have following tables:

Here, person can own either home or car. person can't own both at a time. If person owns home, then owns field will be equal to 1, if it owns car, owns field will be equal to 2.

Then, to retrieve what person owns, I will have to fire two queries in application like this:

select first_name, last_name, owns from person where id=1000;

if(owns == 1){
    select id, no_of_rooms, area, city from home where person_id=1000;
}else if(owns == 2){
    select id, make, model, color from car where person_id=1000;
}

Also, what I will do, after first query has executed, someone has deleted things for that person. Then I got in zombie state, I had known person information, but now have no idea about thing he owns.

Or may be I can do like this:

Then query will be:

select p.first_name, p.last_name, t.id, t.type_of_thing, t.no_of_rooms, t.area, t.city, t.make, t.model, t.color from person p, owned_thing t where p.id=1000 and p.id = t.person_id;

if(type_of_thing == 1){
    // process home in application
}else if(type_of_thing == 2){
    // process car in application
}

Which approach is better? Or is there any other better approach than this? I am going to use MySQL database.

Don't use an attribute value to indicate what entity a person owns. Instead, take advantage of the relational nature of the database in your queries. Remove the owns column from person and find related entities by joining the tables:

SELECT
p.id AS person_id, p.first_name, p.last_name,
h.id AS home_id, h.no_of_rooms, h.area, h.city,
c.id AS car_id, c.make, c.model, c.color
FROM person p
LEFT JOIN home h
ON p.id = h.person_id
LEFT JOIN car c
ON p.id c.person_id
WHERE p.id = 1

A LEFT (OUTER) JOIN will select the desired person whether it owns any entities or not. You can then use logic in your application to detect which owned entities have values and which are NULL. Use an INNER JOIN to only select the person if it also owns one of the entities.


MariaDB Galera Multi-Master Performance I am playing around with a MariaDB Galera Multi Master Cluster and I have some performance issues which I do not understand.

I have 2 virtual test machines with Ubuntu 16.04 and MariaDB 10.1 installed. I wrote a simple "benchmark" tool which simulates the creation of a contact in an example application. If I run this benchmark on a single machine, I get around 1050 transactions per seconds on each machine. I did not change anything on the default configuration of mariadb.

If I now cluster those two machines together via galera to a multi-master cluster, I get a significant performance drop to around 99 transactions per second.

The two machines are connected via a Gigabit network. I measured the throughput with iperf and its 950Mbits. The ping times are around 0.35ms.
The galera configuration is:

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.193.220,192.168.193.221"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address=192.168.193.220
wsrep_cluster_name="DB Cluster"

I do not wonder about the absolute transactions per second, but about the relative drop from a standalone instance to the cluster. What am I missing?

I don't know about your application/workload but generally, you should try with:

wsrep_slave_threads=N (Do not use a value for wsrep_slave_threads that is higher than the average given by the wsrep_cert_deps_distance status variable)
innodb_flush_log_at_trx_commit=0 (default is 1)
Have you tried redirect all writes to one node and leave the other as a hot-backup? Some workloads get a huge improvement by doing this.


Workaround for Anti-Semi Join bug I built the following SQL Server query, but it is encountering the anti-semi join defect in SQL Server 2005 which results in inaccurate cardinality estimates (1 -- urgh!) and runs forever. Since it is a longstanding production SQL Server I can't easily suggest to upgrade versions, and as such I cannot force the traceflag 4199 hint on this specific query.

I'm having a hard time refactoring the WHERE AND NOT IN (SELECT). Can anyone care to help? I've made sure to try and use the best joins based on clustered key pairs.

SELECT TOP 5000 d.doc2_id
    ,d.direction_cd
    ,a.address_type_cd
    ,d.external_identification
    ,s.hash_value
    ,d.publishdate
    ,d.sender_address_id AS [D2 Sender_Address_id]
    ,a.address_id AS [A Address_ID]
    ,d.message_size
    ,d.subject
    ,emi.employee_id
FROM assentor.emcsdbuser.doc2 d(NOLOCK)
INNER JOIN assentor.emcsdbuser.employee_msg_index emi(NOLOCK)
    ON d.processdate = emi.processdate
    AND d.doc2_id = emi.doc2_id
INNER LOOP JOIN assentor.emcsdbuser.doc2_address a(NOLOCK)
    ON emi.doc2_id = a.doc2_id
    AND emi.address_type_cd = a.address_type_cd
    AND emi.address_id = a.address_id
INNER JOIN sis.dbo.sis s(NOLOCK) ON d.external_identification = s.external_identification
WHERE d.publishdate > '2008-01-01'
    **AND d.doc2_id NOT IN (
        SELECT doc2_id
        FROM assentor.emcsdbuser.doc2_address d2a(NOLOCK)
        WHERE d.doc2_id = d2a.doc2_id
            AND d2a.address_type_cd = 'FRM'
        )**
OPTION (FAST 10)

Note that the Employee_MSG_Index table is 500m rows, doc2 is 1.5b rows, SIS is ~500m rows.

Any help would be appreciated!

Since it is a longstanding production SQL Server I can't easily suggest to upgrade versions

The anti semi join cardinality estimation bug is reproducible on all versions of SQL Server from 2005 to 2012 inclusive. All require trace flag 4199 to enable the fix, so upgrading would not solve your problem without activating 4199 (though there are many other good reasons to upgrade from 2005, of course).

...as such I cannot force the traceflag 4199 hint on this specific query.

If it is just one particular query that is affected, you can use OPTION (QUERYTRACEON 4199) to enable the trace flag for just that query. This query hint is documented and supported for use with 4199, and applies from SQL Server 2005 Service Pack 2 onward.
This hint effectively runs DBCC TRACEON (4199) and DBCC TRACEOFF (4199) around the query, and requires sysadmin permission as a result. If that is a problem, add the hint using a plan guide.
You should also look at testing your whole system with 4199 enabled instance-wide. Plan regressions are possible, but overall you may find that the various optimizer fixes enabled by this flag are well worth it. All future plan-affecting query processor fixes require this flag to activate.
All that said...
As mentioned in ypercube's answer, the bug requires two or more join columns to manifest (among many details). The redundancy in your NOT IN clause causes the optimizer to see two column comparisons (though logically there is only one), thereby exposing the bug.
Removing this redundancy will 'solve' the problem for this particular query, though other queries that really do have more than one join predicate will still be vulnerable.
Example
To illustrate, here is an example based on the CSS blog post linked in the question (but with a complete script!):
CREATE TABLE dbo.tst_TAB1
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL
);

CREATE TABLE dbo.tst_TAB2
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL
);

CREATE INDEX i ON dbo.tst_TAB1 (c1, c2);
CREATE INDEX i ON dbo.tst_TAB2 (c1, c2);

Sample data:
INSERT dbo.tst_TAB1
    (c1, c2, c3)
SELECT
    number, number, number
FROM master.dbo.spt_values
WHERE
    [type] = N'P'
    AND number BETWEEN 1 AND 2047;

INSERT dbo.tst_TAB2 (c1, c2, c3)
VALUES (1, 1, 1);

Test query using NOT IN with redundant predicate:
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
        -- This is redundant!
        WHERE
            t2.c1 = t1.c1
    );

The estimated execution plan shows an estimate of 1 row after the anti semi join:

Side note: In fact this is an example of another (rare) bug. Writing the WHERE clause as t1.c1 = t2.c1 instead of t2.c1 = t1.c1 allows the optimizer to see that the two join predicates are in fact the same, and the bug does not manifest.
The same query with OPTION (QUERYTRACEON 4199):
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
        WHERE
            t2.c1 = t1.c1
    )
OPTION (QUERYTRACEON 4199);

The estimated execution plan now shows an estimate of 2046 rows, which is exactly right:

We can also remove the redundant predicate:
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
    );

The execution plan happens to use an additional unrelated optimization (the Stream Aggregate), but the important point is that the post-join estimate is correct without having to enable 4199:

Multiple anti semi join columns
It is possible to express an anti semi join over multiple columns using NOT IN syntax. These cases will require 4199. For example, the next query joins on c1 and c2:
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
        WHERE
            t2.c2 = t1.c2
    );

The execution plan shows the erroneous 1-row estimate:

With 4199, the issue is resolved:
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
        WHERE
            t2.c2 = t1.c2
    )
OPTION (QUERYTRACEON 4199);

Other syntaxes
Using NOT IN in this way is best avoided, not least for the reasons mentioned in Books Online:

That issue with NOT IN and NULLs has been written about many times. There are many alternative syntaxes available, of which NOT EXISTS is my personal preference. Note that changing syntax will not avoid the cardinality estimation bug:
SELECT
    T1.c1
FROM dbo.tst_TAB1 AS t1
WHERE
    NOT EXISTS
    (
        SELECT 1
        FROM dbo.tst_TAB2 AS t2
        WHERE
            t2.c1 = t1.c1
            AND t2.c2 = t1.c2
    );

That two column anti semi join produces the 1-row estimate, and requires 4199 to fix it. The execution plans are exactly the same as seen previously, so I will not repeat them. The NOT EXISTS syntax does avoid the NULLs issue with NOT IN.
Other observations
I agree with ypercube's other observations.

Sprinkling NOLOCK hints over every table in a query is a bad code smell. If the query can truly tolerate READ UNCOMMITTED transaction semantics, set the isolation level explicitly.

TOP without ORDER BY is another sign of poor code. TOP requires an ORDER BY clause to define what TOP means. Never rely on observed behaviour, use an explicit top-level ORDER BY to obtain a guarantee.

INNER LOOP JOIN and join hints in general, imply a FORCE ORDER query hint. This severely limits the optimizer's freedom, and is usually misunderstood and misapplied. Never use hints that you don't fully understand.


Do OR conditions in Oracle SQL queries spoil index use / cause full table scan? I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans:

select t.Id, t.ObjectId, t...[other Tasks columns]
    , od1...[ObjectData1 columns]
    , od2...[ObjectData2 columns]
from Tasks t
left join ObjectData1 od1 on t.ObjectId=od1.ObjectId
left join ObjectData2 od2 ...
... [more leftjoins] ...
where t.ObjectId = 12345
or t.Id in (
    select TaskId from ObjectAffectingTasks where ObjectId=12345
)

Tasks has indices on Id, ObjectId; ObjectAffectingTasks has index on both ObjectId and TaskId. All joined tables have proper indices, too. The ObjectAffectingTasks table contains task IDs affecting the object, but having another in the ObjectID, so all Tasks affecting object with ID 12345 shall be selected.

When analyzing the query, it seemed to be the OR condition that spoiled the execution plan. Where clauses with only ObjectId or only the subquery were using all the indices. Another workaround was to create a Union subquery, which also used the indices:

where t.Id in (
    select TaskId from ObjectAffectingTasks where ObjectId=12345
    union
    select Id from Tasks where ObjectId = 12345
)

I would, anyway, expect the original query and IN subquery to be translated into a semi-join with index lookup on both Id and ObjectId. However, I see the IN subquery is internally translated into EXISTS (select 0 from...)

May this be only a statistics problem, or does the OR condition spoil it all? Is it possible to lookup the indices on Tasks and ObjectAffectingTasks with such an OR condition?

Unfortunately, the queries are created by rather complex program code, so rewriting them would be way too much work. Another issue is that they're a bit generic, being used for both Oracle and MS SQL Server, so proprietary hints for index usage are not an option, either.

I had a very similar issue in Oracle 12c. In experimenting, the PRECOMPUTE_SUBQUERY optimizer hint 'forced' the subquery to execute first, thus making use of the defined indexes.  In your snippet, that would look like this:

...
where t.ObjectId = 12345
or t.Id in (
    select /*+ PRECOMPUTE_SUBQUERY */ TaskId
    from ObjectAffectingTasks where ObjectId=12345
)


Performance of Polybase We've been experimenting with Polybase on a SQL Server 2019 (CU2) box, using SQL Server external data sources and the performance has not been good - in most cases a 1400% increase.  In each case all of the tables/views we're querying are from external tables pointing at the same external data source. We've tried both running the query broken out on the local box, and using the same query as a view pulled in as an external table. We've also scripted every statistic from the remote server onto the external tables with no change.  You can see the performance difference below, using a sample query.

The servers are setup identical resource wise: 32GBs of RAM, 8 vCPU, SSD disks, and no other running queries.  I've tried against two different remote servers, one running SQL Server 2016 with the latest SP/CU, and a separate 2019 box running CU2.  The servers are VMs running on the same host, and we've ruled out any type of host contention.

Sample Query:

SELECT
     StockItem_StockNumber, BlanktypeId, NameHTML, BackgroundStrainName, IsExact, IsConditional
     ,ROW_NUMBER() Over(Partition By StockItem_StockNumber, BlanktypeId Order By pt.Name, p.Name, gptr.Text) as row_num
     ,pt.Name as Level1, p.Name as Level2, gptr.Text as Level3, MGIReference_JNumber
     ,gptr.Type as Level3Type
FROM
     StockItemBlanktypes sig
     INNER JOIN Blanktypes g on g.BlanktypeId = sig.Blanktype_BlanktypeId
     INNER JOIN BlanktypeStockTerms gpt on gpt.Blanktype_BlanktypeId = g.BlanktypeId
     INNER JOIN StocktypeTerms p on p.StocktypeTermId = gpt.StocktypeTerm_StocktypeTermId
     INNER JOIN BlanktypeStockTermReferences gptr on gptr.BlanktypeStockTerm_BlanktypeStockTermId = gpt.BlanktypeStockTermId
     INNER JOIN StockTermClosures ptc on ptc.ChildStockTerm_StocktypeTermId = p.StocktypeTermId
     INNER JOIN StocktypeTerms pt on pt.StocktypeTermId = ptc.ParentStockTerm_StocktypeTermId
WHERE
     ptc.ParentHeaderKey = 3

Running on the remote 2016/2019 box directly:

 SQL Server Execution Times:
   CPU time = 3486 ms,  elapsed time = 5035 ms.

Running on the 2019 box with Polybase and with PUSHDOWN OFF:

 SQL Server Execution Times:
   CPU time = 15016 ms,  elapsed time = 92113 ms.

Running on the 2019 box with Polybase and with PUSHDOWN ON:

 SQL Server Execution Times:
   CPU time = 3875 ms,  elapsed time = 74149 ms.

Polybase Execution Plan (they look the same regardless of PUSHDOWN option):

sp_whoisactive (wait info) shortly after starting Polybase query:

sp_whoisactive (wait info) further into the Polybase query:

Using a Linked Server instead of Polybase:

 SQL Server Execution Times:
   CPU time = 3032 ms,  elapsed time = 9316 ms.

That's roughly a 1400% increase in the time it takes the query to run.  What's confusing is that Microsoft is pushing Polybase as an ETL replacement, but there is no way that's possible with this kind of performance.

Are other people seeing similar performance using Polybase with connections from SQL Server to SQL Server?  And, does anyone know what Polybase internal operations could be causing this kind of slowness?

Thank you.

Update on 2/23/2020:

Not that it has any implications to the query performance, but I discovered today that Polybase queries are not honoring MAXDOP settings (either instance wide, or query hints) and the set statistics CPU time is not reporting accurately.

I was able to look at the execution plan through XML, and this is the only section that is informative.  The only information I could find on PREEMPTIVE_SHAREDMEM_GETDATA is its a wait type when a thread is waiting for a call to the GetData method of a COM object to complete.

Second update on 2/23/2020:

I created a single large table and I ran a "select *" both locally and over Polybase.  Those runs have almost identical run times. I opened a support case with Microsoft and I'll report back if we make any headway.

Update on 2/24/2020:

After Kevin's response (below) I made a few tweaks to the original post for clarity, and running the queries/DMVs he referenced comes back with these results:

I'll work from the assumption that all of the tables listed in the query above are external tables pointing to the same external data source.  With that in mind, here are a few considerations.
Distributed Requests
There are two DMVs which will provide a bit more information than what you have currently:  sys.dm_exec_distributed_requests and sys.dm_exec_distributed_request_steps.  Try running your slow query and see what shows up in the distributed requests DMV.  Here is a sample query I like to use for this purpose:
SELECT TOP(100)
    r.execution_id,
    r.status,
    r.error_id,
    r.start_time,
    r.end_time,
    r.total_elapsed_time,
    t.text
FROM sys.dm_exec_distributed_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY
    r.end_time DESC;
GO

You might end up seeing multiple results like so:

For each one of these, you can get the set of steps involved (changing the query to use whatever your execution IDs are):
SELECT
    rs.execution_id,
    rs.step_index,
    rs.operation_type,
    rs.distribution_type,
    rs.location_type,
    rs.[status],
    rs.error_id,
    rs.start_time,
    rs.end_time,
    rs.total_elapsed_time,
    rs.row_count,
    rs.command
FROM sys.dm_exec_distributed_request_steps rs
WHERE rs.execution_id IN ('QID573', 'QID574')
ORDER BY
    rs.execution_id DESC,
    rs.step_index ASC;
GO

What I tend to look for is "excessive" row counts.  For example, if I'm expecting a small number of rows back but the row count is much higher for a given step, then the PolyBase data movement service is sending over many more rows than I'd ideally like and forcing the PolyBase engine to do the dirty work of combining tables together.  That leads to the next consideration.
Filters and Predicates
In your query, I don't see any explicit filters or predicates, but I wonder if there are implicit filters.  For example, in the StocktypeTerms table, I see ParentHeaderKey = 3 in the join criteria.  If this is a highly selective join criterion, it is likely that PolyBase is streaming all of the rows over and then performing the filters on the local side rather than performing the filter operation remotely and pulling in just the rows it needs.
There are several reasons why this might be the case, including complex filters (which this might be), a predicate which PolyBase cannot push down (there are some stringent limitations on what is available for pushdown), or a predicate formed from two different external data sources (which is a scenario I'd love to see but does not work well today).  Given the lack of a WHERE clause, I won't dive into more detail on this section.
Network Performance
If you have network issues between the external data source and your local SQL server instance, that can result in slowdowns.  In your second update, you mention creating an external table against a single table and streaming all of its data down, and the timing differences being minor.  This is an indication that network speed is not a significant issue in your case.
Specific Guidance
With the above in mind, here's what I would recommend for your specific scenario as a way of figuring out what might be the issue.
If you see that more rows are coming to the local SQL Server instance than the 313,585 indicated in your execution plan, that means there is extra work happening on your local SQL Server instance which could be handled remotely.  There are two things which might help.
First, try to run your query with OPTION(FORCE EXTERNALPUSHDOWN) on.  You mentioned enabling and disabling predicate pushdown (by setting PUSHDOWN = ON and PUSHDOWN = OFF on the external data source definition), but it wasn't clear if you specified this hint.  There is a non-zero chance that this will trigger the PolyBase engine to behave differently than what you see simply by writing the query.  Given your specific query, my guess is that this query hint will not make a difference.
Second, create a view with the above SQL query on your remote data source.  Then, create an external table locally that references that remote view.  What this does is force the external data source to do the work of joining together all of these tables before sending anything to the local SQL Server.
In my simple case, creating a view for my query resulted in fewer distributed requests and less time retrieving data:


Efficiently perform multiple similar updates I'm trying to extend some existing SQL (SQL Server) that builds a table of all items to act on using a number of queries. Some of these are very similar and costly, and I was hoping to get some advice on how to either combine them or rework this section to avoid doing the hard query multiple times. My SQL experience is a bit shallow, so maybe there is an obvious solution to this.

--Move Messages if moveMessages=1
UPDATE t1
SET t1.foo = temp.newFoo,
    t1.bar = temp.newBar
FROM Table1 t1
INNER JOIN @TempTable TEMP ON TEMP.baz = t1.bar
                          AND TEMP.moveMessages = 1
INNER JOIN Table2 t2 ON t2.type = 'M'
                    AND t1.thingId = t2.thingId

--Move Documents if moveDocuments=1
UPDATE t1
SET t1.foo = TEMP.newFoo,
    t1.bar = TEMP.newBar
FROM Table1 t1
INNER JOIN @TempTable TEMP ON TEMP.baz = t1.bar
                          AND TEMP.moveDocuments = 1
INNER JOIN Table2 t2 ON t2.type = 'D'
                    AND t1.thingId = t2.thingId

There are more repetitions of the above, but the only difference between each one is which column in TEMP is checked, and which letter is referenced for t2.type. So for example, for any rows in TEMP that have moveMessages=1, move all the 'M' types. For each row in TEMP that has moveDocuments=1, move all the 'D' types, and so on.

Is there a way to do all of this in one statement? Is this a situation when I should use another temporary table?

One of the nice things about INNER JOIN syntax is that it actually allows you to separate filter criteria from join criteria. The two statements in your question could be collapsed into a single query as follows:

UPDATE t1
SET t1.foo = temp.newFoo,
    t1.bar = temp.newBar
FROM dbo.Table1 t1 -- please always use schema qualifiers
INNER JOIN @TempTable TEMP
  ON TEMP.baz = t1.bar
INNER JOIN dbo.Table2 t2
  ON t1.thingId = t2.thingId
WHERE (TEMP.moveMessages  = 1 AND t2.type = 'M')
   OR (TEMP.moveDocuments = 1 AND t2.type = 'D');


How to Use a Certificate from a Different Database Here are the requirements:

  Key-encrypting keys are stored separately from data-encrypting keys.  ( Certificate and the symmetric key should be stored separately – not within the same database)

I am using certificate based encryption so I made the certificate on the database I'll be using.  How do I access a certificate from a different database?

In SQL Server for column encryption, you typically use the Service Master Key to encrypt your Database Master Key, which, in turn, encrypts your Certificate, which encrypts your Symmetric Key, which encrypts your data.

See https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encryption-hierarchy

For Transparent Database Encryption the hierarchy is a little different.  The Database Encryption Key in your database is encrypted by the Database Master Key in the Master Database.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

In both cases the key used to encrypt/decrypt data is protected by an encryption chain that goes through the Master database.  I think both meet your requirement in effect.


SQL Server Alert - Description (None) For every system Alert defined to send an email, we receive a 2 alerts with the following bodies. How do I stop the message with the empty (None) description?

Empty Alert

SQL Server Alert System: 'Severity 010' occurred on \my-server

  DATE/TIME:    1/30/2013 10:22:59 AM

  DESCRIPTION:  (None)

  COMMENT:  (None)

  JOB RUN:  (None)

Valid Alert

SQL Server Alert System: 'Severity 010' occurred on \my-server

  DATE/TIME:    1/30/2013 10:22:59 AM

  DESCRIPTION:  Log was backed up. Database: [mydatabase], creation date(time): 2011/10/03(17:20:11), first LSN: 859915:34267:1, last LSN: 859915:192686:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\my-backup-server.domain.local\sqldump\my-server\mydatabase\LOG\my-server_mydatabase_LOG_20130130_114000.trn'}). This is an informational message only. No user action is required.

  COMMENT:  (None)

  JOB RUN:  (None)

Here is our definition for the "Severity 10" Alert.

EXECUTE msdb..sp_add_alert @name = N'Severity 010'
  , @message_id = 0
  , @severity = 10
  , @enabled = 1
  , @delay_between_responses = 60
  , @notification_message = NULL
  , @include_event_description_in = 1
  , @database_name = N''
  , @event_description_keyword = N''
  , @job_name = NULL
  , @performance_condition = NULL
  , @wmi_namespace = N''
  , @wmi_query = N''
GO
EXECUTE msdb..sp_add_notification N'Severity 010', N'Project Support', 7
GO

By recompiling/committing the Alert, the double emails have stopped. It must have been a bad commit initially.


easy upgrade of all sp_blitz-procs? I wonder if you could make the upgrade of the procs easier and faster by providing a sqlcmd-skript or something which executes all sp_blitz[xxx].sql files.
I first did a little sqlcmd-script myself (but failed because of quoted_identifiers). It would be great if something like this could be a part of the package? Or is there already a easy way to implement new sp_blitz[xxx] versions on a server?

This is how I deploy them in my environment.
In SSMS, view Registered Servers, create a local server group called Staging and put all the Staging servers in there.
Right click on the Staging group and select New Query. This will open a new query window connected to all the servers in the group. Status bar will say Connected xx/yy where xx is the number of connected servers and yy is the total number of servers.
In the Query Window, paste the sp_Blitz code and execute it.
This applies the proc to all the Staging servers at once.
Repeat the process for the other sp_Blitz procs.
Repeat the process for the Production group of servers.
It only takes a few minutes to deploy all the sp_Blitz procs to a large number of servers this way.


MongoDB syncingTo change I have a 3 members replica set version 2.6.1 with following configuration

                    "_id" : 0,
                    "host" : "10.125.16.22:30000",
                    "priority" : 2

                    "_id" : 1,
                    "host" : "10.125.16.22:40000"

                    "_id" : 2,
                    "host" : "10.125.16.22:60000",
                    "priority" : 0,
                    "slaveDelay" : 3600,
                    "hidden" : true

And i want id:2 to sync from id:1.

I execute

db.adminCommand( { replSetSyncFrom: "10.125.16.22:40000" } )

and got

"syncFromRequested" : "10.125.16.22:40000",
"prevSyncTarget" : "10.125.16.22:30000",
"ok" : 1

But on rs.status the sync is still syncingTo : "10.125.16.22:30000"

I found on documentation:

  Changed in version 2.4: The sync target falls more than 30 seconds
  behind another member of the replica set; the mongod will revert to
  the default sync target.

Is this what happened in my case? if yes is there any workaround?

There isn't enough information here to know if the "sync target falling more than 30s behind" was the case, but it seems likely if you are pushing through a lot of activity with all of your replica set nodes on the same machine.

There should be some log entries confirming the change; if you're still on 2.6 you should be able to grep for:

 changing sync target because current sync target's most recent OpTime is

One thing to note is that rs.syncFrom() is a temporary change. By default the sync targets are automatically chosen by MongoDB, and will be reset if you restart instances or lose connectivity.

There's a helpful open source tool called Edda which you can use to process MongoDB log files and visualise changes in replica set history. NOTE: The github repository has changed since that blog post was published; the current repo is 10gen-labs/edda.