Help on improving the query performance of MySQL table

You can try creating one single fully covering index, based on the queries you provided, on the fields timestamp, type, status, bookname. This should be applicable to each of the three queries above, but knowing if it's ideal or the best index would be dependent on your table's data and testing it. As Andrew points out, depending on how large the timestamp ranges are, and more so meaning depending on how many rows the typical range returns, will affect the selectivity of indexing on that column. But after you create the index, you should be able to test and run an EXPLAIN and ANALYZE and see if it's using the index appropriately.
Note for a 200 million row table, it might take a little bit to create the index, and you might want to do it during a maintenance window, off-hours. Also you should look to use proper data types and clean up your table when possible as well, for example timestamp should be stored in a timestamp type column, and does the status column really need up to 1,000 characters when there's only 6 distinct values for status?...these minor things could also affect performance in minor ways, and definitely affect maintainability, but this is more of a sidenote.


Cosmos DB ODBC driver installation problem

Try installing Microsoft Visual C++ 2012, 2013 and 2015. We had the exact same issue with Cosmos ODBC Drivers and this resolved it in Windows 2012 R2 server.


How to design this database to avoid cyclic dependency?

Instead of searching for tips and tricks (deferred constraints included) I would suggest that you simply design your way out of this "reference lock" -- so try something like this:

Facts

User(UserID) exists.
Address(AddressID) was created by User(UserID).
Address(AddressID) was created on Date(DateCreated).
Address(AddressID) was last modified by User(UserID) on Date(ModifiedOn).
User(UserID) resides at Address(AddressID) since Date(ValidFrom).

Constraints

Each Address was created by exactly one User. It is possible that more than one Address was created by the same User.
Each Address was created on exactly one Date. It is possible that more than one Address was created on the same Date.
For each Address and Date, that Address was modified by at most one User on that Date.
For each User and Date, that User resides at most one Address since that Date.

Logical

As far as mandatory address is concerned, verify that on  the application layer and wrap the loading statements into a transaction -- that way you'll get all or nothing.


Is the "load data infile" command in MySQL unsafe in general, or just when using statement based replication?

As always, there is a trace on why they marked it unsafe in 5.5.6 and also in 5.1.52

There are several problems with reproducing LOAD DATA INFILE queries in this manner:

If the user does not know the internal workings of mysqlbinlog, the temp files will
accumulate on disk and never be removed.
There is no easy way to remove the temp files automatically (the safest way is to
parse the output of mysqlbinlog, which is hard and there is still no guarantee that the
format never changes, etc.)
Temp files will accumulate on the pushbuild server.


Dynamic SQL - How can you use it to run a SELECT against all schema's and Tables?

Below is self explanatory code. Alternatively you can use sp_ineachdb (written by Aaron Bertrand).

 set nocount on
  -- create temp table ..
 create table #commandsToRun (currentDB sysname, schemaName sysname, TableName sysname, columnName sysname, datatype sysname, commandToRun nvarchar(max))
 insert into #commandsToRun
SELECT
        db_name() as CurrentDB, SCHEMA_NAME(tbl.schema_id) as SchemaName, tbl.name AS TableName,
         c.name AS ColumnName, tp.name as DataType,
         N'select count('+c.name+') as TotalDateColumnAsNULL, '
                    + ''''+SCHEMA_NAME(tbl.schema_id)+'''' + ' as  SchemaName, '
                    + ''''+tbl.name + ''''+ ' AS TableName' + ''
                    +' from '+ db_name() +'.' + SCHEMA_NAME(tbl.schema_id)
                    +'.'+ tbl.name +' with (nolock) where '
                    +c.name+' is NULL'as CommandToRun
FROM
  sys.columns c
JOIN sys.tables tbl ON
  c.object_id = tbl.object_id
JOIN sys.types tp on c.user_type_id = tp.user_type_id
WHERE
  ( c.name = 'Date' -- filter the column that you want to check
  or tp.name = 'datetime' -- filter the datatype if you dont know the column names
  )
-- check the contents of the table
select * from #commandsToRun

-- generate exec() to run the sql generated in the CommandToRun

declare @sqltext nvarchar(max) = N''

select  @sqltext += N'exec ('''+replace(CommandToRun,'''','''''')+'''); '
from #commandsToRun

-- uncomment below to run the sql generated.
-- exec(@sqltext)


What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?

In more recent versions of Postgres (I think since 8.3) you can assign a special tablespace for temporary tables which might help you. This is documented here:

http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-TEMP-TABLESPACES

Given the fact that 8.2 will be de-supported at the end of the year it might be a good idea to upgrade. There have been numerous enhancements to VACUUM and the handling of temporary files since 8.2 so you might benefit from those.

Edit:
The reason why I think this (separate tablespace) could help you, is that you can simply drop and recreate the tablespace (files) to reclaim the spaces occupied.

But then I'd assume that due to all the improvements that were implemented in the last 5 years the current version might release the space without any further action from your side (especially because VACUUM FULL has been completely rewritten in 9.0)


Select the greatest value from each column, even if one of the values is null

select
GREATEST(nvl(surface_year,0),nvl(base_year,0)) as greatest_year
from
road_age_test;

--Edit using NULLIF
select
NULLIF(GREATEST(nvl(surface_year,0),nvl(base_year,0)),0) as greatest_year
from
road_age_test;

--Adding a segmented version for readability (some find it worse, eye of the beholder I guess)
select
NULLIF(
    GREATEST(
            nvl(surface_year,0),
            nvl(base_year,0) --Just keep adding columns here if you need to
            )
      ,0) as greatest_year
from
road_age_test;

Can't imagine it's much more efficient (and shouldn't be used in a where clause due to indexing), but it's a little cleaner than the case statement and allows you to add more values (columns) easily.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm


Best Data compression technique in Oracle

As longs as you don't do ANY updates on compressed tables you are fine. The old fashioned block level compression works fine when you can add data in batches. The old block level compression only works on loads, not on regular single record inserts.

Assuming that archiving goes in batches block level compression is OK.
PDF's will hardly compress using the available compressors in Oracle but when things like layout and font info is identical for many files, 7zip could be your friend but AFAIK this is not available in the database.

If you want to compress transactional data advanced compression is for you but it has a performance penalty.

When your data accesses are in scans, the compression could even give you a performance benefit because there are less io's to do. This is only true when you have plenty CPU available to do the inflight de-compression.

If you have to do updates on compressed tables, your performance is dead and your volume is back to the uncompressed volume. So be careful. You won't be the first one who is hit by this feature. This is also very true for the archive compression mode in Advanced Compression.


ORA-01122: database file 5 failed verification check

You cannot put the datafiles in Offline when your DB is in No Archive Log mode.

So below are the steps to recover the database considering current situation :

Shutdown immediate;
startup nomount;
Recreate the control file using backup trace. :

alter database backup controlfile to trace as '$ORACLE_HOME/dbs/control_new.sql';

Now make changes in the created control_new.sql:

CREATE CONTROLFILE REUSE DATABASE "db_name" RESETLOGS ARCHIVELOG
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 100
   MAXINSTANCES 8
   MAXLOGHISTORY 2921
 LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/db_name/redo1.log' SIZE 400M,
  GROUP 2 '/u01/app/oracle/oradata/db_name/redo2.log' SIZE 400M,
  GROUP 3 '/u01/app/oracle/oradata/db_name/redo3.log' SIZE 400M,
  GROUP 4 '/u01/app/oracle/oradata/db_name/redo4.log' SIZE 400M,
  GROUP 5 '/u01/app/oracle/oradata/db_name/redo5.log' SIZE 400M,
  GROUP 6 '/u01/app/oracle/oradata/db_name/redo6.log' SIZE 400M
 -- STANDBY LOGFILE
 DATAFILE
  '/u01/app/oracle/oradata/db_name/system01.dbf',
 ...
  '/u01/app/oracle/oradata/db_name/FCJ_DATA_TS_13.dbf'
 CHARACTER SET AL32UTF8;

RUn the file in SQL prompt :

@$ORACLE_HOME/dbs/control_new.sql

5.Mount the database :

alter database mount;

Recover database using control file:

Recover database using backup control file until cancel;
Now open the database :
alter database open;


Schema review for system to advertise cars

Initially I have thought to only have reference to "car_model" in "posts" table. Cause technically I could access the car make via the "car_model" table. But then decided that for search purposes it would be easier to have both keys in this table, correct decision?

No,
this adds redundancy to your database and this is something you generally do not want to have at all. It also does not fit with the 3rd Normal Form.

To read about Normal Forms look [here][1]

  Another point, is with properties.

It is no problem and very typical to have database querys across multiple tables. (This is basically why we use databases and have multiple table and not smash everything together) Do not worry about it and please do NOT add extra redundancy with such an extra text field.

  any other suggestions given the specification?

Yes, there is no need for the idproperties_post_rel column in your properties_post_rel table. You should make the both foreign keys the primary key so you can not accidentally set a property on one offer twice.
Also the relationship is well enough defined by the unique combination of these FKs so no need to waste space for an extra synthetic key.


Oracle 11g r2: Cannot create partition on timestamp field

change to

insert into t select 1, to_timestamp ('2009-02-10 12:34:45.56','YYYY-MM-DD HH24:MI:SS.FF2') from dual;

should work. Don't rely on default formats.


The below query works like this:

It first uses the ROW_NUMBER window function to partition it by PRODUCTID and ORDER them by CREATEDATE from 1 to n
For STATUS = 'pass' you give 1 (0 otherwise)
You then only keep the first 2 values with n <= 2 (-> 1 or 2)
Finally if the sum of the 2 rows for a given PRODUCTID is equal to 2 and the count of distinct LEVELs is 2, it returns TRUE.

Query:

SELECT PRODUCTID
    , CASE WHEN SUM(STATUS) = 2 AND COUNT(DISTINCT LEVEL) = 2
          THEN 'TRUE'
          ELSE 'FALSE'
      END
FROM (
    SELECT PRODUCTID, LEVEL
        , STATUS = CASE WHEN STATUS = 'pass' THEN 1 ELSE 0 END
        , n = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC)
    FROM data
) agg
WHERE n <= 2
GROUP BY PRODUCTID

See SQL Fiddle.

Output:

PRODUCTID   RUNSTATUS
ABC         TRUE
DEF         FALSE
GHI         FALSE
JKL         FALSE


Connecting to SQL Express Server 2017 with SQL Server Authentication Remotely

Typically, a default instance will use port 1433, and a named instance will use an arbitrary port in the 49152 to 65535 range. Because SQL Express uses a named instance (even if it is the first and only instance on a server), it will grab an arbitrary high port. So make sure that TCP port 50207 is allowed from your second machine to Desktop. And also open UDP 1434, in order for the SQL Browser service to do its job.


If the ultimate number of apps possible is very small (4-5 would be the limit for me, I think), what you've got can work.

If, at some point, there will be other data to store for each user and each app, and that data would vary considerably from one app to another, then it may be the easiest structure to work with.

If there will never be a need to store additional data per user per app; or, if that data would be basically the same from one app to another, then you could have an app table to store unique information about the apps (not tied to individual users), and have a user_app table to link users to their apps, and store any common data that would apply to all apps (first sign-in, last sign-in, whatever you might need, if anything).

CREATE TABLE app (app_id int PRIMARY KEY, app_name varchar(128), ...);
CREATE TABLE user_app
     ( user_app_id int PRIMARY KEY
      ,app_id -- foreign key to table app
      ,user_id -- foreign key to table user
      ... - other columns, if needed
     );

This is probably the best solution if the number of apps grows large (say, if you're tracking users and their apps in Google Play, rather than users and 4 in-house apps, where you're not expecting more in the foreseeable future).

Update - as noted by ypercubeᵀᴹ, user_app should have a unique index on (user_id, app_id), assuming each user can only have one account per app.

If it was possible for a single user to have multiple accounts on a given application for some reason, then you would want to include the account_id as a column. Then, you would not want the (user_id, app_id) unique index; instead, you would want a unique index on (app_id, account_id) (assuming that duplicate account IDs are fine across different applications; otherwise, the unique index should just be on account_id).


can we use a mongodb 3.0 and mongodb 2.6 in same replica set?

You can perform a rolling upgrade of your replica set from MongoDB 2.6 to 3.0.

While a mixed version 2.6/3.0 replica set is supported for the purposes of upgrading, you should not run with this configuration for longer than is required.

You should also read the 3.0 upgrade and compatibility change notes carefully, as important considerations will be included there. As with any upgrade, you should test in a development/staging environment before prior to your production release.

A few particular points to be aware of:

Before enabling the new WiredTiger storage engine, ensure that all replica set/sharded cluster members are running at least MongoDB version 2.6.8, and preferably version 3.0.0 or newer.
Once upgraded to MongoDB 3.0, you cannot downgrade to a version lower than 2.6.5
If you upgrade to 3.0 and have run authSchemaUpgrade, you cannot downgrade to 2.6 without disabling --auth or restoring a pre-upgrade backup. The authSchemaUpgrade discards MONGODB-CR authentication credentials used in MongoDB 2.6.


Can one part of a multi-column primary key be used across tables?

It is not possible to have 'hidden' columns.

When you are using GUIDs to avoid merge conflicts, you must use GUID keys in every table that contains data to be merged.
And when you already have a GUID, it does not make sense to add another column to the primary key.

If you do not want to use GUIDs, just deal with the conflicts, i.e., instead of blindly copying the records, map all old IDs to new ID values.


Calculate percent changes in contiguous ranges in PostgreSql


Options to include with mysqldump when taking backup of hybrid databases

It would make more sense in my opinion to do a backup by table instead of by database. Here is a script I use to make backups of databases that contain MyISAM and InnoDB tables.

#!/bin/bash
USER="some_user"
PASSWD="some_password"
STAMP=$(date +"%d-%m-%y-%H:%M:%S")
HOST="some_host"
db="some_database"
FILESTAMP=$(date +"%d%m%y")
FILE="/some_directory/some_file-$STAMP.sql.gz"
Port=3306

TABLE_DUMP=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT table_name FROM information_schema.tables where table_schema = '$db' and engine is not null order by table_type, table_name;")
for table_name in $TABLE_DUMP
do
    table_engine=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT engine FROM information_schema.tables where table_schema = '$db' and table_name = '$table_name';")
    case "$table_engine" in
        InnoDB )
            echo "$table_name InnoDB\n"
            mysqldump --skip-opt --disable-keys --create-options --single-transaction --quick --extended-insert --add-drop-table -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
        ;;
        MyISAM)
            echo "$table_name MyISAM\n"
            mysqldump --opt -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
    esac

    #register all table names
    skiptables="$skiptables --ignore-table=$db.$table_name"
done

# View
mysqldump -P $Port -u $USER -P$PASSWD -h $HOST $db $skiptables | gzip -9 >> $FILE


Replicating a postgres database for testing

Unless the DB is huge, your best bet will be to clone it periodically.

Here's what I'd do if you're on PostgreSQL 9.3, which supports cascading replication:

On the live server, add a streaming replica that keeps an up to date copy on the dev server. You don't use this DB directly, it's just there so you can copy the DB without disrupting the master.
Use pg_basebackup -X stream to take a copy of the replica when you want to refresh the dev db. You can do this while still using the dev db so long as you send the base backup to a separate location.
Stop the dev db, delete its data directory, move the data directory you just copied with pg_basebackup to replace the old dev db, and start the dev db again. This ensures minimal downtime for the dev db.

The downside of this approach is that your dev server needs space for three copies of the master db - the streaming replica, the base backup you're copying, and the current dev db.

You can reduce that to two copies by deleting the dev db before doing the base backup, in exchange for a longer dev outage. Or by stopping dev and the replica, rsync'ing them, then starting them back up, instead of doing a basebackup and directory swap.

You can reduce that to one copy by doing away with the streaming replica and doing the base backup direct from live after deleting the dev db. Or you could stop the dev db, pg_start_backup() the master, rsync master to dev, and pg_stop_backup() on master. However, these approaches impose greater disk I/O on live while the base backup runs.

There are many possible variants; for example, you can stop the streaming replica and copy it at the file system level rather than using pg_basebackup.

One alternative I'd be tempted to use if my dev db was quite big would be to make a file-system or drive-level snapshot of the streaming replica while it's running, then mount that snapshot copy-on-write on a different directory and start it as the dev DB. That way you only have to actually copy data that changes. This approach is supported by LVM and by AWS EC2 EBS at the volume level, or by zfs and btrfs at the file system level.

Do not attempt to "sync" the master to the replica in real time. Not only will it impose an unreasonable load on the master and possibly cause issues on the master,


SSAS Deployment Wizard - Issue with multiple connection strings

What happened is that developers added new data sources instead of adding tables from an existing connection. In the future go to the Model menu... Existing Connections... Open. Then choose another table or query to import. This process is documented here.

I don't believe there is a UI way of switching an existing table to use a different data source. The best approach is probably to just delete and recreate that table with the process above.

You should be able to delete the extra unused data sources from the Existing Connections dialog.


Can't shrink Log file - "An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623)"

You should update your question with the full @@version of your server.

You mentioned only that you use SQL Server 2008 R2, if you are only on SP2 this hotfix is for you:  Cumulative update package 8 for SQL Server 2008 R2 Service Pack 2

Among the issues it fixes, there is yours:

But I recommend you to patch your server to SP3.


What's the formula for calculating Key Efficiency, Key Buffer Used, and Query Cache Hitrate in MySql?

For your reference i am trying to explain some concepts also.

Key hit rate

There are basically two forms of key hit rate

1.Key Read Efficiency

  Key Reads: The number of physical reads of a key block from disk.

  Key Read Request: The number of requests to read a key block from the
  cache.

Key Read Efficiency = [1 - (Key_reads/Key_read_requests)]*100

  Key Read Efficiency: The ratio of the number of physical reads of a
  key block from the cache to the number of requests to read a key block
  from the cache in percentage. The MySQL performance is good if the
  value of Key Read Efficiency is 90 percent and above. Increasing the
  size of the cache improves the value of Key Read Efficiency and hence
  an improved the performance.

2.Key Write Efficiency

  Key Writes: The number of physical writes of a key block to disk.

  Key Write Request: The number of requests to write a key block to the
  cache.

Key Write Efficiency=(Key_write/Key_write_requests)*100

  Key Write Efficiency: The ratio of the number of physical writes of a
  key block to the cache to the number of requests to write a key block
  to the cache in percentage. For a good performance of the MySQL
  server, the value of Key Write Efficiency must be 90 percent and
  above. If it is found less, then you can increase the size of the
  cache to improve the performance.

Key Buffer Used

Find value of key_buffer_size as show variables like 'key_buffer_size';

convert it into MB.

Find the All MyISAM index Size :

  SELECT SUM(INDEX_LENGTH)/(1024*1024) 'Index Size' FROM
  information_schema.TABLES where ENGINE='MyISAM' AND TABLE_SCHEMA NOT
  IN('mysql','information_schema');

Find

(Index Size)/key_buffer_size(in MB) * 100

  if result is (<=100 ) then your all indexes are cached into key_buffer

  if result is (>100) then your all indexes are not cached into
  key_buffer you may gain performance boost by increasing
  key_buffer_size.

query cache hitrate

  Hit rate = Qcache_hits / (Qcache_hits + Com_select) * 100

  Insert rate = Qcache_inserts / (Qcache_hits + Com_select) * 100

  Prune rate = (Qcache_lowmem_prunes / Qcache_inserts) * 100


How to avoid invoking functions twice when using GROUP BY and HAVING?

Your second query (the one where you implement it with the HAVING clause) is probably faster. In your first query (with the subselect), postgres has to calculate the count values for the entire table. In your second query, it can start ignoring rows to count once it reaches a count value above 1 (although I don't 100% know if postgres is smart enough to do that - I'm fairly certain it is, though).

Since COUNT() is an aggregate function, it's going to be run the number of times it'll be run regardless of the number of rows returned. If you had a function that was NOT an aggregate function, then running your group and where/having clause in a subselect would likely be faster.

Example of what I'm referring to:

SELECT
  some_non_agg_function(a.id, a.child)
FROM join_tab1 a
GROUP BY a.id, a.child
HAVING COUNT(a.id) > 1;
-- probably not as fast as
WITH rows_to_process AS (
  SELECT DISTINCT
    id, child
  FROM join_tab1 a
  GROUP BY a.id, a.child
  HAVING COUNT(a.id) > 1
) SELECT
  some_non_agg_function(id, child)
FROM rows_to_process;

To answer your question specifically - yes, postgres will keep track of what aggregate values it has calculated and re-use them (as opposed to re-calculating them) within the HAVING clause. I believe it will also re-use them in the SELECT clause (if for some weird reason you run the exact same aggregate more than once in the SELECT)

To quote Postgres's excellent documentation (bold mine)

  It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.)

This doesn't specifically say that it re-uses the calculated values .. but it implies it with saying that the HAVING clause is used after aggregates are calculated.


Deadlock error in inserting bulk of records to MySQL database using trigger and Qt multi threading

SET @source_names_id = (select id from source_names USE INDEX (PRIMARY) where source_name=NEW.source_name);

-->

SELECT @source_names_id := id
    FROM source_names
    WHERE source_name = NEW.source_name;

AND have

PRIMARY KEY(id)
INDEX(source_name)

Do not use USE INDEX(PRIMARY) unless it is PRIMARY KEY(source_name).  Even then, there is no need for USE INDEX.

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


Please, try to reset Intellisense Cache:
CTRL+SHFT+R

But Intellisense completes object names not sql statement like SELECT.


Zero Rows return on querying DMV sys.dm_os_performance_counters

If you're sure the user in question has View Server State (and it looks like in your screenshot he does).

Then there are a number of reasons previously put into an msdn blog. Ranging from:

Performance Objects and counters set-up during the SQL Server installation failed.
A mixture of 64 and 32 bit platforms.
Registry permissions have been skewed

To resolve this we can use the same steps outlined in the guidelines for reinstalling the performance counters in a different stack-exchange post:

Using an elevated administrator command prompt perform the following steps.

Change the path to the BINN directory of the SQL Server instance you desire to correct.
(Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)
Execute unlodctr <<REGISTERED SERVER NAME>>
For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008 ...
Execute lodctr /T:<<perf-sql* matching the counters you desire to load>>
For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent. The /T is important to load the SQL Server performance counter provider as a trusted provider.
Cycle the remote registry service:
net stop "Remote Registry" then net start "Remote Registry"
Force a WMI synchronization using winmgmt /resyncperfctr "<<PID>>"
where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

The following may also be required:

Make sure that the correct security permissions have been granted to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009
registry key:

Grant Full Control permissions to the Creator Owner account.
Grant Full Control permissions to the Administrators account.
Grant Read permissions to the SQL Administrators on that box/Node.
Grant Full Control permissions to the System account


mongo cluster collection count showing different every time

First, of course you are always connecting to your cluster thru mongoS, so query (count) goes to cluster and not directly to one individual shard.

Second, you are connected to right DB when you do count, I have seen situations where user has accidentally changed/connected to wrong DB and count (of course) return NULL, because that collection don't exists on that current DB.

You can always use

  db.getSisterDB("yourDB").collection.count()

where ever you are...

Actually count() returns "cached" (metadata) count from collection, if you want to have absolute value, use

  db.getSisterDB("yourDB").collection.find({_id:{ $gt: MinKey,
  $lt:MaxKey} }, {_id:1}).itcount()

what is "slow" operation because it really goes in the collection and count how many documents is there.


SQL Server 2008 Script Changes from Database Diagram

More Google searching did not help, but after fiddling around with SSMS I managed to figure it out. Thought I'd answer my own question for anyone else who might stumble across this.

To script all of the changes at once, simply right-click a blank area of the diagram and click 'Select All' (or press Ctrl+A). Then right-click one of the selected tables and choose 'Generate Change Script...' at the bottom.


How do I set up high availability at the instance level?

It sounds like Failover Clustering is what you may be looking for.  You can have the instance failover (though there will be a period of disconnect for end-users, but that is typically minimal while the passive node becomes active and SQL Server starts up).  SQL Server Failover Cluster Instances (FCI) are the way to achieve high availability at the instance level.

It is noteworthy, that this technology sits on top of WSFC respectively, and you will require shared storage (this will be different if you are on SQL Server 2012 implementing AlwaysOn Availability Groups, but that is not instance-wide failover).

Failover Cluster Instances are a robust technology within themselves, much more that can be directed through a Stack Exchange answer, so my recommendation is to use the above link and if you have any more specific questions feel free to ask them on a separate question.


Using CLR User Defined Functions in SQL Server

This is the classic argument, for or against stored procedures in general. The fact is, you can't stop people connecting to your database. Sure Joe in the warehouse will use the in-house stock control app that has all your business rules in, he's got a dumb terminal/web browser on the Intranet, he's got no choice. But if Bob the CFO wants to connect with Excel and do some pivot tables, are you going to say no, you can't do that, give me 6 months to write you a custom app in the application server tier? No. Or Jane SQL consultant who likes a command line client, and you're on vacation so you can't tell her no.

But you don't want to risk any integrity issues so put your validation and business rules somewhere there's no way round them, right up close with the data. Maybe T-SQL or C# isn't the prettiest language going - but SPs and triggers are the right tool for the job.

Other than that, managing code deployed in the database is actually no different to managing code deployed in an application server (e.g. J2EE, COM objects, whatever). It's a matter of just not treating it as anything "special", having proper discipline about versioning, building and releasing it, etc. How is it "hidden" at your site?


How to categorize records in groups in PostgreSQL?

There is no "sites" field in your SQL statements.

I did the following to solve your problem:

(You'll notice that I've taken many more fields than are necessary to do the operation - it's so that you understand where the data is coming from. I often find it very helpful to include far more fields than required in my initial attempts at formulating SQL and then I pare them down as required by the problem!).

WITH emp_group_join AS
(
  SELECT e.id AS eid, e.name AS ename, e.group_id AS egid,
         g.id AS gid, g.name AS gname
  FROM emp e
  JOIN groups g ON
     e.group_id = g.id
),
sites AS
(
  SELECT egj.*,
    CASE
      -- This is necessary to distinguish between Managers + Teachers
      -- and other categories of worker - of course, if you add a site
      -- field, you may not have to do this!

      WHEN gname = 'Managers' THEN 'White Collar'
      WHEN gname = 'Teachers' THEN 'White Collar'
      ELSE gname
    END AS work_type
  FROM emp_group_join egj
)
SELECT work_type, COUNT(work_type) FROM sites
GROUP BY work_type
ORDER BY work_type DESC;

and the result is:

  work_type   | count
--------------+-------
 White Collar |     3
 Drivers      |     1
 Cleaners     |     1
(3 rows)

This is how to do it using a subSELECT (same result):

SELECT work_type, COUNT(work_type) AS wt_count
FROM
(
  SELECT e.group_id,
         g.id,
         g.name,
         CASE
           WHEN g.name = 'Managers' THEN 'White Collar'
           WHEN g.name = 'Teachers' THEN 'White Collar'
           ELSE g.name
         END AS work_type
  FROM emp e
    JOIN groups g ON
    e.group_id = g.id
) AS join_tab
GROUP BY work_type
ORDER BY wt_count DESC;

You could try running the WITH clause SELECT and/or the subSELECT statements on their own to see in detail what's happening! p.s. +1 for providing DDL and DML!


Mixed granularity weeks/months time series data in a data warehouse?

I don't know if there is a better term for fixing the problem, but the name for the problem in the first place is "mixed granularity" or "overlapping granularity". It is most common in calendar related data but can occur in data split by geography (different sources using different boundaries when aggregating their results) especially when reporting over time (as official boundaries can change in significant ways some times deliberately (https://en.wikipedia.org/wiki/Gerrymandering)). Fixing the issue so you have some useful merging of the data in your warehouse depends on how you need to report upon the data.

If you are reporting on calendar quarters then it is fairly easy: you know the three months that go in each quarter, and 13 weeks go into each too. As weeks and quarters don't align perfectly you are going to have some data at each end of the quarter that has the possibility of not quite being counted right and there are one or two extra days (two in a leap year). One suggestion would be to count January the 1st as day 1 of week 1, and count weeks 1 through 13 quarter 1 and so on. For the one or two days that make up the partial week 53, add them to quarter 4. There is going to be some "error" here but that is unavoidable and should be more than small enough (given large enough samples/figures) to no significantly affect the trends you are trying to detect and report upon.

The other that springs to mind you have already suggested: average out all the data sources, breaking them down to a common such as a day during your ETL process - so record of £123,000 sales in June becomes £4,100 counted against each day, and £35,000 in the week beginning averages to £5,000 on each of the seven days. You can then aggregate this back up to whatever larger granularity you desire. Again you are losing some definition in your data, but the results will be less "lumpy" than, for example, mapping whole weeks to months and having some with four and some with five.

Of course the ideal solution is to try get hold of the original data before it was rolled up at all so you have it all at the finest granularity to start with, but that is often not practical or not even possible because only the aggregated data remains.


SQLAnywhere: Syntax error on call function; Call without IF construct is working

I think this is caused by a confusion between Watcom-SQL and T-SQL syntaxes.

Because you use CALL (rather than EXEC) in the first example the database engine will assume that the code is in Watcom SQL. (There is a PRINT statement later but the decision will have been made before then).

In the second example the command PRINT is only valid in T-SQL and the whole code segment is valid T-SQL so the database engine will assume that all the code is T-SQL and the THEN is not needed.

If I'm right then this should work (untested in Watcom-SQL):

DECLARE @tmp BIT;
SET @tmp = 0;
IF @tmp = 1
   THEN  call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')
ELSE
    MESSAGE 'Path, which is chosen' TO CLIENT
END IF;

For the differences in SQL Anywhere support for various SQL dialects see: http://dcx.sybase.com/index.html#1200/en/dbusage/ug-sql-compat.html

Update - this batch runs fine in dbISQL:

BEGIN
  DECLARE @tmp BIT;
  SET @tmp = 0;
  IF @tmp = 1
       THEN call dbo.sa_info()
  ELSE
      MESSAGE 'Path, which is chosen' TO CLIENT
  END IF;
END

(I've added a BEGIN and END to give the DECLARE a context & used a built-in procedure rather than your user defined one) Tested in 10.0.1.4310 & 16.0.0.1535. If I omit the THEN I get the same error as you were getting.

A pure T-SQL version would be:

BEGIN
  DECLARE @tmp BIT
  SET @tmp = 0
  IF @tmp = 1
   EXEC dbo.sa_info
  ELSE
   PRINT 'Path, which is choosen'
END

which also works on both 10.0.1.4310 & 16.0.0.1535. Don't forget that if you use T-SQL EXEC the parameters have to be specified individually (not in brackets):

[ EXECUTE ] | [ EXEC ][ @return_status = ] [creator.]procedure_name [ argument, ... ]
argument :
 [ @parameter-name = ] expression
| [ @parameter-name = ] @variable [ output ]


Stack Exchange offers their monitoring tool as open source. It's really powerful, if you can get over the installation process.

https://github.com/opserver/Opserver


IF that is actually your script, then after you connect "/ as sysdba", then sqlplus is simply waiting for input.  It doesn't see the 'alter session', etc, because those aren't being redirected into sqlplus.  Rather, they are simply treated as the next command for the shell to process after sqlplus exits.

Try this:

sqlplus / as sysdba <<EOF
alter session set "_ORACLE_SCRIPT"=true;
create user username identified by 12345;
grant dba to username; exit
EOF

BTW, you should not be using 'as sysdba' if you can accomplish the same thing with a less privileged user, such as SYSTEM.  Connecting to oracle as SYS is like connecting to Linux as root.  You are running with scissors, knives, and blowtorches, all without a net.


The normal forms are specified in such a way that for any normal form, the model is also meets the criteria for the lower numbered normal forms.  So, yes if your model is in 3rd normal form, it is also in 2nd normal form and 1st normal form.

When normalizing a model, you normally work at resolving one normal form before the next.  This makes normalization simpler and less error prone as you are only applying a few rules at at time.  It is possible that an entity at a lower normal form is also at a higher normal form.  Most 3rd normal form entities are also in 4th and 5th normal form.

4th, 5th, and 6th normal forms go beyond the functional dependence.  Where applicable, they further simplify the data, at the expense of more tables.  They reduce redundancy, and increase data integrity.


ORA-600 errors are usually bad news. ORA-600 on OraFAQ.
If this is an environment you or anyone else cares about I recommend:
1) Opening an Oracle service request
2) Find your organizations DBA and engage them immediately
3) Dig those backups out because you may have a significant corruption of RDBMS binaries or data.

Best of luck!


Index or View of all UUIDs in Postgres DB?

Disclaimer: I think the whole idea doesn't really make sense. But anyway here is one way to get a uniform "view" on all UUIDs in the database.

You can create a function that goes through all columns that are defined as UUID and then returns the values for those columns. You can optionally pass a UUID value that should be searched for:

Something like:

create or replace function find_uuids(p_id uuid default null)
  returns table(schema_name text, table_name text, column_name text, uid uuid)
as
$$
declare
  l_row record;
  l_sql text;
begin
  for l_row in select n.nspname, tb.relname, col.attname
               from pg_attribute col
                 join pg_type ty on ty.oid = col.atttypid
                 join pg_class tb on tb.oid = col.attrelid
                 join pg_namespace n on n.oid = tb.relnamespace
               where col.attnum > 0
                 and not col.attisdropped
                 and ty.typname = 'uuid'
                 and n.nspname not in ('pg_catalog', 'information_schema')
                 and n.nspname not like 'temp%'
  loop
    l_sql := format('select %L as schema_name,
                            %L as table_name,
                            %L as column_name,
                            %I as uid
                     from %I.%I', l_row.nspname, l_row.relname, l_row.attname, l_row.attname, l_row.nspname, l_row.relname);
    if p_id is not null then
       l_sql := l_sql || format(' where %I = %L', l_row.attname, p_id);
    end if;
    return query execute l_sql;
  end loop;
end;
$$
language plpgsql;

Then you can use:

select *
from find_uuids('19d2efcc-45c1-4371-addc-45028a054b85');

to find the tables where that UUID value appears, or

select *
from find_uuids();

to see everything.

The above returns something like this:

schema_name | table_name | column_name | uid
------------+------------+-------------+-------------------------------------
public      | uuid_test  | some_id     | 666ce0a8-11c9-11e0-b5e2-3b49c86ffc2c
public      | uuid_test  | some_id     | 7fffda0c-11c9-11e0-967c-a300aec7eb54
public      | some_table | id          | 3aa04664-863c-43e6-9890-1087db021fd8

But this is going to be really slow


Can I query a tab-delimited file from SSMS?

You must create a schema.ini file containing the delimiter in the same directory as the text file you are opening. This is the only way to override the registry values on a per-file basis. See the file format documentation on MSDN. Example:

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text; HDR=YES; Database=C:\Text',
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:

[testupload2.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0

If this activity needs to be repeated frequently, I would suggest a script to create schema.ini. Multiple files can be referenced in the same schema.ini or a separate schema.ini can be included with each text file in its own directory.


MongoDB - Stand Alone Server Security SSL or SSH

x.509 is, of course, best choice. You can get keys (for free) from let's encrypt organization if you "own" domain address.

Then there is keyfile what you can use.

Other choices are SSL tunnel or OpenVPN, latter preferred.


What is the most efficient method of performing the FIZZBUZZ test in SQL Server?

Using a SQL Server 2014 memory optimized table and a natively compiled procedure:

-- Setup
CREATE DATABASE InMem;
GO
ALTER DATABASE InMem
ADD FILEGROUP FG1
CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE InMem
ADD FILE
(
    NAME = 'FN1',
    -- Change to suit your system
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\FN1.mod'
)
TO FILEGROUP FG1;
GO
USE InMem;
GO
CREATE TYPE dbo.FizzBuzzTableType AS TABLE
(
    n integer NOT NULL INDEX i,
    FizzBuzz varchar(8) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);
GO

Native procedure:

CREATE PROCEDURE dbo.FizzBuzz
WITH
    NATIVE_COMPILATION,
    SCHEMABINDING,
    EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'english'
)
    DECLARE @n AS dbo.FizzBuzzTableType;

    DECLARE @i integer = 1;
    WHILE @i < 50
    BEGIN
        IF @i % 15 = 0
        BEGIN
            INSERT @n (n, FizzBuzz)
            VALUES (@i, 'FizzBuzz')
        END
        ELSE
        BEGIN
            IF @i % 3 = 0
            BEGIN
                INSERT @n (n, FizzBuzz)
                VALUES (@i, 'Fizz')
            END
            ELSE
            BEGIN
                IF @i % 5 = 0
                BEGIN
                    INSERT @n (n, FizzBuzz)
                    VALUES (@i, 'Buzz')
                END
                ELSE
                BEGIN
                    INSERT @n (n, FizzBuzz)
                    VALUES (@i, CONVERT(varchar(8), @i));
                END;
            END;
        END;

        SET @i += 1;
    END;

    SELECT
        N.n,
        N.FizzBuzz
    FROM @n AS N
    ORDER BY
        N.n;
END;

Test:

SET NOCOUNT ON;
PRINT SYSUTCDATETIME();
GO
DECLARE @T AS dbo.FizzBuzzTableType;

INSERT @T (n, FizzBuzz)
EXECUTE dbo.FizzBuzz;
GO 100

PRINT SYSUTCDATETIME();

Typical results:

-- 95ms for 100 iterations, < 1ms each
2014-12-31 10:07:13.7993355
Beginning execution loop
Batch execution completed 100 times.
2014-12-31 10:07:13.8943409

This writes the procedure output to an in-memory table variable, because otherwise we're just testing the speed of displaying results in SSMS.

One million rows

The above native procedure takes about 12 seconds to run on 1,000,000 numbers. There are all sorts of faster ways to do the same thing in T-SQL. One I have written before follows. It runs in about 500ms on my laptop on a million rows when the intended parallel plan is achieved:

IF  OBJECT_ID(N'tempdb..#Result', N'U') IS NOT NULL
    DROP TABLE #Result;

IF  OBJECT_ID(N'tempdb..#Thousand', N'U') IS NOT NULL
    DROP TABLE #Thousand;

SET NOCOUNT ON;
DECLARE @start datetime2(7) = SYSUTCDATETIME();

CREATE TABLE #Thousand
(
    n integer NOT NULL,

    CONSTRAINT PK_#Thousand
    PRIMARY KEY CLUSTERED (n)
);

-- Add 1,000 rows numbered 0-999 to #Thousand
WITH
    L1 (n) AS
(
    SELECT  V.n
    FROM
    (
        VALUES  (0), (1), (2), (3), (4),
                (5), (6), (7), (8), (9)
    ) AS V (n)
),
    Thousand AS
(
    SELECT  n =
        CONVERT
        (
            integer,
            ROW_NUMBER() OVER (
            ORDER BY (SELECT NULL))
            - 1
        )
    FROM L1
    CROSS JOIN L1 AS L2
    CROSS JOIN L1 AS L3
)
INSERT #Thousand (n)
SELECT n
FROM Thousand;

-- To hold the Fizz Buzz output
CREATE TABLE #Result
(
    n integer NOT NULL,
    result varchar(8) NOT NULL
);

INSERT #Result
SELECT
    Million.n,
    Million.result
FROM
(
    -- Modulo operation to encourage few outer rows parallelism
    SELECT  n
    FROM    #Thousand
    WHERE   n % 1 = 0
) AS T1
-- Outer Apply to keep the Compute Scalar parallel
OUTER APPLY
(
    SELECT
        F2.n,
        F2.result
    FROM #Thousand AS T2
    CROSS APPLY
    (
        -- Row numbers 1 to 1,000,000
        SELECT  (T1.n * 1000) + T2.n + 1
    ) AS F1 (n)
    CROSS APPLY
    (
        -- The Fizz Buzz bit
        SELECT
            F1.n,
            result =
                CASE
                    WHEN F1.n % 15 = 0 THEN 'FizzBuzz'
                    WHEN F1.n % 3 = 0 THEN 'Buzz'
                    WHEN F1.n % 5 = 0 THEN 'Fizz'
                    ELSE CONVERT(varchar(8), F1.n)
                END
    ) AS F2
) AS Million
OPTION  (MAXDOP 4, QUERYTRACEON 9481);

PRINT DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME());


Get rows that are referenced in another table

Sounds as if you are looking for something like this:

SELECT product.id
FROM product
where exists (select *
              from bom
              where bom.parent_item_id = product.id);

Alternatively

SELECT id
FROM product
where id in (select parent_item_id
             from bom);


Do SQL Server Compatibility Levels Introduce Old Security Vulnerabilities

It does not introduce past vulnerabilities, and applies mostly to T-SQL syntax and T-SQL features. As the documentation states,

  Compatibility level affects behaviors only for the specified
  database, not for the entire server. Compatibility level provides only
  partial backward compatibility with earlier versions of SQL Server.

Later on the documentation goes on to list the differences between each mode and you'll see no mention of security or hotfixes, just features/behaviors that will be differently implemented or unavailable.

Exploits would generally be at the server and DB engine level, so that isn't really a concern here. It would be a pretty huge security risk if I always kept my MSSQL instances patched but had one database in an older mode that was never able to take advantage of my diligent patching efforts. Although that would be a great way to get people onto the newest version!

You could argue that using old T-SQL code constrained by old version syntax might cause one to use work-arounds to inelegantly achieve certain requirements, which in turn might cause code to be more at risk for having vulnerabilities, but that onus is on the developer and nothing inherent to MSSQL. I can't even think of an example where this applies, but I'm sure they exist.


Why is there an option to rebuild/reorganize an index when SQL Server does this automatically?

Are you confusing fragmentation with statistics?
There is no auto handling of fragmentation. Do you have some reference to point to?
There is, however, auto update of statistics. It is a bit crude, which is why we might complement it with scheduled stats update jobs.


AlwaysEncrypted is not designed to be a solution for encrypting all of the data in the database.  There are quite a number of limitations, such as those documented in T-SQL Tuesday #69 : Always Encrypted Limitations and the official Always Encrypted (Database Engine).  For example, you can't encrypt columns with IDENTITY property--so right there is one reason it's unlikely anyone is going to encrypt an entire production database with AlwaysEncrypted.

AlwaysEncrypted is a solution for encrypting a few columns in a table that hold truly sensitive data, such as Social Security numbers, tax IDs, birth dates, credit card numbers, etc.

Additionally, the client application is what decrypts the data when AlwaysEncrypted is used.  Accordingly, you won't be able to use it with a third-party application unless it specifically supports AlwaysEncrypted.  If it is an in-house application, it would need to be modified to support AlwaysEncrypted.

If you need all of the data at rest to be encrypted to comply with regulations, you'll have to get an Enterprise Edition license to use TDE, encrypt it at the storage layer, or use a different database engine.


Schema blocking in READ_COMMITTED_SNAPSHOT

The following behavior may be caused by missing indexes on referring side of your FKs: "the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated"

When a row is deleted or its PK/Unique is mutating, the database engine need to make sure there are no orphans. When there is no proper index to support it, it scans the whole thing.


Best way to export excel table into MySQL db

For 1-time:

Save as text (csv) file.

CREATE TABLE ... with the columns approximating the Excel columns.

LOAD DATA LOCAL FILE ... into that table in MySQL.

(If you need 'automation', there may be better ways.)


Postgresql WAL Archiving, How to do it properly?

Using pgBarman, This was really easy as it walked me through it all, plus it's open source! I stumbed upon this while googling
link to pgBarman tutorial (pdf)
link to pgBarman site


Empty CDC scans on highly active SQL Server

Can you check if any table is enabled for cdc. You can query sys.tables dmv.

I suspect db is enabled for cdc but no tables are configured for cdc that might be the reason cdc couldn't fetch transactions.


Community Wiki answer originally based on notes by ypercubeᵀᴹ and Rick James suggested in the comments on the question.

The immediate issue is the parentheses around the UPDATE set list: they should not be there according to the syntax. That is what the error message is about.

Once you have fixed that one, there are other issues with your INSERT statement.

The IGNORE keyword just does not make sense when used together with ON DUPLICATE KEY. You either perform an update when a duplicate key is encountered, or you ignore the duplicate key entry completely, and it looks as though you want the former – therefore, lose the IGNORE keyword.
Setting product_id to '' in the ON DUPLICATE KEY clause is not consistent with setting it to NULL in the VALUES clause: an empty string ('') is not the same as a null. Judging from the column name, you probably want to have NULL specified in both places.
Similarly, seeting id to '1' in the ON DUPLICATE KEY clause is not consistent with setting it to 1 in the VALUES clause: the string ('1') is not the same as the number (1). This will probably not be an issue as the string will be converted to a number but it doesn't hurt to be explicit and very careful with the types of your column and values you pass to them.
While this is not exactly a problem, you don't have to specify the same values twice (in VALUES and in ON DUPLICATE KEY UPDATE clauses). You can specify them once and use them in two places, using the VALUES() function, like this:

INSERT INTO shop_item_details
  (shop_id, product_id, product_name, Product_model,
   Product_category, Product_details, price)
VALUES
  (1, NULL, 'camera', 'sony', '', 'hd', '5000')
ON DUPLICATE KEY UPDATE
  shop_id          = VALUES(shop_id),
  product_id       = VALUES(product_id),
  product_name     = VALUES(product_name),
  Product_model    = VALUES(Product_model),
  Product_category = VALUES(Product_category),
  Product_details  = VALUES(Product_details),
  price            = VALUES(price) ;

Please note that the above is just an example. Since you are updating the same set of columns as those you are inserting into, setting each of them using the column = VALUES(column) pattern is slightly redundant. The columns that define the unique (or primary) key being violated (you have not specified which columns they are in your case - perhaps shop_id and product_id? Or shop_id and product_name and product_model?) can be safely removed from the ON DUPLICATE KEY UPDATE clause.


Named pipes connection closed when subjected to load

What do you consider to be high-traffic?

Turn off Named Pipes and turn on TCPIP
It's lighter in it's communication. In my SQL installations, I never use Named Pipes except if the Application is on the same machine as the SQL service, and that's really ... never ... I always separate the application and SQL Server because monitoring and performance measures are more difficult to obtain and decipher.

Separate Applications/services from SQL Server
Also, if you have SQL and your application on the same server, put them on different servers from each other so that the SQL server is dedicated.

Index and stats maintenance
Make sure your indexes and stats are all maintained. Sometimes the activity can be high enough when stats and indexes are stale or fragmented that this in and of itself consumes most resources. Especially with standard installs and with no adjustment to CPU and RAM pinnings (see next item).

Pinning CPU/RAM to settings so OS/Networks/Services are not entirely consumed by SQL

update the CPU Affinity to use all but 1 or 2 cpus (if box has 8 cpus, use 6 or 7 for SQL and leavning 1 or 2 unchecked for the OS/Network/Services to use)
set the MIN and MAX RAM for the SQL Server to be "pinned" at the same RAM setting (if box has 8GB or RAM, set MIN to 6144KB and MAX to 6144KB), or something where MIN=(MAX Machine RAM * 20%) or MAX=(MAX Machine RAM * 10%) rounded to multiples of 1024KB.

These last two settings help to make sure that the resources for the SQL are not entirely consumed by SQL and the OS and Network and other services can respond and perform their standard requests. I've had situations where so much activity was happening (1K of simultaneous users, all indexes, stats, etc were good) that the box resources were completely consumed by SQL and the network services reported that the box could not be reached. Once I made these changes, and have been doing this ever since, many of my resource-consuming problems have gone away.

You could even try these last two with Named Pipes on and it should work because the Network can respond with the high traffic. But I would recommend TCPIP instead.


You have to use the GUI management tool to create a script and then run that - you cannot do it directly - take a look here (or here) for relatively clear explanations.


Reason for using write barrier before unlocking the buffer header in Postgresql

Without looking at the code, that seems not surprising to me: if you have the buffer header locked and write to it, then you want all these writes to happen before you unlock the buffer, right?


For optimal performance, MongoDB schema design must be custom-tailored for each application's unique access patterns. It's better to study general MongoDB design techniques than specific architectures (which may not match your specific application).

Having said that, Wordnik is a great case-study:

Similar application: real-time dictionary
20 billion records stored in 3.5 terabytes of MongoDB
Many online presentations sharing their MongoDB knowledge/experiences

Inside Wordnik's Architecture gives a more "macro" view of Wordnik's
architecture.
Lots of other slides

Here is how Wordnik apparently modeled their words:

source: MongoDB Schema Design


Datawarehouse - Dimension, Fact, or Degenerative dimension

This is how I would approach it myself. Remember to think of your Facts as "actions" or "verbs" and your dimensions as the the descriptors of your facts.

So your invoice and invoice line items are both facts. One approach to dealing with this is what you mentioned, making the fact table to the granularity of the invoice level line item. This will lead to redundancy of certain things like you mentioned. This if fine for things as an billing address dimension and certain invoice level facts such as a tax could even be broken down to the line level item and it could then aggregate back up to the invoice level amount.

This approach can break down if you have invoice level facts that don't break down nicely. For example, are customers allowed to have more than one payment method? Could there be more than one type of marketing material attached to the invoice? Does the marketing material correlate to specific line items?

Asking these questions can definitely lead you to creating multiple facts such as FactInvoiceLineItem, FactInvoice, FactPayments, etc. that you end up tying together with something like invoice number. Doing this will give you more flexibility to analyze specific facts better.

If doing this approach and you are building cubes off the fact tables, I would recommend using views to load your cubes. This way you could have a high level view for the invoice that joined your various fact tables and had measures on there such as number of line items, number of payments, etc.

All that to say, I believe going that route is much better than splitting out your fact tables that have the same granularity.

Feel free to respond with more details if you need more help hashing it out.


To debug this problem, first find out what the session that blocks the others is doing:

SELECT state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid = 29303;  -- or whoever blocks the others

If the session is in state active, it is a long running query. Use EXPLAIN (ANALYZE, BUFFERS) with the query to find out where it spends its time and how to improve that.

If the state is idle in transactions, you have an application bug that forgot to close a transaction. Since all locks are held until the end of the transaction, this blocks other transactions even though it is doing nothing.

The short-term solution is to use the pg_cancel_backend() function to kill the blocking transactions.

The long-term solution is to fix bugs that keep transactions open and to improve concurrency by using fewer and less heavy locks:

The EXCLUSIVE lock that the transactions in your images place on a table are heavy-handed. In my experience, explicit table locks are hardly ever necessary or indicated. Usually they are a sign that the programmer doesn't understand database transactions well.


Looking for solution to add double quotes in field which has commas in SSIS

You should be able to use SSIS CASE syntax in your derived column node as follows:

FINDSTRING(TextLine,",",1) > 0 ? "\"" + TextLine + "\"" : TextLine

The expression basically says - if a comma is found in TextLine, THEN wrap it in double quotes ELSE just keep Textline


Postgres 10 shutting down on installation in Ubuntu 14

listening on IPv4 address "127.0.0.1", port 5432, listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432", database system is ready to accept connections

Just your server starting up.

database system was shut down at 2017-12-16 02:41:39 PST

You always get this message at bootup, it's produced by the "expected state" of the write-ahead log manager.

incomplete startup packet

Nothing happened here.. a client buzzed in and then left., produced by a backend.

received fast shutdown request, aborting any active  transactions, database system is shut down, worker process: logical replication launcher (PID 19050) exited with exit code 1

This is just the postmaster shutting down


The only thing we have done is replaced ScanImage on every row with a
  much smaller image (this is how so much unused space is there)

From doing some experimentation the most space effective method would be to drop the allocation unit and repopulate it (if you have a maintenance window to do this in).

Example code that achieved the best space reduction for me with the table structure in the question is:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRAN

SELECT [ImageID],
       [ScanImage]
INTO   #Temp
FROM   [dbo].[MyTableName]

ALTER TABLE [dbo].[MyTableName]
  DROP COLUMN [ScanImage]

/*Allocation unit not removed until after this*/
ALTER INDEX PK_Image ON MyTableName REBUILD

ALTER TABLE [dbo].[MyTableName]
  ADD [ScanImage] IMAGE NULL

UPDATE [dbo].[MyTableName]
SET    [ScanImage] = T.[ScanImage]
FROM   [dbo].[MyTableName] M
       JOIN #Temp T
         ON M.ImageID = T.[ImageID]

DROP TABLE #Temp

COMMIT

Everything is in a transaction so if the machine crashes it will be rolled back. Could probably do with some error handling or at least SET XACT_ABORT ON. I used SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; to prevent any concurrent modifications from happening during or after the copy and being lost.

The number of LOB pages reserved after reducing the size of an image in all rows was as follows:

+--------------------------------------------------+---------------------+-------------------------+
|                      Event                       | lob_used_page_count | lob_reserved_page_count |
+--------------------------------------------------+---------------------+-------------------------+
| Inserted 10,000 rows with 100,000 byte data each |              135005 |                  135017 |
| Updated all rows to 10,000 byte image data       |               31251 |                  135012 |
| Reorganize                                       |               23687 |                   25629 |
| Drop and re-add image data                       |               13485 |                   13489 |
+--------------------------------------------------+---------------------+-------------------------+


Do you know an easy way to generate one record for each hour of the past 12 hours?

For SQL Server 2005+ you can generate those 12 records very easily with a loop ar a recursive CTE. Here is an example of a recursive CTE:

DECLARE @Date DATETIME
SELECT @Date = '20120308 11:00:00'

;WITH Dates AS
(
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour],
      DATEADD(HOUR,-1,@Date) [Date], 1 Num
    UNION ALL
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,[Date])),
      DATEADD(HOUR,-1,[Date]), Num+1
    FROM Dates
    WHERE Num <= 11
)
SELECT [Hour], [Date]
FROM Dates

Then you just nedd to join it with your events table.


Retrieving specific results from a query

Allow me to start with a review of your table design decisions (or of those that preceded you) that directly led you to this problem, and how to avoid it. If you do not want to read it, go directly to the end, where I suggest on an edit the actual solution.

You have a single table that is crammed by several design entities. While this is not bad per se, you must be aware that you are denormalizing your table design, and that can lead to problems.

I see 3 concepts on the same table:

Employee intrinsic details (First name, Family name)
Department membership
Job position holding by the employee within the department

According to your description, an employee can be part of several departments (and vice-versa) and you are implementing that as a bit field. I find interesting that you have chosen a binary data type, when a  bit or a set data types would be potentially smaller and more legible respectively. However, those data types are usually avoided, not only because they are not very standard, but because they are used to denormalize columns.

Assuming there is a departments table, a better option would be to create a separte table: DepartmentEmployees, like this

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)
JobCode    Char(1)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?":

SELECT E.*
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

Now let's have a look at JobCode. You have to ask yourself if a Job Position is a property of the employee or of the employee within a department. If the latter, please consider adding that property there:

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
JobCode Char(1)
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?", with its Job Code:

SELECT E.*, DE.JobCode
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

This way you can get the JobCode, which can be different for each employee-department tuple (if needed).

You can consider creating (if they do not exist already), separate tables for Department, Jobs (as in, positions, for JobCode) and add unique Primary key everywhere.

In general, creating an intermediate table whenever there is an N:N relationship is a good option, unless there is a reason to denormalize the relationship (leading to more complex code).

Edit: The problem you are facing is horrible from all points- aside from the normalization problems, adding one extra record makes no sense. But here is the wanted solution:

Knowing there is nothing you can do about it, you are lucky because it is a very common query- that not only has a name "GROUPWISE MAXIMUM", it even has its own page on the MySQL manual: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

There is both a subquery and a JOIN solution, I usually like the JOIN solution more, it would go something like this:

SELECT E1.FirstName, E1.Surname, E1.Department, s1.JobCode
FROM Employees E1
LEFT JOIN Employees E2
ON E1.FirstName = E2.FirstName AND E1.Surname = E2.Surname -- use an id for the SELF JOIN if possible, whatever is unique
AND E1.JobCode < E2.JobCode -- change the direction of the comparison depending if you want the lowest or the highest JobCode
WHERE E2.FirstName IS NULL  -- again, use the id if available
  AND E1.Department = ...;  -- or whatever the department filter should look like in your current setup

Allow me to shower after writing such a solution.


Partitioning an existing table into multiple filegroups

Create partition functions and partition schemes on required file groups. Then just rebuild/create the clustered index of required table on related partition scheme. It will involve data movement and take time. You can also use ONLINE = ON for keeping the table available.


Log shipping restore: Only members of the sysadmin fixed server role can perform this operation

I found your question while googling the same errors and solved my issue by giving the monitoring server's agent account sysadmin (sysadmin was not needed for the agent accounts on the primary and secondary)

I think the problem might be caused by having the monitoring server on a separate machine but I could not find any documentation on this.

Running profiler during each logshipping job showed that the jobs explicitly ask if the user is sysadmin before continuing on to update the history tables:

select IS_SRVROLEMEMBER(N'sysadmin')

Initially I tried adding sysadmin to the primary and secondary server's agent accounts but the same errors continued. In the end I discovered that it is only the monitoring server's agent account that needs sysadmin. This allows the msdb.dbo.log_shipping_ tables to be updated with the last backup, copy and restore dates as well as the lsn numbers on the three servers. Then the LS_Alert_ job and the Transaction Log Shipping Status report are populated with the latest values.


How to split a string on every 2nd delimiter?

I created a table value function (taken from here) to create an ordered split of your string.

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS @t TABLE([Index] INT IDENTITY(1,1), Item NVARCHAR(255))
AS
BEGIN
    INSERT @t(Item) SELECT SUBSTRING(@List, Number,
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ORDER BY Number OPTION (MAXDOP 1);

    RETURN;
END
GO

I then used that TVF in the following code

Declare @X table (string varchar(50))
insert into @x (string) values('0,0,1,1,2,1.9,2.9,2.8,3.6,3.5')
SELECT s1.Item AS colx
    ,s2.item AS coly
FROM @x AS x
CROSS APPLY dbo.SplitStrings(x.string, ',') AS s1
CROSS APPLY dbo.SplitStrings(x.string, ',') AS s2
WHERE s2.[Index] = s1.[Index] + 1
    AND s1.[index] % 2 = 1
    AND s2.[Index] % 2 = 0

The code passes in your string into SplitString TVF using CROSS APPLY.  We use TWO CROSS APPLY's.  One for column X (alias s1) and one for column Y (alias s2).

Each table (s1 and s2) have the exact same rows ordered.  I'm basically using the logic that you commented on in your question.

  Hmmm could i split on each value and use use odd/even row numbers to split into x,y?

We know the X sequence values are odd and the Y sequence values are even.  The logic is making sure the s2 index (which represents the Y-column) is +1 from the s1 row (which represents the X-column).

WHERE s2.[Index] = s1.[Index] + 1

We want to make sure the s1 rows are only the odd ones by using the modulus     operator (%).  If the remainder of the s1.index / 2 = 1, we have an odd row

AND s1.[index] % 2 = 1

We want to make sure the s2 rows are only the even ones by using the modulus operator (%).  If the remainder of the s1.index / 2 = 2, we have an even row

AND s1.[index] % 2 = 0

It seems to work on your sample data.

| colx | coly |
|------|------|
| 0    | 0    |
| 1    | 1    |
| 2    | 1.9  |
| 2.9  | 2.8  |
| 3.6  | 3.5  |


This is login and permission related error. What kind of account are you using to connect when you build the AOA features? If you are using a domain account you can fix this error it in two ways.

Use the same domain account on all of the participating replicas. If you can't (allowed) change the account you could use the second option
Add the service accounts that you are using on participating servers into the primary replica.

N.B. make sure those accounts have at least a connect permission to the instances


SQL Server: How to create a table that fills one 8 KB page?

A row is maximum 8060 bytes but there is row overhead included.

CREATE TABLE dbo.FullPage1 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL)
GO

If you change filler2 to char(50) it breaks with

  Creating or altering table 'FullPage1' failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The 8060 is over all columns. There is no column overhead except the NULL bitmap (see later)

CREATE TABLE dbo.FullPage3 (
    ID int NOT NULL,
    filler1 char(2683) NOT NULL,
    filler2 char(2683) NOT NULL,
    filler3 char(2683) NOT NULL)
GO

Here, data is 8053 bytes with overhead of 2 bytes record type) + 2 bytes NULL bitmap pointer + 3 bytes NULL bitmap.

Even though we have no NULLable columns, the NULL bitmap exists. When we add a NULLable column, we don't increase the row size.

CREATE TABLE dbo.FullPage3 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL,
    filler3 varchar(8000) NULL)
GO

This gives a warning of course

  Warning: The table "FullPage3" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.

The NULL bitmap is "one bit per column". Not just NULLable columns. Uncomment filler6v and it fails with "... including 8 bytes of internal overhead ..."

CREATE TABLE dbo.FullPage4 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL,
    filler1v varchar(10) NULL,
    filler2v varchar(10) NULL,
    filler3v varchar(10) NULL,
    filler4v varchar(10) NULL,
    --filler6v varchar(10) NULL,
    filler5v varchar(10) NULL
    )
GO

For more info about on-disk structures, see Inside the Storage Engine: Anatomy of a record. Interestingly, where is the 4 byte row header? This appears excluded from the 8060 bytes.

Tested on SQL Server 2008 SP1 and SQL Server 2005 SP3


Difference between candidate key & primary key

I think this post does a pretty good job of explaining the difference.

To summarize:

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key.


Trigger table name as variable into stored procedure

MySQL provides no way to do what you want.

The obvious suggestion would be to modify the triggers and the procedure to add one more parameter, which is the table name. But since it's obvious, you probably don't want to do so. Probably because the triggers and the procedure run in production, and they cannot be updated atomically (so if you modify the procedure first, triggers will fail; but if you modify a trigger first, it will fail).

If that it the reason, I suggest to use a user variable. In the triggers, you can do something like:

SET @table := 'wood_table';

And in the procedure, you will be able to check the value of @table.


DR Site availability if primary goes down

Technically, yes, you should still be able to read from the DR instance assuming it is configured as a Read Only replica and you are connecting to it directly (As read only routing through the primary would not be working).

You should really be concerned in this case though about bringing the DR site online as the new primary replica.  You would need to force quorum on the DR site, and manually failover (with possible data loss) so you can then accept reads and writes at the DR site.

Perform a Forced Manual Failover of an Availability Group (SQL Server)


Customizing nls_sort and nls_comp in Oracle

I think the Oracle Locale Builder utility is the tool that you want. The documention on the 10.2 version (since you've tagged with oracle-10g) of this utility is here: http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch13custlocale.htm


use of coalesce to check for all variables being null

As long as all the variables involved are of datatypes compatible with sql_variant (they are in this case - basically no LOB datatypes, CLR types, or user defined datatypes) then you can use

SELECT CASE WHEN COALESCE(
    CAST(@StartDate as sql_variant),
    @EndDate,
    @StateCode,
    @CountyCode,
    @ProducerName,
    @TaxID,
    @Farm) IS NULL
THEN 'yes' ELSE 'no' END

It is only necessary to cast one of the arguments as COALESCE

  Returns the data type of expression with the highest data type
  precedence.

And sql_variant has a very high datatype precedence (only beaten by user-defined data types and they would prevent the method from working anyway).

Personally I don't find this any more understandable then just finding the conjunction of the individual IS NULL results though.


MongoDB in ReplicaSet databases showing different sizes and objects

The rsync process will also apply a de-fragmentation for the MongoDB data files on the crashed primary replica. That's why you see the data file reduced in size on the primary after crashed.

This effect is the same if you run the compact command on your secondary to reclaim the fragmented storage caused by data CRUD operations over time.


It means either it's permission issue or you are running out of space on target.

Permission Issue

The service account of SQL Server (which used to start SQL Server service) does not have access to the target.

START > RUN > type SERVICES.MSC.

Find SQL Server Service > Right click and go to PROPERTIES.

Go to LogOn/Security tab

If it's system account (NETWORK SERVICE, LOCAL SYSTEM etc.) then change it to any specific account (domain account like ex. dba\sqlserviceact).

Then restart SQL Server Service.

And grant the account (ex. dba\sqlserviceact) read & write privilege on the shared folder (network path).

If it's other then system account the grant the account ready/write privilege.

Thanks


You could try refactoring the query to use equivalent, but different, syntax.

IN is more-or-less an inner join. So your query could become

...
FROM cte_table JOIN tables JOIN more tables
...

For your query a row has to be in the CTE output to be returned. The same applies to this inner join syntax. The two queries are equivalent.

Although you and I know these two are interchangeable the query optimizer doesn't always realize this. So by giving it a different place to start from you can (sometimes) end up with a different plan.

Really need to see the table definitions and the "slow" plan for a proper answer, though.


How to migrate a database that has a database master key and certificate for encryption

I need to migrate this database to another instance of SQL, but after I migrate the database, I cannot decrypt the information.

You have a database master key (DMK), which is protected by both the service master key (SMK) and a password. Since you're migrating to a different instance of SQL Server you can still access the DMK through the password, however automatic key decryption will no longer work because the SMK has changed.
The fix is easy enough. When you restore the database to the new instance, run the following:
use databasename
go

open master key decryption by password = 'MyPassword'
go
alter master key drop encryption by service master key
go
alter master key add encryption by service master key
go
close master key
go

This will drop the encryption of the DMK by the SMK, since it can't decrypt it on the new instance. Then it will add the encryption through the SMK back to the DMK so that automatic key decryption will work.
The reason I don't say to backup and restore the SMK, as the other answer shows, is that it affects all of the databases on the instance whereas just fixing the broken one won't cause any other issues on any current databases.


How to loop through all table rows and perform subquery using values of same row?

Answer based on comments originally left by Akina

UPDATE table
SET valA = xml.value(_parsing queryA_),
    valB = xml.value(_parsing queryB_)

When you use a column name in the right-hand side of an assignment expression (parsing expression in your case) it is referenced to a column value in the same record that is currently being updated (if you want to think about an update like it is a record-by-record process). It is always the same row field. Referencing a column in another row is always more complex (needs a window function or another reference to the table).

To continue processing a partially-updated you could add WHERE reference > last_ref to the SET clause of the UPDATE. Your WHERE clause will determine whether each record will be updated or not.

A better way may be to check valA and/or valB values for NULL - if not then the record was already processed previously. For example UPDATE ... WHERE valA IS NULL (an index on the tested field will help). If it is possible that some of the columns will remain null after processing, it is a good idea to set it to some non-NULL value (empty string or single space, for example).


Should I denormalize lat & lon on area searches?

I don't see why would't you just store the coordinates for every user. You can keep them either in the same table as users or in some kind of user details table, depending on how your current schema is designed. There is no point in having all geo to postcode locations mapped.

To get the locations you can use something like google maps API.


SUPER privilege not defined for master user in Amazon MySQL RDS

You can try to change log_bin_trust_function_creators; however, there is an alternative approach that seems more appropriate when you consider the meaning of that variable:

It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log.
A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation.

All that option does is assume that you know what you are doing, without making you assert that you do by using one of the three characteristics in your CREATE statement... but if you don't properly declare the function, you may miss out on potential optimizations.

misdeclaring a routine might affect results or affect performance

Taken together, this implies that the most correct approach is to declare your stored functions with DETERMINISTIC or READS SQL DATA or NO SQL as appropriate, and, if these do not correctly describe your function's behavior, then your function still may result in unsafe statements being written to the binary log, because these options are also "trusted:"

Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results.

Curious aside: astute observers will note that I omitted something from the documentation's description:

If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege.

Since nobody gets SUPER in RDS, and assuming this is not an error in the official documentation, this seems like it must be an AWS customization of MySQL behavior.


How to index two tables for JOINed query optimisation

For starters, get your LEFT JOIN right and try to get rid of total rows (as discussed in comments):
Query
Updated to your comment:

I only want to returns rows from request_logs that have a parallel entry in response_logs with non nil status:

SELECT q.uuid
     , s.status
     , q.method
     , q.requesturi
     , q.accessed
     , q.payload reqpayload
     , s.payload respayload
--   , COUNT(*) OVER() AS total_rows  -- see below
FROM   request_logs q
JOIN   response_logs s USING (uuid)   -- not LEFT JOIN
WHERE  q.accountid = 2
AND    q.requesturi NOT ILIKE '/v1/sessions%'
AND    q.accessed BETWEEN '2018-01-01 15:04:05 +0' AND '2019-01-02 15:04:05+0'
AND    q.applicationid = 1
AND    s.status IS NOT NULL           -- see below
ORDER  BY q.accessed DESC
LIMIT  1001;                          -- see below

Your LEFT [OUTER] JOIN burns down to a plain [INNER] JOIN. Related:

Query with LEFT JOIN not returning rows for count of 0

COUNT(*) OVER() AS total_rows is expensive for counts substantially bigger than LIMIT (and you are expecting "10m+ rows"). Maybe it's good enough to use LIMIT 1001, only use the first 1000 rows, check the row count and if it's 1001 then you know there are "more than 1000 matching rows". Related:

Best way to get result count before LIMIT was applied

Indexes
If (like you commented)

the only 'moving parts' in this query are the dates (from and to) that are compared with accessed

... and more than a few rows are excluded with the other predicates
on applicationid, requesturi and accountid, then a partial index should help read performance (a lot):
CREATE INDEX ON request_logs (uuid, accessed DESC)
WHERE  accountid = 2
AND    requesturi NOT ILIKE '/v1/sessions%'
AND    applicationid = 1

And if a substantial percentage of rows is eliminated by status IS NOT NULL, also:
CREATE INDEX ON response_logs (uuid)
WHERE  status IS NOT NULL;

It may pay to append the columns status and payload as index columns if you can get index-only scans out of it. Some preconditions apply.


SQLite's pragmas are specific to SQLite; you won't find them (or indeed the PRAGMA statement) in other engines.

As for database "journalling", that's just SQLite's name for its transaction log (see the entry for journal_mode in SQLite's reference). The transaction log is a very different thing to the file-system journal and generally not something you want to disable. Without a transaction log you won't be able to rollback transactions and (as the linked documentation notes): "If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt."

The file-system journal helps ensure that the file-system is consistent (shortly) after a crash; but that says nothing about whether the internal structure of files in that file-system is consistent from the point of view of an application (e.g. SQLite).

In other words: no, it's not redundant and most other databases don't provide any option to disable the transaction log. I'd suggest if your inserts and updates are going slowly, there's probably other reasons (how many changes are made per transaction, etc.)

Journaling is critical to enabling databases to provide the ACID (Atomic, Consistent, Isolated, Durable) guarantees they promise.
SQL-ite supports modes:  DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF

DELETE, TRUNCATE, PERSIST specify different ways of handling the journal file after each transaction commit.
WAL - Write-Ahead Logging allows the database file to be updated in-place.
MEMORY, OFF - Allow inconsistent state on crash, although OFF "allows the database file to be corrupted using ordinary SQL"

For performance, Write-Ahead Logging (WAL) can sometimes be helpful, or using a database designed for multiple concurrent writers.


Why include tomorrow (CURRENT_DATE + INTERVAL '1 DAY')? A future date in order_creation_date would normally indicate a data error. I would not even include today in the statistic, since that is typically incomplete and misleading until the day is over. Consequently I use the last 30 days up until yesterday in my query.

Assuming order_creation_date is an actual date, just GROUP BY and ORDER  BY that date, that's simpler an cheaper than using extracted month and day numbers for the same. If it's not a date, but a timestamp or timestamptz, just cast to date to group by the day: o.order_creation_date::date. Be aware that the result depends on the timezone setting of the session when dealing with timestamptz (Day boundaries are defined by the time zone.)

Also assuming you want a result row for every single one of the 30 days. To make sure, days without orders don't go missing, generate the series of days with generate_series() and LEFT JOIN to that. You can generate timestamps, but I use the simpler integer variant while we are operating with dates. We can just subtract an integer from a date to subtract days.

SELECT CURRENT_DATE - i AS date
     , sum(oi.invoice_amount) AS daily_total_invoice_amount
FROM   generate_series (1, 30) i -- to return exactly 30 rows
LEFT   JOIN orders      o ON o.order_creation_date = CURRENT_DATE - i
LEFT   JOIN order_items oi USING (order_id)
GROUP  BY i    -- effectively group by days
ORDER  BY i;   -- effectively descending dates


How to change page size in an existing sybase database

Take a look at the dbunload utility, particularly the -an and -ap switches. You didn't say what version you are using, but here are the docs on v12.0.1.


Joining to same table and updating column on row match for multiple columns

You can do it using a self-join and an aggregate function (string_agg) to aggregate the identifiers that are different from the current row as below.

The first and last lines initializes the status column to NULL and finalizes it to foo if it isn't assigned, respectively:

UPDATE table_1 t1 SET status = NULL;

UPDATE table_1 t1
SET status = (
  SELECT string_agg(t2.identifier,',') FROM table_1 t2
  WHERE t2.identifier != t1.identifier
    AND t1.col1 = t2.col1
    AND t1.col2 = t2.col2
    AND t1.col3 = t2.col3
    AND t1.col4 = t2.col4
);

UPDATE table_1 t1 SET status = 'foo' WHERE status  IS NULL;


Yes, it's safe. Use SIGTERM (the default, signal 15 on Linux) not SIGKILL (signal 9). If it doesn't complete it might waste recent work and have to repeat it.

However, you should really just let autovacuum do its job.


PostgreSQL - Removing Unused Index on Check Constraint column

will removing this index impact the check constraint performance?

No, it will not.
Check constraints only operate on the columns of a single row, not on multiple rows. So it is completely unaffected by the existence (or absence) of an index.


Why CONTROL permission on certificate to be able to use it?

With security in SQL Server, one needs to be very watchful that permissions are not indirectly granted when not intended. Designing granular permissions for the SQL Server system must be a very meticulous task for Microsoft. Once created, the certificate can be altered, but only to drop the private key. View definition lets the user see the metadata, however when it comes to the private key this is not enough because it is encrypted by the database master key, which, in turn is encrypted by the service master key. Besides, the documentation states that "view definition does not imply access to the securable itself". References permission as well is not enough for a certificate, however, it is enough to use a symmetric key. The user of encryption needs control permissions.

There are several very good reasons why the other answer recommends designing stored procedures to read and write encrypted data using code signing. Anyone with the control permission on a certificate can back up the certificate and private key to the file system. By code signing, you don't need to grant control directly to any user as the additional permissions are made available by associating a certificate user with permissions with the procedure through code signing.  Also, anyone with control permissions can grant permissions on the securable to others, which could lead to some surreptitious activity. The db_owner role, db_securityadmin and the db_ddladmin role have CONTROL permissions on certificates automatically and the db_backupoperator role can back up the database.

Although it seems that you are asking about column encryption. Certificates with SQL Server are created in master and msdb for other purposes, such as Transparent Data Encryption and  backup encryption. Stolen certificates can be used to defeat these. So don't allow anyone in the roles mentioned above in master or msdb.


DROP TABLE IF EXISTS test_comments;
Query OK, 0 rows affected (0.08 sec)

CREATE TABLE test_comments (ID INT, name CHAR(30)) COMMENT 'Hello World';
Query OK, 0 rows affected (0.22 sec)

Check you comments in table structure

show create table test_comments\G
*************************** 1. row ***************************
       Table: test_comments
Create Table: CREATE TABLE `test_comments` (
  `ID` int(11) DEFAULT NULL,
  `name` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Hello World'
1 row in set (0.00 sec)

You can also check comments from information_schema like below

SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'test_comments';
+---------------+
| TABLE_COMMENT |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)

Alter table to modify comments

ALTER TABLE test_comments COMMENT = 'This is just to test how to alter comments';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check the modified comments

show create table test_comments\G
*************************** 1. row ***************************
       Table: test_comments
Create Table: CREATE TABLE `test_comments` (
  `ID` int(11) DEFAULT NULL,
  `name` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='This is just to test how to alter comments'
1 row in set (0.00 sec)

SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'test_comments';
+--------------------------------------------+
| TABLE_COMMENT                              |
+--------------------------------------------+
| This is just to test how to alter comments |
+--------------------------------------------+
1 row in set (0.00 sec)


How to select only the number from "show global status like 'Com_insert';"

If you are using MySQL 5.1+, you can get the number alone in MySQL client

mysql> select VARIABLE_VALUE from information_schema.global_status
    -> where VARIABLE_NAME = 'Com_insert';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 479512448      |
+----------------+
1 row in set (0.00 sec)

mysql>

No matter what version of MySQL 5.x, you can also get the number in the OS:

[root@*** ~]# mysql -root -p -ANe"show global status like 'Com_insert'" | awk '{print $2}'
479512506


What does pg_stat_all_tables.idx_scan actually mean?

Whenever an index is used, that counts as an index scan.  There is no separate counter for full index scans.  You could compare idx_scan to
idx_tup_fetch and see how any rows are returned from the index per scan on average.  But what is the point?  For the most part, it fetches the number of rows it needs to in order to do the job you assigned it.  Maybe you are missing an index that could do the job better, but looking at this stat will not tell that that is the case, nor tell you what hypothetical index that might be.

I do a lot of performance investigation and almost never look at those values, unless I'm trying to tune/debug autovac.  EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, auto_explain are the right tools for the job.


Difference Between a Staging and the Production DWH

I think this is a case of the same word being used to describe two different things.

The first being a staging environment. As you mention, this is a near copy of the production environment architecture and can be used to test releases that are due to be moved into production or to allow users to view upcoming features before they are released.

The second is a data staging area. Although it is called staging, it could actually be located within the production environment (as has been the case in most companies that I have worked for). This area is used to cleanse and validate data extracted from OLTP systems before being loaded into a data warehouse.

Generally, at least in my work history, I have found that production data may not be copied into the staging environment due to legal or logistical constraints. Mostly I have found that it is because developers/testers should not access sensitive data such as personal details, credit cards etc... but it can be copied into a data staging area in preparation of loading it into the data warehouse.

Over time, as you gain more experience it gets easier to tell which one an individual is referring to - but until then, just remember that live data should not leave the production environment and it is perfectly fine to have a dedicated area within production in which you can process your data.


Do I need to reenter old data after adding an index to a table?

According to the comments posted for your question, @ypercube is right. It is no. Here is why in terms of Storage Engine under /var/lib/mysql (default datadir):
InnoDB (innodb_file_per_table enabled)
With innodb_file_per_table enabled, table data and its indexes live in the database folder next to the .frm file. For example, for the table mydb.mytable, the manifestation of that InnoDB table outside of ibdata1 would be:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

InnoDB (innodb_file_per_table disabled)
With innodb_file_per_table enabled, table data and its indexes live in the system tablespace file better known ibdata1. The data pages and index pages are 16KB blocks where rows and TEXT/BLOB overflow are managed.
MyISAM
For the MyISAM mydb.mytable, three files comprise its logical presence

/var/lib/mysql/mydb/mytable.frm is table structure header
/var/lib/mysql/mydb/mytable.MYD holds the data only
/var/lib/mysql/mydb/mytable.MYI holds the indexes (BTREE, FULLTEXT)

EPILOGUE
Regardless of Storage Engine, data pages and index pages are separate and distinct entities. Under the hood, when you issue the command
ALTER TABLE mydb.mytable ADD INDEX new_index (col1,col2);

a complete copy of the data is made. That complete copy is scanned and all index pages are migrated along with new index pages for the new_index index.


Sqlite Create Table with Check column from other table

Looking up a value from another table would require a subquery, but the documentation says:

  The expression of a CHECK constraint may not contain a subquery.

There is no such restriction on a trigger's WHERE clause:

CREATE TRIGGER employee_salary_check
BEFORE INSERT ON employee
WHEN NEW.salary < (SELECT min FROM position WHERE id_position = NEW.id_position)
  OR NEW.salary > (SELECT max FROM position WHERE id_position = NEW.id_position)
BEGIN
  SELECT RAISE(FAIL, "invalid salary");
END;