As Brent Ozar noted in his answer, you are attempting to use a Logon trigger for a purpose for which it was not designed.  Logon triggers are typically used to either capture details about who is logging onto the server, or to deny particular combinations of users/machines etc from connecting.

Luckily, Extended Events offers a mechanism for capturing T-SQL in memory that, when configured correctly, can be fairly light on system resources.

I've used something like the following code in the past to capture T-SQL from a particular user or machine:

IF EXISTS
(
    SELECT 1
    FROM sys.server_event_sessions dxs
    WHERE dxs.name = 'queries'
)
BEGIN
    IF EXISTS (
        SELECT 1
        FROM sys.dm_xe_sessions dxs
        WHERE dxs.name = 'queries'
    )
    BEGIN
        ALTER EVENT SESSION queries
        ON SERVER
        STATE = STOP;
    END
    DROP EVENT SESSION queries
    ON SERVER;
END

CREATE EVENT SESSION queries ON SERVER
ADD EVENT sqlserver.sql_statement_starting
(
    ACTION
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6 /* track a specific database only */
        AND sqlserver.client_hostname <> 'excluded_host_name'
)
, ADD EVENT sqlserver.sql_statement_completed
(
    ACTION
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6
        AND sqlserver.client_hostname <> 'excluded_host_name'
)
, ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6
        AND sqlserver.client_hostname <> 'excluded_host_name'
        /* fluff errors below - for SQL Server 2008 R2, use "error" instead of "error_number" */
        AND error_number <> 5703 /* Changed language setting to %.*ls. */
        AND error_number <> 5701 /* Changed database context to '%.*ls'. */
        AND error_number <> 2528 /* DBCC execution completed. If DBCC printed error messages, contact
                            your system administrator. */
        AND error_number <> 7969 /* No active open transactions. */
        AND error_number <> 4035 /* Processed %I64d pages for database '%ls', file '%ls' on file %d. */
        AND error_number <> 18265/* Log was backed up. Database: %s, creation date(time): %s(%s),
                first LSN: %s, last LSN: %s, number of dump devices: %d, device information: (%s).
                This is an informational message only. No user action is required. */
        AND error_number <> 3014 /* %hs successfully processed %I64d pages in %d.%03d seconds (%d.%03d MB/sec). */
        AND error_number <> 14570/* (Job outcome) */
        AND error_number <> 8153 /* Warning: Null value is eliminated by an aggregate or other SET operation. */
)
ADD TARGET package0.ring_buffer
(
    SET max_memory = 1024
)
/* add or remove the below target as required. */
, ADD TARGET package0.asynchronous_file_target
(
    SET filename = 'C:\temp\queries_xe_target.xel'
        , max_file_size = 10        /* max size in MB */
        , max_rollover_files = 10
        , increment = 1             /* file growth increment in MB */
)
/* Don't start this Extended Events session automatically when the server starts */
WITH
(
    STARTUP_STATE = OFF                                 /* Extended Event Session will NOT be automatically
                                                            started at server startup */
    , TRACK_CAUSALITY = ON
    , MAX_MEMORY = 5MB                                  /* buffer size to use */
    , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS    /* ALLOW_MULTIPLE_EVENT_LOSS or NO_EVENT_LOSS */
    , MAX_DISPATCH_LATENCY = 15 SECONDS                 /* maximum number of seconds until buffer contents
                                                            are written to the target */
    , MEMORY_PARTITION_MODE = PER_NODE                  /* NONE, PER_NODE, PER_CPU */
);
GO

Use this to start the session as required:

IF EXISTS
(
    SELECT 1
    FROM sys.server_event_sessions dxs
    WHERE dxs.name = 'queries'
)
BEGIN
    ALTER EVENT SESSION queries ON SERVER STATE = START;
END

This shows the session target details:

SELECT SessionName = xe.name
    , TargetName = xet.target_name
    , EventData = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries';

Use this to see the results from the ring buffer:

DECLARE @xml XML;

SELECT TOP(1) @xml = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries'
    AND xet.target_name = 'ring_buffer';

IF OBJECT_ID('tempdb..#xmlResults') IS NOT NULL
DROP TABLE #xmlResults;

CREATE TABLE #xmlResults
(
    RowNum INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , xeXML XML NOT NULL
);

INSERT INTO #xmlResults (xeXML)
SELECT xm.s.query('.')
FROM @xml.nodes('/RingBufferTarget/event') AS xm(s)
OPTION (Optimize FOR (@xml = Null)); -- immensely improves performance in SQL Server 2008

SELECT t.EventName
    , DateStamp = DATEADD(HOUR, -6, t.EventDateStamp)
    , DatabaseName = d.name
    , t.ErrorNumber
    , t.ErrorSeverity
    , t.ErrorState
    , t.ErrorMessage
    , t.CollectSystemTime
    , t.ClientAppName
    , t.ClientHostName
    , t.PlanHandle
    , t.SqlText
    , t.UserName
FROM (
        SELECT EventName =          xeXML.value('(event/@name)[1]','varchar(500)')
            , EventDateStamp =      xeXML.value('(event/@timestamp)[1]','datetime')
            , DatabaseID =          xeXML.value('(event/data[(@name)[1] eq "source_database_id"]/value/text())[1]','varchar(255)')
            , ErrorNumber =         xeXML.value('(event/data[(@name)[1] eq "error"]/value/text())[1]','varchar(255)')
            , ErrorSeverity =       xeXML.value('(event/data[(@name)[1] eq "severity"]/value/text())[1]','varchar(255)')
            , ErrorState =          xeXML.value('(event/data[(@name)[1] eq "state"]/value/text())[1]','varchar(255)')
            , ErrorMessage =        xeXML.value('(event/data[(@name)[1] eq "message"]/value/text())[1]','varchar(255)')
            , CollectSystemTime =   xeXML.value('(event/action[(@name)[1] eq "collect_system_time"]/text/text())[1]','varchar(255)')
            , ClientAppName =       xeXML.value('(event/action[(@name)[1] eq "client_app_name"]/value/text())[1]','varchar(255)')
            , ClientHostName =      xeXML.value('(event/action[(@name)[1] eq "client_hostname"]/value/text())[1]','varchar(255)')
            , PlanHandle =          CONVERT(xml, xeXML.value('(event/action[(@name)[1] eq "plan_handle"]/value/text())[1]','varchar(255)')).value('(plan/@handle)[1]', 'varchar(255)')
            , SqlText =             xeXML.value('(event/action[(@name)[1] eq "sql_text"]/value/text())[1]','nvarchar(max)')
            , UserName =            xeXML.value('(event/action[(@name)[1] eq "username"]/value/text())[1]','varchar(128)')
        FROM #xmlResults xm
    ) t
    LEFT JOIN sys.databases d ON t.DatabaseID = d.database_id
WHERE t.UserName NOT IN (
      'user_1'  COLLATE SQL_Latin1_General_CP1_CI_AS
    , 'user_2'  COLLATE SQL_Latin1_General_CP1_CI_AS
    , 'user_3'  COLLATE SQL_Latin1_General_CP1_CI_AS
    )
ORDER BY t.UserName
    , t.EventDateStamp;

SELECT *
FROM #xmlResults


Why delete with "where in" condition is slow, while looping & using single where is fast?

When building an index, start with the things that are tested with =.  That is, the index should be
 INDEX(node_id, is_successful,   -- in either order
       monitor_id)               -- last

Another tip:  When you have both INDEX(a) and INDEX(a,b), the former is unnecessary since the latter can handle searching just by a.  (But not just b.)  That is, currently your single-column index (monitor_id) can be DROPped.
More on building indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


User resources are store in "mysql.user" table :

SELECT max_questions,
       max_updates,
       max_connections
FROM   mysql.user
WHERE  user = 'myuser'
AND    host = '%';

Max.


How to monitor history of database usage if login auditing is not enabled?

I blogged about it exactly one month ago. Since link-only answers are discouraged and pasting here the whole blog post would be ridiculous, here is a summary of what you will find there.

Basically, you have to record index usage in a user table, in order to make sure that server restarts and index maintenance don't delete entries for the databases you're interested in.

A second method involves auditing index usage by means of streaming an Extended Events session that captures lock acquired events. This method is particularly useful for capturing additional information about who is accessing the tables without having to capture detail information (audits and XE file targets would be too verbose for this task).

I hope you find it useful.


Returning multiple result sets from server-side procedures in Postgres 11

you can return multiple result sets from a procedure - similar to the way it has always been possible with a function:

create procedure getdata(result_one inout refcursor, result_two inout refcursor)
as
$$
begin
  open result_one for
    select *
    from (values (1,2,3), (4,5,6)) as t(a,b,c);

  open result_two for
    select *
    from (values ('one'),('two'),('three'),('four')) as p(name);
end;
$$
language plpgsql;

However, displaying the results is a bit cumbersome - at least in psql:

postgres@localhost/postgres> \set AUTOCOMMIT off

postgres@localhost/postgres> call getdata(null, null);
     result_one     |     result_two
--------------------+--------------------
 <unnamed portal 1> | <unnamed portal 2>
(1 row)

postgres@localhost/postgres> fetch all "<unnamed portal 1>";
 a | b | c
---+---+---
 1 | 2 | 3
 4 | 5 | 6
(2 rows)

postgres@localhost/postgres> fetch all "<unnamed portal 2>";
 name
-------
 one
 two
 three
 four
(4 rows)

postgres@localhost/postgres>

Some SQL clients can display the results automatically though.


Need to maintain transactional consistency of the data across both servers, need to achieve these goals without manual intervention

The tricky part is in this requirement:

  You need to ensure that database remains available if a catastrophic
  server failure or a disk failure occurs. You need to maintain
  transactional consistency of the data across both servers. You need to
  achieve these goals without manual intervention.

The disk failure part means a failover cluster alone won't work because the storage is shared with both nodes. If the storage where the data files live fails, then both nodes will be affected.

However, a 2-node synchronous Availability Group isn't the answer either, because as Microsoft's own documentation points out:

  If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

Read further in that link in the "Factors That Disrupt Data Synchronization" section, and Microsoft elaborates on the reasons why you can't guarantee that a 2-node AG will not lose data on failover.

So what's the right answer for SQL Server 2012?

There isn't one. You can't guarantee zero data loss with 2 independent SQL Server 2012s without third party tools (like SAN replication, and even then, there's a ton of work involved.) I'm guessing the question came from a test or certification written by somebody without real-world experience. That wouldn't be the first time, and it won't be the last.

Is there a right answer for later versions?

Yes, SQL Server 2017 introduced a new REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting at the Availability Group level. The default is 0, which means as long as the primary receives the transaction, it's committed. You can change that to 1 (or more), which means that if at least that number of secondaries don't also commit the transaction, then the transaction fails.


User Permissions Messed Up following a Backup -> Restore Operation

I see you already found a solution to your problem, one thing I noticed in your original question was that you still had access to the old server.

The following question on SO had a similar problem and includes links to a Microsoft article with a script to generate the user permissions.

https://stackoverflow.com/questions/461385/restoring-a-backup-to-a-different-server-user-permissions

(Resource listed for that question http://support.microsoft.com/kb/918992)

It sounds like changing the server setting from Windows authentication to Mixed mode authentication fixed your problem, but just in case it didn't completely resolve the problem I thought this might be useful.


When to use a spare index over a dense index?

In context:

  when the file is not sorted on the indexed field (such as when the index is a secondary index)

"the file" = the database table (or similar object) being indexed
"the indexed field" = the field being considered for the new index. A sparse index relies on the records being sorted in the same order as the indexed field.

Example: data in table is sorted on ID:

  ID    Last Name
   1    Smith
   2    Francis
   3    Jones
   4    Zygoski
   5    Bohr
   6    Josephson
   7    Michaels
   8    Able

And, imagine this is a sparse index on Last Name (with pointers to the appropriate records:

Index
  Able
  Francis
  Jones
  Smith

Now, to find "Bohr", You go to "Able" and then search sequentially. Unfortunately, "Able" is the last record in the file, so, you can't get to "Bohr" from there.

  when the index file is small compared to the size of memory.

Basically, if the entire index can be loaded into memory, and still have plenty of room to load in the data from the table that's needed, then the database engine should be able to find each record in the index, and go directly to that record in the data table (instead of possibly having to pull in several pages from the table, scanning to locate the record it wants). If the dense index takes up too much room in memory, then either you have to read it in in parts, or it's going to squeeze the available memory for the rows you need from the data table.

Or, at least, that's my best guess. Note that I'm speaking form the view of someone who's been working with databases for almost 30 years; when I was in class, I don't remember dense vs sparse indexes being discussed.


SQL 2014 Always On Availability Groups - Read Replica

No, you don't need to rebuild indexes or update statistics. You can't do any writes to a readable replica at all.


How to add sequential numbers to an existing table?

Generate the numbers:

select subno, ar_invdate, row_number() over (partition by subno order by ar_invdate) as num from first_second_bill_v4;

Update the table with these numbers:

merge into first_second_bill_v4 t1 using (select subno, ar_invdate, row_number() over (partition by subno order by ar_invdate) as num from first_second_bill_v4) t2 on (t1.subno = t2.subno and t1.ar_invdate = t2.ar_invdate)
when matched then update set t1.num = t2.num;

Theoretically, not tested.


Upgrading postgres to 10 from 9.5 using pg_upgradecluster

To check for compatibility, etc use the native upgrade command with check option.

pg_upgrade —check
Refer to the the pg_upgrade doc for details. And remember (just found out for Ubuntu today), datadir is actually the location of the dir which has the config files (hba, ident, pg.conf).

If the —link option is used

sudo pg_dropcluster 9.5 main

should be run. IIRC pg_upgradecluster starts the db cluster automatically.

If you follow pg_upgrade route, there is defined point where the old cluster can still be used/ rolled back to.


No, the differential backup is based on the differences since the main.  It doesn't contain a full usable structure.


Always On FCI vs Always On Availability Groups

What is the advantage of Always On Failover Clustering vs Always On Availability Groups?

The main advantages given 2012->2017 for an FCI is:

No instance level metadata or objects required to be synchronized
Databases can be in any recovery model
Only one copy of the databases is required (1x total disk space required)
Generally easier to troubleshoot as it isn't a distributed system

  Seems Availability Groups is better, Looking at the chart comparison I see none

It depends on what you're protecting against and what infrastructure you have in order to protect it. Then it comes down to what people have the most experience supporting.

In Availability Groups, here are some common disadvantages:

Instance level metadata needs to be manually synchronized between participating nodes (Logins, Jobs, certain encryption keys, proxy accounts, etc.)
It's a distributed system and is generally harder to troubleshoot
Requires your networking be of the utmost quality and lowest latency (even in asynchronous mode)
Requires x*n amount of storage where n is the number of replicas in the availability group and x is the sum total of size for the participating databases.


You're oversimplifying the relations.  No two relations in your diagram are the same, since they don't relate the same domains.  <ORDER> has <ORDER-ITEM> is not the same as <PROJECT> has <PROJECT-PHASE>.  They might both be injective, but that's not sufficient to call them the same.

Try to give your relations more descriptive names.  While attributes (aka one-to-one binary relations) tend to be aggregated on a common determinant to create "entity tables", multivalued attributes and more complicated relations like the ternary relation between Customer, Service and Order-item usually become tables on their own.  Makes really doesn't describe the relation or resulting table properly.

I suggest you add cardinality indicators to your diagram and give your relations more descriptive names, using the predicate statement of each relation for guidance.

On second thought, those names are probably fine in a conceptual design as long as you read them together with the entity sets they relate.  When you get to physical design, 1-to-1 and 1-to-many relations will probably be implemented as attributes of one of the entity sets, so the relation names won't affect the physical design.  The many-to-many relations and non-binary relations will become their own tables, for table names at worst you can concatenate the entity names, e.g. CustomerServiceOrderItems.


postgres sequence problems, manually created pids, and proper sequence resetting

The reason they stopped working the first time is the well-known cause that a table was restored for example. However, at this point I think the manual code and sequences are stepping on each other, and the sequence resetting code is not solid. It seems obvious that inserting the last max will undermine the sequence, which simply has its own number it increments.

If you're restoring from pg_dump there is no way that can be the problem. Take for instance,

CREATE TABLE foo (id serial PRIMARY KEY);
INSERT INTO foo DEFAULT VALUES;

This generates a table like this,

                         Table "public.foo"
 Column |  Type   |                    Modifiers
--------+---------+--------------------------------------------------
 id     | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

You can see here that the type of id is just int and all the sequence does is default it to nextval. Using pg_dump, this will generate a dump like this (removing comments and permissions)

CREATE TABLE foo (
    id integer NOT NULL
);

CREATE SEQUENCE foo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE ONLY foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq'::regclass);

COPY foo (id) FROM stdin;
1
\.

SELECT pg_catalog.setval('foo_id_seq', 1, true);

ALTER TABLE ONLY foo
    ADD CONSTRAINT foo_pkey PRIMARY KEY (id);

That a lot of stuff, but it's the safest way to restore even a simple table. So notice here the call to pg_catalog.setval

SELECT pg_catalog.setval('foo_id_seq', 1, true);

After you merge in other pkids that are potentially higher, you'll have to call that with the new highest value.

SELECT pg_catalog.setval(
  'foo_id_seq',
  (SELECT max(id) FROM foo),
  true
);

Then viola, you're back to working. They're not broken at all.

Important note, this is for serial types. Pg 10 will change this interface if you're using Identity Columns and it will instead be a standardized ALTER TABLE command to change the current value.


Entity Framework Core for SQL Server Failover Basic Availability Groups does not work

Ok, so I had to go out of my way and took a risk to setup DNS without AD. It wasn't a big deal since we had only 2 SQL instances and 3 client VMs. Apparently "Failover Partner" keyword only works with the deprecated "mirroring" feature. And the listeners are to be only accessed with their names. I don't think there is a solution to not using DNS without a number of ugly hacks.


My SQL Server Upgrade from 5.6.12 to 5.6.22

That's a very uncommon way to upgrade.  Instead, leave the data folder in place.

backup the data folder.
install the new version
run mysql_upgrade

(No, I can't explain what went wrong with what you did.)


Why MySQL doesn't utilize leftmost Index field (UK - col1, col2, col3) in select query?

It's almost certainly due to the low cardinality of the index on the first column.  Since there are only 3 values, the optimizer decides that it is better do do a full scan on the table.

Using the index on the first column will point to about one third of the rows.  Looking up that many rows based on the primary key will likely lead to practically all of the primary key pages being pulled into the buffer along with a large portion of the secondary index pages, leading to more memory usage and potentially disk traffic.  Overall, it is more efficient and usually about the same speed to just do a full scan on the data if a large portion of the data is likely to be selected based on a secondary index lookup.


Creation of a simple field failed in MySql

by is a reserved word, and so is for

You should surround reserved words with backquotes (`) if you use them as table or column names. Just to be on the safe side, surround all column names with backquotes

CREATE TABLE bdt.transactions (
`transaction no`     int UNSIGNED NOT NULL  AUTO_INCREMENT,
`data`               date    ,
`by`                 int  NOT NULL  ,
`hours`              int  NOT NULL  ,
`for`                int  NOT NULL  ,
`transaction type`   set('normale','corso')   NOT NULL DEFAULT 'normale' ,
`activity`           text  NOT NULL  ,
CONSTRAINT pk_transactions PRIMARY KEY ( `transaction no` ) ) engine=InnoDB

Give it a Try !!!


It's all documented here and here.

Relevant part:

  To make your general or slow query logs available, enable the
  corresponding flag and set the log_output flag to FILE. This makes the
  log output available using the Logs Viewer in the Google Cloud
  Platform Console. Note that Stackdriver logging charges apply.

  If log_output is set to NONE, you will not be able to access the logs.
  If you set log_output to TABLE, the log output is placed in a table in
  your database. If this table becomes large, it can affect instance
  restart time or cause the instance to lose its SLA coverage; for this
  reason, the TABLE option is not recommended. If needed, you can
  truncate your log tables by using the API. For more information, see
  the instances.truncateLog reference page.


If i understand correctly, you want to see all activity from a single user.

The quickest would be a profiler trace filtered to that user, start a trace and in the Event Selection and Column Filters add the user under "Like" in the LoginName filter, or NTUserName if appropriate.

Beware of overhead when using profiler in production it can be resource hungry.

If you want something more long term, you can look into auditing. Here is a useful tutorial for Select Statements but there is much more you can do with it.

Auditing Select Statements 2008


If the collation of your database is set to accent insensitive, then this should work (for most accented characters).

For example in the collation Latin1_General_CI_AS, the CS means Case Insensitive, Accent Sensitive.  You would want the collation to be Latin1_General_CI_AI which means Case Insensitive, Accent Insensitive

Be warned though, that not all "accented" characters are created equally, but for spanish and french searches I found things work out as expected.


Welcome to stack exchange. As a general rule, you should try to limit your questions to a single, well-defined question. Here it reads as if you just want general tuning help for your server, which isn't the best fit for the site. Ideally, questions and answers will be useful to a wide audience and will remain relevant over time. With all of that said, I'm going to answer the two discrete questions that you have about the number of logical reads and the significance of query cost.

It is certainly possible to have 15 billion reads on a server over an eight hour period. Those reads are logical reads which represents operations against memory instead of disk. I just ran a random query on my desktop that did about 2 million logical reads in a minute. Extrapolating that out would give me a total of about 58 billion logical reads over an eight hour period.

The query cost is a unitless measure of the expected amount of work that SQL Server will need to complete to execute a query. In general, the number doesn't mean much without context and you don't have to be concerned with it unless there's a problem. It is used by the engine in various ways if you're interested in that. Here's the list I know off the top of my head:

If the query cost is too low it won't be eligible for a parallel plan. This is controlled by the cost threshold for parallelism option.
The number of seconds that a query will wait for a memory grant before throwing an error. There are some configuration options here which can influence it, but the general formula is something like query cost / 25 (rounded in seconds) with a maximum of 86400 seconds (1 day).
The amount of steps taken by the optimizer during query plan creation. The optimizer may do more work in evaluating possible query plans for queries which are expected to be more expensive.
If the query is sent to a small query resource semaphore.
If the query is not able to run due to the query governor cost limit.

In general, you don't have to worry about those details unless something is going wrong. There are a few common cases where the query cost can be useful though. If certain queries aren't going parallel or if too many queries are going parallel then you may need to adjust CTFP based on your workload. If a query is slow and you're able to find a query hint that makes it faster than the difference in costs between two queries can provide useful clues as to why the optimizer didn't naturally select the query plan that you hinted. If a query is very slow with an absurdly slow cost (for example, < 1 unit) that suggests that the optimizer is working off bad information. For example, a cardinality estimate could be way off. If you're able to correct that bad information you might get a more efficient plan.


copy entire PostgreSQL cluster to another (identical) server

Your option 1, pg_dump | psql does have the limitations you describe.

Your option 2 doesn't make much sense.  You need to start out with a clone in order to set up streaming, and by the time you have the clone then you don't need the streaming, as you already have the clone.  Also, you don't have to "reconfigure both to disable replication again".  When you promote the standby, it is no longer a standby, no more reconfiguration is needed.  If you used a named slot on the master, you just need to drop it.  If you used a temp slot, you don't even need to do that.  You could drop "wal_level" down "minimal" if you want to create extra work for yourself, you could set "max_wal_senders" down to 0 if you want to create extra work for yourself.  So just don't do those things.  Leave the master configured in a way that will allow future streaming replication. (On v10, being ready for streaming is already the default configuration anyway, other than the setting in pg_hba.conf to allow a remote streaming connection)

Your concerns in option 3 also don't make any sense.  Why would rsync'ing the data over be any more susceptible to corruption than anything else you can do with a terabyte of data, like ordinary backups, or even just running a database?  Rsync does not go out of its way to corrupt your data.  If you need more things than just the data directory depend on how configured the system in the first place.  Do you have tablespaces?  Are the config files in the data directory, or someplace else.  If you do those things, then yes, you have to handle them.  That is what you signed up for when you decided to depart from the simplest configuration.

On option 4, the output of "pg_basebackup" does not get piped to "pg_receivewal".  They are not designed to work together in this way. "pg_basebackup" can already create a free standing directory, which is what you want, so you can just use it. I think you want to execute, on the host where you want the new server to be, pg_basebackup -D newdir -F plain -h prod_host.example.com

How are you backing up your database currently?  The best way to make a clone is probably to tap into your current backup strategy.  How would you restore from your backup if you needed to?  Minor alterations on that should get you your clone (as well as testing if your current backup strategy works)


You have to keep in mind two major aspects about using the MEMORY Storage Engine

ASPECT #1

The MEMORY table behaves like a MyISAM table in that it performs a full table lock when doing INSERTs, UPDATEs, and DELETEs (MySQL Documentation says Locking granularity : Table). I would be very concerned with a high lock ratio if it bottlenecks your I/O performance. If 300 INSERTs per second is coming at this table, you can be fully confident that there will be 300 full table locks per second.

ASPECT #2

No matter how much data you load into a MEMORY table, mysqld will always contact the .frm file of the MEMORY table to verify the table's existence before query parsing. Although the .frm is not a large file, it will incur a very tiny amount of disk I/O. Thousands, or even just hundreds, of queries against a MEMORY table can reveal a noticeable, even a significant, amount of disk I/O.

Since this is the case with the MEMORY storage engine, you must also avoid MyISAM because it has the same locking granularity (Full Table Locking).

Here are my recommedations

Recommedation #1

Cleanup InnoDB so that it has innodb_file_per_table enabled.

Recommedation #2

Use a large InnoDB Buffer Pool

Since the table is 200MB, you can make InnoDB Buffer Pool 5 times as large (restart required)

[mysqld]
innodb_buffer_pool_size=1G

Recommedation #3

Upgrade to MySQL 5.5 in order to have multithreaded reads and write that engages multiple CPUs for InnoDB. If you have InnoDB 5.1.38+ and are using the InnoDB Plugin, the same options that engages multiple CPUs for InnoDB already exists.

Recommedation #4

Common sense would tell me to recommend turning the table back to InnoDB


This is the expected case if you have defined a unique index which is non-sparse: documents missing a value for the field with the unique index will have an indexed value of null. This means that only a single document in the collection can be missing the unique field.

The duplicate key message includes the index name and key violation:

  "errmsg" : "E11000 duplicate key error collection: testdb.setup index: name dup key: { : null }",

In your example, the collection setup in database testdb has a unique index on the name field. The attempted upsert failed because the name field was missing and there was already a document in this collection with a missing or null value for name.

If you want to use a unique index without requiring the field to be present you have a few  options:

Drop and recreate the unique index with the sparse property:

db.setup.createIndex(
    {name: 1},
    {unique:true, sparse:true}
)

Drop and recreate the unique index using a partial filter expression (which allows further criteria if needed):

db.setup.createIndex(
   { name: 1},
   { unique:true, partialFilterExpression: {name: {$exists:true }}}
)


Simply backup the database on 2008R2, then restore it on the new server. You can change the compatibly level to the new version. The last thing to do is to add the logins from the old server, you can check out this link: Restore database rights on restored database SQL 2012


How to delete archive log on just one dest?

rman target /
RMAN> delete archivelog like '/path/to/logs/%';

archivelogRecordSpecifier


Connect Jira to Postgresql with Pgpool-II

Well ... check the pgpool logs and your servlet container logs.

I'd also try psql 'host=localhost port=9999 dbname=postgres username=postgres password=whatever'

Most likely the PgPool configuration has issues. If it works from psql, you'd need to check out the server logs to see what's up.


Unstable backup size, where to look for causes

When using backup compression, the size of the backup will vary quite a lot assuming data is being changed in the database.

Performing index maintenance each night will move data around inside the database a lot, resulting in unpredictable changes to the amount of allocated pages inside the database.  Index defragmentation is not perfect and does not guarantee the index is completely defragmented - it is merely a way of reducing fragmentation.

The amount of change you're seeing is nothing to be concerned about, especially when you see your file sizes are measured in kilobytes.


Yes, if you've dropped the linked server then you'll need to setup everything again from scratch.  You could script it out first, all you'll need to do then is change the IP address and password in the script and run it back in again.


Deleting rows from multiples tables at once

If you change your code to this, it'll work:

if(isset($_POST['deletecontr'])){
  $dcquery = $_POST['dni']; // This get the ID of the person who I want to delete.
  $catchempr = ("SELECT `empresa` FROM `contratistas` WHERE dni='$dcquery'"); //This catch the name of the company of that person
  $query = ("DELETE FROM `contratistas` WHERE dni='$dcquery'"); // This works fine
  $query2 = ("DELETE FROM `empleados` WHERE empresa in ($catchempr)"); This does not.
  mysqli_query($link,$query);
  mysqli_query($link,$query2);
  echo "La empresa a sido eliminada de la base de datos";

By doing this, I have changed the query from roughly:

DELETE FROM `empleados` WHERE empresa='SELECT `empresa` FROM `contratistas` WHERE dni='$dcquery'' -- // ignore the $dcquery part

to:

DELETE FROM `empleados` WHERE empresa in (SELECT `empresa` FROM `contratistas` WHERE dni='$dcquery')

By expanding the query strings you can see that the first query is not valid for a couple of reasons.

I'll also add that this code is susceptible to SQL Injection because you are not verifying or filtering the input string $_POST['deletecontr']. To mitigate this, use bind variables/prepared statements.


how do I configure postgresql to find the pg_controldata command

Your shell PATH environment variable is not pointing to your folder /usr/lib/postgresql/9.4/bin/.

In order to do this, use the command PATH="/usr/lib/postgresql/9.4/bin/:$PATH".

If you want to automate this, you can add this line in your shell config file (ie. ~/.bashrc) with this :
echo 'PATH="/usr/lib/postgresql/9.4/bin/:$PATH"' > ~/.bashrc.


As CPU is in milliseconds.From the beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6 of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3 of a second). Even in SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

For your further ref Interpreting CPU Utilization for Performance Analysis and Here


SQl query to create a report based on multiple relational tables

This can be written as a single query.
I have guessed the last line.

SELECT
   MONTH(a.Created) AS `Month`
 , SUM(a.totalValue) as totalValue  -- Total amount in $
 , COUNT(a.id) as totalSales
 , SUM(CASE WHEN a.id_delivery = 1 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalDelivery
 , SUM(CASE WHEN a.id_delivery = 2 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalTaken
 , SUM(CASE WHEN a.id_payment = 1 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalTaken
 , SUM(CASE WHEN a.id_payment = 2 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalCard
 , SUM(CASE WHEN a.id_payment = 3 AND b.status >= 3 AND b.status <= 6 THEN 1 END) as totalMoney
 , SUM(CASE WHEN b.status IN (7, 8) THEN 1 END) as totalCanceled
FROM order a, orderStatus b
WHERE YEAR(a.Created) = ?
 AND b.id = a.id_status
GROUP BY MONTH(a.Created)
ORDER BY 1


AWS has an excellent guide for deploying MongoDB on AWS.

https://d0.awsstatic.com/whitepapers/AWS_NoSQL_MongoDB.pdf

If you are looking for specific instructions on how do deploy MongoDB on AWS be sure to check this out:
https://docs.mongodb.org/ecosystem/platforms/amazon-ec2/

Also bear in mind that AWS micro instances are too small for any production use of MongoDB. If your goal is simply learn MongoDB and AWS the micro instances maybe fine, but for any performance/stress testing the results for a micro  instance would not be realistic.

Good luck!


SQL Server Always On File Share Witness (Quorum vote) on different subnet to other nodes

Could someone confirm is having the file share server on a different subnet a big no no in an AlwaysOn environment?

It's completely fine to have the FSW on a different subnet, there is absolutely nothing wrong with that. There is no need to have it on the same subnet, in fact there is an Azure witness which definitely won't be on the same subnet and it works without issue.

  "A connection timeout has occurred on a previously established connection to availability replica"

Seems to be pointing to either something in the network being an issue or if this is on a virtual machine something is happening to the guest/host which is giving you this trouble. Given there are a plethora of in-depth configuration settings at the host, guest, and OS level that can contribute to this I won't go into any further depth as it'd be out of scope of this site.

  The errors in the failover cluster manager say "File share witness resource 'File Share Witness' failed to arbitrate for the file share" The server the file share sits on hasn't restarted or had any issues and all permissions are working.

This means that whomever attempted to arbitrate for the witness was only one vote off of having quorum for the cluster. Since it's a two node cluster, if the nodes couldn't talk to each other they'd be in this exact situation.

If neither node can talk to each other (obviously an issue) and neither node can talk to the FSW (another issue) this makes me wonder what's broken in the infrastructure - again, either at the virtual layer or the physical (network) layer. It's clear something is going on to cause this and is specific to your environment, not SQL Server.

  The other weird thing is there are 3 votes in the quorum including the file share, so even if the file share loses connectivity to the failover cluster, node1 & node2 shouldn't lose connectivity between each other as there's enough votes for quorum (2)

Yes, however I'm betting the nodes lost connectivity to each other. There are probably some entries about missed heartbeats, connectivity to ~3343, regroups, etc., in the cluster log.

Connectivity doesn't mean votes, connectivity means health checks. Once health checks fail, nodes become partitioned and that's when these events occur. You'll need to find out what occurred in your environment around the time this happened. If it happens quite often and on schedule, then it's some task or software in the environment, if it happens randomly then it's most likely infrastructure issues such as networking or Host/Guest/OS settings if it happens when under load.


How do I create a point with an SRID in MySQL?

MySQL 8.0+

MySQL supports Point(x,y) which is a GIS function that constructs a point. With MySQL 8.0 and newer, you can further assign an SRID to that point with ST_SRID(srid)

SELECT ST_SRID( Point(0,0), 4326);

This is a relatively new feature implemented in MySQL 8.0 with #WL8543. MariaDB does not support it.

In PostGIS, you would use,

SELECT ST_SetSRID( ST_MakePoint(0,0), 4326);

In MariaDB, your only option is,

ST_PointFromText(wkt[, srid [, options]])


Which version of SQL Server 2016 supports R

SQL Server 2016 Community Technology Preview 2.2  is the latest release of SQL Server 2016. This version does not have R support.

Later release of 2016 (probably 3.0) will have support for R language.

   Found this slideshare presentation (slide - 130) that says R support will be in CTP 3.0.

EDIT: Announced and demoed in PASS Summit 2015 - As of SQL Server 2016 CTP 3.0, SQL server supports Advanced Analytics (RRE integration):

  With this release, we are very excited to announce the public availability SQL Server R Services in SQL Server 2016, an Advanced Analytics capability which supports enterprise-scale data science, significantly reducing the friction for adopting machine learning in your business. SQL Server R Services is all about helping customers embrace the highly popular open source R language in their business. R is the most popular programming language for Advanced Analytics. You can use it to analyze data, uncover patterns and trends and build predictive models. It offers an incredibly rich set of packages and a vibrant and fast-growing developer community. At the same time, embracing R in an enterprise setting presents certain challenges, especially as the volume of data rises and with the switch from modeling to production environments. Microsoft SQL Server R Services with in-database analytics helps customers embrace this technology by supporting several scenarios.

Also, Henk blogs about Introducing Microsoft SQL Server 2016 R Services


JDBC Sqlserver to elastic search : one input to different output?

So I figured out what I was doing wrong, there were 2 different problems.
1. Wrong inserted row count
This was caused by the SQL query I was using. Elastic wants unique document_id values and the left join I was using caused multiple rows to be returned with the same values. What elastic does in such case is it overwrites the existing row with the new values.
2. Assign rows to different indexes and types
I had tried using the same syntax as in the document_id which ended up looking like this:
index => "%{Language}"
document_type => "%{MerchantID}"

This would result in rows getting inserted with in an index called %{Language} which was obviously not the desired result.
The problem was simple: ELASTIC DOES NOT LIKE UPPERCASE LETTERS.
So my final configuration file ended up looking like this:
input {
jdbc {
jdbc_driver_library => "C:\elastic\Microsoft-JDBC-Driver-6.0-for-SQL-Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://10.11.12.13:1433;databaseName=testdb1;integratedSecurity=false;user=ElasticExtractor;password=flyingweisels;"
jdbc_user => "ElasticExtractor"
jdbc_password => "flyingweisels"
statement => "select top 50000 id itemid,item_id itemid,Merchant_ID merchantid,model,case when (ID%2=0) then 'germanindex' else 'englishindex' end language from items order by id desc"
}
}
output {
elasticsearch {
hosts => "localhost:9200"
index => "%{language}"
document_type => "%{merchantid}"
document_id => "%{itemid}"
}
}

It works great !


SQL Server Service does not start automatically after system restart

One issue that can cause this is that some systems, particularly VMs with the system volume on solid state storage, is that it boots up so fast and gets to the point of starting services before some of the networking has been fully initialized.  This, then, results in a failure of the service account authentication, and the service start fails accordingly.

To address this, set the SQL Server services to Automatic (Delayed Start).  This will put a 2 minute delay on starting them.  If 2 minutes is too much or not enough, you can tweak it by changing HKLM\SYSTEM\CurrentControlSet\Control\AutoStartDelay, or you could create a Task Scheduler job that runs on Windows startup and continually attempts to start the services until they are started.


After successfully executing this query, how do go I back to the previous stage in MySQL?

If your table engine is not InnoDB, or you haven't used transactions for this operation, then it might be not possible.

To have possibility to ROLLBACK DML statements (like INSERT, UPDATE or DELETE queries) you should use transaction blocks:

START TRANSACTION;

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;
-- and more DML queries

COMMIT; -- or ROLLBACK;

Since transaction was COMMITed it can not be rolled back. Only option (that I see) is to restore database backup (if it exists).

UPDv1:

Just as an advice: Before you do any critical changes to database (perform transactions, execute complex scripts, that includes INSERTs, UPDATEs and DELETEs or change data structures with ALTERs or DROPs) make a backup of your database.

You may probably restore backup as another database copy, then test script on it. If nothing goes wrong, then perform script for a real database.


How to calculate Day on the base of Hours Condition.?

I want when Hours=>8 then Day=1 and Hours<=8 then Day =0

So when it is 8 you want both 0 and 1?

I have chosen hours smaller than, or equal to 8 = 1 and hours < 8 = 0 as day.

Let me know if that is not what you want.

   SELECT Empcode,cast(Date as date) As [Date],
   MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
   MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
   DATEDIFF(Hour ,MIN(Date),Max(Date)) AS [Hours],
   case when DATEDIFF(Hour ,MIN(Date),Max(Date)) >= 8 Then 1
        when DATEDIFF(Hour ,MIN(Date),Max(Date))  < 8 then 0
        end as [Day]
   FROM @HR2 AS t
   GROUP BY Empcode,CONVERT(date, date);

Testing

DECLARE @HR2 table(empcode int, date datetime2,inout tinyint)
insert into @HR2(empcode,date,inout)
values(1,'2019-01-03 07:47:46.487', 1)

insert into @HR2(empcode,date,inout)
values(1,'2019-01-03 17:47:46.487', 2)

insert into @HR2(empcode,date,inout)
values(2,'2019-01-03 09:47:46.487', 1)

insert into @HR2(empcode,date,inout)
values(2,'2019-01-03 15:47:46.487', 2)

SELECT Empcode,cast(Date as date) As [Date],
   MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
   MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
   DATEDIFF(Hour ,MIN(Date),Max(Date)) AS [Hours],
   case when DATEDIFF(Hour ,MIN(Date),Max(Date)) >= 8 Then 1
        when DATEDIFF(Hour ,MIN(Date),Max(Date))  < 8 then 0
        end as [Day]
   FROM @HR2 AS t
   GROUP BY Empcode,CONVERT(date, date);

Result

Empcode Date    INOUT_INTIME    INOUT_OutTime   Hours   Day
1   2019-01-03  2019-01-03 07:47:46.4870000 2019-01-03 17:47:46.4870000 10  1
2   2019-01-03  2019-01-03 09:47:46.4870000 2019-01-03 15:47:46.4870000 6   0


"bytes currently in the cache" : 6009288998 (5.5 GB), "maximum bytes

The number of bytes currently in wiredTiger cache.

  configured" : 7516192768 (7 GB),

Maximum size cache is configured (by default half of physical memory on the server).

  "bytes read into cache" : 18380155206 (17 GB),

Cumulative number of bytes that have been read into the cache since startup.

  "bytes written from cache" : 17020204785 (16 GB),

Cumulative number of bytes that have been written out of the cache into files on disk since startup.

  bulk update on DB3 with 23000 update command

You ran a single command with 23K update operations in it, so you were seeing the results you should have expected - you can see in the mongostat output that commands are constantly running and that you always have active writer.

If you actually want high throughput you should use multiple threads to send the operations in parallel to allow use of all cores on the machine.

  see dirty% is getting increased continuously

That shows that in fact the writes are happening - higher and higher percentage of the cache is being written to (dirtied).  When a checkpoint starts running, the dirty percentage will go down signifying that data from the cache is being persisted to disk (but of course, it's also been written already into write-ahead-log aka journal at the time of original write).

  does compression of index also reduces RAM requirement

Yes, indexes are prefix-compressed and the format is the same on disk and in RAM.


SSMS doesn't have any built-in formatting functions, but there are several 3rd party tools and web sites that can help, many of them free, and all of them surely better than using Word:

http://www.red-gate.com/products/sql-development/sql-prompt/
http://www.apexsql.com/sql_tools_refactor.aspx
http://www.dpriver.com/pp/sqlformat.htm
http://www.cleancss.com/sql-beautify/
http://www.sql-format.com/
http://poorsql.com/
http://www.freeformatter.com/sql-formatter.html

This is just a brief subset from the first page of a search result. I am sure there are dozens of others, but I don't want to turn this into a shopping list question - the main thrust is that you need to pick a tool that you like, rather than hoping SSMS can do it directly.


How can I get all CREATE VIEW statements in Teradata?

You'll need to use the catalog views (h/t to Dieter for alerting me to truncation of RequestText in TablesV):
SELECT
  Tbl.DatabaseName
 ,Tbl.TableName
 ,COALESCE(TblText.LineNo,1) AS LineNo
 ,COALESCE(TblText.RequestText,Tbl.RequestText) AS RequestText
FROM
  DBC.TablesV Tbl
LEFT JOIN
  DBC.TableTextV TblText
    ON TblText.DatabaseName = Tbl.DatabaseName
            AND TblText.TableName = Tbl.TableName
        AND TblText.TableKind = Tbl.TableKind
WHERE
  DatabaseName = <Database name>
    AND TableKind = 'V'


The elevated permissions are not documented, and the person who granted them is no longer with the organization. Nobody is confident that we know what they all are.

You'll have to fix that.
Sart with dumping the easy stuff, like the explicit permissions, role memberships, and members of the QA role.  eg
--qa role explicit permissions
select *
from sys.database_permissions p
where grantee_principal_id = user_id('qa')

--roles that the qa role is added to
select r.name role_name
from sys.database_principals r
join sys.database_role_members rm
  on rm.role_principal_id = r.principal_id
where rm.member_principal_id = user_id('qa')

---members of the qa role
select user_name(rm.member_principal_id) qa_member
from sys.database_principals r
join sys.database_role_members rm
  on rm.role_principal_id = r.principal_id
where rm.role_principal_id = user_id('qa')

And use those to generate a permissions script that is applied after restore to QA.  But keep a copy of your old QA database online so you can find stuff you missed.  EG individal team members with directly-granted permissions, etc.


Is there any way to connect to one server and utilize a linked server using Windows authentication and NTLM?

Without setting up Kerberos then no.  Why do they not want to setup Kerberos?  It's already 99% setup.  All you need to do is create the SPNs and restart the SQL Server instances.  Kerberos is the default AD authentication system.  You are falling back to the less secure authentication systems.  Sounds like the powers that be don't understand the differences.


How to drop all connections to a specific database without stopping the server?

The query like this should help (assuming the database is named 'db'):

select pg_terminate_backend(pid) from pg_stat_activity where datname='db';

pid used to be called procpid, so if you're using a version of postgres older than 9.2 you could try the following:

select pg_terminate_backend(procpid) from pg_stat_activity where datname='db';

However you have to be a superuser to disconnect other users.

It might also be useful to REVOKE CONNECT ON DATABASE FROM PUBLIC or something similar, and then GRANT it afterward.


How to select the first unique data type in a table

Due to the bad naming convention on your question, I will use ColumnA as A, ColumnB as B and ColumnDate as Date.

I am assuming that you require the first item as it's fetched from storage, no particular order. And you have to know that if the row gets updated it can be migrated from the current block and so, the query result will eventually change.

This queries are examples only and you have to edit them to get exactly what you want.

You may try the following query:

SELECT ColumnA, ColumnB, ColumnDate
FROM YOURTABLE
WHERE ColumnA='New'
AND rownum = 1;

In Oracle every result set have an implicit row number that you can use for limiting output.

If, by any chance, you're using oracle 12c you can use the brand new feature for top n queries

SELECT ColumnA, ColumnB, ColumnDate
FROM YOURTABLE
WHERE ColumnA='New'
FETCH FIRST 1 ROWS ONLY;

I hope this helps!


Why I can tnsping Server P from Server A but cannot tnsping Server P from Server B?

Ping uses ICMP, tnsping checks the connectivity of the host and port defined in the TNS entry.

  I checked the tnsnames.ora of A and B. They are identical.

Yes, and name resolution may be still different. Or more likely the firewall may block connection attempts from host B on the listener port.

Just use telnet, nc or whatever tool you have installed to check the host + port combination:

telnet serverP_ip_address listener_port
nc -z serverP_ip_address listener_port
echo 1 > /dev/tcp/serverP_ip_address/listener_port


DB2 Trigger Error : An unexpected token "OR INSERT" was found following SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

You need to replace BEGIN ATOMIC with just BEGIN in the trigger body.

BEGIN ATOMIC indicates an inlined compound SQL statement, BEGIN -- a compiled compound statement. Documentation states:

  If multiple events are specified, the triggered action must be a compound SQL (compiled) statement.


Extract SYNONYM and SEQUENCE from Informix metadata

Although you don't mention the version of the informix, try the ones above.

I believe that since V9.4 there is a system catalog table named SYSSEQUENCES where you can find the information about sequences on each database.

For the synonyms the one to be used is SYSSYNTABLE, if you have an older version try the SYSSYNONYMS.


What are the options of reducing tempdb latency?

You've got two basic options.

Reduce the workload required by the tempdb database
Get faster hard drives

As for #1 look for indexes in other databases which are missing.  Fixing missing indexes will cause less spill to the tempdb database which will reduce the workload.

If the applications on the server are using temp tables a lot there's nothing you can do but get faster hard drives.

Now all that said, if the latency problem is on the PFS pages in the tempdb database (you'll need to find the specific page IDs which are having latency issues) then the fix is easy, add more tempdb database files.


Duplicate records returned from table with no duplicates

There are some tricky scenarios which can result in the same row being read twice from an index, even under the READ COMMITTED isolation level.

Your query does not qualify for an allocation order scan, so the storage engine will read the data from the table in the order of the clustered key.

For your table, you have the InProgress as the first column of the clustered key. It's likely that you're getting row or page locks as you scan through the table. If you read a row near the start of the scan, release the lock on it, that row is updated such that InProgress changes from 0 to 1, and then the row is read again in a different page then you could see duplicate WorkID values from your query.

There are lots of workarounds. You could insert into a heap and simply remove duplicate values. You could add a DISTINCT to the query. You could also enable a row-versioning isolation level, to provide a stable view of the committed state of the database, either as at the beginning of the transaction (snapshot isolation), or as at the beginning of the statement (read committed snapshot isolation).

Perhaps it's appropriate to add locking hints or to change the structure of the table. For a rather fun solution (probably not appropriate for production), you could try reading the index backwards. This can be done with a superfluous TOP along with an ORDER BY. Below is a very simple demo to illustrate the point:

CREATE TABLE #WorkTable (
    InProgress TINYINT NOT NULL,
    WorkID INT NOT NULL
    , PRIMARY KEY (InProgress, WorkID)
);

INSERT INTO #WorkTable WITH (TABLOCK)
SELECT (RN - 1) / 5000, RN
FROM
(
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

The following query has the Ordered:false property but it'll still read the data in clustered key order:

SELECT WorkId
FROM #WorkTable;

However, the following query will read the data in reverse clustered order:

SELECT TOP (9223372036854775807) WorkId
FROM #WorkTable
ORDER BY InProgress DESC, WorkId DESC;

We can see this by looking at the scan properties:

For your table, this means that if a row is updated such that InProgress changes from 0 to 1 it will be far less likely that it'll show up twice. It may not show up at all which could be a different problem.


pgAdmin and PostgreSQL compatibility matrix

PostgreSQL versions 9.5 and above are incompatible with pgAdmin 3.

Attempting to connect gives the message:

  The server you are connecting to is not a version that is supported by this release of pgAdmin III. pgAdmin III may not function as expected. Supported server versions are 8.4 to 9.4.


Efficient query plan for selecting all data duplicated on several columns

Updated after OP supplied test case voiding much of this answer.
Postgres version
There is a good chance Postgres 9.2 will perform better. A lot of improvements for big data have gone into it.
Simplified query
First off, you can simplify your query quite a bit:
SELECT u.usr_id
      ,t.team_name, t.team_streetno, t.team_city
      ,u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
FROM   usr  u
JOIN   team t USING (team_id)
JOIN (
   SELECT u.match_firstname, u.match_lastname, u.match_jobtitle, u.match_department
         ,u.match_email, t.match_team_name, t.match_team_streetno, t.match_team_city
   FROM   usr  u
   JOIN   team t USING (team_id)
   GROUP  BY u.match_firstname, u.match_lastname, u.match_jobtitle, u.match_department
            ,u.match_email, t.match_team_name, t.match_team_streetno, t.match_team_city
   HAVING count(usr_id) > 1
   ) AS sub USING (match_firstname, match_lastname, match_jobtitle, match_department
                  ,match_email, match_team_name, match_team_streetno, match_team_city)
ORDER  BY u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
         ,t.team_name, t.team_streetno, t.team_city;

I removed the JOIN to the table team from the subquery, guessing that all the columns stem from table usr (my request for table definitions went unanswered ...) That should help a bit.
Turns out, some of the columns belong to table team.

I replace the verbose equijoin conditions with the much shorter USING syntax. That's also probably slightly faster.

Replace count(usr_id) > 1 with count(*) > 1. Does the same here (since usr_id is the PK, ergo NOT NULL), slightly faster.

I took the test case @RichardTheKiwi posted in the comments and created an sqlfiddle from it.
I also ran a local test with temporary tables on PostgreSQL 9.1.6.
Only slightly faster than the query in the question:
1338 ms for 15137 rows (out of 70k).
EXISTS semi-joins might be a hot contender if you have many duplicates. With only a few, not so much. You already tried and ruled that out. Are you positive you tried optimal syntax?
Window function count()
You could also try if count() as window function is faster - without CTE and without additional joins. But I doubt it. I sure does make the query shorter, though:
SELECT *
FROM  (
   SELECT u.usr_id
         ,t.team_name, t.team_streetno, t.team_city
         ,u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
         ,count(*) OVER (PARTITION BY
            u.match_firstname, u.match_lastname, u.match_jobtitle, u.match_department
           ,u.match_email, t.match_team_name, t.match_team_streetno, t.match_team_city) AS ct
   FROM  usr  u
   JOIN  team t USING (team_id)
   ) x
WHERE  ct > 1
ORDER  BY usr_firstname, usr_lastname, usr_jobtitle, usr_department
         ,usr_email, team_name, team_streetno, team_city;

50% slower in my local test.
Multi-column index?
This is largely obsolete after I have seen the test case.
Indexes are obviously hard to use here, since the columns to check for dupes vary. You might be able to profile user behavior and identify the most common combinations to support those with an index. There is a more radical possibility here, though:
Postgres can use multi-column indexes, even if not all index columns are used. Performance is much better if one or more leading columns of the index are used in the condition, but it still works if not. Compare:
Working of indexes in PostgreSQL
If we can assume that most use cases will check on, for instance,  (match_firstname, match_lastname), a multi-column index like the following might help. Not sure, you'll have to test:
CREATE INDEX usr_match_multi_idx
ON usr (match_firstname, match_lastname, match_jobtitle, match_department, match_email);

Columns of the index in order of decreasing usefulness. Order of columns in your GROUP BY / PARTITION BY clause is not relevant.
Index did not help in my local test - was used, but made the query ~ 20 % slower.
Partial indexes?
If the table is read-only or at least rarely written
and many of your columns often have unique values, you could take this a step further. Rows with a unique value in one of the used match_* columns can be excluded right away. For columns with many unique values it would pay to either add a column match_email_mult boolean which you set TRUE if match_email is not unique for this row. Or, if you have many columns, you could use a bit(n) column or use an integer columns for an even small footprint on disk. More under this related question on SO.
Don't bother with columns with only few unique values.
CREATE INDEX usr_match_email_mult_idx ON usr (usr_id)
WHERE match_email_mult;

Repeat for all qualifying columns. Then add conditions to your query:
WHERE match_firstname_mult
AND   match_lastname_mult
AND   ...

Postgres can combine a number of bitmap index scans rather quickly. If that can weed out a large portion of the table to begin with, the query planner might switch from a sequential scan to bitmap-index scans ...
Not tested.


MySql Error #1054 when adding new column to table using prepared statement

Remember that building SQL in this way is just building a String that happens to contain some text that your DBMS understands.

Your new column name, >>lot<<, is just part of that SQL:

SET @sql = CONCAT( 'ALTER TABLE ipp_sorted ADD `lot` INT PRIMARY KEY AUTO_INCREMENT=', @lot );

Leaving >>lot<< outside the string literal, the DBMS is trying to interpret it as a column in something or other (variables being prefixed with '@' signs) but it can't find such a column, so gives up with this error.


Urgent help with TempDB log file growth mystery

What you are you seeing is by design for tempdb Transaction Log file. I wish Microsoft had better documentation on this.

How is the transaction log growth increasing upto 80% and then
lowering down back to 0 and then increasing and growing and so on ,
with reaching almost max 90-95%.

From the First reference article:

Tempdb is not recovered in the event of a crash, and so there is no
need to force dirty tempdb pages to disk, except in the case where the
lazywriter process (part of the buffer pool) has to make space for
pages from other databases.
A checkpoint is only done for tempdb when the tempdb log file reaches
70% full – this is to prevent the tempdb log from growing if at all possible (note that a long-running transaction can still
essentially hold the log hostage and prevent it from clearing, just
like in a user database).
Of course, when you issue a manual CHECKPOINT, all the dirty pages
are flushed, but for automatic checkpoints, they’re not.

I suggest you monitor your tempdb Transaction Log usage and set it to a reasonable size that under normal workload it does not have to grow and shrink.
I do not know exactly why your tempdb logfile filled to 100%. There has to be a reason why the log file did not get truncated.
Will indirect checkpoint help?
In most cases no.

Any non-logged “bulk” operation that qualifies for an “eager write” in
tempdb is not a candidate to be flushed by the recovery writer (the
internal thread that runs the indirect checkpoint).
This raises an important question: which data load operation is
minimally logged on tempdb? This is important to know because
minimally logged operations on tempdb will not be flushed by the
indirect checkpoint. The following list can be used to assist you in
understanding which load operations on tempdb will be minimally logged
and which will not.

For details read Tempdb – Here’s a Problem You Didn’t Know You Had by Fabiano Amorim
I noticed this question is for SQL Server 2014, if someone is reading this for SQL Server 2016 read the below documentation by Microsoft.

Database Checkpoints (SQL Server) - check the "Indirect Checkpoint" section.
Change the Target Recovery Time of a Database (SQL Server).
Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler by Parikshit Savjani

Reference:

What does checkpoint do for tempdb? by Paul Randal
See question and answer section Inside the Storage Engine: What’s in the buffer pool? by Paul Randal


insert into dbo.tblexecution (
     fname
    ,lname
    ,brtday
)
select top 100
     [FirstName]
    ,[LastName]
    ,convert(date, dateadd(day, (abs(CHECKSUM(newid())) % 3650) * - 1, getdate()))
from [AdventureWorks2008R2].[Person].[Person]
order by newid()


MySQL2::Error: BLOB/TEXT column can't have a default value

BLOB, TEXT, GEOMETRY, and JSON columns cannot be assigned a default value.

  http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html

What you are in essence asking -- without realizing it, presumably -- is how to force MySQL to revert to its old, incorrect behavior of ignoring your incorrect table definition and pretending that you really said ALTER TABLE items ADD description text NOT NULL but with the default value missing because that is the only alternative.  So you are left with a nonsensical table definition.

The new, correct behavior is indeed tied to the new recommended @@SQL_MODE defaults.  You're going to make trouble for yourself it your preferred solution is to unfix the server, rather than unbreak your table definition.

I understand that this is "working" for you now, but my point is that it is still in fact broken.  SHOW CREATE TABLE should reveal that your declared default value is in fact not there on your current production server.


How to select all rows from column including two specific numbers within a set of digits, but not in beginning or end?

You could just use %_99_% instead of %99%.

% means 0 ore more characters
_ means exactly 1 character

So %_99_% means at least 1 character before and after 99, and anything before and after.

SQL> select * from t1;

C1
--------------------
1991
1199
9911

SQL> select * from t1 where c1 like '%_99_%';

C1
--------------------
1991


How do I ALTER all columns of one type to another type?

In psql, you'd run something like this which will output a bunch of commands.

SELECT format(
  'ALTER TABLE %I.%I.%I ALTER COLUMN %I SET DATA TYPE int;',
  table_catalog,
  table_schema,
  table_name,
  column_name
)
FROM information_schema.columns
WHERE data_type = 'bigint'
  AND table_schema NOT LIKE 'pg_%'
  AND lower(table_schema) <> 'information_schema'
  AND is_updatable = 'YES';

You can spot check them and if you're good to go execute them with \gexec.


Very strange tables in mysql own database

So I ended deleting this tables, and luckly everything is working fine , and MySql database is OK again.

No clue yet of why this tables where created , when they were created and how. Also changed all database users to avoid this happen again.

Thanks for the reply!


Using SQL Server Profiler to track performance for nested stored procedure

I got the answer in the meantime - sorry for the inconvenience

in profiler settings it is possible to select all events from there a list is provided and some settings can be checked to tracks for SP execution


Select next row only if different from current row

You need to join the table to itself. One instance of the table to find the values of the "current" row and the second to look for the first row that the values differ.

Since the date parameter may not exist in the table, we also need a subquery (derived table, named c) to find the previous row that holds the values for the "current" date:

SELECT
        s.*
FROM
        ( SELECT start_date, breakfast, lunch, snack, dinner
          FROM times
          WHERE start_date <= DATE '2016-10-16'
          ORDER BY start_date DESC
          LIMIT 1
        ) AS c        -- "current"
    JOIN
        times AS s    -- "search"
    ON
            s.start_date > c.start_date
        AND
            (s.breakfast, s.lunch, s.snack, s.dinner)
            <>
            (c.breakfast, c.lunch, c.snack, c.dinner)
ORDER BY
        s.start_date ASC
    LIMIT 1 ;

Tested at SQLfiddle.

This part:

        (s.breakfast, s.lunch, s.snack, s.dinner)
        <>
        (c.breakfast, c.lunch, c.snack, c.dinner)

is just a compact way to write:

        (  s.breakfast <> c.breakfast
        OR s.lunch     <> c.lunch
        OR s.snack     <> c.snack
        OR s.dinner    <> c.dinner
        )

I assumed that the four time columns are not nullable. If they are nullable, the above expression would need to be more complicated.


If this is local storage, layout matters a lot more. If it's SAN storage, things are a little bit different.

Here's what I do for local storage:

Put big data files (mdf, ndf) for user databases on non-SSD storage
Put log files (ldf), and tempdb (all files) on SSD

Data files are typically around 80-90% reads to writes. You can mitigate the "slow" part of reading from them by, as you say, "spend[ing] a lot on RAM". RAID 5 or 6 is the usual choice here.

Log files and tempdb are different, and they need to absorb a lot of writes quickly. They're a much better fit for faster tiers of storage. RAID 10 is the usual choice here.

The system drive storage class isn't terribly important here, but I'd make a note that backups either shouldn't be done locally, or they should be copied to redundant storage. If you lose your backup drive, you, well... Don't have backups.

If this is SAN storage, what matters even more than drive layout and storage tiers is making sure you have adequate networking paths between the server and the SAN.

For more details why, see this Q&A: SQL Server has encountered occurences of I/O requests taking longer than 15 seconds.


Finding the same value in a column and only returning rows without the same value

SELECT MIN(ProductName), DiscountPercent
   FROM Products
   GROUP BY DiscountPercent;

You might find the output from this interesting:

SELECT DiscountPercent,
       GROUP_CONCAT(ProductName)
   FROM Products
   GROUP BY DiscountPercent;


SMO Copy database or remove all non-system database objects

You could use SELECT * FROM sys.databases d WHERE d.name = '<target_db_name>'; to obtain the existing settings for the database prior to running the transfer.  Store the results in a DataSet so you can potentially use them later.

If the database does not exist, you'll get no rows returned.  If the database does exist, you could drop the existing database as the first step.

After you run the TransferData() method, you could apply the necessary configuration changes based on the items in the DataSet.  For instance the recovery_model_desc column contents could be used to set the recovery model by running this code:  ALTER DATABASE <target_db_name> SET RECOVERY = 'xxx';  where xxx is the value contained in the column.


MySql GROUP BY, ORDER BY LIMIT query taking more than 5000 seconds

I would try an index for:

location, abc_key, a_time

If you want to stretch that a bit and create a covering index

location, abc_key, a_time, val

Hovever, a table without a primary key is a bit suspicious. In fact, nothing is mandatory. You may want to investiga why this is so, and if possible fix


For this scenario I prefer the method of doing (I added WITH NO_COMPRESSION just in case the server default setting is to use Compression since there is little point in using extra CPU when the resulting file isn't going to take up any space anyway):

BACKUP LOG [databasename] TO DISK = 'NUL:' WITH NO_COMPRESSION;

The reason I prefer this method over switching to SIMPLE and then back to FULL is that it doesn't change the overall backup process. This way you can have more consistency between your environments. It also allows you to have a testing environment for your backup process because it isn't different than what you have in Production. And if sending the file to NUL: presents too much of a difference, then just send it to a folder where the backup file can be deleted.

Flipping to SIMPLE and then back to FULL makes it harder to test DIFFerential backups since you need to have a FULL backup first. So if you do a FULL backup once a week and DIFF the rest of the week, flipping to SIMPLE and then back to FULL would work if done just prior to the weekly FULL backup, but not at any other time during the week. On the other hand, taking the LOG backup and either directing it to NUL: or deleting the backup file can be done at any point during the week.

Also, taking the LOG backup and either directing it to NUL: or deleting the backup file allows you to maintain a pre-grown LOG / .ldf file. And at that point you can use DBCC SHRINKFILE to enforce a maximum initial-size of the empty LOG file and not get stuck with a file that grew A LOT due to reindexing or something else.

PLEASE NOTE that getting rid of your LOG backups means that you will not be able to restore to points in time between your FULL / DIFF backups. Of course, if you don't have FULL / DIFF backups in the first place, then this is a moot point.


Is it possible to generate DB diagram for all SQL databases opened in SSMS

No it is not possible

You have to go into each database and create database diagrams there


Transferring full database to another machine oracle 10.2 xe

You should be able to do that with an RMAN backup. You need to see what kind of backup Oracle XE supports. But you should be able to shutdown the database, startup mount, then do an RMAN backup making sure to tell Oracle where to send the backup, make sure that you include the control file with the backup. Then copy the backup over to the 64 bit server and inside RMAN use "CATALOG START WITH 'D:\backups';" or where ever the backup is. You need to copy over the init/spfile, create a service on the new server, startup nomount the new service, in rman, connect to the new database service, restore the control file, make sure that the control file restored, then restore the database. You will not need to recover the database since it was done while the database was not open.

You then need to find and run the script in %ORACLE_HOME%/rdbms/admin that will convert the database to 64 bit then upgrade the database to 64 bit. It would help if you have a 10g 64 bit home on the new server, but I was not able to install 10g on Windows 2008, it just won't install, so you probably need to run the upgrade manually. You can also upgrade as a 32 bit database on XP and then migrate the database as 11g.

Another option is to create a new 11g 64 bit database on the new server and use data pump to migrate it. Regardless you will need to test the process and probably try it more than once.


Multi-Subnet AlwaysOn: Is Routing and Remote Access always required?

Do I really have to treat this as an "AlwaysOn AG Multi subnet" even when all required routing is already implemented at networking device level and computers can all reach each other?

Yes. SQL Server isn't doing any "routing" at the network level, it's not a layer-3 device... However, if you don't treat it as a "multi-subnet cluster" then you can get yourself into some time-out causing situations. While the situations are easily rectifiable, why not save the trouble and set everything up the first time, then not have to go back and change things?

  I've read that Routing and Remote Access is supposed to be deployed and configured but I do not get why this would be necessary since the routing is already implemented at networking device level.

I don't know why someone is telling you to setup RRAS... unless it's for some local repro environment, such as the one on my laptop that doesn't have any routing devices as it's all internal virtual machines. This doesn't make any sense unless you're using RRAS as the routing device (software router).

Either way, this is all network design and has nothing specifically to do with SQL Server.


Query for cross matching multiple joined tables

Sample Data

This is actually very easy, first let's create your schema. Next time around, it's better and easier for everyone if you provide data like this.

CREATE TEMP TABLE country (
  countryid serial PRIMARY KEY,
  name text
);
CREATE TEMP TABLE city (
  cityid serial PRIMARY KEY,
  name text,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_country (
  userid int,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_city (
  userid int,
  cityid int REFERENCES city
);
INSERT INTO country (name) VALUES ('England'),('Canada');
INSERT INTO city (name,countryid) VALUES ('London',1),('Leeds',1),('Vancouver',1);
INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1),(4,2);
INSERT INTO user_city (userid, cityid) VALUES (3,1),(4,2),(2,3);

Solution

SELECT DISTINCT
  user_city_country.userid AS user1,
  user_country.userid AS user2
FROM user_country
JOIN country USING (countryid)
JOIN (
  SELECT *
  FROM user_city
  JOIN city USING (cityid)
  JOIN country USING (countryid)
) AS user_city_country
  USING (countryid);

This breaks down easy... Path one is to resolve the user to the country..

SELECT DISTINCT user_country.userid
FROM user_country
JOIN country USING (countryid)

Path 2 is to resolve the user_city to the country.

SELECT *
FROM user_city
JOIN city USING (cityid)
JOIN country USING (countryid)

Then we just JOIN them. Note, we're not actually using the country at all. You have it in your schema. So I preserved it here.

This returns...

 user1 | user2
-------+-------
     4 |     2
     3 |     1
     2 |     1
     3 |     2
     4 |     1
     2 |     2

Now, if you want to know all matches, just add WHERE user_country.userid = '1' OR user_city_country.userid = '1';

 user1 | user2
-------+-------
     4 |     1
     3 |     1
     2 |     1


How can I select the last edited version of the post?

You need a groupwise max.  I think you will need one query to find the "latest" question, then one for the latest answer.  Then UNION ALL the results together in order to get them in the same resultset.


MariaDB configuration for many updates, writes

If the server is solely used by MariaDB then you can dedicate 16-24 GB of RAM to the DB service (now it is limited to the 12 GB). That allows to make bigger InnoDB pools. If your tables/indexes are fit into the RAM then your service need no expensive additional disk I/O.

You can reduce the max_connections variable to the reasonable value but that require clients to release the connections immediately after use. That can decrease the buffers consumption and overall peak RAM requirements.

Also you have to set up the join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size variables to the appropriate values to ensure your queries do not use on-disk temporary tables for the intermediate datasets. But that values shouldn't be too big as far as they are defined for each connection and cumulative RAM consumption can reach (max_connections * all_buffers) + innodb_buffer_pool_size and even more.

The good starting point is the
SET @@global.slow_launch_time = 0.5, @@global.slow_query_log = 1;
This command will start logging of the queries running for more than 0.5 second for further analysis. If the global variable log_queries_not_using_indexes is set to 1 then queries with no indexes will also be logged there. As far as your DB isn't too big it's more probable that slowdown is caused by poor indexing than bad server configuration.


SSDT/SSIS 2012 - How to automatically load some projects?

It's a little unclear what you're after, but here are two different features in SSDT (Visual Studio) you may want to take advantage of. These can be accessed by going to Tools | Options, then selecting the appropriate node from the tree as shown in the screenshots.

First, you can set SSDT to open the solution that was open when you closed the application the last time:

Second, you can set the default location for your projects, so when you go to Open a project, it automatically goes to this location first:


If you were designing from scratch, the structure you have is not ideal... however, it can easily be made to work as it is, with one small change to the structure and a more thorough understanding of what SQL can accomplish through joins.

You need to relax the unique constraint on `article_asset`.

UNIQUE KEY `asset_article` (`asset_id`,`article_id`)

This restricts you to not reusing the numeric asset_id of two different types of assets with the same article, which isn't correct, since asset_id is a value that can come from three different information domains (the primary keys of the three tables).  The correct constraint would be this:

UNIQUE KEY `asset_article` (`asset_type`,`asset_id`,`article_id`)

With this, you are only restricted from duplicating the asset_id with the same asset_type for the same article, so this is the correct constraint for that unique index.  That's the only actual change you have to make to your schema.

Then, modify your query structure to effectively treat asset_article as if it were multiple different tables, "taa" (topic article assets) and "caa" (celebrity article assets), each containing a distinct subset of the rows in asset_article.

SELECT a.*
  FROM articles a
  JOIN article_assets taa ON taa.article_id = a.id AND taa.asset_type = 'topic'
  JOIN topics t ON t.id = taa.asset_id
  JOIN article_assets caa ON caa.article_id = a.id AND caa.asset_type = 'celebrity'
  JOIN celebrities c ON c.id = caa.asset_id
 WHERE t.name = 'Family'
   AND c.name = 'Ashton Kutcher';

A logically-equivalent way of expressing this query (above) looks like this (below), which might make it clearer how we are joining both the "taa" and "caa" subsets of article_asset to the articles on the article_id ... or it may make everything much less clear. :)

SELECT a.*
  FROM articles a
  JOIN (article_assets taa JOIN topics t
                           ON t.id = taa.asset_id
                           AND taa.asset_type = 'topic') ON taa.article_id = a.id
  JOIN (article_assets caa JOIN celebrities c
                           ON c.id = caa.asset_id
                           AND caa.asset_type = 'celebrity') ON caa.article_id = a.id
 WHERE t.name = 'Family'
   AND c.name = 'Ashton Kutcher';

A similar structure could be used to find articles about two different celebrities, two different topics, or 1 celeb + 2 topics + 1 brand or however many you felt like stringing together.

With your sample data, my example queries return nothing, because there is no overlap among the different types of assets associated with each story, but after changing the UNIQUE constraint, you can modify the data so that there are overlaps and the query will find the correct results.

http://sqlfiddle.com/#!2/99e4d/4

On the other hand, if you do decide to go with a different design, I would suggest that the more correct approach involves fewer tables -- not more tables.  If you step back and look at the three kinds of "things," you may see that they are all, in fact, one single kind of thing, or at least subtypes of a single kind of thing.  There is no reason why celebrities, topics, and brands should be stored separately -- they are all "subjects" of the articles.  When "things" in a database are the same "kind of thing," they should all be in one table.

For example, if you had a database of contact information, you wouldn't have an "office phone numbers" table and a "home phone numbers" table, and then a table matching people to phone numbers telling you which table to look in for the phone number... right?  That makes no sense at all.  This is the same scenario, just slightly less obvious because you didn't model it based on the common thread among these apparently different (but, arguably, actually the same) kinds of things.

I can't give you a textbook phrase to describe what I'm attempting to say, but if you ever need to look at an attribute in a row in order to determine what information domain another attribute in the row represents, you are definitely "doing it wrong."

One important reason for this assertion is that you cannot enforce referential integrity via foreign key constraints when the "foreign key" could reference the primary key of any one of several tables.

Consider this structure:

CREATE TABLE subject (
  id INT NOT NULL AUTO_INCREMENT,
  subject_type ENUM('brand','celebrity','topic') NOT NULL,
  name VARCHAR(255) not null,
  PRIMARY KEY(id)
);

CREATE TABLE article_has_subject (
  article_id INT NOT NULL,
  subect_id INT NOT NULL,
  PRIMARY KEY(article_id, subject_id),
  KEY(subject_id, article_id) /* to optimize joins in either direction */
);

You could replace that ENUM with a subject_type_id and a corresponding subject_type table, and then, if you wish one day that you wish you had created an "places" table so you could find all of the stories about 'Family' and 'Rhode Island' there's no need to change your schema... you just add another subject_type record.

In your current structure, you're also storing a redundant piece of information in every row of article_asset -- the type of the asset -- because you need that to find the table where you should look.  Redundant data in a database is rarely a good thing.


Three table query to find the most recent item based on a distinct value

This returns the row from table A that relates to the most recent record in table B for each keyvaluebig in table C

Setup:

DECLARE @TableA TABLE
(
    itemnum INT
    , itemname NVARCHAR(50)
);

INSERT @TableA (itemnum, itemname)
VALUES (123, 'document #1'), (456, 'document #2');

DECLARE @TableB TABLE
(
    itemnum INT
    , keyvaluedate DATETIME2
);

INSERT @TableB (itemnum, keyvaluedate)
VALUES (123, '2015-09-05 00:00:00.000'), (456, '2011-06-04 00:00:00.000')
,(789, '2011-06-04 00:00:00.000'), (546, '2011-06-04 00:00:00.000')
,(378, '2011-06-04 00:00:00.000');

DECLARE @TableC TABLE
(
    itemnum INT
    , keyvaluebig BIGINT
);

INSERT @TableC (itemnum, keyvaluebig)
VALUES (123, 123456), (456, 987654), (545, 987654), (387, 987654);

Query:

WITH cte_Numbers
AS
(
    SELECT  c.keyvaluebig
            , b.itemnum
            , ROW_NUMBER() OVER(PARTITION BY c.keyvaluebig ORDER BY b.keyvaluedate desc) AS RowNumber
    FROM    @TableB b
    JOIN    @TableC c ON b.itemnum = c.itemnum
)

SELECT  a.*
FROM    @TableA a
JOIN    cte_Numbers n ON a.itemnum = n.itemnum
WHERE   RowNumber = 1

As mentioned by Mark Sinkinson this really is the place to go to see how to do this sort of stuff


Checking when a row was inserted into a table on SQL Server?

To achieve this retrospectively you could do so by inspecting the contents of the transaction log to identify when the insert occurred, of course you need to have access to the relevant transaction log that contains the details.

Here is a walkthrough of how you would inspect the transaction log to locate a table drop event but the process you would follow is mostly the same.

If auditing of this level is actually something that's required then your best bet is to build it into your overall solution, whether through the use of DML Triggers, CDC or Change Tracking or a customised solution.


SQLite: Using count in triggers to update another column

Your error i can't reproduce, but i think you want this for your trigger.
MAX(u_cnt) + 1 can also be used as long as the insert only uses 0 for u_cnt

CREATE TABLE rec_info
    ("id" INTEGER, "username" TEXT(6), "u_cnt" TEXT(6))
;

create trigger count_user
after insert on rec_info
begin
  update rec_info
      set u_cnt = ( select COUNT(*) from rec_info  where username = new.username)
  WHERE username = new.username;
end

INSERT INTO rec_info
    ("id", "username", "u_cnt")
VALUES
    (1, 'MarkC', '0')
;

INSERT INTO rec_info
    ("id", "username", "u_cnt")
VALUES
    (2, 'PeterB', '0')
;

INSERT INTO rec_info
    ("id", "username", "u_cnt")
VALUES
    (3, 'MarkC', '0')
;

INSERT INTO rec_info
    ("id", "username", "u_cnt")
VALUES
    (4, 'MarkC', '0')
;

SELECT * FROM rec_info

id | username | u_cnt
-: | :------- | :----
 1 | MarkC    | 3
 2 | PeterB   | 1
 3 | MarkC    | 3
 4 | MarkC    | 3

db<>fiddle here


I had before my knexfile.js like this:

    var knex = require('knex')({
    dialect: 'sqlite3',
    connection: {
      filename: './database'
    },
    useNullAsDefault: true
  });

It was needed to activate here the PRAGMA for foreign_keys. To do it change it to:

    var knex = require('knex')({
    dialect: 'sqlite3',
    connection: {
      filename: './database'
    },
    pool: {
      afterCreate: function (conn, cb) {
        conn.run('PRAGMA foreign_keys = ON', cb)
      }
    },
    useNullAsDefault: true
  });


sh.status() can only be executed on your mongos. As you posted your "sharding status" with sh.status() in your question yourself. You can't do that on the separate shards.
Sharding is for distributing your documents. For example documents ABC on shard0000 and documents DEF on shard0001. So Sharding is not Replication. If you want to replicate your documents then you need to look at 'How to setup a mongodb replica set' : http://docs.mongodb.org/master/tutorial/deploy-replica-set/

Update :  a full answer you can find here : Difference between Sharding And Replication on MongoDB


Sure it is possible. I have done this dozens of times.
It is a simple RESTORE, then a RECOVER phase 5 days later. Or you can even run RECOVER periodically to keep the copy up to date.

Just make sure you do not mess up the catalog with the copy database. Usually I just restore the latest controlfile and use without a catalog connection to prevent RMAN registering restored copies of datafiles in the catalog.

Or use DUPLICATE if you want to use Data Guard as suggested.


If the content of the properties column is like in your screenshot, it's easy to remove everything from the content except for the pure number IDs:

SELECT REPLACE(REPLACE(properties, '{"listing_id":', ''), '}', '') AS listing,
       *
FROM   ahoy_events;

This will give you the pure numbers from your json column as (new) column listing.

| listing | id | visit_id | ... | properties        |
|---------------------------------------------------|
| 27      | 57 | 2        | ... | {"listing_id":27} |
| 23      | 58 | 2        | ... | {"listing_id":23} |

You can then group by this value.

Was this what you were asking for?


Am I supposed to let PG know in advance before I back up its datadir?

In order to create a usable online file system backup, you have to do one of the following:

Run pg_basebackup with -X stream (the default in later versions). That will do everything for you.

If you want to use this Robocopy, you have to use the low-level API:

enable WAL archiving by setting archive_mode and archive_command to archive WAL segments (you might use Robocopy there)

run SELECT pg_start_backup('label'); to get the database into backup mode

backup the data directory with Robocopy

end backup mode with SELECT pg_stop_backup();

To recover, you wilk have to use restore_command
Note that I showed you the comparatively simple deprecated "exclusive backup", the "non-exclusive backup" is somewhat more complicated.

Consult the documentation for details.


Oracle explain for cascading delete is wrong

I don't think this is possible.

If I'm not mistaken, the optimizer doesn't even know (or at least doesn't try to know) that the statement will "touch" other tables as well.

It's not explicitely documented, but the following quote from the manual only talks about the tables "referenced" in the SQL statement (emphasis is mine):

The row source tree is the core of the execution plan. It shows the following information:
   -  An ordering of the tables referenced by the statement
   -  An access method for each table mentioned in the statement
...

I think the important part is: "tables referenced by the statement". No mentioning of "dependent" tables.

Edit:
If you need to extract that information, you probably have to trace your session and look at the TKPROF output.


MySQL Assuming unsigned result in subtractions, what's the motive?

Catch-22.
I'll use TINYINT to make the numbers easier.  SIGNED range is -128..127; UNSIGNED is 0..255

Your question, as I see it, is "Why not make the result SIGNED?"

Your case is (10) - (12) = (-2), which cannot be represented as UNSIGNED.

A different case is (200) - (1) = (199), which cannot be represented as SIGNED.

Either decision leads to some arguments producing an value that is "out of range".

(I don't know the history of the decision.  Also, I don't know if there is an SQL standard that is involved.)


SSRS (2005): conditionally format null/zero count cells based on group

I finally found the solution was to reference the category it's grouped by (weeks wait) - it's a little cumbersome because it's a string, but:

  =Iif( Instr("7-8wks8-9wks[...]51-52 wks52 wks +",ReportItems!Weeks_Wait.Value) > 0,"Red","Green")


How to optimize inner join query on one large table and one small table

Looks to me like the best plan is being used:

First scan the small table B to make the hash buckets,
then scan the big table A to find the matching rows from B via the hash.

You may have an I/O problem, 15 seconds for 15 million records, although if I assume that each row of table A is about 100 bytes long, then scanning it in 50 seconds is a throughput of about 30 MB/s, which isn't out of the ordinary on normal hardware, and depending on what else is going on.

And then, yes, you spend a lot of time in the DISTINCT sort. If you join and you get multiple matches, but you are not interested in the replicates at all, then it means you don't really need the columns of the joined table, except if your schema isn't normalized.

It seems that that's the clue actually.

SELECT distinct a.student_id, b.student_name, a.class_year
  FROM table_a a
  INNER JOIN table_b b on a.student_id = b.student_id;

The query doesn't seem to be logically thought through. You would get all those different class years, but nothing else from table_A. Look at your final result numbers, it says "rows=5078", which is about the size of table_B. You probably should normalize all these repeating class_years out of this massive table_A.


Sqlserver exec sp_execute to simple query?

The application that you are profiling is using prepared statements. The SQL statements are being sent as sp_prepare @handle OUTPUT, params, 'T-SQL statement' before the calls to sp_execute are executed. You need to also capture the "SQL:Prepare" event in order to see what statements are being submitted. But I am not sure if you get to see the returned handle (i.e. the statement_id) that is then passed in as the first parameter to sp_execute (example: the 2 in exec sp_execute 2,...). The handle is per each Connection / Session, so handle of 2 for Session 53 is not necessarily the same as handle of 2 for Session 78. Meaning, you need to match the session_id / SPID between calls to sp_prepare and sp_execute.

Please see the following documentation:

sp_prepare
sp_execute