How to display the exact query in the output section of a stored procedure when using 'Dynamic SQL' and 'Bind variables' in Oracle

Would REPLACE do any good? See line #6 and result of DBMS_OUTPUT.PUT_LINE.
SQL> create or replace procedure first_test(i_tx varchar2, o_count out number) is
  2    qry varchar2(1000);
  3  begin
  4      qry := 'select count(*) from z_test_a where c_num = :1';
  5
  6      dbms_output.put_line(replace(qry, ':1', i_tx));    --> like this
  7
  8      execute immediate qry into o_count using i_tx;
  9  end;
 10  /

Procedure created.

SQL> declare
  2    l_cnt number;
  3  begin
  4    first_test(1, l_cnt);
  5  end;
  6  /
select count(*) from z_test_a where c_num = 1               --> result you want, I guess

PL/SQL procedure successfully completed.

SQL>


Overly eager explicit row locking causes deadlock

Would it suffice to SELECT ... FOR UPDATE instead?
Can we look at the entire transaction?  Speeding it up will decrease the frequency of deadlocks.
Even if you find a way around this deadlock, you must prepare to get deadlocks.  Usually, it suffices to re-run starting with the START TRANSACTION.

Another approach might prevent the "over-eager" problem:

Put the list of ids in a table (possibly a TEMPORARY table).  Then JOIN to it instead of using IN ( big list ).


delete from #PulledData where #PulledData.Name0 IS NULL


Is there a way to find all Logins, and all database users this login have?

First, go grab sp_foreachdb from the First Responder Kit (background here):

SQL-Server-First-Responder-Kit/sp_foreachdb.sql

Then you can collect all the database users here:

CREATE TABLE #DBUsers
(
  DBName sysname,
  DBUserName sysname,
  DBPrincipalID int,
  SID varbinary(85),
  LoginName nvarchar(128),
  LoginPrincipalID int
);

EXEC master.dbo.sp_foreachdb N'INSERT #DBUsers
SELECT ''?'',d.name, d.principal_id, d.sid, s.name, s.principal_id
FROM ?.sys.database_principals AS d
LEFT OUTER JOIN sys.server_principals AS s
ON d.sid = s.sid
WHERE d.principal_id > 4
  AND d.type <> ''R''
  AND d.name NOT LIKE N''##%##'';';

Now for example to find any server-level login that doesn't have an associated user in any database (note this will currently include those in elevated roles, like sysadmin):

SELECT * FROM sys.server_principals AS p
WHERE type NOT IN ('S','R','C')
  AND name NOT LIKE N'##%##'
  AND name NOT LIKE N'NT %\%'
  AND NOT EXISTS
  (
    SELECT 1 FROM #DBUsers
    WHERE SID = p.sid
  );

Note that you might have to dig a bit deeper, because a user might have been added to a database to explicitly deny access. But that should be a good start for the normal case.


DBF file exist while user has been dropped?

Datafiles are allocated to tablespaces, not users. Dropping the users will remove the tables etc. from the tablespace, but the underlying storage is not affected.

Don't delete the dbf files from the filesystem directly, you'll get your database into a mess. To remove them, find which tablespace the files belong to using the following statement:

select tablespace_name
from   dba_data_files
where  file_name = <file name with full path>;

You can then remove the file(s) by dropping the tablespace:

drop tablespace <tablespace_name> including contents and datafiles;

Before doing this you should verify that there aren't any objects still allocated to the tablespace for other users however. You can find this by running:

select * from dba_segments
where  tablespace_name = <tablespace to drop>;

If this returns anything, move the objects to another tablespace if you want to keep them before dropping the tablespace.


Denied access sending dbmail while impersonated under a login with sysadmin privilege

When EXECUTE AS is used within a stored procedure to switch security contexts, it is sandboxed to the current database. This can be extended by using the database property TRUSTWORTHY or the server option cross database ownership chaining but there are security implications to those decisions.


How to use --excludeCollectionsWithPrefix in mongodump

I don't know squat about MongoDB but after reading the docs something like the following is probably what you're looking for:

mongodump  --db test --excludeCollectionsWithPrefix registry.logging


SQL Server: Stored Procedure to add parent & child records

You could use Table-Valued Parameters to pass a set of InvoiceItems to a procedure that will insert a new Invoice and also insert the InvoiceItems to the InvoiceItems table.

Without knowing your table schema, I am just going to throw in a few columns.

Example user-defined table type for use as a table-valued parameter:

create type dbo.InvoiceItems_udt as table (
    ProductId int
  , Quantity decimal(9,2)
  , LineTotal decimal(19,2)
    );
go

Example procedure using scope_identity() to get the InvoiceId after inserting a row into dbo.Invoice:

create procedure dbo.Invoice_Insert_WithItems (
    @InvoiceId int output
  , @CustomerId int not null
  , @InvoiceItems dbo.InvoiceItems_udt readonly
) as
begin;
  set nocount on, xact_abort on;
  begin tran;
    insert into dbo.Invoice (CustomerId, InvoiceTotal)
      select @CustomerId, InvoiceTotal = sum(LineTotal)
        from @InvoiceItems i;

    select @InvoiceId = convert(int,scope_identity());

    insert into dbo.InvoiceItems (InvoiceId, ProductId, Quantity, LineTotal)
      select @InvoiceId, ProductId, Quantity, LineTotal
        from @InvoiceItems i;
  commit tran;
end;
go

Table-valued parameter reference:

SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven! - Leonard Lobel
Using Table-Valued Parameters in SQL Server and .NET - Erland Sommarskog
Maximizing Performance with Table-Valued Parameters - Dan Guzman
User-Defined Table Types - msdn
Table-Valued Parameters - msdn

In SQL Server 2012+, an alternative to using an auto-numbered identity() for InvoiceId, you could use a sequence.

Example of how to create and use a sequence as the primary key on dbo.Invoice:

create sequence dbo.InvoiceIdSequence as int
  start with 1
  increment by 1;

create table dbo.Invoice (
    InvoiceId  int not null default next value for dbo.InvoiceIdSequence
  , CustomerId int not null
  , InvoiceTotal decimal(19,2)
  , constraint pk_Invoice primary key clustered (InvoiceId)
  , constraint fk_Invoice_Customer foreign key (CustomerId)
      references dbo.Customer(CustomerId)
  );

In this example, instead of using scope_identity() after inserting a row into dbo.Invoice,
we would use next value for dbo.InvoiceIdSequence before inserting a row into dbo.Invoice.

create procedure dbo.Invoice_Insert_WithItems (
    @InvoiceId int output
  , @CustomerId int not null
  , @InvoiceItems dbo.InvoiceItems_udt readonly
) as
begin;
  set nocount on, xact_abort on;
  begin tran;
    set @InvoiceId = next value for dbo.InvoiceIdSequence;

    insert into dbo.Invoice (CustomerId, InvoiceId, InvoiceTotal)
      select @CustomerId, @InvoiceId, InvoiceTotal = sum(LineTotal)
        from @InvoiceItems i;

    insert into dbo.InvoiceItems (InvoiceId, ProductId, Quantity, LineTotal)
      select @InvoiceId, ProductId, Quantity, LineTotal
        from @InvoiceItems i;
  commit tran;
end;
go

Sequence reference:

Simple Talk - Sql Server sequence basics - Joe Celko
sequence - msdn
next value for - msdn


This is pretty broad. Let's answer (2) first:

http://www.postgresql.org/support/professional_support/

Now, on to (1):

  Is there any good conceptual design that could offer reliable pgsql
  cluster - reliability, data corruption prevention, high availability,
  no SPOF, load balancing and access to DB through one (virtual?) IP
  address?

The question is not "if" but "which".

You are describing a heartbeat/failover cluster. There aren't really fully canned solutions for this, you'll need your own heartbeat and STONITH using appropriate non-PostgreSQL-specific tools.

You can use PostgreSQL 9.1 streaming replication and log shipping for the replication. The replication manager repmgr can help automate much of the management.

Other replication options are described on the PostgreSQL wiki. All will require added heartbeat and STONITH for a fully transparent cluster with virtual IP, etc.

You might also want to look at pgbouncer. It's a useful tool not only for connection pooling, but also for failover and load balancing.

See the PostgreSQL manual on high availability, load balancing, and replication for more information.


Replication reverted partitioned table to primary filegroup, can I re-link to the partitions?

Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.

By default replication will initialize the subscriber with a copy of the table from the publisher.  This could have dropped your existing table on the subscriber.

On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it.

Replication won't change files or filegroups, but if the table on those filegroups was dropped, there's no way to recover it other than restoring a backup.
If you want to copy the publication data to the subscriber before starting replication, that's called Manual Initialization.  It allows you to tell replication that you've already initialized the subscriber and to just start applying changes.
Also when you set this up again, note that it's not normally necessary to land partitions on different filegroups.  It's more complicated and doesn't perform any better, so unless you are trying to put older data on slower disks, you can just put all the partitions on the same filegroup.  Using a non-default filegroup, and adding multiple (6 is a good choice) equal-sized files to the filegroup are both good practices.


Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)?

The main difference between these two query plans is in fact in the difference of Hash  Match and Merge Join. Hash Match is more efficient and as you can see the query runs faster in option 1 (not using CTE).

CTE is a great tool, but it seems to be not efficient in two cases,Complex Predicates or Non-Unique Parent/Child Key. In your case there is no unique key and SQL server has to sort the data sets first to be able to fulfill your requirement.
Have a look at the link below which tells you more on this issue:
http://blogs.msdn.com/b/sqlcat/archive/2011/04/28/optimize-recursive-cte-query.aspx

So it seems either you have to accept its slowness or rewrite the logic with WHILE loop which can be more efficient.


How do you export a database schema from SQL Server Management Studio to Excel?

You can link your server as Data Source in Excel and then run this query:

SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'

This will export data into Excel

How to connect SQL server to Excel:
https://support.office.com/en-us/article/Connect-a-SQL-Server-database-to-your-workbook-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad

Or if database is not that big then just run it in Management Studio and copy-paste the result to Excel


How should row-specific metadata be created handled for an outer join view?

If you're just trying to do a batched update to attach a bunch of tickets to an invoice (i.e. Invoices and tickets live in a 1:M relationship) then you have to get a list of ticket IDs into the stored procedure in the first place.

You could use a table variable as an input parameter if you have a version of SQL Server that supports these. Otherwise, you would have to encode the list of ticket IDs in some way, for example a list serialised as a string. You could encode the list as XML if you don't mind eternal damnation :)

If you're trying to add an Invoice record to a batch of tickets (i.e tickets live in a 1:0-1 relationship with invoices) then you have the same options for input.

In both cases the most efficient way to do the updates is likely to be creating a table variable with your list of invoice changes and then doing an update/insert operation joining the table variable against the main tables.

OTOH I can't think of anything more elegant than that.

If your problem is passing the list in across from the client via then you can pass in table-valued parameters in recent versions of ADO.NET.  In older versions you are basically in the job of scrubbing and escaping the identifiers client side and passing the list across serialised as a string or XML (supported from .NET 2.0 and SQL Server 2005 onwards).

If you're using anything else (e.g. PHP) then you're probably going to have to drop down to the lowest common denominator - scrub the list of tickets and encode them as a string. Maybe the stored procedure can do some validation, such as checking the tickets all belong to the correct user.


Query Store "Overall Resource Consumption" details

The Query Store GUI uses this T-SQL:

exec sp_executesql N'WITH DateGenerator AS
(
SELECT CAST(@interval_start_time AS DATETIME) DatePlaceHolder
UNION ALL
SELECT  DATEADD(d, 1, DatePlaceHolder)
FROM    DateGenerator
WHERE   DATEADD(d, 1, DatePlaceHolder) < @interval_end_time
),
UnionAll AS
(
SELECT
    CONVERT(float, SUM(rs.count_executions)) as total_count_executions,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) as total_duration,
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) as total_cpu_time,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) as total_logical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) as total_logical_io_writes,
    ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) as total_physical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) as total_clr_time,
    ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) as total_dop,
    ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,
    ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) as total_rowcount,
    DATEADD(d, ((DATEDIFF(d, 0, rs.last_execution_time))),0 ) as bucket_start,
    DATEADD(d, (1 + (DATEDIFF(d, 0, rs.last_execution_time))), 0) as bucket_end
FROM sys.query_store_runtime_stats rs
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY DATEDIFF(d, 0, rs.last_execution_time)
)
SELECT
    total_count_executions,
    total_duration,
    total_cpu_time,
    total_logical_io_reads,
    total_logical_io_writes,
    total_physical_io_reads,
    total_clr_time,
    total_dop,
    total_query_max_used_memory,
    total_rowcount,
    SWITCHOFFSET(bucket_start, DATEPART(tz, @interval_start_time)) , SWITCHOFFSET(bucket_end, DATEPART(tz, @interval_start_time))
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY bucket_start ORDER BY bucket_start, total_duration DESC) AS RowNumber
FROM UnionAll
) as UnionAllResults
WHERE UnionAllResults.RowNumber = 1
OPTION (MAXRECURSION 0)',N'@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@interval_start_time='2018-02-19 10:41:32.4646188 -06:00',@interval_end_time='2018-03-19 11:41:32.4646188 -05:00'

The results look like:

╔════════════════════════╦════════════════╦════════════════╦════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════╦═══════════╦═════════════════════════════╦════════════════╦════════════════════════════════╦════════════════════════════════╗
║ total_count_executions ║ total_duration ║ total_cpu_time ║ total_logical_io_reads ║ total_logical_io_writes ║ total_physical_io_reads ║ total_clr_time ║ total_dop ║ total_query_max_used_memory ║ total_rowcount ║        (No column name)        ║        (No column name)        ║
╠════════════════════════╬════════════════╬════════════════╬════════════════════════╬═════════════════════════╬═════════════════════════╬════════════════╬═══════════╬═════════════════════════════╬════════════════╬════════════════════════════════╬════════════════════════════════╣
║                      2 ║           3.25 ║           3.25 ║                      0 ║                       0 ║                       0 ║              0 ║         2 ║                           0 ║              1 ║ 2018-03-18 18:00:00.000 -06:00 ║ 2018-03-19 18:00:00.000 -06:00 ║
╚════════════════════════╩════════════════╩════════════════╩════════════════════════╩═════════════════════════╩═════════════════════════╩════════════════╩═══════════╩═════════════════════════════╩════════════════╩════════════════════════════════╩════════════════════════════════╝

I saw that using a T-SQL statement trace against the server while simultaneous opening the GUI for "Overall Resource Consumption" on my local SQL Server 2016 instance.

I re-factored that above query down a bit, to:

DECLARE @interval_start_time datetimeoffset(7);
DECLARE @interval_end_time datetimeoffset(7);
SET @interval_start_time = '2018-02-19 10:41:32.4646188 -06:00';
SET @interval_end_time = '2018-03-19 11:41:32.4646188 -05:00';

SELECT
      total_count_executions = CONVERT(float, SUM(rs.count_executions))
    , total_duration = ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2)
    , total_cpu_time = ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2)
    , total_logical_io_reads = ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2)
    , total_logical_io_writes = ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2)
    , total_physical_io_reads = ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2)
    , total_clr_time = ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2)
    , total_dop = ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0)
    , total_query_max_used_memory = ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2)
    , total_rowcount = ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0)
    , bucket_start = DATEADD(d, ((DATEDIFF(DAY, 0, rs.last_execution_time))),0 )
    , bucket_end = DATEADD(d, (1 + (DATEDIFF(DAY, 0, rs.last_execution_time))), 0)
FROM sys.query_store_runtime_stats rs
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY DATEDIFF(DAY, 0, rs.last_execution_time)
ORDER BY DATEDIFF(DAY, 0, rs.last_execution_time);

That returns a row for each day between the start end end dates provided.


Find over-payment amount using a three-table join

There is no straight-forward way, just linking these tables, to give the result.

The code below gives you the answers, with the bonus that, letting @ColId null, every Client will be processed.

You may think: it is computationally expensive!

Yes, it may seems so. BUT, check the complementary comments after the code snippet, plz.

DECLARE @CliNo INT

SET @CliNo = 1

;

WITH pay AS
(
    SELECT cli.[Client No], (pay.Amount - inv.Amount) AS DeltaPayment
    FROM
        dbo.[Client TBL] cli
            INNER JOIN(
                SELECT inv.[Client No], SUM(inv.Amount) AS Amount
                FROM dbo.[Invoice TBL] inv
                GROUP BY inv.[Client No]
            ) inv
            ON inv.[Client No] = cli.[Client No]
            --
            INNER JOIN(
                SELECT pay.[Client No], SUM(pay.Amount) AS Amount
                FROM dbo.[Payment TBL] pay
                GROUP BY pay.[Client No]
            ) pay
            ON pay.[Client No] = cli.[Client No]
    WHERE cli.[Client No] = @CliNo OR @CliNo IS NULL
)
SELECT
    pay.[Client No],
    CASE WHEN pay.DeltaPayment > 0 THEN pay.DeltaPayment
                                   ELSE 0
    END AS OverPayment
FROM pay

At first sight, it seems we are asking the SQL Server to SUM every invoice, SUM every payments, and only then computes any overpayment.

But, SQL Server 2012 is a hell of a smart beast.

Check the actual execution plan:

The SQL Server query engine is filtering the payment and invoice records BEFORE sumarizing them.

Hence, no performance penalty, although this is not exactly what you asked for - a simple query with tree direct INNER JOINS.

The complete code for this POC follows:

CREATE TABLE [Client TBL]([Client No] INT NOT NULL PRIMARY KEY)
CREATE TABLE [Invoice TBL]([Invoice No] INT NOT NULL PRIMARY KEY, Amount MONEY, [Client No] INT NOT NULL FOREIGN KEY ([Client No]) REFERENCES [Client TBL])
CREATE TABLE [Payment TBL]([Payment No] INT NOT NULL PRIMARY KEY, Amount MONEY, [Client No] INT NOT NULL FOREIGN KEY ([Client No]) REFERENCES [Client TBL])
go

INSERT INTO dbo.[Client TBL] VALUES(1), (2)
INSERT INTO dbo.[Invoice TBL] VALUES(1, 200, 1), (2, 100, 2), (3, 500, 1), (5, 300, 1), (6, 200, 2), (7, 500, 1)
INSERT INTO dbo.[Payment TBL] VALUES(1, 1000, 1), (2, 100, 2), (4, 1000, 1)
GO

DECLARE @CliNo INT

SET @CliNo = 1

;

WITH pay AS
(
    SELECT cli.[Client No], (pay.Amount - inv.Amount) AS DeltaPayment
    FROM
        dbo.[Client TBL] cli
            INNER JOIN(
                SELECT inv.[Client No], SUM(inv.Amount) AS Amount
                FROM dbo.[Invoice TBL] inv
                GROUP BY inv.[Client No]
            ) inv
            ON inv.[Client No] = cli.[Client No]
            --
            INNER JOIN(
                SELECT pay.[Client No], SUM(pay.Amount) AS Amount
                FROM dbo.[Payment TBL] pay
                GROUP BY pay.[Client No]
            ) pay
            ON pay.[Client No] = cli.[Client No]
    WHERE cli.[Client No] = @CliNo
)
SELECT
    pay.[Client No],
    CASE WHEN pay.DeltaPayment > 0 THEN pay.DeltaPayment
                                   ELSE 0
    END AS OverPayment
FROM pay
go


Database design: how to handle the "archive" problem?

It's not clear to me if these requirements are for auditing purposes or just simple historical reference such as with CRM and shopping carts.

Either way, consider have an main and main_archive table for each major area where this is required.  "Main" will only have current / active entries whereas "main_archive" will have a copy of everything that ever goes into main.  Insert / update into main_archive can be a trigger from insert / update into main.  Deletes against main_archive can then run across a longer period of time, if ever.

For the referential issues such as Cust X bought Product Y, the easiest way to solve your referential concern of cust_archive -> product_archive is to never delete entries from product_archive.  Generally, churn should be much lower in that table so size shouldn't be too bad of a concern.

HTH.


What happens to queries that are executing when SSMS crashes?

When SSMS closes for whatever reason, your session will end and any uncommitted transactions will be rolled back (and yup, database restores will stop).

BUT, if SSMS crashes and gives you the 'This Program Has Terminated Unexpectedly' or whatever the error message is, don't touch anything.  At this point you might find that your session is still active and will be until you click 'Close' or 'Restart'.

I've had this a few times but if you open up another instance of SSMS and run sp_who2 you'll be able to see if your transaction is still active.


determine redo log size, number of redo log files

The recommendation is to have log switches occur a few times an hour. If you have a 100 log switches an hour with 50MB redo logs, you need e.g 1GB redo logs for 5 log switches/hour.
100*50 MB redo/hour for a 6.5GB database is unusually much though.

You can add the new logs, e.g:

ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 SIZE 1G;

Then force log switches until the new logs are used (CURRENT status) and old logs become ACTIVE/INACTIVE by:

ALTER SYSTEM SWITCH LOGFILE;

Then drop the old logs:

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;

If you can't drop a logfile group because it is ACTIVE, do

ALTER SYSTEM CHECKPOINT;

so it becomes INACTIVE and try again.


MySQL vs. MariaDB Binary Logging Difference

There are many differences in default values for system variables between MariaDB 10.3 and MySQL 5.7, so that can explain both some of the performance and binlog size differences. Additionally, MariaDB 10.3 is of course newer code, being the most recent major MariaDB release at the moment, whereas MySQL 5.7 is not the most recent MySQL major version since the release of 8.0.

For example, the default value for binlog_format is different between MySQL 5.7: ROW and MariaDB 10.3: MIXED. MIXED means statement-based in most cases, and statement-based binlog format has the advantage of less data written to log files:

  When updates or deletes affect many
  rows, this results in much less storage space required for log files.
  This also means that taking and restoring from backups can be
  accomplished more quickly.

MariaDB also has log_bin_compress, see Compressing Events to Reduce Size of the Binary Log, though this is disabled by default.


MYSQL Dense Rank Trigger or Update Statement

Switch to MariaDB 10.2 and use their "dense rank" windowing function.

https://mariadb.com/kb/en/mariadb/dense_rank/


can not connect to oracle database with ip address

Listener listens on port 1522, but you are trying to connect port 1521. Update your tnsnames.ora with the correct port number (1522).


The disparate edges tables can be combined into a single table, holding all associations:

create table Connection(ConnectionType varchar(20)) as edge;

insert Connection($from_id, $to_id, ConnectionType)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Bob'),
    'Friend';

insert Connection($from_id, $to_id, ConnectionType)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Chris'),
    'Manager';

This table can be queried to retrieve all associations

select
    b.Name,
    c.ConnectionType
from Person a, Person b, Connection c
where a.Name = 'Alice'
and match(a-(c)->b);

Giving

Name     ConnectionType
-------  --------------------
Bob      Friend
Chris    Manager

Column ConnectionType is optional, but allows for the same level of specificity as the original schema did.

This approach is analagous to an EAV design, and likely to suffer the same performance issues at scale. Filtered indexes will help

create nonclustered index IX_Friend on Connection($from_id, $to_id)
where ConnectionType = 'Friend';


SQL Server Agent maintains thousands of sleeping connections

KB 3034297 - Cumulative Update 6 for SQL Server 2014 describes the issue that you are encountering and is fixed in SQL Server 2014 CU6

  When you turn on SQL Server Managed Backup in Microsoft SQL Server 2014. When you let SQL Server and SQL Server Agent run for several days, there are many connections with the program name SQLAgent - Temporary Worker. They are created by "sqlagent.exe," and cannot be closed.


Start Windows Service If Replication Fails

As a result of the replication job failure, you could respond with an xp_cmdshell statement to perform a Windows function: http://msdn.microsoft.com/en-us/library/ms175046.aspx

xp_cmdshell needs to be enabled in your Surface Area config: http://msdn.microsoft.com/en-us/library/ms190693.aspx


Your MySQL Instance is using binlog_format as ROW and not STATEMENT.

You can check with one of the following:

SELECT @global.binlog_format;
#
SHOW GLOBAL VARIABLES LIKE 'binlog_format';
#
SELECT variable_value FROM information_schema.global variables
WHERE variable_name = 'binlgo_format';

This should be OK for you because it keeps the table layout a secret, known only to your developers, DBAs, and Project Managers. You will now be able to see the SQL with your current binary logs. I just mentioned this about 3 weeks ago : Show sql statements from mysql binlog (GTID)

You could switch binlog_format to MIXED and see if it exposes the actual SQL.


Sure-- there is even a chapter in the Upgrade Guide on using DataPump to move the data from the old database to the new database.


How to install SQL Server Express 2005 with Full text search

Full Text Search features are available in

Microsoft SQL Server 2005 Express Edition with Advanced Services
Microsoft® SQL Server® 2008 Express with Advanced Services

Installation and configuration are pretty simple, hope you have already installed express edition.

Give it a try!

UPDATE: 2011 - MARCH - 08

You will not be able manage Full Text Catalogs via SSMS.

  SQL Server Management Studio Express

  You cannot update the full-text catalog by using SSMSE. You can use sqlcmd utility or Windows Scheduler to update the full-text catalog.


As sp_BlitzErik noted in his comment, he has an excellent post on this topic.

However, his query was something of a special case, and some of the things he did only worked because he wanted a COUNT. As you only present an expression from your WHERE clause, we can't make any particular assumptions about the rest of your query. I do recommend looking Erik's post over, in any case. It's very good.

Given what you've presented, I come up with the following:

case
when A.TD IS NULL and B.TD IS NULL THEN S.TE
when A.TD IS NULL and '1900-01-01' > B.TD THEN '1900-01-01'
when A.TD IS NULL and '1900-01-01' < B.TD THEN B.TD
when A.TD IS NULL and '1900-01-01' = B.TD THEN S.TE
when A.TD > '1900-01-01' and B.TD IS NULL THEN A.TD
when A.TD < '1900-01-01' and B.TD IS NULL THEN '1900-01-01'
when A.TD = '1900-01-01' and B.TD IS NULL THEN S.TE
when A.TD > B.TD then A.TD
when A.TD < B.TD then B.TD
when A.TD = B.TD then S.TE end as DZ

Basically, ISNULL(A, n) = m can be broken down into two parts:

(   (A IS NULL and n = m)
 OR A = m
)

As you know, SQL technically uses trinary logic - an expression can be TRUE, FALSE, or NULL. For two expressions joined by OR to evaluate to TRUE, one of them must be true - the other can be either FALSE or NULL, it doesn't matter.

So, if A is not NULL, the first expression will always be FALSE, and the result of the second expression (which will not be NULL as long as m is not NULL) will determine the value of the pair.

And, if A is NULL, the second expression will be NULL, and the first expression (which will not be NULL as long as neither n nor M is NULL) determines the value of the pair.

Since we're looking at multiple comparisons on two expressions, things get a bit more complicated, but not necessarily unmanageably so.

Note that you may be able to simplify the expression. If your dates can never be lower than the hardcoded date, then you don't need to see if A.TD or B.TD is less than that date.

You may also want to rearrange the various WHEN clauses. When it doesn't hurt maintainability, I'm inclined to put the clause that represents the most likely occurrence first; in your cases, if NULLs are rare, that may be the last three checks. I'm not sure if query optimizer perform the checks in the order they're presented, but (if they do) it's a good idea to minimize the number of checks that have to be done before the right one is hit.

A final note: you didn't specify what DBMS you're using, and it's possible that something I've presented wouldn't work globally. I mostly use MS SQL Server, and this would work there.


We don't have the full picture about the table or the queries that access the table or the load in the database/server but from what we know:

  Background info - I have a table that stores Job information with a unique JobID (PK) and a ClientID (one client can have many jobs), as well as bunch of columns that are heaped data.

OK, so the table has a JobID and a PRIMARY KEY constraint (and therefore an index on it).
There is a ClientID which is an (explicit or implicit) foreign key to Client.
We don't know if there is a FOREIGN KEY constraint defined.   Is a FOREIGN KEY defined?
We don't know the other columns of the table and their sizes. What are the columns, types and sizes?
We don't know if the index on (JobID) is clustered or not (the "heaped data" suggests not but we can't be sure). Is the table clustered or a heap?
It would better to provide the whole CREATE TABLE statement with all the indexes.

  This table only has 35K rows in it, but it is heavily used and updated every few seconds.

OK, but "heavily" is not a number. 100 queries per second or 10K queries per second?
A row insert/update/delete every few seconds or all rows updated every few seconds?
The load on the table and database matters:

What queries are run?
Is the load OLTP or OLAP?
What is the version and edition of SQL Server?
What are the server specs (RAM, CPUs, HDD/SSD)?

  The current index is on the PK which is a unique value. I do not know much about index tuning, but if I remove this index and ...?

No, do not go and blindly remove indexes, especially when the table has so few (i.e. just one).
You could add indexes on columns used in joins and WHERE conditions and expect (usually) no problems and likely improvements.

  ... and create a nonclustered index for the JobID + ClientID, ...?

The specific suggested index on (JobID, ClientID) is likely useless, considering there is already a unique index on (JobID).
Adding an index on (ClientID) on the other hand will likely help some queries (that have joins to Client). Since ClientID is an FK, it is also likely that you have such queries.
Still, adding an index blindly may give you no performance gain (and even make things worse), so test, test, test, before and after the index.

  .. am I on the right track to reducing the aggressiveness of the index? Or am I making it worse?

I'm not sure what "index aggresiveness" means but see the points above about potential actions.
If you really want to see significant improvements in the efficiency of queries, you'll need to do some proper analysis of the structure of the table(s) and the queries that run most often and then testing of different indexing strategies.


Is updating many columns in a MERGE statement bad for performance?

My first thought was: no, the whole row will get re-written whether you update one column or many, in both the log and data files. This should be true for simple un-indexed columns at least but there could be complications.

It may make a difference if the "extra" columns are indexed: will the engine be bright enough to know it doesn't need to make changes for what is effectively no change? Of course if the update results in the row moving to a different page because it is variable length and the update causes it to no longer fit forcing a page split, then all the indexes will need updating anyway. Another complication might be your own code in triggers if your database engine supports identifying which columns were altered (UPDATE() and COLUMNS_UPDATED() in SQL Server): if your code is dependent on that does the engine identify which columns really changed or just list those that were written to?

This will all be dependent on which database engine you are using, so you will need to specify that in your question text and/or tags to get less generic advice.


Moving DataFile to Another Location ORACLE

The SQL statement required to list the database files locations would be something like:
set lines 230
set pages 50
column file_name format a50
select ddf.FILE_NAME, ddf.TABLESPACE_NAME, ddf.STATUS, ddf.ONLINE_STATUS from dba_data_files ddf;

This will return something like the following:
|              FILE_NAME                  | TABLESPACE_NAME |  STATUS   | ONLINE_STATUS |
|-----------------------------------------|-----------------|-----------|---------------|
| /app/oracle/oradata/ENOVIA/SYSTEM01.DBF | SYSTEM          | AVAILABLE | SYSTEM        |
| /app/oracle/oradata/ENOVIA/SYSAUX01.DBF | SYSAUX          | AVAILABLE | ONLINE        |
| /app/oracle/oradata/ENOVIA/UNDOTS01.DBF | UNDOTS          | AVAILABLE | ONLINE        |
| /app/oracle/oradata/ENOVIA/USERS01.DBF  | USERS           | AVAILABLE | ONLINE        |

Once you have found the contradicting entry, you can modify it with the following SQL statement:
ALTER DATABASE
    RENAME FILE '/app/oracle/oradata/ENOVIA/system01.dbf'
             TO '/home/oradata/ENOVIA/system01.dbf'

Reference: Renaming and Relocating Datafiles (Oracle | Docs)

Note:
To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

Good luck.


How to check my MongoDB throughput and perfomance limit?

You can get your queries per second (and many other stats) using mongostat as well as many graphical tools (from MongoDB themselves and third parties). As for whether you have reached your maximum, that will depend on what you consider acceptable performance. If you are OK with the delay involved with fetching data from disk, then that would be vastly different than if you only find in-memory queries to be acceptable. It's basically impossible to say what your maximum is without (a lot of) context and data over time.


SQL Server 2008 DB Performance on single disk

First off, gather a performance baseline in order to know what the situation is now. Keep monitoring the performance to see if there is a trend for degeneration. If the performance seems to become an issue, there are a few things to do.

Identify the root cause for lack of performance. Is the server expiring memory pressure, IO waits or high CPU usage? The word on the street is to look at Brent's performance tuning pages for a quick start.

Don't overlook good DB design. Check the DB against missing indexes, outdated statistics and poorly written queries. Any of these issues can eat up all the iron you got and then some.

Edit:

Using multiple file groups can - in certain situations - increase performance. Even Microsoft's documentation claims performance advantages. Alas, in your scenario they do not, as you do not have extra disks.

The idea of file groups is to divide database contents to multiple files. File groups make it possible to locate hot tables to high performance storage. This, requires advanced storage configuration your system is lacking. Partitioning tables can take advantage of storage this way too.

Administration tasks like backup/restore can be more manageable with filegroups. Usually a VLDB (very large database) is needed to get such an advantage. Another a classic example is handling read-only data and separating it to its own filegroup. As the data doesn't change, it can be backed up more seldom than the rest of the database.

If you deploy multiple file groups to single a disk, there is no performance bonus. The reason is that you still got a single disk, a single spindle, single set of read/write heads. In order to access data, the disk has to move heads to proper a location. They can't be at two locations at the same time, so extra file group doesn't increase performance.


Having two data folder in my postgresql setup- Postgresql

On a terminal, type

systemctl cat 'postgresql*'

That will show you where the PGDATA environment variable is pointing to when you start the service normally via systemd, and all the parameters used for starting and stopping the service.


Recursive Query in MySQL using stored proceedure and CURSOR

So the three questions I'm hoping someone can answer are:

1.Should I be using a CURSOR here or is there an alternative that will get the same recursive result?

No, you should not. The CURSOR keep being opened over and over. Thinking of the overhead make me cringe. Personally, I stay away from CURSORs.

2.How can I get the results back in a single result set so that it can be used in the same fashion as a subselect?
3.What is the proper way of using the results from the CALL because I can't at least as far as I've tried get the sample SELECT statement above to work? I believe this is because I can't use CALL inline but I'm not sure.

I would like to bail you out of this by suggesting something I learned from my college days when learning about data structures. You need to perform tree traversal using a queue. This allows you to start at a root and express all descendants of a tree.
The algorithm goes like this

STEP 01 : Start with an empty queue
STEP 02 : Dequeue Node From the Front of the Queue
STEP 03 : Enqueue All Children of the Latest Node
STEP 04 : Process Info From the Latest Node
STEP 05 : If the Queue is Not Empty, Go Back to STEP 02
STEP 06 : All Done

This allows you to traverse a recursive structure without using Programmatic Recursion. At this point, you are probably asking: How can I traverse a tree structure without recursion?
I wrote a post about how to script three Stored Procedures that can using a loop in a single CALL that will traverse a table with nodes and its parent in a table:

GetParentIDByID
GetAncestry
GetFamilyTree

The post is Find highest level of a hierarchical field: with vs without CTEs (Oct 24, 2011). It contains the Stored Procedures already written that will traverse the following table structure:
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| parent_id  | int(11)      | YES  |     | NULL    |                |
| name       | varchar(255) | YES  |     | NULL    |                |
| notes      | text         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Please read the code carefully and apply the principles.
Give it a Try !!!


How to change pg_largeobject data from psql? (posgresql-9.1)

Note that PostgreSQL version 9.1 has been obsoleted in 2016, it should no longer be used.

Anyway, there is a set of large object server-side functions in 9.1 that should cover your needs. They tend to expose a large object as if it was a file, providing file-like primitives.

For instance, here's a plpgsql function that replaces the value inside a large object using these primitives. It should work with 9.1 or newer:

CREATE FUNCTION lo_replace(_loid oid, _str bytea) RETURNS void
AS $$
declare
 fd integer;
begin
 fd = lo_open(_loid, 393216); -- INV_READ|INV_WRITE
 if (fd < 0) then
   raise exception 'Failed to open large object %', _loid;
 end if;

 if (lo_truncate(fd, 0) != 0) then
   raise exception 'Failed to truncate large object %', _loid;
 end if;

 if (lowrite(fd, _str) != octet_length(_str)) then
   raise exception 'Failed to write to large object %', _loid;
 end if;

 if (lo_close(fd) != 0) then
   raise exception 'Failed to close large object %', _loid;
 end if;
end;
$$ LANGUAGE plpgsql;


Wrong InnoDB table status (Size/Rows) after updating from MySQL 5.7 to 8

I've got suggestion to make Check and after it Analyze, and it worked for me! Now size and other table service information updated. I will try to find why its not updated automatically and post here.


I Need Help Setting Up Roles and Passwords in PostgreSQL

Basic Role and User Management

By default, PostgreSQL stores encrypted passwords:

create user u_one with password 'password';

select *
from pg_authid
where rolname = 'u_one';

-[ RECORD 1 ]--+------------------------------------
rolname        | u_one
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcatupdate   | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | -1
rolpassword    | md54e83b97ec0bf59917d9d2689a961e390
rolvaliduntil  |

for some testing:

create table tt (
    a serial primary key,
    v varchar
);

insert into tt (v)
values ('one'), ('two');

the goal is to create one user who has read-only access and one user who has all privileges. It is better to create roles that have the privileges defined and then attach users to each of those roles:

create role readonly;
create role readwrite;

grant select on all tables in schema public to readonly;
grant insert, update, delete on table tt to readwrite;

In my example, I would like u_one to have read-only access. This user does not yet:

select *
from tt;
ERROR:  permission denied for relation tt

I'm going to attach u_one to read-only:

grant readonly to u_one;

And now u_one can select from table tt.

Now I'll create a u_two who is readwrite:

create user u_two with password 'password';
grant readwrite to u_two;

you probably meant to allow u_two select privileges as well:

grant readonly to u_two;

When you attempt to insert into tt using u_two:

insert into tt (v) values ('three');
ERROR:  permission denied for sequence tt_a_seq

You need to add insert, update, and/or delete privileges to the users who are going to modify this sequence:

grant usage on tt_a_seq to readwrite;

Notice how I only added this to the role, and not the user: u_two is  now able to insert a new record.

Using this role / user strategy will give you considerable flexibility by reducing duplicate permissions and allowing you to quickly create and set up new users.

The HBA config file:

By default, all users will be able to login from localhost, and as long as that is true, you won't have to make changes to pg_hba.conf. You will need to make changes if you want a user to be able to access the database from another ip-address (remote computer).

If you are going to grant remote access:

DATABASE: host --
USER: the username
ADDRESS: the ip address the user is connecting from
(I doubt this will work with roles since roles do not have passwords,
 though I've never tried either)
(if you want the user to be able to connect from any IP, then use
0.0.0.0/0)
METHOD: md5

PostgreSQL docs on creating roles and user syntax.
http://www.postgresql.org/docs/current/static/sql-grant.html

Granting privileges to sequences:
https://stackoverflow.com/questions/9325017/error-permission-denied-for-sequence-cities-id-seq-using-postgres

PostgreSQL docs on hba:
http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html


Access Db `Long Text` Unicode Byte Corruption

It turned out to be a \n character that was imported into the record. Did a query on the data and removed it and updated the record. Not having issues anymore.


Teach MySQL cli autocomplete to only list table names, not field names?

Interesting question! This is one reason I don't use the autocomplete.

It's called auto-rehash if you want to check out the documentation on it.

But, no, looking at the code, I don't believe you can configure this.


As per your system details the readahead is set to 16K. But as per MongoDB BOL  for the WiredTiger storage engine. Set the readahead setting to 0 regardless of storage media type (spinning, SSD, etc.).

Setting a higher readahead benefits sequential I/O operations. However, since MongoDB disk access patterns are generally random, setting a higher readahead provides limited benefit or performance degradation. As such, for most workloads, a readahead of 0 provides optimal MongoDB performance.
In general, set the readahead setting to 0 unless testing shows a measurable, repeatable, and reliable benefit in a higher readahead value. MongoDB Professional Support can provide advice and guidance on non-zero readahead configurations.

As MongoDB documentation for the WiredTiger, consider the following recommendations on Linux environment:

Turn off atime for the storage volume containing the database files.
Set the file descriptor limit, -n, and the user process limit
(ulimit), -u, above 20,000, according to the suggestions in the
ulimit reference. A low ulimit will affect MongoDB when under heavy
use and can produce errors and lead to failed connections to MongoDB
processes and loss of service.
Disable Transparent Huge Pages. MongoDB performs better with normal
(4096 bytes) virtual memory pages. see  Transparent Huge Pages Settings.
Disable NUMA in your BIOS. If that is not possible, see MongoDB on NUMA Hardware.


Oracle Text: query scoring with CTXCAT index?

The score can only be accessed with a CONTENT index using a query like this

SELECT ID, TEXT,SCORE(1) FROM t WHERE CONTAINS(text, 'my query',1) > 0
ORDER BY SCORE(99) DESC;

By Oracle 11 everything you can do with a CTXCAT index you can do more with a CONTENT index.  Why not use that?

Edit: The op asks if CONTENT indexes must be synced.  Yes, this is correct.  I find the time and load factor to do this is entirely acceptable for smaller records sets.  Your mileage may vary based on record size.
Further reference:

Oracle how to
Text index how to


Report transition from Access to Crystal (multiple queries)

Given that you have a SQL Server backend you have a few options depending how much freedom you have to add objects to the application db or on the server.

If you are not permitted to add objects to the native schema i would request a small report db on the same server where you can create views or stored procs that reference the source database schema using 3 part names.

If you can re-write the query and deploy it as a view that might be a good option.  If the query is too complex to be rewritten as a single query then you could reproduce the behavior of "make tables" with temorary #tables and deploy it as a stored procedure.

If you arent permitted to create objects on the server at all i would rewrite the query using #tables and embed the whole thing in the report query.

Views and SP's get better performance as the number of users/hits increase compared to embedded queries.


Why and When the pg_stat_activity's most of the columns are null?

That must be because you don't have permission to see these user's activities.

Statements can contain sensitive data, so they are only visible to superusers by default.

Some rows in pg_stat_activity also contain many NULLs because they belong to background processes.


Child, Parent and Grand-Parent relationship where a child may not necessary have a parent or a grand parent

I would add hostel_id to inmate, and leave Room-id empty till he arrives and the room-number is chosen.
So you could also, allow inmate to book a specific room with sea view for example
And you could check if any rooms for a date are available, for example hostel 1 has 10 room and 10 users have chosen the hostel already, you ca tell him  that is not possible and give additional information when a room is free again or a hostel nearby who has vacancy.
table hostel(id, name)
table inmate(id,hostel_id, room_id, name, etc)
table room(id, hostel_id, room_no)


I guess you can do the following:
1. In your new table buynsell, add a column , old_id
2. Reinsert data from the old table as you said, skipping auto_increment field, and storing old_buynsell.id as old_id in your new table.
3. Import data to new replies table
4. Update bns_id column in replies :

UPDATE replies a
INNER JOIN (select id, old_id FROM buynsell) b ON (b.old_id = a.bns_id)
SET a.bns_id=b.id

5 Drop old_id column, you don't need it anymore.


What is meant by the RMAN Duplicate "noncurrent point in time"?

It's the moment of the last redolog archival, before some point in time during the RMAN duplicate command. You can test this easily, if you either manually provoke redolog switches (and thus subsequent archivals), or even better: write some script to provoke very frequent and unnecessary redolog switches. In which you perform some insert/update/delete, to further track things down.


Execution of the variable @sqlStr is done under a separate context than that in which you are currently running. Therefore the variables that you have declared in your session will not be available.

Try this instead:

declare @params = N'@gID smallint';
exec sp_executesql @sqlStr, @params, @gID = @gID;

Documentation for sp_executesql can be found here:

https://msdn.microsoft.com/en-us/library/ms188001.aspx

Full code example:

declare @dbName varchar (50) = 'student';
declare @fldName varchar (50) = 'stID';
declare @gID smallint = 4;
declare @params nvarchar(max) = N'@gID smallint';
declare @sqlstr nvarchar(max) =
        N'select max([' + @fldName +'])
            from [dbo].['+ @dbName +']
           where [dbo].['+ @dbName +'].[gID ] = @gID';
exec sp_executesql @sqlStr, @params, @gID = @gID;


Restoring SQL Server database on another box

What Bob said.

Couple of ways to get round this.

Firstly, you could copy the files from the FTP server to your test server and then use xp_cmdshell and the DIR command to read in the contents of that folder (full, diff and log backups) and order them accordingly (you need the date/time in the filename to achieve this).  Once you have this information, you can dynamically build your restore statements.

I did this once, but, I have xp_cmdshell disabled in my environment here, so it's not an option.

The way I currently do it.  I build the restore statements based on the metadata in msdb and export that out to a text file which is stored on the FTP server.  I simply read that text file on along with my backup files on my test server and away I go.

My solution performs a point in time on a daily basis to my test box.  What it can't guarantee is if the subsequent log backups are valid and I can restore from those.

I also perform manual restores to my test box (another instance) just to keep myself fresh when it comes to a disaster - I vary the restores too.


How to finds users that can access a particular table?

If you are sys user then you can use dba_tab_privs view as shown below.

SQL>Conn / as sysdba
SQL>select grantee, table_name, privilege from dba_tab_privs where table_name='Table01';

Update:

As Justin Cave and mustaccio said, its a bit complex to get the accurate result from general dba_ or role_ views.

Though you can query role_tab_privs to get all the roles and its object privileges.

SQL> select grantee,  privilege from dba_tab_privs where table_name='EMPLOYEES' and owner='HR';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
SCOTT                          SELECT
OE                             SELECT
OE                             REFERENCES
JAY                            UPDATE
R1                             SELECT

In the above result Grantee R1 is a role which has SELECT object privilege on EMPLOYEES table.

Here is a useful post similar to this. oracle - list users with access to certain tables


Query to delete all rows which are not referenced in any other tables

I don't think you can write a single query, but you can write a query to get the list of tables that refer to the images table, and then you can do a second task which deletes the rows from images which are not referred to in any of the tables in the list.

List of tables that refer to the images table:

SELECT rf.TABLE_NAME,
kcu.COLUMN_NAME,
kcu.REFERENCED_COLUMN_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS rf
  INNER JOIN information_schema.KEY_COLUMN_USAGE kcu ON
    kcu.CONSTRAINT_NAME = rf.CONSTRAINT_NAME AND
    kcu.CONSTRAINT_SCHEMA=rf.CONSTRAINT_SCHEMA
WHERE rf.REFERENCED_TABLE_NAME = 'images' AND
  rf.CONSTRAINT_SCHEMA = 'your_database';

Delete all rows in images not referred to by those tables:

For this part, perhaps it's better to do a loop and look at one and one row in images. Assume '$row_i_val' is the PK column value of the current row you're looking at:

SELECT sum(q.c) FROM
(
  SELECT count(*) as c FROM $table1 WHERE $table1_col = $row_i_val
  UNION ALL
  SELECT count(*) as c FROM $table2 WHERE $table2_col = $row_i_val
  UNION ALL
  SELECT count(*) as c FROM $table3 WHERE $table3_col = $row_i_val
  UNION ALL ...
) q

If this sum is 0, then you can delete it:

DELETE FROM images WHERE pk_col = $row_i_val;


Why does SET ROWCOUNT not reset after GO?

Most (all?) set statements take effect at the session level...

  provides several SET statements that change the current session handling

... with the normal set of caveats.

See SET Statements (Transact-SQL) for more details.

Quite a few of those set statements listed @ that link are obviously session-level settings, eg:

set dateformat
set fips_flagger
set indentity_insert
set statistics time/io
set nocount
set transaction isolation level

And by session-level I mean that they remain in place across batches/go statements.


What is the correct SQL for using where ID in List and the items are with AND operator

I think what you are trying to do is relational division. Here is the best explanation I could find

https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

Honestly I do not fully understand this myself yet, but I believe this should work (using an intermediate table):

CREATE TABLE tblCountry (
    countryId int,
    countryName nvarchar(100)
)

INSERT INTO tblCountry
    SELECT 1, 'Germany' UNION
    SELECT 2, 'United States' UNION
    SELECT 3, 'France'

CREATE TABLE tblStandards (
    standardId int,
    standardName nvarchar(100)
)

INSERT INTO tblStandards
    SELECT 1, 'standard_1' UNION
    SELECT 2, 'standard_2' UNION
    SELECT 3, 'standard_3'

CREATE TABLE tblCountryStandards (
    countryId int,
    standardId int
)

INSERT INTO tblCountryStandards
    SELECT 1, 1 UNION
    SELECT 1, 2 UNION
    SELECT 2, 1 UNION
    SELECT 2, 3 UNION
    SELECT 3, 2

SELECT countryName
FROM tblCountry
    INNER JOIN tblCountryStandards ON tblCountry.countryId = tblCountryStandards.countryId
    INNER JOIN tblStandards ON tblStandards.standardId = tblCountryStandards.standardId
WHERE tblStandards.standardId IN (1, 2) -- unique standards selected by user
GROUP BY tblCountry.countryName
HAVING COUNT(DISTINCT tblStandards.standardId) = 2 -- number of standards selected by user above

This is based on your example, but I tested more combinations and it works if the commented values are changed.

There are many other ways of doing the division explained in the link provided above - you should use the one that suits your purposes best.


Versioning a dataset in an RDBMS using initials and deltas (xpost from SO)

If the "bunch of work to translate it" is occurring during the transaction, then that is adding to the transaction time.  If you can do bulk processing on the data to translate it before you start dong inserts/updates/deletes that would at least decrease the transaction time providing fewer opportunities for contention.

An alternative would be to maintain a copy of the table and make your changes on the copy and then during a brief outage window swap the table names.  The downside is the added storage and the outage window, but the advantage is zero contention.

Update:

I see a couple difficulties with your solution.  Inserts would still cause contention if there were unique indexes on the table.  Updates would become inserts, so those wouldn't be any more difficult.  Deletes would have to be handled somehow.  You could insert a blank record with the delta number to indicate that the record has been deleted.  I'm concerned that the view necessary to pull this data together for querying would be too slow.

Perhaps someone with MySQL knowledge could suggest something.


12c Grid Agent installation issues - Shell path is incorrect or not defined

try the following doc
EM 12c Agent Push Fails With Shell Path is Incorrect or Not Defined : /bin/bash(SH_PATH),-c(SH_ARGS) (Doc ID 1489437.1)


How to start Oracle database's listener service after a change in the IP address?

To resolve this it is best to delete the listener using the net configuration tools and recreate it.  Stop the listener service before doing this.  Oracle gets very picky when IP addresses change.


How to increase performance with joins to create VIEW from Wordpress wp_usermeta

In this case you don't need to join the table to itself.  If you only query it once then it will go faster.

SELECT user_id,
    MAX(CASE WHEN meta_key='vorname' THEN meta_value ELSE null END) Vorname_M,
    MAX(CASE WHEN meta_key='nachname' THEN meta_value ELSE null END) Nachname_M,
    MAX(CASE WHEN meta_key='adress' THEN meta_value ELSE null END) Adresse_M
FROM wp_usermeta
GROUP BY user_id


complex query joining multiple tables with aggregate functions

Check the result here: http://rextester.com/RQKTP46266

I made it in postgresql, but I think its the same for mysql.

This is the structure used:

create temp table ch (cid int, name text);
create temp table pk (pid int, name text);
create temp table pkch (id int, pid int, cid int);
create temp table pu (id int, userid int, prid int, cat int, expiry date);

insert into ch values (1, 'channel 1');
insert into ch values (2, 'channel 2');
insert into ch values (3, 'channel 3');
insert into ch values (4, 'channel 4');
insert into ch values (5, 'channel 5');

insert into pk values (1, 'pack 1');

insert into pkch values (1, 1, 1);
insert into pkch values (2, 1, 2);
insert into pkch values (3, 1, 3);

insert into pu values (1, 10, 1, 1, '2016-05-01');
insert into pu values (2, 10, 2, 2, '2016-04-01');
insert into pu values (3, 10, 3, 2, '2016-04-01');

First you should expand channels included in purchased packages, and UNION with the single channels.

The next step is easy, select max(expiry) and goup it by channel.

select userid, channel_id, max(expiry)
from
(
    select pu.id, pu.userid, pkch.cid as channel_id, pu.expiry
    from pu
        join pkch on pu.prid = pkch.pid and pu.cat = 1
    where pu.userid = 10
    UNION
    select pu.id, pu.userid, pu.prid as channel_id, expiry
    from pu
    where pu.userid = 10 and pu.cat = 2
) a
group by userid, channel_id


How to only display a variable in PHP inside a MySQL query when it has a content or when a condition is true?

You must do some work in PHP.  You have a set of numbers: find the non empty ones, build a string, and put the string into the IN() clause.  Sample code:

$a = array();
foreach (array($mon, $tues, ...) as $val)
{
    if (! empty($val)) $a[] = $val;   // add the useful values to the array
}
$str = implode(',', $a);       // turn the array into a comma-list
$sql = "SELECT ... IN($str)";  // interpolate into the query

What I failed to do in that code snippet is to validate the input -- a hacker could destroy your system via "SQL Injection":

if (! empty($val)) $a[] = "'" . addslashes($val) . "'";


Datatype for different categories in a database

Speaking from the programming side of the house, I would do a numerical value that is a foreign key to a "type" table. This way if you change the text value, all previous entries automatically update, and adding new types is just a matter of adding them into the table.

This has the added benefit that if you do something like an interface with a dropdown list to select the value, it also automatically updates and there are no coding changes needed to update the list.


Merge two SELECT queries with different WHERE clauses

Treat your two current queries' resultsets as tables and join them:

select
    FirstSet.Region,
    FirstSet.OpenServices,
    FirstSet.DFC,
    SecondSet.ClosedYesterday
from
(
    SELECT U_REGN as 'Region', COUNT(callID) as 'OpenServices',
    SUM(CASE WHEN descrption LIKE '%DFC%' THEN 1 ELSE 0 END) 'DFC'
    FROM OSCL
    WHERE ([status] = - 3)
    GROUP BY U_REGN
    --ORDER BY 'OpenServices' desc
) as FirstSet
inner join
(
    SELECT U_REGN as 'Region',
    COUNT(callID) as 'ClosedYesterday'
    FROM OSCL
    WHERE DATEDIFF(day, closeDate, GETDATE()) = 1
    GROUP BY U_REGN
    --ORDER BY 'ClosedYesterday' desc
) as SecondSet
on FirstSet.Region = SecondSet.Region
order by FirstSet.Region

Not the prettiest bit of SQL I've ever written but hopefully you'll see how it works and understand how to maintain it.

I suspect a better-performing query would be a single SELECT from OSCL, grouped by U_REGN, with each of your three counters as separate SUM(CASE ...) statements akin to what you do currently for DFC.  This will be a single table scan, at most, depending you your indexes & schema.


If you are doing log shipping, I would highly recommend (and the best option I believe) you do have a separate monitoring instance. The monitoring instance could be in Express Edition so you don't have to worry about licensing. You just need to know that after you setup log shipping and you then in the future want to change the monitoring instance or add one, you may need to reconfigure your log shipping. The upside is that you can use the monitoring instance for multiple log shipping and have the view of all the primary/secondary databases status in one location. Once you completed the log shipping setup with monitoring instance, on the monitoring instance, right click on the instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

For reporting side of things, you need to disconnect the users/clients when doing a restore or wait until no one is connected to the database (delay restore). So plan for it. Another thing to be mindful of is that you can't add indexes or statistics on secondary database during standby mode. Doable but personally i would not use log shipping for reporting (only for DR purpose).

Personally, I would consider and recommend transactional replication for reporting. This gives you more control on your reporting database as you can create indexes, stats, create objects, etc on the subscriber database.

HTH


Something like this might do the trick (here's the SQLFiddle)

SELECT
Line,
MAX(CASE WHEN `Column` = 'C1' THEN `Value` END) AS C1,
MAX(CASE WHEN `Column` = 'C2' THEN `Value` END) AS C2,
MAX(CASE WHEN `Column` = 'C3' THEN `Value` END) AS C3,
MAX(CASE WHEN `Column` = 'C4' THEN `Value` END) AS C4
FROM YourTable
GROUP BY Line

Like Sascha suggested in their answer, you might want to consider either creating this as a view, or creating a view that looks like Table 2 once you've finished putting the table back to how it was. That should prevent this kind of problem popping up again in the future.


pg_temp is an alias for the current session's temporary schema.

If you do a SET search_path TO pg_temp before running your tests it should all just work (as long as nothing is referencing a schema explicitly).

If you don't want to change your script at all, then set the search_path on the user that the tests log in as:

> ALTER ROLE testuser SET search_path = pg_temp;

Then everything that user creates will be in pg_temp unless explicitly specified.

Here's an example from psql, showing the actual schema (for this connection) that the alias resolves to:

> SET search_path TO pg_temp;
SET
> create table test();
CREATE TABLE
> \dt test
          List of relations
  Schema   | Name | Type  |  Owner
-----------+------+-------+----------
 pg_temp_4 | test | table | postgres
(1 row)

And, as you'd expect, that schema is different for every concurrent connection, and is gone after the connection is closed.

Note that this also works for functions, though you will have to explicitly reference the pg_temp schema when calling them.


Feasible to have thousands of foreign data wrappers in Postgres?

As your comment rightly suspects, that would lead to many connections to the shared database, so that you'd probably need a connection pooler in between.

It might be better to put the data into different schemas in a single database. An alternative would be cascading logical replication to keep consistent copies of the shared data in all databases.


Extended Event definition information for a stopped session

Extended event catalog views fall under both sys.dm_xe* and sys.server_event_* views. The "active" or "running" event sessions have information stored in sys.dm_xe* (also global package metadata) views and overall server defined metadata stored in sys.server_event_* views regardless of state.

Specifically since the question was about information on extended event sessions that aren't currently running, the metadata for that still exists and the basics of it can be found in sys.server_event_sessions catalog view. Joining this with other catalog views such as sys.server_event_session_events, sys.server_event_session_actions, and sys.server_event_session_targets (to name a few) can return most of the information that should be needed.


Why does PostgreSQL require its own user/group for installation?

The reason is that it is wise to run the database processes under a separate account.  At least one advantage of this is that the OS processes as safer in the case of the database server being hacked.  On the other side the database related files are safer from the 'normal' user accounts too - the PostgreSQL cluster (or at least its data) is relatively easy to destroy with a well-aimed single-file deletion.

If you check your passwd file you may find lots more similar users for other services, I imagine on similar grounds.


The easiest method would be to move it by restoring the database, and then just clean out the filestream table in the original.

If you really need to move it, then it would be treated like any other situation where you want to move one table to another database. The additional steps involved would be repeating how you setup filestream on the original database.


how to integrate ola hallengren solution with log shipping?

If you are using Ola's solution, its intelligent to exclude log shipping dbs. See FAQs

Does the SQL Server Maintenance Solution support log shipping?
The SQL Server Maintenance Solution supports log shipping. Full and differential backup, integrity check, and index and statistics maintenance are performed on the primary database. Transaction log backups are not performed on databases that are involved in log shipping.

.

what about the full backups that I take once a day? would that mess up the log shipping? should I do a full backup with copy_only?

A full backup wont break log backup chain. Only an additional NON COPY_ONLY log backup will break the log chain.
If you have folks taking adhoc log backups, you can DENY BACKUP LOG to [user|group]


Connecting to local database on Azure VM

Turns out I have neglected to set up a subnet in Azure portal. I am connected now.


Problem replicating IDENTITY columns from Sybase ASE 15.5 to Sybase ASE 15.5

Assuming your primary/replicate tables were create like such:

create table mytable
(id     int           identity
,name   varchar(100)
,dob    date
... snip ...

And assuming you want to replicate identity values from the primary table to the replicate table, you need to tell SRS that the id column is an identity column, and the way you do this is by designating the id column as an identity in the repdef, like such:

create replication definition mytable_repdef
with primary at ...
with all tables named mytable
(id     identity             <<<=== use 'identity' instead of the actual datatype
,name   varchar(100)
,dob    datetime
... snip ...

By designating the id column as an identity column in the repdef, SRS should now enclose all INSERTs (sent to the replicate database) in a set identity_insert on/off wrapper, and UPDATEs should be wrapped in a set identity_update on/off wrapper.


On Debian or Ubuntu you can also do this on the command line to login as the administrator:

sudo mysql --defaults-file=/etc/mysql/debian.cnf

Then you reset the password as randy has indicated.


MariaDB 10.1.16 on Ubuntu fails to start: WSREP Failed to recover position

There was a bug in galera_recovery.sh script.
https://jira.mariadb.org/browse/MDEV-10396


You can not use ALTER SYSTEM when the instance is not started, but you can not start the instance because of an incorrect parameter value. Create a PFILE (if you do not already have one), edit that, then start the instance.

$ sqlplus / as sysdba
SQL> create pfile from spfile;
SQL> exit
$ vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

Set the value of diagnostic_dest as required, then save the file.

$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup

Note: the directory must exist.

Answer to comment:

  For UNIX or Linux, the platform-specific default location (directory)
  for the SPFILE and text initialization parameter file is:

  $ORACLE_HOME/dbs

  Oracle Database locates your initialization parameter file by
  examining filenames in the following order:

  spfile$ORACLE_SID.ora
  spfile.ora
  init$ORACLE_SID.ora

Or, for a test, you can startup the instance by manually specifying the pfile, for example:

SQL> startup pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora


How to copy blob (binary data) from one DB to DB using a script?

I would use the "Generate Scripts" option in SQL Server Management Studio.

In Object Explorer - highlight the database hosting the source
table.
Right-click menu - Tasks | Generate Scripts
Select specific table object - click Next page
Click Advanced button
Under General Options
    - Change 'Type of data to script' to "Data Only"
Save to file - change folder and filename as required.

I tried a simple test with a table storing a varbinary field.  The output from the Scripting process looked ok:

USE [TestDB]
GO
/****** Object:  Table [dbo].[tjc_blob]    Script Date: 07/04/2017 14:46:23 ******/
INSERT [dbo].[tjc_blob] ([id], [datablob])
VALUES (1, 0x6162636465666768696A6B6C6D6E6F707172737475767778797A)


The safest position you should replicate from is

Log File mysql-bin.000044
Log Position : 25338679

These represent the Latest Binary Log Event that Executed on the Slave. That is where you pick up replication from. Running CHANGE MASTER TO will erase the relay logs and start with brand new relay logs.


GROUP BY needs ORDER BY NULL to avoid filesort

MySQL 5.7 Reference Manual  /  ...  /  SELECT Syntax

  If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL


Should I use a Domain User account to manage SQL Server (or other services)

What's the worst case scenario if e.g. malicious software, running as you, gets concomitant access to the database in question?
The answer to that should probably dictate what you need to do.  Note that if you want to use another domain user for administration, you can run SSMS as another user by shift-right-clicking the shortcut (as opposed to a normal right-click).  There's also a Windows 10 system setting, "show 'run as different user' in start" (under developer settings).


Permissions required to execute UDF Scalar Function inside a trigger

Permissions on indirectly referenced objects are not checked as long as the objects involved have the same owner. This is known as ownership chaining. Users need permission on only the object used directly (table in this case), not on the objects used by the trigger (e.g. function) as long as the ownership chain is unbroken.

One can leverage ownership chains to encapsulate functionality in views, stored procedures, triggers, and functions without granting direct permissions.

See the Ownership Chains topic in the Authorization and Permissions in SQL Server documentation for more information.


Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times

In SQL Server 2014 & up, new cardinality estimation logic was introduced.

From BOL :

  The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.

Recently, we upgrade from SQL server 2012 to SQL server 2014 and got hit by the new Cardinality estimator short coming - queries were timing out, cpu pegging close to 100%.

After much troubleshooting, updating stats, rebuilding indexes, doing query plan analysis, we figured out that changing compatibility level to sql 2012 works well.

Paul White explains - Cardinality Estimation for Multiple Predicates

  Selectivity computation in SQL Server 2014 behaves the same as previous versions (and trace flag 4137 works as before) if the database compatibility level is set lower than 120, or if trace flag 9481 is active.

So my advise would be

For small number of queries revealing the problem, use QUERYTRACEON(9481) hint.
If you dont want to gamble, then just have the trace flag TF9481 as a start-up parameter, so it gets persisted during server restarts.

Note: Enabling TF 9481, you dont need to set the compatibility level of the database to a lower level.

From KB2801413 :

  9481   :Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 2012 version) of the cardinality estimator when creating the query plan.

As a side note, along with proper testing -  you also want to look into TF4199 (Think of it as a master key to turn on every fix for the query optimizer). TF4199 behavior changes with sql server 2016.. TF4199 helped in my environment at lot and is by default ON for all new installations.

In SQL Server 2016, you dont need to turn on the trace flag 9481 .

SELECT  name, value
    FROM  sys.database_scoped_configurations
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
-- if above is having value = 0, then set to ON
ALTER DATABASE
    SCOPED CONFIGURATION
        SET LEGACY_CARDINALITY_ESTIMATION = ON;
go

For Trace Flag 4199, you can just do

ALTER DATABASE
    SCOPED CONFIGURATION
        SET QUERY_OPTIMIZER_HOTFIXES = ON


Why does my SELECT query not return null values?

Generally, avoid NOT IN when NULL values can be involved on either side. The Postgres Wiki suggests as much. And avoid NOT IN (SELECT ...) in any case. The Wiki has the explanation. And also happens to have the perfect answer to your core question:

NOT IN behaves in unexpected ways if there is a null present:

select * from foo where col not in (1,null); -- always returns 0 rows

select * from foo where col not in (select x from bar);   -- returns 0 rows if any value of bar.x is null

This happens because col IN (1,null) returns TRUE if col=1, and NULL
otherwise (i.e. it can never return FALSE). Since NOT (TRUE) is FALSE,
but NOT (NULL) is still NULL, there is no way that NOT (col IN (1,null)) (which is the same thing as col NOT IN (1,null)) can return
TRUE under any circumstances.

Here is a maybe not so obvious solution:
SELECT *
FROM   the_values
WHERE  value IN (1,2,3,4,5,6,10) IS NOT TRUE;

Or:
..
WHERE  value = ANY('{1,2,3,4,5,6,10}') IS NOT TRUE;

It's shorter and typically faster than your "obvious" one.
For long lists, consider switching to a different (faster) technique:

Select rows which are not present in other table


What is branching factor of mysql myisam's b-tree

There is about 8 bytes of overhead per index entry.  So, if the key is typically 6 bytes, and the key_buffer block size is 1KB, and the BTree is 69% full:
1024 * .69 / (8+6)

says there will be about 50 rows.
So, 3 levels of BTree can handle up to 50^3 = 125K rows.
The .69 comes from assuming lots of random inserts -- leading to each block oscillating between 100% full just before a block split and 50% after.
There may be an obscure SELECT against the information_schema to get the exact value for the indexes.  (I know of one for InnoDB.)


Unable to connect to Mysql via JDBC but works fine with Mysql

Which MySQL Version you are using ?

You won't be able to use root user I guess.

Create new application user and use that user for application.

CREATE USER appuser@'%' identified by 'PASSWORD';
Grant SELECT on *.* to appuser@'%';
flush privileges;


Copy from file: ERROR: invalid input syntax for integer: "\N"

Use the NULL parameter. From the documentation:

  NULL

  Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format.

Example

copy my_table from '/my/csv_file.csv' (format csv, null '\N');


If you don't rollback a transaction will the change still be made?

You have to understand what a transaction is - It is a single unit of work. It is ALL or NOTHING (follows ACID properties) and this guarantees the database consistency.

  If I DON'T rollback the transaction, will these changes still be made

The changes will be made only if you commit. SQL server will record all the changes to transaction log and once you commit, they are hardened to the data file.

If you don't commit, then your transaction will remain OPEN indefinitely - which you can see in sys.dm_tran_active_transactions / sys.dm_tran_database_transactions / sys.dm_tran_session_transactions or using DBCC OPENTRAN.

Also, what you have initiated is an explicit transaction, which should be explicitly ended with a COMMIT or ROLLBACK statement.

Also read - Is it a bad practice to always create a transaction?


Where I run mysqldump or pg_dump, Both services (mysql or postgres) freeze?

MySQL answer because I don't know pg_dump.
To allow SQL to continue with mysql during a mysql dump the following is required:

Tables needs to be innodb. Check with show create table {tablename}, change with alter table {tablename} Engine=innodb if it isn't (will cause the table to be unavailable during the change).

Use mysqldump with --single-transaction --master-data=2. Single transaction is the main point to get a consistent snapshot. --master-data enables a point in time recovery if you have binary logs enabled.

The transactional dump will enable the website to continue to read and update date while the mysqldump is occurring. The data in the mysqldump will be the data on the server at the point the backup began.
Note you cannot modify tables during a mysqldump. In MySQL table structure changes aren't transactional.


Improving performance of count where only result > 0 matters

You can add LIMIT 1, but LIMIT is applied after aggregation (so after the count). Adding it in the same query level does nothing useful, since there is only 1 row left to return anyway: the row count. Would be misleading nonsense.

If you want to go that route, add LIMIT in a subquery and count in the outer SELECT, like:

SELECT count(*) FROM (
   SELECT             -- empty SELECT list
   FROM   submission
   JOIN   ...
   WHERE  ...
   LIMIT 1
   ) sub

For this special purpose it's fastest to leave the SELECT list empty. count(*) only counts the existence of rows without regard to any columns.

You can do that with any LIMIT, really. To check whether there are at least N rows. See:

Fast way to discover the row count of a table in PostgreSQL

Checking for N = 1 is just your special case. And while we only care whether any rows exist and not how many, EXISTS is the better, faster tool:

SELECT EXISTS (
   SELECT FROM submission s
   JOIN   post p ON s.id = p.submission_id
   WHERE  s.user_name = ?
   AND    p.tag ~* 'verified'
   AND    NOT p.tag ~* 'unverified'
   );

Without index, lower(col) LIKE '%something%' is a bit faster than col ~* 'something'. But if your tables are big and performance matters (like your question indicates), you should have matching indexes to begin with, a trigram index on tag to be precise. Assuming tag is from table post (you left room for interpretation there):

CREATE INDEX post_tag_gin_trgm_idx ON post USING gin (tag gin_trgm_ops);

Then the simpler expression is just as fast. See:

LOWER LIKE vs iLIKE
How is LIKE implemented?

Depending on cardinalities and data distribution it might make sense to combine that with submission_id in a multicolumn index:

CREATE INDEX post_foo_trgm_idx ON post USING gin (submission_id, tag gin_trgm_ops);

You'd need the additional module btree_gin for that. See:

Best index for similarity function

And it might make sense to have another multicolumn index on submission(id, user_name) - or on submission(user_name, id), again depending on the mentioned details. The first variant is more likely to be useful in case Postgres expects the predicate on submission.user_name to be more selective.

(Your handling of tags might be optimized further.)

Aside:

  If the result is greater than 0 I do something in my application for this user.

If that something is another SQL DML statement, make it a single statement for additional gains. Example:

UPDATE foo
SET    bar = 'baz'
WHERE  EXISTS ( ...) -- like above
AND    bar IS DISTINCT FROM 'baz' -- avoid empty updates

About the added last line:

How do I (or can I) SELECT DISTINCT on multiple columns?

Or you embed the expression in plpgsql code for procedural needs, like demonstrated here:

PL/pgSQL checking if a row exists


Remove all special characters without removing accented characters

MySQL 8 has the REGEXP_REPLACE function that should work.

If you only need to leave alphanumeric characters, including accented characters, this would be simply

SELECT REGEXP_REPLACE(your_column, '[^[:alnum:]]+', ' ') ...

to replace any non-alphanumerics with spaces. If you want to only eliminate characters on your list, you'd use something like

SELECT REGEXP_REPLACE(your_column, '[\]\\[!@#$%.&*`~^_{}:;<>/\\|()-]+', ' ') ...

Note that in the latter case you will need to escape ']', '[', '\', and possibly '-' (or put the latter as the first or last element in the set).

dbfiddle link.


Are these two ERDs the same? Do they represent the same thing? [Weak entities and multi-valued attributes]

These two diagrams look significantly different to me.
You should have a table for employees, a second table for skills, and a third table mapping each employee to each skill they have.


Join tables where the table name changes every day

Use a synonym.

USE YourDatabaseName;
DECLARE @sql nvarchar(max) = N'CREATE SYNONYM
  dbo.EventScan FOR dbo.evt_scan_'
  + CONVERT(CHAR(8), GETDATE(), 112);
EXEC sys.sp_executesql @sql;

Then your code can always reference dbo.EventScan.

Now, schedule a job to run every night at 12:00:01 AM (or create a DDL trigger on table creation and when the new table name matches the expected pattern, or put code in whatever creates those new tables each day) that does this:

USE YourDatabaseName;
DROP SYNONYM dbo.EventScan;
— repeat code from above


WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?

The conditions in HAVING are not applied against the aggregations, but on the non-aggregated columns.

The problem here is in how you are describing what the HAVING clause applies to. The HAVING clause always applies to aggregated fields, which is all remaining columns post-aggregation. You are trying to show / say that the HAVING clause is not being applied to any aggregate functions, which is what they usually apply to. But in reality, the HAVING clause governs the result of that aggregate function, or, in your first example, the result of the grouping column. But in both cases, the aggregation has already been performed.

So in terms of performance (not to mention readability for others trying to update this code later), you use the WHERE clause to filter down to what will be aggregated, and then the HAVING clause to filter out what has been aggregated. And, while the result of a simple test as shown in the question masks the difference between the timing of the two (or logical placement in the sequence that the query is processed in) such that they "appear" to be doing the same thing, I would be quite surprised if it was not less efficient to aggregate a bunch of rows only to throw them out later when logically they could have been eliminated prior to storing / computing the aggregations. HOWEVER, if you do see that they execution plans are similar for this simple example, I am willing to bet that it is merely due to the optimizer seeing that it would be more efficient to make those HAVING conditions actual WHERE conditions as it rewrites the query before it executes it. But in that case, I still would advise against writing queries this way because you are making the optimizer take extra time to rewrite bad code when it should be spending that time / CPU cycles finding a more efficient plan. @DavidSpillett added (in a comment on this answer): "Furthermore, you are relying on the query planner seeing the optimisation potential, which it may not in more complex queries or if your code ends up ported to another database (or even just an older version of SQL Server)".

For what it's worth, even the Microsoft documentation for the HAVING clause stated that it acted as a WHERE clause when no GROUP BY was present. Now that the documentation is on GitHub, I was able to correct it recently via Pull Request #235: Correct and improve HAVING clause.


WITH cte AS ( SELECT PolicyID,
                     PolicylocationID,
                     Country,
                     COUNT(CASE WHEN Country = 'US' THEN 1 END) OVER (PARTITION BY PolicyID) us,
                     COUNT(Country) OVER (PARTITION BY PolicyID) total
              FROM test
            )
SELECT PolicyID,
       PolicylocationID,
       Country,
       CASE WHEN us = total THEN 'US'
            WHEN us = 0     THEN 'International'
            ELSE 'Global'
       END CType
FROM cte

fiddle


Is there any reason to have nonclustered indexes on a table with only 1 page in the clustered index?

do the nonclustered indexes even matter?

Yes.  You have multiple copies of this table (or subsets of it), and updates have to be coordinated across all the copies.  Query performance is probably not enhanced by the non-clustered indexes, but they are probably the cause of your deadlocks.
But for SELECT query performance, the non-clustered indexes shouldn't help.


Msg 39011, SQL Server was unable to communicate with the LaunchPad service

UPDATE: It looks like R Services setup is significantly simpler from SQL 2016 RC2 onwards.  See here.

This post applies to early CTPs of SQL Server 2016 where additional setup was required for R.

I had this error when I upgraded from CTP3.2 to 3.3.  The first thing to check is, if you have a named instance, did you use that name when you ran the post-installation script:

"...rxLibs\x64\registerRext.exe" /install /instance:sql2016

I assume so because you've got the db_rrerole but if not, uninstall using the uninstall switch and try again using your instance name:

"...rxLibs\x64\registerRext.exe" /uninstall

Monitor the output for errors during the install or consider re-directing the output to a file so you can review it afterwards, eg:

"...rxLibs\x64\registerRext.exe" /install /instance:yourNamedInstance > mylog.txt

Check through the log and report any errors you find.  If the script succeeds, the Launchpad service should be running and you can check this by running services.msc from the command-line.  Here's my service running for the default instance:

Yours should say SQL2016 where mine says MSSQLSERVER.  If that's all ok you can open up the the config file, eg from the command-line:

notepad "%programfiles%\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\rlauncher.config"

You will have to change the path above, but the file should be in the Binn directory of your SQL Server install.  Inspect the config file and make sure the paths are correct / look sensible.  I found initially that there were version numbers on the paths from multiple installs eg ( MSSQLSERVER1000 ) and basically this was interfering with the R install.  You can check registry keys HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLLaunchpad$SQL2016 and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server to see if they are consistent but I strongly advise against making changes to your registry, unless you can afford to lose the machine / VM and have to do a full re-install of Windows etc.

I basically did a full uninstall of SQL Server (including a somewhat brutal registry clean-up) and then re-installed a default instance and it worked.  I've since resolved not to mix CTPs and set up clean VMs for each one.  Also remember CTPs are basically beta products so you should not install them on any servers you value - hopefully this is a test box or non-production VM?

My best advice to you is honestly just use a VM for the CTPs because they are not designed to play well with other versions or even CTP upgrades.  I use VirtualBox on my laptop which is fine most of the time.  If you have an MSDN subscription (or even a trial one) you can set up a free VM with the latest CTP and throw it away when you're done.

Good luck!


Whether it means the end time of the SYBASE LICENSE?

This message does not mean that the license is expired or something. It only states that sybase found a valid license and checked it out. More information about the sybase license manager you can find here: http://www.sybase.com/detail?id=1009350


The server ID is used for replication to enable master and slave servers to identify themselves, uniquely. While replication may work if two slaves have the same server ID, it could affect performance. Also, having them all unique can provide help in managing and monitoring the MySQL servers participating in the replication. Currently, a MySQL master server allows slaves with the same server-ID. This is a known problem, and could cause slaves to make several smaller relay logs files. Closing and opening files as a result will consequently slow down the replication process.


Stream every row coming from a Postgres function

Q: But still i am getting all rows at the end of the function and no
intermediate rows

Unfortunately these results are accumulated (as opposed to streamed) by the server-side implementation of RETURN QUERY. This is explicitly mentioned in the documentation:

Note
The current implementation of RETURN NEXT and RETURN QUERY stores the
entire result set before returning from the function, as discussed
above. That means that if a PL/pgSQL function produces a very large
result set, performance might be poor: data will be written to disk to
avoid memory exhaustion, but the function itself will not return until
the entire result set has been generated. A future version of PL/pgSQL
might allow users to define set-returning functions that do not have
this limitation.


This is not database administration, this is data modeling. Very different disciplines.

I'm guessing your main entity is Simulation and the tables you list describe it. You don't show the structure or content of these tables so the following is based on conjecture.

Measurement looks like it could be a list of measurement types: temperature, flow, particles per unit volume, etc. SamplingRates also looks like a list of valid rates: 1/sec, 10/sec, 100/sec, etc.

Finally there are three table that look like they should be one, FlowRates, that is also a lookup table.

This would mean a Simulation is the recorded results of, say, a temperature reading at a rate of 10 times per second of a 30 ml/sec flow.

Is that accurate? If so, here would be an example:

Measurements
 ID  Name
  1  Temperature
  2  Particles per ml

SamplingRates
 ID  Name  Period
  1     1     sec
  2    10     sec

FlowRates
 ID  Rate  Unit Period
  1    10    ML    sec
  2    20    ML    sec
  2    30    ML    sec

So the example Simulation entry would show a Measurement of 1, SamplingRate of 2 and FlowRate of 3 -- along with the results of the measurement, of course, and probably a timestamp of when the simulation was performed.

It would help a lot if you would give a plain language description of an experiment: "An experiment consists of any number of simulations. A simulation is made up of various readings of...made at a certain frequency based on ..." Don't think in terms of tables and columns. Pretend you're talking to a lab technician.

Update: When designing a table (and this pertains to naming the fields) it is generally a good idea to isolate the entity from all other entities -- that is, the naming should be done as context-free as possible. What that means is if you have a field that represents, say, the name or description of the entity, then by all means call those fields "Name" and "Description". No matter that you have dozens of other fields with identical names in tables scattered around the database.

A table has no context. It is the query that establishes a context.

select  s.Name as NewSite, u1.Name as Owner, u2.Name as Manager
from    Site s
join    Users u1
    on  u1.ID = s.OwnerID
join    Users u1
    on  u2.ID = s.MgrID
where   s.Created > '2015-01-01';

Here are three tables each with the field Name -- it really doesn't matter that one table is used twice. Within each table, Name means "this is the name of the entity represented by this row."

The context established by this query is easily identified. It is looking at the owner and manager of all newly created sites and renames each Name field to suit the context. Different queries can and do use the fields in completely different contexts. As it is the query which sets the context, let the query rename the fields to whatever best suits that context.

Don't try to force a context with a table by naming the fields User_ID or User_Name and so forth. In a query, fields should be prefixed with the table name or alias anyway, so there's never any confusion.

where  User.Name = 'John Smith'

Compare with

where  User.User_Name = 'John Smith'

The extra "User_" adds no useful information. Besides, you're bound to hit something like this:

where ExtremelyLongTableName.ExtremelyLongTableName_SomewhatLongFieldName = ...

I'm exhausted just typing it the one time. Besides, some DBMSs limit the length of object names. Oracle, iirc, only considers the first 32 characters of an object name. I've hit that limit more than once in shops that use the tablename_fieldname convention. At that point you have to use abbreviations which is really messy.

Anyway, "best practices" is a fairly subjective concept. Opinions will vary. Choose what is most comfortable for you.


Unable to locate database.mdf in production server

Might be a rights issue. Have a look at the security settings on the physical .mdf file and make sure the user you are using for SSMS has access to that .mdf file.

Most probably, as you are supplying credentials, you are logging on with your Windows user id, so make sure that you have read-write priviliges on the .mdf file.

To get to the security settings:
Explore -> Locate your file -> Right click -> Properties -> Security Tab

Your user group or your user should be in that list.


To my knowledge it's not possible to create a view that is missing objects referenced in that view.

Why is it such a pain to know what the dependencies are on your views?  You are CREATING them, right?

Do they already exist in another database?  If so you can use SSMS or SMO to script them out in dependency order and bypass this issue.