Threads_cached 0 while thread_cache_size > 0

When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there.

In your case, it would read

  When a client disconnects, the client's threads are put in the cache if there are fewer than 4 threads there.

Also, note the following from the MySQL Documentation

  Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads.

Given this statement, 4 would seem way too low. You should set it much higher or don't set it at all (letting MariaDB set it for you).


Traversing tree-like data in a relational database using SQL

Celko's book is a good resource - if a bit overly "academic" at times.

I also have really found  this method, known as 'closure tables' to work fairly well.

If you're using a database that allows recursive CTEs (such as PostgreSQL 8.4 or newer, or SQL Server 2005 or newer), they're really the best way to go. If you're on Oracle, there's always the venerable "connect by".

It is my experience that it's far more common to be handed a set of tables in a "naive tree" schema, and have to figure out how to extract the correct tree from that storage, than it is to have an opportunity to create the cleaner "closure tables" structure.


How to avoid Recovery Catalog's metadata being purged after 60 days?

This is described in the note:

How to Clean up The information in EM Backup Report (Doc ID 430601.1)

Edit recover.bsq, find the procedure cleanupRSR, change the default value 60 as needed:

  DELETE FROM rsr
        WHERE rsr_end < nowTime-60
          AND rsr.dbinc_key IN
              (select dbinc_key from dbinc
               where dbinc.db_key = this_db_key);

When finished:

RMAN> upgrade catalog;


What is an ODBK file and how to restore that?

ODBK isn't any abbreviation officially used by OpenEdge. But if it indeed is a backup file from Progress OpenEdge you need to use prorest:
prorest newdatabase backedupfile

Start by reading this article


SQL - Design question for almost identical tables

Below is my first pass at a relatively simple system.

Don't know what RDBMS you're using, so your SQL
may vary a bit depending.

CREATE TABLE School
(
  School_id INTEGER PRIMARY KEY,
  School_name TEXT NOT NULL,
  Address_1 TEXT NOT NULL, -- must have at least 1 address field
  Address_fields..
  Tel TEXT NOT NULL -- I'm assuming that each school has a phone number,
                    -- put a UNIQUE CONSTRAINT on this. Different schools
                    -- can't have the same number! **But**, there might
                    -- be a boys and a girls school with the same office?
  Other_fields...
  ..
);

CREATE TABLE Player
(
  Player_id INTEGER PRIMARY KEY,
  Player_name TEXT NOT NULL,
  Player_gender BOOLEAN,  -- You forgot this one!
  Player_DOB DATE,        -- and this :-)
  ..
  Other_fields
  ..
);

CREATE TABLE Team
(
  Team_id INTEGER PRIMARY KEY,
  Team_name TEXT -- put in UNIQUE? Don't allow any teams with same name even
                 -- from different schools.
  Team_Player_id_1 INTEGER NOT NULL,  -- Put UNIQUE CONSTRAINT on both player 1
  Team_Player_id_2 INTEGER NOT NULL,  -- and player 2.
);

CREATE TABLE Tournament
(
  Tournament_id PRIMARY KEY,
  Tournament_name NOT NULL, -- UNIQUE?
  Tournament_type CHAR(1) CHECK Tournament_type IN ('S', 'D'),
  Tournament_start_date DATE,
  Tournament_max_entrants  INTEGER  -- is there a max no of entrants?
);

For the first pass, I've decided to split the registration
tables into singles and doubles - there may be better/more
sophisticated methods of dealing with this.

You could think of having an Entrant table with either a
singles or a doubles entity and maybe (probably as part
of code and not in the RDBMS) ensure that teams (i.e.,
an Entrant with two members) can only enter doubles tournaments?

CREATE TABLE Singles_Registration
(
    Reg_id INTEGER PRIMARY KEY,
    Reg_entrant_id  INTEGER FOREIGN KEY (Player) (Player_id),
    Reg_school_id,
    Reg_Category text,  -- don't know what this is for in your schema?
    Reg_tournament_id FOREIGN KEY (Tournament) (Tournament__id),
    Reg_selected Boolean  -- foreign key to table X
);

CREATE TABLE Doubles_Registration
(
    Dreg_registration_id INTEGER PRIMARY KEY,
    Dreg_entrant_id  INTEGER FOREIGN KEY (Team) (Team_id),
    Dreg_school_id,
    Dreg_category text,  -- don't know what this is for in your schema?
    Dreg_tournament_id FOREIGN KEY (Team) (Team__id),
    Dreg_selected Boolean  -- FK to table X
);

You might get more ideas from http://www.databaseanswers.org, here.

In particular, here and esp. here.

I think you might well need a bit more sophistication - you have
nothing about attainment levels/qualification_prerequisites,
heats or possibly even a tournament_location table - not every
school may be able to host a tournament and may use other
local courts. The list of potential fields is quite large. To
say nothing of results?

You don't mention your RDBMS, if you still have a choice and you're
going for an Open Source solution , I recommend PostgreSQL - it is
infinitely more capable than MySQL.

Neither do you mention your development environment - not that
it's critical, but it might be useful to know. The more information
you provide here, the better.

A Google of "sports tournament schemas" brought up several possibly
interesting sites (here inter alia).

A+


Why am I unable to create this Availability Group? (error 35220)

Here's the blog where I found the answer: https://bahtisametcoban.home.blog/2019/01/04/always-on-availability-groups-with-multi-subnet-failover-cluster/.

In case you don't feel like reading through it, the answer is

  If you enable [Availability Groups] before cluster is fully operational and ready  you will get an error when you try to create AG

When I installed and configured SQL Server on the machines in question, I jumped the gun on enabling the Availability Groups feature (SQL Server Configuration Manager->Services->SQL Server->Properties->AlwaysOn High Availability tab) and checked the box (and restarted the service) before the WSFC was created.  This is what caused the error when I tried to actually create an AG.

The fix is easy: just disable Availability Groups, restart the service, then re-enable them and restart it again.

Hope this has been helpful!


Conversion failed when converting the varchar value 'Hours' to data type int

This is not clear what output is needed.
You are trying to convert text strings to int. This obviously does not work. Besides this type of formatting might fit better outside of SQL Server.

If you want something like 5 Hours 15 Minutes you can try this query:

[Time Spent(In hrs)] =
    CAST(
        SUM(
            DATEDIFF(hour, ld_time_tracker_start_time, ld_time_tracker_end_time)
        )
    as varchar(10))
    +' Hours '+
    CAST(
        SUM(
            DATEDIFF(minute, ld_time_tracker_start_time, ld_time_tracker_end_time) % 60
        )
    as varchar(10))
+' Minutes'

Note that I have replaced HH by hour. This is easier to read and understand and it avoid mistakes. See DATEDIFF (Transact-SQL).


pt-table-sync error: Called not_in_left in state 0

You could try:

Checking the MySQL error log after you attempt the sync. It may reveal some consistency issues with the table that you weren't aware of
Working around this table for now (using --exclude-table), and then coming back to it later
Trying different combinations of checksum options (--algorithms, --chunk-size) for the problem table - this might fix the problem, or shed more light on the underlying issue

I received this error recently, and the underlying issue turned out to be a corrupt table in need of repair on the master server.

I discovered the problem by changing the behaviour of pt-table-sync to using the "nibble" algorithm for my problem table (--algorithms nibble). Instead of the cryptic error, I got a specific error along with the SQL Statement that caused it, when I then attempted to execute directly on the server. This led me straight to my problem.

I could have also checked the error log and discovered the same thing.

Alternatively, you could try changing algorithms or reducing the chunk size from 1,000 (e.g. --chunk-size 100). This may require a bit of trial and error, and it's likely to slow down the checksums significantly, which is why I suggest skipping your largest tables to start with.

For a sufficiently large table (and 57GB certainly qualifies) with a poorly distributed primary key, you might also experience problems with query or wait timeouts depending on your MySQL configuration - I'm not sure if this would also result in the same error. The pt-table-sync documentation offers a little bit more information on this.


Connecting to local Oracle Server via MacOS SQL Developer

Your listener.Ora file is set to listen on localhost rather than your 10.0.0.10 ip. Change that, then restart the listener and make sure your instance is getting registered with it. Note that your tnsnames.Ora Isn’t involved here as you’re specifying everything in your sql developer connection, but you also need to correct the host there.


Finally traced the issue to a table on the Subscriber that was missing the PK index. Still not too sure how this could have happened as all subscribers were initialized from the same snapshot at the same time. Re-initializing the individual subscriber had no affect.
I had to;
    - remove the article from the publication
    - run the Snapshot Agent
    - manually drop the table on each Subscriber
    - add the article back to the publication
    - re-run the Snapshot Agent

All good now.


How to return when inserting into multiple tables as part of a views on inert rule

Model.

Insert data into two tables via view with returning some field values.

Test tables:

create table test1 (id int, val int);
create table test2 (id int, val int);

View:

create view total
as
select t1.id, t1.val val1, t2.val val2
from test1 t1
join test2 t2 on t1.id = t2.id;

Trigger function and trigger which inserts data into tables:

create function fninsert()
returns trigger as $fninsert$
begin
insert into test1 values (new.id, new.val1);
insert into test2 values (new.id, new.val2);
return new;
end;
$fninsert$ LANGUAGE plpgsql;

create trigger trinsert
instead of insert on total
for each row
execute procedure fninsert();

Rule which returns inserted values:

create rule r_insert as
on insert to total
do also
select new.val1, new.val2;

Demo fiddle.


Plpgsql seems to be deleting and inserting instead of updating - Why?

PostgreSQL doesn't really do updates, it does the equivalent to delete and insert in terms of how it does them.  It leaves the old version of the row in place and creates a new version that all future transactions will see.  Eventually vacuum reclaims the old row space for reuse.  A word of advice, update immediately to a supported release. 7.4 is VERY old, is no longer supported, and has known data eating bugs that will never be fixed.  I'd recommend going right to 9.0, and researching the issues with removed implicit casts which will be the biggest problem.  We had to fix three queries in out app when we went to 8.3 the first version to remove implicit casts.


The order of things in SELECT ... does not matter.

The main performance killers:

Having to fetch from disk.
Fetching more rows than needed.
Fetching more columns than needed.  That is, SELECTing * when you don't need all the columns.
Everything else is relatively insignificant -- expressions, ordering, etc.

In particular...

FROM UserContact u
LEFT JOIN User cu ON cu.ID = u.CreatedByID
LEFT JOIN User uu ON uu.ID = u.UpdatedByID
WHERE u.UserID = ?

Needs these indexes to tackle #2, above (unless they are already the PRIMARY KEYs)

UserContact: INDEX(UserID)
User:  INDEX(ID)

Please provide SHOW CREATE TABLE if you need to discuss further.


Can you rename or name Reporting Services jobs?

The GUID identifies the job in the ReportServer database too. So don't change it.

That is, the schedule uses uniqueidentifier datatype in the table: the value matches the job


Azure SQL Database Restore after Server Failure - Where are the Backups?

I think the key to understanding that sentence is what I have marked in bold:

  Restore a database to another geographical region. This allows you to
  recover from a geographic disaster when you cannot access your server
  and database. It creates a new database in any existing server
  anywhere in the world.

See the documentation on Geo restore:

  Geo-restore is the default recovery option when your database is
  unavailable because of an incident in the region where the database is
  hosted. If a large-scale incident in a region results in
  unavailability of your database application, you can restore a
  database from the geo-replicated backups to a server in any other
  region. There is a delay between when a differential backup is taken
  and when it is geo-replicated to an Azure blob in a different region.
  This delay can be up to an hour, so, if a disaster occurs, there can
  be up to one hour data loss.

The difference between this and a regular restore is that there is a bigger latency and possibility of data loss. You also can't do a point in time restore.

So when you restore the backup from an existing database through the azure portal you have access to the backups on that same location.

Microsoft is replicating those backups to azure blobs in different regions in case some serious incident happens in a region.

In order to restore you would add a new database in your portal and select "backup" as a source:

  To geo-restore a database during its retention period using the Azure
  portal, open the SQL Databases page and then click Add. In the Select
  source text box, select Backup. Specify the backup from which to
  perform the recovery in the region and on the server of your choice.

So in case your entire database and data center is gone, you have the option to create a new database  in another data center, and you can use the latest geographically replicated backup (not the latest backup) as a source.


How to create a temporary table using VALUES in PostgreSQL

EDIT: I am leaving the original accepted answer as it is, but please note that the edit below, as suggested by a_horse_with_no_name, is the preferred method for creating a temporary table using VALUES.
If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like:
WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100))
SELECT * FROM vals;

To actually create a temporary table in a similar fashion, use:
WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100))
SELECT * INTO temporary table temp_table FROM vals;

EDIT: As pointed out by a_horse_with_no_name, in the docs it states that CREATE TABLE AS... is functionally similar to SELECT INTO ..., but that the former is a superset of the latter and that SELECT INTO is used in plpgslq for assigning a value to a temporary variable -- so it would fail in that case. Therefore, while the above examples are valid for plain SQL, the CREATE TABLE form should be preferred.
CREATE TEMP TABLE temp_table AS
WITH t (k, v) AS (
 VALUES
 (0::int,-99999::numeric),
 (1::int,100::numeric)
)
SELECT * FROM t;

Note, also from the comments by a_horse_with_no_name, and in the OP's original question, this includes a cast to the correct datatypes inside the values list and uses a CTE (WITH) statement.
Also, as pointed out in Evan Carrol's answer, in Postgres prior to version 12 a CTE query is always an optimization fence, ie, the CTE is always materialized. There are many good reasons for using CTEs, but there can be quite a significant performance hit, if not used carefully. There are, however, many instances where the optimization fence can actually enhance performance, so this is something to be aware of, not to blindly avoid.


MySQL: Is it possible to run a search on a specific database for a specific string?

You might be able to query your database with the help of the Needle in a Haystack code.

From the author

  Sometime we need to find out a small piece of string in big Database. Like where is the configuration is saved, or where is Jon's Date of birth is saved. This code is search all the tables and all the rows and columns in a MYSQL Database. The code is written in PHP. For faster result, we are only searching in the varchar field. (if anyone needs to search in other field, he can just comment the varchar selecting part.)

Description

The above mentioned solution is a PHP page which allows you to enter your connection details and then just queries your database for the string.

The PHP program iterates through the tables (show tables;) stores this information in an array and then iterates through the description of each table to retrieve the columns to search.

It then performs a simple SELECT * from $table where $column = $value.

The standard version searches only in varchar fields, but this can be altered in the source code.

  Disclaimer: This is not my work and I am in no way affiliated with the author.


In addition to the command line \d+ <table_name> you already found, you could also use the Information Schema to look up the column data, using information_schema.columns:

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'your_schema'
   AND table_name   = 'your_table'
     ;

Note: As per the example above, make sure the values are enclosed within quotes.


None of the above.

A "full scan" is irrelevant if the table has only 10 rows.  Ditto for "not using an index".

There are many queries for which no index is useful; you stats will have you stumbling over them.  For example, if you index a 'flag' (yes/no, M/F, etc), the optimizer will usually eschew the index and simply scan the table -- because it is more efficient to do so.

"First table".  Unless you are looking at the EXPLAIN, it is difficult to predict which table will be looked at "first".  Even the existence of LEFT does not conclusively predict ordering of the tables.

What is your goal, anyway?  The main goal, in my opinion, is to find and fix queries that are bogging down the system.  To that end, I want a list of queries, ordered by total elapsed time (number of occurrences times avg time).  An that is very efficiently provided by

long_query_time = 1
turn on slowlog
wait a day
use one of these to get the list:
    mysqldumpslow -s t
    pt-query-digest

Then, I get EXPLAIN and SHOW CREATE TABLE for the first couple of queries and work on them.

If you want to see 'change over time', then record the 'total time' for the worst queries and see it those get better or worse next month.

You may find that some of the worst queries are not SELECTs.


Difference between [mysql] and [mysqld] in MySQL configuration file

MySQL calls these section "Groups". The [mysqld] group contains the variables apply to the MySQL Server (the mysqld process). The [mysql] group contains the variables for the client program (mysql).

The [client] option group is read by all client programs (but not by mysqld), so "mysql", "mysqldump" etc...

None of these groups are mandatory but usually we set at least the [mysqld] because it's where we configure the server.

About how MySQL interprets these variables, MySQL Documentation says:

  If an option group name is the same as a program name, options in the
  group apply specifically to that program.

Max


Getting ORA-01031:insufficient privileges during first time login to oracle 11.2.0.1.0 database on my windows 8 PC

There are some things which have to be set up correctly:

The user you are logged on has to be a member of the ora_dba operating system group.
The parameter SQLNET.AUTHENTICATION_SERVICES=(NTS) must be set correctly. It can include more options but "NTS" is required.
You have to connect to the database correctly which is: sqlplus / as sysdba. You can also add these quotes but that was Oracle 9i style. But you need a space betweed "/" and "as".

  Note: If you want to connect to Oracle as sysdba from a remote computer, you need a password file.


As per MySql documentation, KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.
 In your case ADD KEY will explicitly define index on that field.


how login other account by sysdba in oracle?

It is really easy to log in as another user, just use the proxy user feature that is available since 8i. Unfortunately it does not work with SYS, but works with any other user. Basically you can grant the privilege to users to connect as another user.

SQL> alter user system identified by Oracle123;

User altered.

SQL> alter user hr identified by password account unlock;

User altered.

So the password of SYSTEM is Oracle123, and the password of HR is password. Now allow SYSTEM to connect as HR:

SQL> alter user hr grant connect through system;

User altered.

Finally, connect with SYSTEM and its password, providing HR in the brackets:

SQL> connect system[hr]/Oracle123
Connected.
SQL> show user
USER is "HR"

You can use any user for this, except SYS:

SQL> alter user hr grant connect through sys;
alter user hr grant connect through sys
                                    *
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'SYS'


Not able to search on zipcode column a table in postgresql

Try to remove the whitespaces in the table:

UPDATE my_table
       SET zip_ = trim(_zip);

And then find out who enters ZIPs with whitespaces and tell them to stop, or add a trigger to the table that trims the data upon insertion or updating.

Simple version for a start:

    CREATE FUNCTION my_table_normalize_zip()
                    RETURNS TRIGGER
    AS
    $$
    BEGIN
      NEW.zip_ = trim(NEW.zip_);

      RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;

    CREATE TRIGGER normalize_zip
                   BEFORE INSERT
                           OR UPDATE
                   ON my_table
                   FOR EACH ROW
                   EXECUTE PROCEDURE my_table_normalize_zip();

Possibly add more normalization logic to it as needed.

You might also consider a CHECK CONSTRAINT or rather a CONSTRAINT TRIGGER (because this can get complicated and beyond the scope of a simple CHECK CONSTRAINT) to have some plausibility check on the ZIPs. But a warning: We tried that once in the company I worked for. For some countries it's pretty simple. For others it's just a nightmare and again for others you can search your b**t to even find any rules. In the end we gave up. It was to expensive compared to the benefit (most postal services are quite good in finding out where misaddressed mail really belongs to anyway). But a "light version", for those countries it's easy for, might be reasonable.


When I first ran your query on SQL*PLUS (Oracle 12.2)-

SQL>  SELECT EMPLOYEE_ID,DEPARTMENT_ID,SALARY FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY HIRE_DATE
MEASURES
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.SALARY < PREV(B.SALARY))
)  2    3    4    5    6    7    8    9   10   11  ;
 EMPLOYEE_ID,DEPARTMENT_ID,SALARY
            *
ERROR at line 6:
ORA-62505: expression needs to be aliased

I didn't get ORA-0933 as you have mentioned. But when I ran your code on Oracle 11.2.0.4 then I got the same error as you have said. And the reason is, the MATCH_RECOGNIZE clause is added on analytic function syntax from Oracle 12c only.

Then on 12c, I corrected the error as-

SELECT emp_id,dept_id,SAL FROM EMPLOYEES
MATCH_RECOGNIZE(
PARTITION BY DEPARTMENT_ID
ORDER BY salary
MEASURES
 EMPLOYEE_ID as emp_id,
 DEPARTMENT_ID as dept_id,
 SALARY as sal
ONE ROW PER MATCH
PATTERN( A B* )
DEFINE
      B AS ( B.salary < PREV(B.salary))
);

which runs perfectly for me, at least without any syntax error.


Using an AWS database that can store more than 65,535 bytes in a row

(Addressing Q1)

There are many workarounds for having bloated columns.  Let's tackle that.

What is the table's ROW_FORMAT?  DYNAMIC and COMPACT work differently.

Do they all need to be TEXT?  Or can some be VARCHAR with low limits?  (Eg: name TEXT --> name VARCHAR(60))

Consider "vertical partitioning".  This is where you split a table into 2 tables -- putting some columns in each table.  They would have the same PRIMARY KEY (except not both being AUTO_INCREMENT).

Do any of the columns have repeated values?  If so, consider normalizing.  (Eg, country TEXT -> country_code CHAR(2) CHARACTER SET ascii with values like 'US', 'ES', 'UK', 'IN'.)

Have you splayed an array across columns?  That is a no-no.  Turn that set of columns into rows in a related table.  (Eg home_phone, work_phone, fax, cell.)

Please provide SHOW CREATE TABLE with the actual column names; we may have more hints.  (And some of my suggestions above may be painfully obvious.)

After looking at the schema...

I see a bunch of columns that sounds like money or other numeric stuff, but is in VARCHAR(25); consider changing to INT or some other numeric datatype

I see about 80 each of TEXT and VARCHAR.  This does stress some builtin limits in InnoDB.  But they can, and need to be, worked around:

Plan A:  Vertical partitioning -- break out FR, COP, PP, Customer, etc. into a few extra tables.  Use JOIN to re-gather the data.

Plan B:  Throw most of the columns into a large JSON text column.  Or maybe one for FR, one for Customer, etc.  Most app languages can easily parse JSON.

Since you have only one index (on id) I guess there is no call for searching the table?  A few other columns, like maybe userID and name, should be kept in their own columns, and possibly indexed.


MySQL : Is there a command similar to FLUSH STATUS to reset GLOBAL STATUS?

Try from command-line (not mysql prompt):

$ mysqladmin flush-status


invalid default value (mysql 5.7) for timestamp field

since mysql 5.7, SQL_MODE default value is strict, please view this page:
https://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field


I have solved thanks to Jeff Janes on the pgsql-performance mailing list:

The GIN index was not used by PostgreSQL for the "NOT" operation. Creating a Btree index on the whole array solved the problem, allowing an index only scan. Now the query takes only a few milliseconds instead of minutes.


How to use MySQL Server 5.6 for GeoIP replication?

Would you be interested in a MySQL Replication setup that is shaped like a snowflake ?

You could setup a Star Topology

Have a Write Master
Setup up a Distribution Master (DM). The role of the Distribution Master is simple

Slave to Write Master
Store No Data (All tables use BLACKHOLE Storage Engine)
Have Binary Logging Enabled
Act as Master to Multiple Read Slaves

For your case, you could have

Write Master in WM1

DM1 as Slave to WM1

SLV1 as Slave to DM1
SLV2 as Slave to DM1

DM2 as Slave to WM1

SLV3 as Slave to DM2
SLV4 as Slave to DM2

DM3 as Slave to WM1

SLV5 as Slave to DM3
SLV6 as Slave to DM3

You could hang a DM as a Slave off of another DM

The possibilities are endless

I have written about setting this up before

https://stackoverflow.com/a/5781504/491757
https://serverfault.com/a/264444/69271
https://dba.stackexchange.com/a/9336/877


Combine Multiple Rows Results From Left Join in a unique cell

You can use a subquery and a GROUP_CONCAT for that
Schema (MySQL v8.0)
CREATE TABLE Table1 (
  `FieldID` INTEGER,
  `FieldName` VARCHAR(3),
  `FieldAccount` INTEGER
);

INSERT INTO Table1
  (`FieldID`, `FieldName`, `FieldAccount`)
VALUES
  ('01', 'Jon', '12345');

CREATE TABLE Table2 (
  `FieldID` INTEGER,
  `FieldName` VARCHAR(7),
  `FieldAccount` INTEGER
);

INSERT INTO Table2
  (`FieldID`, `FieldName`, `FieldAccount`)
VALUES
  ('01', 'Jon', '12345'),
  ('02', 'Charlie', '12345');

Query #1
SELECT
t1.*
,(SELECT GROUP_CONCAT(`FieldID`) FROM Table2 WHERE `FieldAccount` = t1.`FieldAccount`
) 'LookAt '
FROM Table1 t1;

| FieldID | FieldName | FieldAccount | LookAt  |
| ------- | --------- | ------------ | ------- |
| 1       | Jon       | 12345        | 1,2     |

View on DB Fiddle


Using GREATEST and LEAST

Essentially, if we can sort of the columns in the index, we can ensure that the duplicate entry causes a collision and a constraint violation this is because sort(5,2) = sort(2,5). Because there is no sort, we use greatest and least.

CREATE TABLE edge (
  parent int,
  child  int
);

CREATE UNIQUE INDEX
  ON edge ( greatest(parent,child), least(parent,child) );

INSERT INTO edge(parent,child)
VALUES (42,7), (42,9), (5,7);

INSERT INTO edge(parent,child)
VALUES (7,42);
ERROR:  duplicate key value violates unique constraint "edge_greatest_least_idx"
DETAIL:  Key ((GREATEST(parent, child)), (LEAST(parent, child)))=(42, 7) already exists.


Database cannot be opened. It is in the middle of a restore

This

USE master
GO
RESTORE LOG test
FROM Kingston
WITH STOPAT = '2020-05-13 14:05:25', RECOVERY;

Will not perform recovery if the STOPAT time is outside the range of times in the log backup.  This allows you to specify the STOPAT for multiple log backups, and the database will only run recovery when you've applied the log backup containing the target time.

From the docs:

  The RECOVERY and STOPAT options. If the transaction log backup does
  not contain the requested time (for example, if the time specified is
  beyond the end of the time covered by the transaction log), a warning
  is generated and the database remains unrecovered.

Restore a SQL Server Database to a Point in Time (Full Recovery Model)


Survey Database - Respondent Table (1NF or 3NF - Pros and Cons)

Your question really is not about normalization.  Instead it is about specialization vs. generalization with respect to design.  Let me give some background to show why this is the case.

Background

A table is a relational (R-Table) table, and thus normalized (meaning in 1NF by definition) if in its design a discipline is followed that ensures:

Distinct, un-ordered rows
Uniquely named, un-ordered columns
Each column contains a single value from the corresponding domain

Normalization, more formally called Projection-Join Normalization, is a scientific process in which one can remove redundancies in R-tables due specifically to join dependencies which are not implied by the candidate keys. The join dependencies are exploited by taking projections based on them to create two or more tables from the original table which removes the redundancy. It is important to note that normalization cannot remove all redundancy. Instead, it can remove only redundancies caused by join dependencies not being implied by the primary key.

The first 3 normal forms and Boyce-Codd Normal Form (BCNF) deal with redundancies due to functional dependencies not implied by the candidate keys.  While most designers talk about normalization to the third normal form, what they really mean is normalization to BCNF as that is the normal form with respect to the functional dependencies.

Functional Dependencies

In order to determine if either table is in any normal form, 1NF or 3NF, (and thus an R-Table), we must clearly state the functional dependencies.  A mistake most designers make is to assume the functional dependencies based upon what makes sense to them.  In simple examples this isn't usually a problem, but in complex real world scenarios it is deadly.  For your example let us assume the following functional dependencies for the first table:

RESPONDENT_ID -> RESPONDENT_SERIAL_ID, RESPONDENT_AGE_CODE, RESPONDENT_GENDER_CODE, RESPONDENT_STATELIVEIN_CODE,
RESPONDENT_REGION_CODE, CREATE_DATE, UPDATE_DATE, CHECK_SUM - MD5,
CURRENT_IND, 58 other attributes that define a respondent such as zip code,
political party, employment and etc.
RESPONDENT_AGE_CODE -> RESPONDENT_AGE_DESC
RESPONDENT_GENDER_CODE -> RESPONDENT_GENDER_DESC
RESPONDENT_STATELIVEIN_CODE -> RESPONDENT_STATELIVEIN_DESC
RESPONDENT_REGION_CODE -> RESPONDENT_REGION_DESC

Aside:  One thing I will gloss over in the interest of space but that is extremely important is truly understanding all of the candidate keys.  In this example we have RESPONDENT_ID as the PK.  Are there other data elements that uniquely identify a respondent?  Just designating a surrogate key upon which we declare all the other now "non-key" columns functionally dependent does not change the functional dependencies of those non-key columns on some other column or set of columns which also form a candidate key.

Another Aside: There is a CURRENT_IND column.  That makes the PK of RESPONDENT_ID very suspicious.  If this means what it usually means, then the table could potentially have multiple rows per RESPONDENT_ID, many of which are not current, and one of which is.  Then the real candidate key is something like RESPONDENT_ID plus CREATE_DATE.  This makes the example too complex to analyze though so I will ignore it.  But in reality this is a real problem that implies you really have two types of things here - current respondents and former respondents.  This gets into temporal concerns which are complicated to address.

Let us assume the following functional dependencies for the second table:

RESPONDENT_ID -> RESPONDENT_SERIAL_ID, 58 other attributes that
define a respondent such as zip code, political party, employment and etc.
RESPONDENT_ID, RESPONDENT_TYPE -> RESPONDENT_CODE, CREATE_DATE,
UPDATE_DATE, CHECK_SUM - MD5, CURRENT_IND
RESPONDENT_TYPE, RESPONDENT_CODE -> RESPONDENT_DESC

Evaluating The First Respondent Table

Given the assumed functional dependencies, the first table is indeed in 1NF, and thus an R-Table, as it meets the rules laid out in the background.  However, realize that you really must look at the data to really determine if those rules are being met.  For example, we may assume that each column contains a single value, but upon inspection of the data find that some column has been populated with multiple values separated by commas or pipes.  An even more insidious violation of normalization may be using the order of rows or columns to encode additional meaning, for example the arrival order of survey responses.  For this example let us assume none of this to be the case and the table is normalized and thus an R-Table.

The R-Table is also in 2NF as there are no partial key dependencies.  The T-Table is clearly not in 3NF, as there are transitive dependencies.  Each of the 4 DESC columns functionally dependent on the CODE columns, which in turn functionally depend on the RESPONDENT_ID column.

Evaluating The Second Respondent Table

Given the assume functional dependencies, the second table is not in 3NF.  It is not even normalized (in 1NF) and thus not an R-Table.  The RESPONDENT_ID is claimed to be the PK but there is also a composite candidate key on the RESPONDENT_ID plus the RESPONDENT_TYPE.  This is because there must now be multiple rows per respondent, each with a different type and that type's corresponding code.  The 3NF violation with a transitive dependency between the RESPONDENT_ID and RESPONDENT_TYPE functionally determining RESPONDENT_CODE and then RESPONDENT_TYPE and RESPONDENT_CODE functionally determining RESPONDENT_DESC is still present.

Conclusion

Many designers often assume that taking a specific data element, such as a categorical domain for age, and breaking it into two data elements which are a name value pair is performing "normalization."  It is not.  It is instead choosing a more generalized vs. more specific design.  Normalization is a scientific process with clear rules.  An R-Table in BCNF clearly has less redundancy that one on 2NF.  The BCNF R-Table is clearly better with respect to avoiding redundancy.  Deciding between specialization vs. generalization however is not a scientific process.  So how to know which is better?  Well it depends on the situation.  The advantage to generalization is that it is more flexible for future considerations only in that the data structure doesn't have to change when more data points are added, but comes at a cost of more complexity.  The advantage to specialization is it is simpler and enables the DBMS to constraint the domain of each specific column, but it does require additional columns to be added when new data points are added.  Personally I prefer to do things like Einstein said "as simple as possible but no simpler."

With respect to the two tables you've presented, I prefer the first design (over a corrected second design) but again not because it is more or less normalized.  It is simply more clear what is being represented.  The only change from a normalization perspective I would make would be to place each CODE --> DESC combination into its on R-Table so as to achieve full normalization.  If you want to be sure to avoid the anomalies associated with R-Tables that are less than fully normalized you must ensure full normalization.

References

A good primer on normalization (and on data management fundamentals in general) is Fabian Pascal's Practical Database Foundation Series.  Paper number one describes business modeling (where dependencies are determined) and paper number two which describes normalization. Chris Date's Database Design and Relational Theory provides an in-depth look at normalization along with orthogonality with respect to reducing redundancy in logical database designs.


What is the Relationship between Instagram users and user followers

The relationship between Instagram user and follower is many to many because:

One user can have multiple followers.
One follower can have multiple "followed users".

Useful:  Always name the entities (classes) as singular e.g. User & Follower


selecting from sys.dm_db_missing_index_details will start to return rows, and then sits and spins

This sounds like other sessions are blocking the dynamic management view.

CREATE TABLE and DROP TABLE force an update to the internal tables maintained for the sys.dm_db_missing_index_details.

I was able to recreate a blocking scenario where an open transaction prevents reads against sys.dm_db_missing_index_details.  Run this in an SSMS window on your development machine:

BEGIN TRANSACTION
IF OBJECT_ID(N'dbo.mid_test_2', N'U') IS NOT NULL
DROP TABLE dbo.mid_test_2;
IF OBJECT_ID(N'dbo.mid_test', N'U') IS NOT NULL
DROP TABLE dbo.mid_test;
CREATE TABLE dbo.mid_test
(
    ID int NOT NULL PRIMARY KEY CLUSTERED
    , SomeVal varbinary(2000) NOT NULL DEFAULT (CRYPT_GEN_RANDOM(2000))
);

CREATE TABLE dbo.mid_test_2
(
    ID int NOT NULL FOREIGN KEY REFERENCES dbo.mid_test(ID)
    , SomeVal varbinary(2000) NOT NULL DEFAULT (CRYPT_GEN_RANDOM(2000))
);
GO

;WITH src AS
(
    SELECT v.num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.mid_test (ID)
SELECT s1.num
    + (s2.num * 10)
    + (s3.num * 100)
    + (s4.num * 1000)
    + (s5.num * 10000)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5
ORDER BY 1;

;WITH src AS
(
    SELECT v.num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.mid_test_2 (ID)
SELECT s1.num
    + (s2.num * 10)
    + (s3.num * 100)
    + (s4.num * 1000)
    + (s5.num * 10000)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5
ORDER BY 1;

SELECT *
FROM dbo.mid_test m1
    INNER JOIN dbo.mid_test_2 m2 ON m1.ID = m2.ID
WHERE m2.ID = 66666

Now, in another SSMS window, run the following (you may need to run it several times depending on how quick you are!)

SELECT *
FROM sys.dm_db_missing_index_details;

For me, the above query blocks until I either commit or rollback the transaction started in the first query window, with:

COMMIT TRANSACTION

or

ROLLBACK TRANSACTION

Unfortunately, running SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; prior to querying sys.dm_db_missing_index_details does not help; it still blocks until the open transaction commits or rolls back.


Get The Most Recent Record In A Table By Date And User

There are many ways to do this. Here are some of them:

common table expression with row_number() version:

with cte as (
  select  *
      , rn = row_number() over (
              partition by ModUser
              order by EffectiveDate desc
            )

    from Vision
)
select ModUser, EffectiveDate, Rate
  from cte
  where rn = 1;

cross apply version:

 select distinct
     t.ModUser
   , x.EffectiveDate
   , x.Rate

  from Vision t
    cross apply (
      select top 1
            ModUser
          , EffectiveDate
          , Rate
        from Vision i
        where i.ModUser = t.ModUser
        order by i.EffectiveDate desc
       ) as x;

top with ties version:

select top 1 with ties
    *
  from Vision
  where
  order by
    row_number() over (
      partition by ModUser
          order by EffectiveDate desc
      );

inner join version:

select
      v.ModUser
    , v.EffectiveDate
    , v.Rate
  from Vision v
    inner join (
      select
          ModUser
        , MaxEffectiveDate = max(i.EffectiveDate)
        from Vision i
        group by ModUser
       ) as x on x.ModUser          = v.ModUser
             and x.MaxEffectiveDate = v.EffectiveDate;


Which application should I use to create a relational database from an excel file?

Well if you know how to modify/insert/delete data by using some basic SQL queries and would like to have remote access to the database from other machine, then MS SQL Server can be a good choice. You can download the Express version here for free.


Is there a better way than this to split a string and get the first substring?

Well - here is 'another' way...

DECLARE @str AS VARCHAR(255)

SET @str = 'abc&def&ghi'

SELECT CASE
        WHEN charindex('&', @str) > 0
            THEN substring(@str, 1, charindex('&', @str) - 1)
        ELSE @str
        END AS SelectedString
-------------------------------
SET @str = 'abc'

SELECT CASE
        WHEN charindex('&', @str) > 0
            THEN substring(@str, 1, charindex('&', @str) - 1)
        ELSE @str
        END AS SelectedString

OR - you could simply ALWAYS concatenate '&' at the end of the string like this:

DECLARE @str AS VARCHAR(255)

SET @str = 'abc&def&ghi'

SELECT substring(@str, 1, charindex('&', @str + '&') - 1)
-------------------------------
SET @str = 'abc'

SELECT substring(@str, 1, charindex('&', @str + '&') - 1)


Insert values from a record variable into a subclass table

I've realized my problem has a very simple solution. As long as we are sure about order of the schema and the extra columns are after all the original columns, my problem is simply solved by adding the values to the SELECT of the INSERT INTO

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record
   computed_column1 := 'some value';
   EXECUTE format('INSERT INTO %s SELECT $1.*, computed_column1', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 are tables with identical schemas, but t1 has added a varchar column. The name of the column doesn't matter here because we're only using the order of the columns as they appear in the schemas of the two tables.


How to use an alias name that has RANK() OVER in a WHERE clause

I would like to use the Rnk column in the WHERE clause

The Rnk is a column computed in the SELECT clause. It's not avaiable in the WHERE clause of the same level, as the logical order of execution a query is FROM -> WHERE -> SELECT.

You have to wrap the query in a subquery. You can use either a CTE (Common Table Expression):

USE SMSResults ;
go
with CTE as
    ( SELECT Student_No,Result,Module_Name,Semester,Year,
             RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
      FROM tbl_results
      WHERE Student_No = '201409')
select * from CTE
where rnk > 1   -- change here with whatever you want ... !!
ORDER BY Year DESC ;

or a derived table:

USE SMSResults ;
go
select * from
    ( SELECT Student_No,Result,Module_Name,Semester,Year,
             RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
      FROM tbl_results
      WHERE Student_No = '201409')
  AS derived_table
where rnk > 1   -- change here with whatever you want ... !!
ORDER BY Year DESC ;

As a side note for future readers - worth reading - What's the difference between a CTE and a Temp Table? by JNK♦


How to write a query which finds all circular references when a table references itself?

This calls for a recursive CTE:

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX))
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId
  AND R.ParentId <> 0;

See it in action here: SQL Fiddle

Update:

Added distance to be able to exclude all self cycles (see ypercube's comment):

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path, 0 Distance
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX)), C.Distance + 1
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId
  AND R.ParentId <> 0
  AND R.Distance > 0;

SQL Fiddle

Which one you should use depends on your requirement.


How do I kill Oracle DataPump expdp job stuck in DEFINING state?

expdp stgdata@ORADB1S attach=EXPORT_TABLE_2017_1205_1402
EXPORT> Kill_job

Have you tried dropping the export table?

DROP TABLE SYS.EXPORT_TABLE_2017_1205_1402;


stored proc to handle XML as input param for multiple record inserts

I c an't speak for performance but this is one way of doing things.

declare @x xml

select @x = '<MessageRecipient>
<MessageId>52</MessageId>

<Recipient>
        <RecipientTypeId>2</RecipientTypeId>
        <EmailAddress>dig.s@mail.com</EmailAddress>
        <FriendlyName>dig s</FriendlyName>
        <UFID>dig123</UFID>
</Recipient>

<Recipient>
    <RecipientTypeId>3</RecipientTypeId>
    <EmailAddress>ash.k@mail.com</EmailAddress>
    <FriendlyName>ash k</FriendlyName><UFID>ashish</UFID>
</Recipient>

<Recipient>
    <RecipientTypeId>4</RecipientTypeId>
    <EmailAddress>dee.v@mail.com</EmailAddress>
    <FriendlyName>dee v</FriendlyName>
    <UFID>dee123</UFID>
</Recipient>
</MessageRecipient>'

select
    Finaldata.R.value ('../MessageId[1]', 'NVARCHAR(10)') AS MessageID
    , Finaldata.R.value ('RecipientTypeId[1]', 'INT') AS RecipientTypeId
    , Finaldata.R.value ('EmailAddress[1]', 'NVARCHAR(200)') AS EmailAddress
    , Finaldata.R.value ('FriendlyName[1]', 'NVARCHAR(100)') AS FriendlyName
    , Finaldata.R.value ('UFID[1]', 'NVARCHAR(10)') AS UFID
from @x.nodes ('//MessageRecipient/Recipient') as Finaldata (R)


SQLServer:Buffer Cache Hit ratio is a good indication of POSSIBLE memory bottleneck?

We need to look at SQLServer:Buffer Cache Hit ratio.If that ratio is less than 95% than the server is under memory pressure

Stop looking at Buffer Cache hit ratio to determine memory pressure. This is because with read ahead mechanism in SQL Server more than enough pages are alredy in buffer pool to satisfy the query so BCHR does not gives accurate figure about memory pressure. You might even see that BCHR even would not drop when there is memory pressure. All these has been explained in Great SQL Server Debate about BCHR

PLE output you posted seems really low but we cannot just use one counter to gauge memory pressure. PLE is more indication of I/O activity on server. It could be possible that due to heavy I/O activity PLE plummeted. If you note Target and Total server memory still remains same. Which is good sign.

For Edition upto 2008 R2. You can use below counters

SQLServer:Buffer Manager--CheckpointPages/sec:
SQLServer:Buffer Manager--Memory Grants Pending:
SQLServer:Buffer Manager--Target Server Memory:
SQLServer:Buffer Manager--Total Server memory
SQLServer:Buffer Manager--Free Pages
SQLServer:Buffer Manager--Free List Stall/sec
SQLServer:Buffer Manager--Page Life expectancy

For SQL Server 2012 onwards few of Buffer Pool counters are deprecated and so we have to use Memory Manger counters

SQL Server: Memory Manager-- Target Server Memory (KB)
SQL Server: Memory Manager--Total Server Memory (KB)
SQL Server: Memory Manager- Free Memory (KB)
SQL Server: Memory Manager--Database Cache Memory (KB)
SQLServer:Buffer Manager--Free Pages
SQLServer:Buffer Manager--Free List Stall/sec
SQLServer:Buffer Manager--Page Life expectancy

Note if you have heavy disk activity do not forget to refer Disk related counters as well. Create a data collector set and allow it to run for 4-5 hrs when load on system is at peak and then add the snapshot of data collector in your question. Then we can determine whether SQL Server needs more memory or not.

Personally 8G is a bit of less RAM considering workload and OS requirements these days. At the back of your head you should always think about increasing RAM.


How to retrieve data of two columns from two tables?

Try following.

select T1.M_ID,
    (select T2.T_NAME from TEAM_MASTER as T2
    where T1.TEAM1_ID = T2.T_ID) as t1_Name,
    (select T2.T_NAME from TEAM_MASTER as T2
    where T1.TEAM2_ID = T2.T_ID) as t2Name
    from MATCH_MASTER as T1;


How to get SHOW CREATE TABLE without table borders made of dashes?

You need to use \G as in SHOW CREATE TABLE options \G

EXAMPLE

mysql> show create table mysql.db \G
*************************** 1. row ***************************
       Table: db
Create Table: CREATE TABLE `db` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Is your concern performance, or that you are charged per IO?

Activate "pg_stat_statements" and turn on "track_io_timing".  Then you can look in "blk_read_time" column to find queries that spend a lot of time reading data from disk.  I recommend to look at the time column, not the count column "shared_blks_read", because a lot of the "reads" will actually be fulfilled by the OS's filesystem cache, so the count doesn't give you an accurate picture of true disk IO.  Timing should give a more accurate picture of that.

pg_stat_statements doesn't give you info on when queries are being run, other than 'since the last pg_stat_statements_reset() call'.  If you want to able to line the queries up with your chart, then you would want to enable "auto_explain", again after turning "track_io_timing" on, and adopt the settings of:

auto_explain.log_min_duration = '50ms' ## need to tweak for your needs
auto_explain.log_analyze=on
auto_explain.log_timing=off  ## this may have high overhead, so turn off
auto_explain.log_buffers=on

And then go mine the data out of the server log file.  This does come with an overhead, so you might to turn it off again once you find the problem.  The amount of overhead depends on your hardware and kernel, on modern systems I find it low enough to be worth paying to leave it on all the time, just cranking up "log_min_duration" to the point few queries get logged when I am not actively tracking a problem down.


This is essentially an islands problem, except you are trying to find all the rows that don't belong to an island (these are gaps, but conceptually I find it easier to think about the islands). Basically, we want to find all of the possible days in your overall range, then identify the days that are "covered" within that range. The end result you want are all of the islands of days that are not covered.
My solution assumes you are on a currently supported version of SQL Server (2012+). Please always use a tag for the minimum version of SQL Server you need to support, since that can change the answer and prevent people from wasting time.
-- sample table + data:
DECLARE @t table(PlaceID int, StartDate date, EndDate date);

INSERT @t(PlaceID, StartDate, EndDate) VALUES
(1,'20200101','20200104'),(1,'20200110','20200115'),
(2,'20200103','20200106'),(2,'20200120','20200123');

-- input parameters (place you care about, start and end of date range):
DECLARE @PlaceIDofInterest   int  = 1,
        @StartDateOfInterest date = '20200101',
        @EndDateOfInterest   date = '20200131';

;WITH date_range(d) AS -- the entire range of days we care about
(
  SELECT @StartDateOfInterest UNION ALL
  SELECT DATEADD(DAY, 1, d) FROM date_range
         WHERE d < @EndDateOfInterest
),
islands AS -- grouped sets of days _not_ covered
(
  SELECT r.d, island = DATEADD(DAY, DENSE_RANK() OVER (ORDER BY r.d) * -1, r.d)
    FROM date_range AS r
    LEFT OUTER JOIN @t AS t
      ON  r.d >= t.StartDate
      AND r.d <= t.EndDate
      AND t.PlaceID = @PlaceIDofInterest
    WHERE t.PlaceID IS NULL
)
SELECT MIN(d), MAX(d) -- for each island, grab the start and end
  FROM islands
  GROUP BY island
  ORDER BY MIN(d);

Generating the contiguous range of dates is a topic of its own. I like the recursive CTE option because it's quick and easy for me to remember, and doesn't require any other objects to exist. But it has a quirk: If the range of days could be more than 100, you'll need to add OPTION (MAXRECURSION x) to the end of the statement (where x is the largest number of days you would need to support). If x > 32,767 (89 years or so), x will need to be 0.
If you don't like recursive CTEs, but you already have a calendar table (say, dbo.Calendar with a [date] column), the date_range CTE can simply be:
;WITH date_range(d) AS
(
  SELECT [date] FROM dbo.Calendar
  WHERE [date] >= @StartDateOfInterest
    AND [date] <= @EndDateOfInterest
),

If you don't like recursive CTEs, and don't have a calendar table, but you do have a numbers table (say, dbo.Numbers, with an int column named num), you can do this for the initial CTE:
;WITH date_range(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDateOfInterest, @EndDateOfInterest) + 1)
    DATEADD(DAY, num-1, @StartDateOfInterest)
  FROM dbo.Numbers
  WHERE num >= 1 ORDER BY num
),

And if you don't like recursive CTEs and you don't have a calendar or numbers table, you can do this:
;WITH date_range(d) AS
(
  SELECT TOP (DATEDIFF(DAY, @StartDateOfInterest, @EndDateOfInterest) + 1)
    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id])-1, @StartDateOfInterest)
  FROM sys.all_columns
  ORDER BY [object_id]
),

If you don't like recursive CTEs, you don't have a calendar or numbers table, and the caller doesn't have access to catalog views like sys.all_columns, I give up.
More information here:

Generate a set or sequence without loops – part 1
Generate a set or sequence without loops – part 2
Generate a set or sequence without loops – part 3
The SQL Server Numbers Table, Explained - Part 1
The SQL Server Numbers Table, Explained - Part 2
Creating a date dimension or calendar table in SQL Server


Delete duplicate records by column 1 but having differ values in column 2 and column 3

It looks like you want to keep the rows where the combination of SSN, FName and LName are the most common.

You can do that with an updateable CTE that counts the number of occurrences for each combination using count() over() and then enumerates the counts using rank().

declare @T table
(
  SSN int,
  FName char(1),
  LName char(1)
);

insert into @T values
(1, 'a', 'b'),
(1, 'a', 'b'),
(1, 'b', 'b'), -------------> to delete
(2, 'c', 'd'),
(2, 'c', 'd'),
(3, 'a', 'b'),
(3, 'c', 'd');

with C1 as
(
  select T.SSN,
         count(*) over(partition by T.SSN, T.FName, T.LName) as C
  from @T as T
), C2 as
(
  select rank() over(partition by C1.SSN order by C1.C desc) as RN
  from C1
)
delete from C2
where C2.RN > 1;

select *
from @T;

Result:

SSN         FName LName
----------- ----- -----
1           a     b
1           a     b
2           c     d
2           c     d
3           a     b
3           c     d

In the case where there is a tie for most common combination (SSN = 3) the above query will keep all the rows for the tie.


Time taken to execute query seems longer than time measured in query...?

The elapsed time measured by the T-SQL batch will include only execution time.

The elapsed time measured by the client application will include not only execution time but also compilation time and network latency.

Run your batch with SET STATISTICS TIME ON to see if the unaccounted time is due to compilation. With SSMS, you can also include more detailed client statistics (Query-->Include Client Statistics ).


Is there a better option than Union All for multiple selects from the same row?

You can use CROSS APPLY ... VALUES to UNPIVOT multiple columns

SELECT ID,
       OptionID,
       OptionName,
       OptionLName
FROM   [dbo].[Base]
       CROSS APPLY (VALUES([Option1ID], [Option1Name], [Option1LName]),
                          ([Option2ID], [Option2Name], [Option2LName]),
                          ([Option3ID], [Option3Name], [Option3LName]))
                     V( OptionID, OptionName, OptionLName)

The execution plan for this has one scan of Base. The plan is in fact the same as for the 2005 compatible rewrite that uses UNION ALL

SELECT ID,
       OptionID,
       OptionName,
       OptionLName
FROM   [dbo].[Base]
       CROSS APPLY (SELECT [Option1ID], [Option1Name], [Option1LName] UNION ALL
                    SELECT [Option2ID], [Option2Name], [Option2LName] UNION ALL
                    SELECT [Option3ID], [Option3Name], [Option3LName])
                     V( OptionID, OptionName, OptionLName)

But I presume the UNION ALL you were trying to avoid was the multiple scans of

SELECT ID,
       [Option1ID],
       [Option1Name],
       [Option1LName]
FROM   [dbo].[Base]
UNION ALL
SELECT ID,
       [Option2ID],
       [Option2Name],
       [Option2LName]
FROM   [dbo].[Base]
UNION ALL
SELECT ID,
       [Option3ID],
       [Option3Name],
       [Option3LName]
FROM   [dbo].[Base]


How do I determine if a table exists in a SQL Server database in SQL Server 2008?

If you query the sysobjects table, with a query like

SELECT * FROM sysobjects WHERE xtype = 'U' AND name = 'yourTableName'

xtype = 'U' is a user table

you can then wrap this is an IF EXISTS statement

IF EXISTS (SELECT * FROM sysobjects ...)
BEGIN
    ' do your stuff here if it exists
END


Shrink log file after configuring backups properly

I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).

IMHO, 5MB autogrowth is not sensible. You should monitor your log growth using default trace and find a good value for autogrowth.

Below script will help you monitor your Log (and Data file) autogrowths. Trend it over a week (or whatever suits your need). This will help you get a "near to best number" for your autogrowth setting.

IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
    DROP TABLE #autogrowthTotal;
IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
    DROP TABLE #autogrowthTotal_Final;

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
  ,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.[FileName] as LogicalFileName
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
,mf.physical_name AS PhysicalFileName
into #autogrowthTotal
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
join sys.master_files mf on (mf.database_id = ftg.databaseid) and (mf.name = ftg.[FileName])
WHERE (ftg.EventClass = 92  -- Data File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

select count(1) as NoOfTimesEventFired
        , CONVERT(VARCHAR(10), StartTime, 120) as StartTime
        , EventName
        , DatabaseName
        , [LogicalFileName]
        , PhysicalFileName
        , SUM(GrowthMB) as TotalGrowthMB
        , SUM(DurMS) as TotalDurationMS
into #autogrowthTotal_Final
from #autogrowthTotal
group by CONVERT(VARCHAR(10), StartTime, 120),EventName,DatabaseName, [LogicalFileName], PhysicalFileName
having count(1) > 5 or SUM(DurMS)/1000 > 60 -- change this for finetuning....
order by CONVERT(VARCHAR(10), StartTime, 120)

-- Report back the results
select * from #autogrowthTotal_Final
order by TotalDurationMS desc
-- optional Filters
-- where DatabaseName = ''

  Why shouldn't I use autogrow if a log file is really being recreated after a differential backup?

This is not true. When you take "Log Backups", SQL Server - for databases in proper FULL or BULK_LOGGED recovery models will truncate the transaction log i.e the portion of transaction log will be marked as "no longer needed" and will be overwritten.

Read up more on : Misconceptions around the log and log backups: how to convince yourself  and 8 Steps to better Transaction Log throughput

  I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).
  How can I do it and what are the consequences of shrinking the file?

You can use DBCC SHRINKFILE(yourdb_log, 200);. Remember to use SHRINKFILE and NOT SHRINKDATABASE.

I would suggest you to be PROACTIVE (monitor your log autogrowths and give a sensible value for autogrowth) rather being REACTIVE (frequently shrink log file, because you think that you will need disk space (or for whatever reasons)).

You should read up answers from Mike Walsh and Aaron Bertrand for Why Does the Transaction Log Keep Growing or Run Out of Space? to understand the consequences of shrinking log file.


I believe, I found it)

mysql> RESET SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql>


Select all rows with multiple conditions in another table

You can get the result by using a combination of a WHERE, GROUP BY, and HAVING to return the products that have both the Book and Audio values.

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
where t.key = 'Format'
  and t.value in ('Book', 'Audio')
group by p.id, p.name
having count(distinct t.value) = 2;

See SQL Fiddle with Demo.  Basically, you'll place the values that you want in the WHERE clause - this would be the Book and Audio.  Then you'll GROUP BY the product.id and product.name these are unique to each product. Finally you will use the HAVING clause to count the distinct properties.value that are returned.  This should equal the number of items that you have in your WHERE clause.

This could also be written using conditional aggregation inside of the HAVING:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) > 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) > 0

See SQL Fiddle with Demo

If you want to include the Language key, then you can use conditional aggregation in the HAVING clause:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having
  sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) > 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) > 0
  and sum(case when t.key = 'Language' and t.value = 'English' then 1 else 0 end) > 0

See Demo


Optimizing bulk update performance in PostgreSQL

Assumptions

Since information is missing in the Q, I'll assume:

Your data comes from a file on the database server.
The data is formatted just like COPY output, with a unique id per row to match the the target table.
If not, format it properly first or use COPY options to deal with the format.
You are updating every single row in the target table or most of them.
You can afford to drop and recreate the target table.
That means no concurrent access. Else consider this related answer:

Best way to populate a new column in a large table?

There are no depending objects at all, except for indices.

Solution

I suggest you go with a similar approach as outlined at the link from your third bullet. With major optimizations.

To create the temporary table, there is a simpler and faster way:

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

A single big UPDATE from a temporary table inside the database will be faster than individual updates from outside the database by several orders of magnitude.

In PostgreSQL's MVCC model, an UPDATE means to create a new row version and mark the old one as deleted. That's about as expensive as an INSERT and a DELETE combined. Plus, it leaves you with a lot of dead tuples. Since you are updating the whole table anyway, it would be faster overall to just create a new table and drop the old one.

If you have enough RAM available, set temp_buffers (only for this session!) high enough to hold the temp table in RAM - before you do anything else.

To get an estimate how much RAM is needed, run a test with a small sample and use db object size functions:

SELECT pg_size_pretty(pg_relation_size('tmp_tbl'));  -- complete size of table
SELECT pg_column_size(t) FROM tmp_tbl t LIMIT 10;  -- size of sample rows

Complete script

SET temp_buffers = '1GB';        -- example value

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

COPY tmp_tbl FROM '/absolute/path/to/file';

CREATE TABLE tbl_new AS
SELECT t.col1, t.col2, u.field1, u.field2
FROM   tbl     t
JOIN   tmp_tbl u USING (id);

-- Create indexes like in original table
ALTER TABLE tbl_new ADD PRIMARY KEY ...;
CREATE INDEX ... ON tbl_new (...);
CREATE INDEX ... ON tbl_new (...);

-- exclusive lock on tbl for a very brief time window!
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

DROP TABLE tmp_tbl; -- will also be dropped at end of session automatically

Concurrent load

Concurrent operations on the table (which I ruled out in the assumptions at the start) will wait, once the table is locked near the end and fail as soon as the transaction is committed, because the table name is resolved to its OID immediately, but the new table has a different OID. The table stays consistent, but concurrent operations may get an exception and have to be repeated. Details in this related answer:

Best way to populate a new column in a large table?

UPDATE route

If you (have to) go the UPDATE route, drop any index that is not needed during the update and recreate it afterwards. It is much cheaper to create an index in one piece than to update it for every individual row. This may also allow for HOT updates.

I outlined a similar procedure using UPDATE in this closely related answer on SO.


It sounds like you have removed the View Any Database permission from the Public role.  If the Public role doesn't have the View Any Database permission an account has to be the owner of the DB to see it's row in sys.databases.


access columns returned by psql from bash

In addition to what @Milen already provided, you may want -A (--no-align) to remove leading white space:

psql "con params" -tAc "SELECT pg_size_pretty(pg_database_size('mydb'))"

As an aside: pg_size_pretty() may or may not be of interest.


Check PostgreSQL documentation for detailed syntax for INSERT statement.

In particular, there's an optional ON CONFLICT part:

ON CONFLICT [ conflict_target ] conflict_action

  where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )
[ WHERE index_predicate ]
ON CONSTRAINT constraint_name

  and conflict_action is one of:

DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
                ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
                ( column_name [, ...] ) = ( sub-SELECT )
              } [, ...]
          [ WHERE condition ]

In your case, you probably want to append at the end of your INSERT statement:

ON CONFLICT DO NOTHING

and the conflicts will be skipped, and further inserts will continue.

This answer is based on @Akina's comment. Thanks!


The parameter list in @params can have names matching the names of the parameters used by the sp_execute_external_script procedure itself, like @script or @language, for instance. There would need to be a way, then, to determine whether a parameter pertains to the system SP or to the user script.

Making it a rule that @params be always specified last in the list of the parameters pertaining to sp_execute_external_script and all the parameters specified after @params be considered pertaining to the user script is one easy way to implement the distinction. So, if a parameter like @language is specified after @params, it will be interpreted as a script parameter and not the SP's parameter.

And I believe that is what is happening in the cases where you are specifying @output_data_1_name or @language after @params. Those parameters simply must go before @params to avoid potential ambiguity.

Granted, there is not a word about this in the manual, which, however, is a problem with the manual more than a problem with sp_execute_external_script.


Thanks to @ypercubeᵀᴹ , one can go about it by first creating intermediary type with all the required columns (from both tables, since it's join) such as:

CREATE TYPE suppliers_products AS (s_guid VARCHAR(64), supplierId, ...)

The following query can be then written such as:

SELECT (unnest(r.agg)).*
  FROM (
     SELECT array_agg(x :: suppliers_products) AS agg
     FROM (
            SELECT *
            FROM northwind.u_suppliers S LEFT JOIN northwind.u_products p ON S.guid = p.supplier
          ) x
     GROUP BY x.supplier
   ) r
  LIMIT 1;

If there was no anonymous record (coming from join), we would not need intermediate type.

As far as I'm aware there is no way to do it without manually preparing such type upfront.


MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

The first thing that comes to mind is the Server Model : db.m1.large
What limiting factors are placed on a MySQL RDS?
If you spin up an Amazon RDS instance of MySQL, you would subject yourself to whatever  constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

max_connections
innodb_buffer_pool_size

Here is a Chart I posted Last Month
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I posted this in my other posts in the DBA StackExchange

Local database vs Amazon RDS
Scaling Percona datacenters: setup and replication

You are using m1.large. Since the InnoDB Buffer Pool is 3/4 of the Instance. That's means you have 7.2G but only 1.8 is usable. That model is capable of having up to 623 connections. Each connection can consume memory because of

join buffers
sort buffers
read buffers
thread info

Amazon RDS is simply micromanaging resources. Since DB Connections can consume RAM, connections are probably being disallowed due to the lack of RAM needed.
SUGGESTIONS

Try reducing InnoDB log IO innodb_flush_log_at_trx_commit=0 during the mass creation
Make sure you are not doing large transactions during any automatic backups or snapshots
Try a bigger server model m1.xlarge or m2.xlarge


How to retrieve foreign key constraints data

After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:

SELECT
    CONS.CONSTRAINT_NAME,
    CONS.TABLE_NAME,
    COLS.COLUMN_NAME,
    CONS.R_CONSTRAINT_NAME,
    CONS_R.TABLE_NAME R_TABLE_NAME,
    COLS_R.COLUMN_NAME R_COLUMN_NAME

FROM USER_CONSTRAINTS CONS
    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME

-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'

ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME


Storing STRING and BOOL in a STRING Column?

Use tinyint for these and use boolean in your ORM. SO it will automatically mapped from boolean to 0 and 1.

Its not good way to store boolean as string and then checking the value and assigning bool value on basis of string match.

For boolean just 0 and 1 value in your column as tinyint will solve your problem. For other column which arestring and if cardinality of the values in that column is very small as compared to overall data size then you can either use ENUM datatype or normalize table by joining it with another table containing those values.


SQL Server chooses Nested Loop join with dimensional table and make seek for each row

There appear to be three reasons why you get a serial nested loop join plan in one of your environments and a hash join in the other. Based on the information that you've provided, the best fixes involve query hints or splitting the query into two parts.

Differences between your environments

One environment has 480662 rows in your CCI and the other has 686053 rows. I wouldn't call that nearly identical. There also appears to be a hardware or configuration difference between your environments, or at the very least you're getting very unlucky. The serial sort of 251 MB of estimated data has an IO cost of 0.0037538 units. The parallel sort of 351 MB of estimated data has an IO cost of 23.1377 units, even though it is discounted by parallelism. The engine expects to spill a relatively significant amount of data for the parallel plan. Differences like that can lead to different plans between environments.
The optimizer misapplies a row goal cost reduction which can favor a nested loop join plan

The nested loop plan is costed as if only 100 rows need to be output from the sort:

However, the query contains the following in the SELECT clause: COUNT(*) OVER ()

The engine must read all rows in order to produce the correct result for the aggregate. This is indeed what happens in the actual plan, and the index seek is executed 450k times instead of 100 times. This cost reduction appears to happen on a variety of versions (I tested back to 2016 SP1 base), on both CEs, with many different window functions, and for both batch mode and row mode. It is a limitation in the product which results in a suboptimal query plan here.
The nested loop join plan is serial due to a limitation with batch mode sorts

It's possible that your serial nested loop join qualifies for parallelism (depends on your CTFP) and you may be wondering why the optimizer did not find a lower costed parallel plan. The optimizer has heuristics which prevent a parallel batch mode sort from being the first child of a nested loop join (which must run in row mode). The problem is that the parallel batch mode sort will put all rows on a single thread which won't work well with a parallel nested loop join. Moving the sort to be a parent of the loop join won't result in a decrease in the number of estimated executions for the index seek (due to the optimizer problem). As a result you're very likely to end up with a serial plan, even if CTFP was set to the default of 5.

Here is a reproduction of your issue, which I can't upload to PasteThePlan because it doesn't support my version of SQL Server:

drop table if exists cci_216665;

create table cci_216665 (
    SORT_ID BIGINT,
    JOIN_ID BIGINT,
    COL1 BIGINT,
    COL2 BIGINT,
    COL3 BIGINT,
    INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO cci_216665 WITH (TABLOCK)
SELECT TOP (500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 50
, 0, 0, 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

drop table if exists YEAH_NAH;

CREATE TABLE dbo.YEAH_NAH (ID INT, FILLER VARCHAR(20), PRIMARY KEY (ID));

INSERT INTO dbo.YEAH_NAH WITH (TABLOCK)
SELECT TOP (50) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 'CHILLY BIN'
FROM master..spt_values t1;

GO

-- takes 780 ms of CPU with nested loops
SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID;

-- takes 111 ms of CPU with hash join
SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID
OPTION (HASH JOIN);

The most straightforward way to solve your problem is to split your queries in two. Here's one way to do it:

SELECT COUNT(*)
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID;

SELECT TOP (100) *
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID;

On my machine this is actually faster than the hash join plan but you may not see the same results. In general, my first attempt for a query like yours would be to avoid a window aggregate without an OVER clause when only the first 100 rows are needed.

A reasonable alternative is to use the DISABLE_OPTIMIZER_ROWGOAL use hint introduced in SQL Server 2016 SP1. For this type of query, there's a problem with row goals so this hint directly addresses the problem without any dependence on statistics or anything like that. I would consider it to be a relatively safe hint to employ.

SELECT TOP (100)
*, COUNT(*) OVER ()
FROM cci_216665 c
INNER JOIN YEAH_NAH y ON c.JOIN_ID = y.ID
ORDER BY SORT_ID
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

This results in a hash join plan on my machine.


Drop all tables from database command apart from named few

Using such a CURSOR, you can print or execute all the DROP statements:

DECLARE cur_del CURSOR FOR
    SELECT QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)
    FROM sys.tables
    WHERE name NOT IN ('x', 'y', 'z', ...);

OPEN cur_del;

DECLARE @table sysname;
DECLARE @sql nvarchar(max);

FETCH NEXT FROM cur_del
INTO @table;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql =  'DROP TABLE ' + @table + ';';
    PRINT @sql;
    --EXEC sp_executesql @sql;

    FETCH NEXT FROM cur_del INTO @table
END

CLOSE cur_del;
DEALLOCATE cur_del;

This query only prints the DROP statements out. You can uncomment the EXEC line if you want DROP them.

The first SELECT does not look for anything at all aside from what is included in the IN(...) and you have to make sure it does not match more table than what you really want to delete.

In the WHERE clause, you can replace name (table name) by the fully qualify name with schema using QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name) NOT IN ('schema.x', ...).
If there is only one schema, you can add AND SCHEMA_NAME(schema_id) = 'xxx' and keep name.

It also does not look for dependencies which may prevent DROP.

If there are Foreign Keys, you can first DROP them using a similar cursor along with this query:

SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(f.schema_id))+'.'+ QUOTENAME(OBJECT_NAME(o.object_id)) + ' DROP CONSTRAINT ' +  QUOTENAME(f.name)
FROM sys.foreign_keys f
INNER JOIN sys.objects o ON f.parent_object_id = o.object_id
WHERE OBJECT_NAME(f.referenced_object_id) NOT IN ('x', 'y', 'z')
    AND  OBJECT_NAME(o.object_id) NOT IN ('x', 'y', 'z');


How to include in the result of a `SELECT ... GROUP BY ...` all the other columns that are functionally dependent on the grouping ones?

Under the assumptions that you have some columns (say x and y in your example), that you don't know if they are functionally equivalent or not - and that these columns do not have any NULL values (which would complicate things), you can use:

SELECT
    u, v, w,                    -- the grouping columns

    AVG(z) AS z_avg,            -- the non-functionally dependent

    CASE WHEN MIN(x) = MAX(x)   -- a possibly functionally dependent
      THEN MIN(x) ELSE NULL     -- column
    END AS x,

    CASE WHEN MIN(y) = MAX(y)   -- another one
      THEN MIN(y) ELSE NULL
    END AS y
FROM
    a
GROUP BY
    u, v, w ;

If a column is not functionally dependent (with the current data), it will have at least 2 distinct values, so the MIN and MAX will be different and the result will be shown as NULL in that column, in all rows. You can then remove it from the query.


Best practice for securely backing up RDS Postgres "offsite"?

I think your way of thinking with a separate machine in AWS which has access to the db is generally the right way to do it, and would just add that the "normal" way of making incremental backups (with any "real" database) is named PITR, for Point-in-time-recovery. If you search for this term and PostgreSQL, you will find tutorials on how to do generic incremental database backups, and it's up to you to implement them using AWS.


Backup-SqlDatabase: Cannot bind parameter 'InputObject' Error Being Thrown

Please check
out my blog for a detailed explanation of the problem.
I was having the same issue, blogged after I couldn't find a lot of information online.

The solution comes down to importing only the required module, in stead of the modules for several versions of SQL Server.

First check what modules are being loaded:

# Get loaded assemblies
([appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*smo*"}).Location

Next make sure your session is closed if you already loaded the modules, then use this modified script to load only the modules you need:

$TempArray = @()
$TempArray = $env:PSModulePath -split ';'
# 110 for SQL 2012, 120 for SQL 2014, 130 for SQL 2016
$env:PSModulePath = ($TempArray -notmatch '110') -join ';'

Now, load SQLPS module and run commands.


Create read only user without being root

From the manual

  To use this statement, you must have the global CREATE USER privilege
  or the INSERT privilege for the mysql database.

So first you must have the root account grant you access to CREATE USER. Then your account will be able to create other users.


How to script all permissions on a schema

Your friend is sys.database_permissions to check/script out permisssions.

Below is the script that I am using when doing a refresh of PROD on DEV or UAT server. I will script out before hand all the permissions and after the restore I will just run the script.

/*
Script DB Level Permissions v2.1
Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/

DECLARE
    @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR FOR

/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********     DB USER CREATION      *********/
/*********************************************/

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
        3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
        4 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

UNION

/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
        5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
        6 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) IN (
                                                --get user names on the database
                                                SELECT [name]
                                                FROM sys.database_principals
                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                                              )
--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc
            ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
        + CASE
                WHEN cl.column_id IS NULL THEN SPACE(0)
                ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
        + CASE
                WHEN perm.state <> 'W' THEN SPACE(0)
                ELSE SPACE(1) + 'WITH GRANT OPTION'
          END
            AS [-- SQL STATEMENTS --],
        9 AS [-- RESULT ORDER HOLDER --]
FROM
    sys.database_permissions AS perm
        INNER JOIN
    sys.objects AS obj
            ON perm.major_id = obj.[object_id]
        INNER JOIN
    sys.database_principals AS usr
            ON perm.grantee_principal_id = usr.principal_id
        LEFT JOIN
    sys.columns AS cl
            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],
    10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
        END
    + SPACE(1) + perm.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE
            WHEN perm.state <> 'W' THEN SPACE(0)
            ELSE SPACE(1) + 'WITH GRANT OPTION'
      END
        AS [-- SQL STATEMENTS --],
        12 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
            END
                + SPACE(1) + perm.permission_name --CONNECT, etc
                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
                + QUOTENAME(SCHEMA_NAME(major_id))
                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> 'W' THEN SPACE(0)
                    ELSE SPACE(1) + 'WITH GRANT OPTION'
                    END
            AS [-- SQL STATEMENTS --],
        15 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
    inner join sys.schemas s
        on perm.major_id = s.schema_id
    inner join sys.database_principals dbprin
        on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema

ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT @sql
        FETCH NEXT FROM tmp INTO @sql, @sort
END

CLOSE tmp
DEALLOCATE tmp


How to change DB file location with Replication

Here's how I'd do it:

Schedule some time where the database can be down for the amount of time to move the files
Execute a series of 'alter database [foo] modify file (name = 'foo_1', filename = 'new location here') statements
alter database [foo] set offline (you'll have to kill any active spids in the db or wait for them to finish their business)
move your files
alter database [foo] set online

If you want to be really clean about this, you can stop the distribution agent while the database is offline and restart it when it's back online.  But that's not necessary.  Just so you know, I've also done this with a distribution database and would imagine that it would work fine with a publisher, too.


This is a query with an idea like in MySQL count multiple columns and sum the total occurrence

SELECT
drawno,
SUM(CASE WHEN FIRST = 0 THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN FIRST = 1 THEN 1 ELSE 0 END) one,
SUM(CASE WHEN FIRST = 2 THEN 1 ELSE 0 END) two,
SUM(CASE WHEN FIRST = 3 THEN 1 ELSE 0 END) three,
SUM(CASE WHEN FIRST = 4 THEN 1 ELSE 0 END) four,
SUM(CASE WHEN FIRST = 5 THEN 1 ELSE 0 END) five,
SUM(CASE WHEN FIRST = 6 THEN 1 ELSE 0 END) six,
SUM(CASE WHEN FIRST = 7 THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN FIRST = 8 THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN FIRST = 9 THEN 1 ELSE 0 END) nine
FROM
(
  SELECT
    first
    ,drawno
  FROM table1
  UNION ALL
  SELECT
     second
     ,drawno
  FROM table1
  UNION ALL
  SELECT
    third
    ,drawno
  FROM table1
  UNION ALL
  SELECT
    fourth
    ,drawno
  FROM table1
) AS t GROUP BY drawno;

Two queries, this and from first answer:
http://sqlfiddle.com/#!2/2eb93d/2


Find rows with specific string inside column text

Your column is most likely of the VARBINARY data type, so it's visual representation starts with 0x and displays hex characters (from 0 to F). You can't simply use LIKE against a VARBINARY column, since LIKE operates with string data types like VARCHAR or NVARCHAR.

You can convert your VARBINARY to the string you are seeing by using CONVERT with the 3rd parameter as 1, check this example:

DECLARE @Varbinary VARBINARY(MAX) = CONVERT(VARBINARY(MAX), 'This is my string value.')

SELECT
    OriginalRepresentation = @Varbinary,
    ConvertToString = CONVERT(VARCHAR(MAX), @Varbinary),
    ConvertToStringHex = CONVERT(VARCHAR(MAX), @Varbinary, 1)

Result:

OriginalRepresentation                              ConvertToString             ConvertToStringHex
0x54686973206973206D7920737472696E672076616C75652E  This is my string value.    0x54686973206973206D7920737472696E672076616C75652E

If you are using SSMS, when you query a VARBINARY column, SSMS is automatically converting your VARBINARY to the hex-string representation so it's "interpretable" to the user.

So to correctly filter using LIKE:

SELECT * FROM DB WHERE CONVERT(VARCHAR(MAX), [COLUMN], 1) LIKE '%0F86FFF00%'


Postgresql pg_basebackup from slave didnt save WAL's

You might want to try to set up max_wal_senders on your replica, and use --xlog-method=stream instead of --xlog-method=fetch, which would let the basebackup grab the individual WAL records as they are shipped, rather than trying to grab the WAL segments at the end of the basebackup. If you have to use --xlog-method=fetch, then you should set wal_keep_segments high enough to let you take a reasonable basebackup. checkpoint_segments x2 should be reasonable as a starting point.

As a reference, the documentation for the 9.3 version of pg_basebackup is here.

I highly recommend that you test out the backup by restoring it on another server, and running a few tests against it to make sure it comes up in a consistent state, and has a reasonable snapshot of the data that you wanted backed up.

One additional note, if you set up an archive_command under 9.3, and a restore_command in your recovery.conf, your streaming replica will be able to recover itself and continue if network conditions cause streaming replication to lag. An example and discussion of this problem and a solution is talked about here: http://evol-monkey.blogspot.com/2014/09/offsite-replication-problems-and-how-to.html

I hope this helps. =)


Format a value from other values in the table

While clarifying your problem you say "how can I make it so if I ever rename Beach I don't have hundreds of potential problems?" This sounds like a normalization problem to me. Looking at the examples above I think you're mixing data and formatting inside the database, then trying to untangle that in processing.

Starting from an analysis of the problem domain it looks like you have two entity types - Location (or names) and Path, where a Path connects exactly two Locations. This I would implement as two tables. As you're concerned about changing natural keys without touching many rows I'd implement surrogate primary keys (using pseudo code since each DBMS's dialect will vary).

Location
  LocationId    int primary key
  LocationName  char(999) not null

Path
  FromLocationId int not null FK references Location.LocationId
  ToLocationId   int not null FK references Location.LocationId

  Primary key (FromLocationId, ToLocationId)

insert Location values (1, 'Beach');
insert Location values (2, 'Town');
insert Path values (1, 2);

The presentation can be taken care of in SQL if you really want to, though I'd suggest letting the persistence classes of the application take care of it:

select 1 as ID, l.LocationName
from Path p
inner join Location l
  on l.LocationId = p.FromLocationId

union all

select 2, l.LocationName
from Path p
inner join Location l
  on l.LocationId = p.ToLocationId

union all

select 3, f.LocationName + ' to ' + t.LocationName
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId

union all

select 4, t.LocationName + ' to ' + f.LocationName
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId

union all

select 5, f.LocationName + ' to ' + t.LocationName + ' Path'
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId;

Because of the UNION ALL each path will produce five rows of output. These are distinguished by the constant-valued ID column in each sub-query so a where ID = .. query will work as you imagine.

Likely you will have more than one path in your database. It may be worth adding the column PathId to Path. Then your application queries become where PathID = xxx and ID = 5.

Now if you rename "Beach" to "Banana" exactly one column of one row changes (just like 3NF says it should) but all results return the new name, guaranteed, with no possibility of inconsistent results.


Group By - Incorrect syntax near 'mycolumn'

Figured it out shortly after I posted.  I need to remove the alias from the GROUP BY clause.

Solution:

SELECT
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM
    NameAddressCoverHoldingTable
GROUP BY
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))),
    PostCode,
    PolicyId
ORDER BY
    PolicyId,
    MIN(EffectiveOn);