This may be the problem:

query_cache_size = 200M

Don't set it more than 50M.  When a write to a table occurs, that cache must be scanned to purge any entries relating to that table.  (Most production systems actually benefit from turning the QC off.)

Are you using MyISAM?  Or InnoDB?  The following values assume a mix of the two, and is sub-optimal for the typical case (all InnoDB):

key_buffer_size                = 512M
innodb_buffer_pool_size= 8G

5.5.58?  That's 3 major revisions behind.

You can only rarely "tune your way out of a performance problem".  Hopefully, the slowlog will point out that it is simply a matter of adding a composite index or reformulating a query.

Analysis of STATUS and VARIABLES

Observations:

Version: 5.5.58-log
30 GB of RAM
Uptime = 18:00:31; some GLOBAL STATUS values may not be meaningful yet.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Your dataset seems to be rather small, so the small setting for innodb_buffer_pool_size is actually OK, at least until the dataset grows significantly.

Shrink query_cache_size to 50M to improve performance.

The frequency of full table scans and range scans indicates the need to look into the slowlog.  Set long_query_time=1

Are the SAVEPOINTs working well for you?

Details and other observations:

( innodb_buffer_pool_size / _ram ) = 8192M / 30720M = 26.7% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (512M / 0.20 + 8192M / 0.70) / 30720M = 46.4% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 5,000 -- Number of table descriptors to cache
-- Several hundred is usually good.

( innodb_buffer_pool_size ) = 8192M -- InnoDB Data + Index cache

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 396,447 * 16384 / 8192M = 75.6% -- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 396,447 / 524280 = 75.6% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 64,831 / 60 * 512M / 258605568 = 2,243 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 110,734 / 105511 = 1.05 -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( innodb_stats_on_metadata ) = innodb_stats_on_metadata = ON -- Re-analyze table when touching stats.
-- ON is likely to slow down certain SHOWs and information_schema accesses.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( net_buffer_length / max_allowed_packet ) = 16,384 / 32M = 0.05%

( query_cache_size ) = 200M -- Size of QC
-- Too large = too much overhead. Recommend either 0 or no more than 50M.

( Qcache_lowmem_prunes/Qcache_inserts ) = 942,628/2529864 = 37.3% -- Removal Ratio (pct of time need to prune)

Qcache_lowmem_prunes = 15/second

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (200M - 149143456) / 29625 / 8192 = 0.25 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size

(Some of the other STATUS values indicate that the QC is effective.)

( Select_scan ) = 163,611 / 64831 = 2.5 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 163,611 / 2592449 = 6.3% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( expire_logs_days ) = 1 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin = OFF)

( log_slow_queries ) = log_slow_queries = OFF -- Whether to log slow queries. (Before 5.1.29, 5.6.1)

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 1

( Connections ) = 97,624 / 64831 = 1.5 /sec -- Connections
-- Increase wait_timeout; use pooling?

( thread_cache_size ) = 50 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
0 is inefficient for non-Windows; 10 is probably fine.
More than 100 may lead to OOM.

( thread_cache_size / Max_used_connections ) = 50 / 25 = 200.0%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 5,435
interactive_timeout = 50
query_cache_limit = 262,144
query_cache_min_res_unit = 2,048
wait_timeout = 50

Abnormally large:

Com_release_savepoint = 1.6 /HR
Com_savepoint = 1.6 /HR
Handler_savepoint = 3.2 /HR
Key_write_requests = 407 /sec
Qcache_free_blocks = 18,324
Select_range / Com_select = 34.2%
innodb_spin_wait_delay = 24
innodb_sync_spin_loops = 200
max_user_connections = 150
table_definition_cache = 4,096

Abnormal strings:

have_symlink = DISABLED
innodb_fast_shutdown = 1
tx_isolation = READ-COMMITTED

Tentative conclusions

Nothing jumps out to explain why slows down.
There could be some good clues in the slowlog -- such as table scans that don't show up until some time after startup.


Can I rely on the order of columns on an index generated by database tuning advisor

You need to provide more info, specifically why do you expect the order you expect. An index on (A, B, C) is completely different from an index on (B, A, C) and one cannot replace the other. The DTA may be wrong sometimes, but is way more often right rather than wrong.


CREATE FILE encountered operating system error 5 (Access is denied.)

You are getting a permissions error.  The account which is running the SQL Server doesn't have the needed rights on the folder which will contain the database files.

You need to give the account which is running SQL Server (not your account) full control of C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA.


Passing info on who deleted record onto a Delete trigger

Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Yes: by using a very cool (and under utilized feature) called CONTEXT_INFO. It is essentially session memory that exists in all scopes and is not bound by transactions. It can be used to pass info (any info--well, any that fits into the limited space) to triggers as well as back and forth between sub-proc / EXEC calls. And I have used it before for this exact same situation.

Context info is a VARBINARY(128)
Set via: SET CONTEXT_INFO
Get via: CONTEXT_INFO()

Test with the following to see how it works. Notice that I am converting to CHAR(128) before the CONVERT(VARBINARY(128), ...  This is to force blank-padding to make it easier to convert back to VARCHAR when getting it out of CONTEXT_INFO() since VARBINARY(128) is right-padded with 0x00s.

SELECT CONTEXT_INFO();
-- Initially = NULL

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), 'I deleted ALL your records! HA HA!')
                          );
SET CONTEXT_INFO @EncodedUser;

SELECT CONTEXT_INFO() AS [RawContextInfo],
       RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())) AS [DecodedUser];

Results:

0x492064656C6574656420414C4C20796F7572207265636F7264732120484120484121202020202020...
I deleted ALL your records! HA HA!

PUTTING IT ALL TOGETHER:

The app should call a "Delete" stored procedure that passes in the UserName (or whatever) that is deleting the record. I assume this is already the model being used since it sounds like you are already tracking Insert and Update operations.
The "Delete" stored procedure does:

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), @UserName)
                          );
SET CONTEXT_INFO @EncodedUser;

-- DELETE STUFF HERE

The audit trigger does:

-- Set the INT value in LEFT (currently 50) to the max size of [UserWhoMadeChanges]
INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges)
   SELECT del.ID, COALESCE(
                     LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50),
                     '<unknown>')
   FROM DELETED del;

Please note that, as @SeanGallardy pointed out in a comment, due to other procedures and/or ad hoc queries deleting records from this table, it is possible that either:

CONTEXT_INFO has not been set and is still NULL:

For this reason I have updated the above INSERT INTO AuditTable to use a COALESCE to default the value. Or, if you don't want a default and require a name, then you could do something similar to:

DECLARE @UserName VARCHAR(50); -- set to the size of AuditTable.[UserWhoMadeChanges]
SET @UserName = LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50);

IF (@UserName IS NULL)
BEGIN
   ROLLBACK TRAN; -- cancel the DELETE operation
   RAISERROR('Please set UserName via "SET CONTEXT_INFO.." and try again.', 16 ,1);
END;

-- use @UserName in the INSERT...SELECT

CONTEXT_INFO has been set to a value that is not a valid UserName, and hence might exceed the size of the AuditTable.[UserWhoMadeChanges] field:

For this reason I added a LEFT function to ensure that whatever is grabbed out of CONTEXT_INFO will not break the INSERT. As noted in the code, you just need to set the 50 to the actual size of the UserWhoMadeChanges field.

UPDATE FOR SQL SERVER 2016 AND NEWER

SQL Server 2016 added an improved version of this per-session memory: Session Context. The new Session Context is essentially a hash table of Key-Value pairs with the "Key" being of type sysname (i.e. NVARCHAR(128) ) and the "Value" being SQL_VARIANT. Meaning:

There is now a separation of values so less likely to conflict with other uses
You can store various types, no longer needing to worry about the odd behavior when getting the value back out via CONTEXT_INFO() (for details, please see my post: Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?)
You get a lot more space: 8000 bytes max per "Value", up to 256kb total across all keys (compared to the 128 bytes max of CONTEXT_INFO)

For details, please see the following documentation pages:

sp_set_session_context
SESSION_CONTEXT


Insert into Table ignoring duplicate values

If I run into a Violation of UNIQUE KEY constraint the first thing I do is ask myself the following questions:

Does the duplicate key already exist in the target table?
Is the key duplicated in any source table?

From answering the above you will have a good idea of where the duplicate value is sourced from and a reasonable idea of the cause. Example possibilities are:

Duplicate exists in source table
Duplicate does not exist at source, but a transform of the data causes duplicates to be returned

Ultimately you need to strip this problem back to basics, as you have the duplicate value in question you need to start digging into the data and pay close attention to the source query. Luckily in your example it is a simple query and you can easily strip out joins to see if any of those introduce the duplicate, or if indeed the duplicate exists due to non-key attributes being different therefore causing your DISTINCT to not do what you expect it to do.

I guess what I am trying to say is that there isn't necessarily a quick win for this kind of problem and you need to do some good ol' fashion detective work using the clues provided by SQL.

Moot point, but I like to explicitly list the columns on an insert so that nothing breaks if the order of columns on the target table is different to that of the source query.


The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values


A possible bug for not displaying the constraint name with bulk DML and SAVE EXCEPTIONS clause:

Bug 7210333 : CONSTRAINT NAME IN SQLERRM IS NOT OUTPUT WHEN SAVE EXCEPTIONS IS USED

To catch the records causing the error, you could use DML error logging as well:

create table t1(c1 number unique);

begin
  dbms_errlog.create_error_log('T1', 'T1_ERRORS');
end;
/

Insert data:

declare
  type t_varray is varray(4) of number;
  l_varray t_varray := t_varray(1, 2, 2, 3);
begin
  forall i in 1..l_varray.count
  insert into t1 values (l_varray(i))
    log errors into t1_errors
    reject limit unlimited;
end;
/

Actual data:

select * from t1;

        C1
----------
         1
         2
         3

Rows violating the constraint with row data and constraint name:

select * from t1_errors;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                ORA_ERR_ROWID$       ORA_ERR_OPTYP$       ORA_ERR_TAG$         C1
--------------- ------------------------------------------------------------ -------------------- -------------------- -------------------- ----------
              1 ORA-00001: unique constraint (BP.SYS_C0013693) violated                           I                                         2


I need to do periodic commits in my purge function

Transaction control (commit, rollback) is not allowed inside PostgreSQL functions.
Starting with PostgreSQL 11, it's possible in procedures (see CREATE PROCEDURE in the documentation). In fact that's the main difference between functions and procedures.
Before PostgreSQL 11, the ability to commit inside a loop comes from client-side beyond-SQL programming. The psql CLI can of course issue commit and rollback but it doesn't have some of the basic constructs found in programming languages, such as loops.


It seems that Oracle doesn't like ANSI-style joins in the materialized view definition when refereshing...

Changing the definition to

create materialized view dept_emp_mv as
   select dname, job, hiredate, count(*) as nbr
               from emp e, dept d
               where e.deptno = d.deptno
               group by dname,hiredate,job;

makes it work for me.

See http://sqlfiddle.com/#!4/f706b/1

Changing the definition in the SQL Fiddle back to the one included in the question will cause the staleness to be COMPILATION_ERROR.

Personally, I would classify this as a work-around rather than an answer.


How to implement posts with “seen by” like facebook?

I think the main thing to consider here is how you store who have seen what.  The most convenient way is to store the post-user pairs.  Any solution with less granularity will have some cases hard to handle - your second suggestion, if I understood it correctly, would store a group-user pair, plus a flag that all posts are already seen (I imagine that this would be rather the last post ID at the moment of joining the group).  This would only work if you can't miss posts, so that the unseen ones form a contiguous group, all having a bigger ID than all already-seen ones.

Since the user-post pairing consists exactly these two fields, which can be, expecting extremely high user and post numbers, 2×8 bytes per row, I wouldn't expect performance problems with 10,000 users and 100,000 posts (provided there are really groups, I mean, not all users belong to all groups and be able to read all posts - in this case, the table would be approximately 30 GB in size (plus the same size for the index necessary for the primary key).  This size can only be handled by appropriate hardware and some magic, like archiving old posts as those are queried rarely and so on.  If your system exceeds that size, you probably make nice money of it, being able to beef your system up :)


MariaDB doesn't set authentication_string during installation

Unlike newer versions of MySQL (5.7+), MariaDB doesn't seem to use the authentication_string of the user table for normal passwords when using the default plugin (i.e. mysql_native_password). Instead, the passwords are found in the password column, like in older versions of MySQL.

However, you can get something into the authentication_string if you create a user using e.g. the pam plugin:

CREATE USER u1@localhost IDENTIFIED VIA pam USING 'mariadb';

(authentiation_string now contains 'mariadb' in plaintext for this user, though this is not the password, just a reference to a PAM configuration file. With PAM the password is no longer stored in MariaDB.)


Is a Postgres long-running query aborted if the connection is lost/broken?

"It depends".

If the client vanishes due to network connection loss the query will generally run until it's retrieved enough rows to fill its network send buffer, then stop and get stuck until the TCP connection drops, at which point it'll abort. If it completes before it fills the TCP send buffer it'll complete successfully, so if it's autocommit the query will commit.

If the client is killed in a way that the client's operating system can report to the server via a TCP RST (like a client segfault/crash, SIGTERM, SIGKILL, etc), the PostgreSQL server will set the interrupt flag. Next time the query checks for interrupts as it executes it will see the flag and abort. Sometimes a query might be doing CPU-heavy work within code that doesn't check for interrupts - some extensions, and a few places within the PostgreSQL core - in which case it might not notice the interrupt for a long time and keep on running. It'll pretty much always see the interrupt and abort before completing and committing if it's autocommit, though.

If the client is killed by something like a sudden OS reboot, so that the client host suddenly knows nothing about the TCP connection but can still respond on the network, the query will probably get aborted the first time it triest to write a row, like Jeff said, because the client's host will send a TCP RST in response to the first packet sent by the server after the reboot. PostgreSQL checks for interrupts at each row it sends.

This behavour isn't configurable. As far as PostgreSQL is concerned if the client goes away its job is to terminate any queries the client was running.  To change that you'd need some kind of query completion token that you could obtain at query start, then use to ask the server about the query via another connection later. Essentially you'd have to implement asynchronous/background queries. Possibly a nice feature, but not currently supported.

If the query is autocommit, or if your query was a COMMIT that was in-flight at the time you killed the client/lost the connection, it is possible for a transaction to be in an indeterminate state where the client doesn't know whether or not it committed. There's no real way to find out, other than looking for the effects of the transaction on the data.

Where that is unacceptable you can use two phase commit and a client-side transaction manager.


It's better not to skip by hundreds. Just loop one-by-one:

until mysql -e "show slave status\G;" | grep -i "Slave_SQL_Running: Yes";do
  mysql -e "stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;";
  sleep 1;
done


In logical modelling one tends to record the natural keys to entity types. These will often require the "parent" and hence weak entity types are documented. Consequently they can show the scope of re-use of particular values. Take the cannonical Customer-Order-Order Line-Product example. Although the Order will have Customer Number as a foreign key it is usual for Order Numbers to be globally unique so Order is a strong entity. Often each Order's line numbers will start again at 1. By making Order Line weak we document this possibility more transparently.

In physical design we may choose to implement "weak" tables to reduce the number of joins in some queries. Continuing the example, if we make Customer Number part of the key to Order and also to Order Line then a query to count how many widgets the Jones Trading Company has ordered need only touch one table. Needless to say this involves costs in other areas and should be weighed carefully before implementation.


It is possible to rename an enum entry, so the cast is not IMMUTABLE.

If you are sure that you will never rename enum values, write your own conversion function that uses a cast internally and label the function IMMUTABLE. Then PostgreSQL will trust that label, and it is up to you to fulfill the promise.


Not using the GUID but using the T-SQL procedure:

sp_stop_job

Could help resolve issues like the one you are having.

Example:

exec msdb.dbo.sp_stop_job @job_name = 'enterjobnamehere'

Checking the current running queries for any queries started by the agent and killing these could also help.

An example of adapting the script to find running queries started by the agent service:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_Sessions s
on req.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE [Program_Name] like 'SQLAgent%';


Storing and retrieving raw XML in Oracle table

You can store it as a straightforward VARCHAR2 column or use the data type of XMLType. This will make it easier to query using XQuery directly if that is something you envisage.

This is Oracle's XML developer guide - https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAGCBGJ


MS SQL Server Performance Issues After Enabling All CPU's

There is 3 NumaNodes and 12 CPU's in them. 6 of them was checked.
  Yesterday, i have checked all of them (12 CPU's) and after that
  performance issues started.

Processor affinity

You should look into these affinity settings, disabling / enabling these binds specific schedulers to specific cores. I would not advise to manually change these unless you absolutely know what you are doing.
More on setting processor affinity here and here.

If you must do this, look into enabling traceflag 8002 as a startup parameter and restarting SQL Server. This way the schedulers are not bound to cores in a 1 on 1 relationship.

The easier solution is letting SQL Server handle the affinity.

You can do this by right clicking the instance name in Object explorer in SSMS and checking the boxes next to Automatically set processor affinity mask for all processors and Automatically set I/O affinity mask for all processors.

  Here is the result: cost threshold for parallelism    5 max degree of
  parallelism   0

Maxdop & CTFP

You should try changing these settings, these are the default settings and not recommended in most cases. An idea / starting point could be to start with setting cost threshold for parallelism to 50.

How to change this can be found here

Setting Max degree of parallelism to 4 could be a starting point. How to change this can be found here.

Prevent worker splits

I would not set my maxdop higher than the least amount of cores per node configured with processor affinity, as to try to not go cross numa.

But this is harder to force than only changing CTFP & maxdop as you can read in a part of this blogpost by Joe Obbish.

  ...This plan won’t work if any of the following occur:

  Any query runs with a query level MAXDOP hint other than 0, 1, 10, or
  Any query is downgraded in MAXDOP but still runs in parallel.
  A parallel stats update happens. The last time I checked these run with a query level MAXDOP hint of 16.
  Something else unexpected happens ...

In the same post, the most reliable method as to not go cross node:

  The most reliable method by far is to have a single hard NUMA node, so
  if you have a VM that fits into a single socket of a VM host and you
  care about performance then ask your friendly VM administrator for
  some special treatment.

Source

Max memory setting for hard numa.

You should also look into setting your max memory leaving some for the OS and divisible by 3 for Hard-NUMA.

Since you are on SQL Server 2012 standard edition, the max amount of memory for your buffer pool = 64 but you could set more for the rest of the caches.
More on that here

Upgrading to at least SQL Server 2014 standard edition will give you the ability to use 128GB for the buffer pool, but I would advise to to a higher version than 2014.

An idea could be capping at 98304MB = 96GB of max memory.

Configuration commands:

EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'98304'
GO
RECONFIGURE WITH OVERRIDE
GO

Remember to monitor your queries and act accordingly after changing these settings. If some of these settings do not improve your situation, tweak them.

Getting help in the future

If there are other issues with certain queries, adding the actual query plan & as much information to a different question will help in getting answers faster. You should also check with the infrastructure team if any of these cores are overcommitted.

There should normally not be a reason to set your affinity to use 6 cores if you could use 12 dedicated cores (as you are within 4 sockets / 24 cores for your standard licensing).


Here are the changes i made to make this work:

add empty api node at the end of mgmd config.ini: [api]
stop both of ndbd from mgmd: ndb_mgm -e "3 stop" | "4 stop"
stop both of mgmd: ndb_mgm -e "1 stop" | "2 stop"
start mgmd 1: ndb_mgmd --initial --reload --config-file=/var/lib/mysql-cluster/config.ini
start mgmd 2: ndb_mgmd --initial --config-file=/var/lib/mysql-cluster/config.ini
start ndbd manually: ssh to the machine and run ndbd
wait until all node connected
make sure my.cnf at mysql node is having: [mysql_cluster] ndb-connectstring=192.168.1.1,192.168.1.2, or can add -c on ndb_desc param later
run ndb_desc at mysql node: ndb_desc -p -d db_name -t tb_name

the important missing part after adding empty api node is restarting the data node

link to source: No free node id found


Error while trying to export logins from SQL Server 2000 to SQL Server 2005

After dealing with this error quite a lot I was not able to find a way to make it work, since support for SQL2000 is no longer available I ended up using dbatools (an awesome PowerShell module): github.com/sqlcollaborative/dbatools

dbatools has a function called Export-SqlLogin which does exactly what I needed: script all logins with sid, password, server role, default db, etc.

I highly recommend looking into this PowerShell module.


ADD and DROP triggers frequently on a database

You may use

ALTER TABLE tblname ENABLE\DISABLE TRIGGER


How to determine the fully qualified tablenames of aliased tables, when a specifically named column is used in a stored procedure in SQL Server?

You have a couple of DMVs for this. My guess is that sys.dm_sql_referenced_entities can be useful:
USE Adventureworks
GO

CREATE OR ALTER PROC dbo.myProc
AS
SELECT h.AccountNumber, d.LineTotal
FROM Sales.SalesOrderDetail AS d
 INNER JOIN Sales.SalesOrderHeader AS h ON d.SalesOrderID = h.SalesOrderID
WHERE 1 = 0
GO

SELECT * FROM sys.dm_sql_referenced_entities('dbo.myProc', 'OBJECT')

SELECT * FROM sys.dm_sql_referencing_entities('Sales.SalesOrderDetail', 'OBJECT')


The right way is setting hot-standby.

Using Repmgr is just a question of your convenience. You can use it, or you can setup trigger file creating, or promoting with pg_ctl, for example. It's your choice. Repmgr is not involved in process of streaming data from master to slave, it is just management tool.

Also,

Be careful with 'automatic promotion of slave in case of failing master'. You should definitely know what you are doing and you should define 'failing' very strictly. If you don't have a super-large cluster, then it is the best not to have failover fully automated.
Be careful about 'data loss'. You should know, that streaming replication will save you only in case there is some failure on server side, or something wrong with network/disk/etc. It won't help you if someone will come to your db and type DROP DATABASE. Changes from statements will be immediately streamed to slave and the DB will be lost everywhere. The only way to avoid data loss from SQL statements is a valid backup.


Microsoft seem to have a solution for this problem:

  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use fn_my_permissions and Has_Perms_By_Name instead. Continued use of the PERMISSIONS function may result in slower performance.


How to configure an instance to listen on port 1433 to allow remote connection

If you are connecting from outside the domain you can either

1) Use SQL authentication to login.  If you have enabled mixed mode during the install you will have an sa account and you can create a sql login for yourself

2) There are other tricks, such as enabling named pipes and then authenticating against the IPC$ share with your domain account and then you can login using np:ipaddress in SSMS.  But beware it is a bit slow and most people don't want named pipes enabled on a production system.

But if you are connecting from outside the domain the easiest way is use SQL authentication.


Update with id returned from insert using WITH

You don't need to INSERT and then UPDATE - and it isn't advisable anyway, to insert/update/delete the same rows twice in one statement. You can calculate the values and then do the INSERT:

WITH disca
  ( id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato,
    telefone, telefone_discar,
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro
  )
AS
  ( VALUES
      ( nextval('callcenter.campanha_cliente_chamada_id_campanha_cliente_chamada_seq'),
        154424, 1824704,
        '(11) 2524-9636', '1125249636',
        1, 1, 0, 0,
        CURRENT_TIMESTAMP::timestamp(0), CURRENT_TIMESTAMP::timestamp(0)
      )
  )
INSERT INTO callcenter.campanha_cliente_chamada
  ( id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato,
    telefone, telefone_discar,
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro,
    accountcode
  )
SELECT
    id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato,
    telefone, telefone_discar,
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro,
    ('D-' || id_campanha_cliente_chamada || '-' || tentativa)::varchar(128)
FROM
    disca;

You can find the actual name of the sequence that has to be put in nextval('...') with \d campanha_cliente_chamada.


No parallelism in Express Edition of SQL Server

The limitation is mentioned in the Query Processing Architecture Guide under the "Parallel Query Processing" section:

Parallel plans not supported for Desktop and Express edition.

The Editions and supported features of SQL Server 2019 (15.x) documentation says (under "Scale Limits") that Express is:

Limited to lesser of 1 socket or 4 cores

But that is just related to how many cores you can make available to the database engine.
It doesn't say anything about parallel plans (other than a limitation related to batch mode operations being limited to 1 core on Express Edition).
All that to say that it looks like Express really doesn't allow parallel plans at all.  I tried running queries on SQL Server 2019 and 2014 with the same NonParallelPlanReason given, so it doesn't appear to be a new limitation either.


Pivoting row contents to produce one dimensional result

If you cannot change your current schema, then you can create a split function:

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end;

Then you can use an outer apply to join with your table:

select x.items
from yourtable t
outer apply dbo.split(col1, '|') x;

Which will produce the following results:

| ITEMS |
---------
|     a |
|     b |
|     e |
|    gg |
|   foo |
|    oV |
|   foo |
|    do |
|  boop |

See SQL Fiddle with Demo

Or if you do not want to use a split function, then you can perform this with CTE:

;with cte (value, fullValue) as
(
  select
      cast(left(col1, charindex('|',col1+'|')-1) as varchar(50)) value,
      stuff(col1, 1, charindex('|',col1+'|'), '') fullValue
  from yourtable
  union all
  select
    cast(left(fullValue, charindex('|',fullValue+'|')-1) as varchar(50)) ,
    stuff(fullValue, 1, charindex('|',fullValue+',|'), '') fullValue
  from cte
  where fullValue > ''
)
select value
from cte

See SQL Fiddle with Demo


I realize you've already worked around the issue, but here is the explanation and the fix for what you encountered.  It's nice to be able to do it with the pipe, so you don't have to write that big file somewhere.

As you know, when you type a query into the mysql command line client, while that query is executing, the client doesn't accept any more input from you.  That same thing essentially happens when you're stringing the these tools together with a pipe -- the target mysql client waits for the target server to return from the query before it can read more data from the pipe from mysqldump.

Meanwhile, as mysqldump is being blocked on writing to the pipe, because we're waiting for the target server to finish a query, it stops reading data from its socket connection.  The OS will only buffer so much data before it stops accepting data from the origin server.

If we do something like ENABLE KEYS which takes a while, we hit a timeout... on the origin MySQL server.  But the timeout value can be changed.

MySQL Server has two timers, net_write_timeout and net_read_timeout, which default to 60 and 30 seconds respectively, and which will cause the serve to tear down a client connection when the timer is exceeded while blocking on a write to or a read from the network.

Most likely, it's net_write_timeout you're hitting, so the origin server is giving up on the connection from mysqldump, which isn't accepting data fast enough (because it's blocked on its output).  Or, more precisely, it isn't accepting data often enough.  If any one statement takes too long to execute, the game is over, so, on the origin server:

SET GLOBAL net_write_timeout = 3600; # one hour

This is setting approximately how much time we can wait for any single query to finish executing on the destination server without the origin server timing out.  Normally, you wouldn't want MySQL to sit and wait for an hour for a blocking client, so set the timer back after you're done.


How else can I determine the performance increase of my selective
  index besides reviewing query execution times?

You could also look at IO utilization and CPU. Perhaps most interesting when dealing with selective XML indexes is to figure out if the index is actually used by the query. To do that you can have a look at the execution plan.

If you see one of the Table-valued functions responsible for parsing the XML then you know that you have not covered your XML query with a XML index.

What you should have instead is a scan or seek operator on the internal table used for the XML index.

  Is it preferred to use XQUERY's xs:double or SQL's INT when specifying
  data types?

If you want the index to be used for the value() function you should match the data type in the index with the data type used in the query. You have nvarchar(max) in the query and xs:double in the index so ChildTag1 and ChildTag2 is not used in your query. It is instead parsing the XML data using the table valued functions.

To make your query use only the indexes (no xml parsing functions) you can change the query and the index to this.

create selective xml index SIX_CTE on CTE(SampleXML) for
(
  ParentTag = '/Root/ParentTag' as xquery 'node()',
  ParentTagID = '/Root/ParentTag/@ParentTagID' as sql nvarchar(255),
  ChildTag1 = '/Root/ParentTag/ChildTag1' as sql int,
  ChildTag2 = '/Root/ParentTag/ChildTag2' as sql int
);

Note that the node() indexes on ChildTag1 and ChildTag2 is not used by your query so not needed in the index.

SELECT ID,
       Root.ParentTag.value('@ParentTagID','NVARCHAR(255)') AS ParentTagID,
       RootParentTag1.ChildTag1.value('.', 'INT') AS ChildTag1,
       NULL
FROM CTE
OUTER APPLY CTE.SampleXML.nodes('/Root/ParentTag') as Root(ParentTag)
OUTER APPLY Root.ParentTag.nodes('ChildTag1') as RootParentTag1(ChildTag1)
UNION
SELECT ID,
       Root.ParentTag.value('@ParentTagID','NVARCHAR(255)') AS ParentTagID,
       NULL,
       RootParentTag2.ChildTag2.value('.', 'INT') AS ChildTag2
FROM CTE
OUTER APPLY CTE.SampleXML.nodes('/Root/ParentTag') as Root(ParentTag)
OUTER APPLY Root.ParentTag.nodes('ChildTag2') as RootParentTag2(ChildTag2);

  What is the most effective SELECTIVE XML index?

Depends entirely on your use case and what the data looks like that you have. This answer is in no way an attempt to optimize your query, only a way to show you how to make use of the indexes. The result of testing on your data could show that you get the best performance by not using any indexes at all, or perhaps use the index only on some parts of the query. As a general rule it is always good to keep an index as small as possible so using int instead of float is a good thing if you know that you are dealing with integers.


Why does the CBO not chose a lower cost skip scan without stats?

It seems the CBO does not consider a skip scan at all with dynamic
  sampling, is this true?

Actually this is really easy to verify, you can do this by enabling 10053 trace. You will see that the optimizer does not even consider skip scan at all. The reason for this, is the "_optimizer_skip_scan_guess" parameter. The default value for this parameter is FALSE, meaning the optimizer will not consider skip scan when all it has is "guessed" selectivity, which is the case with dynamic sampling.

If you set "_optimizer_skip_scan_guess" to TRUE, skip scan will be considered, this can be also confirmed with the 10053 trace again.

PS: your db_file_multiblock_read_count parameter seems to be lower than the default value. On my 11.2.0.4 sandbox, with the default value of 128, after collecting statistics on the table, index FFS had about one-third of the cost of index SS.

Edit: added output

SQL> create table foo( bar integer, baz integer, qux char(99), constraint pk_foo primary key (bar, baz) ) organization index compress;

Table created.

SQL> insert into foo(bar,baz) select mod(level,1000), level from dual connect by level<1000000;

999999 rows created.

SQL> commit;

Commit complete.

Original:

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1715140356

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    66 |  8382 |  1302   (1)| 00:00:16 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   546   (1)| 00:00:07 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   546   (1)| 00:00:07 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

Enable skip scan for guessed selectivity:

SQL> alter session set "_optimizer_skip_scan_guess"=true;

Session altered.

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3033162421

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    66 |  8382 |   772   (1)| 00:00:10 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |    22 |  2794 |   350   (1)| 00:00:05 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |    22 |  2794 |   211   (0)| 00:00:03 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BAZ"=1)
       filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement (level=2)

24 rows selected.

Just for completeness:

SQL> analyze table foo compute statistics;

Table analyzed.

SQL> explain plan for
select * from foo where baz=1
union all
select /*+ full(foo) */ * from foo where baz=1
union all
select /*+ index_ss(foo) */ * from foo where baz=1;  2    3    4    5    6

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1715140356

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     3 |    30 |  1717   (2)| 00:00:21 |
|   1 |  UNION-ALL            |        |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |   357   (3)| 00:00:05 |
|*  3 |   INDEX FAST FULL SCAN| PK_FOO |     1 |    10 |   357   (3)| 00:00:05 |
|*  4 |   INDEX SKIP SCAN     | PK_FOO |     1 |    10 |  1002   (0)| 00:00:13 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("BAZ"=1)
   3 - filter("BAZ"=1)
   4 - access("BAZ"=1)
       filter("BAZ"=1)

19 rows selected.


How do I tell what agent profile is being used?

Ref: Replication Agents: Profiles and Parameters

Use the below query to find out which agent is using which profile.

SELECT
    [publication] as Publication
    ,c.srvname as SubscriberName
    ,b.profile_name as Profile
    ,[name] as DistributionJobName
FROM
    [distribution].[dbo].[MSdistribution_agents] a
INNER JOIN
    msdb.[dbo].[MSagent_profiles] b
    ON a.profile_id = b.profile_id
INNER JOIN
    master..sysservers c
    ON a.subscriber_id = c.srvid
ORDER BY
    b.profile_name;

If you want to see what profile your agent job was using previously best place to see is job history.


Constrain array values to an allowed set of values

Use the “is contained in” operator in your check constraint:

CHECK (countries <@ ARRAY['Morocco', 'Mali', 'Indonesia'])


Where is my ORACLE_HOME Windows directory?

Open up a command prompt

Start -> Run -> "cmd.exe"

Start sqlplus as administrator

sqlplus / as sysdba

There's a system stored procedure called get_env for retrieving the value for ORACLE_HOME, thanks Sabdar Syed for the query.

SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM

Should return a path like this:

  C:\app\oracle_account\product\12.1.0\dbhome_1


How can I Search for a string in all fields across all tables of a MySQL database?

This is a grueling problem and I have grueling suggestions
SUGGESTION #1: Use information_schema
See my old posts on how to go about doing this

Jul 19, 2012 : Query to find and replace text in all tables and fields of a mysql db
Mar 19, 2013 : How to search whole MySQL database for a particular string

SUGGESTION #2: Use mysqldump
You should be able to hunt down the string using mysqldump
Let's say the string you are looking for is 'Hello, World'
You can mysqldump all user-defined databases into a text file and grep the text file:
STRTOFIND="Hello, World"
DUMPFILE=mysqldump_to_search.sql
SRCHFILE=Search_Resutls.txt
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers -B ${DBLIST}"
mysqldump ${MYSQL_CONN} -A ${MYSQLDUMP_OPTIONS} > ${DUMPFILE}
grep -n "${STRTOFIND}" ${DUMPFILE} > ${SRCHFILE}

The search results file will not only have the line the string is located, but also the linenumber within the dump file.
GIVE IT A TRY !!!


How can I display 9 rows of results in a 3x3 grid?

Well, you can do something similar with SSRS.

First you have to map your dataset to a list. Inside the list you insert some text boxes and format them acording to the layout you desire.

Now in the report properties you configure to have three columns.

The tricky part is to adjust the widths and margins so the objects fit nicely in your page. For a letter size page (8.5 x 11 in) the list object should have a width less than 2.83 inches and a height of less than 3.66 inches to give space for the page and column margins.

In the preview check the print layout. You should see something like this:

The order is not exactly what you asked for, but if this is to print labels I guess it wont matter.


MySQL Stored Procedure - 0 rows affected

Something like the following should work for you:

CREATE PROCEDURE is_expired()
BEGIN
  INSERT INTO exp_mast(id,name,e_date)
    SELECT id, name, e_date
    FROM i_mast
    WHERE e_date = CURDATE();

  DELETE from i_mast
  WHERE e_date = CURDATE();
END;


Don't do index defrag is one option. Did you measure the improvements from defrag in the first place?
HQISILON Doesn't say anything to me, your disk subsystem is one aspect. But also the query load, the execution plans etc. Best is to have something load to measure what you actually gain.
A parallel work should be to investigate whether you have index that don't help you. Sys.dm_db_index_usage_stats can be useful here.
If blocking is your issue then consider doing reorg instead of rebuild. I.e., change the parameters to the proc since you now do rebuild if > 30%. Reorg is more online-ish than rebuild (in offline mode).


Constrain a postgres timestamp to "End of day"?

For now
While stuck with your unfortunate solution:
CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time)

That's right, AT TIME ZONE two times:

The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times.

The second instance converts the timestamptz back to timestamp at your given time zone. Now you can just check that the time component is whatever you wish.

Cast to time, instead of converting to a string, that's cheaper and more robust.
To get rid of fractional digits you could cast to time(0) instead, but that rounds the values instead of floor in your example. Instead, truncate  with date_trunc(), which is the cheaper method for floor() with positive numbers:
CHECK ((date_trunc('sec', end_time) AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time
        = '23:59:59'::time)
Proper solution
timestamp values have fractional digits and using the time component '23:59:59' as upper limit is an unfortunate decision. Instead, use 00:00 of the next day as exclusive upper border. It is trivial to enforce that with a check constraint.
Next, since you are dealing with multiple time zones, I would suggest to use timestamptz instead of timestamp. Internal storage is the same as with timestamp in the given time zone 'UTC', but input / output handling is different.

The timestamp is shifted to the current time zone automatically on output.
Input timestamps with time zon offset so the values are saved as according UTC times automatically.

Related answer on SO with a lot more details.


Postgres connection access denied on IPv6 address

The IPv6 addresses starting with fe80: are link-local addresses. They cannot be routed across different subnets or the internet. They are for communication between machines connected to the same LAN only. The link-local range is defined as fe80::/10. If you trust everybody on your local LAN then you could do

host  all  all  fe80::/10  trust

If you don't trust everybody on your local LAN then you probably want to use username+password protection:

host  all  all  fe80::/10  md5

When specifying md5 you are still using username+password protection, but the password is not transmitted in a readable format anymore. Since you're not trusting people on your local LAN you probably shouldn't send a snoopable password over the wire either.


Problem trying to remove sub-query and use WHERE only to return certain rows

Without a predicate to describe matches between the two tables, you're simply listing all product / Japanese supplier combinations, regardless of whether or not the supplier is the right one for each product.

USE TSQL2012
GO

SELECT
    p.productid ,
    p.productname ,
    p.unitprice ,
    p.supplierid
FROM    Production.Products p
JOIN  Production.Suppliers s ON p.supplierid = s.supplierid
WHERE   s.country = N'Japan';


How To Detect locking due to multi row updates

we are thinking of moving our updating records operations over from c# to sql

Typically the performance benefits of multi-row DML outweigh any increased blocking.  Plus you should be using transactions when performing a multi-row update from C#, in which case you'll have similar locking footprint to a server-side multi-row update:  Every row updated will be locked until the transaction commits.
You don't want to monitor locking, you want to monitor blocking.  And it's tracked in the system wait stats, session wait stats, and in query store wait stats, and with the good old Blocked Process Report.
See generally from the docs:
Transaction Locking and Row Versioning Guide
And from Brent Ozar:
Locking and Blocking in SQL Server
and
Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide


SQL Server, remove all users' access right to a table object

This code should work below:

declare
    @user_name sysname,
    @deny_cmd nvarchar(500)

declare UserCursor cursor for
select name
from sys.database_principals
where type in ('U', 'S')
and name not in ('dbo', 'information_schema', 'sys')

open UserCursor

fetch next from UserCursor
into @user_name

while @@fetch_status = 0
begin
    set @deny_cmd =
        'deny select, insert, update, delete on object::UberSecureTable
        to ' + @user_name

    exec (@deny_cmd)

    fetch next from UserCursor
    into @user_name
end

close UserCursor
deallocate UserCursor

Let me know if that does what you're looking for.  Change UberSecureTable to your actual table name.


Can underscore be forced as a word splitter without a full-text parser plugin?

I have never heard of such a plugin. Sure, you could make your own plugin.

However, I have a rather wild suggestion if you can afford the diskspace.

Let's take an example table:

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    txt text,
    primary key (id),
    fulltext txt (txt)
) ENGINE=MyISAM;

Let's say a table like this is populated already.

Suppose you want to index any words such as

abc_def_ghi
innodb_file_per_table
key_buffer_size

You may want to create a separate column with the underscores replaced with blanks. You will have to perform the following conversion

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ADD txtsrch TEXT;
ALTER TABLE mydb.mytable_new ADD FULLTEXT txt_txtsrtch (txt,txtsrch);
ALTER TABLE mydb.mytable_new DROP INDEX txt;
INSERT INTO mydb.mytable_new (id,txt,txtsrch)
SELECT id,txt,REPLACE(txt,'_',' ') FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Once done, the table should look like this

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    txt text,
    txsrch text,
    primary key (id),
    fulltext txtsrch (txt,txtsrch)
) ENGINE=MyISAM;

This will index the data both ways. Consequently, this blows up the table and index files in size.

You will have to do searching with the following clause

MATCH(txt,txtsrch) AGAINST ...

Please perform this on a test server, look at the increased size of the new table and index.  You can decide whether this table layout provides the data you want with tokens separated.

Give it a Try !!!


Performace of SELECT query based on primary key vs. indexed column

In PostgreSQL there is no difference between a primary key index and other indexes, so that does not surprise me.
The only slight difference could be caused by the size of the indexed data (wider values cause deeper indexes, so more blocks have to be traversed) or the speed of the comparison function. In most real world cases, the difference will be hard to measure.


Replicate only some tables of MariaDB database

In order of your topics:

You can't do that with cluster replication.
Multi-master is a cluster feature.
You should try to use the replicate_wild_do_table setting.

Slave threads will be restricted to replicating tables that match the specified wildcard pattern. For example replicate-wild-do-table=foo%.bar% will replicate only updates to tables in all databases that start with foo and whose table names start with bar.


How to clear all plans from a single database?

DBCC FLUSHPROCINDB (<db_id>)

This command allows you to specify a particular database id, and then clears all plans from that particular database. Check more information here.

DBCC FREEPROCCACHE

This command removes all cached plans from memory

  It is, of course, recommended that you don’t use these commands on
  your production servers, as it could impact the performance of your
  running applications. Usually, you want to keep plans in cache.


You can't expect a service that is not running to execute a command to start itself. You need to use something external. The SQL Agent service is dependent upon the SQL service running, that's why you can't do this in a job inside of SQL Agent. You could use the default windows task scheduler, or you could look to have the SQL service try to restart itself if it is found to be stopped.

That being said, your reason for restarting the service in order to release memory means that you should focus your efforts on proper memory management and not on how to dynamically restart the instance.


SQL Server strategy for high availability with batch drop/create updates

First, don't drop the table to do this. You have two better options:

Use the MERGE command
TRUNCATE the table before inserting, don't DROP and CREATE.

The main limitation on the TRUNCATE...INSERT pattern is permissions, since a simple read or update permission grant will not allow for TRUNCATE. ALTER permission on the table is needed to use this command. It also has some other specific limitations, since you can't use TRUNCATE IF:

The table is referenced by a FOREIGN KEY constraint (unless that FK reference is from the same table)
The table that is a participant in an indexed view.
The table is a source for transactional or merge replication.

If none of these conditions are true for the table, and the login performing the task has sufficient permissions, then you are good to go with TRUNCATE...INSERT processing.

MERGE only needs insert, update, and delete permissions. The syntax can be a bit (understatement) confusing until you understand what is happening. However, a quick search on the net will reveal a number of good tutorials for learning the command.

You can also technically write separate DELETE, UPDATE, and INSERT commands for the DUI update. However, this needs to be within an explicit transaction, and is more difficult to maintain than the MERGE, which does not need to be explicitly within a transaction.


Those sequence numbers are necessarily large, monotonically increasing, numbers.  They are not byte offsets into the log.  You may find the number in the log, of you have fun with hex dumps.  (I don't.)
That Percona blog, while good, is a decade old.  A large log no longer delays restart as much as it once did.
Consider this for judging whether to increase (or decrease) the log_file_size:
Minutes between InnoDB log rotations = Uptime / 60 * innodb_log_file_size / Innodb_os_log_written
60 minutes is a good target, but it is not a precise target.
Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
innodb_log_files_in_group traditionally has been 2.  I suggest you use 2 unless you are on a new version of MariaDB, where it is no longer needed.


Merge 2 tables and fetch data one by one

Seems pretty straightforward.

SELECT * from products left join offers on prod_id=prod_id
ORDER BY offer_id DESC LIMIT 20;

See it here:  http://sqlfiddle.com/#!2/dff49/3/0


Yes, for example:

FUNCTION insert_file (IN filepath VARCHAR(255)) ...
sqltxt:='COPY table_name from '''||filepath||''' with (FORMAT csv)';
execute sqltxt;

'''||variable||''' will produce 'value' with quotation marks.

'||variable||' will produce the value without quotation marks. usefully for table names and integers for example.

You need to declare sqltxt as text.


As per the documentation states:

  The NEXT VALUE FOR function is nondeterministic, and is only allowed in contexts where the number of generated sequence values is well defined. Below is the definition of how many values will be used for each referenced sequence object in a given statement:

  SELECT - For each referenced sequence object, a new value is generated once per row in the result of the statement.


mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES

You apparently have a missing drupal_install_test in your database schema. As the name says, it's a test table that is created at installation time to test the performance of the database and of the database user you provided for your site. At the end of the installation process, it gets dropped from the database.

The problem here is that the table has been dropped but mysql still thinks it exists. The reason why that table has remained in information_schema.tables should definitely be investigated and may reflect some issues in the database or in your server's configuration.

That said, if drupal_install_test is the only corrupted table in your database, you can try and drop it and proceed with your backup. I recommend that you make a full backup of the mysql data directory (in a typical linux installation /var/lib/mysql) and issue a DROP TABLE drupal_install_test command. Mysql should be able to restore the integrity of the schema tables and you should be able to proceed with your backup.


SQL Server 2008 unable to backup due to Failed to flush the commit table to disk in dbid 11 due to error 2601

Do you happen to use the change tracking services provided by in SQL 2008 Enterprise?  If so, it's time to update your system.  Please try to avoid running RTM anywhere.  It's very risky from a security and sometimes stability point of view. :)

The following Microsoft Knowledge Base articles may be of use to you:

FIX: Backup fails in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012 if you enable change tracking on the database

This fix was first released in Cumulative Update 7 for SQL Server 2008 Service Pack 2.

  Symptoms:

  Error: 2601, Severity: 14, State: 1.  Cannot insert duplicate key row
  in object 'sys.syscommittab' with unique index 'Name'.  Error: 3999,
  Severity: 17, State: 1. Failed to flush the commit table to disk in
  dbid due to error 2601.

FIX: The sys.syscommittab system table is not cleaned up after the retention period for change tracking in SQL Server 2008

This fix was first released in Cumulative Update 11 for SQL Server 2008 Service Pack 1.

  Symptoms:

  You enable the Change Tracking and Auto Cleanup properties for a
  database in Microsoft SQL Server 2008.

  You set the Retention Period and Retention Period Units properties.
  In this scenario, the sys.syscommittab system table for change
  tracking is not cleaned up after the specified retention period.


Performance issue with xpath in SQL Server 2008

The second query uses parallelism. That is, it was expensive enough for the optimizer to shut its eyes to the additional overhead.

I'd guess the second query tells optimizer to "dump everything", which is performed with a paralleled scan. SQL Server likes to "dump everything" in this way when asked.
Whereas the first query asks for "analyze and then give some." The optimizer has no way of knowing there's only one node anyway, so the execution plan it ends up picking is very different.

I'd say it's similar to situation when one table scan is cheaper than many index seeks.


Golden Gate realtime replication basic question

Is this doable in GoldenGate(GG)?

The Map or Table parameter can be used to select Table, depending on whether to apply in Extract or in Replicat.

The Cols and ColsEXCEPT attributes of the Table parameter can be used for Columns selection.

I believe that the Oracle's official documentation is good link to create the solution.

Here are some.

Selecting Table

Selecting Columns

SQLEXEC:Overview


Efficiently searching geolocations - LAT/LNG and DECIMALs

"Standard" MySQL indexes use B-Trees, which are horrible for searching ranges in 2 or more dimensions (they can only use the first column). You want to use R-Trees, available, like in many other RDBMS, when using the GIS/spatial extensions for MySQL.

They require specific datatypes. POINT is what you want- you will have to convert the latitude and the longitude to this format. Then create a spatial index. Then using the function Contains() or MBRContains().

Please note that this feature is typicaly criticised in MySQL for the lack of features (for example, it wasn't available for InnoDB before 5.7, no distance function, no projection support, ...). Ironically, the lack of those features made it a very fast implementation -when it worked. And it should be enough for what you want to do.

If you cannot use spatial indexing in MySQL, an index on (latitude, longitude) may be a bit faster in 5.6 with ICP, or you can alternatively try to use some indexing tricks. If that does not work for you, and it is still too slow, you will need an external indexing solution or a complete change of database server.


How to do a recursive sum of children in mysql?

With your example, you can run
CREATE PROCEDURE procedure_name()
BEGIN
        DECLARE finished INTEGER DEFAULT 0;
        DECLARE _accoujntno INTEGER DEFAULT 0;
        DECLARE _uacn varchar(191) DEFAULT "";
    DEClARE curaccount
        CURSOR FOR
            Select account_no ,user_account_number FROM TBL1 WHERE length(user_account_number)-length(replace(user_account_number,'-',''))+1  = 2 order by user_account_number;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

        CREATE TEMPORARY TABLE credits(
        user_account_number VARCHAR(191),
        `Debit_Before_Period` DECIMAL(20,2),
         `Credit_Before_Period` DECIMAL(20,2),
         `Debit_In_Period` DECIMAL(20,2),
         `Credit_In_Period` DECIMAL(20,2)
        );

        OPEN curaccount;

    getaccount: LOOP
        FETCH curaccount INTO _accoujntno,_uacn;
        IF finished = 1 THEN
            LEAVE getaccount;
        END IF;
                INSERT INTO credits SELECT _uacn ,SUM(`Debit Before Period`),SUM(`Credit Before Period`) ,SUM(`Debit In Period`) ,SUM(`Credit In Period`)
                FROM TBL1
                WHERE LEFT(user_account_number,7) = _uacn AND account_no <> _accoujntno;
    END LOOP getaccount;
    CLOSE curaccount;

   SELECT * FROM credits;
END

The resulting temporary table credits you can use to run  further analysis
simply add the procedure and CALL procedure_name()
In a query tab in phmyadmin wor mysql Workbench you need to add DELIMITER to the procedure, but that doesn't work in dbfiddle
example
It is like in the comment
CREATE PROCEDURE procedure_name()
BEGIN
        DECLARE finished INTEGER DEFAULT 0;
        DECLARE _accoujntno INTEGER DEFAULT 0;
        DECLARE _uacn varchar(191) DEFAULT "";
    DEClARE curaccount
        CURSOR FOR
            Select account_no ,user_account_number FROM TBL1 WHERE length(user_account_number)-length(replace(user_account_number,'-',''))+1  = 1 order by user_account_number;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

        CREATE TEMPORARY TABLE credits(
        user_account_number VARCHAR(191),
        `Debit_Before_Period` DECIMAL(20,2),
         `Credit_Before_Period` DECIMAL(20,2),
         `Debit_In_Period` DECIMAL(20,2),
         `Credit_In_Period` DECIMAL(20,2)
        );

        OPEN curaccount;

    getaccount: LOOP
        FETCH curaccount INTO _accoujntno,_uacn;
        IF finished = 1 THEN
            LEAVE getaccount;
        END IF;
                INSERT INTO credits SELECT _uacn ,SUM(`Debit Before Period`),SUM(`Credit Before Period`) ,SUM(`Debit In Period`) ,SUM(`Credit In Period`)
                FROM TBL1
                WHERE LEFT(user_account_number,3) = _uacn;
    END LOOP getaccount;
    CLOSE curaccount;

   SELECT * FROM credits;
END

new example


This certain MySQL database organisation

You can approach this by creating the relevant foreign keys so you can use relations in your databases.
Here is a nice read about relational databases.
I strongly recommend it for you to read it, sou you can get the hang of the relational design idea.

Edit:
In your particular case i would to this:

Products (id, product_name, added_timestamp, seller_type, seller_id)
Individuals (id, name, last_name)
Companies (id, company_name, company_number, company_address)

where seller_type can be either 'i' as Individual or 'c' Company, and seller_id is the corresponding foreign id.


Finding data recently created/updated in a whole PostgreSQL database

Rather than trying to look at the data that changed, I would start by looking at the commands that changed it.  Set log_statement = mod in the postgresql.conf.


SQL Server 2012 patching failure - The language of SQL Server instance MSSQLSERVER does not match the language expected by the SQL Server update

Please try to go to Control Panel, Regional Settings and change the "Format" language.  close , re-open it and change format back to yours. Retry the setup of the patch


Error "cannot connect to server" installing MongoDB on Ubuntu

You probably just need to delete the lock file in /var/lib/mongodb, and then restart the service.

If you still have problems, you should run mongod --repair (along with the other options, at least the dbpath).


Postgresql - Recovery using recovery.conf

DISCLAIMER : I am not a PostgreSQL DBA, though I dabble a lot with it.

You probably need to check your timezone in the OS and in psql.

In the OS run this:

[postgres@radarPG-db1 ~]$ date
Fri Jun 17 12:55:37 EDT 2011

In psql, run the following:

postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
 date_part | date_part
-----------+-----------
        -4 |         0

postgres=# select now();
              now
-------------------------------
 2011-06-17 12:54:39.195291-04

Many times, some forget to have postgres default the timezone to that of the OS. If the WAL files also contain the timezone in its internal timestamps, you must align the timezone set in postgres with that of the WAL files.

Something else to consider is the following:

  The default behavior of recovery is to
  recover along the same timeline that
  was current when the base backup was
  taken. If you want to recover into
  some child timeline (that is, you want
  to return to some state that was
  itself generated after a recovery
  attempt), you need to specify the
  target timeline ID in 'recovery.conf'.
  You cannot recover into timelines that
  branched off earlier than the base
  backup.

UPDATE 2011-06-20 15:08 EDT

This is right from the online documentation. Please look this over and see if you missed any WAL files along the way. If you waited too long to setup the WAL files on the new server, postgres may have deleted them on startup. You may need to find those WAL files. See if they reside back in pg_xlog folder from the old server.

Recovering using a Continuous Archive Backup

Stop the server, if it's running.
If you have the space to do so, copy the whole cluster data directory and any tablespaces to a temporary location in case you need them later. Note that this precaution will require that you have enough free space on your system to hold two copies of your existing database. If you do not have enough space, you need at the least to copy the contents of the pg_xlog subdirectory of the cluster data directory, as it might contain logs which were not archived before the system went down.
Clean out all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.
Restore the database files from your base backup. Be careful that they are restored with the right ownership (the database system user, not root!) and with the right permissions. If you are using tablespaces, you should verify that the symbolic links in pg_tblspc/ were correctly restored.
Remove any files present in pg_xlog/; these came from the backup dump and are therefore probably obsolete rather than current. If you didn't archive pg_xlog/ at all, then recreate it, being careful to ensure that you re-establish it as a symbolic link if you had it set up that way before.
If you had unarchived WAL segment files that you saved in step 2, copy them into pg_xlog/. (It is best to copy them, not move them, so that you still have the unmodified files if a problem occurs and you have to start over.)
Create a recovery command file recovery.conf in the cluster data directory (see Recovery Settings). You might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery has worked.
Start the server. The server will go into recovery mode and proceed to read through the archived WAL files it needs. Should the recovery be terminated because of an external error, the server can simply be restarted and it will continue recovery. Upon completion of the recovery process, the server will rename recovery.conf to recovery.done (to prevent accidentally re-entering recovery mode in case of a crash later) and then commence normal database operations.
Inspect the contents of the database to ensure you have recovered to where you want to be. If not, return to step 1. If all is well, let in your users by restoring pg_hba.conf to normal.


Select data from three tables using sub query and group by

Your subquery:

SELECT     sum(store_sales)
FROM       sales_fact
INNER JOIN product
ON         sales_fact.product_id = product.product_id
GROUP BY   product_name

returns a list of aggregate sum by product.

But your main query expects only one value due you're grouping by month.

SELECT     time_by_day.the_month
FROM       sales_fact
INNER JOIN time_by_day
ON         sales_fact.time_id = time_by_day.time_id
GROUP BY   time_by_day.the_month
ORDER BY   DATEPART(mm,CAST(the_month+ ' 1900'  AS DATETIME)) asc;

SQL Server returns an error indicating that your subquery returns more than one value.

If you want aggregate sales by product, instead of a subquery, join your products table on your main query on this way:

SELECT     t.the_month,
           p.product_id,
           p.product_name,
           sum(store_sales) as total_sales
FROM       sales_fact
INNER JOIN time_by_day
ON         sales_fact.time_id = time_by_day.time_id
INNER JOIN product p
ON         s.product_id = p.product_id
GROUP BY   t.the_month, p.product_id. p.product_name
ORDER BY   DATEPART(mm,CAST(t.the_month + ' 1900' AS DATETIME)) ASC;

However if you need total sales by month, remove prduct.id and product.name from both, SELECT and GROUP BY statements.

SELECT     t.the_month,
           sum(store_sales) as total_sales_month
FROM       sales_fact
INNER JOIN time_by_day
ON         sales_fact.time_id = time_by_day.time_id
INNER JOIN product p
ON         s.product_id = p.product_id
GROUP BY   t.the_month
ORDER BY   DATEPART(mm,CAST(t.the_month + ' 1900' AS DATETIME)) ASC;

If your time_by_day table has a datetime field, you can use it on your  ORDER BY clause instead of build a datetime value. In fact if you're filtering more than one year, actual ORDER BY clause do not work properly.

ORDER BY   time_by_day.date_field ASC;

If there is no datetime value, you can order by month without cast.

ORDER BY   t.the_month ASC;


I would have your event table and use the event id as a foreign key to another table we might call event_times. This new table might look like this:

id  |  eventID  |  dayOfWeek  | startTime  |  endTime
--------------------------------------------------------
1      1           1            13:00:00      14:00:00

The day of week could be a smallint where 0 represents Sunday and 6 represents Saturday. Start time and end time would be the the time data type, as you only need to store the time of day (in 24-hour time).

To produce your output, your sample query would be:

SELECT eventID FROM event_times WHERE dayOfWeek = '1' AND startTime = '17:00:00' AND endTime = '19:00:00';

To get more information about the event, you would just need to join the eventID on your event table.


What privileges do I have to grant to allow a user to select from information_schema.table_privileges?

This information_schema view (my bold)

  Returns one row for each table privilege that is granted to or granted by the current user in the current database.

This doesn't take into account roles etc: it all works on DATABASE_PRINCIPAL_ID which can be seen if you look at the definition of INFORMATION_SCHEMA.TABLE_PRIVILEGES

Personally, I'd just use sys.database_permissions and not bother with the information_schema rubbish. This relies solely on Meta data visibility to filter rows, so you'll see the actual permissions unfiltered by the INFORMATION_SCHEMA.TABLE_PRIVILEGES view which adds a further filter that bollixes you.

For completeness, here is the view. Note the implicit "old style" JOIN :-)

CREATE VIEW INFORMATION_SCHEMA.TABLE_PRIVILEGES
AS
SELECT
    USER_NAME(p.grantor_principal_id)   AS GRANTOR,
    USER_NAME(p.grantee_principal_id)   AS GRANTEE,
    DB_NAME()                       AS TABLE_CATALOG,
    SCHEMA_NAME(o.schema_id)            AS TABLE_SCHEMA,
    o.name                          AS TABLE_NAME,
    convert(varchar(10), CASE p.type
        WHEN 'RF' THEN 'REFERENCES'
        WHEN 'SL' THEN 'SELECT'
        WHEN 'IN' THEN 'INSERT'
        WHEN 'DL' THEN 'DELETE'
        WHEN 'UP' THEN 'UPDATE'
        END)                        AS PRIVILEGE_TYPE,
    convert(varchar(3), CASE p.state
        WHEN 'G' THEN 'NO'
        WHEN 'W' THEN 'YES'
        END)                        AS IS_GRANTABLE
FROM
    sys.objects o,
    sys.database_permissions p
WHERE
    o.type IN ('U', 'V')
    AND p.class = 1
    AND p.major_id = o.object_id
    AND p.minor_id = 0  -- all columns
    AND p.type IN ('RF','IN','SL','UP','DL')
    AND p.state IN ('W','G')
    AND (p.grantee_principal_id = 0
        OR p.grantee_principal_id = DATABASE_PRINCIPAL_ID()
        OR p.grantor_principal_id = DATABASE_PRINCIPAL_ID())


How can we achive the replica of databases for SQL Server?

To reinforce what Jon says - a single DB to which all clients connect all the time, with the schema designed accordingly, will be a much better solution.  You only need replication in a few circumstances.  One is if a client machine must be able to work while disconnected.  Another will be if there are huge latency issues between your various sites.  Replication, especially two-way merge replication, can be a huge pain - during upgrades, for example.


Linked Server Error - Login Failed for user 'myaccount'

In linked servers - you'll need to make sure you have permissions on both servers that you're looking to set the link between.

So for instance - if you're sa on Server A, you'll still need at least read-only access to the database you're trying to access on Server B in order to use the linked server.

Additionally - when you're configuring your linked server, it's important to make sure your security context is set correctly.

By default, Linked Servers are made without any security context which puts the responsibility for authentication on the connection string you specify.

Usually you'll want to use the credentials of who is attempting to connect through the linked server by selecting "Connections will be made using the login's security context", but this varies based on you and your companies needs.

More information on linked servers can be found on technet:

General Linked Servers -
https://msdn.microsoft.com/en-us/library/ms188279(v=sql.105).aspx

Linked Server Security -
https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx


Couldn't find sql server agent in Sql Server 2014 Management Studio?

No you cannot have SQL Server agent feature in express edition of SQL Server. To get that you would have to upgrade to licensed version of SQL Server like Enterprise, Standard, Web and Developer editions. Just so that you know you can see the SQL Server agent services in express edition but that cannot be started.
If you want to use scheduling to schedule jobs you have to rely in windows scheduler

From SQL Server 2014 onwards the developer edition is free to download and use and this version has SQL Server agent feature. The only limitation is you cannot use developer version in production, you can ONLY use it in development and testing environment.


Shards error msg “can't use localhost … either use localhost or ipadresses”

Yes, you cannot mix IP addresses and localhost. Those started mongod processes are now using your network device ip-address. If you don't know your machines ip-address, you can check address with ifconfig or ip address command.

Then if you connect mongos using that ip-address instead of localhost. Remember check that your firewall have all those ports open!


I assumed that "failed" means passed = 0.

There are some detail missing from the question, i.e. do we need to compare the very last row (when ordered by sampledate) with the previously last "failed" row? If yes, then the following query will do:

with last_row as
( select top (1) sampledate, level
  from dbo.tablex
  -- where failed = 0
  order by sampledate desc
)
select top (1) result =
    case when t.level = r.level
        then 1 else 0
    end
from dbo.tablex as t
  join last_row as r
  on t.sampledate < r.sampledate
where t.passed = 0
order by t.sampledate desc ;

If, on the other side, we want to compare the last "failed" row with the previous "failed" row, then we could uncomment the where failed = 0 line above or use the less complicated, just find the last 2 failed rows and compare their levels with a simple group by:

with last_2_failed_rows as
( select top (2) sampledate, level
  from dbo.tablex
  where failed = 0
  order by sampledate desc
)
select result =
    case when min(level) = max(level)
        then 1 else 0
    end
from last_2_failed_rows ;


Access to folder is denied (log shipping)

It would be useful to see the output of the actual job itself.

You mentioned you are using local system, if so then you need to grant permissions to the computer account on the folder.  The account you give permissions to would be in the format:

<DomainName>\<ServerName>$

So for a computer called ServerA on the Contoso domain you would give permissions to the folder for this account

Contoso\ServerA$

The $ on the end means this is a computer account.

If this works you may still run into issues with logging  if the computer accounts don't have any permissions in SQL Server.


The documents tend to look just like records in 1NF at this stage.

For an array of primitive values the output of $unwind looks similar to First normal form (1NF), but pedantically the $unwind output should be expected to be UNF (Unnormalised form) since:

documents output by $unwind do not have a unique key constraint (although arguably the includeArrayIndex option can be used to provide an explicit row identifier)
arrays support complex data types (nested arrays and documents)

  I have searched and searched and not found that phrase uttered in any Mongo topics, certainly it was not said in courses I took in MongoDB University.

Database normalisation is a strongly RDBMS concept, where you design a model to reduce data redundancy ("How to efficiently store data"). Standard normal forms are expressions of more rigid data relationships and constraints for tabular data.

MongoDB data model design is application-centric ("How to efficiently use data"). Since the decision on whether to normalise or denormalise data is influenced by the usage context, data model discussion generally focuses on data growth and use case patterns rather than highly normalised forms.

  Is 1NF essentially the same thing as the data form necessary for an aggregation step?

1NF (or an $unwind operation) certainly isn't a necessary step for aggregating or transforming arrays. MongoDB has other array expression operators such as $arrayToObject, $filter, and $map which implement different data transformations.

  Is there any data manipulation term for it?

I'm not sure if there are any generally accepted terms, but I think an $unwind operation would typically be described as either "flattening" or "unnesting" arrays.


There isn't any way to reset autoincrement such that it goes back to the beginning and just overwrites existing data.  Autoincrement isn't built that way.

What you could do is update rather than insert.  You'd have to either pre-populate every record with blank data so that every entry is always an update, or you could write a procedure which tries to read the next ID first and then either inserts or updates accordingly.  Either way, you will need to track the next ID yourself instead of letting the database handle it for you (with autoincrement).  When you get to the maximum value, reset the next ID to 1 and keep going.

Thinking Practically:

The largest value for UNSIGNED BIGINT is 18,446,744,073,709,551,615.  Depending on what is in your record you are looking at thousands (even millions) of petabytes of data.  Are you sure you aren't going to have space constraints with that much data?  You may find that what you need to do is purge out data that is old enough to be discarded as you go just to make room in your database.  If you do that, then you can avoid the prepopulation and just insert every time.

If you purge periodically, say every night, then you just need to wait until the gap between where your remaining data is and ID=1 is big enough that you don't have to worry about ID collisions.  When that happens, reseed your ID and start inserting from ID=1 again.  If you do this, then you can go back to using autoincrement.

Edit: Additional Information about reseeding...

OP mentioned in a comment that the command for reseeding an autoincrement column in mysql is of interest.  This command will reset the starting autoincrement value:

ALTER TABLE table AUTO_INCREMENT = 1

I have read that this should be done with caution because it can take a long time in a large table.

See also the documentation for AUTO_INCREMENT.


update dbo.B
set b.[ID] = a.orig_id
FROM dbo.A a
join dbo.B b
on b.[NUMBER] = a.number
and b.[ID] <> a.orig_id

the <> is so it can avoid taking a lock if the are equal


CTE returns an empty set even though anchor is non-empty

I figured it out. (Apologies to those of you who tried to help me. There was no way you could have figured this out.)

After a lot of experimentation, I noticed that dynamic queries (Connection.createStatement()) were returning results but parameterized queries (Connection.prepareStatement()) were returning an empty set. You had no way of knowing this because my question incorrectly listed the query as:

  anchor.child_id = -32763

whereas in actuality it was a PreparedStatement

  anchor.child_id = ?

with a value of -32763.

Deep within H2's release notes I ran across this wonderful sentence:

  Parameters are only supported within the last SELECT statement (a workaround is to use session variables like @start within the table expression).

In short, this looks like an H2 limitation/bug. I really wish H2 would have thrown an exception instead of returning an empty set.

https://groups.google.com/d/msg/h2-database/OJfqNF_Iqyo/Z748UP7W3NAJ confirms this issue (I am getting an empty set instead of null, but otherwise the problem description is identical).

Thank you to those of you who tried helping!


Use system timestamp comparison in SELECT query

The WHERE expression does not make sense:
WHERE date_trunc('day',NOW() - interval '1 day'

It has to evaluate to a boolean value and you need to compare to an actual column of the table like (assuming a timestamp , timestamptz or date column):
WHERE my_timestamp_column >= (NOW()::date - 1)
AND   my_timestamp_column <   NOW()::date

Postgres does not store a creation timestamp for table rows automatically. You need to add a column yourself to keep track (with default value and / or a trigger to make sure). Detailed instructions with code in this related answer:

Tables accessed during last period

"Commit timestamps" in Postgres 9.5 or later
You can activate the track_commit_timestamp setting to start tracking. Then you can use pg_xact_commit_timestamp(xmin) to retrieve the timestamp when each row was inserted or updated (after you started tracking). To get all rows that were inserted or updated within the last day:
SELECT * FROM mytab
WHERE  pg_xact_commit_timestamp(xmin) > now() - interval '1 day'

Certain limitations apply. Commit timestamps are not kept indefinitely. Read the Postgres Wiki for details.
Once activated, commit timestamps are tracked for all transactions in the DB cluster, which causes a performance hit. If you only need it for one or a few tables, rather add timestamps to involved table(s) manually like instructed above.


There are no users in DB2 that are not OS users. The client software (not only Toad is guilty of this) makes this a bit confusing: what they show as "users" are actually grants given to the (externally defined) users.

As you have discovered, you can grant privileges to arbitrary users or groups, and no verification is done if those users and groups exist at that time.

However, to connect to the database and exercise the granted privileges you must authenticate first, and for that you must have the user defined in the OS (or another configured authentication service, such as an LDAP directory).


The other answers are technically correct, but not real-world correct. Here's what you need to ask the business:

What time horizon am I aiming for? In your case, you're looking for a 12-month number.

During that time, will we be archiving data, or keeping all data? In some businesses, you're allowed to (or required to) only keep a certain amount of data, like the last 12 months. In that case, you'll need to figure out the data growth (which the subsequent questions will answer) but then back down to the last rolling 12 months. You can't just say, "Right now that amount of data is 100GB," because if your data volume is growing, then the last 12 months is growing too. The time amount might be constant, but the data is not.

Will we be adding additional users? For example, the business might be growing into new territories or acquiring new customers. If they double the user base, then in some cases, the data will start doubling as well.

Do we expect the business volume to grow? If you're tracking sales on a web site, for example, and you start running Super Bowl or World Cup ads, your data volume can hit the hockey stick growth curve.

Will we be adding additional functionality in the app? If the app suddenly starts storing images, this will dramatically affect database size.

Will we be adding data from another source, or logging new data? If you start capturing web site clicks, or in a data warehouse, adding additional sources, then data volume will grow.

Will developers or DBAs be performance tuning indexes? If you're going to let people create indexes, you can easily double (or triple, or quadruple) the size of your data depending on how overzealous they get.

And as long as you're asking these questions, you should also ask if performance is expected to stay the same, degrade, or get better. I like mapping out the projected growth on a line chart, and then comparing hardware and staff training investments over that same timeline.


Is it possible to force an adaptive join with a query hint?

As it says in the documentation, adaptive join currently supports SELECT statements only:

  Adaptive join eligible statements

  A few conditions make a logical join eligible for a batch mode Adaptive Join:

  The database compatibility level is 140.
  The query is a SELECT statement (data modification statements are currently ineligible).
  The join is eligible to be executed both by an indexed Nested Loops Join or a Hash Join physical algorithm.
  The Hash Join uses batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
  The generated alternative solutions of the Nested Loops Join and Hash Join should have the same first child (outer reference).


How do you move a SQL Server 7 database to SQL Server 2012?

Solution 1

Copy the data using an ETL process.  This assumes you have a SQL Server 7 instance up and running with the database accessible.  You have several tools at your disposal, but the simplest approach I could see is:

Script out all the SQL Server 7 objects and create them in SQL Server 2012. You might have to adjust the script if there's any SQL Server 7 specific syntax.
Use an ETL process to copy data.  Some alternatives:

BCP - Use BCP to dump every table to a RAW or text file, then use BCP to load them into the SQL Server 2012 objects.
Import/Export Wizard in SQL Server 2012 - Use the Import/Export wizard to create data transfer tasks.
SSIS - This is essentially the same as the Import/Export Wizard, but with more functionality.

Overall, this could be a tedious process, but allows you to copy data directly from one database to the other. If you go the BCP route, you will need some storage for the dump files, but this doesn't have to be database storage.

Solution 2

Upgrade the database by using an intermediate step of upgrading to SQL Server 2005. This assumes you have access to SQL Server 2005 media and a server to stand it up on. SQL Server 7 can not be upgraded to SQL Server 2008 as that upgrade path is not supported, but if you can restore the database to SQL Server 2005, you can then successively backup the SQL Server 2005 version and restore it to SQL Server 2012.


use variable to determine the name of a column in the select in postgre

I think you might be looking for the USING clause:

EXECUTE format('SELECT COALESCE(%I,col,$1) FROM public.TableMix
                WHERE name = $2', ruleName)
        USING (username, whateverTheSecondParameterIs);

You use $1, $2, etc for placement-parameters, passed by USING. You use %I for itentifiers expanded by format.

Update: If you want to coalesce the column name ruleName if it's null, perhaps you want something more like:

EXECUTE format('SELECT %I FROM public.TableMix
                WHERE name = $1', coalesce(ruleName, 'col'))
        USING (username);

??

You need to think about order of evaluation. What's part of the generated SQL, and what's evaluated in order to create that SQL?


Upgrading SQL 2016 AGs to SQL 2019 question

"Synchronized/In Recovery" means the database is synchronized with the primary and applying log records continuously from the primary.  This keeps the database up-to-date against the source 2016 database.  The database cannot be used while in this state, not even for read-only queries.
If you were to failover to a database in that state, the server would run recovery on the database once it has applied all the outstanding log records in the redo-queue.  At that point the database comes online, and can be used by clients.  However, once recovery runs, and the database engine upgrades the database to SQL Server 2019, it cannot be restored onto prior SQL Server versions - i.e. you couldn't fallback to a 2016 server.
Once you complete the upgrade of the last node to SQL Server 2019, database status should return to normal on the readonly secondaries.


Quick nearest neighbor search in the 150-dimensional space

PostgreSQL 9.6 using cube

First install the cube extension

CREATE EXTENSION cube;

Now we will create some n-dimensional space with 100,000 points in 50 dimensions. In addition we'll add a GIST index.

CREATE TEMP TABLE space_nd
AS
  SELECT i, cube(array_agg(random()::float)) AS c
  FROM generate_series(1,1e5) AS i
  CROSS JOIN LATERAL generate_series(1,50)
    AS x
  GROUP BY i;

CREATE INDEX ON space_nd USING gist ( c );
ANALYZE space_nd;

Now we will generate a single point and use the <-> operater to find the nearest point using Eucledian distance.

WITH points AS (
  SELECT cube(array_agg(random()::float)) AS c
  FROM generate_series(1,50)
    AS x
)
SELECT i,
  pg_typeof(space_nd.c),
  pg_typeof(points.c),
  cube_distance(space_nd.c, points.c)
FROM space_nd
CROSS JOIN points
ORDER BY space_nd.c <-> points.c
LIMIT 5;

PostgreSQL 9.6+ supports other distance operators over cube. All of which can use the GIST index we created. Namely,

a <-> b float8  Euclidean distance between a and b.
a <#> b float8  Taxicab (L-1 metric) distance between a and b.
a <=> b float8  Chebyshev (L-inf metric) distance between a and b.

That said there is one caveat,

  To make it harder for people to break things, there is a limit of 100 on the number of dimensions of cubes. This is set in cubedata.h if you need something bigger.

You ask for 150 dimensions. That may present a minor complication.


Why does MongoDB extend FileSize if it is already 5x larger than DataSize

Based on the comments I have received the following actions seem to address my concerns:

Migrate existing collections to power of 2 sizes.
Run repair or compress periodically to optimize the free list search so that default allocation of new disk space on timeout is avoided.
Only capped collections should be considered "100% maintenance-free".


How to ignore columns in parquet/polybase import?

Polybase does not have this ability.  The source file and external table definition must match for it to work.  Simply add or remove additional column(s) in your external table definition.  Then when you are physically creating your table in the database eg via CTAS, add/remove the additional column(s) from your CTAS statement.

Alternately, consider the new COPY INTO statement.  This does have the ability to specify a column list, eg

COPY INTO test_parquet ( col1, col2, col3, col4 )
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.parquet'
WITH (
    FILE_FORMAT = myFileFormat
    CREDENTIAL = ( IDENTITY = 'Shared Access Signature', SECRET = '<Your_SAS_Token>' )
)

However please note this feature is i) currently in preview, ii) for copying into physical tables (ie not external tables) and iii) I haven't tested the column list specifically with Parquet.