postgresql count null and blank value from jsonb column

You can use the function json_array_elements to convert your array into a set of elements. Each element is itself an array, so you can use the -> operator to retrieve the second element. Once you have them, use a standard count(*), with the corresponding FILTER.

You can do it with the following SQL statement (the WITH helps you view the "step-by-step" approach):

WITH original_data(var) AS
(
    VALUES (
    '
    [
        ["a", 921],
        ["b", ""],
        ["c", null]
    ]
    '::json)   -- Note the ::json to make sure PostgreSQL uses the proper type
)
, second_elements AS
(
SELECT
    json_array_elements(var)->1 AS e
FROM
    original_data
)
SELECT
    count(e) AS total_elements,
    count(e) FILTER (WHERE e::text = '""') AS blank_elements,
    count(e) FILTER (WHERE e::text = 'null') AS null_elements
FROM
    second_elements ;

... or the following one:

SELECT
    count(e) AS total_elements,
    count(e) FILTER (WHERE e = '""') AS blank_elements,
    count(e) FILTER (WHERE e = 'null') AS null_elements
FROM
    (
    SELECT
        (json_array_elements(var)->1)::text AS e
    FROM
        (
        VALUES (
        '
        [
            ["a", 921],
            ["b", ""],
            ["c", null]
        ]
        '::json)
        ) AS original_data(var)
) AS second_elements ;

In both cases, you get:

 total_elements | blank_elements | null_elements
 -------------: | -------------: | ------------:
              3 |              1 |             1

You can check everything at dbfiddle.

NOTE: If you want to use jsonb instead of json, just change the ::jsonwith ::jsonb in the first statement, and call the jsonb_array_elements instead of json_array_elements.


Denormalize when it's OLAP operations, normalize when OLTP (from linked article under section Denormalization)

  Databases intended for online transaction processing (OLTP) are
  typically more normalized than databases intended for online
  analytical processing (OLAP). OLTP applications are characterized by a
  high volume of small transactions such as updating a sales record at a
  supermarket checkout counter. The expectation is that each transaction
  will leave the database in a consistent state. By contrast, databases
  intended for OLAP operations are primarily "read mostly" databases.
  OLAP applications tend to extract historical data that has accumulated
  over a long period of time. For such databases, redundant or
  "denormalized" data may facilitate business intelligence applications.
  Specifically, dimensional tables in a star schema often contain
  denormalized data. The denormalized or redundant data must be
  carefully controlled during extract, transform, load (ETL) processing,
  and users should not be permitted to see the data until it is in a
  consistent state. The normalized alternative to the star schema is the
  snowflake schema. In many cases, the need for denormalization has
  waned as computers and RDBMS software have become more powerful, but
  since data volumes have generally increased along with hardware and
  software performance, OLAP databases often still use denormalized
  schemas.

  Denormalization is also used to improve performance on smaller
  computers as in computerized cash-registers and mobile devices, since
  these may use the data for look-up only (e.g. price lookups).
  Denormalization may also be used when no RDBMS exists for a platform
  (such as Palm), or no changes are to be made to the data and a swift
  response is crucial.


reapply permissions post sql server restart

An alternative to modifying model might be to create a stored procedure in the master database that contains whatever you need to do. Then you mark this as startup proc (sp_procoption).
You might have to put in a WAITFOR in there in case SQL Server executes this before model has been recovered and tempdb has been created. For you to play with.
I've only used startup proc occasionally, so make sure you do thorough testing.
And, I fully agree with J.D.: modify them jobs instead, if at all possible.


Check out relational database design.  It's an art and a science, and something that really can't be explained to the fullest in a post.  But what you should do is create relationships between contacts and all possible data that will be related to it.  This is most prevalent through the use of primary and foreign key relationships.


DLL Error While Creating an In-Memory Table in SQL Server 2014

We have an internal "parity" server to protect our production and pre-production environments.  After some research and help from the #sqlhelp tag on twitter we found the location of the DLLs that SQL Server generates prior to loading into memory.  Once those were loaded into the parity server as approved locations for DLLs and EXEs - things were all good.  So this was completely an environmental error - nothing to do with SQL Server per se.


You want to write this - which is valid SQL by the way - just not implemented in Sybase:

select *
from A
where (x,y) in (select u,v from B) ;

The easiest way is to rewrite is by using an EXISTS correlated subquery instead:

select *
from A
where exists
      (select 1 from B
       where A.x = B.u
         and A.y = B.v
      ) ;


First and most importantly, the great majority of users should simply use pg_basebackup. For those few it is unsuitable for, there are existing tools you should use instead of rolling your own, like PgBarman, WAL-E and OmniPITR.

If the server crashes mid-backup when using pg_basebackup you might need to remove the backup.label file from the data directory manually. It can result in some increased table bloat while it's running but is otherwise very safe.

Note that PostgreSQL 9.6 addresses the issue with the leftover backup.label file by providing a new API that returns the backup label file over the SQL connection. See the release wiki and docs. You should strongly prefer to use this mode when backing up 9.6 or newer.


Extract a table from a MySQL database using mysql/databasename folder

If you see [tablename].MYD and [tablename].MYI files it's MYISAM. If you see [tablename].ibd or only the .frm file then it's InnoDB. Based on that the process:

MyISAM

You can simply copy the .MYI, .MYD and .frm files to any existing MySQL instance's database directory.

For example:

You have your database in /var/lib/mysql/sitename then you find the table which will be /var/lib/mysql/sitename/[dbname]/[tablename].* (.MYD, .MYI and .frm). You can copy only these three files to an arbitrary database on a newly installed MySQL like /var/lib/mysql/sitename/[restored_db]/.

InnoDB

It's possible to import tablespace since 5.6 but if you can it's much simpler to copy the whole directoy. It's a common procedure to clone new replication slaves.

In your case you copy the whole /var/lib/mysql/sitename directory onto the new server and start MySQL over it. Make sure the innodb_log_file_size and innodb_log_files_in_group matches the size and number of what you have now. These are the files called ib_logfile0 and ib_logfile1.

When you have the server running you can use mysqldump or any other method you prefer.


How does IDENTITY_INSERT affect concurrency?

Setting IDENTITY_INSERT ON on its own does not eliminate concurrency - this does not place any exclusive locks on the table, only a brief schema stability (Sch-S) lock.

So what could theoretically happen, under the default behavior, is you could do this in session 1:

BEGIN TRANSACTION;

-- 1
SET IDENTITY_INSERT dbo.tablename ON;

-- 2
INSERT dbo.tablename(id, etc) VALUES(100, 'foo'); -- next identity is now 101

-- 3
INSERT dbo.tablename(id, etc) VALUES(101, 'foo'); -- next identity is now 102

-- 4
SET IDENTITY_INSERT dbo.tablename OFF;

COMMIT TRANSACTION;

In another session, you can insert rows into the table at points 1, 2, 3 or 4. This may seem like a good thing, except what happens for any insert that happens between 2 and 3, is that the auto-generated value triggered by another session is based on the results of statement 2 - so it will generate a 101, and then statement 3 will fail with a primary key violation. This is pretty simple to set up and test yourself with some WAITFORs:

-- session 1
-- DROP TABLE dbo.what;
CREATE TABLE dbo.what(id INT IDENTITY PRIMARY KEY);
GO
BEGIN TRANSACTION;

SET IDENTITY_INSERT dbo.what ON;

INSERT dbo.what(id) VALUES(32);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(33);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(34);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(35);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(36);

SET IDENTITY_INSERT dbo.what OFF;

COMMIT TRANSACTION;

Once that batch has started, start this batch in another window:

-- session 2
INSERT dbo.what DEFAULT VALUES;
WAITFOR DELAY '00:00:01';
GO 20

Session 2 should only ever insert values from 1-20, right? Except that because the underlying identity has been updated by your manual inserts session 1, at some point session 2 will pick up where session 1 left off, and insert 32, or 33, or 34 etc. It will be allowed to do this, but then session 1 will fail on the next insert with a PK violation (which one wins may just be a matter of timing).

One way to workaround this is to invoke a TABLOCK on the first insert:

INSERT dbo.what WITH (TABLOCK) (id) VALUES(32);

This will block any other users trying to insert (or do anything, really) with this table until you are done moving those archived rows back. This throttles concurrency, sure, but this is the way you want blocking to work. And hopefully this isn't something that's happening at such a frequent rate where you're blocking other people all the time.

A couple of other workarounds:

stop caring about the IDENTITY value generated. Who cares? Use a UNIQUEIDENTIFIER (maybe generated in a separate table with an IDENTITY as a surrogate) if the original value is very important.
change the archive process to use a "soft delete" where something is marked as archived initially and the archival isn't made permanent until some later date. Then whatever process is trying to move them back can simply perform a direct update and fix the soft delete flag.


Problem with a foreign key referencing a primary key which is also part of an unique key (MariaDB)

MariadDB seems to handle it correctly. According to Fiddle the information_schema is correct, so the problem seems to be with your tools:
SELECT table_name, constraint_name, referenced_table_name, unique_constraint_name
FROM information_schema.referential_constraints;

table_name          constraint_name         referenced_table_name   unique_constraint_name
-----------------------------------------------------------------------------
author_personal1    author_personal_FK      author1                 author_UN
work_authors1       work_authors_FK_author  author1                 PRIMARY


Update the host field in Grant privileges in mysql

How do I change 'localhost' to the *.*

That part of the question is not clear, since 'localhost' refers to a hostname, while '*.*' refers to all databases, all objects.

If you are wanting to give access to the username 'ssc' from any IP or host, not just 'localhost' -- that's what it sounds like -- then there are two different approaches.

First, though, you need to understand that an individual "user" in MySQL is the combination of username and host specification -- not just the username.  A user called 'ssc'@'%' would be a user with username 'ssc' who could log in from any host ('%' is the host wildcard, not '*'); however, that's not really the same "user" as 'ssc'@'localhost' as far as MySQL is concerned.

Technically, you could just update the grant tables, changing entries for that user to the wildcard hostname '%'.  These are in the mysql schema, tables named user, db, tables_priv, columns_priv, procs_priv, and (in newer versions) proxies_priv.

These can be updated with standard queries (e.g., UPDATE mysql.user SET host = '%' where host = 'localhost' AND user = 'ssc', and a similar query for the 'db' table).

After you update them you have to issue the FLUSH PRIVILEGES; statement to reload the already-in-memory privilege structures from the tables.

The other approach, of course, is the DROP USER to remove the old user and GRANT the desired privileges to the new user.  This can be accomplished even if you don't know the user's password, because the IDENTIFIED BY {password} clause in the GRANT statement will also accept an already-encrypted password (which you can get from the mysql.user table).

The potential problem you'll have with both of these approaches is the way some objects have a DEFINER user, that establishes the privileges associated with the object, no matter who is actually using the object at the time.  Stored programs (triggers, procedures, functions, and events) and views are all affected by this.  Base tables and databases themselves are not.

Since 'ssc'@'localhost' and 'ssc'@'%' (or another other host spec) are not really "the same user," it follows that making the changes using either method above means you will have to identify and then redefine any objects the previous user has defined in the database, using a new DEFINER declaration (or allowing the definer to be set implicitly) ... or they will not work, once you remove or redefine the user.

As an example, here's a view that was created by user 'hr2'@'foo'.  I'll drop that user, then examine the view.

mysql> drop user hr2@foo;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view hr2v;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| hr2v | CREATE ALGORITHM=UNDEFINED DEFINER=`hr2`@`foo` SQL SECURITY DEFINER VIEW `hr2v` AS select `test`.`t1`.`id` AS `id` from `t1` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message                                                         |
+-------+------+-----------------------------------------------------------------+
| Note  | 1449 | The user specified as a definer ('hr2'@'foo') does not exist |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

Note that I am logged in with SUPER privilege, but now that I've dropped the user who was DEFINER of the view, I am prevented from opening that view.

mysql> select * from hr2v;
ERROR 1449 (HY000): The user specified as a definer ('hr2'@'foo') does not exist

Aside: if you end up in a circumstance where this comes up, the first fix is to simply re-create the user.  Unlike in some systems where a user has a hidden identifier beyond their visible username, MySQL treats the recreated user as identical, since it's just matching strings.

But you can prevent this problem by verifying that no objects are associated with the user you'll be removing, or by finding them and fixing them first.

SELECT * FROM information_schema.views WHERE definer ='user@host';
SELECT * FROM information_schema.triggers WHERE definer ='user@host';
SELECT * FROM information_schema.routines WHERE definer ='user@host'; # stored procs and functions
SELECT * FROM information_schema.events WHERE definer ='user@host';

There are two important notes here, also.  First, note that the quoting is different.  Instead of 'user'@'host' these are expressed as 'user@host' (or 'user@%').  Also, unlike the grant tables, which really are tables, the information_schema "tables" are not tables at all, but rather representations of server internals, queryable via the SQL interface... and as such, they cannot be updated.  Each object you identify has to be dropped and redefined so its security context will be associated with a valid user, at which time the DEFINER shown in the respective table in information_schema will show the new values also.


Updating a column after expiration of specific time in MySQL

SET GLOBAL event_scheduler = ON; -- enable event scheduler.
SELECT @@event_scheduler;  -- check whether event scheduler is ON/OFF
CREATE EVENT e_store_ts  -- create your event
    ON SCHEDULE
      EVERY 24 HOURS  -- run every 24 hours
    DO
      UPDATE myschema.youtable set mycolumn='1'


Query involving TVF very slow on SQL Server 2008

Before SQL Server 2012 SP1, the permissions needed by the linked server login to read remote statistics were:

  In order to view the statistics object, the user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

From SQL Server 2012 SP1 onwards, the login only needs SELECT permission:

  Users must have permissions on all columns in the statistics object
  Users must have permission on all columns in a filter condition (if one exists)
  The table cannot have a row-level security policy.

More information in Distributed Queries – Remote Login Permissions and Execution Plans

Without access to statistical information, the optimizer makes a guess at the number of rows returned by the TVF (one row) and chooses nested loops on that basis.

If you cannot make the linked server login a member of one of the groups mentioned, the best workaround for the specific query in the question might be an OPTION (HASH JOIN) hint, or to read all the data from the remote server into a #temporary table.


How to get actually allocated tablespace size by each postgres database?

To get the disk space occupied by a database (nicely formatted):

SELECT pg_size_pretty(pg_database_size('mydb'))

Disk space occupied by a tablespace:

SELECT pg_size_pretty(pg_tablespace_size('mytblspace'))

Find out more about database object size functions in the manual:


Using psql with string variable that has two periods

Try an extra layer of quotes around your variables:

psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string="'Me'" -v version="'1.7.3'"


Cassandra query performs differently at different times

primary key (empID,uniqueID)

  This CF contains data for more that 5K row entires and and each employee contains minimum of 100K columns and the maximum of 1000K columns

That's WAY too many rows per partition.  My guess is that the query slowdowns happen when large partitions are queried.  It all depends on data cell value size and data width, but as a general rule, I would not model more than 10k-30k rows per partition.

To test this out, you could run nodetool tablehistorgrams on your table to gauge things like max cell count and partition size.  Then run your query against both small and large partitions with TRACING ON, and I'm sure you'll see the difference.

Basically, try reworking your model for smaller partitions.


How to have folders in the SSMS 2017 project?

You can create groups in the server registration view... not sure how that fits with your use of the word ‘project’, so whether it’s suitable for you - you need to decide.

Create or Edit a Server Group (SQL Server Management Studio)


MYSQL 8.0 server not found in Workbench directory in Windows 10?

MySQL server and MySQL Workbench both are installed in the default location ("Program Files" for 64bit apps or "Program Files x86" for 32bit apps). MySQL Workbench however only exists as a 64bit arch build.

Did you check the server path actually exists? If it does not you may have forgotten to install the new server. Simply run the MySQL Windows installer again and let it install the server.

About the left behind ProgramData content: that's probably a safety measure to avoid deleting your data unwantedly. If you don't need it anymore then just remove the 5.7 subfolder.


Selecting Consective rows with same Value & calcute the difference between timestamps of same value rows.Please Check What will b error in this query?

Right before

) AS t2

is

 ORDER BY  MSISDN,times

which does not make sense.  If times is to be a table, then it should not be JOINed (with a comma) to another table with an ORDER BY clause.  (Unless you put parens around the subquery.)

Or was that supposed to be a period, not a comma?:  MSISDN.times

When Error 1064 says "near", it means it.


SQL Server Docker image on Mac: Storing a Database

This is a known issue: mapping volumes on Macs isn't supported yet. You can follow that Github issue for more news, like when it's fixed.

Until then, no, people aren't doing anything more than simple testing with that combination of tools (Mac, Docker, Linux, SQL Server).

If you need to do real, productive development on a platform that's supported today, use:

The current supported version of SQL Server (2016, as of this writing)
Running on a currently supported OS (Windows, as of this writing)
If you insist on using a Mac as your hardware layer (as I do too), then the easiest way to bridge these technologies is a hypervisor like VMware Fusion or Parallels


Should we add extra 5 columns or build a separate table?

The decision does not depend on the number of columns you already have, but on whether those 5 columns do belong to what you are trying to model.
There is no rule that says: "if you have 10 columns, keep in same table; use a separate table when you reach 36 columns".
If those 5 columns are like a person's street, town, region, country, then no; they should be on a separate table "location" or similar.
If those 5 columns are like a person's 1st name, last name, 2nd last name, ID-number, and email address, then they should probably be there.
However, if your table already has 35 columns, then you probably haven't done much normalization to begin with and it looks like you're just stuffing everything on that table...
Update

first we have a table called association then based on that table we have the gps data linked to the assocition. In addition we also got alert tables linked to the association too. So association is the main table here. Each device also is table by itself but it will be linked to a association when and where it is being used.
…
Basically the association is just like a trip from a to b. So moving from a to b there is a number of gps data which is kept in this main table [?]. If in this trip there are alerts then is kept in the alert table. This 5 new column are indicating the status of the rfid devices which are linked to the main gps device. But in a trip not necessary to have 5 it can be less or even none too.

Assumptions:

For each trip there can be 0..N alerts
as I understand it, what you call association is the description of a journey
multiple RFID devices are used per journey or trip.
A GPS Device has 0..N RFID devices associated to it; in other words, each rfid device is associated to one GPS device (not sure I understand the logic of this, though; why are RFID devices used? what's the role of the GPS if you are )
Given that you are using a GPS device, I assume there will be multiple readings during a Journey, in combination with RFID readings (?)
When you say moving from a to b there is a number of gps data which is kept in this main table, I'm not sure I understand what info is stored, or why it is stored in the table called association / journey instead of in some associative table (given that there seems to be "a number of gps data" and not just "one gps datum per journey")

Given this, I would add your 5 columns to the RFIDStatus table, since This 5 new column are indicating the status of the rfid devices. Below is a first attempt at modeling something similar to what you might be modeling...
[     Journey    ]<——————[  Alert  ]
| fk_from        |       | fk_trip |
| fk_to          |
|                |—(from)—>[    Location    ]
|                |–—(to)—–>| long/lat/name… |
|                |
|                |<—————[    RFIDStatus    ]—————>[RFIDDevice]—————>[ GPSDevice ]
|                |      | fk_rfiddevice    |      | fk_gps   |      |           |
|                |      | fk_journey       |                        |           |
|                |      | datetime         |                        |           |
|                |      | *status details* |                        |           |
|                |<———————————————————————————[ GPSStatus  ]———————>|           |
|                |                            | fk_journey |
|                |                            | fk_gpsdev  |
|                |                            | datetime   |
|                |                            | latitude   |
|                |                            | longitude  |

However, as I mentioned in the comments, it all depends on what you're modeling. Please update your question with more details so other people can give you a better answer. Also, you should read something about database modeling so you start from a good basis and a good understanding of what's needed when modeling a database; it will also help you to ask better questions regarding database design.


MySQL: Performance issues in heavy multi-threaded environment

First, some cleanup.  Then I will provide the solution.

First, the UNIQUE(DateTime) obviates the need for any of the other unique keys.  They could be plain (non-unique) indexes.

Second, you have not specified a PRIMARY KEY for your InnoDB table.  This is naughty, but not the end of the world.  I recommend you promote UNIQUE(DateTime) to be PRIMARY KEY(DateTime).

Third; once you have done that, the rest of the indexes (plain or unique) are useless; get rid of them.

The query is a tough one to optimize -- it needs, shall we say, a "2-dimensional" index.  But indexes are only 1D.  WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>999999 would probably involve scanning all the rows with a date after the given one if an index starting with DateTime were used.

So, to make that variation run faster, you would need an index starting with AskHigh.  But then that is essentially useless if you ask AND AskHign > 0.0001.

PARTITIONing won't help because of the same issues -- pruning partitions with low values of DateTime or AskHigh is no better than having an index to avoid it.

With only 4GB of RAM, 3G is too much for the buffer_pool.  Lower it to 1500M.  Swapping is terrible for performance of MySQL.

For further discussion, please provide

EXPLAIN SELECT ...
the size of the table


"SQL1042C An unexpected system error" when creating a database

Try creating your database in a different directory: db2 create db orbeon on /datadir dbpath on /dbdir, where /datadir and /dbdir are directories where whoever runs the db2sysc process has full access.


Migration of SQL Server to Oracle Database

Global temporary tables in Oracle are very different, as they have a permanent logical definition and transient storage, so practically the reverse of your situation.

I would look at trying to avoid temporary tables at all, in favour of Common Table Expressions (WITH clauses). They are very powerful stuff and can transiently use temporary disk space if it aids performance.

Otherwise you'll have to manually code unique table names etc -- it'll be unpleasant to support in future.


Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

NOTIFICATION is emptied fast by means switching in an empty table.
NOTIFICATION is immediately available for new INSERTs
The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
Dropping the old version of NOTIFICATION does not interfere with new INSERTs
NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!


(Indexed views aside, of course.)

A view is not materialized - the data isn't stored, so how could it be sorted? A view is kind of like a stored procedure that just contains a SELECT with no parameters... it doesn't hold data, it just holds the definition of the query. Since different references to the view could need data sorted in different ways, the way that you do this - just like selecting from a table, which is also an unsorted collection of rows, by definition - is to include the order by on the outer query.

Also to give a little insight into the history. You could never put ORDER BY in a view, without also including TOP. And in this case the ORDER BY dictated which rows were included by TOP, not how they would be presented. It just so happened that in SQL Server 2000, if TOP was 100 PERCENT or {some number >= number of rows in the table}, the optimizer was fairly simplistic and it ended up producing a plan with a sort that matched the TOP/ORDER BY. But this behavior was never guaranteed or documented - it was just relied upon based on observation, which is a bad habit. When SQL Server 2005 came out, this behavior started "breaking" because of changes in the optimizer that led to different plans and operators being used - among other things, the TOP / ORDER BY would be ignored completely if it was TOP 100 PERCENT. Some customers complained about this so loudly that Microsoft issued a trace flag to reinstate the old behavior. I'm not going to tell you what the flag is because I don't want you to use it and I want to make sure that the intent is correct - if you want a predictable sort order, use ORDER BY on the outer query.

To summarize and just as much to clarify a point you made: Microsoft didn't remove anything. They made the product better, and as a side effect this undocumented, non-guaranteed behavior became less reliable. Overall, I think the product is better for it.


How can I filter an extended event session by is_cache attribute?

You just need to specify is_cached, without prefixing it with "sqlserver.":

WHERE is_cached = 0


How do you store tabular data in an RDBMS?

Arbitrary data can be put into a RDBMS even if, as the word might suggest, some relation should be applied.

Some points are obvious: every table must have a unique name, any column must have a unique name. For datatype you can use everywhere a VARCHAR. The best way is to analyse the table requirements and use the appropriate datatype for each column, this is useful for space management and query.

For query: in order to speed up the query on your data you have to apply a partitioning (and, if the database technology can support it, subpartitioning). The efficient use of partition and subpartitioning can speed up all DML operations if you can apply to every SELECT statement a WHERE clause based on the partition key. If you want also to use parallel access try to split different partitions across different disks accessed by different disk controllers.

Moreover many RDBMS allow a limited number of columns per table.

Finally, this is a very theoretical answer, too many details are missing. Although you can consider to leave the RDBMS techonology and adopt a NoSQL database. Probably, with the info supplied, a columnar nosql database is the best choice for you: HyperTable or HBase for example. This document is a must read for NoSQL Databases


Unable to connect to Amazon RDS instance

The problem was solved by opening the "Firewall" under Security Groups in the AWS Dashboard to incoming connections from my computer's IP.


The query with NOT LIKE is not using any index because the optimizer decides that it is cheaper to do a sequential scan. Nothing unexpected here, it is probably much cheaper.

The condition user_code LIKE 'TE%' will probably keep a very small number of rows (say 1%), so it is good to find this small number by using an index.

But the query with user_code NOT LIKE 'TE%' will probably return a big number of rows (99% if LIKE returns 1%) so it is easier to do a sequential scan and just remove the 1% afterwards. If an index was used, it would have to do the index seek and then still go and read (that 99% of rows) from the table anyway, so almost the entire heap.


ASPState Log file growing tremendously - SQL Server 2008

99% of the time, the asp.net session state database does not warrant FULL recovery and transaction log backups. In fact, it usually doesn't warrant any backups at all. I'd favour a script to recreate it over taking backups.

NB: Be wary of folk sneaking persistent objects in to your state database. Lock 'em out.

If your SQL Server isn't clustered, you have the option of targeting tempdb for state storage as an alternative to a persistent database, so it's treated as truly disposable.

Assuming your usage is typical and recovery of the data isn't required either:

Switch to SIMPLE recovery model.
Follow Kimberly Tripp's suggested route for shrinking the log.

Or, if you aren't clustered go the tempdb route and drop your state database, by re-running aspnet_regsql with the option -sstype t.


Distributing reads is easy; distributing writes is not. In SQL Server, most availability technologies require a specific node or database to be the "master" where all writes go.  Some possible options:

Merge Replication
Peer-to-Peer Replication
Transactional Replication with Updatable Subscribers (deprecated)

What is "best"? That's not really the kind of question we can answer here unless you get much, much more specific about what you mean by "best."


You could create a partial index:

CREATE INDEX ON employee_employee (year, jurisdiction_id) WHERE ual IS NOT NULL;


Well, I think the questions is not well formulated, but as far as I got it:

You have a table containing two columns user_id and company_id
You have set the user_id to be AUTO_INCREMENT (so it's a PRIMARY KEY)
You are willing to change this column to user_id + "_" + company_id to make it UNIQUE

Answer: you're doing it wrong

Both data must not be concatenated.

Instead, what you want is a (user_id, company_id) UNIQUE KEY, which can be done with an ALTER TABLE my_table ADD UNIQUE INDEX user_company (user_id, company_id).

Thus, the way you want your AUTO_INCREMENT to work is not clear. IMO, you should remove it from the user_id, and add it on a new dedicated column id. Then, when you INSERT/UPDATE in that table, you'll have to know the user_id you're inserting/updating, which makes sense since that user_id is no longer UNIQUE to identify a row.


Using dynamic database name for DML in trigger

What a brittle solution. Does the return value of sfn_GetSecondDbName() really change that often? Where does its value come from? Would be much easier to hard-code the database in the trigger, and change the trigger when that changes, rather than change the function and try to "save work" by not having to update the trigger.

Your current approach will require nested levels of dynamic SQL, and this becomes more complicated because dynamic SQL can't see the scope where inserted is, so additionally you'll have to dump those contents into a #temp table before you can access them from dynamic SQL. Ugly, ugly, ugly.

ALTER TRIGGER [dbo].[CTR_Production_InsertRecordInDatabase2]
ON [dbo].[Table1]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #inserted(column1 INT); -- guessing
  INSERT #inserted(column1) SELECT column1 FROM inserted;

  DECLARE
    @db  SYSNAME = dbo.sfn_GetSecondDbName(),
    @cmd NVARCHAR(320),
    @sql NVARCHAR(MAX) = N'INSERT dbo.pDM_Production_RecordInfo(column1)
      SELECT column1 FROM #inserted;';

  SET @cmd = @db + N'.sys.sp_executesql';
  EXEC @cmd @sql;
END
GO

(Also note that nothing will get inserted in the event of a delete, since inserted will be empty.)

Better yet, store this data in a staging table that is in this database, then create a backup job that moves the data to the other database. That way the trigger doesn't have to do all this dynamic dynamic dynamic SQL, and the transaction that called it isn't affected by any delays there or communicating with the other database (think about the fact that another database could be offline, single user, detached, or the customer could have fat-fingered the name when they changed it - do you want any of these things to block DML on the primary?).


How to find admin user of pluggable database in Oracle 12c up

CREATE PLUGGABLE DATABASE

  ADMIN USER

  Use this clause to create an administrative user who can be granted
  the privileges required to perform administrative tasks on the PDB.
  For admin_user_name, specify name of the user to be created. Use the
  IDENTIFIED BY clause to specify the password for admin_user_name.
  Oracle Database creates a local user in the PDB and grants the PDB_DBA
  local role to that user.

The admin user is just a local user with the PDB_DBA role.
Example:

SQL> CREATE PLUGGABLE DATABASE mypdb ADMIN USER balazs IDENTIFIED BY Oracle123 FILE_NAME_CONVERT=('pdbseed','mypdb');

Pluggable database created.

SQL> alter pluggable database mypdb open;

Pluggable database altered.

SQL> select grantee from cdb_role_privs where con_id = (select con_id from cdb_pdbs where pdb_name = 'MYPDB') and granted_role = 'PDB_DBA';

GRANTEE
--------------------
BALAZS
SYS


Possible to use first CTE as filter for second CTE?

Looking into my crystal ball, your query might work faster by orders of magnitude like this:

SELECT i.*, rr.urllist
FROM   vw_image i
LEFT   JOIN LATERAL (
   SELECT string_agg(r.url, ',') AS urllist
   FROM   resourceonpage rop
   JOIN   resource r ON r.id = rop.pageid
   WHERE  rop.siteid   = 2294  -- or: = i.siteid
   AND    r.resourceid = i.resourceid
   ) rr ON true
WHERE  i.siteid = 2294
AND    i.childtype = 'i'
ORDER  BY i.id
OFFSET 1 ROWS
FETCH  NEXT 50 ROWS ONLY;  -- or just: OFFSET 1 LIMIT 50

You don't need CTEs here, those will only slow you down.

I use a LATERAL join to use indexes on resourceonpage and resource and aggregate in the subquery right away. About LEFT JOIN LATERAL ... ON true:

What is the difference between LATERAL and a subquery in PostgreSQL?

Looking even deeper into my crystal ball, the best indexes might be:

CREATE INDEX ON vw_image (siteid, id) WHERE childtype = 'i';
CREATE INDEX ON resourceonpage (siteid, pageid);
CREATE INDEX ON resource (resourceid, id, url);

Appending url is only useful if you get index-only scans out of it.

An alternative approach would be to use parentheses in the FROM list like demonstrated here:

How to include missing data for multiple groupings within the time span?

But, according to my crystal ball (and an educated guess) the LATERAL version will be faster. There is not enough information in the question to give you more than educated guesses.


MySQL: Error 1114. table is full. can't drop the table

wouldn't that whole file needed to be loaded into RAM first? I would recommend splitting it up into chunks not bigger that 3GB each before submitting them.

Also, this post suggests that "setting buffer pool a bit larger than your database size" was a good practice.

It looks to me that your whole set up is not quite up to the task.


How are DB locks tied to connections and sessions?

If this is true, what benefits does this have over row-/page-level locks?

You may have misunderstood what he meant.

A client connection and a session are essentially the same thing.  A session starts after a client authenticates and ends when the client disconnects.  (These are also referred to in the documentation as threads; each connection/session typically has a dedicated server thread servicing it.)

All locks in MySQL -- row locks, table locks, and named locks (which are advisory mutexes, not data structure locks) -- are always associated with, and held by, the session that created the locks.

The following statement is taken from the MySQL documentation for table-level locks, but it is true of all locks:

  A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

If not explicitly released, all locks are always automatically released when the session holding the locks ends.  A session ends when the client disconnects, the client thread is killed, or times out due to expiration whichever of the interactive_timeout or wait_timeout timers applies to the session.

So I suspect that in these senses, it could be said that locks are "scoped to" the session; however, locks are always global in their scope and impact.  If that weren't the case, there wouldn't be much point in locking -- which exists precisely to keep other sessions from doing things that conflict with what your thread is doing.

  InnoDB supports multiple granularity locking which permits coexistence of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table locks in InnoDB. The idea behind intention locks is for a transaction to indicate which type of lock (shared or exclusive) it will require later for a row in that table.
  — http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html

This is referring to the SELECT ... LOCK IN SHARE MODE (obtains "Intention Shared" row locks) and SELECT ... FOR UPDATE (obtains "Intention Exclusive" row locks) statements, which do allow you to deliberately obtain locks on rows... however, to be precise, these are not at the session level -- they are even more granular: they're a the transaction level, which is a subset of a session.  As soon as you COMMIT or ROLLBACK the current transaction, these are released.  Of course, if your session terminates unexpectedly, the transaction rolls back, which releases the locks also.


PostgreSQL: simple equality query returning no results for indexed columns

There are hash indexes on citing_id and on cited_id, neither of which
  has any NULL values (confirmed), but queries selecting on equality in
  these columns are returning zero results no matter what.

The index is probably corrupted. Hash indexes are only WAL-logged since PostgreSQL 10. In previous versions, they don't have that mechanism that makes them persist correctly across unclean shutdowns.

The doc on CREATE INDEX for 9.5 has this warning:

  Hash index operations are not presently WAL-logged, so hash indexes
  might need to be rebuilt with REINDEX after a database crash if there
  were unwritten changes. Also, changes to hash indexes are not
  replicated over streaming or file-based replication after the initial
  base backup, so they give wrong answers to queries that subsequently
  use them. Hash indexes are also not properly restored during
  point-in-time recovery. For these reasons, hash index use is presently
  discouraged.

Consider upgrading to PostgreSQL 10, and in the meantime, REINDEX the hash indexes.


Active Expensive Queries and Recent Expensive Queries Meaning

It depends on what column you sort by. To dig deeper, I suggest you read the source code that these are on. I blogged about AM a while ago, here is a part from that blog post:
The “Recent Expensive Queries” pane:
This shows the most expensive queries, based on what column you sort on, executed since the last snapshot. If you have, say, a 10 second snapshot interval, you will only see the queries executed during these 10 seconds. AM uses a procedure named #am_get_querystats to collect the information. There are a few things going on inside this procedure, but at the most basic level, it uses sys.dm_exec_query_stats and sys.dm_exec_requests to get queries from cache and currently executing queries. It then does some processing and store the result in temp tables so we later can sort on different columns depending on what metric we are interested in. I suggest that you spend some time with the source code if you want to dig deeper.
The “Active Expensive Queries” pane:
This is very straight forward. It executes a query which uses sys.dm_exec_requests joined to a few other DMVs.
Here's a link to my blog post: http://sqlblog.karaszi.com/explaining-activity-monitor/


Some records are missing while selecting from table

You didn't state which version of SQL Server you are using, but I'll assume it is 2005 or later, and therefore, will suggest using a CTE . And, as Skagalak mentioned, I've rewritten the joins.

I wasn't sure whether you wanted the max value from the whole WORKITEM table or if you wanted to group it by PROCESS_INSTANCE_ID. The example below groups the  MAX(WORKITEM_ID). If you don't want that, just remove  the GROUP BY clause from the CTE.

CAVEAT: I've checked for syntax, but since I couldn't test against a data set I give no guarantees that this will give the desired results. It is merely an example of how you might want to structure the query.

;WITH cte AS (SELECT MAX(WORKITEM_ID) AS max_workitem_id, PROCESS_INSTANCE_ID
                  FROM WORKITEM
                  GROUP BY PROCESS_INSTANCE_ID
                 )
    SELECT WI.WORKITEM_ID AS WORKITEMID,
           PI.PROCESS_INSTANCE_ID PIID,WS.WS_LABEL AS WSNAME
    FROM WORKITEM AS WI
        INNER JOIN PROCESSINSTANCE AS [PI] ON WI.PROCESS_INSTANCE_ID = [PI].PROCESS_INSTANCE_ID
        INNER JOIN TASKASSIGNMENT_V4 AS TA ON TA.PROCESS_INSTANCE_ID = [PI].PROCESS_INSTANCE_ID
        INNER JOIN SDI_WIDGET_WORKSTEP AS WS ON WS.WS_NAME = WI.WORKSTEP_NAME
        INNER JOIN cte ON WI.WORKITEM_ID = cte.max_workitem_id AND cte.PROCESS_INSTANCE_ID = WI.PROCESS_INSTANCE_ID
    WHERE [PI].status IN ('PI_ACTIVATED','PI_COMPLETED')
          AND WI.status IN ('I_ASSIGNED','I_AVAILABLE')


Running loadjava with Windows authentication

You should not use SYS for this.

With a regular user, you can simply provide the username/password@address.

To do this with SYS, without the password:

Create the class:

C:\Users\Administrator>copy con Hello.java
public class Hello
{
    public static String world()
    {
    return "Hello World";
    }
}
^Z
        1 file(s) copied.

C:\Users\Administrator>type Hello.java
public class Hello
{
    public static String world()
    {
    return "Hello World";
    }
}

C:\Users\Administrator>set JAVA_HOME=O:\oracle\base\product\12.2.0\dbhome_1\jdk

C:\Users\Administrator>set PATH=%JAVA_HOME%\bin;%PATH%

C:\Users\Administrator>javac Hello.java

C:\Users\Administrator>jar cvf Hello.jar Hello.class
added manifest
adding: Hello.class(in = 273) (out= 209)(deflated 23%)

Connect to pluggable database without password:

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 9 16:18:34 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=pdb1;

Session altered.

Load the class:

SQL> exec dbms_java.loadjava('C:\Users\Administrator\Hello.jar');

PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type, status, created
     from dba_objects where object_name = 'Hello';

OWNER      OBJECT_NAM OBJECT_TYPE             STATUS  CREATED
---------- ---------- ----------------------- ------- ---------
SYS        Hello      JAVA CLASS              INVALID 09-NOV-18

SQL> alter java class "Hello" compile;

Java altered.

SQL> select owner, object_name, object_type, status, created
     from dba_objects where object_name = 'Hello';

OWNER      OBJECT_NAM OBJECT_TYPE             STATUS  CREATED
---------- ---------- ----------------------- ------- ---------
SYS        Hello      JAVA CLASS              VALID   09-NOV-18

Clean up the mess:

SQL> drop java class "Hello";

Java dropped.


Syntax to find text in string, then pull out following text until a comma is identified?

If you're using SQL Server, you can use this.

If you're not, I'll delete my answer.

DECLARE @s NVARCHAR(100) = N'Lot D, Plan VIP12345, Section 70, Suchandsuch District';

SELECT @s,
        SUBSTRING(@s AS string,
                 CHARINDEX('VIP', @s),
                 CHARINDEX(',', @s, CHARINDEX('VIP', @s))
                 - CHARINDEX('VIP', @s)
                 ) AS sub

How does this work?

We can use substring and the results of charindex to get just the parts of the string we're interested in.

Charindex is a better choice here than Patindex because it accepts a 3rd argument. The third argument tells charindex at which point in the string to start searching.

        SUBSTRING(@s, --The string
                 CHARINDEX('VIP', @s), --Charindex of VIP
                 CHARINDEX(',', @s, CHARINDEX('VIP', @s)) --Charindex of the first comma _after_ VIP
                 - CHARINDEX('VIP', @s) --Subtract the position of VIP
                 )

Which gives us:

string                                                  sub
Lot D, Plan VIP12345, Section 70, Suchandsuch District  VIP12345

Hope this helps!


Error deleting SQL Server local database

You simply need to make sure you have stopped the database before deleting it.

You can check the current state by running this:

SqlLocalDb info "MyInstance"

and stop it by running :

SqlLocalDb stop   "MyInstance"

Once stopped you should be able to delete the instance.

This is a nice guide on installing and deleting express databases.


SQL Server Always on DBCC Check DB and Backups startegey

Brent Ozar has a good article regarding this here: Where to Run DBCC on Always On Availability Groups
Specifically these parts of the article are relevant to your question (his example is kind of the reverse of yours, but the same principal applies in regards to the importance of running DBCC CHECKDB on both replicas):

We could be backing up corrupt data every night as part of our full
backups.  If we experienced corruption on the primary server, and it
tried to take care of business with automatic page repair by fetching
a copy from the secondary server (which also happened to be corrupt),
we’d be screwed.
The moral of the story: with Availability Groups, we need to run DBCCs
on whatever server is doing our full backups, too.  Doing them on the
primary database server isn’t enough.  In a perfect world, I’d run
them regularly on any server that could serve a role as the primary
database.  Automatic page repair can only save your bacon if a replica
is online and has a clean copy of the page.

In short: By backing up the Primary replica without running DBCC CHECKDB on it, you could be backing up corrupt data without realizing, especially more so when you've experienced a failover event that caused your replicas to switch places in the interim, for your specific scenario.


My crystal ball says the two queries end up using different query plans. You can verify by running EXPLAIN on each. More useful details with EXPLAIN (ANALYZE, BUFFERS).

The subquery gets the greatest id with time_track more than 5 minutes before the one of the greatest id overall in table eco.tracks. Assuming a strong positive correlation between id and time_track, humans get a rough idea of the work to be done here: it only concerns the (probably very few) latest rows within the last 5 minutes in the table. But Postgres has no idea at the time of planning the query.

(Aside: It may be a lingering logic bug to assume that serial IDs are in sync with timestamps in the column time_track. Not enough information to say more.)

For the first (slow) query, Postgres has to prepare a plan that best copes with any value that might come out of the subquery. That may very well be a sequential scan, which turns out to be a bad choice for only the few latest rows.

For the second (fast) query, Postgres knows from the given value what to expect. It can start by identifying the few relevant rows with an index or bitmap index scan on an index on (id) which undoubtably exists (again assuming that's the PK). The rest is peanuts.

Possible solutions

There are many ways. Anything to get Postgres to pick the appropriate query plan ...

1. Two separate queries

Dead simple. Build the second query with the actual value returned from the first. The downside: two round trips to the db server collecting two times network latency and varying overhead.

2. Dynamic SQL

CREATE OR REPLACE FUNCTION f_latest_tracks(_intv interval = '300 seconds')
  RETURNS SETOF eco.tracks AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT DISTINCT ON (track) *
 FROM   eco.tracks
 WHERE  id > $1
 ORDER  BY track, time_track DESC'
USING (
   SELECT id
   FROM   eco.tracks
   WHERE  time_track < (SELECT time_track - _intv FROM eco.tracks ORDER BY id DESC LIMIT 1)
   ORDER  BY id DESC
   LIMIT  1
   );

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_latest_tracks();
SELECT * FROM f_latest_tracks('10 minutes');

Why? The manual about Executing Dynamic Commands:

  The important difference is that EXECUTE will re-plan the command on
  each execution, generating a plan that is specific to the current
  parameter values; whereas PL/pgSQL may otherwise create a generic plan
  and cache it for re-use. In situations where the best plan depends
  strongly on the parameter values, it can be helpful to use EXECUTE to
  positively ensure that a generic plan is not selected.

Downside: You need to create a server-side function introducing a dependency on  table eco.tracks. And you need to be comfortable with PL/pgSQL, of course.

Using a parameter default for conveniencse. See:

Add function parameter to SQL query WHERE clause


SSMS - How to do case-insensitive searches in Object Explorer

It is technically possible to do this case-insensitive filtering, though only due to a bug in SSMS. I don't have SSMS 2014 (version 12.x), but I did get it working in SSMS 17.9.1 and 18.0 Preview 6, connected to an instance with a default collation of Hebrew_100_BIN2.

Attempt Uno (no)

Seeing that the query uses LIKE, I figured I would try using single-character range wildcards (i.e. [ and ]). Passing in a pattern of [Tt][Ee][Ss][Tt] did not work, however, due to certain characters getting escaped. It seems that [, %, and _ all get wrapped in [ and ] to turn them into literals (i.e. no longer having special meaning).

Attempt Dos (Yes)

Seeing that the value used with the LIKE operator (i.e. the value entered into the UI) was concatenated into the query instead of passed in as a variable (i.e. tbl.name like N''%Test%'' instead of tbl.name like N''%'' + @_msparam_XX + N''%''), I started thinking, "Isn't there a reason why it's dangerous to do this with user input? Hmm, I wonder ..." And so I passed in a ' and it was escaped into ''. Normally this is exactly what should happen with a single-quote. However, this is being used in Dynamic SQL, so it's nested one-level deeper than where '' is properly escaped. A string within a string needs a single-quote to be escaped as '''', or double-escaped, because '' merely ends the string within a string.

For example, when filtering on "Tables", the generated code (the one line that matters) is:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Test%'')

If I had passed in "Te'st", the generated code would have been:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Te''st%'')

and yes, it does get an "unclosed quotation mark" error.

I tried adding a COLLATE clause, but all collations have underscores, so Latin1_General_100_CI_AS became Latin1[_]General[_]100[_]CI[_]AS. D'oh!

We can't pass in either [ or % without those being wrapped in [ and ], BUT, we can concatenate stuff onto the N'% string, such as the output of a function. And, we can bypass the escaping of the [ and % characters by passing them in as hex / VARBINARY values, and then CONVERT them back into NVARCHAR once inside.

So, when filtering on Stored Procedures, we are starting with normally generated code of:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1

In order to make this work, we need to pass in a string having the following structure (in place of "Test"):

' + CONVERT(NVARCHAR(MAX), 0x.....) AND 'x' <> '

We could use -- at the end instead of AND 'x' <> ', but the AND construct ensures that if there is additional parts of the query that following this part, and are on the same line, that it continues to function as expected.

To generate our "escaped" filter, we can use the following:

SELECT CONVERT(VARBINARY(MAX), N'[Tt][Ee][Ss][Tt]%'); -- must end with "%"
-- 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500

Pasting that hex value into our SQL Injection work-around, we get:

' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '

If we set our filter to the value directly above, the generated code will be:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%'' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND ''x'' <> ''%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'1'

And the generated code is passed into sp_executesql. The statement that is executed shows up in Profiler as:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N'%' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '%')

This technique works because 0xYYYY.... is not something that the parser is looking for. It's only looking for the literal values of [, _, and %, and we aren't passing those in. We are just passing in something that will be translated into those once the Dynamic SQL is executed.

But it doesn't need to be a VARBINARY string that is passed in. Any function that passes back a string will work. You just need to concatenate the ending of N'% with something, followed by what comes just before %'), hence: ' + {something_ending_with_%'} AND 'x' <> '. I could have used + NCHAR(91) + N'Tt' + NCHAR(93) + to represent [Tt], but that seemed a lot bulkier than a continuous VARBINARY literal. However, if you just want a %, then doing + NCHAR(37) + N' would be just fine. Remember to use NCHAR() and prefix literals with N since this is all NVARCHAR data.

Attempt Tres (Mucho Yes)

So, you don't want to have to create the [Tt][Ee][Ss][Tt] string each time, and convert it to VARBINARY. I get that. Oh, and you have no access to add / change objects in SQL Server. Ok. But you do have access to tempdb, right? If you can create a function in tempdb, then this can be even easier. Just execute the following:

USE [tempdb];
IF (OBJECT_ID(N'dbo.ObjectFilter') IS NOT NULL)
BEGIN
  DROP FUNCTION dbo.ObjectFilter;
END;

GO
CREATE FUNCTION dbo.ObjectFilter(@Name [sysname], @Filter [sysname])
RETURNS BIT
AS
BEGIN
  IF (@Name LIKE N'%' + @Filter + N'%' COLLATE Latin1_General_100_CI_AS_SC)
  BEGIN
    RETURN 1;
  END;

  RETURN 0;
END;
GO

Then set your filter to:

' AND tempdb.dbo.ObjectFilter(sp.name, N'Test') = 1 AND 'x' <> '

Generated code will be:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N'%' AND tempdb.dbo.ObjectFilter(sp.name, N'Test') = 1 AND 'x' <> '%')

You will have to adjust the column alias since tables uses tbl.name instead of sp.name. But now you can pass in any string and it will compare using a case-insensitive collation!!

And, this function will stick around until the instance is restarted. So you will only need to execute the above T-SQL every once in a while.

{ drops mic ... walks off stage } 😎

Additional Notes:

Keep in mind that this technique works only because there is a bug in SSMS. If that bug is ever fixed, then this work-around will likely stop working. Please see the "UPDATE" section at the bottom for links to the bug report I filed about this, as well as the enhancement suggestion I filed requesting a "case-insensitive" check-box, per filterable property.
From Randi's answer:

  Unless you find where the query is stored and change it, ...

There is no point in looking for the queries. They are coming from the DLLs that make up SSMS. I think C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.Management.Sdk.Sfc.dll might be one of them, but not sure.
From Bob's comment on the question:

  SSMS does not enforce case-sensitivity unless server collation is case-sensitive.

Well, it depends. There are two levels of collations — instance-level and database-level — and the one that matters depends on what meta-data someone is trying to filter. Instance-level collation affects filtering instance-level meta data (e.g. Logins, Linked Servers, etc) and meta data in system databases. Database-level collation affects filtering user database meta data (e.g. Users, Tables, etc). In a Contained / Partially-Contained database, filtering the database-level meta data will always use the collation: Latin1_General_100_CI_AS_WS_KS_SC.

This separation of levels explains the following finding noted in Randi's answer:

  It also appears to not be possible on CS DB's on CI Servers:

From Elaskanator's comment on the question:

  Currently it is set to a binary collation (Latin1_General_BIN) which is case-sensitive.

Just FYI: this is, unfortunately, a very common misconception, but No, Binary Collations are not Case-Sensitive

UPDATE

I have submitted the following feedback items to Microsoft:

BUG: SSMS: Object Explorer Filtering allows for SQL Injection (oops)
SUGGESTION: SSMS: Allow forcing case-insensitive matching in Object Explorer filters


How to write a query that counts rows by date?

That SQL dialect is from Oracle but this answer will be for MySQL.

If datum is DATE

select count (*),datum date from tx_feeder
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by datum;

If datum is DATETIME or TIMESTAMP

select count (*),DATE(datum) date from tx_feeder
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by DATE(datum);

If you are looking for a grand total to be included, do WITH ROLLUP

select count (*),datum date from tx_feeder
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by datum WITH ROLLUP;

or

select count (*),DATE(datum) date from tx_feeder
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by DATE(datum) WITH ROLLUP;


How can I identify and fix bottlenecks in an Oracle11gR2 import

Depending on the edition (enterprise, standard, express, etc.), the licensed options (particularly the Performance and Tuning Pack), and whether you have installed Statspack, I would start by taking an AWR or Statspack report for both system when they are executing a similar workload and compare them.  If you are licensed to use the AWR (note that querying the AWR tables violates your license agreement if you aren't), use an AWR report.  Otherwise, assuming you (or the prior DBA) installed Statspack, use that.  Otherwise, you'll have to install Statspack in both systems.

If your guess is correct, I/O is the bottleneck, and the problem is that the I/O subsystem on the new server is slower than the I/O subsystem on the old server, you would expect that the top wait events in the report on the new server would be primarily I/O, that the amount of I/O would be consistent across the reports, and that you'd see that individual I/O operations on the various tablespaces and data files were consistently slower on the new system.  Otherwise, comparing the reports should tell you what the bottleneck is (though an AWR report has a ton of information and a comprehensive discussion of interpreting an AWR report is a book not a forum post).


How to log all SQL queries for a single connection or user or client host?

I don't know how to do it for a client host, but for a single database user, you can use this (as a superuser):

ALTER ROLE someone SET log_statement TO 'all';

Which will then turn on log_statement every time that that someone connects in the future.

For a single connection which is cooperating with you, you could create this function (as a superuser):

create or replace function set_log_statement() returns void as $$
 set log_statement='all'
$$ language sql security definer ;

And then have the desired single connection invoke this function to turn on logging for itself.


How to get value from subquery in mysql?

But as i said you don't need in article
title and image, as they are already existent in books and you can always join the books table
But you would do this so
INSERT INTO article(title,price,cart_id,book_id,image_id)
VALUES ('Book1',20.00,387,
(select b.id from books b  where b.title =  'Book1' limit 1),
(select b.image_id from books b where b.title =  'Book1' limit 1))


Methods of speeding up a huge DELETE FROM  with no clauses

What you can do is batch deletes like this:

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable

Where xxx is, say, 50000

A modification of this, if you want to remove a very high percentage of rows...

SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..some condition..

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable


I need help resolving a retrieval problem

varbinary can be used directly in a SQL range:

SELECT d.ADDRESS,
       i.country,
       i.area,
       i.city
FROM download AS d
INNER JOIN ip_lookup AS i
   ON d.ADDRESS BETWEEN i.start_ip AND i.end_ip
      AND length(d.ADDRESS) = length(i.start_ip)
WHERE d.FILENAME IS NOT NULL
  AND d.country IS NULL;

The lengths are a double check they are comparing same IP versions. It may not be needed.

To ensure the ip_lookups are quick

CREATE INDEX ip_range ON ip_lookup (start_ip, end_ip)

If you aren't using the id of ip_range maybe the above should be the primary key.


Connection Error while connecting SQL Developer with DB2

An error like this might be returned if you will try to connect to a port which is actually not the port DB2 server listens on.
E.g. if I would, by mistake, try to connect to port 22 (ssh) I will get the same error:

$ java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:22/SAMPLE -user db2v111 -password passw0rd

[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:22/SAMPLE -user db2v111 -password ********
[jcc][10512][13714]Failed to create connection.
  SQLCODE: -4499
  SQLSTATE: 58009
  Message: [jcc][t4][2034][11148][3.72.52] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
A DRDA Data Stream Syntax Error was detected.  Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

You can check the port Db2 listens on with:

 db2 get dbm cfg | grep SVCENAME
 TCP/IP Service name                          (SVCENAME) = 60111

Db2 will listen on this port if you have DB2COMM=TCPIP set. On there server side you can verify that e.g. with lsof:

$ lsof -p <pid_of_db2sysc> -iTCP -sTCP:LISTEN -a

(all sockets in listening state and -a to AND selections)
e.g.

$ lsof -p 10661  -iTCP -sTCP:LISTEN -a
COMMAND   PID    USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
db2sysc 10661 db2v111    3u  IPv4 39727390      0t0  TCP *:60111 (LISTEN)

If the server listens only for SSL connections (only SSL_SVCENAME set in dbm cfg) then you can test that connection to Db2's SSL port does work e.g. with OpenSSL client:

$ openssl s_client -connect localhost:61111
CONNECTED(00000003)
depth=0 C = CA, ST = ON, L = myLocation, O = myOrganization, OU = myOrganizationUnit, CN = DB2INSTANCE
verify error:num=18:self signed certificate
verify return:1
depth=0 C = CA, ST = ON, L = myLocation, O = myOrganization, OU = myOrganizationUnit, CN = DB2INSTANCE
verify return:1

If it is OK then test from Java:

~/sqllib/java/jdk64/bin/java -cp ~/sqllib/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true\; -user db2v111 -password XXXX

[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true; -user db2v111 -password ********

[jcc][10512][13714]Failed to create connection.
  SQLCODE: -4499
  SQLSTATE: 08001
  Message: [jcc][t4][2030][11211][4.25.1301] A communication error occurred during operations on the connection's underlying socket, socket input stream,
or socket output stream.  Error location: Reply.fill() - socketInputStream.read (-1).  Message: com.ibm.jsse2.util.h: PKIX path building failed: java.security.cert.CertPathBuilderException: unable to find valid certification path to requested target. ERRORCODE=-4499, SQLSTATE=08001

not quite there - you need also sslCertLocation that points to certificate of the database:

~/sqllib/java/jdk64/bin/java -cp ~/sqllib/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true;sslCertLocation=/home/db2v111/db2ssl/mydbserver.arm;" -user db2v111 -password XXXX

[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true;sslCertLocation=/home/db2v111/db2ssl/mydbserver.arm; -user db2v111 -password ********

[jcc][10516][13709]Test Connection Successful.

DB product version = SQL110144
DB product name = DB2/LINUXX8664
DB URL = jdbc:db2://127.0.0.1:61111/SAMPLE
DB Drivername = IBM Data Server Driver for JDBC and SQLJ
DB OS Name = Linux


Do SQL Server Express Editions support CLR procedures?

Yes, the Express editions do support CLR integration. The link is for the latest version, but if you switch to 2008 you will see it is supported there too.


The documentation explains it perfectly:

  The total value is the value of the statistics since instance
  startup. The delta value is the value of the statistics from the
  BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT
  view.

So in this case, the PHYSICAL_READ_BYTES_TOTAL is the number of physical reads (in bytes) since the instance was started, and PHYSICAL_READ_BYTES_DELTA is the number of physical reads (in bytes) since the previous snapshot (SNAP_ID).


I've done this in stages using CTEs so that you can see how it's done as the queries progress. Each CTE adds a column in the output in order to show you progress.

It's pretty much self-documenting with the CTE names, to be honest.

with lags as (
  select player,
         dt,
         is_winner,
         lag(is_winner) OVER (partition by player ORDER BY dt ASC) as prev_is_winner,
         expected
  from matches
),
     group_changes as (
       select lags.*,
              case
                when prev_is_winner <> is_winner or prev_is_winner is null
                  then 1
                else 0
                end as is_new_group
       from lags
     ),
     groups_numbered as (
       select *,
              sum(is_new_group)
                  over (partition by player order by dt, is_winner desc) as streak_group
       from group_changes
     ),
     expected_in_groups as (
       select groups_numbered.*,
              row_number()
                  over (partition by player,streak_group
                    order by dt asc, streak_group asc) - 1 as expected_unsigned
       from groups_numbered
     )
select expected_in_groups.*, case when is_winner = 't' then expected_unsigned else expected_unsigned * -1 end as actual
from expected_in_groups
order by player asc, dt asc;

DB Fiddle Link (I added an extra row just to make sure it was working at a certain point)

Basically:

lags CTE: use LAG() to get the previous result relative to the current row.
group_changes CTE: Detect whether the previous streak, whether win or loss, has ended
groups_numbered CTE: Give each streak a number
expected_in_groups CTE: Number the rows in the group
Final select: negate the loss streaks

.

+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
| player |     dt     | is_winner | prev_is_winner | expected | is_new_group | streak_group | expected_unsigned | actual |
+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
| A      | 2019-01-01 | t         |                |        0 |            1 |            1 |                 0 |      0 |
| A      | 2019-01-03 | t         | t              |        1 |            0 |            1 |                 1 |      1 |
| A      | 2019-01-04 | t         | t              |        2 |            0 |            1 |                 2 |      2 |
| A      | 2019-01-09 | f         | t              |        0 |            1 |            2 |                 0 |      0 |
| A      | 2019-01-10 | f         | f              |       -1 |            0 |            2 |                 1 |     -1 |
| A      | 2019-01-11 | f         | f              |       -2 |            0 |            2 |                 2 |     -2 |
| A      | 2019-01-15 | t         | f              |        0 |            1 |            3 |                 0 |      0 |
+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+


Storing a very large matrix on Postgresql

I would create a table with three columns:

create table distances
(
  from_zip_code int not null,
  to_zip_code int not null,
  distance numeric not null,
  primary key (from_zip_code, to_zip_code)
);

If you want, you can prevent storing the reversed combination through a unique index.

If your ZIP codes aren't numbers, then obviously you need to use text or varchar for those two columns.

While the table is moderately large (9550 * 9550 rows), access through the primary key is typically pretty much constant in time.


We can't say why, since you posted the SSIS error message. A maintenance plan is an SSIS package. SQL Server returned an error message to SSIS, and SSIS show you something else. You need to hunt down the SQL Server error message. Check the maintenance plan report file, assuming you have such. And the maintenance plan history table as well.


date not inserting - shows 0000-00-00 after insert

Put the date into quotation marks:

INSERT INTO DDD(datecolumn, open, high, low, close, volume)
VALUES('2011-05-26', 12.09, 13.31, 12.05, 13.09, 1293441)

MySQL will have silently done some mathematics with 2011-05-26 and got its knickers in a twist.

It does warn you though:

mysql> insert into d VALUES(2011-05-26);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'datecolumn' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Without using quotes, MySQL is essentially inserting the integer 1980 ( which is 2011 minus 05 minus 26) as a date, as it treats it as mathematics:

mysql> select 2011-05-26;
+------------+
| 2011-05-26 |
+------------+
|       1980 |
+------------+
1 row in set (0.00 sec)

mysql>


Can a subscriber be a publisher at same time

Yes, you can achieve the goal and implement replication topology. What I understood from your requirements is depicted in the diagram.

To do this follow these steps:

Enable distribution on Server 1.
Create replication, add articles and choose server 1 as publisher and server 2 as subscriber.
Once articles are replicated, enable distribution on server 2.
Follow step 2 for servers 2 and 3.


Should ERD derived attributes be included in logical tables?

What you can do is not always appropriate for the various usages of your relational model. If you were creating a data warehouse in order to analyze customer sales then derived attributes would be appropriate.  I have done this for a summary table for a reporting tool.  The query would have joined upwards of thirty tables with many aggregations such as sums and show all values an entity has had.  A summary table, refreshed daily, listed derived attributes was a great solution for reporting.

For an online transaction processing database using derived attributes is not always the best solution.

For example: now your total is price * quantity

Next month management decides to implement a discount of 10% for customers who order more than $1000 in a calendar year.   Your total column now looks inflexible.

Unfortunately the things you can say today that "will never change" such as Total = price * quantity are really an example of business logic.  Business logic can change anytime in unexpected ways.

To continue with your example....if management institutes a discount and you have a customers table, orders table then all you have to do is add a discount table.  Then you can create a view which encapsulates the business logic of the day to derive total sales.  When the logic changes you can change the view much easier than recalculating the derived attributes that are fixed in a table.

And if you really want to be prepared you could store the changes in the business logic in a table in the database and cover off "Who, What, why".  So if the Bob the Manager offers a discount and five years later Sue, the new manager, says "When did we start offering discounts and who authorized it?" you are a database star.


how to pull data source information on data-driven subscriptions

I kept poking around and... found it.  For the future reference of others:

select
  Catalog.Name
, Subscriptions.[Description]
from
  Subscriptions
  join DataSource
    on Subscriptions.SubscriptionID = DataSource.SubscriptionID
  join Catalog
    on DataSource.Link = Catalog.ItemID


Transferting large SQL Databases to Azure (>300MB)

You should use the Azure migration wizard instead of using SSMS create script.

How to: Use the SQL Azure Migration Wizard

Behind the scenes it uses bcp to transfer your data to Azure.

For the future, SSMS is not meant to handle large .sql files. Even a 1 or 2 MB file will crash SSMS. Instead you should use command line tools like sqlcmd or bcp - depending on your needs.


First Question: In order for this communication to be secure, would it be a better idea to use HTTPS port 443 instead of that basic TCP port 27077 , then disable the HTTP interface in the mongo.conf file? Is that enough to make the communication secured?

--yes enough.As port and ip security is there apart from that you can use iptables for more security. As currently you are just preventing mongo attack only.

What would be the best way to always make sure the mongo server is always responsive or running? (for example if the server was rebooted, I need to make sure that the mongo is still running) or (to avoid mongodb.lock file) because I had that issue before.

-- if you are running it as service then lock file issue will not be there.in case you are using directly from bin then put it in rc.d simple.
advised you to create a script to check your server status and configure alerts accordingly.

BACKUP: As you are not using replica then simply mongodump will be advisable but you can also start your single machine as replica means without any secondary you will have oplog for incremental backup.
You can start instance with replset option or change in config file.
then below command.

>rs.initiate()

it will give you something like :

{
    "info2" : "no configuration specified. Using a default configuration for the set",
    "me" : "machine:27017",
    "ok" : 1
}

check one oplog.rs will be there in local database.


THe solution was:

$ export PATH=/opt/pgv10/bin:$PATH
-- go to hypopg directory
$ USE_PGXS=1 make install

Best Regards
Neto


How to create multiple azure sql database in an elastic pool using powershell script?

I tried to work on this question...I hope it helps who is looking for it.The solution script is as follows:

Set subscription

Set-AzContext -SubscriptionId $subscriptionId

Create a new resource group

$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location

Create a new server with a system wide unique server name

$server = New-AzSqlServer -ResourceGroupName $resourceGroupName -ServerName $serverName -Location $location -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

Create a server firewall rule that allows access from the specified IP range

$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $serverName -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp

Create an elastic database pool:

$firstPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName -ServerName $servername -ElasticPoolName $firstPoolName -Edition "Standard" -Dtu 50 -DatabaseDtuMin 10 -DatabaseDtuMax 20

Declare an array to store db names:

$databasename = @("DB1", "DB2")

Loop to create automatic multiple database as per requirement:

   For ($i=0; $i -lt $databasename.Length; $i++)
        {
        $resourcegroupname = "elas-rg"
        $location = "centralus"
        $servername = "sirurname"

            $azuresqldb= Get-AzSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename[$i] -ErrorAction SilentlyContinue

     if ( -not $azuresqldb )
     {
                "Creating Azure SQL Database " + $databasename[$i] + " in Server Name sirurname"

                New-AzSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename[$i] -ElasticPoolName "Mysecondpool"
            }
            else
            {
                "Azure SQL Database already exists " + $databasename[$i]
            }
        }


select person, t1.food, COUNT() total, COUNT() 100 / fcnt "Perc %"
from food_table t1, (select food, COUNT() fcnt from food_table group by food) testsum
where t1.food = testsum.food
group by t1.person, t1.food, fcnt
order by 1
Output
person  food    total   Perc % of total
1       bread   2       66
1      fruit    1       50
2      bread    1       33
2      fruit    1       50


Database schema for a variable-level hierarchy with variable types?

A good way to do this in a relational database is with statement, category, and category_structure tables. The statement represents the line of text. Assume that each statement resides in a single category.  A group of statements in a category have to appear in a certain order. Categories include the text groupings including Cardiovascular and Palpitations. The category_structure relates the hierarchy of categories. Each structure has a child_Category, parent_category, and sort_order. A structure is a unique combination of a child and a parent. Here are the table definitions.

statement
PK   statement_ID         number   An autogenerated sequence
FK   category_ID          number   A foreign key to category table
     statement_text       varchar  The long description On a scale of one...
     statement_sort_order number   The order within a category

category
PK   category_ID          number   An autogenerated sequence
  UK category_unique      varchar  Uniquely identifies the purpose of a category.
                                   Values could be Discharge. Also, Chest Pain Symptom,
                                   Chest Pain Discharge.
     category_description varchar  The visible text description of a category. The
              values would be Symptoms, Cardiovascular,
              Discharge, Chest Pain, Palpitations.

category_structure
PK   structure_ID         number  An autogenerated sequence
FKUK child_category_ID    number  foreign key to category
FKUK parent_category_ID   number  foreign key to category. Optional.
     category_sort_order  number  The order of categories or the order within
                                  categories

Note the Symptoms category would be the parent of all symptoms. The Discharge category would be the parent of all discharge. The structure table does not prevent a category from having both symptom and discharge as parents.

I see from the reformatting edit that Chest Pain is both a symptom category and a discharge category. This would still work fine. One option is to make two categories for Chest Pain, one as a symptom, one as a discharge. They just happen to have the same name, but they contain different sets of statements. The other option (reflected in the table design) is to add a field to the statement table describing whether the statement is a symptom-style statement or a discharge-style statement. So the Chest Pain category has both symptom statements and discharge statements. The query would only show the appropriate subset based on the need.


TransactionLog not getting truncated in AlwaysOn Cluster

Restarting the secondary replica server and taking another fullbackup and then subsequent Tlog backup fixed the issue.


How can we handle out-of-range errors when aggregating data?

If you create a user function (or 2), some parts of your 'problem' can be solved:

DROP FUNCTION IF EXISTS clamp(val numeric);
CREATE FUNCTION clamp(val numeric) RETURNS bigint AS $$
BEGIN
RETURN CASE WHEN val>+9223372036854775807 THEN +9223372036854775807 WHEN val<-9223372036854775808 THEN -9223372036854775808 ELSE val::bigint END;
END; $$
LANGUAGE PLPGSQL;

DROP FUNCTION IF EXISTS clamp(val double precision);
CREATE FUNCTION clamp(val double precision) RETURNS bigint AS $$
BEGIN
RETURN CASE WHEN val>+9223372036854775807 THEN +9223372036854775807 WHEN val<-9223372036854775808 THEN -9223372036854775808 ELSE val::bigint END;
END; $$
LANGUAGE PLPGSQL;

After this, you can do:

select clamp(9223372036854775807 * 9223372036854775807::float);

This will return the bigint 9223372036854775807, which is 'Infinity'


Changing database compatibility from legacy CE to 120

This regression in performance usually happens because you already had a bad query that was considered "fast enough" under the old compat, but is super slow under the new compat, due to the weird/bad way it was written.

I'd get rid of the 2 functions in your DELETE statement's ON clause, by pre-computing them and storing them into your temp tables before the DELETE happens.

You could also try to rewrite the ISNULL parts, since ISNULL is also a function.

--new temp table to store table1
SELECT col2
    ,col3
    ,col4
    ,CATCH_WAY --this scalar function gets calculated here and saved into the temp table
INTO #tempTable1
FROM dbo.table1;

DELETE t2
   OUTPUT del.col1
          del.col2
          del.col3
   INTO #temp1
FROM #temp2 t2
JOIN #tempTable1 tb1
ON tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1]) --not a good idea
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'') --not a good idea, the ISNULL "hides" the columns from the SQL Optimiser


The choices are more for your convenience, not the database's ability to handle it.

Here's another:

+---------------+--------+------------+------------+
|  DESCRIPTION  | AMOUNT | COMMİSSİON | VALUE DATE |
+---------------+--------+------------+------------+
| INSTALLMENT   |    100 |          1 | 01.03.2018 |
| INSTALLMENT   |    100 |          1 | 01.04.2018 |
| INSTALLMENT   |    100 |          1 | 01.05.2018 |

This has the advantage of tying the AMOUNT and COMMISSION together.


What is the recommended RAID configuration for an Oracle database?

It depends. When looking at a data warehouse, if you don't have a specific design in mind, automatic storage management may be an excellent route.

Consider the discussion at AskTom, OTN Forums, OTN Forums 2, and OTN Forums 3.

There is no one right way to deal with things, and the answers change based on a host of hardware and network factors. In order to discover for yourself, preload a sample data warehouse (only a gig or two, enough to play with) on an ASM based machine, on a SAN with the Raid being virtualized by linux and on a hardware based raid machine.

By timing the results of queries on all three of the environments, you'll be able to discover which methodology works the best for you performance-wise. I've deployed databases using ASN and linux-based virtual raids, and a virtual raid behaved slightly better (a few years ago.) However, I suspect that was in part the way the drives were set up.

There is no singular right answer. If you can provide us more details about the size and performance requirements, it may be possible to explore various test cases.

--Edit--

Every "disk group" may be made up of one or more disks, directories, or files on the appropriate subsystem. Oracle recommends "For best performance and reliability, choose a RAID device or a logical volume on more than one physical device and implement the stripe-and-mirror-everything (SAME) methodology." when placing files on a filesystem. That reads as if oracle is recommending RAID 1+0.

ASM managed disk groups, however, "A normal redundancy disk group requires a minimum of two failure groups (or two disk devices) if you are using two-way mirroring. The effective disk space in a normal redundancy disk group is half the sum of the disk space in all of its devices" apparently automatically provide mirroring.

These devices themselves can be comprised of RAID devices, and so on. In practical tests when I was setting up RAIDed data warehouses, a simple virtual RAID 5 on the filesystem provided acceptable performance, and additional ASM added no performance benefits. In this kind of optimization task, first identify your resources, and then test the every possible configuration, as sometimes the results can be extremely counterintuitive.


use a string array without CREATE privileges

I would use the following approach:

Create a string array type:

create or replace type string_array as varray(1000) of varchar2(10);
/

Use that in a with clause to construct the list of values as a virtual table, and use that table in your query, like this:

with t as (
  select column_value as code from table(string_array('1-ABCD','1-BCDE',...))
)
select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in (select code from t)
UNION
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in (select code from t)
UNION
...


MySQL NOW() function - whose clock is used?

That should be the time of the host (DB Server).
To be sure, login to MySQL from your client and run
SELECT NOW();

and then run the date() command in PHP and compare.
The NOW() and PHP date() would only be the same if you are running MySQL and Apache/NGINX on the same server.
CAPTAIN's LOG : SUPPLEMENTAL
You may also compare the difference between NOW() and SYSDATE() if you care about the datetime being deterministic.
The example in the documentation for [SYSDATE()] show how
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

Sorry, if this is WTMI, but just know that NOW() comes from the DB Server, not the WebServer.
Back in March of 2014, I talked about monkeying around with the server time and its effect on binary logs : Risk of changing clocktime on MySQL server host


Select Distinct on subset of columns, name different set of columns to return

You're almost there. SELECT DISTINCT returns that group of columns, as you've found out. If you use DISTINCT ON instead though, you'll get the rows you're looking for.

INSERT INTO Table_A
    (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT DISTINCT ON (colA, colB, colC, columnD, colE)
    colA, colB, colC, columnD, colE, columnF, colG, colH, colI, colJ
FROM
    Table_B

As pointed out by ypercube and in the Postgres docs, you can improve this query by adding ORDER BY. Without it, it seems that the choice between two conflicting rows is unpredictable.

INSERT INTO Table_A
    (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT DISTINCT ON (colA, colB, colC, columnD, colE)
    colA, colB, colC, columnD, colE, columnF, colG, colH, colI, colJ
FROM
    Table_B
ORDER BY
    colA, colB, colC, columnD, colE    -- needed as it is
--  , colX, colY                       -- to choose which row to pick
  ;


Different sort despite same collation in Postgres

PostgreSQL uses the operating system for its locales (at least with libc as the collation provider, otherwise icu may be used), and unfortunately, different operating systems give inconsistent sort results for the same UTF-8 locale names.

The fact that UTF-8 collations with Postgres.app doesn't sort as expected is an old open item on their issue tracker: Problems with sort order (UTF8 locales don't work). It can't really be fixed unless Apple fixes macOS, and so far it's not happening. See Ordering differences between Postgres instances on different machines (same locale) for a 2012 question on SO mentioning the problem.

  What am I missing here, and how can I configuration postgres in my dev
  box to behave as expected?

Your options:

use the same operating system for dev and production, possibly virtualized.
use ICU collations. They sort the same across all operating systems, but as of PG version 12, they can be used only with explicit COLLATE clauses at the column or expression level (not at the database level).
use the "C" or "C.UTF-8" collations, which sort binary-wise by codepoints. They don't provide a linguistically-aware sort order, but they're faster, immutable and consistent across operating systems.


There is no syntax variant that lets you update the whole row at once. However, there is a shorter form than what you have so far.
Also, you do not actually want to update all columns. The WHERE condition on id pins down at least one column (id) to remain unchanged. But that's just nitpicking.
UPDATE table_a a
SET    (  c1,   c2, ...)
     = (b.c1, b.c2, ...)
FROM   table_b b
WHERE  a.id = b.id;

More details in this related answer:
Bulk update of all columns
DELETE / INSERT
Internally, due to the MVCC model of Postgres, every UPDATE effectively inserts a new row anyway and marks the old one as obsolete. So, behind the curtains there is not much difference between UPDATE and DELETE plus INSERT.
There are some details in favor of the UPDATE route:

HOT UPDATE.
TOAST tables:  If you have large columns, the content may be stored."out-of-line" in TOAST tables and the new row version can link to the same row in the TOAST table if toasted columns remain unchanged.
Index maintenance may be cheaper for updates.

Otherwise, locking should be about the same. You need an exclusive lock on affected rows either way. Just make it quick.
If you are dealing with a huge number of rows and you don't need a consistent state (all rows or none), you can split the operation into multiple batches. (Separate transactions!) Increases the total cost, but keeps the lock time per row shorter.


Help with optimising a sql query on two partitioned tables

Without query execution plan, my first thoughts are:

I. Use datetime variables to remove implicit convertion and use index.

DECLARE @day as datetime2(2) = '20141007'
DECLARE @dayStart as datetime2(2) = CONCAT(@day,' 00:00:00.000')
DECLARE @dayEnd as datetime2(2) = CONCAT(@day,' 23:59:59.999')

II. Change EXISTS for INNER JOIN

EXISTS (Select * FROM Acks AS B where (A.messageId = B.messageId)

to

INNER JOIN ON A.messageId = B.messageId

III. Consider limiting table B on date - at the moment it looks through whole table.

UPDATE.
1. Do parts of your join work much faster as single queries?
2. Please include both Execution Plans on your post. Links may stop working in future.


Mysqlfailover command - No slave is listed in health status

Workaround for this is to give slave details also:

mysqlfailover --master=root:abc@10.24.184.12:3306 --discover-slaves-login=root:abc --slaves=root:abc@<host>, root:abc@<host>

where  is IP or hostname of slaves.


Firstly: you should value literals that match the data type of the columns. '1' is a character, where 1 is a number. Avoid any implicit data type conversion.

I assume that display_seq and result_label_seq are really defined as number not varchar in the following statements. If they are indeed varchar columns you should change that. Never store numbers in character columns.

If you just want to increment the values, you can use

UPDATE anc_result_seqs
   SET display_seq = display_seq + 1
WHERE service_code = 'CBC'
  AND facility_id = 'C'

However if you want to renumber the display_seq according to the current ordering that is defined by the column result_label_seq then the following should do:

MERGE INTO anc_result_seqs ars
USING (
   SELECT rowid as rid,
          row_number() over (order by result_label_seq) as rn
   FROM result_label_seq
   WHERE service_code = 'CBC'
   AND facility_id = 'C'
) t ON (t.rid = ars.rowid)
WHEN MATCHED THEN UPDATE
   SET display_seq = rn;


Best read performance when every query is known, and the data doesn't change

Off the top of my head, here are a few things to do, in no particular order.

VACUUM FREEZE
ANALYZE
Add the indexes that you think are going to help.
Execute as many queries as you can.
Look at pg_stat_user_indexes to see which queries are being used.
Look at the EXPLAIN ANALYZE plan for each query.
Review https://wiki.postgresql.org/wiki/Slow_Query_Questions
Review https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Post follow-ups with server specs and questions to SO or the Postgresql mailing lists (most of the core developers answer questions in the pg lists).

It will likely be an iterative process, so don't expect to get it right in the first go.