How to count items in specified ranges in one query?

This will return a row for every range from 0-49999 up to whatever range the highest followers count falls in.

DECLARE @RangeSize int = 50000;
DECLARE @NumberOfRanges int = (SELECT MAX(FollowersCount) FROM #table) / @RangeSize;

;WITH n(n) AS
(
  SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n <= @NumberOfRanges
), ranges(s,e) AS
(
  SELECT @RangeSize * (n-1), @RangeSize * n FROM n
)
SELECT r.s, r.e-1, COUNT(t.Id)
FROM ranges AS r
LEFT OUTER JOIN #table AS t
ON t.FollowersCount >= r.s
AND t.FollowersCount < r.e
GROUP BY r.s, r.e
ORDER BY r.s;

If you only want the ranges with a non-zero user count, just change the LEFT OUTER to INNER. And I prefer an open-ended range over BETWEEN for two reasons - one, it's ambiguous, since if someone has exactly 100K, do they fall in the 50k - 100k bucket, or the 100k - 150k bucket, and two, while you can force that either way, it doesn't translate well to other data types, where it's even more troublesome.


Replicating redis foreign table via postgresql synchronous replication

a Foreign Data Wrapper (fdw) point and interpret the data (keep no data),its like a view. Is the redis_fdw compiled on the slave side? if not complile it and master and salve points to your RD1-Table.


Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I agree that MySQL has to do search all 30 partitions. Since you are searching by an auto_increment column, it must be declared as a PRIMARY KEY.
Since indexes are BTREEs, I would expect O(log n) search time per partition.
MySQL (really Oracle) says in the Documentation on Partitioning Overview:

Other benefits usually associated with partitioning include those in the following list. These features are not currently implemented in MySQL Partitioning, but are high on our list of priorities.
Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By “parallelized,” we mean that the query can be run simultaneously on each partition, and the final result obtained merely by summing the results obtained for all partitions.
Achieving greater query throughput in virtue of spreading data seeks over multiple disks.

Given these comments about partitions being processed in parallel on their wishlist, it would have to be true at this present time to say that all partitions have to be searched sequentially.
UPDATE 2014-02-21 10:51 EST
With regard to your original question, you stated

Partitioning on SHA1 PRIMARY KEY
Partitioned MyISAM table with 30 partitions

OBSERVATION #1
Given the fact that SHA1 output is always 40 characters, my guess is that you are not using RANGE partitioning.
Here is what MySQL Documentation says on HASH partitioning

Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. With range or list partitioning, you must specify explicitly into which partition a given column value or set of column values is to be stored; with hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.

Since you are not using RANGE partitioning

MySQL will place new data into a partition after figuring out which partition to choose based on how even data are distributed.
There is no mechanism to reasonably determine any ordering of the PRIMARY KEY.
Using PARTITION BY HASH creates buckets for data with no rhyme or reason for ordering.
Using PARTITION BY LINEAR HASH could create ordered buckets.

The overall problem is that the PRIMARY KEY is not an INT. You cannot set up any real RANGE partitioning for a non-scalar type (such as CHAR string). Given this scenario, MySQL cannot avoid doing a SELECT lookup without opening every partition.
OBSERVATION #2
A partitioned MyISAM table with 30 partitions is made up of

30 .MYD files
30 .MYI files

Each MYD requires a file handle. Each MYI requires a file handle. Thus, you can have 60 file handles open. Here is what you can do to see how many file handle open up after a mysql restart
FLUSH TABLES my_partitioned;
SELECT variable_value INTO @open1
FROM information_schema.global_status
WHERE variable_name='open_files';
SELECT bar FROM my_partitioned WHERE foo_ak = 42;
SELECT variable_value INTO @open2
FROM information_schema.global_status
WHERE variable_name='open_files';
SET @opendiff = @open2 - @open1;
SELECT @opendiff;

What you can conclude:

If @opendiff is close to 60, MySQL tried to open all the partitions.
If @opendiff is close to 0, MySQL had pruned the partitions

The second bulletpoint is what you want because it will show how heavy handed MySQL is at opening partitions,... or not.


Configuring PostgreSQL to match server configuration

This is a pretty broad topic. I suggest you pick up a copy of Greg Smith's excellent book "PostgreSQL 9.0 High Performance" and work through it. http://www.packtpub.com/postgresql-90-high-performance/book

Can you show the output of the following query:

SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

Things to test:

wal_buffers = 16MB
checkpoint_segments = 32 #(or higher)
shared_buffers = 6GB
random_page_cost = 2.0 # not the first choice of things to tune

What are your logging settings? Are there obvious slow queries? If so, you can run them through pgbadger to give you a good idea of queries to target.

Here are some settings you can test, given your current settings and what you have mentioned about your system.

log_line_prefix = '%t [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] '
shared_buffers = '6GB' # requires cluster restart
checkpoint_completion_target = 0.8
checkpoint_segments = 32
effective_io_concurrency = <lesser of cpus and disks/channels>
log_destination = stderr    # This and the log_directory are what I use,
log_directory = pg_log      # it is up to you whether or not you want to change them
logging_collector = 'on'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = '2s' # Set this as necessary, set lower per role
random_page_cost = 2.5 # If faster disks, drop to 2.0
track_activity_query_size = 4096
work_mem = '50MB'   # Adjust this as necessary.
                    # Be aware this is a per-action setting,
                    # eg. aggregation, sorts, etc can each consume up to
                    # this amount of RAM

You'll need to restart the database for some of those settings to take effect.


Script out Oracle DDL in an automated fashion

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size.  By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text.

It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL.

The script you need is:

-- Run this script in SQL*Plus.

-- don't print headers or other crap
set heading off;
set echo off;
set pagesize 0;

-- don't truncate the line output
-- trim the extra space from linesize when spooling
set long 99999;
set linesize 32767;
set trimspool on;

-- don't truncate this specific column's output
col object_ddl format A32000;

spool sys_ddl.sql;

SELECT dbms_metadata.get_ddl(object_type, object_name, owner) || ';' AS object_ddl
FROM DBA_OBJECTS
WHERE
      OWNER = 'SYS'
  AND OBJECT_TYPE IN (
      'TABLE'
    , 'INDEX'
    , 'SEQUENCE'
    , 'VIEW'
  )
ORDER BY
    OWNER
  , OBJECT_TYPE
  , OBJECT_NAME
;

spool off;


MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

Foreign key relationships are to enforce data integrity, not for query performance, that is what indexes are for. Also note that InnoDB creates an index on each column with a foreign key relationship.

However I would recommend having the foreign key relationships to ensure that the data is always valid, especially when updating and deleting, which may become significant when you have to start archiving data.


The idea would probably be to look for the empty tables using INFORMATION_SCHEMA.TABLES

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS =  '0'
AND TABLE_SCHEMA = 'my_database_only'

Then you might be able to produce an SQL query with

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS = '0'
AND TABLE_SCHEMA = 'my_database_only';

Quick and a bit dirty, but should actually work.


How is time stored in the FireFox sqlite database "places.sqlite"?

It's not encrypted.

The db-schema is:

CREATE TABLE moz_places (
  id INTEGER PRIMARY KEY,
  url LONGVARCHAR,
  title LONGVARCHAR,
  rev_host LONGVARCHAR,
  visit_count INTEGER DEFAULT 0,
  hidden INTEGER DEFAULT 0 NOT NULL,
  typed INTEGER DEFAULT 0 NOT NULL,
  favicon_id INTEGER,
  frecency INTEGER DEFAULT -1 NOT NULL,
  last_visit_date INTEGER ,
  guid TEXT,
  foreign_count INTEGER DEFAULT 0 NOT NULL
);

It's the field last_visit_date and is saved as Timestamp (Integer in milliseconds)
it means you could divide it by 1 000 000 and than use it as normal timestamp with seconds, when you don't need the precision of milliseconds.


How to run a mysqldump from a .txt or .sql file inside a command line window?

The commandline mysql allows \! to do what you want, but you appear to be in Windows, which does not really allow for such a feature.

So, No, you can't do it.

For reading INSERT statements (etc) back in there is the source command.

Can you turn things inside out?  You can get a list of databases, or otherwise create MySQL commands, from

mysql ... -e "statement..."

and you redirect that to a .bat file, which could then be executed.

It seems like you need a .bat file.


You can't configure a MySQL server to connect to multiple masters as a replication slave.

You can configure Master B with log_slave_updates enabled, Master A with binlog_format = ROW and declare table 2 using Engine=BLACKHOLE on Master B, and the replication events from Master A's binary log will cascade through Master B onto Slave C, which would be connected only to Master B (not directly to A).

Otherwise, your alternative is Multi-Source Replication in MariaDB, which (unlike Oracle MySQL) does allow a single server to connect to multiple upstream masters simultaneously, as long as there are no conflicting combinations of schema+table names being replicated.  This configuration should work whether the master servers are actually running MariaDB or if they're running MySQL.


Database not able to startup showing an error in reading control file

if you do not have problems with your disks and the controlfile is corrupted you can do the following:
start the database in nomount (startup nomount), then check the output of the command

show parameter control

(run the command in sqlplus). If the command shows more than one control file, you can overwrite D:\APP\FROZEN\ORADATA\ORCL\CONTROL01.CTL with onw of the others controlfile listed in the output.

Alternatively you can try to overwrite the bad controlfile with the snapshot controlfile:

  The snapshot control file is a copy of a database control file created
  in an operating system-specific location by RMAN. RMAN creates the
  snapshot control file so that it has a consistent version of a control
  file to use when either resynchronizing the recovery catalog or
  backing up the control file.

In Windows the default location should be

%ORACLE_HOME%/database

The name of the snapshot controlfile should be:

snapcf_instancename.f

If you can't solve in this way, you have to restore the controlfile from backup.
Regards
Giova


What would be the best way to show table/database growth?

There's a hierarchy in analytics value that goes descriptive, diagnostic, predictive, prescriptive. What your question talks about is "descriptive." I think you should aim for "predictive."
You're collecting these numbers because you're concerned about exhausting some resource - disk space, backup size, sequential IDs, whatever. That's what will cause problems, not the absolute size of the table per se. Therefore that is what should be reported.
The output should be

Extrapolating over the last 4 months, Disk D: will be full by Jan 2021.
Last month's report predicted Feb 2021.

There are many ways to calculate the exhaustion point. Simple linear extrapolation is one. I've used Monte Carlo to some success.


Moving data from Oracle to SQL Server : Table size optimization

Try non unicode strings: varchar instead of nvarchar. Oracle's VARCHAR2 isn't unicode datatype and in nvarchar every character takes 2 bytes instead of 1.


From my understanding, This is a restriction specified on the server piece. So you would have to modify it's web.config to change the max upload size.  I'm not aware, although I could be wrong, of a way to change this setting through options within the reporting services management site.

You could ask your client to temporarily modify this.  Or ask them if they would be able to increase the max upload limit to 10mb.


Create index on a Postgres table as fast as possible

If it's an option, you could pre-sort the data externally, before database import.

In my test using PostgreSQL 9.6.1, a table with three double precision columns and 10M records with random values, creating an index on the same data, but preordered, shaved off 70% of index creation time:

db=# create table indexing_test_data (a varchar(50), b varchar(50), c real);
CREATE TABLE
Time: 3,586 ms
db=# insert into indexing_test_data select random()::text, random()::text, random() from generate_series(1, 10000000);
INSERT 0 10000000
Time: 25590,475 ms
db=# select a, b, c into indexing_test_sorted from indexing_test_data order by a, b, c;
SELECT 10000000
Time: 77389,665 ms

db=# create index test_data_idx on indexing_test_data (a, b, c);
CREATE INDEX
Time: 57399,140 ms

db=# create index test_sorted_idx on indexing_test_sorted (a, b, c);
CREATE INDEX
Time: 16219,639 ms

An extra speedup can be had if you can afford to use a database with C locale and collation, which can then use a feature called abbreviated keys, available since PostgreSQL 9.5. This speeds up sorts up to 20x, but only works on C locale, due to buggy locale support in older libraries:

db=# create database testdb lc_collate "C" lc_ctype "C" template template0;
CREATE DATABASE
Time: 429,710 ms
db=# \c testdb
You are now connected to database "testdb" as user "user".
testdb=# create table indexing_test_data (a varchar(50), b varchar(50), c real);
CREATE TABLE
Time: 2,794 ms
testdb=# insert into indexing_test_data select random()::text, random()::text, random() from generate_series(1, 10000000);
INSERT 0 10000000
Time: 25977,964 ms
testdb=# select a, b, c into indexing_test_sorted from indexing_test_data order by a, b, c;
SELECT 10000000
Time: 20794,850 ms

testdb=# create index test_data_idx on indexing_test_data (a, b, c);
CREATE INDEX
Time: 16371,426 ms

testdb=# create index test_sorted_idx on indexing_test_sorted (a, b, c);
CREATE INDEX
Time: 8046,787 ms

Here, indexing sorted data takes around 50% time of unsorted, but compared to first version, that took 57 seconds, you're down to 14% of time.

Other things, as mentioned in the comments, can help too: setting maintenance_work_mem appropriately and not running create index concurrently (which is significantly slower).


In general, everyone that gives advice on this says to never enable auto-close because it just causes unnecessary delay when a user queries the database after it's been closed, along with a spike in resource utilization when a database has to come online.  And in general, that is golden advice for databases that are used on a daily basis.

However, when you get outside the theoretical perfect environment where every production database is really in production, it may make sense to enable auto-close in some instances if the following conditions are met:

There is a large number of databases that contain only archive data that is queried infrequently (less than once per day, for example).
The users are aware that there will be some delay the first time they query the database after it's been idle for a while, and they are OK with that.

Due to data retention policies requiring the data to stay around long after it's no longer used on a daily basis, and the struggle to get business units to actually decommission database they aren't using, it is certainly reasonable that auto-close might make sense in some cases.  If it can lower your hardware resource requirements and still meet the users' needs, it may be something to seriously, but cautiously, consider.

As for the memory allocation difference cited, there is no way to be sure what caused the difference without more information.


Mirroring problems after removing domain

After days of searching, I found the answer 10 minutes after this post. The problem was that the admin account did not have read/write rights on c:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ . After changing that I also had to recreate the master keys and certificates to make it work.


DB locks being created on temp tables. Can percona ignore temp tables for replication

Consider trying row based replication ( https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html ) in your case to prevent issues like yours. If your inserts are mostly complex queries, inserting only the resulting rows, rather than reexecuting the full queries may decrese locking and performance. Note this is highly application specific and may have other disadvantages in other queries.


On SQL Server 2008 R2 I'd guide you to SQL Server Utility Control Point, with some details here.

From MSDN description:
"The SQL Server Utility models an organization’s SQL Server-related entities in a unified view. Utility Explorer and SQL Server Utility viewpoints in (SSMS) provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP). Entities that can be viewed in the SQL Server UCP include:

Instances of SQL Server
Data-tier applications
Database files
Storage volumes

Resource utilization dimensions that can be viewed in the SQL Server UCP include:

CPU utilization
Storage space utilization"

On 2008 and earlier - MOM, as Gaius pointed, or maybe give a try to Multi Server Administration as defined by MS. You can see detailed article on SQLServerCentral Multi-Server Administration or on MSDN.


It's getting upset about your other columns. So try:

SELECT [NEW]
,[IN]
,[OUT]
,READY = [READY FOR DISPOSAL]
,TRANSIT = [IN ROUTE TO DISPOSAL]
,[DISPOSED]
,[TYPE] = [ItemType]
FROM (SELECT ItemType, Status FROM [EVAULTTEST].[dbo].[Items]) AS ei

PIVOT(
COUNT([Status])
FOR [Status] IN ([NEW], [IN], [DISPOSED]
, [IN ROUTE TO DISPOSAL], [OUT], [READY FOR DISPOSAL])
) AS piv

... and this is because the implicit grouping is on any column that's not in the PIVOT clause. So removing the extra columns in the first part of the query should fix it.


PostgreSQL IN query performance on a larger array

Correctness
First things first.

I [...] want to count how many of the words already exist in a specific column

Your query does not do that if there can be duplicates in the input, and the misplaced DISTINCT keyword in the query suggests as much.
When passing ('name1', 'name1', 'name1'), with 'name1' in the table, the correct answer is: 3. But your query says 1.
The DISTINCT keyword makes sure of it, but it's a complete waste. The IN construct folds duplicates in the input implicitly, and there are no duplicates in your unique column. IN is the wrong tool to answer your question to begin with. One of these queries would do that:
SELECT count(*)
FROM   unnest(ARRAY['name1', 'name1', 'name1']) name
JOIN   account USING (name);

SELECT count(*)
FROM   unnest(ARRAY['name1', 'name1', 'name1']) i(name)
WHERE  EXISTS (SELECT FROM account a WHERE a.name = i.name);

The first one counts duplicates in the table (multiplied with duplicates on the same name in the input), the second one does not. Since there are no duplicates in the table, both do the same.
Adapted question
Let's assume you meant to ask:
How many of the words in my table are listed in the input (at least once)?
Now duplicates in the input don't matter (logically), and IN fits the job description. But we still don't need DISTINCT while the table column is unique (and rows are not multiplied by joins or similar).
Removing DISTINCT makes it faster already. Next, there is no need to involve the additional column id. Insignificant as though this may seem, it can force an index scan instead of an index only scan. count(name) is typically faster (and safer). But use count(*) instead. Faster, yet. And equivalent while id is the PK:
SELECT COUNT(*) FROM account WHERE name IN ('name1', 'name2', 'name3');

With modern Postgres that should be about the optimum for your case. You should see index-only scans on the unique index (if the table is vacuumed enough, read the manual), which is as fast as it gets, even with thousands of search terms. In particular do not rewrite with UNION or OR as has been suggested. That would get you a much more verbose and slower query.
Nor will it be faster to put the words in a (temporary) table first. That might be useful if you go on to do more with it, but not for this query alone.


Calculating percentage over sub query SQL

Part of the secret is

SELECT  (...)/total, ...
    FROM campaigns_log
    JOIN ( SELECT SUM(...) AS total FROM ... ) x

That is, compute the total separately and make it available in the expressions.

Similarly:

SELECT @total := SUM(...) FROM ...
SELECT (...)/@total, ...


Restore database - maintenance plan task

There is no 'Restore DB Task' in the maintenance plan. You will have to use the Execute T-SQL Statement Task. In that task, add a restore script in your T-SQL statement. Simple example:

RESTORE DATABASE YourDBName FROM DISK = 'D:\DBbackups\YourDB.BAK'
WITH REPLACE
GO

Then create a schedule for weekly execution. Hope this is what the business requires, as you are overwriting a DB.


as far as I could find no option in phpmyadmin, but I found http://www.mysql.com/products/workbench/  which seems very useful for visual database design.


Why does this ALTER TABLE ADD FOREIGN KEY query create two foreign key constraints

This looks like a bug in the lexer/parser logic.

My theory is that it has to do with the name of the constraint. When a foreign key constraint is defined without a name, MySQL assigns automatically a name of its choice. This is usually of the form referencingtable_ibfk_X.

Since the bug appears when we define a name for the constrain that contains the string _ibfk_, we can conclude that the parser gets confused somewhere and adds the 2nd constraint (see sql-fiddle, bug appears in version 5.5.32).

And yes, it's a known bug. It seems that it was fixed in some older minor 5.5 versions but then reappeared. Please check the latest 5.5 and 5.6 versions (5.5.34 and 5.6.22) if it has been fixed (the bug does not appear in 5.6.6).


CDC Capture job failure/CT table column changes after applying 2014 SP2+CU4

It looks like CU2+SP4 (12.0.5540) changes the structure of the underlying _CT system tables that get generated.  It adds the column [__$command_id] int null.

When you upgrade in place to the new CU, the capture jobs will start failing or go to 'between retries...' with the following errors:

Dropping and recreating the capture job does not work (using cdc_add_job).  I had to drop the capture agent CDC job, disable CDC (sp_cdc_disable_table) on the table and then re-enable CDC (sp_cdc_enable_table), which re-adds the job and successfully fires the job and begins collecting.

The issue is that, upon dropping and re-enabling CDC, you lose the previously existing _CT table and all of it's data.

The only information I ahve found about CU4 changing CDC is the following from MS:
https://support.microsoft.com/en-us/help/3030352

It says that they fixed the ordering issue, but do not list any structural changes to the CDC system tables.

UPDATE 1:
I've opened a paid support case with MS regarding this issue.  There's also a connect item open for it: https://connect.microsoft.com/SQLServer/Feedback/Details/3130381

UPDATE 2:
Per MS, using the command sp_vupgrade_replication after patching to CU4 will allow the capture agent job to start successfully.  It still doesn't address the jobs failing before the command is run.  It also does not address this added column not being documented anywhere.  Still waiting to see what the final verdict is.

UPDATE 3:
MS has officially posted to their blog and recognized this as an issue:
https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/

No word yet on if they will be re-releasing the CU4 patch to not require manual intervention to restart the CDC jobs or officially document the column change.


Example of Logical Schema vs Physical Schema?

I think this StackOverflow Answer probably defines the difference between the two pretty well.
Specifically these two points:

A logical schema is a conceptual model of the data. In relational databases, it's often platform-agnostic - i.e. the logical schema can, in principle, be implemented on any SQL database.

The physical schema converts the logical schema into an implementation that works on a particular database platform. Sometimes, this is a largely mechanical exercise, applying the right datatypes to the attributes...

So in other words, if you had a schema that represented MovieTheaters, you'd probably have a few tables Movies, TicketSales, ConcessionSnacks. The TicketSales table would probably have a TicketId column, a Price column, and a MovieId column. This high level detail is essentially your logical schema.
Once you start implementing this schema on a specific database system, take Microsoft SQL Server for example, and using features and data types specific to Microsoft SQL Server, is when it becomes a physics schema. For example, your TicketSales table's TicketId column could be a INT data type with a primary key on it, and the MovieId could be a BIGINT field with a many-to-one foreign key on it, and the Price column could be a DECIMAL(4,2). And the Movies table can have a unique constraint on the MovieTitle column to prevent duplicate Movies being inserted into it.


What is the difference between ROLE and PRIVILEGE for Oracle?

Yes, a role is a collection of system and/or object privileges and/or other roles.  It simplifies privilege management by allowing you to manage bundles of privileges.


How to change a user's name in SQL Server 2000?

In SQL Server 2000, I think you'll have to drop and re-create the user, unless you want to reply on crazy hacks like this one:

Rename User Login

I don't really recommend that, both because updating system catalogs is dangerous, and also because it uses undocumented, unsupported, and buggy sp_MSForEachDB (see why I urge you to stay away from that one).

In versions newer than SQL Server 2000, it is as simple as:

ALTER USER foo WITH NAME = bar;


Can you try PL/SQL? With FOR loops, Oracle performs implicit bulk collects of 100 rows at a time, as per this link.

Make sure you are doing the order by in your select, as you said this gave you the correct results.  I also recommend you to clean your duplicates.

SET SERVEROUTPUT ON
declare
  cursor c1 is
    select <YOUR SELECT>
begin
  for c in c1 loop
    null;
  end loop;
end;
/


Powershell SQL Server High Availability PrimaryReplicaServerName - inconsistent results

Whether you run that code while you are on the primary or secondary is irrelevant. When your path is SQLSERVER:\SQL\SERVERx\INSTANCE... you are technically running it against that server, it does not matter if you ran the command from the server or a random desktop in the domain.

The issue with it showing as secondary though is local. I do not get that on my servers. If, you had PowerShell and the provider opened on "Server1" when you failed it over then I would suggest opening a new PowerShell prompt and see if it shows the same thing. If it does then try from another computer all together.


Performance impact of a group by function call

So I guess my performance impact comes down to the following

Since your question is about performance I advise you to start using performance tools that you already have at your disposal to dig into this problem. This way your guess will be more precise or better yet: you won't have to guess at all.

Display an Actual Execution Plan

SET STATISTICS TIME (Transact-SQL)

SET STATISTICS IO (Transact-SQL)

If after using these tools you are not able to figure out your answer, update your questions adding their result plus the function code and we'll try to help you further.

  However, do you know if anything I can do in the group by part of the
  query to reduce the delays?

Without your actual code it's not possible to help very much.

  Is this function being called twice because I have it in the select
  and the group by portion of my query?

It can be verified on the execuion plan.


How to show a column as NULL if it doesn't exist in match?

You're talking about how you display or project null when the query returns it. Remember, null is a type that represents something. It has no fixed display/rendering. For that use coalesce,

SELECT coalesce(a.message_id, 'NULL') -- or whatever you want the result is `null`

instead of

SELECT a.message_id

null
For more information see the docs on COALESCE,

  The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display.


Many-to-many tables tracking participation at multiple events

Ideally, your system could comprise:

a Locations table with location information and a location_id
a People or Observers table with personal details and a person_id
an Observations table just the way you have it
and an Observer_Observations table with person_id and obs_id (i.e. your many-to-many table)

The total_time column in your Observers table should be calculated from Observer_Observations rather than stored.


Incorrect string value: '\xC5\xABt\xC4\x97'

Since nobody answer the question and I finally figured it out. I'll answer my own question and hope it may help someone later.

Data is being inserted by stored procedure, alter dababase, table charset and collations will not override charset and collation setting in stored procedure, nor the changes in my.cnf.

After all the database chartsets, collations are correct, drop and reinsert the stored procedure.
Before drop, the procedure was like this:

| MAILSERVER | sp_getDataForNewReward         | PROCEDURE | root@localhost | 2014-05-19 23:08:38 | 2014-05-19 23:08:38 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

After drop and reinsert, it become this:

| MAILSERVER | sp_insertDeliveredEmail        | PROCEDURE | root@localhost | 2015-01-15 23:38:24 | 2015-01-15 23:38:24 | DEFINER       |         | utf8mb4              | utf8mb4_unicode_ci   | utf8mb4_unicode_ci |

Then the procedure will work properly. When stored procedure being created, it will get the charset and collation from the database setting, so after database setting, it need a "refresh".


MS sql query value one table but not another join table where*

Assuming the following table structures and that you want to return students that have NOT registered for CourseID 11...you need to change your LEFT JOIN to include CourseID = 11 and then add CourseID is NULL in the WHERE clause to pull the students that are not registered for that course.

In your query having CourseID = 11 in your WHERE clause limits the results to the students that have registered for that course.

CREATE TABLE #AllStudentsEmail (StudentID INT, StudentName VARCHAR(20), StudentEmail VARCHAR(20))
CREATE TABLE #Course (CourseID INT, StudentID INT)

INSERT INTO #AllStudentsEmail VALUES (1000,'Joe T','jt@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (2000,'Jill B','jb@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (3000,'Tommy S','ts@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (4000,'Sally J','sj@blank.edu')

INSERT INTO #Course VALUES (9,1000)
INSERT INTO #Course VALUES (10,1000)
INSERT INTO #Course VALUES (11,1000)
INSERT INTO #Course VALUES (9,2000)
INSERT INTO #Course VALUES (10,2000)
INSERT INTO #Course VALUES (11,2000)
INSERT INTO #Course VALUES (9,3000)
INSERT INTO #Course VALUES (10,3000)
INSERT INTO #Course VALUES (10,4000)
INSERT INTO #Course VALUES (11,4000)

SELECT  #AllStudentsEmail.[StudentID],
 [StudentName],
 [StudentEmail],
 [CourseID]
FROM #AllStudentsEmail
LEFT JOIN #Course ON #AllStudentsEmail.StudentID = #Course.StudentID
    AND #Course.CourseID = 11
WHERE #Course.CourseID IS NULL

DROP TABLE #AllStudentsEmail
DROP TABLE #Course

--results

StudentID   StudentName StudentEmail    CourseID
3000    Tommy S ts@blank.edu    NULL


The view is called whatever is listed in the CREATE statement, which would look like this in the script.

CREATE VIEW viewA AS

The vw_tableB is an table alias
https://technet.microsoft.com/en-us/library/ms187455.aspx

  The readability of a SELECT statement can be improved by giving a table an alias, also known as a correlation name or range variable. A table alias can be assigned either with or without the AS keyword:


Design for hierarchical embedded database

With the restrictions you have supplied and if you want to implement this is DBMS, I think you could use a supertype/subtype pattern for the 5 (or more) types of objects and only one additional table for the "object contains objects" list:

-- auxiliary table that has only 5 rows, one for each type
CREATE TABLE types
( level TINYINT NOT NULL PRIMARY KEY
, type_name VARCHAR(10) NOT NULL UNIQUE
) ;

INSERT INTO types (level, type_name)
VALUES (1,'A'), (2,'B'), (3,'C'), (4,'D'), (5,'E') ;

-- this is the supertype
CREATE TABLE objects
( object_id INT NOT NULL UNIQUE
, level TINYINT NOT NULL REFERENCES types (type_id)
, UNIQUE (level, object_id)
) ;

-- and the subtypes
CREATE TABLE a
( object_id INT NOT NULL PRIMARY KEY
, level TINYINT NOT NULL CHECK (level = 1)
--- various columns
, FOREIGN KEY (level, object_id)
    REFERENCES objects (level, object_id)
) ;

--- and similarly for the other 4 tables
--- all referencing objects table

and finally the list table, which is simple many to many junction table with only an additional constraint to ensure that an object of higher level can contain only objects of lower levels:

CREATE TABLE contains
( container_level TINYINT NOT NULL
, container_id INT NOT NULL
, item_level TINYINT NOT NULL
, item_id INT NOT NULL
, PRIMARY KEY (container_id, item_id)
, FOREIGN KEY (container_level, container_id)
    REFERENCES objects (level, object_id)
, FOREIGN KEY (item_level, item_id)
    REFERENCES objects (level, object_id)
, CHECK (container_level > item_level)
) ;

SQLite has CHECK constraints and has recently implemented recursive CTEs, if you want to get the results of an object and all the objects underneath it in one query.

From a fast search, H2 also supports recursive CTEs but Derby does not.


Oracle DB what is the difference between ALL, USER and DBA Objects?

USER_OBJECTS are your objects, i.e. the objects where the current user is the owner
ALL_OBJECTS are all objects which you can access. These are of course your own objects from above plus objects from other schemas where access is granted to you.
DBA_OBJECTS are all objects in the database. As a DBA you have access to all objects, thus ALL_OBJECTS and DBA_OBJECTS should be the same if you are connected as DBA.

If you want to remove all objects from a user then the best command would be DROP USER xyz CASCADE;

Do not delete anything from XXX_OBJECTS view. Use DROP ... commands to removed those objects.


MySQL To Show Month Over Month Data By Year

You could wrap a new query around this one to allow you to aggregate the results:

SELECT Month, SUM(salesAmount) FROM (
    SELECT DATE_FORMAT(salesdate, "%m-%Y") AS Month, employeeName,    SUM(saleAmount)
    FROM  prevyear
    WHERE BDM = 'Jason Hernandez'
    GROUP BY DATE_FORMAT(salesdate, "%m-%Y"), employeeName
    UNION ALL
    SELECT DATE_FORMAT(salesdate, "%m-%Y") AS Month, employeeName, SUM(saleAmount)
    FROM  curryear
    WHERE BDM = 'Jason Hernandez'
    GROUP BY DATE_FORMAT(salesdate, "%m-%Y"), employeeName
) previousQuery
GROUP BY Month

Ideally I'd build the query to do this in one go rather than turning your query into a subquery but it will work. Here's an example that shows it working.


(Beginner) MYSQL: What can I do about this where statement?

SELECT tableA.*
FROM tableA
JOIN ( SELECT DISTINCT column1
       FROM tempTable ) temp ON tableA.column1 <=> temp.column1

<=> is null-safe compare operator. It assumes that NULL is equal to NULL whereas regular comparing operator = considers them unequal.
Subquery is used for to collapse equal values in tempTable - if not then each value from tableA may be selected a lot of times.


Crash of VMWare Workstation when generating EER Diagram

I found a way to prevent this crash.

In MySQL Workbench, go to Edit > Preferences...

Go to the Modeling section and tick "Force use of software based rendering for EER diagrams"

Restart MySQL Workbench.


Log in as another SQL Server user via SSMS to check their permissions?

You can run SSMS as a different user via runas /netonly as described here.

However for that method you would need to know the other user's password.

You can also use EXECUTE AS to impersonate that user from inside a query window logged in as you (as long as you have the required permissions to do so).

EXECUTE AS 'user_name';

/* Some SQL Statements */

REVERT;

Or you could adopt a policy of never granting permissions to users directly but always granting them to Windows groups or database roles. Then you just need to have another dummy user that you can grant the same role membership to.


Restoring plain-text pg_dump with psql and --disable-triggers

@dezso had the completely right idea:

  All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data

The only thing left now was to make it happen.

So here's what I did. I took a leaf out of his book and manually edited the dump-file to use a table named table_backup. Then I created said table using the definition provided in my pgAdmin (but it can be done manually, too).

I left out triggers and constraints, as well as Foreign Keys, and then proceeded to "updating" the original table with the data from the backup table like the following:

BEGIN TRANSACTION;
ALTER TABLE table DISABLE TRIGGER ALL;

UPDATE table SET
    (column1, column2, ...) =
    (table_backup.column1, table_backup.colum2, ...)
FROM table_backup WHERE table.pk_column = table_backup.pk_column;

ALTER TABLE table ENABLE TRIGGER ALL;
-- I didn't but you can drop table_backup here
COMMIT;

So I am finally back with my original data, ready for the next testrun ;)


Can InnoDB use a clustered index as a covering index?

How indexes work in InnoDB.  This discussion is limited to InnoDB.

(Let me establish some ground work, then I will get to your question.)

The PRIMARY KEY is a BTree where the leaf nodes contain all the columns of the row.  That is, the PK and the data coexist in the same BTree.

Each secondary key is a BTree where the leaf nodes contain a copy of the PRIMARY KEY.  Hence, a second lookup is needed to actually get to the rest of the columns.

DELETEing a row requires removing a 'row' from each BTree.

UPDATEing a column that is not in any index requires (1) locating the record (in the data BTree), and (2) modifying the record.  (Plus lots of Multi Version Concurrency Control stuff.)

UPDATEing a column that is in some index(s) requires (logically) deleting the record from each index where the column was part of that index and inserting a row in another place in that BTree.

SELECTing a row via the PK (WHERE Id = ...) usually requires drilling down the BTree to the leaf node.  (You have presented a rare exception.)

SELECTing a row via a secondary key (WHERE other = ...) requires two steps: (1) drill down the secondary BTree to the leaf node, fetching the PK columns, and then maybe (2) drill down the PK BTree to get to the record.

The "maybe" depends on whether or not all the needed columns are found in the secondary index (keep in mind that that includes the columns of the PK).  If everything is found in the secondary BTree, it is called a "covering index" for that select.

An optimization is to add extra non-TEXT, non-BLOB columns to a non-UNIQUE index to make it "covering".  (There are diminishing returns if the columns you want to add are big, or there are "too many".  I suggest no more than 5 columns in an index, but there are exceptions.)

The next-to-bottom node in a BTree contains only the boundaries of the leaf node.  That is, for SELECT 1 FROM table WHERE id = 345, the "345" probably exists only in the leaf node.

The only case I know of for not going all the way to the leaf node is when ANALYZE TABLE is gathering statistics.

The + in B+Tree refers to the leaf pages being linked together, thereby making "range" scans (in table or in index) slightly more efficient.  A hundred (or so) rows are found in one leaf node block, then the link gets it to the next block for the next hundred.  Otherwise, it would have to come "back down" the tree to find the 'next' block.  An InnoDB 'block' is 16KB.

Back to your key question.  Why not optimize the case where 345 can be seen in a non-leaf node?  Because it is such a rare query that it does not warrant extra code, extra code to break, and might not average out as being better.  Some issues:

Only 1% (or so) of keys would be sped up.
The block is likely to be in cache already.
The block is likely to be needed -- Note that your example requires SELECT 1, which is a rather rare subset of selects.  It would take extra effort to discover this special case.
If the row needs to be locked (for MVCC, etc), then it needs to get to the leaf node.  This probably requires that the leaf node be fetched.

A Rule of Thumb of mine:  If some optimization is not likely to save 10% (by some metric), I should look elsewhere to improvements.


MySQL Slave replication: Need to determine where replication left off to start slave in correct location

The Primary thing you need to do was left in a comment:

  Follow the suggestion in Duplicate entry in MySQL slave:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

However, here is how you can start off at the better position with the CHANGE MASTER TO command.

Let's take a look at a sample SHOW SLAVE STATUS\G

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.4.16.245
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001527
          Read_Master_Log_Pos: 554619670
               Relay_Log_File: relay-bin.004561
                Relay_Log_Pos: 554619815
        Relay_Master_Log_File: mysql-bin.001527
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: phpmyadmin
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 554619670
              Relay_Log_Space: 554620007
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

Please take note of Relay_Master_Log_File and Exec_Master_Log_Pos. These represent the log file and the position of the last SQL statement that was completed on the Master that is next in line to be executed on the Slave.

So, the CHANGE MASTER TO in this case would be:

CHANGE MASTER TO master_log_file='mysql-bin.001527',master_log_pos=554619670;

For your particular case, here is what you need to do on the Slave Server:

Step 01) Start mysql up with replication disabled at startup

$ service mysql restart --skip-slave-start

Step 02) Login to mysql and show the slave status:

mysql> SHOW SLAVE STATUS\G

Step 03) Get Relay_Master_Log_File and Exec_Master_Log_Pos from SHOW SLAVE STATUS\G

Step 04) Run CHANGE MASTER TO command using Relay_Master_Log_File and Exec_Master_Log_Pos

This will erase any relay logs collected and start collecting with a fresh, empty relay log.

Step 05) mysql> START SLAVE;

Step 06) mysql> SHOW SLAVE STATUS\G repeatedly to watch Seconds_Behind_Master go to 0

Step 07) If replication breaks due to Error 1062 (Duplicate Key), now you can implement @DTest's suggestion. Then, goto Step 06. Repeat this until Seconds_Behind_Master go to 0.

  We stopped at log 'mysql1-bin.000362' position 3384732.

That's what you use:

CHANGE MASTER TO master_log_file='mysql1-bin.000362'. master_log_pos=3384732;

If you ran RESET SLAVE on the Slave, do not despair. Just run the full syntax version of the CHANGE MASTER TO command:

CHANGE MASTER TO
MASTER_HOST='IP Address of Master',
MASTER_PORT=3306,
MASTER_USER='Replication Username',
MASTER_PASSWORD='Replication Password',
MASTER_LOG_FILE='mysql1-bin.000362',
MASTER_LOG_POS=3384732;


The SQLServer version of this could look like this.

First we want the drug table and some values

create table #drugs (drug nvarchar(10),date_1 date, date_2 date, seq_id int)
 insert into #drugs values ('sildenafil',NULL,NULL,1)
                          ,('sildenafil',NULL,NULL,2)
                          ,('sildenafil',NULL,NULL,3)
                          ,('tadalafil',NULL,NULL,1)
                          ,('tadalafil',NULL,NULL,2)
                          ,('tadalafil',NULL,NULL,3)
                          ,('vardenafil',NULL,NULL,1)
                          ,('vardenafil',NULL,NULL,2)
                          ,('vardenafil',NULL,NULL,3)

Then we need to update the next lowest seq_id with a date

update #drugs
  set date_1 = getdate()
   where seq_id =
   (select min(seq_id)+1
    from #drugs d2
    where #drugs.drug = d2.drug)

And the update the lowest date_2 value with the date_1 value from the second lowest seq_id - This is update with a join with the strange looking SQL-Server syntax and is

update D1
  set D1.date_2 = d2.date_1
  From #drugs D1
   inner join
    (select drug,date_1,seq_id
     from #drugs d1
     where seq_id = (select min(seq_id)+1 from #drugs)
    ) as d2
     on d1.drug = d2.drug and d1.seq_id = d2.seq_id-1

And Clean up

Drop Table #drugs


Transaction Log and mirroring - looking for dumbest explanation possible

The transaction log is a important method to restore your database in a specific time. If you have a large database > 500 GB and if you must restore your database from a full backup, this will cost very much time. Also if you full backup your database everytime, think about how long this backup could take.

A very easy concept for SQL Server can be:
Set Recover Model of your Database Full

Create a Maintenance Plan(1) in SQL Server:

Do FullBackup every Week maybe in D:\yourbackup\FullDBBackup.bak
Do Differential Backup every two days in D:\yourbackup\DiffBackup.bak
Do every 2 houers Backup your Transaction log in D:\Yourbackup\Tranlogbackup.trn

Create a Maintenance Plan(2) in SQL Server:

Delete all older Files 8 days from D:\yourBackup*.bak
Delete all older Files 3 Days from D:\yourBackup*.trn

In this case, you are able to recover your Database in a specifc time, very fast very easy.
SQL Server will automaticly manage your "Backup" files, older files will be deleted after your specific time range.

I would suggest that you read about SQL Server Transaction Log here:

http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

For using the Maintenance Plans in SQL Server just ask BING / google :D

you should build a small test db and test this before you go in production


MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows

What are we doing wrong? Is there a table-level lock even if using InnoDB?

Your problem is the lack of a proper index to use. InnoDB does next-key locking, meaning it will lock only the rows that it will update, but also the gaps in-between using the look up index. Because no proper index can be used for the given filter gate LIKE '%[1]%' it doesn't technically do a table lock, but it setups a lock on every single row gap according to the query plan (locking all rows).

I've recreated your structure, and SHOW ENGINE INNODB STATUS gives us all the information we need:

mysql> create table lpr (gate_id int, gate varchar(10));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into lpr values (1, 'wqer[1]sd');
Query OK, 1 row affected (0.11 sec)

mysql> insert into lpr values (2, 'wqer[2]sd');
Query OK, 1 row affected (0.06 sec)

mysql> insert into lpr values (3, 'wqer[2]sd');
Query OK, 1 row affected (0.03 sec)

mysql> insert into lpr values (4, 'wqer[4]sd');
Query OK, 1 row affected (0.05 sec)

mysql> insert into lpr values (5, 'wqer[5]sd');
Query OK, 1 row affected (0.09 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SHOW ENGINE INNODB STATUS\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4126, ACTIVE 8 sec
2 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 8, OS thread handle 140628280551168, query id 21 localhost root starting
SHOW ENGINE INNODB STATUS

On another session:

mysql> UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
    -> ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 4127, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 9, OS thread handle 140628236515072, query id 28 localhost root updating
UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `enwiki`.`lpr` trx id 4127 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000001011; asc       ;;
 2: len 7; hex 82000001060110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 9; hex 777165725b315d7364; asc wqer[1]sd;;

You have several options:

Increase your concurrency by relaxing your transaction isolation level (but ghost reads can happen):

mysql> SET SESSION transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

On another session, now the session succeeds:

mysql> SET SESSION transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
-> ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Improve your design and/or queries so you only use properly indexed queries for improved concurrency.

More about concurrency and gap locking: https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/


Generate table of all databases and schema with a single psql command

In PostgreSQL is it possible to generate a list of all databases and
  all schema with a single psql command?

No. The list of schemas of a database is private to that database and cannot be obtained without being connected to it.
The list of databases is available with \l or select * from pg_database.


An alternative way to identify the islands is using the LAG analytic function and the SUM() window aggregate function:

WITH
  sentinels AS
  (
    SELECT
      DateStamp,
      Success,
      Error,
      Grp = CASE
              WHEN Success = LAG(Success) OVER (ORDER BY DateStamp ASC)
               AND Error   = LAG(Error  ) OVER (ORDER BY DateStamp ASC)
              THEN 0
              ELSE 1
            END
    FROM
      dbo.myTable
  ),
  fullyMarked AS
  (
    SELECT
      DateStamp,
      Success,
      Error,
      Grp = SUM(Grp) OVER (ORDER BY DateStamp ASC ROWS UNBOUNDED PRECEDING)
    FROM
      sentinels
  )
SELECT
  Starting  = MIN(DateStamp),
  Ending    = MAX(DateStamp),
  Polls     = COUNT(*),
  Success,
  Error
FROM
  fullyMarked
GROUP BY
  Success,
  Error,
  Grp
ORDER BY
  Starting ASC
;

The sentinels CTE marks the first row of each island with a 1 and the others with a 0:

RowID  DateStamp          Success  Error                Grp
-----  -----------------  -------  -------------------  ---
1001   5/24/2019 11:23am  1        None                 1
1004   5/24/2019 11:24am  1        None                 0
1005   5/24/2019 11:25am  1        None                 0
1009   5/24/2019 11:26am  0        SQL Timeout          1
1018   5/24/2019 11:27am  0        SQL Timeout          0
1019   5/24/2019 11:28am  1        None                 1
1026   5/24/2019 11:29am  1        None                 0
1035   5/24/2019 11:30am  0        Planned Maintenance  1
1100   5/24/2019 11:31am  0        Planned Maintenance  0
1111   5/24/2019 11:32am  1        None                 1

The fullyMarked CTE takes the output of sentinels and calculates the running total of the Grp column, thus effectively assigning a unique ID to each island:

RowID  DateStamp          Success  Error                Grp
-----  -----------------  -------  -------------------  ---
1001   5/24/2019 11:23am  1        None                 1    -- 1
1004   5/24/2019 11:24am  1        None                 1    -- 1+0
1005   5/24/2019 11:25am  1        None                 1    -- 1+0+0
1009   5/24/2019 11:26am  0        SQL Timeout          2    -- 1+0+0+1
1018   5/24/2019 11:27am  0        SQL Timeout          2    -- 1+0+0+1+0
1019   5/24/2019 11:28am  1        None                 3    -- 1+0+0+1+0+1
1026   5/24/2019 11:29am  1        None                 3    -- 1+0+0+1+0+1+0
1035   5/24/2019 11:30am  0        Planned Maintenance  4    -- 1+0+0+1+0+1+0+1
1100   5/24/2019 11:31am  0        Planned Maintenance  4    -- 1+0+0+1+0+1+0+1+0
1111   5/24/2019 11:32am  1        None                 5    -- 1+0+0+1+0+1+0+1+0+1

The final step is to just group by Success, Error, Grp and aggregate data of other columns as required, which gives you the expected result.

You can use this live demo at db<>fiddle.uk for playing with this solution.


What is the optimal solution for converting a database schema?

There comes a point when a step-by-step clean-up becomes more work than a clean slate and migrate approach. System availability and time to migrate may factor in to the decision when dealing with larger volumes but at this size, not an issue.

Key factors for me here are:

Renaming foreign key constraints to fit a new application framework.
Refactoring a significant proportion of existing tables.
Low volume of data.

In this situation I'd be very tempted to design a new schema that fits the model you now require and create the necessary scripts to migrate data across (your option 4).


SQL Server Alert Based On Severity Level Not Working

The SQL Agent may need to be restarted to pick up the configuration changes.


how can I divide log files for specific DB queries?

There is only a single PostgreSQL log file per database cluster, so you cannot have that out of the box.

Adding triggers that log to a file is an option, but a trigger (and the logging itself) can add significant performance overhead, certainly more than the PostgreSQL logging itself.

The route I would explore is this:

Try to perform the actions you want to log with a different database user. If that is not easy, be creative: perhaps you can execute SET ROLE/RESET ROLE commands at strategic points in debug mode to become a different user.
Enable logging for that user only:

ALTER ROLE myuser SET log_min_duration_statement = 0;

Then it will be easier to evaluate the log, because it will be smaller.


Oracle Database Copy Failed Using SQL Developer

The problem was caused by datafile has reached its max size though. I have resolved the problem by following the answer of this discussion
https://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-system-how-to-ext

Thank you everyone for the help.


How to Natively Return Output when using Invoke-Sqlcmd?

This code is based on your "monstrosity" example above.

At the start of each loop it clears the $error variable. Then if the Invoke-Sqlcmd generates an error the IF statement picks it up. It will then add the FullName ($sql) to the log file and then add the error on the next line.

$sqls = (Get-ChildItem -Path "I:\2019_06_24\_SQL" -Filter *.sql |
    Sort-Object -Property CreationTime).FullName
foreach ($sql in $sqls)
{
$error.clear()
Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true
if ($error -ne $null)
    {
    $sql + '
' + $error | Out-File -FilePath "C:\Temp\ErrorLog.txt" -Append
    }
}

As it is, the ErrorLog.txt will look something similar to this:

I:\2019_06_24\_SQL\Test_File_5.sql
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 30, Procedure , Line 3.
I:\2019_06_24\_SQL\Test_File_303.sql
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 30, Procedure , Line 3.


Set column values based on condition statement

The syntax for case is not correct and the datatype is Boolean, so you must set it as True or False

CREATE TABLE smartcardtable ("ID" INT, "board_stage" int,"alight_stage" int )

INSERT INTO smartcardtable VALUES (1,1,2)

ALTER TABLE smartcardtable
ADD service_direction boolean;

UPDATE smartcardtable
SET service_direction =
CASE WHEN board_stage < alight_stage THEN  TRuE
ELSE FALSE END;

1 rows affected

SELECT * FROM smartcardtable

ID | board_stage | alight_stage | service_direction
-: | ----------: | -----------: | :----------------
 1 |           1 |            2 | t

db<>fiddle here


When should a primary key be meaningful?

under what circumstances, if ever, is preferable to use a primary key with some other real meaning?  (emphasis added)

Given that the focus of this question is "preferable" and not "acceptable", and accepting that this is still a highly subjective topic, I will say that I cannot think of a situation where it is best for the system to have a truly natural key for a variety of reasons (most of which has been said before in other answers that Paul linked to in a comment on the question):

What is thought to be unique is not always unique (e.g. Social Security Numbers / SSNs in the U.S.)
Sometimes things change, either in value or uniqueness (we don't control the external world)
Even when something should be "stable" in value and uniqueness (e.g. SKU, perhaps), can the incoming value be guaranteed to be correct? Humans often mistype stuff doing data entry. There are also bugs in export processes that might cause values in a file imported by your system to be incorrect. There are also bugs in other systems that feed data into yours that can allow for the data itself to not be entirely correct, even if their export process worked correctly.

I emphasize "truly" because there are two situations where I prefer to not have a new surrogate key:

Bridge Tables

(or whatever you like to call tables used only, or mainly, to represent many-to-many relationships)

Thing                          ThingXTag                Tag
------                         ---------                ---
ThingID INT AutoMagic PK --->  ThingID INT PK, FK
Stuff   SomeType               TagID   INT PK, FK  <--- TagID   INT AutoMagic PK
                                                        TagName VARCHAR

When modeling a bridge table (a table which doesn't exist in the logical model but is needed in the physical model), the PK there should be the existing Primary Key columns of the tables being related via this table. This allows for enforcing the proper uniqueness and non-NULL-ness of the values without needing a separate unique index / constraint. In the rare case of needing to foreign key to this relationship, it will:

be meaningful in that each of the key columns will actually point back to the original source tables without needing yet another join, and
prevent someone or something from updating the key columns that form the relationship between the two primary tables without updating the value being linked to via a foreign key.

WackyTable                           ThingXTag
----------                           ---------
WackyTableID INT AutoMagic PK
ThingID      INT FK            --->  ThingID     INT PK, FK (to Thing.ThingID)
TagID        INT FK            --->  TagID       INT PK, FK (to Tag.TagID)
AttributeX   VARCHAR                 InsertDate  DATETIME
InsertDate   DATETIME

I have worked on a system where these bridge tables had their own auto-incrementing, surrogate key PKs, and the single column surrogate key of the bridge table was referenced in other tables via FK:

WackyTable                           ThingXTag
----------                           ---------
WackyTableID INT AutoMagic PK
ThingXTagID  INT FK            --->  ThingXTagID INT AutoMagic PK
AttributeX   VARCHAR                 ThingID     INT FK (to Thing.ThingID)
InsertDate   DATETIME                TagID       INT FK (to Tag.TagID)
                                     InsertDate  DATETIME

It was a horrible, confusing mess that we wasted way too much time on for debugging, etc.

Sibling Tables

These are tables that are truly a single entity and thus have a 1-to-1 relationship. They are only split into two (or more, as is needed) tables for performance reasons. I have done this with tables having 1 million (or more) rows where it was either very wide, or if it was moderately wide and there were columns that were either not used very frequently or were string of over 50 bytes. Stuff like that. This keeps the core properties of the entity in a narrower table that fits more rows on each data page.

In these cases, the "sibling" table is at the exact same level as the initial table and should have the same PK. There is no use in giving it an auto-incrementing surrogate key since each row has what amounts to a natural key from the initial table.

Product                            ProductProperty
-------                            ---------------
ProductID  INT AutoMagic PK  --->  ProductID        INT PK, FK (to Product.ProductID)
Name       VARCHAR                 ShortDescription VARCHAR
SKU        VARCHAR                 SomethingElse    SomeType
Quantity   INT                     UpdateDate       DATETIME
CreateDate DATETIME
UpdateDate DATETIME

To be clear, I am speaking in terms of the physical model, not the conceptual model. I assumed that the focus of this question was the physical model since it is framed in the context of issues that do not exist conceptually: surrogate keys, issues dealing with usage of the primary key value, etc. With that in mind, I was not implying that natural keys should not be stored and used for identification. On the contrary, natural keys are great "alternate keys" and should have unique constraints / indexes placed on them. However, the idealism of the conceptual model does not always translate directly to the physical model. Data integrity (i.e. the stability and reliability of the data model) is a top, if not the top, priority of the physical model. And so practical considerations, such as using surrogate keys, must be made to ensure that this goal is met and not compromised. Meaning, if you have SSNs or SKUs, etc, then absolutely store them in a column that has a unique constraint on it, and have the system do a lookup on that value since auto-generated numbers shouldn't be used externally anyway. Users don't need to know the auto-generated ID number of a record: they should pass in a value that they do know (e.g. email address as a lookup for UserID / CustomerID, flight confirmation codes in combination with flight dates, etc) and the system should translate that into the auto-generated value that it uses from that point forward.

Yes, the problems noted at the beginning of this answer are still potential issues when using natural keys as alternate keys. However, the difference is that the problem is isolated to (usually) just one table. If someone made a mistake and created a unique index on "flight locator", it might be a while before they get a violation. But once they do, it is easy enough to drop that unique index and recreate it to include the flight dates as well. Or, if you change your email address (often used as the Login) on a system and get an error because it was in use by someone else years ago (legitimately), that can most likely be handled by support without any impact / risk to existing related records. In both cases the rest of the data model is untouched for the necessary changes.

Again, this is a pragmatic approach to:

minimizing potential data loss that can happen when migrating a primary key that has been referenced by one or more foreign keys (and minimizing the scope of the project also reduces the maintenance window :-). While not all PKs (or Unique Constraints/Indexes) have FKs, using a surrogate should certainly reduce the number of columns that have such dependencies
making the system as durable, resilient, and efficient as possible. The physical model is the "in practice" to the "in theory" of the conceptual model. And we already make quite a few adjustments and considerations given that the conceptual model doesn't care about implementation. Yet we make choices to utilize vendor-specific features, for performance (e.g. denormalization, lookup tables, "sibling" tables as noted above, etc), to create the "bridge" tables (as noted above), and so on.

I don't know how many systems used SSNs (Social Security Numbers in the U.S.) as PKs, but for any that did, some of them (many perhaps) may have avoided issues with them not being as unique as they should have been. But, none of those systems were able to avoid changes over the years regarding the need to handle them much more securely. Systems treating SSNs as an alternate key require very little development time to switch over to encrypting those values, and the system requires little downtime (or none) to make the changes at the data layer. Given that we all have backlogs of projects that we will likely never get to, businesses tend to prefer that these annoying yet inevitable changes will cost them 5 hours instead of 20 - 40 (don't forget that changes need to be tested, and hence the scope of the changes also directly impacts how much QA time is needed for the project).

To be explicit, there are some scenarios where it is "acceptable" to have natural keys, though I don't think I would go so far as to say "preferable".

State / Region and Country codes: If you use codes maintained by the International Organization for Standardization (ISO) (e.g. "US" = United States, "FL" = Florida, etc) then these are probably reliable enough to use, and I have used them as they are short (i.e. not bad for performance) and human-readable. These codes are also used often-enough in a variety of other ways, even outside of computer systems, that people have a general familiarity with them, even ones that don't initially make as much sense to some folks (e.g. "DE" = Germany might not be intuitively obvious to those who are unaware that "Germany" == "Deutschland" in German).
Internal lookup value codes: You can't control external sources, but (hopefully) you are in control of your own system. If your system has department codes, status codes, etc that are used internally, then it should be fine to come up with short codes for them (2 - 4 bytes). At 4 bytes it would be using the same amount of space as an INT and if using a binary Collation (one ending in _BIN2, or even _BIN, but _BIN2 is preferred) then it should compare just as fast. Having relatively meaningful values for such codes can make support / debugging easier. However, you still get into the situation where over time, department names, etc might change and the codes might not be meaningful anymore.


This is the one I would use, courtesy of Kimberly Tripp:

SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

It splits out the plan size and total number of plans by Type e.g. Adhoc, Prepared, Procedure, Trigger, View etc.

EDIT:

If you want to then find out the actual pressure each type is placing on the server, you can join the sys.dm_exec_cached_plans to sys.dm_exec_query_stats through the plan_handle and order by total_worker_time (for CPU) or total_elapsed_time (for Duration).


List the CMS SQL Server Instances using Powershell

First, get your server list query returning what you want, bellow is an example query to run against your CMS.

SELECT DISTINCT [server_name]
FROM [msdb].[dbo].sysmanagement_shared_registered_servers_internal
ORDER BY [server_name]

Next the PowerShell, we use a loop that pings the server first to verify it is available then checks the SQL connection. If everything is good it will execute the requested code. The below executes a SQL query but anything could be put in that block. This script runs under the current windows user when connecting to the SQL Instances.

#Import the SQLPS module to use the Invoke-SQLCmd
Import-Module -Name SQLPS -DisableNameChecking -Force

#Create a variable to hold your SQL query for the server list
$ServerListQuery = @"
SELECT DISTINCT [server_name]
FROM [msdb].[dbo].sysmanagement_shared_registered_servers_internal
ORDER BY [server_name]
"@

#Create a variable to hold the query or queries you want to execute against each SQL Server
$SQLServerVersionQuery=@"
SELECT GetDate() AS DateTimeStamp,
CAST(SERVERPROPERTY('ServerName')AS sysname)  AS ServerName,
@@version AS SQLServerVersion
"@

#Create your list and place it in a variable using Invoke-Sqlcmd
#Invoke-Sqlcmd -ServerInstance <TheNameOfYourCMSServer> -Database msdb (because that is where CMS is stored) -Query $ServerListQuery
$list = Invoke-Sqlcmd -ServerInstance MyCMSServer -Database msdb -Query $ServerListQuery

#Loop through the list and run your query
ForEach ($databaseinstance in $list){

#remove named instance if it exists for the ping test
$servername = $databaseinstance[0].split("\") | Select-Object -Index 0
IF(Test-Connection -ComputerName $servername -quiet) {
TRY{

#Run the query variable created at the top $SQLServerVersionQuery
Invoke-Sqlcmd -ServerInstance $databaseinstance[0] -Query $SQLServerVersionQuery -QueryTimeout 300 |
Select-Object -Property DateTimeStamp ,ServerName, SQLServerVersion

}
#Catch the error if SQL connectino doesn't work
CATCH {Write-Output "SQL Connect Error on: $databaseinstance[0] >> $Error[0]"}
}
#Catch the error if the ping test fails
ELSE {Write-Output "Server Ping Error: $servername >> $Error[0]"}
}


What is meant by "nonclustered legacy instance of SQL Server"?

non-clustered just means it's not part of a Failover Cluster Instance ("FCI" -- which is fundamentally different from a Windows Server Failover Cluster ("WSFC")). An FCI requires a WSFC, but the opposite is not true. A non-FCI instance of SQL Server is typically called a standalone instance. A standalone instance can be installed on a WSFC (and take part, or not, in Availability Groups). You can see how they work together here.

legacy is not a special word here, it is just an adjective that means older. There is no more meaning to "legacy instance" than there is to "ancient instance" or "deprecated instance" or "no longer supported instance." In this context 2005, 2008, and 2008 R2 are mentioned explicitly, but 2012 would be in that bucket as well.

The doc portion you reference just means you can't convert a standalone instance of SQL Server to a clustered instance as a part of an upgrade.


How to enable the Federated/FederatedX storage engine on MariaDB 5.5?

FederatedX is enabled by default in MariaDB 5.5


WHERE statement on an inner join with GROUP BY

Can you just put LINE.SALESSTATUS in (3,4) in inner part of subquery like below:

 SELECT
  LINE.ITEMID
  ,LINE.CUSTACCOUNT
  ,LINE.QTYORDERED
FROM dbo.SALESLINE AS LINE
INNER JOIN dbo.SALESTABLE AS STABLE ON LINE.SALESID = STABLE.SALESID AND LINE.DATAAREAID = STABLE.DATAAREAID
where  LINE.SALESSTATUS in (3,4)
GROUP BY LINE.CUSTACCOUNT,LINE.QTYORDERED,LINE.ITEMID
HAVING COUNT(*) > 1

may be it helps you to get filtered record sets inside the inner section.


pg_restore: [archiver] did not find magic string in file header

You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default,  plain format.

From pg_dump manpage:

  -F format, --format=format
       Selects the format of the output.  format can be one of the
       following:

       p, plain
           Output a plain-text SQL script file (the default).

A dump in plain format should be fed directly to the psql command-line tool, pg_restore doesn't know what it is, which is the reason of this error message: did not find magic string in file header.

You can look directly at the dump file with more out.sql in shell and you'll see readable SQL commands. Restore it with psql -f out.sql [other options]. You will probably want to create the target database first, as the --create option is not present in the pg_dump invocation.

On the other hand, you may reinvoke the dump adding --format=c to its options. Then that would be the opposite: pg_restore must be used to interpret a dump file in the custom format.


Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

10 million records is nothing to MySQL.
Database design is VERY importmant - INDEX the field(s) that will be searched.
Don't index every field.
When you get into the billions of records then look into Partitioning tables.
RAM, more RAM, and just to be sure add more RAM. MySQL loves RAM.
For best speeds, make sure the MySQL server is a dedicated box - NOT a VM.
I would lean more towards Linux for the OS.
Hire a database admin.


The audit table does not 'clean' itself.  And there is not just a simple setting, like an init parameter, to do so.  You have to set up and schedule jobs to do that, and @eckes gave you a link to the documentation.  That same package (dbms_audit_mgmt) also provides a procedure for moving the audit table to another tablespace, getting it out of SYSTEM.  I strongly recommend you do so.  I just create a new tablespace dedicated to the audit table(s) and their indexes.


calculate date difference in monthwise in sqlserver

With the help of a mini calendar table, this can be handled pretty easily:

rextester setup: http://rextester.com/ZHTTZ86931

Testing data:

create table tmp (fromdate date,thrudate date,monthdiff decimal(19,7),formula varchar(64))
insert into tmp (fromdate, thrudate, monthdiff, formula) values
 ('2016-02-26','2016-04-03',1.2379310,'(4.00/29.00)+(31.00/31.00)+(3.00/30.00)')
,('2016-03-03','2016-03-05',0.0967741,'3.00/31.00 (daycount/lastday of the month)')
,('2016-03-05','2016-06-05',3.0376343
                         ,'(27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00)')
,('2016-01-01','2016-12-31',12,'');

Creating a mini calendar table to track each date and its percent of its month.

  /* creating a DayPercentOfMonth table */
declare @FromDate date = '20100101';
declare @ThruDate date = '20301231';
create table dbo.DayPercentOfMonth (
    [Date] date not null primary key
  , DayPercentOfMonth decimal(9,8) not null
);
with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, cal as (
  select
    Date =convert(date,dateadd(day, row_number() over (order by (select 1)) -1, @fromdate))
    , [DayPercentOfMonth] = convert(decimal(6,4),1)
                           /convert(decimal(6,4)
           ,datepart(day,dateadd(day,-1,dateadd(Month,datediff(Month,0
           ,row_number() over (order by (select 1)) -1)+1
           ,0))))
    from         n as deka
      cross join n as hecto
      cross join n as kilo     /* 2.73 years */
      cross join n as [tenK]    /* 27.3 years */
      --cross join n as [hundredk] /* 273  years */
    )
insert into dbo.DayPercentOfMonth ([Date],[DayPercentOfMonth])
select [Date],DayPercentOfMonth
from cal;

Alternate version if you really do not want to create the helper table, just uses the recursive cte:

/* set from & thru dates for calendar cte */
declare @FromDate date = '20160101';
declare @ThruDate date = '20161231';

with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, cal as (
  select
    Date =convert(date,dateadd(day, row_number() over (order by (select 1)) -1, @fromdate))
    , [DayPercentOfMonth] = convert(decimal(6,4),1)
                           /convert(decimal(6,4)
           ,datepart(day,dateadd(day,-1,dateadd(Month,datediff(Month,0
           ,row_number() over (order by (select 1)) -1)+1
           ,0))))
    from         n as deka
      cross join n as hecto
      cross join n as kilo     /* 2.73 years */
      cross join n as [tenK]    /* 27.3 years */
      --cross join n as [hundredk] /* 273  years */
    )

Then the query just joins the calendar where the calendar date is between the from and thru dates, and sums the DayPercentOfMonth column.

select
    fromdate=convert(varchar(10),t.fromdate,120)
  , thrudate=convert(varchar(10),t.thrudate,120)
  , monthdiff=format(t.monthdiff ,'f8','en-US')
  , SumDayPercentOfMonth=format(convert(decimal(19,8),sum(DayPercentOfMonth)),'f8','en-US')
  --select fromdate, thrudate, monthdiff, SumDayPercentOfMonth=sum(DayPercentOfMonth)
  from tmp as t
    inner join cal c on c.date >= t.fromdate
                    and c.date <= t.thrudate
  group by t.fromdate, t.thrudate, t.monthdiff, t.formula
  order by fromdate

results in:

+------------+------------+-------------+----------------------+
|  fromdate  |  thrudate  |  monthdiff  | SumDayPercentOfMonth |
+------------+------------+-------------+----------------------+
| 2016-01-01 | 2016-12-31 | 12.0000000  | 12.00000000          |
| 2016-02-26 | 2016-04-03 | 1.2379310   | 1.23793103           |
| 2016-03-03 | 2016-03-05 | 0.0967741   | 0.09677419           |
| 2016-03-05 | 2016-06-05 | 3.0376343   | 3.03763441           |
+------------+------------+-------------+----------------------+

note: Sample data values for output were not as precisely correct as they might appear to be.

(27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00) = 3.037634408602151 != 3.0376343
(3.00/31.00) = 0.0967741935483871 != 0.0967741 -- truncated


It's valid SQL as long as both sub-expressions, (birthday) and (registered=0) are boolean expressions (or can be implicitly converted to). And while registered = 0 is boolean, the birthday is probably not.

In MySQL though, integer expressions can be converted to boolean and vice versa, according to the rules: FALSE is 0, TRUE is 1 (and anything other than 0 is TRUE for the integer->boolean conversion).

So, that's what happens, an implicit conversion. For any birthday value other than 0, the expression is evaluated as:

(TRUE AND registered=0)

which is equivalent to just (registered=0).

In Access, if I remember right, -1 stands for TRUE (instead of 1), but anything other than 0 is also converted to true, so the observed behaviour would be the same.

The final use of the expression in the ORDER BY depends on how the DBMS sorts boolean values. I suppose that both (MySQL and Access) use FALSE < TRUE


How to run update query on multiple table via joins simultaneously on postgres?

I don't see why you would want to do that. Two UPDATE statements in a single transaction will do just fine and won't incur any additional overhead compared to a single statement.

Having said that: it is possible by using a writeable common table expression

with shape_update as (
  UPDATE prd_shape
   SET name_en = 'Another name'
  WHERE serial_number = '1234ST'
  returning id, serial_number
)
UPDATE prd_sectionshapename
  SET company_shape_name = 'ABC'
WHERE (shape_id, serial_number) IN (select id, serial_number from shape_update);

That solution indeed has one advantage: you have to specify the value for the serial_number only once. But it has no performance or transactional advantages.


PostgreSQL procedural languages overhead (plpython / plsql / pllua...)

UDFs in interpreted languages are pretty much always slower than UDFs written in C or built-in functions, all other things being the same.
Each language binding has different code to connect PostgreSQL to the language, with different degrees of optimisation, different ways of passing some data types, etc. So variation certainly exists. It shouldn't be huge unless you're passing a data type that gets very different handling by one language than another, e.g. one passes a hstore as a string, and another converts it to a dict.
Unclear what "the context" is. Can you use it for "real time data mapping" ... well, depends on what the function does and if it's fast enough on the server it's running on, for the clients it's taking to, and for your requirements. How long is a piece of string? Benchmark.
PL/PgSQL is simpler to write, and offers faster access to SQL. It's generally better when you need to wrap a little logic around a lot of SQL. It's very slow for mathematical operations and complex algorithms, so purely computational code in PL/PgSQL should be avoided whenever possible in favour of C, or a faster procedural language.

Speedups when re-implementing PL/PgSQL code in C can vary from neglible to over 1000 times. It all depends on what the code is actually doing.

(This kind of multi-question isn't well suited to Stack Exchange as it's harder to have a definitive answer)


Is there a benefit to using FOR SHARE in this PostgreSql simple select?

That depends on what else happens in the transaction.

With the for share there, nobody can update the selected get_offer_data row until the transaction commits or rolls back.

Without the context, it's really impossible to say what the original intended purpose of adding the lock there was.


How can I drop a Memory Optimized Table?

In addition to the restrictions pointed out in Evan's answer, there are a number of other things that can prevent dropping tables.  Most of them are similar to the restrictions on normal disk-based tables, and you should see them called out in your error message.

For instance, if I have an in-memory table referenced by a natively-compiled (schemabound) stored procedure, I'll get this error if I drop the table:

  Cannot DROP TABLE 'YourTable' because it is being referenced by object 'sp_YourStoredProc'.

The solution in that case is to drop the stored proc first, and then drop the table.

The error message you're getting indicates that there is a server or database level DDL trigger.  You'll need to disable or drop those triggers in order to drop the in-memory table.  You can find any such triggers by running these queries:

USE [master];
GO
SELECT * FROM sys.server_triggers;

USE [YourDatabaseName];
GO
SELECT * FROM sys.triggers;

You should look into what these triggers are doing (auditing, perhaps?).  If it's safe to do so, consider disabling them with the DISABLE TRIGGER statement, then dropping your table, then using ENABLE TRIGGER to turn the triggers back on.

Note that server and database-scoped DDL triggers are different from object level DML triggers (which you mentioned that you checked for and there are none).


Postgres denormalize tables into json objects for node

The answer was using json_agg instead of array_agg


INSERT/UPDATE in same query from previous SELECT with some singularities

Insert query:

INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type)
SELECT
    Id,
    CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))),
    username,
    firstname,
    lastname,
    email,
    'www.some_static_url.com',
    '1'
FROM veeva.user
WHERE Id = "00580000003UB5VAAW"

It will print Only first letter capital from both First & Last name.


Two ways to define may-have-a (1..0-1) relationship

You've got 2 conflicting design goals here. One is to design a good database. The other is to play nicely with OR mappers. You might be able to shift to a more consistent approach to mapping partial participation constraints, but you will continue to have design conflicts in the future.

One option would be to create a set of views to worm with your ORM, and so abstract the problem away.


Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

Seems like this is a feature of Postgres 11: https://www.postgresql.org/docs/current/release-11.html#id-1.11.6.6.4


The simplest structure would be a PERMISSIONS table like this:

create table PERMISSIONS
( Site_ID int
, User_ID int
, Role char(1)
, primary key (Site_ID, User_ID, Role) -- scenario 1
-- OR:
, primary key (Site_ID, User_ID) -- scenario 2
)

Use values for Role such as:

A = Administrator
P = Publisher
E = Editor

How many rows you need depends on how your code interprets the data.  You could have very simple code which interprets each permission strictly at face value.  To do an administrative task, the user would need an 'A' permission.  To do a publishing task, they would need a 'P' permission, etc.  In this case an administrator would need three records per site (one each of A, P, E).  Similarly publishers would need two records per site and editors only one.  There is a risk in this approach that data consistency errors could creep in, like someone could be a publisher, but not an editor (by virtue of the 'E' record being missing).

Alternatively, you could make your code more complex, in which case each user only needs one permission record per site.  In this scenario, administrative tasks require an 'A' permission, publishing requires an 'A' or a 'P' and editing requires 'A', 'P', or 'E'.  The advantage of this approach is that there is less data to maintain and you won't have inconsistencies.

Note too that you do not need to have the Users.Parent column.  If an administrator has an 'A' permission for a site, that automatically gives them control of all of the users for that site.


You need to use the special virtual table INSERTED which represents the rows that have been inserted causing the trigger to fire and JOIN it back to your Table2 using the PK
(ProjectID in this case I think)
Insert into TABLE2 (ProjectID, Status, Period)
SELECT
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1 T
INNER JOIN INSERTED I ON
  T.ProjectID = i.ProjectID
WHERE Status in (1, 2)


MySQL Replication: Insert generating VERY huge temporary tables

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!


Trigger with a Variable Select and Order by

You don't look at sys.tables for that... even if you got the last one created, on a busy system, how would you know that someone else didn't create one more recently? With TOP comes ORDER BY so the following would likely work most of the time, but it is a completely bad idea:

SELECT TOP (1) @table = name FROM sys.tables ORDER BY create_date DESC;

No, instead, you need to extract the table name from EVENTDATA():

CREATE TRIGGER CreateTable
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    DECLARE
      @sql       nvarchar(MAX),
      @EventData xml = EVENTDATA();

    ;WITH x(s,o)
    AS
    (
      SELECT
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'nvarchar(128)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'nvarchar(128)')
    )
    INSERT dbo.NewTable(ColumnName)
      SELECT QUOTENAME(s) + N'.' + QUOTENAME(o)
      FROM x;
END
GO


remove almost duplicates based on insert time

Here is something very quick and dirty

Get the minimum datetime for each personalid, status, ride_taken

SELECT B.* FROM
(SELECT personalid,status,ride_taken,MIN(swipe_time) swipe_time
FROM swipes GROUP BY personalid,status,ride_taken) A
INNER JOIN swipes B USING (personalid,status,ride_taken,swipe_time);

First let's load your sample data:

mysql> drop database if exists sdujan;
Query OK, 1 row affected (0.00 sec)

mysql> create database sdujan;
Query OK, 1 row affected (0.00 sec)

mysql> use sdujan;
Database changed
mysql> CREATE TABLE `swipes` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `personalid` varchar(20) NOT NULL,
    ->  `swipe_time` datetime NOT NULL,
    ->  `status` tinyint(1) NOT NULL COMMENT '1=registered,0=not',
    ->  `ride_taken` tinyint(1) NOT NULL DEFAULT '0',
    ->  `source` tinyint(4) NOT NULL DEFAULT '0',
    ->  PRIMARY KEY (`id`),
    ->  UNIQUE KEY `my_key` (`personalid`,`swipe_time`,`status`,`ride_taken`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into swipes values
    -> (1661,'C08877547','2012-10-21 01:12:08',1,1,3),
    -> (1662,'C09364782','2012-10-21 01:23:38',1,1,3),
    -> (1663,'C09364782','2012-10-21 01:23:48',1,1,3),
    -> (1664,'D09490557','2012-10-21 01:24:39',1,1,3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from swipes;
+------+------------+---------------------+--------+------------+--------+
| id   | personalid | swipe_time          | status | ride_taken | source |
+------+------------+---------------------+--------+------------+--------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 |
| 1663 | C09364782  | 2012-10-21 01:23:48 |      1 |          1 |      3 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 |
+------+------------+---------------------+--------+------------+--------+
4 rows in set (0.00 sec)

mysql>

Here is the output of my proposed query

mysql> SELECT B.* FROM
    -> (SELECT personalid,status,ride_taken,MIN(swipe_time) swipe_time
    -> FROM swipes GROUP BY personalid,status,ride_taken) A
    -> INNER JOIN swipes B USING (personalid,status,ride_taken,swipe_time);
+------+------------+---------------------+--------+------------+--------+
| id   | personalid | swipe_time          | status | ride_taken | source |
+------+------------+---------------------+--------+------------+--------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 |
+------+------------+---------------------+--------+------------+--------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

SUGGESTION #1

To accommodate the query better, add this index

ALTER TABLE swipes ADD UNIQUE KEY `my_key2`
(`personalid`,`status`,`ride_taken`,`swipe_time`);

SUGGESTION #2 (Optional)

To accommodate more than 255 swipe stations, perhaps source should be smallint unsigned

SUGGESTION #3 (Optional)

Some Transit Systems allow multiple swipes (up to 4 for PATH TransHudson) in one station. You could give some additional thought on this should you have to allow multiple swipes.

UPDATE 2012-10-24 17:30 EDT

If you are trying to limit within a time range, I have another viewpoint for you

Here is the subquery within my answer:

mysql> SELECT
    ->     personalid,status,ride_taken,MIN(swipe_time) swipe_time
    -> FROM
    ->     swipes GROUP BY personalid,status,ride_taken
    -> ;
+------------+--------+------------+---------------------+
| personalid | status | ride_taken | swipe_time          |
+------------+--------+------------+---------------------+
| C08877547  |      1 |          1 | 2012-10-21 01:12:08 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:38 |
| D09490557  |      1 |          1 | 2012-10-21 01:24:39 |
+------------+--------+------------+---------------------+
3 rows in set (0.00 sec)

mysql>

Let's say you want to limit to the Latest Entry Per 10 seconds. You will first have to generate a ten-second interval boundary like this:

mysql> SELECT personalid,status,ride_taken,swipe_time,
    -> (swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND) ten_sec_in
    -> FROM swipes;
+------------+--------+------------+---------------------+---------------------+
| personalid | status | ride_taken | swipe_time          | ten_sec_int         |
+------------+--------+------------+---------------------+---------------------+
| C08877547  |      1 |          1 | 2012-10-21 01:12:08 | 2012-10-21 01:12:00 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:38 | 2012-10-21 01:23:30 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:48 | 2012-10-21 01:23:40 |
| D09490557  |      1 |          1 | 2012-10-21 01:24:39 | 2012-10-21 01:24:30 |
+------------+--------+------------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

Perhaps the thing to do is introduce the ten_second boundary into the table

ALTER TABLE swipes ADD swipe_int DATETIME;
UPDATE swipes SET
swipe_int = swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND;
ALTER TABLE swipes ADD UNIQUE (personalid,swipe_int);

I ran this

mysql> ALTER TABLE swipes ADD swipe_int DATETIME;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> UPDATE swipes SET
    -> swipe_int = swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> ALTER TABLE swipes ADD UNIQUE personalid_swipe_int_ndx (personalid,swipe_int);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from swipes;
+------+------------+---------------------+--------+------------+--------+---------------------+
| id   | personalid | swipe_time          | status | ride_taken | source | swipe_int           |
+------+------------+---------------------+--------+------------+--------+---------------------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 | 2012-10-21 01:12:00 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 | 2012-10-21 01:23:30 |
| 1663 | C09364782  | 2012-10-21 01:23:48 |      1 |          1 |      3 | 2012-10-21 01:23:40 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 | 2012-10-21 01:24:30 |
+------+------------+---------------------+--------+------------+--------+---------------------+
4 rows in set (0.00 sec)

mysql> show create table swipes\G
*************************** 1. row ***************************
       Table: swipes
Create Table: CREATE TABLE `swipes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `personalid` varchar(20) NOT NULL,
  `swipe_time` datetime NOT NULL,
  `status` tinyint(1) NOT NULL COMMENT '1=registered,0=not',
  `ride_taken` tinyint(1) NOT NULL DEFAULT '0',
  `source` tinyint(4) NOT NULL DEFAULT '0',
  `swipe_int` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `my_key` (`personalid`,`swipe_time`,`status`,`ride_taken`),
  UNIQUE KEY `personalid_swipe_int_ndx` (`personalid`,`swipe_int`)
) ENGINE=MyISAM AUTO_INCREMENT=1665 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql>

Once you do that, going forward, any INSERT within the same 10-second interval gets immediately rejected. That way, you do not have to code it. It will be part of the table design.

Naturally, you will need a trigger to populate swipe_int. Here is the trigger:

mysql> DELIMITER $$
mysql> CREATE TRIGGER swipes_bi BEFORE INSERT ON swipes
    -> FOR EACH ROW
    -> BEGIN
    ->     SET NEW.swipe_int = (NEW.swipe_time -
    ->     INTERVAL MOD(UNIX_TIMESTAMP(NEW.swipe_time),10) SECOND);
    -> END $$
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;
mysql>


Can I have cross database or cross server references through a relative path?

Assuming that, post migration, you have absolutely no other need for this Linked Server to Server 1, then you should be able to drop and recreate it with the same name but pointing to the current / local instance (i.e. Server 2). This is known as a "loop back" and will allow the code to continue working unmodified.

HOWEVER, that is a short-term solution as you don't want to go through a Linked Server if you don't need to. You should go through all of the code and remove the [Server1]. references. This loop back Linked Server is just a means of buying time to do these changes slowly rather than needing to have it all done up front.