How to aggregate over a "multi source" with recursive, in postgres?

It took a while, but I figured it out:

WITH RECURSIVE
initial AS (
    SELECT
        department_name,
        department_name,
        FALSE
    FROM
        budgets
    GROUP BY department_name
),
tmp (child, parent, root) AS (
    SELECT
        *
    FROM
        initial
    UNION ALL
        SELECT
            tmp.child,
            CASE WHEN
                departments.parent_department IS NULL THEN tmp.parent
                ELSE departments.parent_department
            END,
            departments.parent_department IS NULL
        FROM
            tmp
            INNER JOIN departments
                ON tmp.parent = departments.name
        WHERE
            NOT tmp.root
)
SELECT
    tmp.parent,
    SUM(budget)
FROM
    tmp
    INNER JOIN budgets
        ON tmp.child = budgets.department_name
WHERE
    tmp.root
GROUP BY tmp.parent


Invalid sync source in mongodb config server

As I am able to see in your error log file such as error Fetcher stopped querying remote oplog with error: InvalidSyncSource: sync source

As per MongoDB jira documentation here It seems that the common cause for the remote replSetUpdatePosition failing with those error codes NodeNotFound,NotMasterOrSecondary and InvalidReplicaSetConfig would be a change in config. Config changes currently will cause the oplog fetcher to fail because it will detect a change in the config version contained in the oplog query metadata. Subsequently, after exiting the oplog fetcher, we will go back to the sync source resolver which has its own blacklisting logic.

For your further ref here


The jobs are themselves stored in MSDB:

I believe taking regular backups of system-- MSDB database should do good:

Else you can perform some manual stesps as listed below:

  Start SQL Server Management Studio.
  Expand the SQL Server Agent folder, and then expand the list of Jobs.
  Right-click the job you want to create a backup script for, and then select Script Job as.
  Click CREATE To or DROP To, then click New Query Editor Window, File, or Clipboard to select a destination for the script. Typically,
  the destination is a file with a .sql extension.

Also, for multiple jobs to copy at one go:  In SSMS click on the Jobs folder under SQL Server Agent,  then go to Object Explorer Details.  You can select multiple jobs and script them all at the same time.


Which index type is best suited for exact-match against a list of values?

TL;DR: BTREE looks like it is a bit more efficient than HASH. You should read and adapt this test to your situation

Benchmark

I tried the two solutions for a geohash id column. Here is the script I used:

require 'set'

def random_geohash(precision)
  letters = %w(0 1 2 3 4 5 6 7 8 9 b c d e f g h j k m n p q r s t u v w x y z)

  letters.sample(precision).join('')
end

geohashes = Set.new

loop do
  geohashes << random_geohash(9)
  break if geohashes.length == 50_000
end

sample = geohashes.to_a.sample(1_000).map { |h| "'%s'" % h }.join(", ")

IO.write("perfs.init.sql", <<~SQL)
  DROP TABLE IF EXISTS "tests";

  CREATE TABLE "tests" (
  "id_btree" text NOT NULL,
  "id_hash" text NOT NULL
  );

  CREATE INDEX "index_id_btree" ON tests USING btree (id_btree);
  CREATE INDEX "index_id_hash" ON tests USING hash (id_hash);

  INSERT INTO tests (id_btree, id_hash) VALUES #{geohashes.map { |h| "('%s', '%s')" % [h,h] }.join(", ")};
  VACUUM ANALYZE;
SQL

IO.write("perfs.btree.sql", <<~SQL)
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT * FROM tests WHERE id_btree IN (#{sample});
SQL

IO.write("perfs.hash.sql", <<~SQL)
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT * FROM tests WHERE id_hash IN (#{sample});
SQL

IO.write("perfs.end.sql", <<~SQL)
  DROP TABLE IF EXISTS "tests";
SQL

`psql --user=john_doe -qAt -f perfs.init.sql my_db`
`psql --user=john_doe -qAt -f perfs.btree.sql my_db > perfs.btree.json`
`psql --user=john_doe -qAt -f perfs.hash.sql my_db > perfs.hash.json`
`psql --user=john_doe -qAt -f perfs.end.sql my_db`

This will give you two explain analyze (perfs.<index>.json) that you can now read. I suggest using Pev for a comfortable lecture. Here are the text results for PG11 on my mac:

BTREE

Index Scan using index_id_btree on public.tests  (cost=0.29..2611.50 rows=1000 width=20) (actual time=0.988..4.347 rows=1000 loops=1)
  Output: id_btree, id_hash
  Index Cond: (tests.id_btree = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
  Buffers: shared hit=3014
Planning Time: 1.475 ms
Execution Time: 4.424 ms

HASH

Bitmap Heap Scan on public.tests  (cost=1179.75..2758.75 rows=1000 width=20) (actual time=1.784..15.275 rows=1000 loops=1)
  Output: id_btree, id_hash
  Recheck Cond: (tests.id_hash = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
  Heap Blocks: exact=304
  Buffers: shared hit=1463
  ->  Bitmap Index Scan on index_id_hash  (cost=0.00..1179.50 rows=1000 width=0) (actual time=1.714..1.714 rows=1000 loops=1)
        Index Cond: (tests.id_hash = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
        Buffers: shared hit=1159
Planning Time: 1.495 ms
Execution Time: 15.446 ms


how can an additional natural join with the same table twice *reduce* the row count

Basically, NULL is causing this, because NULL<>NULL. One of the columns in your self-joined table will be all NULLs.

Here's a little test case that shows why this can happen. Naughty NULL equality and the way NATURAL JOIN works, picking column names to join on for you.

Setup:

create table one ( a integer, b integer );

CREATE TABLE two ( A INTEGER , c integer);

insert into one values (1,1);

insert into two values (1,1);

insert into two values (1,null);

insert into two values(2,NULL);

Queries:

SELECT COUNT(*)
FROM ONE
NATURAL JOIN TWO tttt;

SELECT COUNT(*)
FROM ONE
NATURAL JOIN TWO tttt
NATURAL JOIN TWO ttttt;

The first query will give 2, the second query 1.

Your problem will just be a more advanced version of this.

Use JOIN .... USING with the columns of your own choice to workaround this.


Assign value to a field after checking a condition

order_until is data type time, which is an odd design - typically you would have timestamp or timestamptz here to include the date. Be that as it may, the column cannot be 0, which is not valid for time.

To get on with it, let's assume you want NULL instead, which would make sense for order_type = 'NO_ORDERING'. That's simple because the default DEFAULT value of any column is NULL and you needn't do anything extra. Seems like you want more than just a default ...

Next problem in your question: do you just want to enforce the rule or actually write NULL to the column (overwriting offending values), and if so, do that for INSERT and / or UPDATE?

To enforce your rule (exactly):

ALTER TABLE merchants ADD CONSTRAINT home_delivery_cannot_have_time
   CHECK (order_type <> 'HOME_DELIVERY' OR order_until IS NULL);

The documentation:

  A check constraint can also refer to several columns.

Follow the link, there's more.

SQL Fiddle.


Postgresql and jsonb: perform a like over all values of an array

You can use the following query

SELECT
    id_user
FROM
    users
    JOIN LATERAL jsonb_array_elements(emails) AS x(email) ON true
WHERE
    email::text like '%email%' ;

jsonb_array_elements() returns a SET OF jsonb objects. You join them to user_id using a JOIN LATERAL. Then use a WHERE clause where you convert these jsonb objects to text, and then use your like '%email%'.

You can check a simple setup at dbfiddle.

NOTE: If you do not want repeated values: GROUP BY.


How to see the original text of the the function that has been wrapped by "DBMS_DDL.WRAP"

You can't get the original source back.  DBA_SOURCE, ALL_SOURCE, and USER_SOURCE are data dictionary views.  So you can do something like
select line, text
  from user_source
 where name = 'GET_OS_USER'
   and type = 'FUNCTION';

That will show you the wrapped text of the function.  But the point of wrapping your source code is to prevent users that have access to the database from recovering your source code.  It would defeat the purpose if you could just view the unwrapped source in the data dictionary.
If you are going to wrap the source of your procedures, you would need to have the unwrapped source outside of the database.  Presumably in a source control system.
A couple of notes-

Wrapping is a pretty uncommon thing to do.  It really only makes sense if you're delivering an application that clients will install and administer themselves but you don't trust your clients not to steal your intellectual property.  These days, most companies in this sort of position would just offer a software-as-a-service product rather than having customers administer their own installation.
There are presentations and some programs that you can find on the internet that walk through how to unwrap source.  So it's not strictly true that you can't get the original source back.  Newer versions of Oracle implement wrapping in a more secure way than older versions but no version is unbreakable.
If you do wrap your code, the actual SQL statements that it executes will be visible in the clear in various data dictionary tables like v$sql, in AWR reports, etc. If your clients are determined to reverse engineer what your application is doing, they likely can.  Wrapping just increases the degree of difficulty.


5 Connections/sec is slightly high.
5K QPS is high.
Max_used_connections is already very high.  Part of this could be due to nothing being cached in the buffer_pool after the restart, which was barely an hour ago.  What is Max_connections?
Aborted_connects/sec is quite high -- which is what you are asking about.  On the other hand, Aborted_connects/Connections is moderately high.  Any chance a hacker is trying to get in?
If some of those 'slow' queries run a long time and/or block other actions, that could explain some of the issues.  What is long_query_time?  (It defaults to a virtually-useless 10 seconds; change it to 1.)  Are the slow queries being logged?  300% CPU usually indicates inefficient queries (indexing, formulation, etc)

Recommendations:

Turn on the slowlog.
Run for several hours before analyzing.
Digest the slowlog to see what is naughtiest.
See which of these you would like to pursue:  http://mysql.rjweb.org/doc.php/mysql_analysis


PostgreSQL 9.3 is not returning existing rows with Ruby on Rails or Squirrel

The source of the issue was the name of one colunm of the table: PCF_index

psql generated "PCF_index" when exporting the schema. This caused trouble to Squirrel when trying to read it.

After the column is renamed pcf_index, the issue was solved.


Make MySQL dump from parts of each table

You need to use mysqldump option --where like this:

mysqldump -uroot -p --where="id % 2 = 1" db users > C:\Users\User\Desktop\ololo.sql

Give it a Try !!!


Does PostgreSQL take time to start streaming replication after restart master?

I want to make sure replication start as soon as server starts .

You can't. The server needs to start up, and it can only establish a streaming replication connection when it's up and ready.

  I want to know why this happens

Because there's no reason not to accept connections before streaming replication begins.

Among other things, if you're using WAL shipping instead of streaming replication, there might be no replication connection to wait for. Even if you're using streaming, you probably want the replica to keep on serving results if it loses its connection to the master.

  My thought was, when server started it will be ready to accept queries, and then after few seconds server start streaming replication. I though data will be lost in those few seconds.

It isn't.

The server is in recovery mode. Attempts to perform read/write queries will be rejected. This is the case whether or not there's a streaming replication connection.

After all, a server in hot standby mode that uses only a restore_command (no streaming replication) also rejects read/write queries.

You don't need to take any special action.

(In future, please try to include the background, the underlying problem, in your questions, so there's less guesswork and back and forth).


Emulate REGEXP like behaviour in DB2 SQL

Since this old question has been dug up anyway, I'll mention that you can use the built-in XQuery support in DB2 for regular expression matching, something along the lines of

select whatever
from users
where
   xmlcast(
     xmlquery('fn:matches($USER_NAME,"^a[aofdmep][a-z][a-z0-9]{4}[sidbfkfpo]")')
     as integer) = 1

XMLQUERY above calls the XQuery matches function against the column USER_NAME. The result is an XML boolean, so the XMLCAST is used to convert it to an SQL data type.


How to deal with async calls to a stored procedure that updates data

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a queue system to process the asynchronous calls so that you know for a fact the data is already updated before calling the second or to use some other mutex/singleton to deal with it in the database.

While I personally don't like doing it this way, you can use sp_lock: http://msdn.microsoft.com/en-us/library/ms187749.aspx

There are a few drawbacks, namely you're locking an entire object and forcefully making something synchronous.

Other ways involve query hints such as holdlock with xlock. These will force the other calls to wait but you may introduce deadlocks into your environment. At that point, forcing serialization may be what you want as an isolation level.

Since the calls rely on the previous calls being completed, it would make more sense to implement a background queue thread or class to deal with all of the calls in order as using sp_lock will cause concurrency issues later. You could also try to redo the design so that the previous call doesn't have to complete to get the correct information. Since we are scant on details I do not know if that's possible or not.


SQL Server Collation for Arabic, Hebrew, English and French

Language support comes from datatype: use nvarchar/nchar (which stored unicode basically)
Collation is sorting + comparing + non-unicode code page

So, one column can store all languages, but it can have only one collation. You can "add" extra collations using computed columns, or coerce it during a sort/compare using the COLLATE clause:

Example

ORDER BY
    LatinCollationCol COLLATE Arabic_CI_AI_KS = N'عربي/عربى';


Error wile debugging (signature buffer too small)

Its a known bug, fixed in 9.2.0.5.
This is the note on Oracle Support that describes this error:

Bug 2829071 - PLS-801/OERI:[15419] when debugging PLSQL from JDWP debugger (Doc ID 2829071.8)


Error in attaching mdf file from a crashed server to a new server

In general if you have a crashed server and need to bring up your databases on a new server you want to restore from backup not copy and reattach the mdf files.  The crash could have caused corruption that will not exist in the backup.

You can restore your system databases as well although it isn't always recommended.  If you are going to you need to make sure your new system is as close to the old as possible.  Particularly the patching level for SQL Server.

If you do not have backups, and you absolutely have to have your system databases from the old server then you have two options.  You can either attach as a different dbname and copy information over (msdb and model only since it won't work for master).  Or you can tell your instance that the new location of the system database is actually the correct one.

So in your case you would copy the mdf & ldf msdb files to your new server.  Again make sure your patching is the same as the old server.  Then follow the instructions in this link to "move" your msdb files.  In other words you are going to tell it that the files from the old server are actually the correct ones.  Once done you will need to re-start your new instance.  Then make sure you keep the original msdb files from the install just in case.

Of course the other way to "move" your msdb files is to shut down your instance, move/rename the installed msdb files and copy in your old msdb files making sure they are in the same location & have the same name.  Then restart your instance.  If anything goes wrong you can simply move the installed msdb files back into place (while the instance is shut down) and try a different method.


What is the last Mysql Version supported in REDHAT 6?

Here's a list of supported platforms: http://www.mysql.com/support/supportedplatforms/database.html.

The MySQL yum repo is detailed here: http://dev.mysql.com/downloads/repo/ - That has binaries for MySQL 5.5, 5.6 and 5.7.


Getting related data from an uncertain column

It looks like some of your difficulty may be order of operations related. For example, your

where f.user1_id = 1 or f.user2_id = 1 and f.status = 2

is evaluated as

where f.user1_id = 1 or (f.user2_id = 1 and f.status = 2)

It may be more straightforward to start from the profile table of the user you're starting from :

from [profile] p1
left join city c
    on p1.city_id = c.city_id
left join friendship f
    on p1.[user_id] in (f.user1_id,f.user2_id)
    and f.[status] = 2
left join [profile] p2
    on p2.[user_id] in (f.user1_id,f.user2_id)
    and p1.[user_id] <> p2.[user_id]
where p1.[user_id] = 1


How can I configure SQL Server 2012 to use multiple NICs?

As long as the OS is configured for NIC teaming SQL server will use the team.  SQL Server doesn't talk directly to the network cards, all the communications goes through the TCP/IP stack which is a part of the OS.  However the OS is configured to use the NICs will be how SQL Server uses the NICs.

To do what you describe will require doing NIC bonding (aka. NIC teaming).


If the last executed command is something that updates a table you could check:

SELECT COUNT(1) TablesAccessedInLast10Min
FROM information_schema.tables
WHERE update_time >= (NOW() - INTERVAL 10 MINUTE);

If you cannot login to MySQL, binary logging is enabled, and the binary logs are located in /var/lib/mysql, you could examine the time stamp of thew last binary log:

cd /var/lib/mysql
ls -l mysql-bin.0* | tail -1

If you want to monitor any movement as far as logging in, you may have to check out the general log. In mysql 5.1.38, I think you can convert it to a MyISAM table. You will have to index it on the datetime. There is a field called user_host. You could examine that field within the last 10 minutes.


SQL getting IDs from one table with multiple entries in another table

Apparently, your PersonAttributes table is designed using the EAV model. This model has the advantage of being easily extensible: attributes are stored as rows, and adding new rows is easy. However, querying this kind of table is more difficult than those designed the traditional way (attributes stored as columns).

Your solution is quite illustrative of how much more trouble it can be to accomplish a fairly simple task with an EAV-modelled table. It is actually one of the common ways to solve a problem like yours, although I would suggest you try rewriting it without using derived tables – like this:

SELECT p.ID
FROM Persons AS p
  JOIN PersonAttributes AS paB ON p.ID = paB.ID
  JOIN PersonAttributes AS paC ON p.ID = paC.ID
  JOIN PersonAttributes AS paD ON p.ID = paD.ID
WHERE paB.Attr = 'b'
  AND paC.Attr = 'c'
  AND paD.Attr = 'd';

The performance will likely remain the same as with your syntax, but without making the query any faster this rewrite will at least make it more concise and arguably more readable.

That being said, there is another method, fairly common as well, that you could employ, which might offer better performance as the number of the attributes increases. It uses grouping and aggregation:

SELECT
  ID
FROM
  PersonAttributes
WHERE
  Attr IN ('b', 'c', 'd')
GROUP BY
  ID
HAVING
  COUNT(*) = 3
;

By this method, all rows that have any of the specified attributes are retrieved and grouped by ID. In order to determine the groups (persons) having all three attributes, a HAVING filter is introduced to compare the number of rows* in each group to the total number of attributes in the IN list.

The method can be slightly generalised if you can afford storing the attributes to search for in a (temporary) table. Here is what it would look like in that case:

SELECT
  pa.ID
FROM
  PersonAttributes AS pa
  INNER JOIN QueriedAttributes AS qa ON pa.Attr = qa.Attr
GROUP BY
  pa.ID
HAVING
  COUNT(*) = (SELECT COUNT(*) FROM QueriedAttributes)
;

No WHERE clause here – it is replaced by the join to the table of queried attributes, and the total number of attributes required to match is derived from the same table instead of being hard-coded.

This kind of problem is commonly known as relational division. It is discussed in detail in this article by Joe Celko:

Divided We Stand: The SQL of Relational Division

*This particular implementation of the grouping method assumes there is always one row per attribute per person, so COUNT(*) works correctly. If attributes of the same kind may, or will later be allowed to, repeat per person, use COUNT(DISTINCT Attr) instead.


Bulk exporting SQL Server 7 data and bulk importing it to SQL Server 2008 R2

The BCP utility attempts to connect to the default instance if you don't specify the -S parameter.  My guess is there is no default instance available for the 'import' BCP.


"undefined function pg_pconnect()" when connecting to Postgres from CodeIgniter

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'pgsql:host=localhost;dbname=Absences_DB',
    'username' => 'postgres',
    'password' => 'XXX',
    'database' => 'Absences_DB',
    'dbdriver' => 'pdo',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

Make sure you have the PDO driver enabled in PHP.


How to determine the service pack of Sql Server

You can execute

SELECT @@VERSION;

Which will give you the relevant information (note the bold, italicised SP1 from the example results)

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)     Oct 19 2012
  13:38:57      Copyright (c) Microsoft Corporation     Developer Edition
  (64-bit) on Windows NT 6.2  (Build 9200: )


Detect if the family record is incomplete to avoid partial deletion

Why further optimize two separate queries into one query? It only makes the query less readable, and more difficult to understand, or to make changes when this  is needed (in the future):

UPDATE MAIN SET save = 'yes'
FROM  temp_table MAIN
WHERE save IS NULL AND EXISTS
    (SELECT 1
    FROM acct
    WHERE acct.parent_acct_id= MAIN.acct_id
    AND ( NOT EXISTS
        (SELECT 1 FROM temp_table temp WHERE acct.acct_id = temp.acct_id)
    ) OR EXISTS
    (SELECT 1
    FROM acct
    WHERE MAIN.acct_ID = acct.acct_ID
    AND acct.parent_acct_id IS NOT NULL
    AND NOT EXISTS
        (SELECT 1 FROM temp_table temp WHERE acct.parent_acct_id = temp.acct_id)
    ))


By "source file", do you mean the source command in the commandline tool mysql?  If so, you need to kill that client, not the individual commands in PROCESSLIST.

Go to the OS's task manager.  (You may need to be root.)

# ps -axl | grep mysql  (or other args of your picking)
# kill -9 1234567       (using the PID from the ps)


No

A trigger is part of the transaction
An error will abort the INSERT statement (if not in an explicit transaction)
An error in a trigger will doom a transaction (that is, must be rolled back)
An error in a trigger where TRY/CATCH blocks are not used (around the INSERT too) will abort the batch

Don't use a trigger. Use a stored procedure instead to decouple the logic from the INSERT.

Personally, a trigger failing means some complex DRI has failed or I can't write to a history table. That is a bad thing...

Edit:

Use Service Broker to send an notification from the trigger.
Another piece of code will pick it up and send asynchronously

I haven't implemented myself so I'll have to refer you to Google


Is there a way to execute SQL statement based on SQL Server version?

You can use dynamic SQL

I mean first check the version

Then build your SQL statement using a string variable, for example nvarchar(max)

Then execute it by sp_executeSQL

I think following script can work for this task

-- check for sql server version
declare @sql nvarchar(max)
if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) >= 10

set @sql = N'CREATE unique nonclustered index ix1_table ON [table] (column1, column2)
        WHERE column1 is not null and column2 is not null'

        ELSE

set @sql = N'CREATE nonclustered index ix1_table ON [table] (column1, column2)'

exec sp_executeSQL @sql


Oracle Patch Level Differences (Oracle Enterprise Manager and DBA_REGISTRY_HISTORY)

The installation of a PSU is not complete without the execution of the postinstall script. Opatch works at file level, it replaces/relinks files in the Oracle home. Changes inside the database are performed by running the postinstall script. Sometimes I see DBAs forget this step.

The postinstall script maintains the SYS.REGISTRY$ table (on which the DBA_SERVER_REGISTRY view is built) with the actual patch versions.

In 11.2, you run the postinstall script as (stated in the README):

sqlplus / as sysdba
@?/rdbms/admin/catbundle psu apply

This populates the above table (and so the view) with the actual data. When installing a PSU, this script must be executed in all databases running from the affected Oracle Home.

In most of the cases, skipping the postinstall script does not cause any problem, so mostly this goes on unnoticed. But there are exceptions, for example I have seen a database throwing ORA-07445 errors during the configuration of GoldenGate, because the postinstall script was not executed after the installation of the PSU.

Starting with 12c, the postinstall script is executed as:

export ORACLE_SID=orcl
cd $ORACLE_HOME/OPatch
./datapatch


Distributed Availability Group Direct Seeding FAILED, failure_state SQL Error, failure_state 2

The AlwaysOn Professional blog has some general troubleshooting steps for direct seeding and also includes some details about trace flag 9567 to enable compression during seeding, but I didn't find any details about the SQL Error or Seeding Timeout.

We previously have had issues with large databases causing problems in availability groups, but this usually is resolved by applying the latest transaction logs from the primary against the replica.

In this case the databases were listed on the secondary availability group as recovering, so I tried applying the latest transaction log backups from the primary and then joining the database to the secondary availability group:

--Restore transaction logs from primary and stay in recovery mode. Multiple backup files may need to be restored from oldest to newest.
RESTORE LOG stackoverflow from disk = '\\Backups\SQL\_Trans\StackOverflow_AG\StackOverflow\StackOverflow_LOG_20170810_175400.trn' WITH NORECOVERY;

ALTER DATABASE stackoverflow SET HADR AVAILABILITY GROUP = [StackOverflow_RAG];
ALTER DATABASE stackoverflow SET HADR RESUME;

This worked for both of the failed databases and fixed the replication issues. Our reporting cluster now has all databases kept in sync from the primary availability group:


Data sync between two local Mysql databases in different system in same netwrok

You need MySQL replication.
Here is the official documentation https://dev.mysql.com/doc/refman/8.0/en/replication.html
I like to use step by step guides, so here's a more simple explanation: http://www.clusterdb.com/mysql-cluster/get-mysql-replication-up-and-running-in-5-minutes
Those docs should work for MySQL 5.X and 8.X.


SQL Server parallelism for DML operations

SQL Server supports parallelism for some of its physical operators.

DML statements, at a high level, are logical operations. In other words, you specify what you want done, not how it's done. As long as the logical operation is maintained, SQL Server is free to satisfy the statement using any set of physical operators it deems appropriate -- that applies both to DML and DDL.

SQL Server uses the cost threshold server setting to consider using parallelism for the physical operators chosen in the query plan. There's no difference as far as parallelism is concerned between DDL and DML, because as I said, SQL Server parallelizes the physical operations (i.e., an index rebuild can use the same Index Scan physical operator as a SELECT or UPDATE query could).

So, the short answer is yes, SQL Server can parallelize DML, but aside from the cost threshold setting, you have very little control over it. (That said, it can be restricted by using things like MAXDOP hints.)

Related, but a bit aside: for a query plan that includes parallel operators, the DOP at which the query runs is selected dynamically at execution time, not at plan compile time.


MS-Access is rather picky at how it wants the joins to be formed. Add parentheses:

SELECT A.Name, B.Quantity, C.Quantity
FROM
  ( A LEFT OUTER JOIN B ON A.ID = B.ID )
  LEFT OUTER JOIN C ON B.No = C.No ;

Standard SQL syntax - and most other DBMS - do not need require parentheses in the FROM clause, although you are allowed to use them for clarity.


Pragmatic approach to k-means clusters with PostgreSQL

you could use the ranking returned from the text search as a cut-off to decide if the question matches a topic.
For each category you could hold a document with relevant terms and search the query text against these terms.

Btw, there's a nice extension called madlib (it's more of a utility package) - it contains many useful features/algorithms including topic analysis & clustering.
Take a look at madlib documentation


SQL Server report based on stored procedure not showing columns

Add Fields manually which not refreshed under Dataset properties.

Datasets --> Right Click --> Properties --> Fields --> Add.

mentioned column name and parameter name as per query.


Can I see Historical Queries run on a SQL Server database?

Similar Grant Fritchey had the issue where he had closed SSMS and lost the query he had been working on...blogged about here:
Oh **********!

EDIT

To make this a bit more detail of an answer, the referenced linked above Grant provides a query to simply go to the cache on the instance to pull out the query you had just executed (or atleast attempt to):

SELECT  dest.text
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   deqs.last_execution_time > '5/19/2011 11:00'
        AND dest.text LIKE 'WITH%';

A few more options that were noted in the comments of Grant's blog:

Jamie Thomson points where SSMS actually has a "recovery" area under your Windows profile, similar to Word or Excel recovery.
Another individual notes in comments about the SSMS Tools Pack, but this add-on is only free for SQL Server 2008. Starting with SQL Server 2012 it is paid only, but has many features you might find useful.


On a whim, when double checking permissions, I double clicked on the actual DB mail executable. The result on the SQL Server 2014 machine was an empty command window. On SQL Server 2016 clicking the DB Mail executable presented this message:

I cannot find this requirement anywhere in the SQL Server 2016 documentation, yet it is clearly a requirement. DB Mail is functioning perfectly with no other changes than installing .NET 3.5.


sqllogship.exe - how to get it to encrypt log file when backing up log on source server?

No way to do this natively through the log shipping GUI or command line with logship.exe

You can roll your own "poor man's log shipping" scenario. Basically all you are doing is backing up, copying, restoring with standby or norecovery and building some alerting.

An older post but an example of what I mean.


Hekaton does not support direct memory access from client applications. In theory an application could access the same memory space that SQL Server uses using OS memory sections. That is not implemented, though.

The shared memory transport has nothing to do with this. Indeed, it uses a shared memory region to transfer data. But it still runs the normal TDS protocol (including the data conversion and serialization that this involves).


How can I free space in oracle mount point (/u01/app/oracle/)?

If your oracle installation is on your root volume you should have a look at the DIAG_DESTINATION, e.g. /u01/app/oracle/diag/. If there's a lot of old trace files in there you can safely remove them with adrci. If your oracle installation is on a separate disk you have to check where the space is consumed (/var?) and free space there.


Compare two columns and replace duplicates with nulls

Without window functions this is pretty hard in MySQL. Since the two columns - t1 and t2 - store similar content and you only want disctinct values from both, it would be much easier to get the values in a single column:

select t1 as tx from t
union distinct
select t2 from t ;

I don't see any reason to have the original convoluted result, except if you want to keep info from other columns in the table and just remove duplicates from these 2 columns. And in that case, an UPDATE would make more sense.

Here is a method to get this result, anyway. It assumes that (id) has a UNIQUE constraint. Tested at dbfiddle.uk:

select t.id, gt1.tx as t1, gt2.tx as t2
from t
  left join
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut
    group by ut.tx
  ) as gt1
  on t.t1 = gt1.tx and t.id = gt1.id
  left join
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut
    group by ut.tx
  ) as gt2
  on t.t2 = gt2.tx and t.id = gt2.id and gt1.tx <> gt2.tx
 ;

Notice that in MariaDB (MySQL's first cousin), that has CTEs, the same query can be rewritten more compactly and more clearly:

with gt as
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut
    group by ut.tx
  )
select t.id, gt1.tx as t1, gt2.tx as t2
from t
  left join gt as gt1
    on t.t1 = gt1.tx and t.id = gt1.id
  left join gt as gt2
    on t.t2 = gt2.tx and t.id = gt2.id and gt1.tx <> gt2.tx
 ;

Logically, both variations work the same way, though. This subselect:

  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut
    group by ut.tx
  )

returns all distinct t1 and t2 values along with the ID of the first1 row where each is encountered. For your example, it produces this output:

tx   id
---  ---
a    1
b    1
c    2
e    3
f    4
g    4

The above set is joined against the original table twice, on t1 and on t2. In each case, where the original row's ID matches the subselect row's ID, the value is returned intact, because the match indicates that the row is the value's first occurrence; otherwise the value is replaced with a null.

1“First" in the order of ID.


How to multiply rows for a column that contains negative and zero values?

The magic of NULLIF seems to do the trick for the test case in your question. Since you used a different example than in your SQL Fiddle, I don't know if that's what you want there too.

CREATE TABLE dbo.Ids
(
    Id INT NOT NULL IDENTITY(1, 1),
    Value INT,
    Name NVARCHAR(3)
);
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'a', 1 );
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'a', 2 );
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'b', 0 );
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'b', 1 );

SELECT   Name,
         CASE WHEN MIN(Value) = 0 THEN 0
              WHEN MIN(Value) > 0 THEN EXP(SUM(LOG(NULLIF(Value, 0)))) -- trying to get the product of all rows in this column
         END AS Product
FROM     Ids
GROUP BY Name;

Returns:

Name    Product
a       2
b       0

If you need a more general solution that handles negative numbers and other edge cases, see for example The Product Aggregate in T-SQL Versus the CLR by Scott Burkow. One T-SQL construction from that article is:

EXP(SUM(LOG(NULLIF(ABS([Value]), 0))))
*
IIF(SUM(IIF([Value] = 0, 1, NULL)) > 0, 0, 1)
*
IIF(SUM(IIF([Value] < 0, 1, 0)) % 2 = 1, -1, 1)

As to why your original CASE expression did not work as expected, from the documentation for CASE (Transact-SQL) (emphasis added):

  You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.


MySQL Load Balancing what do i need Replication or Cluster?

That topology will help scale reads.  Is that what you need?

Writes must go to the Master, from which they will be 'replicated' to the Slaves.  The Slaves will be readonly.

So, send any write, together with associated reads, to the Master.  Send all other reads to a Slave in a round-robin way.

High CPU usually means you have a query that needs optimizing.  Let's see such a query, plus SHOW CREATE TABLE.

Depending on what you say to those comments, we may move into talk of Galera Cluster.


What does it mean there is multiple sources in a SELECT's "FROM" clause?

Implicit vs Explicit JOIN Syntax

It's called a SQL-89 Implicit (Cross) Join. You should prefer the more modern Explicit syntax. In PostgreSQL the Implicit syntax has a different precedent, and it currently serves as an optimization fence. What you actually want is a CROSS JOIN and because you're using the variables from the left table in a function call what you want is a CROSS JOIN LATERAL.

SELECT
    name,
    foobar.x,
    foobar.y,
    foobar.z
FROM stuff
CROSS JOIN LATERAL pg_temp.extract_stuff (contents) AS foobar;

The reason for the preference is style: the newer syntax allows for syntactical binding between the JOIN-clause and the condition that provides the JOIN's selectivity. While technically this is no different, it makes it easier to maintain and read. The SQL-89 method is to stuff the JOIN-selectivity in the  WHERE-condition. This makes maintaining the query a ton easier.

SELECT f.g
FROM f AS f1
JOIN f2 USING (fid);
JOIN f3 ON f3.fkey_id = f.id
WHERE f.x = 15;

Versus,

SELECT f.g
FROM f, f2, f3
-- Potentially hundreds of lines.
WHERE f.x = 15          -- no
AND f.id = f2.id        -- specific
AND f3.fkey_id = f.id;  -- order

Expanding the scope of the question to [INNER] JOIN, you may want to stare at this example for a bit,

SQL-92: Explicit JOIN with Explicit INNER

SELECT *
FROM a
INNER JOIN b ON a.id = b.id
INNER JOIN c ON b.ref = c.id;

SQL-92: Explicit JOIN with Implicit INNER

SELECT *
FROM a
JOIN b ON a.id = b.id
JOIN c ON b.ref = c.id;

SQL-89: Never use without reason

SELECT *
FROM a, b, c
WHERE a.id = b.id AND b.ref = c.id;

SQL-92: CROSS JOIN with JOIN's selectivity-clause written as a WHERE-clause. Some would say this is "flattened".

SELECT *
FROM a
CROSS JOIN b
CROSS JOIN c
WHERE a.id = b.id AND b.ref = c.id

SQL-92: Explicit JOIN with goofy order (may be useful for optimizing, depending on RDBMS)

SELECT *
FROM a
JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Unless you have a reason, you should always prefer the first or second syntax.

USING clause

USING is shorter (less to type), and more terse. This can also save you some work and seems less problematic from my perspective.

Column Listing

USING adds the nicety of not reproducing the column when * is expanded. Let's again look at some examples,

This will return one row with one column: (a=1). It can do this because its t.a and g.a are marked as the same with USING and as the equijoin condition they must be equal.

-- Returns one column with a=1
SELECT *
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
  USING (a);

This will return one row with two columns: (a=1,a=1).

-- Returns two columns with a=1,a=1
SELECT *
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
  ON t.a = g.a;

That's nice, but even if you never use a there is something to be had with USING.

This will gladly work

SELECT a=1
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
  USING (a);

This dies: requires explicitly picking an 'a' to disambiguate

SELECT a=1
FROM ( VALUES(1) ) AS g(a)
INNER JOIN ( VALUES(1) ) AS t(a)
  ON t.a = g.a;

Mixed Use

Some detractors think it's nasty to mix USING with ON. I don't think so, but take a look for yourself.

SQL-92: With USING sugar, Explicit JOIN with implicit INNER token

SELECT *
FROM a
JOIN b USING (id)
JOIN c ON b.ref = c.id;

SQL-92: Without USING sugar, Explicit INNER JOIN

SELECT *
FROM a
INNER JOIN b ON a.id = b.id
INNER JOIN c ON b.ref = c.id;

Convention

USING establishes a convention from the spec of making your equijoins on id-columns that are globally unique: by this I mean it kills off the all too common convention of naming every table's surrogate key id. Generally, I think that's a admirable goal (or side effect) when possible.

Anti- JOINs

As a final note on SQL-92. SQL-92 provides the EXISTS predicate. This gives you the option to write an anti-join like this,

SELECT *
FROM t1
LEFT OUTER JOIN t2 USING (id)
-- could
-- be
-- lots of stuff
WHERE t2.id IS NULL;

Or, like this

SELECT *
FROM t1
WHERE NOT EXISTS (
  SELECT 1
  FROM t2
  WHERE t1.id = t2.id
);

For the same reasons presented earlier, the option with NOT EXISTS is better then WHERE t2.id IS NULL: it keeps the joining-conditionals semantically linked. And it clears up two otherwise potential areas of confusion. For instance

WITH t(txt,n) AS ( VALUES ('foo',null) )
SELECT txt
FROM t AS t1
LEFT OUTER JOIN t AS t2
  USING (n)
WHERE t2.n IS NULL;

Versus,

WITH t(txt,n) AS ( VALUES ('foo',null) )
SELECT txt
FROM t AS t1
WHERE NOT EXISTS (
  SELECT 1
  FROM t AS t2
  WHERE t1.n = t2.n
);

Without the NOT EXISTS syntax, it's not immediately clear whether the intent is to achieve an equijoin on NULL, or to select where there is no match. Though they get planned the same the NOT EXISTS predicate is more descriptive than an IS NULL predicate for the task.

Summary

My preference is for Explicit JOIN syntax with USING whenever possible. And to always write antijoins using NOT EXISTS ( SELECT 1... ).


Can I change MSSQL interpretation of date order system wide?

This is caused by the user being set to "British English". I've always considered it a bug (I imagine someone was told that UK dates had month & day inverted from the US norms, and they incorrectly assumed that applied to ISO8601-a-like dates also). This has been the case since at least SQL7 IIRC. It is irritating because in every other context YYYY-MM-DD is unambiguous.
If you explicitly convert string to dates using CONVERT() you can specify the string format that is being used using its optional third parameter. This may not help if the issue is with date type parameters being sent in SQL strings (instead of prepared queries with typed parameters) as the conversion in that case is out of your hands.
If you only ever provide dates in a year-first format then just avoid the British English user setting. This may not help if your application sometimes passes in non-US year-last dates.


Assume you have the following table TAB(name,value)

name value
a        2
a        3
a        2
b        4
b        5
b        5
c        6
c        6
c        6

what are the result sets of the following queries

query 1:

select name, sum(value)
from TAB
group by name
order by name

query 2:

select name, sum(distinct value)
from TAB
group by name
order by name

query 3:

select name, value
from TAB
group by name
order by name


Separate table by often changed / not often changed data?

Yes, it will make a difference.  How big a difference will depend both on how many rows the table has and how many columns as well as how frequently the table is read from and updated.  Depending on the database, the block size and how many rows are updated at the same time could also be factors.

Keep in mind the overhead you are introducing by splitting the table for how often the data will be queried separately vs how often it will need to be joined and the performance requirements of various reading and writing activities.


Increased memory to server, but do I need to change page-file size?

I believe the 2x size of RAM recommendation is outdated and unrealistic.  If a server only has 32 GB of RAM it might make sense,  but it's a waste of expensive disk space to make it 2x on a system that had a massive amount of memory.
There are two primary purposes of the pagefile:

To use as a swap file for memory.  If SQL Server and other applications are configured correctly to limit the amount of memory available, it will never be needed for this purpose.  If the system does have to use the pagefile to swap memory, system performance will be so bad you'll effectively have an outage and be forced to deal with it immediately.  So if you configure a 1 TB pagefile, it won't help performance when the system starts swapping to disk and you'll be forced to do something about it before it needs to swap even 100 GB.

To hold a memory dump if the system crashes (on the volume where OS is installed).  Again, if a  server only has 32 GB of RAM, it makes sense to configure it to do a full memory dump if the system crashes.  However, it is rarely necessary for diagnostics purposes to need a full memory dump, anda kernel memory dump is the reasonable choice on a system that has a lot of memory.  Also, Windows crashes are much less frequent these days than in 1996 (or whenever) when that 2x recommendation was made.  Also, if the system crashes and you get a 1 TB memory dump (you'll need another TB free on c: for it), good luck zipping that sucker and transferring it to Microsoft.  I would only configure for a kernel dump and only change that if the system is crashing and Microsoft needs a full memory dump to diagnose the issue.

So if you're super paranoid (which is certainly justified in some situations) and have 2 TB to allocate that will likely never be used, then a gigantic pagefile is a consideration.  However, it's rarely going to be really necessary.
So my answer is that you are highly unlikely to need to increase the pagefile size.  It probably hasn't been used at the current size, and with more memory now, it's even less likely to be used, so it would follow that there's no need to waste more disk space on a bigger one.


You say

  Above plan is for subid = 11 or 7 in @t table variable

I think you may be under a misapprehension here. SQL Server does not look at the contents of the table variable and choose a plan based upon the values it contains.

The statement is compiled before the table variable contains any rows at all and you will get the same plan (that assumes a single row) regardless of whether it eventually contains 2 (and would match 95.5% of the rows) or 1 (and would match only 0.0008%).

The table variable may of course also contain multiple rows but SQL Server will not take account of that except if you use the OPTION (RECOMPILE) hint and even then there are no statistics on table variables so it cannot take any account of actual values.

Some alternate plans are below

These require finding all matching rows and sorting them.

Because NCx_1 is not declared as a unique index the include(QueueItemID) is ignored (as explained in More About Nonclustered Index Keys) and QueueItemID gets added as an index key column instead. This means that SQL Server can seek on IsProcessed, QCode and the matching rows will be ordered by QueueItemID.

The plan in your question therefore avoids a sort operation but performance is entirely reliant upon how many rows in practice need to be evaluated before the first one matching the SubID IN (SELECT SubID FROM @t) predicate is found and the range seek can stop.

Of course this can vary wildly depending on how common the SubID values contained in @t are and whether there is any skew in the distribution of these values with respect to QueueItemID (You say that both around  350k rows match the seek predicate and that around  350k end up being seeked so for SubID = 7 it sounds like these are all at the end or perhaps no rows match at all - which would be the worse case for this plan).

It would be interesting to know what the estimated number of rows coming out of the seek is. Presumably this is much less than 350,000 and thus SQL Server chooses the plan you see based on this estimated cost.

If the table variable will always just have few rows you might find this rewrite works better for you.

SELECT TOP 1 QueueItemID
FROM   @t
       CROSS APPLY (SELECT TOP 1 t.QueueItemID
                    FROM   QueueTable t
                    WHERE  t.IsProcessed = 0
                           AND t.QCode = 'USA'
                           AND SubID = [@t].SubID
                    ORDER  BY t.QueueItemID) CA
ORDER BY QueueItemID

For me it gives the plan below where it seeks into the index on subid,isprocessed,qcode,queueitemid as many times as you have rows in the table variable. It is similar to the first plan shown but may be slightly more efficient as each seek stops after the first row is returned.


What MySQL Shell version are you using?

<Cluster>.options() was introduced in version 8.0.14:

https://mysqlserverteam.com/mysql-innodb-cluster-changing-cluster-options-live/


we face the same problem. Apparently this issue is fixed in version 10.16. I tested 10.9,10.13,10.14,10.5 and all have the same issue.
But with 10.16 it's ok.
I don't see in release note any information on it.


Nulling other Date parameters in SSRS on selection of one

In the screenshots you're setting the expression for the Default values which will only change the values on the initial display of the report. This will not do anything to keep a user from changing those values, so you can easily get a render request with all parameters populated with something other than the default.

I'd suggest making it clear to your report users what is happening with these dates, don't do magic with their parameters behind the scenes. To do this, create an option in each parameter set (you can do something like UNION and hardcode the label and key) to ensure that report execution not only does what you expect, but makes this is clear to any other developer or user.

The user might select something like:

Param1 - "Not Used"
Param2 - 20151202
Param3 - "Not Used"

You can force this behavior with cascading parameters. You can run into issues with updates to cascading parameters though.

Setup like this:

@Param1

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES

@Param2

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
WHERE -1 = @Param1

@Param3

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
WHERE -1 = @Param1
AND -1 = @Param2

This ensures that you can only select the "Not Used" option for param2 if param1 selected anything except "Not Used". You can still set the default to be not used for all of these.


Most suitable DB Architecture for real-time high-volume processing API

Are you doing a round-trip to the DB for every request? If so, the classic cause of performance deteriorating as a table grows is stale statistics, as in, a query plan that made sense when a table was small is suboptimal once it grows. Check the query plans. Also check that you have auto stats creation, update, and async update enabled. I am assuming that you are already using a clustered index.

There is no point moving your hot table to a different database unless you a) understand what it is that you are waiting on and b) the different database relieves that. E.g. if you are waiting on I/O perhaps moving to a different database stored on different disks might help - but if its running through the same storage controller, it might not. If you are waiting on CPU, then this will not help, you would need to add CPUs or move it to another host. If you are waiting on lock contention, then this will not help, you will need to use a more suitable isolation level. So first, perform the necessary analysis to see where your SQL Server is spending its time.

But also consider if a round-trip to the DB is necessary for every query, in C# for example there are data structures offering O(1) access. This may be a better approach.


The broker in the sender's database is in single user mode

After a database restore, Service Broker queues with activation do not appear to have their queue monitors set back up properly, even if WITH ENABLE_BROKER is specified during the restore.

Explicitly re-enabling service broker causes the queue monitor to get recreated properly.  If anybody has a better workaround, I'd love to hear it.

You can check for the problem by querying dm_broker_queue_monitors for any queues that are in the undocumented state 'DROPPED' that do not also have corresponding queues in a different state (e.g. 'INACTIVE').

If this query comes up with anything, you have a broken queue:

select database_id,queue_id,[state] from sys.dm_broker_queue_monitors m
 WHERE state='DROPPED'
   and not exists (select 1 from sys.dm_broker_queue_monitors m2
                    where state <> 'DROPPED'
                          and m.queue_id=m2.queue_id
                          and m.database_id=m2.database_id)

You can fix it by re-enabling service broker (yes, even though it is enabled already; re-enabling it will cause the queue to get rebuilt properly.)  You need to be in single user to enable service broker:

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [dbname] SET ENABLE_BROKER WITH NO_WAIT
ALTER DATABASE [dbname] SET MULTI_USER


oracle format specifiers: to_number vs to_char

According to the documentation, the first value for a to_number needs to be

  a BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or
  NVARCHAR2 data type containing a number in the format specified by the
  optional format model

Therefore Oracle is implicitly converting 123.56 into a character value.  The problem then comes that the format for the to_number does not match the value being given.  This can be seen because you get the same error for the following two variations:

select to_number(to_char(123.56),'999.9') from dual;

select to_number('123.56','999.9') from dual;

But you don't for this:

select to_number(123.56,'999.99') from dual;

When converting characters to numbers the format specification must match exactly, but when converting numbers to characters the number can be selectively extracted using the format specifier.


11gR2 didn't ask me to set username or any password

If the Universal Installer included with 11gR2 did not ask for a password, it is likely that the option to create a database was not selected during installation.

Solution? Create a database with the dbca tool and supply a sys password when prompted.

With older versions, perhaps including Oracle Database XE (11g), you might have luck with one of the default password combinations of sys/change_on_install or system/welcome1 or system/manager. On engineered systems I have also seen sys/welcome1 and sys/changeme


Converting from InnoDB tables to innodb_file_per_table

The process you mentioned works fine but it takes much amount of time if the tables are bigger  in size
Here the way you can do it quickly .but it requires some down time depends on the table size .

Take the full backup of 20 tables that are InnoDB.
Drop those 20  tables that are InnoDB
Stop mysql
Delete the ibdata files
Start the mysql services with  innodb_file_per_table=1 in my.cnf
Restore those 20  tables that are InnoDB with taken backup.


Query to see Index Fragmentation on all databases without using SP or temp tables

It's not possible to achieve without using temp table as the DMVs/DMFs that we are using are database scoped and requirement is server scoped, and i don't see any difficulties or complication using temp table as follows:

Declare @Tbl table (ServerName varchar(128), DBName varchar(128), SchemaName varchar(128), TableName varchar (100), IndexName varchar (100), FragPercent float, IndexType tinyint, IsPrimaryKey bit);

Insert into @Tbl
exec SP_MSforeachdb @command1 =
            'use [?];
                select  @@Servername,
                        DB_NAME(),
                        sc.name as SchemaName,
                        object_name (s.object_id) as TableName,
                        I.name,
                        s.avg_fragmentation_in_percent,
                        I.type,
                        I.is_primary_key
                from sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, ''LIMITED'') as S
                    join sys.indexes as I on s.object_id = I.object_id and s.index_id = I.index_id
                    join sys.objects as O on s.object_id = O.object_id
                    join sys.schemas as sc on O.schema_id = sc.schema_id
                where o.type = ''U'' and avg_fragmentation_in_percent > 20 and (I.name is not null)
                ORDER BY  avg_fragmentation_in_percent DESC'
select * from @Tbl
go


Let's start with definitions then go into differences.

Primary Key
There can be only one Primary Key per table.  It is one of the candidate keys.  A candidate key is a key comprised of a column or group of columns that uniquely identify the row.  In SQL Server none of the values can be NULL but I can't say for certain that is true in other DBMSs.

Foreign Key
A foreign key is a column or group of columns that relate two tables together.    Typically this is called a parent/child relationship.  So for example a child table will have a Parent_Id column that refers back to the Parent table.  This relationship is one of the cornerstones of the relational part of the RDBMS (Relational DataBase Management System).  There can be multiple foreign keys on a table and in fact a foreign key can even relate a table back to itself.

Comparison
As you can see they really have nothing to do with each other.  The column(s) that make up the Primary Key can be the same column(s) that are used in a Foreign Key or even multiple Foreign Keys but they don't have to be.  The Parent table must related by one of the Candidate Keys but that does not have to be the Primary Key.


Tools for synchronizing database schema with Oracle, SQL-Server and Netezza databases

In the past, I have used ErWin from Computer Associates and PowerDesigner to move schema between RDBMSes. It's been many years since then, and I would evaluate their current feature set if I had to pick something today.


You need to use RESET SLAVE to tell the instance to forget about its former master.

The exact commands to run depend on your MySQL version.

For MySQL versions prior to 5.5:

stop slave;
change master to MASTER_HOST='';
reset slave;

For MySQL 5.5 and above:

stop slave;
reset slave all;

Either way, make sure that you also remove any “master-” statements from my.cnf if you have them.


Better way to ensure referential integrity of one column to a composite primary key?

In some complex domains (banking, telekom) name is always an entity not an attribute. An individual can have more than one passport (names, nationality) I would create a table structure for the scenario you provide such;

create table author (
  id integer PRIMARY KEY,
  default_name_id integer -- you may / may not need this
  -- , any other author properties
);

-- just name aliases
create table author_name (
  id integer PRIMARY KEY,
  author_id integer NOT NULL REFERENCES author(id),
  name text not null,
  unique (author_id, name)
);

alter table author add FOREIGN KEY (default_name_id) REFERENCES author_name (id);

create table books(
  id integer PRIMARY KEY,
  book_name text
);

-- a book can be published by more than one author
create table book_publisher (
  book_id integer    REFERENCES books (id),
  author_id integer  REFERENCES author (id),
  PRIMARY KEY (book_id, author_id)
);

Postgresql have array types. Its also very useful. However most of ORM's doesn't support it. If you can use arrays in your application code then its simpler;

create table author (
  id integer PRIMARY KEY,
  name text[],  -- name[1] is default name.
  -- , any other author properties
);


How to create a table in MySQL with automatic numeration of columns?

Unfortunately, MySQL does not have a PIVOT function and it sounds like you are attempting to get the data into the format:

+---------+-----------------+-----------------+-----------------+
| bike_no | repair_cost_Jan | repair_cost_Feb | repair_cost_Mar |
+---------+-----------------+-----------------+-----------------+
| ABC1234 |              70 |               0 |               0 |
| BMX5678 |               0 |              75 |               0 |
| BON3333 |               0 |               0 |              80 |
+---------+-----------------+-----------------+-----------------+

If that is the case, then in MySQL you will want to use an aggregate function along with a CASE expression to convert your rows of data into columns.

If you have a know number of months that you are returning then you can easily hard-code the query similar to the following:

select bike_no,
  sum(case when month(repair_date) = 1 then repair_cost else 0 end) repair_cost_Jan,
  sum(case when month(repair_date) = 2 then repair_cost else 0 end) repair_cost_Feb,
  sum(case when month(repair_date) = 3 then repair_cost else 0 end) repair_cost_Mar
from bike
where repair_date >= '2013-01-01'
  and repair_date <= '2013-03-31'
group by bike_no;

See SQL Fiddle with Demo.

But in your case it sounds like you want to change the start date and end date to your query. If that is the case, then you will want to use a prepared statement to generate dynamic SQL that would be executed:

SET @sql = NULL;
set @startdate = '2013-01-01';
set @enddate = '2013-03-31';
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN month(repair_date) = ',
      month(repair_date),
      ' THEN repair_cost else 0 END) AS `repair_cost_',
      monthname(repair_date), '`'
    )
  ) INTO @sql
FROM bike
where repair_date >= @startdate
 and repair_date <= @enddate;

SET @sql
  = CONCAT('SELECT bike_no, ', @sql, '
            from bike
            where repair_date >= ''',  @startdate, '''
               and repair_date <= ''', @enddate, '''
            group by bike_no');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo.  Both queries will generate the table of data above.


Moving Report Server Databases to Another Drive

There will be minimal downtime, so plan this in a maintenance window:

Don't use detach/attach!

Have verified backups!
Use Alter database...modify file... (do below for mdf and ldf files)

use master;
alter database dbname
modify file (
    name = 'logicalName',
    filename = 'E:\RS\db_name.mdf'); --> This is the new file location

Now offline the database:

alter database db_name set offline with rollback immediate;

Physically copy the mdf and ldf files to the new location.
Bring the database online:

alter database db_name set online;

If you script out and test out on a dummy database, you will see how much time it takes and will build your confidence.


How do I login to SQL Server via group membership in another domain

In my case I was able to narrow down my trouble by working with my sysadmin about how the group was defined.

Knowing that the SQL Server was hosted on a machine that had joined another domain and was running with a user account on that other domain, we looked at the Group Scopes in active directory.

My user could authenticate properly after the scope was changed to universal.

If I find an authoritative source on SQL Server's behavior with respect to these domain group types, I'll be sure to update this answer.


Having trouble with a T-SQL scalar variable during a create table

DECLARE @tt = 'TempNWA'

is missing any dataype declaration. You haven't explained what you are trying to do exactly but I assume you need something like.

SET NOCOUNT ON
DECLARE @TempTable SYSNAME = 'TempNWA'

DECLARE @SQL NVARCHAR(MAX) = N'
CREATE TABLE ' + QUOTENAME(@TempTable) + N'
 (  [Table_Name] varchar(50),
    Row_Count int,
    Table_Size varchar(50),
    Data_Space_Used varchar(50),
    Index_Space_Used varchar(50),
    Unused_Space varchar(50)
 );

DROP TABLE ' + QUOTENAME(@TempTable) + N';'

EXEC(@SQL)

You cannot parameterise object names so would need to use dynamic SQL for this See Dealing with Dynamic Table and Column Names for more.


My connection to a Mysql DB has been blocked after spamming queries, for how long?

SUGGESTION
If you have the RELOAD privilege, you can run this
mysql> FLUSH HOSTS;

According to MySQL Documentation on running FLUSH HOSTS:

Empties the host cache. You should flush the host cache if some of your hosts change IP address or if the error message Host 'host_name' is blocked occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of max_connect_errors is 10. To avoid this error message, start the server with max_connect_errors set to a large value.

You should run this command
SELECT host,Reload_priv FROM mysql.user
WHERE user = 'mysqluser';

If nothing appears, then you do not have the RELOAD privilege. Consequently, you have no choice but to contact the system administrators to run FLUSH HOSTS; for you.
ALTERNATIVE SUGGESTION
Ask the system administrators to configure max_connect_errors in my.cnf
[mysqld]
max_connect_errors = 18446744073709551615;

To avoid restarting mysql, ask the system administrators to run this
mysql> set global max_connect_errors = 18446744073709551615;

Give it a Try !!!


Optimize a query with two range conditions

Well, let's think out of the box.  It seems that there are two sets of results -- the authors with some such books, and those without.  The first case is more efficiently done via:

SELECT  author_id,
        COUNT(*)+1 AS books_under_fifty
    FROM  `book`
    WHERE  `org` = 1
      AND  `country` = 'USA'
      AND  publish_date >= '2010-05-01'
      AND  publish_date <  '2010-05-01' + INTERVAL 1 YEAR
      AND  `price` < 50

That would probably be best served by

INDEX(country, org, publish_date, price, author_id)

If you are happy with the performance of that, then you can think out of the box in finding "the rest of the authors".

Back to the 2-ranges problem.  That can sometimes be solved by Partitioning.  Not knowing the distribution of the data (is one year a small subset of the total? or what about price?), I can't say which would be better:

PARTITION BY RANGE(TO_DAYS(publish_date))

Partitioning by price is out, since DECIMAL can't be used.  Storing price as number of cents and using PARTITION BY RANGE(cents) would work, but clumsily.

Then make 20-50 partitions of the partition key.  This would give you "partition pruning" for one 'range', but then the index needs to be tweaked for the other.  Assuming you partition by publish_date:

INDEX(country, org, price)
PRIMARY KEY(id, publish_date)

Meanwhile, ...

Don't use utf8 if you are using standard country_codes; use ascii, at least for that column.

What is org does it distinguish the book?  Or are there some missing columns?  Is there no other "unique" column(s)?

Another variant that might be better:

 PRIMARY KEY(country, org, price,  -- for clustering
             publish_date,   -- because partitioning requires it
             id)           -- to assure uniqueness
 INDEX(id)

This variant assumes you have no 'natural' PK.  And it will be more efficient because it clusters the desired rows together.  I did not include author_id in the PK since it is readily available in the row.

add index

ADD INDEX ac(author_id, country) tricked it into using i0.  Go figure.  Note:  I left (author_id) intact and first in the list of indexes; so it is not just the order of the indexes.

I'm using Percona's 5.6.22-71.0-log .

Note that there are only 4 different country values, evenly distributed.  But there are many different author_id, making it by itself more "selective".


Verify SSAS Backup returning arguments instead of expected output

That's what happens when you pass in a backup file that either doesn't exist or on which you don't have access.

For example if I run the command on an existing backup file I get this:

msmdsrv -j -b "c:\temp\AdventureWorksDW2012Multidimensional-EE.abf"

Outputs the arguments as well as the test results:

  -j -b c:\temp\AdventureWorksDW2012Multidimensional-EE.abf

  Backup file \?\c:\temp\AdventureWorksDW2012Multidimensional-EE.abf
  contains database with the Name of
  AdventureWorksDW2012Multidimensional-EE and Id of
  AdventureWorksDW2012Multidimensional-EE consisting of 7927 files with the
  total size of 5554858464 bytes
  All files were successfully tested

You can see the output starts with the arguments and then shows the test results.

If you point msmdsrv.exe to a non existing backup file it just returns the arguments and no results (unfortunately no error message either)

msmdsrv -j -b "c:\temp\NOTEXISTING.abf"

simply returns

  -j -b c:\temp\NOTEXISTING.abf


Calculating count on same table based on condition

Below is an example using RIGHT (starting character) and EXISTS (so that matching names are counted only once).

DECLARE @Ref nvarchar(100) = 'CLT00'
    + CAST(
        (SELECT COUNT(*)
        FROM Client c1
        WHERE EXISTS(
            SELECT 1
            FROM Client c2
            WHERE
                LEFT(c1.Client_LName,1) = LEFT(c2.Client_LName,1)
            )
        ) AS varchar(10));


I believe you are correct and the docs need updating.  Should probably read:

  When a symmetric key is encrypted with a password instead of a
  certificate (or another key), the TRIPLE DES encryption algorithm is
  used to encrypt the password. Because of this, keys that are created
  with a strong encryption algorithm, such as AES, are themselves
  secured by a weaker algorithm.

docs.microsoft.com is all on GitHub, so you can propose doc changes.

I've submitted a pull request to update the language.  You can follow it here: https://github.com/MicrosoftDocs/sql-docs/pull/310


This is a resource issue.

The DB server cannot satisfy your queries due to the client host being configured incorrectly as-per the pre-requisites for an Oracle database client installation.

Ask your system administrators to verify that they have set the required number of open files, semaphores, shmax etc etc. Link to the Oracle documentation - I assume Linux, but Solaris docs are a link away from that.


Find all tables that are related to a view recursively sql server

I rewrote using sys tables as follows.

DECLARE @ViewName VARCHAR(MAX);
SET @ViewName = 'YOUR_VIEW_NAME';
WITH deps ( parent, child ) AS ( SELECT v.name ,
            sed.referenced_entity_name
     FROM   sys.sql_expression_dependencies sed
            INNER JOIN sys.views AS v ON sed.referencing_id = v.object_id
     WHERE  v.name = ISNULL(@ViewName, v.name)
     UNION ALL
     SELECT v.name ,
            sed.referenced_entity_name
     FROM   sys.sql_expression_dependencies sed
            INNER JOIN sys.views AS v ON sed.referencing_id = v.object_id
            INNER JOIN deps ON deps.child = v.name ) SELECT   parent ,
         child FROM     deps ORDER BY parent ,
         child;


How to get number of active users with one SQL-Query?

I don't know how to translate your tables into any of n_active_users; n_users_created_classified; n_user_sent_msg; n_user_read_msg.  But here are some clues:

A simple query for the immediately preceding 30-ish days:

SELECT COUNT(DISTINCT user_id)
    FROM tbl
    WHERE timestamp > NOW() - INTERVAL 1 MONTH;

(Or maybe you would want - INTERVAL 30 DAY.)

This might work for n_user_read_msg:

SELECT COUNT(DISTINCT recipient_user_id) AS n_user_read_msg
    FROM messages
    WHERE timestamp > NOW() - INTERVAL 1 MONTH;
      AND status = 4;

If you want the last thing for each month, then do:

SELECT LEFT(timestamp, 7) AS yyyy_mm,
       COUNT(DISTINCT recipient_user_id) AS n_user_read_msg
    FROM messages
    WHERE status = 4
    GROUP BY LEFT(timestamp, 7);


What are options for Bi-Directional Replication of SQL Azure Database

There is currently no option within SQL Azure for bi-directional (merge) replication. You could alternatively look at Azure Sql Data Sync or SQL Server on an Azure VM.


Exporting partial table records as INSERT script

Here is one way to do what you want (taken from this Stack Overflow answer by KT.:

  Do a select [whatever you need]INTO temp.table_namefrom [... etc ...].

  Right-click on the database in the Object Explorer => Tasks => Generate Scripts
  Select temp.table_name in the "Choose Objects" screen, click Next.
  In the "Specify how scripts should be saved" screen:
  Click Advanced, find the "Types of data to Script" property, select "Data only", close the advanced properties.
  Select "Save to new query window" (unless you have thousands of records).
  Click Next, wait for the job to complete, observe the resulting INSERT statements appear in a new query window.
  Use Find & Replace to change all [temp.table_name] to [your_table_name].
  drop table [temp.table_name].

NOTE:  The above sample uses the schema name temp. You may have to create that schema name for the script to work for you.


$in can abort and return success as soon as the first match is found, while $all has to go through all the data.

The runtime is dominated by the time needed to check for matches, not by the time needed to return data, because any data that does get returned has just been checked for a match and is already in the cache.


Use left joins instead of inner joins so that records from users will still display even if r1, r2, and r3 are null:

SELECT sub.id AS first,sub2.id AS second,sub3.id AS third

FROM users AS usr

LEFT JOIN users AS sub ON sub.r1 = usr.id
LEFT JOIN users AS sub2 ON sub2.r2 = usr.id
LEFT JOIN users AS sub3 ON sub3.r3 = usr.id

WHERE usr.id = :CurrentUserId ;

However, based on your example I think you may need the following query instead, changing the join conditions so that sub, sub2, and sub3 represent referred users:

SELECT sub.id AS first,sub2.id AS second,sub3.id AS third

FROM users AS usr

LEFT JOIN users AS sub ON sub.id = usr.r1
LEFT JOIN users AS sub2 ON sub2.id = usr.r2
LEFT JOIN users AS sub3 ON sub3.id = usr.r3

WHERE usr.id = :CurrentUserId ;


How to access mongodb replica set after restart

Check to see what processes you have running:

ps aux | grep mongod

You should see something like that:

mongod --port 27017 --replSet replica --dbpath s1 --logpath s1/monogd.log --fork
mongod --port 27018 --replSet replica --dbpath s2 --logpath s2/monogd.log --fork
mongod --port 27019 --replSet replica --dbpath s3 --logpath s3/monogd.log --fork

You will be able to connect using:

mongo "mongodb://localhost:27017, localhost:27018, localhost:27019/?replicaSet=replica"


There are .deb packages available. Using the ones from http://people.debian.org/~nobse/mysql-5.5/:

sudo su -
wget http://people.debian.org/~nobse/mysql-5.5/mysql-common_5.5.13-2_all.deb
wget http://people.debian.org/~nobse/mysql-5.5/mysql-server_5.5_5.5.13-2_amd64.deb

dpkg -i mysql-common_5.5.13-2_all.deb
dpkg -i mysql-server_5.5_5.5.13-2_amd64.deb

The first dpkg execution may ask for you to install dependencies. If you're installing on a 32-bit system, alter the URLs accordingly.

Latest .debs are available on http://dev.mysql.com/downloads/mysql/#downloads - Select "Debian Linux".


Tempdb approaching disk max: Is it OK to wait?

This isn't a big deal -- in fact, it's the way a lot of people handle tempdb in the first place:

Add multiple data files
Pre-size files equally to nearly fill tempdb drive
Set autogrowth to a small increment

If the space you have in there is empty and caused by a rogue report, I wouldn't worry about it, period.

(This does assume you've set up tempdb on a drive by itself, as is generally recommended. If you have other things on the drive that may require the space, you have to decide if there's a risk, and how to handle it).


Why does SQL Server Profiler show some completed statements in pairs (twice)?

My question: Is SQL Server actually running the queries twice?

Yep!  Given that the SPIDs are different, that means they were run from completely different connections.  The fact that the duration and reads are different, as you mentioned, is also a good indicator that the query was run twice (possibly with different parameters).

If yes, does this mean definitely the client is sending the query twice?

I would say yes.  You should look into your application code.  Maybe more than one user was accessing the system at the same time, or the query is being run from multiple threads, or something else.
Since this is EF, I might think this was the N+1 query problem (disclosure: that's my blog), but that would normally be all on the same SPID.


MySQL - How to group data by Day and Hour

I assume you have one column of interest (dt, a DATETIME) and you COUNTing the number of rows to get the values for that table?

First, do this to see if you get the right values (but not the desired layout):

 SELECT HOUR(dt), WEEKDAY(dt), COUNT(*)
      FROM tbl
      GROUP BY HOUR(dt), WEEKDAY(dt)
      ORDER BY HOUR(dt), WEEKDAY(dt);

Second, you need to "pivot" the table.  However, you have particular columns.

See http://mysql.rjweb.org/doc.php/pivot for how to generate the code for pivoting.

There are still a few formatting issues...

For the headings, you need to turn "0" into "Mon", etc.  Suggest you hard-code the titles.

"23" can be turned into "23:00 - 23:59" with a CONCAT() and some more code.

NULL can be turned into 0 via IFNULL().

See how far you can get.  (Since this question is 3 weeks old, I don't want to put more effort into it if you have already gone away.)

Personally, I would do (and in fact have done) similar stuff using PHP instead of SQL.  PHP code is longer, but less contorted.


How to model multiple types of contacts?

It depends on the data associated with each entity, the semantics of the data and the queries that will be used for the data.

For example, if “sender contact” and “recipient contact” can be the same and have the same attributes, it would be best to keep them in a single table. If they are strictly different, keeping them in different tables might be better.

Similar with contact types: I would have a common table that holds contacts for all types so that this table can be the target of a foreign key constraint. If just a few attributes differ (e.g. “phone number” versus “e-mail address”) just have them all in the same table and add a check constraint that makes sure they are NOT NULL according to the type. If a lot of attributes differ, you can consider adding foreigb key references to type specific tables.

Try to compose the SQL statements that will be used with these tables most often. If they look natural and simple enough and can be supported with few additional indexes, you probably got it right.


How to enable a SQL Server 2012 login to create, alter and drop the created databases?

First you have to

Create Login with create database permission. Note that the login cannot drop any databases.

USE [master]
GO

CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
-- Grant create any database, but not drop or alter any database
 GRANT CREATE ANY DATABASE TO [Kin]
GO

Map the login to the database that you want to have permission to alter or drop.

-- for drop database, you just need db_owner
USE [test_drop]
GO
CREATE USER [Kin] FOR LOGIN [Kin]
 GO

Add the user to the db_owner role.

  EXEC sp_addrolemember 'db_owner', 'Kin'

Now test it  for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop

EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))

EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin]
set online

Below is the error :

  Msg 5011, Level 14, State 9, Line 2
  User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.


Changing Collation with a High Availability server

I have to remove the DB from High Availability Group(Always on), change the DB to single user, change the collation then change it back to multiuser,back to the high availability group. The good thing is this database is new and it only have 5 tables otherwise it will be more complicated. I was just hoping I don't have to remove it from the group.


ulimit 'open files' and MySQL max_connection

max_connections and ulimit and SSDs have virtually no relationship to each other.

ulimit is an upper bound (unnecessarily low at 1024) on the number of files that can be open in any 'process'.  (mysqld is a 'process'.)  It does relate to table_open_cache, which is now set dynamically based on ulimit.  (In older versions, it was not automatically set.)  However, a single table may need to open more than one file, so that 1024 is not really a limit on the number of "tables" in your databases.

See also Open_tables (bounded by table_open_cache) and Opened_tables/Uptime (frequency of opening an uncached table).

The current default for max_connections is 151, which is 'reasonable' for the 'typical' installation.  Almost no one "needs" 3000.

SSDs, as already mentioned, is new technology that is like "disks", but faster.  A few obscure things should be tuned for SSDs, but only if you have a very busy system.


SQLServer - Detect if an insert trigger was fired by an Application insert or a Replication insert

One way would be to inspect the APP_NAME() property inside the trigger.

I setup a simple merge publication and subscription, then created a "log" table with a trigger on the subscriber table to log the details.

IF OBJECT_ID(N'dbo.MergeLog', N'U') IS NOT NULL
DROP TABLE dbo.MergeLog;
CREATE TABLE dbo.MergeLog
(
    id int NOT NULL
        CONSTRAINT PK_MergeLog
        PRIMARY KEY CLUSTERED
    , AppName sysname NOT NULL
);
GO
IF OBJECT_ID(N'TestMergeTrigger', N'TR') IS NOT NULL
DROP TRIGGER TestMergeTrigger
GO
CREATE TRIGGER TestMergeTrigger
ON dbo.MergeReplSource
AFTER INSERT, UPDATE
AS
BEGIN
    INSERT INTO dbo.MergeLog (AppName, id)
    SELECT APP_NAME(), i.id
    FROM inserted i;
END
GO

After the merge ran, I saw the following in the dbo.MergeLog table:

╔════╦══════════════════════════════════════════════════════╗
║ id ║ AppName                                              ║
╠════╬══════════════════════════════════════════════════════╣
║ 1  ║ SourceServer-Test-MergeReplTestPublicat-DestServer-1 ║
╚════╩══════════════════════════════════════════════════════╝

The "AppName" column will reflect the names of the servers involved along with the merge replication publication name.  Inspecting the APP_NAME() should allow you to definitively identify inserts against the subscriber from merge replication vs inserts from other apps.

One should be aware that the app name is simply a connection-string parameter set by the application itself, and as such can be "faked".  Having said that, in order to provide the correct fake value, an attacker would need to know the name of the publication name, along with both the source and destination server names.  The scenario is not very likely, but something to be aware of.


Why 't' and 'f' instead of TRUE and FALSE

PostgreSQL does not return these instead of boolean values.  It is some clients (for example, psql and pgAdminIII) which represents TRUE with t and FALSE with f - try the same query in another client and you will see something else.  See, for example, what DBVisualizer gives you:

I guess the reason for showing t and f is simply sparing space in a command-line client that lacks horizontal scrolling abilities.

Note: I am in no way affiliated to DbVis Software.


SQL Server: how to monitor activity in several databases

To track the inserts and updates, you can use the below query which will collect the SUM of inserts, updates and deletes against the HEAP or CLUSTERED INDEX for each user table in the database.

Log this to a table and then query the deltas over time to see the inserts\updates\deletes per day. NOTE: This would have to be run against each database you wish to monitor as these DMVs are database-scoped.

USE [Database]
GO
SELECT
    DB_NAME() AS [DATABASE NAME],
    SUM(A.LEAF_INSERT_COUNT) AS INSERTS,
    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES,
    SUM(A.LEAF_DELETE_COUNT) AS DELETES
FROM
    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A
    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
    AND I.INDEX_ID IN (0,1)

The user access is a little harder. If your users access the databases through an application, and that application uses a single login for SQL Server access, then you can't just track logins to determine user access. You would need to track this via your application.

If you're using individual logins, then you could use a login trigger to add a record to a tracking table to record the user, login time and initial database. See the documentation for more info. A good example can be found here. This is actually for logging sysadmin logins, but could easily be modified for your purpose.


Assigning permissions for sysmail_update_account_sp

This request is very similar to, if not the same as, another question on here (DBA.StackExchange) regarding the need for very granular elevated permissions. I provided working code in my answer, and that could be used here as well:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

The overall concept is the same: give the Stored Procedure itself the required permission by being associated (via the Certificate) with a Login that has the proper permission. The only difference would be that instead of granting VIEW SERVER STATE TO [MrDoStuff], you would probably just add [MrDoStuff] to the sysadmin fixed server-level role.

One difference, though, between this question and the "What minimum permissions..." question is that the other question simply needed access to a DMV and not to any code objects outside of the code being signed. It just so happens that signatures do not, by default, transfer to code called by the signed code. But there is a way of allowing this to happen: counter-sign the external code being called. Counter-signing allows code executed by signed-code to transfer the signature without actually being signed itself (and so the external code cannot be called independently). Counter signing is handled by the same ADD SIGNATURE function that was used to sign the initial Stored Procedure:

Restore the Certificate into the [msdb] database
Counter-sign the dbo.sysmail_update_account_sp Stored Procedure

Continuing the example code from that other answer, the additional steps should be:

USE [msdb];

CREATE CERTIFICATE [DoStuffCert]
    FROM FILE = 'C:\temp\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY (
        FILE = 'C:\temp\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'DontStartNoneWontBeNone',
        ENCRYPTION BY PASSWORD = 'W0rdUp,Yo!'
    );

ADD COUNTER SIGNATURE TO dbo.sysmail_update_account_sp
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

It is possible that if sysmail_update_account_sp executes any stored procedures, then they might also need to be counter-signed.


How to filter multiple many to many relationship based on multiple tags?

I had just asked that on a different forum.  The best solution for a 3rd Normal Form schema design was to select all that is needed in the linking table and compare the Cardinality (count) of the matching results.

The other forum was for Oracle.  The answer you seek is the PostgreSQL version of this post: https://community.oracle.com/message/15613817#15613817

-- copy paste from original post
-- Oracle version
select *
from my_recipes rp
where rp.recipe_id  in
(
    select tg.recipe_id
    from my_recipe_tags tg
    where tg.tag_id in    (
                            -- expands string '1:2:3' into a table w/ 3 row
                            select to_number(regexp_substr(:p_search_tags,'[^:]+', 1, level) )
                            from  dual
                            connect by regexp_substr(:p_search_tags, '[^:]+', 1, level) is not null
                        )
    group by tg.recipe_id
                      -- counts cardinality of Tags in search
    having count(*) = regexp_count(:p_search_tags, '[^:]+')
) ;

Untested (needs to be fixed)

select *
from PROBLEMS p
where p.id in (
    select pt.problem_id
    from PROBLEM_TAGS pt
        join TAGS t on pt.tag_id = t.tag_id
    where t.name in ('math','binary search', 'brute force') -- should be from a CTE
    group by pt.problem_id
    having count(*) = 3 -- count(*) from CTE
)

For the 2nd version, add AND p.id NOT IN clause.

For the 3rd version add a filter that ensures pt.problem_id has the exact cardinality of the queried tags.

and p.id in (
    select pt.problem_id
    from PROBLEM_TAGS pt
    group by pt.problem_id
    having count(*) = 2 -- you have two tags for 3rd one
                        -- you should pull Cardinality from CTE also
)


Is attaching a database on the same server under a different database name possible?

You can first check to make sure it actually is a valid MDF file using DBCC CHECKPRIMARYFILE. It is an undocumented command but can be useful at times.

If that turns up nothing or shows that it is not a mdf file you can use the RESTORE HEADERONLY to see if it might be a backup file.

To your specific question, yes you can attach this as a different database name as long as the physical file is not in the same directory on the server as the original. So in your case I would just create a sub-directory in your data folder for this file and the future LDF file that would be created.


Unable to start mongod daemon and it looks its crashing

Wernfried Domscheit said:

You don't need to copy any data in a replicat set. Just remove entire directory and start the service. The replicat set will trigger an inital sync and copies all data. In case you like to take a copy from dbPath you should stop the service or at least lock it with db.fsyncLock() (and of course unlock afterwards with db.fsyncUnlock())

db.fsyncLock() & db.fsyncUnlock() did work and I fixed the chronic replication.


Store logging in separate or existing database?

I would go for the same database, for the reasons you state in your question. If you're worried about performance or growth, create a new "logging" filegroup and put the logging table(s) on that. If things grow out of hand in the future, you can move the .ndf (the physical file that contains the new filegroup) to a different physical drive.

Even if you stick to the same server and add a new database, the effect is similar. You don't have to create a new connection in your client application - all you have to do is reference the table by a three-part name, [database].[schema].[table].

Edit: also, consider if the logged information should "follow" the rest of the database, or if it's specific to a certain server or environment.


MongoDB: how to get the all the embedded data?

If you had multiple documents containing products, you could retrieve all as follows by capturing the query cursor and iterating over the set:

replset:PRIMARY> var cur = db.Category.find( {}, {products:1} );
replset:PRIMARY> while (cur.hasNext()) {
... var doc = cur.next();
... printjson(doc);
... }
{
    "_id" : ObjectId("542450a928db034349fb3bf2"),
    "products" : [
        {
            "_id" : ObjectId("542450a928db034349fb3bf3"),
            "name" : "Producto de categoria",
            "price" : 11.11
        }
    ]
}

If you have multiple products within the "products" array then you would iterate over the array contained in each result document.


Duplicate non-clustered index on Primary Key, can I drop one of them?

I think it's safe to remove the non-primary key as it's an exact duplicate of the primary key.

If one of them is used more and takes less space, did you check the fragmentation and last statistics date on both? Maybe one of them is more fragmented or has better stats.


Can this SQL be made more efficient and combined into one select?

As a first pass, I'd see whether you can eliminate the temporary tables. I like CTE's but they don't always provide better performance.

;WITH tmp_Attachments_Sent_To_smc AS
(
    -- Find all the emails to smc+
    -- that had attachments
    -- and something else
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [tally sent to smc w/ attachment] = COUNT(*)
    ,   [total MB size] = SUM(CAST(AD.Size AS decimal(10, 2))) / 1024 / 1024
    FROM
        AttachmentDetail AD
        INNER JOIN
            MessageAttachment MA
            ON AD.AttachmentId = MA.AttachmentId
        INNER JOIN
            MessageRecipient MR
            ON MA.MessageId = MR.MessageId
        INNER JOIN
            [Message] M
            ON MR.MessageId = M.id
    WHERE
        AD.isinline <> 1
        AND MR.RecipientTypeId = 2
        AND LEFT(mr.EmailAddress, 4) = 'smc+'
    GROUP BY
        CONVERT(date, M.crdate)
)
, tmp_Attachments_Sent_from_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [tally sent from smc w/ attachment] = COUNT(*)
    ,   [total MB size] = SUM(CAST(AD.Size AS decimal(10, 2))) / 1024 / 1024
    FROM
        AttachmentDetail AD
        INNER JOIN
            MessageAttachment MA
            ON AD.AttachmentId = MA.AttachmentId
        INNER JOIN
            MessageRecipient MR
            ON MA.MessageId = MR.MessageId
        INNER JOIN
            [Message] M
            ON MR.MessageId = M.id
    WHERE
        AD.isinline <> 1
        AND MR.RecipientTypeId = 1
        AND LEFT(mr.EmailAddress, 4) = 'smc+'
    GROUP BY
        CONVERT(date, M.crdate)
)
, tmp_Sent_to_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [grand total sent to smc] = COUNT(*)
    ,   [total MB size sent to smc] = SUM(CAST(Size AS decimal)) / 1024 / 1024
    FROM
        [Message] M
    WHERE
        MessageSourceId = 2
    GROUP BY
        CONVERT(date, crdate)
)
, tmp_Sent_from_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [grand total sent from smc] = COUNT(*)
    ,   [total MB size sent from smc] = SUM(CAST(Size AS decimal)) / 1024 / 1024
    FROM
        [Message] M
    WHERE
        MessageSourceId = 1
    GROUP BY
        CONVERT(date, crdate)
)
SELECT
    A.*
,   B.[tally sent from smc w/ attachment]
,   B.[total MB size]
,   C.[grand total sent from smc]
,   C.[total MB size sent from smc]
,   D.[grand total sent to smc]
,   D.[total MB size sent to smc]
FROM
    tmp_Attachments_Sent_To_smc A
    INNER JOIN
        tmp_Attachments_Sent_from_smc B
        ON A.date = b.date
    INNER JOIN
        tmp_Sent_from_smc C
        ON A.date = C.date
    INNER JOIN
        tmp_Sent_to_smc D
        ON A.date = D.date;

Otherwise, what is probably hurting your performance are the conversions in your WHEREs and GROUP BY (CONVERT(date, M.crdate) CAST(AD.Size AS decimal(10, 2). Those are likely resulting in table scans which hurts you. You could also look at persisted computed columns if you can't modify the existing data types as a means to store those computed items.

I'm staring at the LEFT(mr.EmailAddress, 4) and I think that'll also force a table scan but I think it can be fixed with a mr.EmailAddress LIKE 'smc+%' That should at least be SARGAble.

At that point, you should be able to look at your query plans and determine whether an index could help.

Also, prefix your tables with the appropriate schema. I presume it's dbo but as I understand it, that will help with plan cache reuse.


Finding Count of Occurrences & Time Gap between a Timeline in MySQL

Using Sql Server,

declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO',  '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001',  'NO' ,'2017-06-19 02:03:26.0')

;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)

, cte
AS (
    SELECT
        rn
        ,col1
        ,col2
        ,col3
        ,0 changecount
    FROM cte1
    where rn=1

    UNION ALL

    SELECT
        t.rn
        ,t.col1
        ,t.col2
        ,t.col3
        ,CASE
            WHEN t.col2 <> t1.col2
                THEN changecount + 1
            ELSE changecount
            END
    FROM cte1 t
    INNER JOIN cte t1 ON t.col1 = t1.col1
        AND t.rn = t1.rn + 1
    )
SELECT *
FROM cte


Delete data from multiple related tables

You can use Jailer tool, it will find all rows of child tables  that reference to master table.

Example: I have 4 tables : employee, employee_detail, phone_address, relationship. I want to delete employee which name="JOHN". With Jailer, it will find rows in employee_detail & relationship which FK to "JOHN" (by id). And because of phone_address reference to employee_detail, so Jailer will find rows in phone_address.

tb_employee ----(1,n)----tb_employee_detail----(1,n)----tb_phone_address
            ----(1,n)----tb_relationship


Is there a way to suppress DBCC TRACEON/OFF messages in the Error Log?

trace flag 2505 can be used. Wrote a blog post with experiment http://sqlserver-help.com/2014/12/09/help-how-to-suppress-dbcc-traceonoff-messages-in-the-error-log