How often to backup database and what are the favoroble practices to do so

I don't understand why you're asking this question. If you're buying in a service, then backups should be part of the SLA and not your responsibility. If it's not part of the service, then why are you using SaaS?

On the other hand, if you need to be backing up something, why are you backing up every hour?

Technically, if your application is running on a DBMS with a transaction log, it's possible to be backing up continuously, even replicating logs to (an)other machine(s). Then you have a backup process that runs regularly simply to make recovery faster (a newer backup + a small amount of logs is faster to restore than an older backup + a larger amount of logs). A retention policy on these backups and log files determines how far back in time you can go back to in case of user error (backups aren't just for when your have a "crash", they'll also be needed if someone deletes or modifies data they shouldn't have). The retention policy will often be a compromise between how far back in time you can "go" and how much space you need to store the backups + logs.


You need to decide if your categories are hierarchical (one parent) or graphical (multiple parents). For example, are "cell phones" in the category "electronics" as well as "communications"?

What kinds of fields might your categories have? Use Table Inheritance to allow categories that are similar but not identical.

Let's say you go with hierarchical categories. It should look like this:

-- hierarchical relationships usually have a single table with a field with a self-referencing foreign key:
create table category (
  id bigserial primary key,
  name text not null unique,
  parent_id bigint null references category(id) --if I'm null then I am a top-level category
);

create table ad (
  id bigserial primary key,
  subject text not null
  ...
);

-- this pattern is called a junction table:
create table ad_category (
  ad_id bigint references ad(id),
  category_id bigint references category(id),
  primary key (ad_id, category_id)
);

I would suggest Postgres over MySql, since it supports WITH queries, which make hierarchical querying easier.

Regarding images, consider storing them in Amazon S3 / Cloudfront. You would associated them with the ad via the HTML in the ad. For example, in your UI, the user would upload an image, get the URL back, and this would get plugged in to an <img> tag.

Though you could also create a junction table similar to the ad_category table, and remove orphan images from S3 on a schedule:

create table ad_picture (
  ad_id bigint not null references ad(id),
  picture_url text not null,
  unique (ad_id, picture_url)
);


Optimized ways for defragment mysql database table

No, deletions do not significantly slow down what remains.

No, an index never becomes "unusable".

What are you purging?  "Old data"?  If so, then PARTITION BY RANGE(TO_DAYS(...)) is an excellent way to purge old data.  DROP PARTITION is 'instantaneous' and does cause any fragmentation.  My blog goes into the details of how to do the PARTITIONing, and the daily/weekly/whatever maintenance, including sample code.

If you cannot use partitioning in that way, you are stuck with whatever fragmentation happens; OPTIMIZE TABLE on a 500GB table would take a loooong time.  (There is an "online" version coming in 5.7, but perhaps not released yet.)

Are you INSERTing at the "end" of the table only?  Are you doing no UPDATEs or DELETEs other than the purging?  If yes to both, then there is a way to optimize the table or to copy the table to a partitioned table...

It involves walking through the table with the PRIMARY KEY and copying chunks of rows at a time.

To discuss further, please provide SHOW CREATE TABLE and answer the questions I posed.


Aurora PostgreSQL CPU usage stuck at 100% for 8 hours

There is a long running query that keeps your CPU busy and keeps autovacuum from cleaning up dead tuples. A long queries implies a long transaction.

The query takes long because of all the dead tuples.

Kill the long running query with pg_terminate_backend, and things will go back to normal.

To prevent long running queries, set statement_timeout.


Generating a password that matches the Password Validation Component

To generating random password for user while creating new one you can use :

mysql> CREATE USER ran_user@localhost IDENTIFIED BY RANDOM PASSWORD;
+----------+-----------+----------------------+
| user     | host      | generated password   |
+----------+-----------+----------------------+
| ran_user | localhost | UN:5_z05J._*VtU3K%Qu |
+----------+-----------+----------------------+
1 row in set (0.10 sec)

For altering password of current/existing user you can use this statement :

mysql> ALTER USER ran_user@localhost IDENTIFIED BY RANDOM PASSWORD;
+----------+-----------+----------------------+
| user     | host      | generated password   |
+----------+-----------+----------------------+
| ran_user | localhost | (YU>{}tu&6@[U%!Pdw>n |
+----------+-----------+----------------------+
1 row in set (0.16 sec)

This RANDOM PASSWORD keyword satisfies password validator component :

mysql>  show global variables like '%validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.23 sec)


Why TRUNCATE TABLE cannot use four-part name?

It's because TRUNCATE is DDL rather than DML

From MSDN:

TRUNCATE TABLE
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
[ ; ]

Also, regarding permissions:

  The minimum permission required is ALTER on table_name. TRUNCATE TABLE
  permissions default to the table owner, members of the sysadmin fixed
  server role, and the db_owner and db_ddladmin fixed database roles,
  and are not transferable. However, you can incorporate the TRUNCATE
  TABLE statement within a module, such as a stored procedure, and grant
  appropriate permissions to the module using the EXECUTE AS clause.

Notice the db_ddladmin requirement.  As DDL, the four-part naming isn't allowed.  Compare it to Technet info on the FROM clause:

  If the table or view exists in another database on the same instance
  of SQL Server, use a fully qualified name in the form
  database.schema.object_name.

  If the table or view exists outside the instance of SQL Server, use a
  four-part name in the form linked_server.catalog.schema.object. For
  more information, see sp_addlinkedserver (Transact-SQL). A four-part
  name that is constructed by using the OPENDATASOURCE function as the
  server part of the name can also be used to specify the remote table
  source. When OPENDATASOURCE is specified, database_name and
  schema_name may not apply to all data sources and is subject to the
  capabilities of the OLE DB provider that accesses the remote object.

Sources:

http://msdn.microsoft.com/en-us/library/ms177570.aspx

http://technet.microsoft.com/en-us/library/ms177634.aspx

Edit:
To answer your question on how to do it, write a stored procedure in the target database.  Grant access to execute the procedure, and call truncate from the procedure.


You can use symbolic links with MySQL. All you would need to do is make a symbolic link for the .frm, .MYI and .MYD files of your MyISAM tables into the MySQL database schema directory that you want. After that, run flush tables; and they should show up.

It is also important that the MySQL user is able to access those files, so you might have a bit of an issue with user rights.

Also, depending on your MySQL version, there maybe a setting in the my.cnf which enables/disables symbolic links, so you would need to keep an eye on that too.


Add auto increment to already existing primary key column PostgreSQL

If that is too much boilerplate code, then create a function that does the job:
create or replace function make_serial(p_table_schema text, p_table_name text, p_column_name text)
  returns void
as
$$
declare
  l_sql text;
  l_seq_name text;
  l_full_name text;
begin
  l_seq_name := concat(p_table_name, '_', p_column_name, '_seq');
  l_full_name := quote_ident(p_table_schema)||'.'||quote_ident(l_seq_name);

  execute format('create sequence %I.%I', p_table_schema, l_seq_name);
  execute format('alter table %I.%I alter column %I set default nextval(%L)', p_table_schema, p_table_name, p_column_name, l_seq_name);
  execute format('alter table %I.%I alter column %I set not null', p_table_schema, p_table_name, p_column_name);
  execute format('alter sequence %I.%I owned by %I.%I', p_table_schema, l_seq_name, p_table_name, p_column_name);
  execute format('select setval(%L, coalesce(max(%I),0)) from %I', l_full_name, p_column_name, p_table_name);
end;
$$
language plpgsql;

You can remove the set not null statement if you know that all your columns are already defined as NOT NULL.
To synchronize the sequence with the existing values, the above code essentially runs:
select setval('person_id_seq', max(person_id))
from person;

(Updating all rows and possibly changing their primary key values is the wrong approach to sync the sequence).
Then to change a table, all you need to do is:
select make_serial('public', 'person', 'person_id');


Change the sql table output - Rows to Columns

I found a very simple way of doing this. It might be not pretty but it's good enough for me!
-- Delete temporary tables
If OBJECT_ID('tempdb..#tempTable0') Is Not Null DROP TABLE #tempTable0
If OBJECT_ID('tempdb..#tempTable1') Is Not Null DROP TABLE #tempTable1
If OBJECT_ID('tempdb..#tempTable2') Is Not Null DROP TABLE #tempTable2
If OBJECT_ID('tempdb..#tempTable3') Is Not Null DROP TABLE #tempTable3

-- Add a row number to each record
SELECT *, ROW_NUMBER() OVER(ORDER BY (Select 0)) AS Row_Num
INTO #tempTable0
FROM Table1

-- Copy each record into a temporary table; Add 1 to be able join the temporary tables
Select Id as ID_1, Name as Name_1, 1 AS Row_Num Into #tempTable1 From #tempTable0 where Row_Num = 1
Select Id as ID_2, Name as Name_2, 1 AS Row_Num Into #tempTable2 From #tempTable0 where Row_Num = 2
Select Id as ID_3, Name as Name_3, 1 AS Row_Num Into #tempTable3 From #tempTable0 where Row_Num = 3

-- Final Record
Select ID_1, Name_1, ID2, Name_2, ID_3, Name_3
From
    #tempTable1 as T1 Left Join
    #tempTable2 as T2 On T2.Row_Num = T1.Row_Num Left Join
    #tempTable3 as T3 On T3.Row_Num = T1.Row_Num


Best practices for backing up a MySQL DB

Best Practices to take MySQL server backup:
MySQL Replication
Setup Replication in MySQL. You will have to setup Master and Slave server. All read-writes to the DB could go to your Slave Server.
Advantage of having Replication is you can take a backup from your slave server without interrupting Master server, Your application will continue to work on Master without any downtime.
Using MySQL Dump
If your data set is small (I realize "small" is a relative term.. to qualify it, let's say <10GB), then mysqldump will probably work great. It's easy, it's online and it's very flexible. Just a few things mysqldump can do:
backup everything or just certain databases or tables
backup only the DDL
optimize the dump for a faster restore
make the resultant sql file more compatible with other RDBMSes
and many more things.
However, the most important options are related to the consistency of your backup. My favorite options are:
--single-transaction : this option gives a consistent backup, if (and only if) the tables are using InnoDB storage engine. If you have any non-read-only MyISAM tables, then don't use this option when backing them up.
--master-data=2 : this option will make sure your dump is consistent (by doing a lock-all-tables unless you've added the option --single-transaction). The --master-data option also records the binary log position in the resulting dump file (=2 causes this line to be a comment in the dump file)
Final note about mysqldump: keep in mind that the restore time may be significantly longer than the backup time. It will depend on several factors, for example, how many indexes you have.
LVM snapshot
For those that have larger datasets, a physical backup is the way to go. While you could take a cold backup (i.e., shutdown the MySQL service, copy the data directory, restart the service), many people do not want downtime. My favorite solution is snapshots. This can be hot (for InnoDB) or require a brief lock (for MyISAM). Don't forget to include all your data (include the ib_logfiles). Lenz provides a nice utility to help with this: http://www.lenzg.net/mylvmbackup/
Using MySQL Enterprise Backup
Advantages of using MySQL Enterprise Backup:

"Hot" Backups of InnoDB tables takes place entirely online, without blocking
Backup only particular tables or tablespaces
Only backup the data that changed since a prior backup
Compressed Backup - Saves storage upto 90% and many more..

Reference:
http://www.mysql.com/products/enterprise/backup/features.html
http://www.mysql.com/products/enterprise/backup.html


If I divide the time for query planning and execution by the number of rows returned, I find that the "big" query took 0.00054 milliseconds per result row, while the "small" one took 0.00234 milliseconds, more than four times as long.
If you add to that the (unknown) time it took to get the shape_ids in the first place, I cannot see how splitting up the query can lead to a performance advantage.
Besides, you later have to combine all the small result sets and sort them by shape_pt_sequence, which will also take some time.
The "big" query doesn't sort at all, it uses an index scan, and that is probably the most efficient way to go about it.


How can I create a user who could only create tables and can't delete/drop them

As the manual says,

  To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.)

You need to either run execute CREATE TABLE as superuser, or create a SECURITY DEFINER function (again, as superuser) that creates such tables and assigns their owner roles, then grant the EXECUTE privilege on that function only to the user in question. See additional notes on security in this case.


json functions: double quotes added when going through a variable

@Evan Carroll is certainly right about the general implementation of JSON in MariaDB.
But if you are still going with MariaDB this might help you:

The behavior you describe is a serious bug which is tracked here:
https://jira.mariadb.org/browse/MDEV-13701
As far as I understood its expected to be fixed with MariaDB-10.2.12.

For the time being I use the following hack:

set @jsonapiheader = json_object("jsonapi",json_object("version", CAST(1.0 AS DECIMAL(2,1))));
set @temp_json = concat('{"data":',@jsonapiheader,'}');
select @temp_json, json_valid(@temp_json);

Output:

{"data":{"jsonapi": {"version": 1.0}}}        |       1

Also look at the CAST within the first JSON_OBJECT. It also works with variables and functions and results in a more accurate JSON output.


The links and information are confusing but we did find a solution.

The short version is you need to install Visual Studio 2015. I used the free Visual Studio 2015 Community Edition. That includes the components needed to run Team Explorer.

Once we installed VS the Team menu is now visible in SQL Server 2016 SSDT. You can then add and configure TFS as before.


Is there a better way to handle a multi-level ParentId table structure?

I would always store the intermediate entities, possibly by adding missing ones as dummy entries, so that you can query using always the same query.
E.g., store

Book > Chapter

as

Book Series (dummy) > Book > Volume (dummy) > Chapter

Now, you can query
SELECT *
FROM
   BookSeries bs
   LEFT JOIN Book b      ON bs.BookSeriesID = b.BookSeriesID
   LEFT JOIN Volume v    ON b.BookID = v.BookID
   LEFT JOIN Chapter c   ON v.VolumeID = c.VolumeID

where BookSeries and Volume will yield NULL columns, except for primary and foreign keys.
So, always store starting with BookSeries and end at the desired level. So If you want to store a book with no volume and no chapter, this is fine. The query will yield NULL columns for volumes and chapters (inlcuding for primary and foreign keys). This is ensured by the LEFT JOIN.
Journals have a different structure. Here two approaches are possible:

Store journals as independent hierarchy in other tables.
(partially) unify the two hierarchies and use a Type or Kind field as a discriminator to differentiate between books and journals
BookSeries > BookOrJournal > Volume +--> Issue > Article
                                    |
                                    \--> Chapter


Yes, it's possible if you change the transaction isolation level for the session (that's what you call "window" in SSMS) that queries modified data. Often this is not such a great idea, as you might get some unexpected results. Consider the side effects carefully. I have no idea if it's possible to change the transacion isolation level in the Excel Power Query.

For example, the following set of queries would insert some data and display the update correctly even without commit/rollback.

-- Session 1
begin tran tx_test;
-- Assume the Test table exists and insert is okay
insert dbo.Test(datadate, content) values (getdate(), 'transaction');
select * from Test; -- Shows the new data
-- After select, one would execute one of the following
-- commit;
-- rollback;

Meanwhile, the second session executes a select that doesn't seem to do anything:

-- Session 2
-- This waits for uncommitted transaction
-- and returns results after 1st session commits/rollbacks
select * from Test;

Create a third session and alter its isolation level:

-- Session 3
set transaction isolation level read uncommitted;
-- This reads the inserted data from the 1st session, even before commit
select * from Test;


Handle concurrency on Sql Server to modify specific column

We had a similar requirement and so added an extra where clause to the query. E.g.

UPDATE AppointmentTable
SET STatus = [Approved/Rejected]
WHERE AppointmentID = @ID AND Status = Pending

Then afterwards, check how many rows were updated. If there were 1 rows updated then report back to the user that their action was recorded. If 0 rows were updated then report back to the user that someone else has already done the work.

  Isn't there a chance the queries will be executed "at the same time"

Nope. If they execute "at the same time" one of them will still get the update lock first. The other will have to wait for the update lock. When the 2nd gets the lock it still has to check the where clause to validate it's still true. If it's no longer true then it skips that row and no update happens (@@rowcount = 0). The key is to have the old value in the where clause.


They do the same thing, but there is a difference and a grave danger.

Here is the difference:

OPTIMIZE TABLE is done in the mysql client or in a mysql session.
myisamchk is a utility, not a client. Thus, there are no table locking safeguards. Therefore, you should never run myisamchk -r -a -S in a live system. Otherwise, you can quickly (in fact, instantaneously) corrupt a MyISAM table.

You could move the MyISAM table to another folder and run myisamchk against the table files without interference from mysqld or any client connections thereof.

Other difference

mysqld will create temp tables
myisamchk can figure out if it needs to make a temp table or do things in place.

More info

Doing OPTIMIZE TABLE will does a complete copy of the table. It does this under the hood

ALTER TABLE mytable ENGINE=MyISAM;
ANALYZE TABLE mytable;

Doing OPTIMIZE TABLE could make the temp table in /tmp and not in place like myisamchk

Please remember that tmpdir is mapped somewhere. That's where space may give out.


SQL Server transactional replication distributor

The Distributor is the server that contains the distribution database.

Configure Distribution at https://msdn.microsoft.com/en-us/library/ms151860.aspx says:

  By default, the Distributor is the same server as the Publisher (a
  local Distributor), but it can also be a separate server from the
  Publisher (a remote Distributor).

SQL Server can support a single Distributor on the local server.  If you are trying to reduce the load on the main server you can instead create the Distributor on a remote server.

If the load is heavy, an additional step that would help manage the distribution is to create a distribution database for each database that is being published.  This could reduce the contention within each database that might be present in a single distribution database.

Both a local server and a remote server can support multiple distribution databases.

David Poole's article from 2010, Scaling Out the Distribution Database. may provide you more insight.  It can be found here: http://www.sqlservercentral.com/articles/Replication/69663/

Both articles are helpful and I have not detailed every step in the setup.


SQL Server compatibility with New TLS Standards

Microsoft has recently revealed (without a lot of fanfare) that they will be investing in TLS 1.2 and phasing out SSL. It should be relevant to all editions of SQL Server.

UPDATE 2016-01-29 : Microsoft has announced official support for TLS 1.2 in 2008, 2008 R2, 2012, & 2014. Downloads and other info can be found in KB #3135244.

I blogged about a few of the issues that have been mentioned, as well as a warning if you are using encrypted endpoints in 2014:

SQL Server support for TLS 1.2 – Read This First!

The post also points to the correct build to download (or other action) depending on @@version.

Whether this move will affect all existing versions, just 2014 and above, or just 2016, remains to be seen. The quote below seems to imply at least 2014 will be part of the work - and I suspect much of the investment will be in the client libraries, not in the engine, so it is feasible that it will work for any version that the next release of the ODBC/Native Client drivers will support.

I got this from a PowerPoint deck by Kevin Farlee of Microsoft, and was given permission to share the information, though I don't know how much of it has been redistributed at this point. Here is the exact quote from the deck:

  Encryption in flight: Protects data between client and server against snooping & man-in-the-middle attacks. Upgrading to TLS 1.2 in CY 15, phasing out SSL.

Also if you look at KB #3052404, it seems there are patches to make it work with 2012 SP+ and 2014 (patches won't be required for 2016), but no indication there will be any back-porting to SQL Server 2005, 2008, or 2008 R2 (and frankly, I'd be quite surprised).


Join on nested obj attribute in JSONB column along and a varchar column

The keys for the -> or ->> operators need to be supplied as strings:
SELECT *
FROM events ev
  JOIN connections con
    ON con.user_id = ev.user_id
   AND con.other_user_id = ev.payload -> 'otherUser' ->> 'id'

Or use the #>> operator and pass an array:
SELECT *
FROM events ev
  JOIN connections con
    ON con.user_id = ev.user_id
   AND con.other_user_id = ev.payload #>> '{otherUser,id}';

Which is basically the same as using jsonb_extract_path_text
SELECT *
FROM events ev
  JOIN connections con
    ON con.user_id = ev.user_id
   AND con.other_user_id = jsonb_extract_path_text(ev.payload, 'otherUser','id');

Online example


Predicate pushdown on view to optimize query?

I would suggest:

change the unrecommended shorthand getdate()-7 to the proper and explicit CONVERT(datetime2(2), DATEADD(DAY, -7, GETDATE()))
avoiding SELECT * everywhere, especially in the view
change your predicate to an open-ended range (>= July 1 and < August 1)
explicitly convert literals to avoid implicit conversions
avoid regional, unsafe formats like yyyy-mm-dd

So:

WHERE SpendDate >= CONVERT(datetime2(2), '20180601')
  AND SpendDate <  CONVERT(datetime2(2), '20180701');

Not only does this guard against both implicit conversions and ydm interpretation, it is much easier to find the beginning of the next month than the end of the current one.


Why can my index seek estimate the right number of rows and the sort operator can't?

I believe your estimates are wrong because of an estimator bug that swaps two of the DATEDIFF arguments. I talk about this here:

Performance Surprises and Assumptions : DATEDIFF

A workaround is to calculate the first day of 13 months ago without using DATEDIFF (2008+):

DATEADD(MONTH, -13, DATEADD(DAY, 1-DATEPART(DAY,GETDATE()), CONVERT(DATE, GETDATE()));

I'm not positive that will address the estimate (I haven't tested with filtered indexes, and I'm not sure what the sort is actually doing or why it has a different estimate without the plan and/or the rest of the query).

The fix Microsoft recommends is to use TF 4199, but I'm not so sure that's what you'll need to do here:

Connect #630583 : Incorrect estimate with condition that includes > datediff

Another option would be to make sure you're on the absolute latest SP/CU for whatever version of SQL Server you're using, as they claim it is fixed in the following KB article (though this will still require the use of TF 4199 unless you are on 2014 or better):

KB #2481274 : FIX: A query that uses the DATEDIFF function may run slowly in SQL Server 2005 or in SQL Server 2008 or in SQL Server 2008 R2

The fix can be obtained with the following builds:

2005 SP3 CU 15 (>= 9.00.4325 AND <= 9.00.4999)
2005 SP4 CU 2 (>= 9.00.5259)
2008 SP1 CU 13 (>= 10.00.2816.00 AND <= 10.00.3999)
2008 SP2 CU 3 (>= 10.00.4279.00 AND <= 10.00.5499)
By extension 2008 SP3 & SP4 (>= 10.00.5500)
2008 R2 CU 7 (10.50.1777.0)
2008 R2 SP1 CU 3 (>= 10.50.2769.0 AND <= 10.50.3999)
By extension 2008 R2 SP2 & SP3 (>= 10.50.4000)
By extension 2012, 2014, 2016 (>= 11.0)

(Next time, please include the results of SELECT @@VERSION in your question.)

I will note that the KB article says that DATEDIFF can underestimate the number of rows, which is the opposite of what is happening in your scenario. That doesn't mean the fixes don't apply to you; I think the KB article wording is inaccurate, as the estimates can go either way depending on the data and the range you're looking at.

My blog post above confirmed that the swapping no longer occurs in 2014 and up. To be safe, I would probably just scrap DATEDIFF from your predicate and use a different method to calculate the beginning of your range. I don't suggest the overkill of 4199 or using dynamic SQL to prevent the bad swap.


Database Dump/Backup but only to specific records?

You would dump each table using the --where option. I wrote a rather gnarly post about 4 years ago : Is it possible to mysqldump a subset of a database required to reproduce a query?

In your case, you can do the following: Suppose the database is called schooldb

Dump by SchoolID

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
OPTS="--lock-all-tables"
SCHOOLID=1
WHERE="tagID in (select tagID from Ownership where studentID in"
WHERE="${WHERE} (select studentID from Students where schoolID = ${SCHOOLID}))"
mysqldump ${MYSQL_CONN} ${OPTS} --where="${WHERE}" schooldb tags > tags.sql

Dump by School Name

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
OPTS="--lock-all-tables"
SCHOOL_NAME=schoolA
WHERE="tagID in (select tagID from Ownership where studentID in"
WHERE="${WHERE} (select studentID from Students where schoolID in"
WHERE="${WHERE} (select schoolID from Schools where name='${SCHOOL_NAME}')))"
mysqldump ${MYSQL_CONN} ${OPTS} --where="${WHERE}" schooldb tags > tags.sql

GIVE IT A TRY !!!

If you are using mysql client only, you could dumping SELECT ... INTO

SELECT B.studentID,B.name,D.tagID,D.data
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Schools A
INNER JOIN Students  B ON A.SchoolID  = B.SchoolID
INNER JOIN Ownership C ON B.studentID = C.studentID
INNER JOIN Tags      D ON C.tagID     = D.tagID
WHERE A.name = 'schoolA';

or

SELECT B.studentID,B.name,D.tagID,D.data
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Schools A
INNER JOIN Students  B ON A.schoolID  = B.schoolID
INNER JOIN Ownership C ON B.studentID = C.studentID
INNER JOIN Tags      D ON C.tagID     = D.tagID
WHERE A.schoolID = 1;


Even if you meant Sql Server and just mis-spelled db_owner, the answer holds true for any database: use the principle of least priviledge. That is, if an user shouldn't be able to drop the database, do not assign such privileges in the first hand at all.

Messing with built-in security group and role settings is bad practice, unless you are very sure what you are up to. Create new roles with appropriate settings instead. This way, you or any other DBA doesn't have to double-check if default permissions are really on default settings.

That being said, you might be able to prevent drops with some creative triggers. As how to do that, depends on your RDBMS.


Oracle: Read from CLOB column line by line and insert to the table

This is not a full answer because it only works if your clob is less than 4000 chars. The clob version (with plain bar instead of to_char(bar) in the query) fails with ORA-00932: inconsistent datatypes: expected - got CLOB - perhaps someone else can explain why exactly?

testbed:

drop table foo;
create table foo(id integer, bar clob);
insert into foo(id, bar) values (1, 'Hello'||chr(10)||'there');
insert into foo(id, bar) values (2, 'Hello'||chr(10)||'there'||chr(10)||'again');

query:

with w(id, line#, line, rest) as (
  select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'),
         substr(to_char(bar), regexp_instr(bar, '$', 1, 1, 1, 'm')+1)
  from foo
  union all
  select id, line#+1, regexp_substr(rest, '^.*?$', 1, 1, 'm'),
         substr(rest, regexp_instr(rest, '$', 1, 1, 1, 'm')+1)
  from w
  where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, line#, line from w order by id, line#;
/*
ID                     LINE#                  LINE
---------------------- ---------------------- ----------------------------------------
1                      1                      Hello
1                      2                      there
2                      1                      Hello
2                      2                      there
2                      3                      again
*/


Amazon rds MySql -> concurrent connections deadlocks

The short answer is no: I can't think of a difference from MySQL defaults that would cause this.

The longer answer is that it is possible to reduce some locking with READ-COMMITTED as the isolation level + Row-based Replication. To which RDS does not support switching to Row-based Replication :(

Whether or not this reduction in locking will help you depends on your schema.  See my blog post here under "Write scalability of certain statements".


How to limit inserts to one row per user per month?

MySQL does not allow functions in keys, only column references. So if you want to restrict by a UNIQUE key, you need another column that would contain only the year and month for the event. The easiest and most straightforward approach is to reduce the date value to something like YYYY-MM-01. If you are using MySQL 5.7 or newer, you can declare that column as generated and define an expression converting the day part to the constant 01 value.

+---------+-----------+------------+------------+
| user    | product   | date       | rdate      |
+---------+-----------+------------+------------+
| 1       | 1         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-08-28 | 2018-08-01 |
+---------+-----------+------------+------------+

And now you can create the UNIQUE INDEX permonth (user, product, rdate). This is the uniqueness established on the physical level.

On the logical level uniqueness can be achieved by (at least) two common ways.

First, you can define a BEFORE INSERT trigger that would perform extended comparison of the dates and decline to insert a duplicate. But triggers are not a very clean and easy tool because of their implicitness, and my advise is to avoid triggers if possible.

The more robust way is to wrap the insert statement into a stored routine and perform call SafeVoteInsert( userID, productID, date ) instead of plain INSERT vote VALUES ....


How do I stop refresh conflicts between Proactive Caching of facts and timed refresh of Dimensions in a SQL Server Analysis Service 2005 OLAP cube?

Sounds like you're getting orphaned fact rows a.k.a. early-arriving facts, meaning you have facts records that don't yet have a corresponding dimension member in one of the dimensions.

There's not really a way to solve this with the approach you're using because it's sounds like you'll always been a in  situation where your dimensions are lagging your facts. But you can work around it by changing your dimension key error options in your fact processing. There is an option to ignore dimension keys not found. What you want to do is set that to "Ignore" and you might have to play around with some of the error limit options as well.

This should do the trick. Not the prettiest of solutions, but it will work and prevent these outages.


How to skip table data for replication in MySQL?

Create a database.  Add binlog_ignore_database on the master to avoid even sending the data out (as opposed to sending it, then tossing on the slave, as with replicate_ignore_*)

Put all your not-to-be-replicated tables in that database.  Reference them via dbname.tblname.

Be aware of how replication filters:  In SBR, USE controls what to check for do/ignore filters.

There's a trick I did quite successfully:

Setup: RBR, extra db, binlog_ignore_db (as above)
Create tables in that db and load data into them
Processed those tables (only on the Master) -- normalization, cleansing, updating Summary Tables, etc
INSERT stuff from that db into replicated tables (in other dbs). -- This is the only step that was replicated.
And drop the tables in the ignored db.

The net effect was to do a lot of ETL stuff only on the Master, yet still get the end results onto the Slave.


How to know SQL Server installation language while it is down?

You can check the system registry to determine the installed language for SQL Server.  To get the name of the SQL Server Instance Key, first look at this registry key:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

That key shows a list of installed instances; the Data column shows the registry key name to use when looking for the Language key in this key:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<name-from-previous-step>\MSSQLServer\CurrentVersion\Language

This Microsoft site lists the various language IDs and the corresponding language name.

I wrote a quick blog post showing this process in greater detail at SQLServerScience.com.


What is the purpose of the Save Transaction statement?

The use of Save Transaction provides you with a mechanism for rolling back portions of a transaction. For example SP A starts a transaction which then calls SP B. At the start of SP B, a Save Transaction Start Processing could be created.

If an error then occurs in SP B you could just rollback the change in the SP B allowing the changes in SP A to be committed.


How to store multiple foreign key values in a single field?

The best way to do this is with a one-to-many relationship message_recipients table.

user_info
(
  user_id INTEGER PRIMARY KEY,
  email VARCHAR(256),
  password VARCHAR(256)
);

messages
(
  message_id INTEGER PRIMARY KEY
  time_stamp DATETIME,
  message VARCHAR(4000),
  sender INTEGER -- references user_info.user_id
);

message_recipients
(
  message_id INTEGER, -- references messages.message_id
  recipient_id INTEGER -- references user_info.user_id
);


mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

Reason for indexes not being used is table column is an input part of the function.
Current SQL Query

select *
 from   users
 where  DATEDIFF(users.created_at,'2019-05-05 23:11:56.547') =  0;

Indexes will be used if the query is in below way

select *
 from   users
 where  users.created_at =  DATE_ADD('2019-05-05 23:11:56.547', INTERVAL 0 DAY);

since above refers to a point of time. To check day we can have to use range timestamp start of today to start off tomorrow,

select *
 from   users
 where  users.created_at between DATE_ADD('2019-05-05 ', INTERVAL 0 DAY)
and DATE_ADD('2019-05-05 ', INTERVAL 1 DAY);


SQL Union with results in Order of Union

Based on your sqlfiddle link, here's a brute force way to get what you're looking for. I inject an ordering value for each result set being unioned, group on matching values to get the minimum order, and then order by the minimum order for each grouped value.

SELECT userID, FirstName, LastName, UserName
FROM (
  SELECT userID, FirstName, LastName, UserName, MIN(UnionSet) UnionSetOrder
  FROM (
    SELECT u.userID, u.FirstName, u.LastName, u.Username, 1 as UnionSet
    FROM follows f INNER JOIN Users u ON f.Following = u.UserID
    WHERE follower=2
    UNION ALL
    SELECT u.UserId, u.FirstName, u.LastName, U.Username, 2 as UnionSet
    FROM users u
    WHERE u.username LIKE '%a%' AND u.UserID <> 2
  ) x
GROUP BY userID, FirstName, LastName, UserName
) y
ORDER BY UnionSetOrder, UserName

Note that this doesn't exactly match the results you're showing in your question, as they differ from what's in the code sample.


I guess you want the results in ascending order but with 9999 always first?

Select EMP
From Emp
Order By Case When Company = 9999 Then -1 else Company End,
         Case When Office = 9999 Then -1 else Office End;


how to create backup for update tables structures in existing DB?

Another tool, apart from those already mentioned is ApexSQL Diff

As Mitch has already mentioned, writing update scripts that will synchronize two databases manually is quite a risky job. You can maybe try to compare by creating scripts for all objects and then use a file compare tool to find the deltas between the two schemas and change it. But for even a bit larger databases I would not recommend this method since it’s quite easy to miss some change

For some object types you can use query like this that will give you objects that are not equal but note that this doesn't cover everything.

select O.name, sm1.definition
from DB1.sys.sql_modules AS sm1
inner join DB2.sys.sql_modules as sm2 on sm1.object_id = sm2.object_id
inner join DB1.sys.all_objects O on sm1.object_id = O.object_id
where sm2.definition <> sm1.definition

Disclaimer: I'm working for ApexSQL as a Technical Support Engineer


When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

If the index is covering it's likely it will be used in conjuction with a sort operation in the query plan to reverse the order.

Edit: Following a little more thought!

It will depend on whether this is a trivial query or involves joins. If trivial:

SELECT x,y FROM MyTable ORDER BY y DESC

and the index order is Y ASC, a reverse scan of the index leaf level should avoid a sort.

If non-trivial:

SELECT x,y FROM MyTable mt INNER JOIN MyOtherTable mot ON mot.y = mt.y

it should depend on the sort order of MyOtherTable.y. If it's ASC as per MyTable.y then the two indexes would be read in index order and a sort applied after the join. If it's desc, in theory a reverse order index scan could be used for the join and an additional sort wouldn't be required to satisfy your order by clause.

Edit2: Couldn't recall if this would show up in the execution plan in SQL Server. The icon doesn't indicate this is a reverse scan, nor does the tooltip on hover. Properties however shows 'Scan Direction - Backward' or checking the plan XML reveals

<IndexScan Ordered="true" --->ScanDirection="BACKWARD"<--- ForcedIndex="false" NoExpandHint="false">
            <DefinedValues>
              <DefinedValue>
                <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[MyTable]" Column="OtherId" />
              </DefinedValue>
            </DefinedValues>
            <Object Database="[TestDb]" Schema="[dbo]" Table="[MyTable]" Index="[IX_MyTable_OtherId]" />
          </IndexScan>


After Restore Log_reuse_wait_desc of Replication

Can you try this following script on your database?

use master
exec sp_replicationdboption @dbname = N'your db name', @optname = N'<<name of your
publication>>', @value = N'false'
GO

select name, is_published, is_subscribed, is_merge_published, is_distributor
from sys.databases where name =<<your database name>>

This might be due to orphaned replication metadata. You can use the below query to remove:

USE master
EXEC sp_removedbreplication
    @dbname = 'myDB'


I suggest you add an index on all the columns used in the query, with this order (actually the order matters for the first 2 columns only, the rest 5 can be in any order within the index):

 (tag_text,
  tag_level,
  knowledge_id, tag_order, tag_weight,
  total_title_direct_words,
  total_title_parenthesis_words)

The query should then use this index for both the WHERE and the DISTINCT (or group by) part. You probably need to change the query slightly, removing the DISTINCT and using GROUP BY (this acts like a hint to the MySQL optimizer.)

SELECT   knowledge_id,                      -- remove DISTINCT
         tag_order,
         tag_weight,
         total_title_direct_words,
         total_title_parenthesis_words,
         tag_level
FROM   knowledge_tags
WHERE  ( tag_text = 'washington' )
  AND  ( tag_level < 10 )
GROUP BY   tag_text,                        -- use the same order
           tag_level,                       -- as in
           knowledge_id,                    -- the index,
           tag_order,                       -- all
           tag_weight,                      -- the
           total_title_direct_words,        -- way
           total_title_parenthesis_words ;  -- down

The EXPLAIN should then show:

  Using where; Using index for group-by

If the result are good enough for your expectations, you can then drop the tags_by_word_text index.


Backup the whole SQL Server databases in one .bak file - SQL Server 2005

There is no built-in way to backup all the databases into a single file, and even if there was, that file would probably become impractically big.

I would go with the ideas you got from your original question, and implement those in an automated script that loops through all your databases; One script to back every database up, one script to restore those backups on the new server. Here's an idea on how to build such a script:

SELECT 'BACKUP DATABASE '+QUOTENAME([name])+' TO  DISK = N''X:\yz\.....'''
FROM master.sys.databases
WHERE [name] NOT IN ('master', 'tempdb', 'model', 'msdb');


Assuming you are using COPY to populate the table I don't see a reason to create the table without the bigserial column.

Just create the table including the bigserial, but don't specify it in the COPY command. It will then automatically be filled with values during the import.

create table foobar (id bigserial, col1 text, col2 text);
copy foobar (col1, col2)
   from 'foobar.txt'
   with (format csv, header false, delimiter ',');

That would remove steps 3,4 and 5.

I'm not sure what you mean with "range of bigserial" in step 6.


What's difference between MySql IN and ANY operator?

There is 2 differences between them.

IN is an operator whereis ANY is not, it is a keyword and may be treated as a comparison operator modifier (the same for ALL and SOME).

See Operators and Operator Precedence. IN is listed in both whereas ANY, ALL and SOME are not.

IN may process an expression list whereas ANY cannot.

For example, the code

WHERE t1.id IN ((SELECT id FROM t2), (SELECT id FROM t3))

cannot be converted into = ANY form without subqueries code rewrite.

In all other respects they are complete synonyms.


Large update and delete based on another table

A delete with an IN condition might be faster than the JOIN:

delete from slave
where mid in (select master.id
              from master
              where master.value in ('some', 'set', 'of', 'values'));

delete from master
    where value in ('the', 'same', 'set', 'of', 'values');

Another option that might be faster is to delete both tables in a single statement. This can also be used to delete multiple slaves.

with removed_master (id) as (
  delete from master
  where value in ('some', 'set', 'of', 'values')
  returning id
), remove_slave1 as (
  delete from slave_one
  where mid in (select id from removed_master)
)
delete from slave_two
where mid in (select id from removed_master);

This works as the foreign key constraints are evaluated when the statement is finished, not when each individual row is deleted. This has the additional benefit that you have to specify the "master values" only once.


How to connect to specific session id & serial# ? Blocked sessions

That will not happen. You can kill a session, or cancel the SQL it is currently executing from another session, but you can not end its transaction (with leaving the session alive). Usually such blocking sessions are just simply killed.

For your other question: blocking another transaction in another session that inserts to another table may be blocked through foreign keys. Example:

create table request(c1 number(10, 0) not null primary key);
create table response (c1 number(10, 0) not null,
                       request_c1 number(10, 0) not null references request(c1));

Session 1:

SQL> insert into request values(1);

1 row created.

No commit yet, session 1 is waiting. In session 2:

SQL> insert into response values(1, 1);

And session 2 hangs there until session 1 performs a commit or rollback.


What would cause extra records to be inserted during a INSERT trigger

First, you don't need the IF EXISTS part in your trigger code. You have the exact same code in the second part so if there are 0 rows produced, you'll be inserting 0 rows anyway:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)
    SELECT a.ID, a.Etc, 1 FROM Inserted a
        LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
        WHERE b.Flag IS NULL AND a.Flag = 0
END ; -- trigger

which can also be written as:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)
    SELECT a.ID, a.Etc, 1
        FROM Inserted a
        WHERE a.Flag = 0
          AND NOT EXISTS
              ( SELECT *
                FROM Foo b
                WHERE b.Flag = 1 AND a.ID = b.ID
              )
END ; -- trigger

So, you want any insert on the table with Flag=0 to also trigger another insert, with same data but Flag=1 (unless there is already a row in Foo with that ID and Flag=1.

Second, and what the actual problem is about, Inserted is a special table with all the (just) "inserted" rows, not the full table you inserted into. It obviously has less (than or equal) rows as Foo after the insert. So a join to it will not give same results as a join to Foo.

The trigger that produces the multiple unwanted rows had Inserted b instead of Foo b so it does this:

For any insert on the table with Flag=0 it also triggered another insert, with same data but Flag=1 (unless this same insert had such a row inserted).

You can read more about the special Inserted and Deleted tables at the MSDN:
How to use the inserted and deleted Tables


Per the documentation, there's a custom stored procedure that does just that:

EXEC rdsadmin.dbo.rds_read_error_log [index]


Index utilization issues in SQL Server queries

Your query is looking for the minimum maturity_date from rows where the as_of_date is greater than a given value.

The optimizer has to choose between two main execution strategies:

Scan an index ordered by maturity_date ASC until it finds the first row where as_of_date >= @as_of_date_from.

As soon as the query processor finds this first row, the scan of the index stops. This is a point often missed when reading execution plans: an index scan does not necessarily always scan the whole index.

If the first qualifying row can be found quickly, a partial scan of the ordered index is a great strategy for your query. On the other hand, this is a poor strategy if the first row matching the as_of_date predicate occurs toward the end of the index (in maturity_date order).

The optimizer therefore makes an assessment of how many rows from the index it will have to scan before it finds one that matches the as_of_date predicate. This assessment is based on an assumption of uniform distribution (often incorrect in practice).

The plan you provided shows that the Index Scan had to read 1,101,479 rows (in maturity date order) before it found the first one matching the as_of_date predicate.
Seek an index (with as_of_date as the leading key) to find all rows where as_of_date >= @as_of_date_from, then compute the minimum maturity_date found in those rows.

This is a good strategy if the as_of_date predicate matches relatively few rows. It is not so good if many rows are expected to qualify. The expected cost of this strategy depends on the value of @as_of_date_from, and statistical information about the as_of_date column.

Your query uses a local variable, so the optimizer cannot 'see' the value contained in it, and makes a guess at how selective the unknown value will be.

For an inequality predicate, the default guess is that 30% of the rows will qualify. For a table of 130 million rows, the optimizer will therefore guess that 30% * 130,000,000 = 39,000,000 rows will match. This is a lot of rows to compute the minimum maturity_date on, the the optimizer prefers the other strategy.

You can allow the optimizer to 'see' the value inside the variable at execution time by adding an OPTION (RECOMPILE) hint to the query. This enables the Parameter Embedding Optimization, at the cost of having to recompile the query plan every time the query is executed.

If the @as_of_date_from variable value varies a lot between executions, using the OPTION (RECOMPILE) hint may be worthwhile for you. The optimizer may still choose the partial ordered scan strategy, depending on its assessment of the relative costs of the two strategies.

In any case, you should change the data type of the @as_of_date_from to datetime to match the as_of_date column to avoid an implicit conversion.

The data type of @tenor_from should probably be datetime not varchar(20). Or change the query to CONVERT(varchar(20),MIN(spc.maturity_date),103). The current CONVERT makes no sense.


You're on the right track with splitting up your domains and using a linking entity.  Separating the Films from the Stars is absolutely the first step.

 ---------------------------    -------------------------
| FilmID |     FilmName     |  | StarID |   StarName     |
|--------+------------------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | 000001 | Bruce Campbell |
| EVIL02 | Evil Dead II     |  | 000002 | Sarah Berry    |
| EVIL03 | Army of Darkness |  | 000003 | Marcus Gilbert |
 ---------------------------    -------------------------

The missing piece is tying the two together.  Obviously, stuffing the StarIDs into the Film table is in violation of the first normal form, since you end up needing to store multiple references to actors in a single row.

 --------------------------------------------
| FilmID |     FilmName     |     StarIDs    |
|--------+------------------+----------------|
| EVIL01 | The Evil Dead    | 000001         |
| EVIL02 | Evil Dead II     | 000001, 000002 |   Wrong,
| EVIL03 | Army of Darkness | 000001, 000003 |   Wrong
 --------------------------------------------

Splitting the offending column out to its own table restores the integrity of the original Film table, but continuing to store multiple references in a single attribute still invalidates 1NF for the data model.

 ---------------------------    -------------------------
| FilmID |     FilmName     |  | FilmID |     StarIDs    |
|--------+------------------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | EVIL01 | 000001         |
| EVIL02 | Evil Dead II     |  | EVIL02 | 000001, 000002 |   Still
| EVIL03 | Army of Darkness |  | EVIL03 | 000001, 000003 |   Wrong
 ---------------------------    -------------------------

To rectify this is fairly simple, as you merely need to associate each FilmID with the respective StarID, bringing your model in-line with the tenants of 1NF.

 ---------------------------    -----------------    -------------------------
| FilmID |     FilmName     |  | FilmID | StarID |  | StarID |   StarName     |
|--------+------------------|  |--------+--------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | EVIL01 | 000001 |  | 000001 | Bruce Campbell |
| EVIL02 | Evil Dead II     |  | EVIL02 | 000001 |  | 000002 | Sarah Berry    |
| EVIL03 | Army of Darkness |  | EVIL02 | 000002 |  | 000003 | Marcus Gilbert |
 ---------------------------   | EVIL03 | 000001 |   -------------------------
                               | EVIL03 | 000003 |
                                -----------------

Now, with the the data normalized in this manner, it is easy to add attributes to expand the model.  Say an actor's character name in a movie was desirable, you could add CharacterName to the FilmStar table - maybe useful for getting a list of everybody who's ever played "James Bond," for instance.


IS-A relationship with only single sub entity

I would not create another table for verbs because join operations on big tables (especially multiple ones in the same query) may be expensive. I would create one table, with the shared AND the non-shared fields, AND add another column "isverb", set it to 1 for verbs and 0 for non-verbs. You could also later on add "isadjective" etc.

In this case it just makes sense to do:

SELECT * FROM words where isverb = 1

rather than:

SELECT *
FROM words w
INNER JOIN verbs v ON w.type = v.id
WHERE w.type = 2

The application is a dictionary. So it's write once, read a zillion times and so I don't personally see a need for a complicated database design. You essentially just need very fast reads which you can achieve without joins to multiple tables


MongoDB Export/Import Failed: error counting

@Raz, I have gone through your mongodump statement. You are doing mistake.

  mongodump --db ride4you --collection rides

As per your mongodump code , As i am able to understand in your mongodump code, there is database name (ride4you) and collection name (rides) without the path of dump (.JSON) file.

Let's start from beginning. As per MongoDB BOL , if you are in BIN directory . Just type along with mongodump --help. You shall get the mongodump command all details. Which i am copy and paste from my environment.

C:\Program Files\MongoDB\Server\3.2\bin>mongodump --help
Usage:
  mongodump <options>

Export the content of a running server into .bson files.

Specify a database with -d and a collection with -c to only dump that database or

See http://docs.mongodb.org/manual/reference/program/mongodump/ for more informat

general options:
      /help                                                 print usage
      /version                                              print the tool
                                                            version and exit

verbosity options:
  /v, /verbose:<level>                                      more detailed log
                                                            output (include
                                                            multiple times for
                                                            more verbosity,
                                                            e.g. -vvvvv, or
                                                            specify a numeric
                                                            value, e.g.
                                                            --verbose=N)
      /quiet                                                hide all log output

connection options:
  /h, /host:<hostname>                                      mongodb host to
                                                            connect to
                                                            (setname/host1,host-

                                                            2 for replica sets)
      /port:<port>                                          server port (can
                                                            also use --host
                                                            hostname:port)

authentication options:
  /u, /username:<username>                                  username for
                                                            authentication
  /p, /password:<password>                                  password for
                                                            authentication
      /authenticationDatabase:<database-name>               database that holds
                                                            the user's
                                                            credentials
      /authenticationMechanism:<mechanism>                  authentication
                                                            mechanism to use

namespace options:
  /d, /db:<database-name>                                   database to use
  /c, /collection:<collection-name>                         collection to use

query options:
  /q, /query:                                               query filter, as a
                                                            JSON string, e.g.,
                                                            '{x:{$gt:1}}'
      /queryFile:                                           path to a file
                                                            containing a query
                                                            filter (JSON)
      /readPreference:<string>|<json>                       specify either a
                                                            preference name or
                                                            a preference json
                                                            object
      /forceTableScan                                       force a table scan

output options:
  /o, /out:<directory-path>                                 output directory,
                                                            or '-' for stdout
                                                            (defaults to 'dump')
      /gzip                                                 compress archive
                                                            our collection
                                                            output with Gzip
      /repair                                               try to recover
                                                            documents from
                                                            damaged data files
                                                            (not supported by
                                                            all storage engines)
      /oplog                                                use oplog for
                                                            taking a
                                                            point-in-time
                                                            snapshot
      /archive:<file-path>                                  dump as an archive
                                                            to the specified
                                                            path. If flag is
                                                            specified without a
                                                            value, archive is
                                                            written to stdout
      /dumpDbUsersAndRoles                                  dump user and role
                                                            definitions for the
                                                            specified database
      /excludeCollection:<collection-name>                  collection to
                                                            exclude from the
                                                            dump (may be
                                                            specified multiple
                                                            times to exclude
                                                            additional
                                                            collections)
      /excludeCollectionsWithPrefix:<collection-prefix>     exclude all
                                                            collections from
                                                            the dump that have
                                                            the given prefix
                                                            (may be specified
                                                            multiple times to
                                                            exclude additional
                                                            prefixes)
  /j, /numParallelCollections:                              number of
                                                            collections to dump
                                                            in parallel (4 by
                                                            default)

Mostly i am doing mongodump in my environment like

For a single database dump

mongodump -d myfirstdb -o C:\Test\TestDB.json

For all database dump at a time

mongodump --verbose C:\Test\

Here In first statement myfirstdb is database in my MongoDB Server. -o is for output directory options and C:\Test  is a folder where TestDB.json my dump (.JSON) File reside.

In second statement i have used --verbose . for more details output

Note: mongodump import all dump (.JSON) files in MongoDB Server at a time. But when doing mongoimport then it import one dump (.JSON) file at a time.

For Further your ref mongodump


MySQL, "columns partitioning" when multiple columns are individiually important

You are right that mysql will check only one partition for a specific sender_id, but checks all the partitions for a specific receiver_id, as shown here:

mysql> explain partitions select * from messages where sender_id =5;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | messages | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from messages where receiver_id =5;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | messages | p0,p1,p3   | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

However, there are still benefits to that partitioning, depending on your hardware.  When looking in all partitions for a receiver_id, mysql is really performing 3 select statements, one for each partition.  It may be able to parallelize these select statements.  Additionally, if you index receiver_id, it will be accessing 3, smaller, indicies.

In the end, you just have to do performance testing and see if it is paying off for your use case.  Seeing as 100MB fits in RAM pretty easily these days, I wouldn't consider partitioning such a small table unless you have specific reasons to do so.


If the list of payment types and currencies are known, you can do this with CTEs.

with paymenttypes as (
  select 0 as ptype,
  Union select 1 as ptype,
  Union select 2 as ptype ),
currencies as
( select 'Aaa' as currency
  union
  select 'Bbb' as currency ),
sourcedata as (
  select currency, ptype as paymenttype, 0 as amount
  from currencies, paymenttypes
  Union all
  select *
  from data)
select currency, paymenttype, sum(amount)
from sourcedata
group by currency, paymenttype;

It basically creates a Cartesian product of all currency, paymenttype pairs with a 0 amount, then joins it with the source data.

If your lists of payment types and currencies are all in lookup tables, then substitute them in.

Also note there are other ways, using COALESCE etc.

SQL Fiddle link,


How does SQL Server know predicates are correlated?

Consider the simple AdventureWorks query and execution plan shown below. The query contains predicates connected with AND. The optimizer's cardinality estimate is 41,211 rows:
-- Estimate 41,211 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

Using default statistics
Given only single-column statistics the optimizer produces this estimate by estimating the cardinality for each predicate separately, and multiplying the resulting selectivities together. This heuristic assumes that the predicates are completely independent.
Splitting the query into two parts makes the calculation easier to see:
-- Estimate 68,336.4 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionID BETWEEN 100000 AND 168336;

The Transaction History table contains 113,443 rows in total, so the 68,336.4 estimate represents a selectivity of 68336.4 / 113443 = 0.60238533 for this predicate. This estimate is obtained using the histogram information for the TransactionID column, and the constant values specified in the query.
-- Estimate 68,413 rows
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

This predicate has an estimated selectivity of 68413.0 / 113443 = 0.60306056. Again, it is calculated from the predicate's constant values and the histogram of the TransactionDate statistics object.
Assuming the predicates are completely independent, we can estimate the selectivity of the two predicates together by multiplying them together. The final cardinality estimate is obtained by multiplying the resulting selectivity by the 113,443 rows in the base table:

0.60238533 * 0.60306056 * 113443 = 41210.987

After rounding, this is the 41,211 estimate seen in the original query (the optimizer also uses floating point math internally).
Not a great estimate
The TransactionID and TransactionDate columns have a close correlation in the AdventureWorks data set (as monotonically increasing keys and date columns often do). This correlation means that the independence assumption is violated. As a consequence, the post-execution query plan shows 68,095 rows rather than the estimated 41,211:

Trace flag 4137
Enabling this trace flag changes the heuristics used to combine predicates. Instead of assuming complete independence, the optimizer considers that the selectivities of the two predicates are close enough that they are likely to be correlated:
-- Estimate 68,336.4
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13'
OPTION (QUERYTRACEON 4137);

Recall that the TransactionID predicate alone estimated 68,336.4 rows and the TransactionDate predicate alone estimated 68,413 rows. The optimizer has chosen the lower of these two estimates rather than multiplying selectivities.
This is just a different heuristic, of course, but one that can help improve estimates for queries with correlated AND predicates. Each predicate is considered for possible correlation, and there are other adjustments made when many AND clauses are involved, but that example serves to show the basics of it.
Multi-column statistics
These can help in queries with correlations, but the histogram information is still based solely on the leading column of the statistics. The following candidate multi-column statistics therefore differ in an important way:
CREATE STATISTICS
    [stats Production.TransactionHistory TransactionID TransactionDate]
ON Production.TransactionHistory
    (TransactionID, TransactionDate);

CREATE STATISTICS
    [stats Production.TransactionHistory TransactionDate TransactionID]
ON Production.TransactionHistory
    (TransactionDate, TransactionID);

Taking just one of those, we can see that the only extra information is the extra levels of the 'all' density. The histogram still only contains detailed information about the TransactionDate column.
DBCC SHOW_STATISTICS
    (
        'Production.TransactionHistory',
        'stats Production.TransactionHistory TransactionDate TransactionID'
    );

With these multi-column statistics in place...
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS TH
WHERE
    TH.TransactionID BETWEEN 100000 AND 168336
    AND TH.TransactionDate BETWEEN '2007-09-01' AND '2008-03-13';

...the execution plan shows an estimate that is exactly the same as when only single-column statistics were available:


What is the best query to use to monitor a SQL Server database's status?

If you're using SQL 2005+ and only want to return the DB name where the DB isn't in the "ONLINE" state I'd use this:

SELECT
    name
FROM sys.databases
WHERE state != 0;

Remember that databases participating in mirroring or log shipping will not be online or may change state regularly. For more info about the sys.databases DMV see documentation here: http://msdn.microsoft.com/en-us/library/ms178534.aspx


Dynamically Define a Range in a Dimension

HOW TO DO THIS WITH T-SQL:

As requested this is an alternative to my previous answer that showed how to do it per-user with Excel.  This answer shows how to do the same thing shared/centrally using T-SQL instead.  I do not know how to do Cubes, MDX or the SSAS stuff for this, so maybe Benoit or someone who does know that can post its equivalent...

1. Add SalaryRanges SQL Table and View

Create a new table called "SalaryRangeData" with the following command:

Create Table SalaryRangeData(MinVal INT Primary Key)

Add calculated columns by wrapping it in a View with this command:

CREATE VIEW SalaryRanges As
WITH
  cteSequence As
(
    Select  MinVal,
            ROW_NUMBER() OVER(Order By MinVal ASC) As Sequence
    From    SalaryRangeData
)
SELECT
    D.Sequence,
    D.MinVal,
    COALESCE(N.MinVal - 1, 2147483645)  As MaxVal,
    CAST(D.MinVal As Varchar(32))
    + COALESCE(' - ' + CAST(N.MinVal - 1 As Varchar(32)), '+')
                        As RangeVals
FROM        cteSequence As D
LEFT JOIN   cteSequence As N ON N.Sequence = D.Sequence + 1

Right-click on the table in SSMS and select "Edit Top 200 Rows".  Then enter the following values into the MinVal cells: 0, 501, 1001, and 2001 (order doesn't matter for SQL Server, it will create it for us).  Close the table-row editor and do a SELECT * FROM SalaryRanges to see all of the rows and range information.

2. Add AgeRanges SQL Table and View

Do the exact same steps as in #1 above, except replace all occurrences of "Salary" with "Age".  This should make the table "AgeRangeData" and the view "AgeRanges".

Enter the following values into the AgeRangeData [MinVal] column: 0, 15, 20, 30, and 40.

3. Add Ranges to The Data

Replace your SELECT statement with CASE expressions for retrieving the data and ranges with the following one:

SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

4. Everything Else, The Same As Now

From here on, just do everything the same as you currently are.  The ranges should all show up in your PivotTable as they currently do.

5. Test The Magic

Go to the SalaryRangeData table-row editor in SSMS again and delete the existing rows and then insert the following values: 0, 101, 201, 301, ... 2001 (again, order doesn't matter for the T-SQL solution).  Go back to your PivotTable and refresh the data.  And just like the Excel solution, the PivotTable ranges should be automatically changed.

Addition

HOW ADD IT TO A CUBE:

1. Create a View

CREATE VIEW CustomerView As
SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

1. Create a a BI Project in Visual studio and add the CustomerView

Connect to the Database, and add the CustomerView View in the Data Source Views to be the Fact table

2. Create A cube and Define Measure & Dimension

we only need customerId, as a measure for customer count and will have the same fact table as a dimension

3. Add Attributes to the Dimension

4. Connect to Cube from Excel

5. View the Data of the cube in the Excel

6. for Any changes in the Ranges just reprocess the Dimension & cube

if you need to change the Ranges, change the data in the SalaryRangeData and AgeRangeData and then just reprocess the dimensions and the cube


PostgreSQL - VACUUM FULL does not free space back to the OS

Craig's comment was spot on--the WAL files were where the space was going. An earlier-than-usual rm gave us the space back.


Rebalance/move or strink oracle undotablespace

You can simply create a new undo Ts and drop the old one .

show parameter undo

NAME TYPE VALUE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '+DATA_NEWPATH' SIZE 100M AUTOEXTEND ON;

http://www.oracle-base.com/articles/9i/AutomaticUndoManagement.php

SQL> alter system set undo_tablespace=undotbs2;

After that can drop UNDOTBS1. Take a look here for more information.

Regards


No, it comes with PostGIS as a standardized catalog of the spatial reference systems your database can work with. It will be recreated when you CREATE EXTENSION postgis;. In fact, copying the table may cause the installation of PostGIS to fail. PostGIS is supposed to provide that table and the schema. You can see the table documented here.

That said, if you have a custom SRS installed such as the ones provided by http://spatialreference.org you will have to copy those rows over, from the docs,

  Various US state plane spatial reference systems (meter or feet based) - usually one or 2 exists per US state. Most of the meter ones are in the core set, but many of the feet based ones or ESRI created ones you will need to pull from spatialreference.org.


SQL Server 2012 compress column for whole table

Is it possible to have column dictionary compression for the whole table, and not just 1 page?

For the situation you are describing SQL Server's built-in compression probably won't do anything at all. I assume that the documents are stored in NVARCHAR(MAX) columns to allow for non-ASCII characters and to allow values longer than 4,000 characters. Off-page values are not compressed by any of SQL Server's methods (row compression, page compression, or the unicode compression that comes with them) so most likely all of your documents won't be touched.

So in short: no there is nothing SQL Server can do automatically to help you here.

Possible Options: (that are a bit more manual and hacky)

If you are using 2016 or later or Azure SQL[1][2] then you could use the COMPRESS/DECOMPRESS functions to store the data as smaller blobs in VARBINARY columns instead. To do this in a way that is transparent to your application(s) you could create a backing table that stores the data, replacing the real table with a view that selects from this and has INSTEAD OF triggers to compress the incoming data. IF you already have a lot of data this could take a long time to roll out. To remove the need for a many hours maintenance window to compress existing content have both NVARCHAR(MAX) and VARBINARY(MAX) columns in the backing table and have the view decide with DocumentText = ISNULL(DECOMPRESS(CompressedText), UncompressedText) and then you can have a process slowly work through the table compressing a batch of rows at a time,
 keeping the batches small enough to avoid locking issues so you can let the process trundle along in production.

Of course if you can affect the application(s) not just the data layer than it would be more efficient to implement the compression of the documents there instead because you save network transfer as well as storage & memory on the database servers.

If a lot of your documents are truly identical then you may see greater gains from deduplication than from compressing each individually, which you can do using similar techniques: store the documents in their own table with a hash of them as a key/index and store a reference to them in the main table, using the backing-table-plus-view-and-trigger method or elsewhere in the application's data manipulation layer. In 2016/Azure (or if using CLR is an option) you can compress the stored documents too for extra space saving.

Another option if many of your documents are almost identical because they are constructed from templates is to store templates and diffs, though this would likely need to be implemented in the application layer or via CLR as I can't imagine doing string diffs and patches in TSQL would be anywhere near efficient enough. This will only work if the documents are stored in an uncompressed form (i.e. RTF files, old office formats, markdown text; not recent MS Office formats or PDFs) because with compressed formats everything is likely to be different after the first change.

These ideas add complexity and result in drops in write performance, so you'd need to judge them with those negatives in mind. I'm playing with the ideas myself for a similar situation where we have a large collection of pickled data (several tens of Gb in total, a few thousand characters average per item) in text format in a legacy application. I'll see if I can get permission to share the results when I'm done.

--

[1] The question was later updated to note that in their case SQL2012 is being used - the deduplication method will still work, and I've left the compression method in the answer as it could be helpful for others with a similar situation. In fact it would be possible in SQL Server 2012 with a custom CLR module to implement the compression, http://aboutsqlserver.com/2015/04/07/compressing-lob-xml-data-in-the-database/ is the first good looking example from a quick search but there are many more out there.

[2] As noted by Paul in the comments below, with 2017 LOB support is coming to compressed columnstore indexes, so that would be another avenue to research for people using a recent enough version of SQL Server.


How to find corrupt page in SQL Server 2008

Yes, there is a suspect_pages table.

Here is more good info on DBCC CHECKDB http://blogs.msdn.com/b/cindygross/archive/2010/06/13/dbcc-checkdb-database-integrity.aspx


How about something like this..

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT @ContactID = contact_id
  FROM supplier_contacts
 WHERE (first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1) AND
       last_name = SUBSTRING(@Contact, CHARINDEX(' ', @Contact) + 1, LEN(@Contact)))
or
first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1)


How to convert query results into groups of common results

Seems a simpler approach:

SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM
(
  SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue)
   FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH('')), 1, 2, '')
  FROM dbo.MyTable AS t
) AS x GROUP BY Val;


Multiple databases through a single connection MongoDB, GridFS

Yes, I had the same issue. I use the db.getSiblingDB('yourdb') command in my scripts to make many calls to various databases.

This is documented further at http://docs.mongodb.org/manual/reference/method/db.getSiblingDB/ .


Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '*%text%*4' to data type int

The problem is that Element_Answer.Element_Answer column is referencing two distinct tables. The weird *%text%* prefix in some of the rows is apparently there just to tell whether the value references one table or the other. Nevertheless, the fact that two different tables are referenced from a single column is a violation of any normalisation rules.

So, I strongly believe that the best solution to this would include a redesign of the Element_Answer table, so as to split its Element_Answer column into two: one referencing Form_Element_Groups.sort_order and the other referencing Element_Answer_text.ID. Both columns would be numeric as they both are essentially just references to numeric IDs.

For the purposes of the scripts below, I am calling the column referencing feg.sort_order by the same name, sort_order, for consistency, and the other reference is called Element_Answer_text_ID. Feel free to replace the names with whatever you consider more appropriate.

So, first you create two new integer columns in Element_Answer:

ALTER TABLE dbo.Element_Answer
ADD sort_order int NULL
    CONSTRAINT FK_ElementAnswer_FEG
    FOREIGN KEY REFERENCES dbo.Form_Element_Groups (sort_order);

ALTER TABLE dbo.Element_Answer
ADD Element_Answer_text_ID int NULL
    CONSTRAINT FK_ElementAnswer_EAT
    FOREIGN KEY REFERENCES dbo.Element_Answer_text (ID);

Note: the above assumes that both feg.sort_order and eat.ID are either declared as PKs or have a UNIQUE constraint defined on them. Otherwise they cannot be referenced. You can still store references without a formally defined relationship between tables, but you cannot have guaranteed data integrity that way, so I recommend you make sure you can declare the new columns as foreign keys.

The next step would be to populate the new columns from the current ea.Element_Answer one. That can be done in one go with an UPDATE statement like this:

UPDATE
  dbo.ElementAnswer
SET
  sort_order = CASE LEFT(ElementAnswer, 8) WHEN '*%text%*' THEN NULL ELSE Element_Answer END,
  Element_Answer_text_ID = CASE LEFT(ElementAnswer, 8) WHEN '*%text%*' THEN SUBSTRING(Element_Answer, 9, 999999) END
;

Now that the references to different tables sit in different columns, you can drop the original:

ALTER TABLE dbo.Element_Answer
DROP COLUMN Element_Answer;

To retrieve data from Form_Element_Groups and Element_Answer_text, you can complete your query with outer joins to those tables in this manner:

SELECT
  ...
  feg.group_title,
  eat.Element_Answer_text,
  ...
FROM
  ...
  LEFT JOIN dbo.Form_Element_Groups feg ON ea.sort_order = feg.sort_order
  LEFT JOIN dbo.Element_Answer_text eat ON ea.Element_Answer_text_ID = eat.ID
;


In general, the more tables in a database, the more normalised it is.

If your table has many columns, the chances are that some of them will be empty a lot of the time, or repeating types, or other breaches of normalisation, so as you've discovered, normalisation leads to more tables. This is perfectly normal (no pun intended), is nothing to worry about and will lead to easier and more efficient querying


Set the file name in "SELECT ... INTO DUMPFILE"

Because the fileName value is from the database you don't need to put the single quotes around it.

SELECT data FROM table WHERE id = 123
INTO DUMPFILE fileName

I'm not sure how filenames with spaces will come out though if you have any. You could always protect against that by using REPLACE on the filename to either remove them or swap them out with a different character before hand.


Get rows above and below (neighbouring rows) a certain row, based on two criteria SQL

The values for id 5 and LIMIT must be calculate beforehand and passed on to the query, because LIMIT must have fixed values or you have to use a prepared statement

CREATE TABLE table1
    (`id` int, `level` int, `score` int, `timestamp` varchar(19))
;

INSERT INTO table1
    (`id`, `level`, `score`, `timestamp`)
VALUES
    (4, 1, 70, '2021-01-14 21:50:38'),
    (3, 1, 90, '2021-01-12 15:38:0'),
    (1, 1, 20, '2021-01-14 13:10:12'),
    (5, 1, 50, '2021-01-13 12:32:11'),
    (7, 1, 50, '2021-01-14 17:15:20'),
    (8, 1, 55, '2021-01-14 09:20:00'),
    (10, 2, 99, '2021-01-15 10:50:38'),
    (2, 1, 45, '2021-01-15 10:50:38')
;

SELECT
    `id`, `level`, `score`
FROM
    ((SELECT
        `id`, `level`, `score`, 1 orderby
    FROM
        table1 a
    WHERE
        level = 1
            AND `score` > (SELECT
                `score`
            FROM
                table1
            WHERE
                id = 5)
    ORDER BY `score` ASC
    LIMIT 2) UNION ALL (SELECT
        `id`, `level`, `score`, 2 orderby
    FROM
        table1 a
    WHERE
        id = 5) UNION ALL (SELECT
        `id`, `level`, `score`, 3 orderby
    FROM
        table1 a
    WHERE
        level = 1
            AND `score` <= (SELECT
                `score`
            FROM
                table1
            WHERE
                id = 5)
            AND id <> 5
    ORDER BY `score` DESC
    LIMIT 3)) t1
ORDER BY orderby ASC , score DESC

id | level | score
-: | ----: | ----:
 4 |     1 |    70
 8 |     1 |    55
 5 |     1 |    50
 7 |     1 |    50
 2 |     1 |    45
 1 |     1 |    20

db<>fiddle here


Side By Side Upgrade and Migration from 2008 to 2016

I've had to do this and just had a record available for the old server.  Renamed the DNS entries and did a ipconfig /flushdns on the servers themselves since they were replicating back in case of a failback.  We set the DNS to a very low TTL so clients also got updated.  Powershell made all this very easy and fast.

If your clients are easily identified and you have access, you can do a flushdns yourself on them as well.  I recorded and documented all connections going to the database prior to the upgrade so I knew all the clients to look out for.  Then finally I set the old database to block all users except the sole one I was using for replication on the old machine and ran traces to see if anyone was attempting to connect to the old instance.  None did but if they were, I would inform them to update DNS or check their connection string.


What is the difference between using varchar(100) and varchar(255)?

The difference is going to be in how much data you are allowed to store there.  The memory and disk space will depend on how much is actually stored, not what limits you put in place.

Most db's (MySQL included) store varchar fields with a length identifier and then a variable length data string.  This means that the size of the field on disk goes up more or less linearly with the actual data stored.  Similarly in memory, you aren't going to be allocating a large amount of memory for a small string unless it is padded (so varchar uses a lot less memory than char does regardless of underlying storage of the latter).  In fact some db's like PostgreSQL store VARCHAR() and TEXT fields the same (and there is a 1GB compressed size limit there) and try to optimize storage based on actual requirements of the data being used.

In general these decisions should be made based on what your data retention and collection needs are, not based on the performance considerations.  The performance can be tuned and that's the lovely thing about an RDBMS.


SQL Server secondary replica snapshot with Basic Availability Group

That link refers to taking a Database Snapshot of the secondary database, which was possible in Database Mirroring.

Remember there are lots of unrelated things that use the word "snapshot" in SQL Server:

  Database snapshots are unrelated to snapshot backups, snapshot
  isolation of transactions, or snapshot replication.

It's not possible to use a Snapshot Replication with a secondary database:

  In an availability group a secondary database cannot be a publisher.
  Republishing is only supported when transactional replication is
  combined with Always On availability groups.

Replication, change tracking, & change data capture - Always On availability groups


Why does adding LIMIT to my query make it crawl?

Use correct ANSI group by (not the MySQL abomination extension) and see what happens
select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by name,gender,dob,country
having sum(score)>=1000
order by sum(score) desc limit 50

Why?
GROUP BY in MySQL implies ORDER BY which causes the filesort here
Changing the query to be more standard may help the optimiser
More on the MySQL abominations extensions:

https://stackoverflow.com/a/6642253/27535
https://stackoverflow.com/a/10894876/27535
https://stackoverflow.com/a/6457144/27535

And from the MySQL Docs on ORDER BY Optimization

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
...
You have different ORDER BY and GROUP BY expressions.


Why MS SQL bigint type is implicitly mapped to float64 python type, and what is the best way to handle it?

I write down the best solution what I found:
CREATE TABLE #test (
    big_integer BIGINT
);

INSERT INTO #test
    (big_integer)
VALUES
    (36028797018963968),
    (36028797018963968 + 1);

CREATE TABLE #out (
    big_integer BIGINT
);

INSERT INTO #out
    EXECUTE sp_execute_external_script
        @language = N'Python',
        @input_data_1 = N'SELECT CAST(big_integer AS VARCHAR(20)) AS big_integer FROM #test',
        @script = N'
import numpy as np
print(InputDataSet)
InputDataSet["big_integer"] = InputDataSet["big_integer"].astype(np.int64)
InputDataSet["big_integer"] = InputDataSet["big_integer"] + 1
InputDataSet["big_integer"] = InputDataSet["big_integer"].astype(str)
OutputDataSet = InputDataSet
';

SELECT big_integer FROM #out;

I did what I supposed in the question:

cast the big_integer column to VARCHAR(20) the maximum length of a string representation of 64 bit signed integer:

In [34]: len(str(-2**63))
Out[34]: 20

In [35]: len(str(2**63-1))
Out[35]: 19

cast it back to numpy.int64 type in the external script.

made a simple calculation: incremented all values in the column

cast it back to string still in python. This step also necessary, because the implicit type conversion works both ways.

Inserted the values into the big_integer column of the #out table. Which also has a BIGINT type and the returned strings were implictly casted to BIGINT

Note
It would be a rare event where you need to handle this problem. The integer value must be bigger than 2^52 so a distance between two float64 will be greater than 1.
In [50]: def float_distance(x):
    ...:     x_float = float(x)
    ...:     x_next_float = np.nextafter(x_float, np.inf)
    ...:     x_float_diff = x_next_float - x_float
    ...:     return(x, x_float, x_next_float, x_float_diff)

In [51]: float_distance(2**52)
Out[51]: (4503599627370496, 4503599627370496.0, 4503599627370497.0, 1.0)

In [52]: float_distance(2**53)
Out[52]: (9007199254740992, 9007199254740992.0, 9007199254740994.0, 2.0)

I guess this may occur if your store the results of high throughput scientific research in physics or in bioinformatics.


Can I Avoid Joining The Same Table 3 Times?

Remove HOME_TEAMand AWAY_TEAM columns from the GAMES table and add a HOME_AWAY flag column to theGAME_TEAM_STATISTICS table.

Right now, you need to code for an ASSERTION (because they do not yet exist) to ensure the GAME_ID+TEAM_ID combo in GAME_TEAM_STATISTICS matches the GAMES.HOME_TEAM/GAMES.AWAY_TEAM information.


You want to use the three-part naming syntax:

<database>.<schema>.<object>
  wwtest  .  data  . Mobiles

...however you seem to be confused between your schema naming. In the table data.Mobiles, data is the schema, not dbo, so the naming should be wwtest.data.Mobiles.

dbo is also a schema, so there is no need to include it in your query when you are selecting from an object in a different schema.

Otherwise you end up with a completely different naming convention. The four-part naming sytax is as follows:

<server>.<database>.<schema>.<object>
 wwtest .   dbo    .  data  . Mobiles

In your example, you are looking for a database called dbo on a server called wwtest.

That is why you get the error:

  Could not find server 'wwtest' in sys.servers.


Check the SIDs of the accounts in SQL and AD before the SQL login deletion to confirm they are indeed the same and the AD admins didn't really delete and recreate the AD user account just telling you otherwise.

Articles: http://sqlserver-help.com/2014/05/27/sql-internals-how-to-map-login-sid-to-domain-account/ and https://technet.microsoft.com/en-us/library/ff730940.aspx

There may be a way to get both from PowerShell but confirm what the AD domain and forest functional levels are and research with your version of SQL Server for known bugs -- maybe it's time to upgrade/update one or the other.


Users vs. People - where to keep the email address?

I would create a distinct emails table and use a surrogate key to instantiate the reference of the email address to the person and to the user.  The model would look like this:

This handles the need to associate the email address with the users as well as the persons, but eliminates the redundancy of using the actual email address text to instantiate the relationship.  It has the additional benefit also of ensuring you understand who is actually using a given email address, as you can place an alternate unique index on the email address text as a given email address in unique.  I think this kind of solution would eliminate the cons while still retaining all of the pros.


When using a master plus multiple slaves, how do I change over from one server to a brand new one with minimal downtime?

I don't know how well this works on Heroku but I think it should work if one is using streaming replication internally.  I would recommend repmgr which is an open source replication cluster management tool from 2ndQuadrant.  This provides a general monitoring and management framework for replicated database clusters.  In 9.3, repmgr will be less necessary but still may be helpful for integration into heartbeat systems.  It provides a command line tool for failovers, preparing slaves, etc.

If you have to do something like this manually and repmgr wont work for you, basically, what you need to do is this:

turn off the master (shoot the other node in the head)
promote the slave to a master
re-base your other slaves and point them at the new master
turn your old master into a new slave when you can bring it up.

How you do this will depend to some extent on the replication approach.


Can a hash index become inconsistent for "no reason"?

FINALLY, after so much time i found the cause of this problem. I actually saw the machine where postgres was running suffering a hard reset, after that, the  exact error happened.
Turns out the index i was using was a HASH index instead of the default B-Tree.
So, looking up on postgres documentation, the first thing i saw was a giant yellow warning explicitly stating about it's unsafety when a crash occurs.
Taken from the docs:

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

What i did was simply drop the hash index and create a new one with B-tree algorithm. So far it seems to be the working solution.


Database query: join tags with exact matches or at least one

This is how I solved it:

For exact combination

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date <= ?
       OR mm.last_promo_sms_date IS NULL)
  AND mm.id IN
    (SELECT mm.id
     FROM merchant_mobile_customer mmc
     JOIN merchant_mobile mm ON mm.id = mmc.merchant_mobile_id
     JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
     RIGHT JOIN tag t ON mmt.tag_id = t.id
     WHERE t.name = ?
       OR t.name = ?
       OR t.name = ?
       AND mm.merchant_id = ?
     GROUP BY mm.id
     HAVING COUNT(*) = 3 )
ORDER BY mm.last_promo_sms_date ASC ;

For at least one

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date <= ?
       OR mm.last_promo_sms_date IS NULL)
  AND mm.id IN
    ( SELECT mm.id
     FROM merchant_mobile_customer mmc
     JOIN merchant_mobile mm ON mm.id = mmc.merchant_mobile_id
     JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
     RIGHT JOIN tag t ON mmt.tag_id = t.id
     WHERE t.name = ?
       OR t.name = ?
       AND mm.merchant_id = ? )
ORDER BY mm.last_promo_sms_date ASC ;